Prev: ModifyMenu issue
Next: CComBSTR vs string
From: Jonathan Blitz on 1 Jun 2006 13:11
Sorry for posting this here but the database forums are totally dead.
Maybe one or two posts a week or so.

I am using VC++ 2005 and am trying to get to an ODBC database.
This is my code:

SQLHENV henv1;
SQLRETURN returnValue;
SQLINTEGER odbcVersion = SQL_OV_ODBC3;
SQLWCHAR SQLState[5];
SQLINTEGER nativeErrorCode;
SQLWCHAR nativeErrorMessage[1000];
SQLSMALLINT nativeErrorMessageLength;

returnValue = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv1);

returnValue =
SQLSetEnvAttr(henv1,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);

/* Allocate connection handle */
SQLHDBC hdbc;
SQLHSTMT hstmt;

returnValue = SQLAllocHandle(SQL_HANDLE_DBC, henv1, &hdbc);


returnValue =
SQLConnectA(hdbc,(SQLCHAR*)"PTPGSQL21",9,(SQLCHAR*)"",SQL_NTS,(SQLCHAR*)"",S
QL_NTS);

returnValue = SQLAllocStmt(hdbc,&hstmt);

// Execute the SELECT statement.
SQLUINTEGER NumRowsFetched;
SQLUSMALLINT RowStatusArray[ROW_ARRAY_SIZE];
SQLINTEGER rowArraySize = 10;
typedef struct {SQLUINTEGER gameId; SQLINTEGER gameIdInd;} gameStruct;
gameStruct gamesData[ROW_ARRAY_SIZE];

returnValue = SQLSetStmtAttr(hstmt,
SQL_ATTR_ROW_BIND_TYPE,(void*)sizeof(gameStruct), 0);

returnValue = SQLSetStmtAttr(hstmt,
SQL_ATTR_ROW_ARRAY_SIZE,(void*)rowArraySize, 0);

returnValue = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_STATUS_PTR,
RowStatusArray,0);

returnValue = SQLSetStmtAttr(hstmt,
SQL_ATTR_ROWS_FETCHED_PTR,&NumRowsFetched, 0);

returnValue = SQLBindCol(hstmt, 1, SQL_C_ULONG, &gamesData[0].gameId,
0,&gamesData[0].gameIdInd);

returnValue = SQLExecDirect(hstmt,(SQLWCHAR *)"SELECT game_id FROM
game",SQL_NTS);

switch (returnValue)
{
case SQL_SUCCESS:
break;
case SQL_SUCCESS_WITH_INFO:
break;
case SQL_ERROR:
SQLError
(henv1,hdbc,hstmt,SQLState,&nativeErrorCode,nativeErrorMessage,1000,&nativeE
rrorMessageLength);
break;
case SQL_INVALID_HANDLE:
SQLGetDiagRec
(SQL_HANDLE_DBC,henv1,1,SQLState,&nativeErrorCode,nativeErrorMessage,1000,&n
ativeErrorMessageLength);
break;
};


All works ok until the SQLExecDirect command. It returns an error: "The # of
binded parameters < the # of parameter markers".
From what I can see I have the parameters. I copied this from an example in
the doumentation so I can't think of anything I left out.


What have I done wrong?


Jonathan Blitz



From: muchan on 2 Jun 2006 06:36
Jonathan Blitz wrote:
> Sorry for posting this here but the database forums are totally dead.
> Maybe one or two posts a week or so.
>
> I am using VC++ 2005 and am trying to get to an ODBC database.
> This is my code:
>
> (snip)
> returnValue = SQLAllocStmt(hdbc,&hstmt);
>
> // Execute the SELECT statement.
> SQLUINTEGER NumRowsFetched;
> SQLUSMALLINT RowStatusArray[ROW_ARRAY_SIZE];
> SQLINTEGER rowArraySize = 10;
> typedef struct {SQLUINTEGER gameId; SQLINTEGER gameIdInd;} gameStruct;
> gameStruct gamesData[ROW_ARRAY_SIZE];
>
> returnValue = SQLSetStmtAttr(hstmt,
> SQL_ATTR_ROW_BIND_TYPE,(void*)sizeof(gameStruct), 0);
>
> returnValue = SQLSetStmtAttr(hstmt,
> SQL_ATTR_ROW_ARRAY_SIZE,(void*)rowArraySize, 0);
>
> returnValue = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_STATUS_PTR,
> RowStatusArray,0);
>
> returnValue = SQLSetStmtAttr(hstmt,
> SQL_ATTR_ROWS_FETCHED_PTR,&NumRowsFetched, 0);
>
> returnValue = SQLBindCol(hstmt, 1, SQL_C_ULONG, &gamesData[0].gameId,
> 0,&gamesData[0].gameIdInd);
>
> returnValue = SQLExecDirect(hstmt,(SQLWCHAR *)"SELECT game_id FROM
> game",SQL_NTS);
>
> (snip)
>
> All works ok until the SQLExecDirect command. It returns an error: "The # of
> binded parameters < the # of parameter markers".
> From what I can see I have the parameters. I copied this from an example in
> the doumentation so I can't think of anything I left out.
>
>
> What have I done wrong?
>


I can't tell what is wrong in your code, (I din't spent enough time
inspecting all the details), but I can tell the way I my code work.

