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
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 < 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; }
