why exit when call SQLFetch? 2

[smilife]

code:
rc = SQLFreeStmt(odbcHandle.hStmt, SQL_CLOSE);
rc = SQLExecDirect(odbcHandle.hStmt, (unsigned char*) sqlCommand,SQL_NTS);
if(HasSqlError(odbcHandle, rc, ExecDirect, sqlCommand))
{
DebugMsg("SQLExecDirect fail when get all vlan record"
return false;
}
for(rc=SQLFetch(odbcHandle.hStmt);
rc==SQL_SUCCESS;
rc=SQLFetch(odbcHandle.hStmt))
{
}
....


when after some SQLFetch, the next call to SQLFETCH let function exit, but not enter the code after for, of course,there isnt any return statement in for circle.
why?
how to resolve?

 

[teriviret]

Some possibilities:

* An exception was thrown, caught by a function further up the stack. Exceptions can cause a return from a function if the function does not catch it.

* setjmp/longjmp was used. Calling "longjmp" is basically a non-local "goto" which basically breaks all the way out and returns to the point where "setjmp" was called.

 

[smilife]

Thanks for teriviret!
I think it's because of exception too.
But how to resolve this problem?

 

[Cagliostro]

In my opinion you should not exec direct there. You should been having there unbound columns.
Instead of SQLExecDirect / SQLFetch you should use

SQLPrepare/SQLBindColumn/SQLBindColumn.../SQLExec (not SQLExecDirect)/SQLFetch

SQLExecDirect is used usualy for create/alter/drop/delete/update statements

Ion Filipski

 

[smilife]

IonFilipski,Thank you!

I have try your idea using SQLExecute instead of SQLExecDirect, but the problem is still there: after my second SQLExecute, in my SQLFetch circle, I cannt fetch all record, after i fetch the nth record(yestoday n is 9, today it's 37), the (n+1) fetch cannt step out--the thread do not enter the code after 'for' and control be returned to Main Window but application can do nothing, and i can not successfully query all record in SQL Server whatever way i use.

why?

 

[Cagliostro]

This is a short sample of using ODBC:

//using ODBC
#include<windows.h>
#include<iostream>
#include<string>
#include<sql.h>
#include<sqlext.h>
using namespace std;
int main()
{
    HENV hEnv;
    HDBC hDbc;
    RETCODE rc;
    int iOut;
    char strOut[256];
    char szDSN[256] = &quot;driver={Microsoft Access Driver (*.mdb)};dbq=[c:\\db1.mdb];&quot;;
    //dsn samples:
    //&quot;driver={Microsoft Access Driver (*.mdb)};dbq=[f:\\db1.mdb];&quot;
    //&quot;driver={SQL Server};pwd={password there};Server={server name};Database={dbname there}&quot;
    //driver names for different databases:
    //{SQL Server}
    //{Microsoft ODBC for Oracle}
    //{Oracle in oracle9}
    //{Microsoft Access Driver (*.mdb)}
    //{MySQL ODBC 3.51 Driver}

    char* szSql = &quot;select * from table1&quot;;
    rc = SQLAllocEnv(&hEnv);
    rc = SQLAllocConnect(hEnv, &hDbc);

    rc = SQLDriverConnect(hDbc, NULL, (unsigned char*)szDSN, 
        SQL_NTS, (unsigned char*)strOut, 
        255, (SQLSMALLINT*)&iOut, SQL_DRIVER_NOPROMPT);
    {
        int ival;
        char chval[128];
        int ret1;
        int ret2;
        HSTMT hStmt;
        rc = SQLAllocStmt(hDbc,&hStmt);
        rc = SQLPrepare(hStmt,(unsigned char*)szSql, SQL_NTS);//1
        //rc = SQLBindCol(hStmt, tab_column, tr_type, tr_value, tr_len, len_or_ind);
        rc = SQLBindCol(hStmt, 1, SQL_C_ULONG, &ival, 4, (SQLINTEGER*)& ret1);
        rc = SQLBindCol(hStmt, 2, SQL_C_CHAR, chval, 128, (SQLINTEGER*)&ret2);
        rc = SQLExecute(hStmt); //2
        //if you have queries like drop/create/many update... 
        //instead of //1 //2 and //3 you could use
        //rc=SQLExecDirectA(hStmt,(unsigned char*)szSql,SQL_NTS);
        cout<< &quot;>table:&quot;<< endl;
        while(1) //3
        {
            rc = SQLFetch(hStmt);
            if(rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)break;
            cout<< &quot;{&quot;<< ival<<&quot;}{&quot;<< chval<< &quot;}&quot;<< endl;
        }
        rc=SQLFreeStmt(hStmt, SQL_DROP);
    }
    rc = SQLDisconnect(hDbc);
    rc = SQLFreeEnv(hEnv);
    return 0;
}

Ion Filipski

 

[smilife]

Thank you for your great patientness.
But i know how odbc works, my problem is why the SQLFetch cannt step out, what's the reason, how to resolve it?
perhaps you dont know my real mean, please see my follow code:
bool Proc_SetVlanTB(ODBC_Handle& vOdbcHandle, UINT_32_T vNeID,SetVlanReq_Para* vpParam)
{
char sqlCommand[1024], VlanName[32];
ODBC_Handle odbcHandle = {mm_ReadOdbcHandle.hDbc, 0};
RETCODE rc;
bool ret;
bool VlanExist = false, PortExist = false, PortNoIncluded = false;
int i, VlanId, RowStatus, PortMask;

rc = SQLAllocStmt(odbcHandle.hDbc,&odbcHandle.hStmt);
if(rc == SQL_ERROR)
{
return false;
}
/* if is modify operation */
if (vpParam->vlanStaticRowStatus == 1)
{
wsprintf(sqlCommand,
"select * from %s%03d.dbo.adsl_VlanTable",
DB_PREFIX, vNeID);
rc = SQLPrepare(odbcHandle.hStmt, (unsigned char *)sqlCommand,SQL_NTS);
rc = SQLExecute(odbcHandle.hStmt);

/*rc = SQLExecDirect(odbcHandle.hStmt, (unsigned char*)sqlCommand,SQL_NTS);/**/
if(HasSqlError(odbcHandle, rc, ExecDirect, sqlCommand))
{
DebugMsg("SQLExecDirect fail when get all vlan record"
return false;
}
else
{
rc = SQLBindCol(odbcHandle.hStmt, 1, SQL_C_ULONG, &VlanId, 0,&cbVlanId);
rc = SQLBindCol(odbcHandle.hStmt, 2, SQL_C_CHAR, VlanName, 32,&cbVlanName);
rc = SQLBindCol(odbcHandle.hStmt, 3, SQL_C_BINARY, EgressPorts, 140, &cbEgressPorts);
rc = SQLBindCol(odbcHandle.hStmt, 4, SQL_C_BINARY, &ForbiddenPorts,1, &cbForbiddenPorts);
rc = SQLBindCol(odbcHandle.hStmt, 5, SQL_C_BINARY, UntaggedPorts, 140, &cbUntaggedPorts);
rc = SQLBindCol(odbcHandle.hStmt, 6, SQL_C_ULONG, &RowStatus, 0,&cbRowStatus);
for(rc=SQLFetch(odbcHandle.hStmt);
rc==SQL_SUCCESS; rc=SQLFetch(odbcHandle.hStmt))
{
/*here do my first analyse, no problem*/
}

if (VlanExist)
{
rc = SQLFreeStmt(odbcHandle.hStmt, SQL_CLOSE);
rc = SQLExecute(odbcHandle.hStmt);
for(rc=SQLFetch(odbcHandle.hStmt); rc==SQL_SUCCESS; rc=SQLFetch(odbcHandle.hStmt))
{
/*here do my second analyse, the n/recordcount SQLFetch cannt step out, program doesnt enter the code after this for, control return to main window but can do nothing*/
}

 

[Cagliostro]

I can not understand this piece of code:

rc = SQLFreeStmt(odbcHandle.hStmt, SQL_CLOSE);
rc = SQLExecute(odbcHandle.hStmt);
for(rc=SQLFetch(odbcHandle.hStmt); rc==SQL_SUCCESS; rc=SQLFetch(odbcHandle.hStmt))


Why do you free the statement before executing it?

Ion Filipski

 

[smilife]

To execute a SELECT statement more than once, the application must call SQLCloseCursor before reexecuting the SELECT statement.
SQLFreeStmt with option SQL_CLOSE is equal to SQLCloseCursor.

 

[teriviret]

Well, not exactly. Read one more line down in the ODBC Programmer's Reference. Here's what it says:

[tt]Calling SQLFreeStmt with the SQL_CLOSE option is equivalent to calling SQLCloseCursor, with the exception that SQLFreeStmt with SQL_CLOSE has no effect on the application if no cursor is open on the statement. If no cursor is open, a call to SQLCloseCursor returns SQLSTATE 24000 (Invalid cursor state).

An application should not use a statement handle after it has been freed; the Driver Manager does not check the validity of a handle in a function call.[/tt]

IonFilipski has a point - it's not good that you're using the statement right after freeing it.

 

[smilife]

To teriviret
I think it should have no effect. SQLFreeStmt with SQL_CLOSE just close the cursor on last record set.

I tried free the hDbc, and realloc, it works.

 

[Cagliostro]

SQLFreeStmt with SQL_CLOSE just close the cursor on last record set means it closes the last statement. So, you can not use anymore this statement for any operations and you should allocate with all alloc/bind/exec it again.

Ion Filipski

 

[smilife]

It's from ODBC Programming perferance:

To execute a SELECT statement more than once, the application must call SQLCloseCursor before reexecuting the SELECT statement.

So i think it's not necessary to free the Stmt.

 

[Cagliostro]

Just a copy/paste from MSDN:
SQL_ CLOSE: Closes the cursor associated with StatementHandle (if one was defined) and discards all pending results. The application can reopen this cursor later by executing a SELECT statement again with the same or different parameter values.

Executing means SQLExecute again.

If your statement returns many than a single cursor, you should use SQLMoreResults instead of SQLFreeStmt.

Ion Filipski

 

[smilife]

My SQL is

"select * from table"

Should the Cursor be a single cursor?

 

[Cagliostro]

Yes, and after SQL_CLOSE it you should SQLExecute it again. When you don't use SQL_DROP, you can SQLExecute it again without a new SQLAlloc/SQLPrepare.

Ion Filipski

 

[smilife]

I see.

But the problem above doesnt appear again after I realloc a hdbc.