Advertisement

03.17.2008 at 06:05AM PDT, ID: 23246851
[x]
Attachment Details

Problem using ODBC SQLPrepare, SQLBindParameter with MS Access database.

Asked by drewthornton in C Programming Language

Tags: ODBC, C

I am adding ODBC to an existing C application so it can work with databases. Later we will be implementing specific driver access methods.

I am new to ODBC and am having a problem with using SQLPrepare and SQLBindParameter when trying to update an MS Access database.

I am using Eclipse IDE with CDT as the build environment. The compiler is gcc 3.4.5 from MinGW on Win XP Pro SP2. I have the MSDN.

The following sample compiles and runs. If I hard code the UPDATE SQL statement it works and updates the correct record toACME. When I try to use SQLPrepare and SQLBindParameter, the record is updated but the value is NULL. No errors are reported.

What am I missing here?
Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:

/*
Preconditions:
- Data source - MS Access database name 'db4.mdb'  
- Table 'Customers' with the following structure:
column      |  data type |  size
------------+------------+-------
CustomerID  |  Number    |  Integer
CompanyName |  Text      |  50
Address     |  Text      |  50
Phone       |  Text      |  11
 
- Initil Values:
CustomerID	CompanyName	Address	     Phone
0           ABC CO      123 Main St. 5555555555
1           DEF Inc     124 Main St. 5555555555
*/
 
#include <windows.h>
#include <sqlext.h>
#include "stdio.h"
 
SQLCHAR   	   *pszDsn  = (SQLCHAR *)"PROVIDER=MSDASQL;DRIVER=Microsoft Access Driver (*.mdb);DBQ=C:\\ODBCTest\\db4.mdb;FIL=;UID=;PWD=;"; 
SQLCHAR        *pszUser = (SQLCHAR *)"";        // User name   
SQLCHAR        *pszPwd  = (SQLCHAR *)"";        // Password 
SQLHENV        henv = NULL;
SQLHDBC        hdbc = NULL;
SQLHSTMT       hsqlStmt;
SQLRETURN      retcode;
SQLLEN         NameLen;
char           strData[50]; //Buffer
 
void dbDisplayError( SQLSMALLINT hType, SQLHANDLE hHandle, SQLRETURN sqlStatus,char *pszAPI)
{//Genaric error function
    SQLCHAR     szSqlState[6];
    SQLCHAR     szErrorMsg[SQL_MAX_MESSAGE_LENGTH];
    SQLINTEGER  nNativeError;
    SQLSMALLINT nMsgLen;
    SQLRETURN   nStatus;
 
    printf( "\n%s ->failed\n----------------------------------\n", pszAPI );
 
    // Usually returned when UPDATE or DELETE do not find match
    if( sqlStatus == SQL_NO_DATA ){
        strcpy( szSqlState, "n/a" );
        nNativeError = -1;
        strcpy( szErrorMsg, "No data found. Check WHERE condition" );
    }
    // Generic error or warning. Description obtained by SQLGetDiagRec
    else if( sqlStatus == SQL_ERROR || sqlStatus == SQL_SUCCESS_WITH_INFO ){
        nStatus = SQLGetDiagRec( hType, hHandle, 1, szSqlState, 
                  &nNativeError, szErrorMsg, sizeof(szErrorMsg), &nMsgLen );
        if( nStatus == SQL_ERROR || nStatus == SQL_INVALID_HANDLE ){
            printf( "SQLGetDiagRec failed to retrieve error info\n" );
        }
    }
    else{
        printf( "Unknown error\n\n" );
        return;
    }
    nStatus = SQLGetDiagRec( hType, hHandle, 1, szSqlState, &nNativeError, szErrorMsg, sizeof(szErrorMsg), &nMsgLen );
    printf( "SQL state: %s\n", szSqlState );
    printf( "DB Native Error: %i\n", nNativeError );
    printf( "Error Message: %s\n\n", szErrorMsg );
}
 
