java odbc timesten_TimesTen 使用ODBC连接数据库的程序问题

问题已解决。更新后的代码和运行结果如下：

代码：

/* TimesTen ODBC example by canoe*/

#ifdef WIN32

#include

#else

#include

#endif

#include

#include

#include

#include

#include

#define SQL_MAXLEN    200     /* Max length of SQL buffer */

#define NAME_MAXLEN   20     /* Max length of Name */

typedef         SQLCHAR *STR;

void CheckReturnCode(SQLRETURN rc, SQLHENV henv, SQLHDBC hdbc, SQLHSTMT hstmt, char *msg, char *filename, int lineno);

int main(void)

{

/* General return code for the API */

SQLRETURN rc = SQL_SUCCESS;

/* Environment handle */

SQLHENV hEnv = SQL_NULL_HENV;

/* Connection handle */

SQLHDBC hDbc = SQL_NULL_HDBC;

/* Statement handle */

SQLHSTMT hStmt_ins = SQL_NULL_HSTMT;

SQLHSTMT hStmt_sel = SQL_NULL_HSTMT;

/* Connection attributes */

SQLCHAR *ConnString = (SQLCHAR*)"DSN=ttodbc;";

/* local variable for insert*/

SQLCHAR    name_ins[NAME_MAXLEN+1];

SQLCHAR    department_ins[NAME_MAXLEN+1];

/* local variable for select*/

int     id_sel = 0;

SQLCHAR    name_sel[NAME_MAXLEN+1];

SQLCHAR    department_sel[NAME_MAXLEN+1];

char     *sqlbuf='\0';

SQLINTEGER     cbStatus = SQL_NTS;

/* Initializtion begin...*/

rc = SQLAllocEnv(&hEnv);

if ( rc != SQL_SUCCESS&& rc != SQL_SUCCESS_WITH_INFO )

{

fprintf(stderr, "*** ERROR in %s, line %d: %s\n",__FILE__, __LINE__, "allocating an environment handle");

exit(1);

}

rc = SQLAllocConnect(hEnv, &hDbc);

CheckReturnCode(rc, hEnv, SQL_NULL_HDBC, SQL_NULL_HSTMT, "Unable to allocate a connection handle\n", __FILE__, __LINE__);

rc = SQLDriverConnect(hDbc, NULL, ConnString, SQL_NTS,NULL, 0, NULL, SQL_DRIVER_NOPROMPT);

CheckReturnCode(rc, hEnv, SQL_NULL_HDBC, SQL_NULL_HSTMT, "Unable to get a connection\n", __FILE__, __LINE__);

rc = SQLSetConnectOption (hDbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF);

CheckReturnCode(rc, hEnv, SQL_NULL_HDBC, SQL_NULL_HSTMT, "Unable to turn off commit\n", __FILE__, __LINE__);

rc = SQLAllocStmt(hDbc, &hStmt_ins);

CheckReturnCode(rc, hEnv, hDbc, SQL_NULL_HSTMT, "Unable to allocate a insert statement handle\n", __FILE__, __LINE__);

rc = SQLAllocStmt(hDbc, &hStmt_sel);

CheckReturnCode(rc, hEnv, hDbc, SQL_NULL_HSTMT, "Unable to allocate a select statement handle\n", __FILE__, __LINE__);

printf("Successfully ini environment...\n");

sqlbuf = (char *)malloc(SQL_MAXLEN+1);

if (sqlbuf == NULL) {

fprintf(stderr, "*** ERROR in %s, line %d: %s\n",__FILE__, __LINE__, "allocating an SQL buffer");

exit(1);

}

/*strcat(sqlbuf,"insert into student values(studentID.NEXTVAL,?,?)"); */

strcat(sqlbuf,"insert into student values(studentID.NEXTVAL,?,?)");

rc = SQLPrepare(hStmt_ins, (STR) sqlbuf, SQL_NTS);

CheckReturnCode(rc, hEnv, hDbc, hStmt_ins, "Unable to prepare statment\n", __FILE__, __LINE__);

/* binding inserting column */

rc = SQLBindParameter(hStmt_ins, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR,0, 0,name_ins, 0, NULL);

CheckReturnCode(rc, hEnv, hDbc, hStmt_ins, "Unable to bind insert name\n", __FILE__, __LINE__);

rc = SQLBindParameter(hStmt_ins, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR,0, 0,department_ins, 0, NULL);

CheckReturnCode(rc, hEnv, hDbc, hStmt_ins, "Unable to bind insert department\n", __FILE__, __LINE__);

/**rc = SQLTransact(hEnv, hDbc, SQL_COMMIT);

//CheckReturnCode(rc, hEnv, hDbc, hStmt_ins, "Unable to commit inserting\n", __FILE__, __LINE__);*/

/*inserting first*/

printf("Preparing insert...\n");

printf("Name  :");

scanf("%s",name_ins);

printf("Department :");

scanf("%s",department_ins);

printf("Inserting...\n");

rc = SQLExecute(hStmt_ins);

CheckReturnCode(rc, hEnv, hDbc, hStmt_ins, "Unable to inserting\n", __FILE__, __LINE__);

rc = SQLTransact(hEnv, hDbc, SQL_COMMIT);

CheckReturnCode(rc, hEnv, hDbc, hStmt_ins, "Unable to commit inserting\n", __FILE__, __LINE__);

if ( hStmt_ins != SQL_NULL_HSTMT ){

rc = SQLFreeStmt(hStmt_ins, SQL_DROP);

CheckReturnCode(rc, hEnv, hDbc, hStmt_ins, "Unable to free the insert statement handle\n", __FILE__, __LINE__);

}

printf("Inserting done.\n");

printf("Selecting...\n");

/** Prepare select statement     */

strcpy(sqlbuf,"select id,name,department from student");

rc = SQLPrepare(hStmt_sel, (STR) sqlbuf, SQL_NTS);

CheckReturnCode(rc, hEnv, hDbc, hStmt_sel, "Unable to prepare statment\n", __FILE__, __LINE__);

/* binding output column */

rc = SQLBindCol(hStmt_sel, 1, SQL_C_ULONG, &id_sel, 0, NULL);

CheckReturnCode(rc, hEnv, hDbc, hStmt_sel, "Unable to bind id\n", __FILE__, __LINE__);

rc = SQLBindCol(hStmt_sel, 2, SQL_C_CHAR,name_sel, sizeof(name_sel), NULL);

CheckReturnCode(rc, hEnv, hDbc, hStmt_sel, "Unable to bind name\n", __FILE__, __LINE__);

rc = SQLBindCol(hStmt_sel, 3, SQL_C_CHAR,department_sel, sizeof(department_sel), NULL);

CheckReturnCode(rc, hEnv, hDbc, hStmt_sel, "Unable to bind department\n", __FILE__, __LINE__);

// rc = SQLTransact(hEnv, hDbc, SQL_COMMIT);

// CheckReturnCode(rc, hEnv, hDbc, hStmt_ins, "Unable to commit inserting\n", __FILE__, __LINE__);

rc = SQLExecute(hStmt_sel);

CheckReturnCode(rc, hEnv, hDbc, hStmt_sel, "Unable to executeSQL\n", __FILE__, __LINE__);

//fetch data

while ((rc = SQLFetch(hStmt_sel)) == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO){

if (rc != SQL_NO_DATA_FOUND){

printf("Id: %d ",id_sel);

printf("Name: %s ",name_sel);

printf("Department: %s \n",department_sel);

}

}

if ( hStmt_sel != SQL_NULL_HSTMT ){

rc = SQLFreeStmt(hStmt_sel, SQL_CLOSE);

CheckReturnCode(rc, hEnv, hDbc, hStmt_sel, "Unable to free the select statement handle\n", __FILE__, __LINE__);

}

rc = SQLTransact(hEnv, hDbc, SQL_COMMIT);

if ( hDbc != SQL_NULL_HDBC ){

rc = SQLDisconnect(hDbc);

CheckReturnCode(rc, hEnv, hDbc, SQL_NULL_HSTMT, "Unable to close the ""connection\n", __FILE__, __LINE__);

rc = SQLFreeConnect(hDbc);

CheckReturnCode(rc, hEnv, hDbc, SQL_NULL_HSTMT, "Unable to free the ""connection handle\n", __FILE__, __LINE__);

}

if ( hEnv != SQL_NULL_HENV ){

rc = SQLFreeEnv(hEnv);

CheckReturnCode(rc, hEnv, SQL_NULL_HDBC, SQL_NULL_HSTMT, "Unable to free the ""environment handle\n", __FILE__, __LINE__);

}

free(sqlbuf);

exit(0);

}

