'programming' 카테고리의 글 목록 (97 Page)

'programming'에 해당되는 글 387건

2017.08.02 ODBC C Code Sample

ODBC C Code Sample

programming/C_C++ 2017. 8. 2. 15:11

ODBC C Code Sample

http://www.easysoft.com/developer/languages/c/examples/index.html

SQL Server - Calling Stored Procedures using C++ ODBC

https://stackoverflow.com/questions/25775771/sql-server-calling-stored-procedures-using-c-odbc

Use Existing MSDN C++ ODBC Samples for Microsoft Linux ODBC Driver

https://blogs.msdn.microsoft.com/sqlblog/2012/01/26/use-existing-msdn-c-odbc-samples-for-microsoft-linux-odbc-driver/

Here is the final source ready to be compiled on RHEL:

#include <stdio.h>
#include <stdlib.h>
#include <sqlext.h>
#include <sql.h>

#define NAME_LEN 50
#define PHONE_LEN 20

void show_error() {
   printf("errorn");
}

int main() {
   SQLHENV henv;
   SQLHDBC hdbc;
   SQLHSTMT hstmt = 0;
   SQLRETURN retcode;
   SQLCHAR szName[NAME_LEN], szPhone[PHONE_LEN], sCustID[NAME_LEN];
   SQLLEN cbName = 0, cbCustID = 0, cbPhone = 0;

   // Allocate environment handle
   retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);

   // Set the ODBC version environment attribute
   if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
      retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER*)SQL_OV_ODBC3, 0);

      // Allocate connection handle
      if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
         retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);

         // Set login timeout to 5 seconds
         if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
            SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);

            // Connect to data source
            retcode = SQLConnect(hdbc, (SQLCHAR*) "SQLCMD", SQL_NTS, (SQLCHAR*) "Test1", 5, (SQLCHAR*) "Password1", 9);

            // Allocate statement handle
            if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
               retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

               retcode = SQLExecDirect (hstmt, (SQLCHAR *) "SELECT CustomerID, ContactName, Phone FROM CUSTOMERS ORDER BY 2, 1, 3", SQL_NTS);
               if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {

                  // Bind columns 1, 2, and 3
                  retcode = SQLBindCol(hstmt, 1, SQL_C_CHAR, sCustID, 100, &cbCustID);
                  retcode = SQLBindCol(hstmt, 2, SQL_C_CHAR, szName, NAME_LEN, &cbName);
                  retcode = SQLBindCol(hstmt, 3, SQL_C_CHAR, szPhone, PHONE_LEN, &cbPhone);

                  // Fetch and print each row of data. On an error, display a message and exit.
                  for (int i=0 ; ; i++) {
                     retcode = SQLFetch(hstmt);
                     if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO)
                        show_error();
                     if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
                        printf( "%d: %s %s %sn", i + 1, sCustID, szName, szPhone);
                     else
                        break;
                  }
               }

               // Process data
               if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
                  SQLCancel(hstmt);
                  SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
               }

               SQLDisconnect(hdbc);
            }

            SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
         }
      }
      SQLFreeHandle(SQL_HANDLE_ENV, henv);
   }
}

Public ODBC headers are installed with unixODBC 2.3.0 and are typically included in the /usr/include/odbc directory.

Be sure your IDE is configured correctly to find the headers and also be sure to link to the odbc library object using the –lodbc switch.

I was able to compile the program above using the following command line options:

cc -m64 -g -I/usr/include -L/usr/lib -lodbc -o SQLBindColtest SQLBindColtest.c

Good luck and I hope this helps you get started with writing C/C++ application that use the Microsoft Linux ODBC driver.

ODBC API Reference

https://docs.microsoft.com/ko-kr/sql/odbc/reference/syntax/odbc-api-reference

Calling a Stored Procedure

http://blog.naver.com/laster40/48723549

Calling a Stored Procedure

스토어드 프로시져 호출하는 방법엔 두가지 방법이 존재함

- ODBC로 Transact-SQL EXECUTE를 수행하는 방법
- ODBC CALL escape sqeuence 를 사용하는 방법