After connection and get hstmt,

//=== in function SomeObjectSQL::Open() ===
if (SQLPrepare(hstmt, select_sql_statement, SQL_NTS) != SQL_SUCCESS) {
// ... error handling
}
if (SQLExecute(hstmt) != SQL_SUCCESS) {
// ... error handling
}

//SQLBindCol(hstmt, ...); // I bind the colam to a SQL object's members
// in your case:
SQLBindCol(hstmt, 1, SQL_C_ULONG, &gamesData[0].gameId, 0,&gamesData[0].gameIdInd);

//=== in another function SomeObjectSQL::Fetch(), for fetching multiple record... ===

RETCODE rc;

rc = SQLFetch(hstmt); // get the data to the binded members

if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO ) {
// here it's OK. do some work like trimming the string, etc.
} else {
// ... error handling
}

//=== after the loop of calling Fetch(), in function SomeObjectSQL::Close()

SQLTransact(link->henv, link->hdbc, SQL_COMMIT); // may not be neccessary
// esp, in nested fetching
SQLFreeStmt(hstmt, SQL_DROP);

//=================================================================

SomeObjectSQL obj; // Typically I have one more layer of POD object
// that uses this SQL object as implementation.
// but here I show direct usage of SQL object

if (obj.Open(where, order_by)) { // select statement is static inside, "where" and "order by"
while (obj.Fetch()) { // are often added dynamically
// .. do something with obj's data.
}
}
obj.Close();

Hope it helps

muchan
From: muchan on 2 Jun 2006 06:52
Jonathan Blitz wrote:
>
> returnValue = SQLBindCol(hstmt, 1, SQL_C_ULONG, &gamesData[0].gameId,
> 0,&gamesData[0].gameIdInd);
>
> returnValue = SQLExecDirect(hstmt,(SQLWCHAR *)"SELECT game_id FROM
> game",SQL_NTS);
>
>
> What have I done wrong?
>
> Jonathan Blitz
>

Shorter answer:
Microsoft's help gives a example of SQLBindCol :

#define NAME_LEN 50
#define PHONE_LEN 10

SQLCHAR szName[NAME_LEN], szPhone[PHONE_LEN];
SQLINTEGER sCustID, cbName, cbCustID, cbPhone;
SQLHSTMT hstmt;
SQLRETURN retcode;

retcode = SQLExecDirect(hstmt,
"SELECT CUSTID, NAME, 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 */

SQLBindCol(hstmt, 1, SQL_C_ULONG, &sCustID, 0, &cbCustID);
SQLBindCol(hstmt, 2, SQL_C_CHAR, szName, NAME_LEN, &cbName);
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. */

while (TRUE) {
retcode = SQLFetch(hstmt);
if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO) {
show_error();
}
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){
fprintf(out, "%-*s %-5d %*s", NAME_LEN-1, szName,
sCustID, PHONE_LEN-1, szPhone);
} else {
break;
}
}
}

So, you should call SQLBindCol after SQLExecDirect, and use SQLFetch to
retrieve the data.

muchan

 | 
Pages: 1
Prev: ModifyMenu issue
Next: CComBSTR vs string