ODBC访问Oracle实例（附代码）

n430n00f33

ODBC访问Oracle基本流程和代码：

#include <Windows.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "sql.h"
#include "sqlext.h"
void check_return( RETCODE rc, HENV henv, HDBC hdbc, HSTMT hstmt )
{
UCHAR state_str [SQL_MAX_MESSAGE_LENGTH];
SDWORD native_error;
UCHAR  error_msg [SQL_MAX_MESSAGE_LENGTH];
SWORD  error_msg_avail = SQL_MAX_MESSAGE_LENGTH - 1;
SWORD  error_msg_len;
RETCODE local_rc;
if (rc != SQL_ERROR && rc != SQL_SUCCESS_WITH_INFO )
{
return;
}
local_rc = SQLError (henv,hdbc,hstmt,state_str,&native_error,error_msg,error_msg_avail,&error_msg_len);
if (local_rc != SQL_SUCCESS && rc !=SQL_SUCCESS_WITH_INFO)
{
fprintf(stderr,"Uninterpretable error; exiting \n");
exit (EXIT_FAILURE);
}
if (rc == SQL_SUCCESS_WITH_INFO)
{
fprintf(stderr,"Fatal Error %s: %s\n", state_str,error_msg);
return;
}
fprintf(stderr,"Fatal Error %s: %s\n",state_str,error_msg);
exit (EXIT_FAILURE);
}
/*----------------------------------------------------------------------*/
int main (void)
{
HENV henv = SQL_NULL_HENV;
HDBC hdbc = SQL_NULL_HDBC;
HSTMT hstmt = SQL_NULL_HSTMT;
RETCODE rc;
char buf[257];
short buflen;
printf ("Initialize the environment structure.\n");
SQLAllocEnv (&henv);
printf ("Initialize the connection structure.\n");
SQLAllocConnect (henv,&hdbc);
printf("Load the ODBC driver.\n");
rc = SQLConnect(hdbc,(SQLCHAR*)"Oracle", SQL_NTS, //注明：DBDEMO是一个系统数据源，如果这样子写就不行L“DNS=DBDEMO”
(SQLCHAR*)"alien", SQL_NTS, (SQLCHAR*)"alienchang", SQL_NTS);
//rc = SQLDriverConnect (hdbc,0,(SQLCHAR*)"DSN=Oracle;UID=alien;PASSWD=alienchang",SQL_NTS,(UCHAR*) buf,sizeof (buf),&buflen,SQL_DRIVER_COMPLETE);
check_return (rc,henv,hdbc,hstmt);
printf ("Initialize the statement structure.\n");
SQLAllocStmt (hdbc,&hstmt);
SQLUINTEGER ID;
SQLCHAR Name[20];
SQLINTEGER szID = 0, szName=0;

/************************************************************************/
/* prepare, bind and execute                                            */
/************************************************************************/
SQLPrepare(hstmt, (SQLCHAR*)"begin say( ?, ?); end;", SQL_NTS);
SQLBindParameter(hstmt, 1, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_VARCHAR, 10, 0,
&Name, 0, &szName);
SQLBindParameter(hstmt, 2, SQL_PARAM_OUTPUT, SQL_INTEGER, SQL_INTEGER, 64, 0,
&ID, 0, 0);
rc= SQLExecute(hstmt);
printf("%03d %d %s %d\n", ID, szID, Name, szName);

/************************************************************************/
/* SqlExecDirect Select                                                 */
/************************************************************************/
SQLSMALLINT cols;
SQLINTEGER   sID;
SQLCHAR   szAccount[50], szRegdate[50];
SQLINTEGER cbID, cbAccount, cbRegdate; //save the length of parameter
rc = SQLExecDirect (hstmt,(SQLCHAR*)"Select id, account, to_char(regdate,'yyyy-mm-dd hh24:mi:ss') from hx_account", SQL_NTS);
check_return (rc, henv, hdbc, hstmt);
while(SQL_NO_DATA != SQLFetch(hstmt)){
SQLGetData(hstmt, 1, SQL_C_ULONG, &sID, 0, &cbID);
//此处并没有指明BufferLength参数的值，是因为数据类型是定长的LONG型
SQLGetData(hstmt, 2, SQL_C_CHAR, szAccount, 50, &cbAccount);
SQLGetData(hstmt, 3, SQL_C_CHAR, szRegdate, 50,&cbRegdate);
printf("%8d %s %s\n", sID,  szAccount, szRegdate);
}
SQLCHAR colName[21];
SQLUINTEGER szCol;
SQLSMALLINT colNums, szCName, dType, Digits, isNULL;
rc = SQLNumResultCols(hstmt, &colNums);
rc = SQLDescribeCol(hstmt, 1, colName, 20, &szCName, &dType, &szCol, &Digits, &isNULL);
printf("列数：%d \n第一列：name：%s %d\t type: %d\t size: %d\t isnull:%d\n", colNums, colName, szCName, dType, szCol, isNULL);
/************************************************************************/
/* Create Table and Insert                                              */
/************************************************************************/
printf ("Creat table table \"foo\".\n");
rc = SQLExecDirect (hstmt,(SQLCHAR*)"CREATE TABLE foo (bar INTEGER)", SQL_NTS);
check_return (rc, henv, hdbc, hstmt);
printf ("Insert values into table \"foo\".\n");
rc = SQLExecDirect (hstmt,(SQLCHAR*)"INSERT INTO foo(bar) VALUES (1)", SQL_NTS);
check_return (rc, henv, hdbc, hstmt);
rc = SQLExecDirect (hstmt,(SQLCHAR*)"INSERT INTO foo(bar) VALUES (2)", SQL_NTS);
check_return (rc, henv, hdbc, hstmt);
rc = SQLExecDirect (hstmt,(SQLCHAR*)"INSERT INTO foo(bar) VALUES (3)", SQL_NTS);
check_return (rc, henv, hdbc, hstmt);
SQLINTEGER rowNums;
rc = SQLRowCount(hstmt, &rowNums);
printf("影响行数：%d\n", rowNums);
printf ("Drop table \"foo\".\n");
rc = SQLExecDirect (hstmt, (SQLCHAR*)"DROP TABLE foo", SQL_NTS);
check_return (rc,henv,hdbc,hstmt);
/*  We're done:free resources and exit*/
printf ("Free the statement handle.\n");
SQLFreeStmt (hstmt,SQL_DROP);
printf ("Disconnect from the data source.\n");
SQLDisconnect (hdbc);
printf ("Free the connection structure.\n");
SQLFreeConnect (hdbc);
printf ("Free the environment structure.\n");
SQLFreeEnv (henv);
printf ("Goodbye!\n");
exit (EXIT_SUCCESS);
}