- OLE DB RPC escape sqeuence 를 사용하는 방법

- CALL 사용해서 SP를 호출하면 ODBC MSSQL Driver 는 Remote stored procedure call 메크니즘을 사용한다. RPC Call는 MSSQL Server에서 구문의 파씽이나 파라메터 처리등의 작업이 없이 처리하므로 EXECUTE를 호출해서 수행하는것보다 좀더 빠를 것이다. )

syntax {call ~ }

- RPC 사용해서 SP를 호출하는건 OLE DB에서 사용가능하며 ODBC의 그것과 동일하게 동작한다.

syntax {rpc ~ }

ms-help://MS.MSDNQTR.2003FEB.1042/odbcsql/od_6_035_5dnp.htm

ms-help://MS.MSDNQTR.2003FEB.1042/oledbsql/9_ole_20_4qm1.htm

/////////////////////////////////////////////////////////////////////////////////
* 제일 최악임
if( stat->execute("EXEC [dbo].[UP_SelectChar] 1") == false )
return false;
if( stat->nextRow() == false )
return false;

stat->getData(1, m_persist.ID );
stat->getData(2, m_persist.Nickname, MAX_NICKNAME_LEN);
stat->getData(3, m_persist.Level );

/////////////////////////////////////////////////////////////////////////////////
* 좀 애매하지만 prepare 사용하지 않는것보다 나음
stat->prepare( L"EXEC [dbo].[UP_SelectChar] ?" );
stat->bindParam( 1, PARAMTYPE_IN, m_playerID );
if( stat->execute() == false )
return false;
if( stat->nextRow() == false )
return false;

stat->getData(1, m_persist.ID );
stat->getData(2, m_persist.Nickname, MAX_NICKNAME_LEN);
stat->getData(3, m_persist.Level );

/////////////////////////////////////////////////////////////////////////////////
* 이런것두 비슷할듯
stat->prepare( L"{call UP_SelectChar (?)}" );
stat->bindParam( 1, PARAMTYPE_IN, m_playerID );
if( stat->execute() == false )
return false;
if( stat->nextRow() == false )
return false;
stat->getData(1, m_persist.ID );
stat->getData(2, m_persist.Nickname, MAX_NICKNAME_LEN);
stat->getData(3, m_persist.Level );

/////////////////////////////////////////////////////////////////////////////////
* 제일 강추 되는듯
stat->bindParam( 1, PARAMTYPE_IN, m_playerID );
if( stat->execute(L"{call UP_SelectChar (?)}") == false )
return false;
if( stat->nextRow() == false )
return false;
stat->getData(1, m_persist.ID );
stat->getData(2, m_persist.Nickname, MAX_NICKNAME_LEN);
stat->getData(3, m_persist.Level );

ODBC Basic Cpp 예제 - Altibase CLI 함수 사용

http://tealeoni.blog.me/120075679516

basic.cpp

/***********************************************
**  파일명 = basic.cpp
**  EMPLOYEE 테이블에 레코드 삽입 예제
**
** 사용된 Altibase CLI 함수 :
**	SQLAllocEnv()
**	SQLAllocConnect()
**	SQLAllocStmt()
**	SQLBindCol()
**	SQLBindParameter()
**	SQLDisconnect()
**	SQLDriverConnect()
**	SQLExecDirect()
**	SQLExecute()
**	SQLError()
**	SQLFreeConnect()
**	SQLFreeEnv()
**	SQLFetch()
**	SQLFreeStmt()		
**	SQLPrepare()
**	SQLEndTran()
**	SQLTransact()
**	SQLSetConnectAttr()
************************************************/
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include <sqlcli.h>

#define SQL_LEN 1024
#define MSG_LEN 1024
#define MAXCOLS 100

int db_error(SQLHENV henv, SQLHDBC hdbc, SQLHSTMT hstmt);

