Re: Database ODBC problem

From:

muchan <muchan@promikra.si>

Newsgroups:

microsoft.public.vc.language

Date:

Fri, 02 Jun 2006 12:36:46 +0200

Message-ID:

<eGKs2BjhGHA.1000@TK2MSFTNGP04.phx.gbl>

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