MySQL Forums
Forum List  »  MySQL Query Browser

with ODBC V5.01.07 SQLFetch retrieves only one row
Posted by: Birgit Blume
Date: September 22, 2010 04:27AM

Hi,

I've got a problem. With ODBC driver version 3.51.27 everything works fine. But if I use the recent ODBC driver version. SQLFetch stops after the first row. The second SQLFetch call returns SQL_NO_DATA although there are four more rows in my table.
I'm using a public MySQL sample database named "sugarCRM" which is available via internet.
Please notice that in version 5.1 SQLDescribeColumn returns a value of '-9' for my varchar columns. In Version 3.51 it's '12'. SQLGetTypeInfo doesn't return a value '-9'. I think that's strange.

Here's my C++ code:

Calling program:
...
DWORD timeSpan = 0;
// MySQL Driver V3.51
DoSomething ("DSN=sugarcrm1", "SELECT * from test", timeSpan);
// MySQL Driver V5.01
// selects only the first of five rows
// SQL_NO_DATA after thre first row
DoSomething ("DSN=sugarcrm", "SELECT * from test", timeSpan);
...


bool DoSomething (CString connectString, CString sqlCmd, DWORD &timeSpan)
{
SQLRETURN rc;
SQLHENV henv = SQL_NULL_HENV;
SQLHDBC hdbc = SQL_NULL_HDBC;
SQLHSTMT hstmt = SQL_NULL_HSTMT;
SQLCHAR completedConString[1025];
SQLSMALLINT stringSize = 0;

rc = SQLDriverConnect (hdbc, NULL, (SQLCHAR*) (LPCSTR) connectString, ...);
bool DBValid = false;
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
printf ("SQLDriverConnect error: %s\n", ...);
return false;
}

...
// Anzahl der Ergebnisspalten bestimmen !!
rc = SQLNumResultCols (hstmt, &colCount);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
printf ("SQLNumResultCols error: %s", ...);
return false;
}

for (int i = 1; i <= colCount; i++)
{
rc = SQLDescribeCol (hstmt, i, colName, 254, &len, &type, &colSize, &decimalDigits, &nullable);
// nur einmal bei Programmstart zur Beschreibung aller benötigten Spalten auslesen
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
printf ("SQLDescribeCol error: %s", ODBCErrorInfo (SQL_NULL_HENV, SQL_NULL_HDBC, hstmt));
return false;
}
else
printf ("%s: Type: %d Len %d\r\n", colName, type, len);
}

// Variablen zur Aufnahme der Ergebnisspalten definieren
//SQLCHAR firstName[2048], lastName[2048], id[10];
//CString firstName, lastName, id;
vector<char*> result;
SQLINTEGER lenInfo;// = SQL_NTS;
#if 1
for (int ii = 1; ii <= colCount; ii++)
{
char *resultItem = new char[1025];
SQLINTEGER *resultLenItem = new SQLINTEGER;
memset (resultItem, 0, sizeof (1024));
*resultLenItem = SQL_NTS;
result.push_back (resultItem);

rc = SQLBindCol (hstmt, ii, SQL_C_CHAR, (SQLCHAR*) (LPCSTR) result[ii-1], 1024, &lenInfo);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
printf ("SQLBindCol error: %s", ODBCErrorInfo (SQL_NULL_HENV, SQL_NULL_HDBC, hstmt));
return false;
}
lenInfo = SQL_NTS;
}
#endif
rc = SQLExecute (hstmt);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
printf ("SQLExecute error: %s", ODBCErrorInfo (SQL_NULL_HENV, SQL_NULL_HDBC, hstmt));
return false;
}

// SQLRowCount => Update/Insert => != -1
/*
SQLLEN rowCount = 0;
rc = SQLRowCount (hstmt, &rowCount);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
printf ("SQLExecute error: %s", ODBCErrorInfo (SQL_NULL_HENV, SQL_NULL_HDBC, hstmt));
*/
rc = SQLNumResultCols (hstmt, &colCount);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
printf ("SQLExecute error: %s", ODBCErrorInfo (SQL_NULL_HENV, SQL_NULL_HDBC, hstmt));

while (true)
{
rc = SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 2);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO && rc != SQL_NO_DATA)
{
printf ("SQLFetch error: %s", ODBCErrorInfo (SQL_NULL_HENV, SQL_NULL_HDBC, hstmt));
return false;
}
else if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
{
CString ergebnis;

for (unsigned i = 0; i < result.size (); i++)
{
ergebnis.Append (result);
ergebnis.Append (" ");
}

ergebnis.Append ("\r\n");
printf ("%s", ergebnis);
}
else
{
rc = SQLMoreResults (hstmt);
if (rc == SQL_SUCCESS) // => weitere Daten vorhanden
;
else if (rc == SQL_NO_DATA)
break;
else
printf ("SQLMoreRows error: %s", ODBCErrorInfo (SQL_NULL_HENV, SQL_NULL_HDBC, hstmt));
}
}

timeSpan = GetTickCount () - start;
// alles freigeben
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
return true;
}

Options: ReplyQuote


Subject
Written By
Posted
with ODBC V5.01.07 SQLFetch retrieves only one row
September 22, 2010 04:27AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.