int main()
{
SQLHENV     henv;
SQLHDBC     hdbc;
SQLHSTMT    hstmt1,
              hstmt2;
SQLCHAR     server[20],
             uid[10],
             pwd[10],
             port[10],
             constr[100],
             query[SQL_LEN],
	id[9],
          name[31],
          dept[11],
          age[4],
          dept_no[5],
          temp[31],
          *tmp;
SQLINTEGER  len;
SQLINTEGER  bindp_len;
SQLSMALLINT colnum;
SQLRETURN   rc;

/* Get environment handle */
rc = SQLAllocEnv(&henv);
if ( rc == SQL_ERROR )
{
    db_error(henv, hdbc, SQL_NULL_HSTMT);
}
    
/* Get connection handle */
rc = SQLAllocConnect(henv, &hdbc);
if ( rc == SQL_ERROR )
{
    db_error(henv, hdbc, SQL_NULL_HSTMT);
}

/* Get server name, port num., uid, pwd by user. */
printf("Enter Server Name & Port num.
(server:port) : ");
gets(temp);
    
tmp = strtok(temp,":");
   
if( tmp == NULL )
{
    printf("You must specify Server name\n");
    SQLFreeConnect(hdbc);
    SQLFreeEnv(henv);
    exit(0);
}
    
strcpy(server, tmp);
tmp = strtok(NULL,":");
    
if( tmp == NULL )
{
    printf("You don't specify Port Number with Server name\n");
    printf("Port num : ");
    gets(port);
} else strcpy(port, tmp);
    
printf("Enter User Name : ");
gets(uid);
    
printf("Enter Password Name : ");
gets(pwd);
    
/* Make connection String for SQLDriverConnect */
sprintf(constr, "DSN=%s;UID=%s;PWD=%s;CONNTYPE=1;PORT_NO=%s",
				server, uid, pwd, port);
    
/* Connect to Altibase */
rc = SQLDriverConnect(hdbc, NULL, constr, SQL_NTS, NULL, 0, NULL,
                          SQL_DRIVER_COMPLETE);
    
if ( rc == SQL_ERROR )
{
    db_error(henv, hdbc, SQL_NULL_HSTMT);
    printf("Connect Error!! 
              Program terminated\n");
    SQLFreeConnect(hdbc);
    SQLFreeEnv(henv);
    exit(0);
}
    
/* Now Processing SQL Query */
rc = SQLAllocStmt(hdbc, &hstmt1);
if ( rc == SQL_ERROR )
{
    db_error(henv, hdbc, SQL_NULL_HSTMT);
    printf("SQLAllocStmt Error!!\n");
    rc = SQLDisconnect(hdbc);
    if ( rc == SQL_ERROR )
    {
        printf("disconnect error\n");
    }
    SQLFreeConnect(hdbc);
    SQLFreeEnv(henv);
    exit(0);
}
    
rc = SQLAllocStmt(hdbc, &hstmt2);
if ( rc == SQL_ERROR )
{
    db_error(henv, hdbc, SQL_NULL_HSTMT);
    printf("SQLAllocStmt Error!!\n");
    if ( rc == SQL_ERROR )
    {
        printf("disconnect error\n");
    }
    SQLFreeConnect(hdbc);
    SQLFreeEnv(henv);
    exit(0);
}
    
/*  AUTO_COMMIT MODE False */
SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT, (void*)SQL_AUTOCOMMIT_OFF, 0);

sprintf(query, "CREATE TABLE EMPLOYEE 
( ID VARCHAR(8) primary key, 
  NAME VARCHAR(30) not null,
 AGE NUMERIC(3), 
 DEPT VARCHAR(10), 
 DEPT_NO NUMERIC(4) )");
    
rc = SQLExecDirect(hstmt1,query, SQL_NTS);
if ( rc == SQL_ERROR )
{
    printf("Error : %s\n",query);
    db_error(henv, hdbc, hstmt1);
}
SQLFreeStmt(hstmt1, SQL_CLOSE);
    
sprintf(query,"INSERT INTO EMPLOYEE
                  VALUES( ?, ?, ?, ?, ? )");
rc = SQLPrepare(hstmt1, query, SQL_NTS);
if ( rc == SQL_ERROR )
{
    db_error(henv, hdbc, hstmt1);
}
	
rc = SQLBindParameter(hstmt1, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 8, 0, id, 0, &bindp_len);
if ( rc == SQL_ERROR )
{
    db_error(henv, hdbc, hstmt1);
}	

rc = SQLBindParameter(hstmt1, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 30, 0, name, 0,
&bindp_len);
if ( rc == SQL_ERROR )
{
    db_error(henv, hdbc, hstmt1);
}	
	
rc = SQLBindParameter(hstmt1, 3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_NUMERIC, 3, 0, age, 0, &bindp_len);
if ( rc == SQL_ERROR )
{
    db_error(henv, hdbc, hstmt1);
}
    
rc = SQLBindParameter(hstmt1, 4, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 10, 0, dept, 0, &bindp_len);
if ( rc == SQL_ERROR )
{
    db_error(henv, hdbc, hstmt1);
}	
	
rc = SQLBindParameter(hstmt1, 5, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_NUMERIC, 4, 0, dept_no, 0, &bindp_len);
if ( rc == SQL_ERROR )
{
    db_error(henv, hdbc, hstmt1);
}
    
sprintf(query,"SELECT * FROM EMPLOYEE 
WHERE NAME = ?");
rc = SQLPrepare(hstmt2, query, SQL_NTS);
if ( rc == SQL_ERROR )
{
    db_error(henv, hdbc, hstmt2);
}

rc = SQLBindParameter(hstmt2, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 30, 0, name, 0, &bindp_len);
if ( rc == SQL_ERROR )
{
    db_error(henv, hdbc, hstmt2);
}
    
bindp_len = SQL_NTS;
while (1)
{
    printf("Insert Id, Name, Age, Dept, Dept_no for EMPLOYEE Table.\n");
    printf("or Type 'q' to Quit.\n");
    printf("Id : ");
    fflush(stdin);
    gets(id);
        
    if ( id[0] == 'q' ) break;
        
    printf("Name : ");
    gets(name);

    printf("Age : ");
    gets(age);
        
    printf("Dept : ");
    gets(dept);
        
    printf("Dept_no : ");
    gets(dept_no);
    
    if ( !id[0] || !name[0]  || !age[0] ||
!dept[0] || !dept_no[0] )
    {
        printf("Missing values\n");
        continue;
    }
        
    rc = SQLExecute(hstmt1);
    if ( rc == SQL_ERROR )
    {
        printf("Insert Failed!!\n");
        db_error(henv, hdbc, hstmt1);
    }
    else 
    {
    	/*rc = SQLTransact(henv,hdbc,SQL_COMMIT);*/
        rc = SQLEndTran(SQL_HANDLE_ENV, hdbc,
 SQL_COMMIT); 
        	
        if(rc == SQL_ERROR)
        {
            db_error(henv, hdbc, hstmt1);
        }
        else
        {
            printf("\n COMMIT Success. \n");
        }
    }
        
    rc = SQLExecute(hstmt2);
    if ( rc == SQL_ERROR )
    {
        db_error(henv, hdbc, hstmt2);
    }
    else
    {
        printf("Inserted row was :\n");
            
        if (SQL_ERROR == SQLBindCol(hstmt2, 1, SQL_C_CHAR, id, sizeof(id), &len)) 
        {
            printf("SQLBindCol error!!!\n");
	      db_error(henv, hdbc, hstmt2);
        }
        if (SQL_ERROR == SQLBindCol(hstmt2, 2, SQL_C_CHAR, name, sizeof(name), &len)) 
        {
            printf("SQLBindCol error!!!\n");
	       db_error(henv, hdbc, hstmt2);
        }
	  if (SQL_ERROR == SQLBindCol(hstmt2, 3, 
SQL_C_CHAR, age, sizeof(age), &len)) 
        {
            printf("SQLBindCol error!!!\n");
            db_error(henv, hdbc, hstmt2);
           }
         if (SQL_ERROR == SQLBindCol(hstmt2, 4, 
              SQL_C_CHAR, dept, sizeof(dept), &len)) 
         {
             printf("SQLBindCol error!!!\n");
	          db_error(henv, hdbc, hstmt2);
           }
	if (SQL_ERROR == SQLBindCol(hstmt2, 5, 
SQL_C_CHAR, dept_no, sizeof(dept_no), &len))
{
               printf("SQLBindCol error!!!\n");
	         db_error(henv, hdbc, hstmt2);
           }
	
	     while (SQLFetch(hstmt2) == SQL_SUCCESS)
	     {
		         printf("ID: %s , Name: %s , Age: %s , 
                       Dept: %s , dept_no: %s\n\n\n", 
                      id, name, age, dept, dept_no);
           }
        }
    }
    
    SQLFreeStmt(hstmt1, SQL_CLOSE);
    SQLFreeStmt(hstmt2, SQL_CLOSE);
    
    sprintf(query, "SELECT * FROM EMPLOYEE");
    
    rc = SQLExecDirect(hstmt1,query, SQL_NTS);
    if ( rc == SQL_ERROR )
    {
        db_error(henv, hdbc, hstmt1);
    }
    else
    {
        printf("%8s%30s%5s%10s%8s\n", "Id", 
"Name", "Age", "Dept", "Dept_no");
        int i;
        for ( i = 0; i &lt; 65; i++ ) printf("-");
        printf("\n");
        
        if (SQL_ERROR == SQLBindCol(hstmt1, 1, 
SQL_C_CHAR, id, sizeof(id), &len)) 
        {
            printf("SQLBindCol error!!!\n");
	      db_error(henv, hdbc, hstmt2);
	   }
        if (SQL_ERROR == SQLBindCol(hstmt1, 2, 
SQL_C_CHAR, name, sizeof(name), &len)) 
        {
	       printf("SQLBindCol error!!!\n");
             db_error(henv, hdbc, hstmt1);
        }
       if (SQL_ERROR == SQLBindCol(hstmt1, 3, 
SQL_C_CHAR, age, sizeof(age), &len)) 
    {
        printf("SQLBindCol error!!!\n");
        db_error(henv, hdbc, hstmt1);
    }
    if (SQL_ERROR == SQLBindCol(hstmt1, 4, 
SQL_C_CHAR, dept, sizeof(dept), &len)) 
    {
        printf("SQLBindCol error!!!\n");
        db_error(henv, hdbc, hstmt1);
    }
    if (SQL_ERROR == SQLBindCol(hstmt1, 5, 
SQL_C_CHAR, dept_no, sizeof(dept_no), &len)) 
    {
        printf("SQLBindCol error!!!\n");
        db_error(henv, hdbc, hstmt1);
    }

    while (SQLFetch(hstmt1) == SQL_SUCCESS)
    {
        printf("%8s%30s%5s%10s%8s\n", id, name, 
age, dept, dept_no);
    }
    printf("\n");
}
    
SQLFreeStmt(hstmt1, SQL_CLOSE);
    
/* Disconnect & free all handle */
rc = SQLDisconnect(hdbc);
if ( rc == SQL_ERROR )
{
    printf("disconnect error\n");
}
SQLFreeConnect(hdbc);
SQLFreeEnv(henv);
}

int db_error(SQLHENV henv, SQLHDBC hdbc, SQLHSTMT hstmt)
{
    SQLINTEGER errNo;
    SQLSMALLINT msgLength;
    SQLCHAR errMsg[MSG_LEN];
	
    if (SQL_SUCCESS == SQLError ( henv, hdbc, hstmt, NULL, &errNo, errMsg, MSG_LEN, &msgLength )) 
    {
        printf(" ERR_-%ld : %s\n", errNo, errMsg);
    }
    return SQL_ERROR;
}

Posted by 공간사랑

이전 1 ··· 94 95 96 97 98 99 100 ··· 387 다음

공간사랑

'programming'에 해당되는 글 387건

ODBC C Code Sample

basic.cpp

카테고리

태그목록

최근에 올라온 글

최근에 달린 댓글

최근에 받은 트랙백

글 보관함

달력

티스토리툴바


	by 공간사랑