void CheckReturnCode(SQLRETURN rc, SQLHENV henv, SQLHDBC hdbc, SQLHSTMT hstmt, char *msg, char *filename, int lineno)

{

#define MSG_LNG 512

SQLCHAR szSqlState[MSG_LNG];

/* SQL state string */

SQLINTEGER pfNativeError;

/* Native error code */

SQLCHAR szErrorMsg[MSG_LNG];

/* Error msg text buffer pointer */

SQLSMALLINT pcbErrorMsg;

/* Error msg text Available bytes */

SQLRETURN ret = SQL_SUCCESS;

if ( rc != SQL_SUCCESS && rc != SQL_NO_DATA_FOUND )

{

if ( rc != SQL_SUCCESS_WITH_INFO )

{

/*

* It隆炉s not just a warning

*/

fprintf(stderr, "*** ERROR in %s, line %d:"" %s\n", filename, lineno, msg);

}

/*

* Now see why the error/warning occurred

*/

while ( ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO )

{

ret = SQLError(henv, hdbc, hstmt, szSqlState, &pfNativeError, szErrorMsg, MSG_LNG,  &pcbErrorMsg);

switch ( ret )

{

case SQL_SUCCESS:

fprintf(stderr, "*** %s\n""*** ODBC Error/Warning = %s, ""TimesTen Error/Warning "" = %d\n", szErrorMsg, szSqlState, pfNativeError);

break;

case SQL_SUCCESS_WITH_INFO:

fprintf(stderr, "*** Call to SQLError"" failed with return code of ""SQL_SUCCESS_WITH_INFO.\n ""*** Need to increase size of"" message buffer.\n");

break;

case SQL_INVALID_HANDLE:

fprintf(stderr, "*** Call to SQLError"" failed with return code of ""SQL_INVALID_HANDLE.\n");

break;

case SQL_ERROR:

fprintf(stderr, "*** Call to SQLError"" failed with return code of ""SQL_ERROR.\n");

break;

case SQL_NO_DATA_FOUND:

break;

} /* switch */

} /* while */

}

}

运行结果：

Successfully ini environment...

Preparing insert...

Name  :tao

Department :computer

Inserting...

Inserting done.

Selecting...

Id: 41 Name: tao Department: computer