sql server odbc: binding input parameters leads to SQL

binding input parameters leads to SQL_ERROR

ScottD
10/4/2004 1:35:04 PM

sql server odbc: I am trying to read the results from a query like

select a, b
from mytbl
where col0 = ?

The first time I run SQLExecute, everything works fine, but when I call
SQLExecute a second time with a modified input parameter, it returns
SQL_ERROR. Below is the code I'm running with the error checking removed and
the names simplified.

SQLAllocHandle(SQL_HANDLE_STMT, hconn, &m_hstmt)) ;
SQLPrepare(m_hstmt, (UCHAR *)m_query.GetBuffer(), m_query.GetLength()));
SQLBindParameter(m_hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0,
&where_param, 0, 0);
SQLBindCol(m_hstmt, 1, SQL_C_BIT, addr1, 0, 0)
SQLBindCol(m_hstmt, 2, SQL_C_BIT, addr2, 0, 0)

SQLExecute(m_hstmt) //works ok
SQLFetchScroll(m_hstmt, SQL_FETCH_NEXT, 0)

where_param = 10

SQLExecute(m_hstmt) //fails
SQLFetchScroll(m_hstmt, SQL_FETCH_NEXT, 0)

My goal is to run execute many times with a variety of parameters. I was
trying to have the odbc driver read from the address specified in
SQLBindParameter to set the value of the input parameter. I'm further
confused by the problem because when I try to retrieve error information by
calling SQLGetDiagRec, nothing is returned. Thanks for your help.

RE: binding input parameters leads to SQL_ERROR

ScottD
10/4/2004 2:19:03 PM

The problem goes away when I call SQLCloseCursor after completing the fetch.
The MSDN SQLBindParameter documentation at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsqlbindparameter.asp
gives no indication that SQLCloseCursor should be needed.

I suppose that I am now just concerned that I am introducing extra
processing overhead by calling SQLCloseCursor.

Scott

[quoted text, click to view]

Re: binding input parameters leads to SQL_ERROR

brannonj NO[at]SPAM gmail.com
10/5/2004 12:58:33 PM

Are you making sure that you've fetch all of the rows? Even if only
one row is returned by the query, you should call SQLFetchXXX() until
it returns SQL_NO_DATA. Otherwise the driver doesn't necessarily know
that you've finished fetching rows on the current resultset/cursor.
Alternatively, you can use SQLMoreResults() to flush the remaining data
on the wire and position on the next resultset (if there is one,
otherwise it cleans up the connection).

SQLCloseCursor shouldn't add too much overhead. I'm assuming you're
using the default (firehose) cursor. If so, SQLCloseCursor just makes
sure that all of the data from the previous statement has been
consumed, and the connection is ready for the next statement.

Brannon

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server odbc group.