java odbc oracle 10演示,ODBC访问Oracle实例（附代码）

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

}