int main(void) 
{//Main
	printf("Start.\n");
   retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
   
   retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER*)SQL_OV_ODBC3, 0); 
 
   retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc); 
 
   retcode = SQLSetConnectOption( hdbc, SQL_LOGIN_TIMEOUT, 5); 
   if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
   {
	   dbDisplayError( SQL_HANDLE_DBC, hdbc, retcode, "SQLSetConnectOption.SQL_LOGIN_TIMEOUT" );
	   return SQL_ERROR;
   }
   retcode = SQLSetConnectOption( hdbc, SQL_ACCESS_MODE, SQL_MODE_READ_WRITE);
   if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
   {
	   dbDisplayError( SQL_HANDLE_DBC, hdbc, retcode, "SQLSetConnectOption.SQL_ACCESS_MODE" );
	   return SQL_ERROR;
   }
   retcode = SQLSetConnectOption( hdbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF);
   if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
   {
	   dbDisplayError( SQL_HANDLE_DBC, hdbc, retcode, "SQLSetConnectOption.SQL_AUTOCOMMIT" );
	   return SQL_ERROR;
   }
 
   retcode = SQLDriverConnect(hdbc, NULL, (SQLCHAR *)pszDsn, (short) (strlen(pszDsn)+1), NULL, 0, NULL, SQL_DRIVER_NOPROMPT);
   if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
   {
       dbDisplayError( SQL_HANDLE_DBC, hdbc, retcode, "SQLDriverConnect" );
       return SQL_ERROR;
   }
 
   retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hsqlStmt);
 
//If this is hard coded it works: "UPDATE Customers SET CompanyName = 'ACME' WHERE CustomerID = 0"
   retcode = SQLPrepare(hsqlStmt, (SQLCHAR*)"UPDATE Customers SET CompanyName = ? WHERE CustomerID = 0", SQL_NTS);
 
   if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
   {
	   dbDisplayError( SQL_HANDLE_STMT, hsqlStmt, retcode, "SQLPrepare" );
	   return SQL_ERROR;
   }
   
   retcode =  SQLBindParameter(hsqlStmt, 		//[Input] Statement handle.
		   					   1, 				//[Input] Parameter number, ordered sequentially in increasing parameter order, starting at 1.
							   SQL_PARAM_INPUT, //[Input] The type of the parameter. For more information
							   SQL_C_CHAR, 		//[Input] The C data type of the parameter
							   SQL_CHAR, 		//[Input] The SQL data type of the parameter
							   50,			    //[Input] The size of the column or expression of the corresponding parameter marker
							   0, 				//[Input] The decimal digits of the column or expression of the corresponding parameter marker
							   &strData[0], 	//[Deferred Input] A pointer to a buffer for the parameter's data
							   0, 				//[Input/Output] Length of the ParameterValuePtr buffer in bytes
							   &NameLen);		//[Deferred Input] A pointer to a buffer for the parameter's length
	
   if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
   {
         dbDisplayError( SQL_HANDLE_STMT, hsqlStmt, retcode, "SQLBindParameter" );
         return SQL_ERROR;
   }
   
   strcpy(strData, "ACME");//
//        "UPDATE Customers SET CompanyName = ? WHERE CustomerID = 0"
//                                            ^
//                                           ACME
   retcode = SQLExecute(hsqlStmt);
   if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
   {
         dbDisplayError( SQL_HANDLE_STMT, hsqlStmt, retcode, "SQLExecute" );
         return SQL_ERROR;
   }
   
   retcode = SQLTransact(NULL, hdbc, SQL_COMMIT);
   if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
   {
	   dbDisplayError( SQL_HANDLE_DBC, hdbc, retcode, "SQLTransact" );
	   return SQL_ERROR;
   }
 
// Clean up.
   SQLCloseCursor(hsqlStmt);
   SQLDisconnect(hdbc);
   SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
   SQLFreeHandle(SQL_HANDLE_ENV, henv);
   printf("All done.\n");
   return retcode;
}
 
Keywords: Problem using ODBC SQLPrepare, SQ…
Title
1 Help connecting to a remote ODBC ser…
2 Transaction and ODBC
3 Test Connection of ODBC
4 ODBC Timing Out
5 ODBC Connections in Sharepoint
6 ODBC With gcc
 
Loading Advertisement...
 
[+][-]03.17.2008 at 08:54AM PDT, ID: 21143353

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zone: C Programming Language
Tags: ODBC, C
Sign Up Now!
Solution Provided By: fridom
Participating Experts: 1
Solution Grade: B
 
 
[+][-]03.17.2008 at 10:37AM PDT, ID: 21144370

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]03.18.2008 at 11:48AM PDT, ID: 21154427

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628