SQL Query execution error Error

[Nithyanandam]

Hi Guys,

when I use this SQL connection and execute a query to fetch data, 90% it works fine, but some times It errors out saying -1.... and the hard part of this is it closes the app too. I tried to catch the error to handle it. Please help me to either catch the error or over come it once for all..

Here assume that the where clause is correct and when I hit the search buttom in my application, most of the time it works perfect, but some times out of no reason it errors out and closes, even those when I hit the search button with the same condition.

The reason from console says as -

C:\Program Files\AutoIt3\Include\_sql.au3 (262) : ==> Variable must be of type "Object".:

$hQuery = $hConHandle.Execute($vQuery)

$hQuery = $hConHandle^ ERROR

Code -

$QueryReportMain = " Select TOP(100) * from ExecutionControl_Tbl where id in (select distinct(id) from ExecutionScripts_Tbl " & $WhereClause & " ) order by id;"
    $QueryReportDetail = "Select TOP(100) Id,Application,Area,Environment,Iterations,Passed,Warnings,Failed,StartTime,EndTime,ExecStatus,ExecBy,ExecTime from ExecutionScripts_Tbl " & $WhereClause & " order by AutoId;"
    _SQL_Startup()
    $forSQLConnect = _sql_Connect(-1, "DBServer1$", "AutomationDB1$", "Auto_User", "autouser")
    $iRval = _SQL_GetTable2D(-1, $QueryReportMain, $aRowMain, $iRowsMain, $iColumnsMain)
    $iRval = _SQL_GetTable2D(-1, $QueryReportDetail, $aRowDetail, $iRowsDetail, $iColumnsDetail)

    If _SQL_GetTable2D(-1, $QueryReportMain, $aRowMain, $iRowsMain, $iColumnsMain) = 0 Or _SQL_GetTable2D(-1, $QueryReportMain, $aRowMain, $iRowsMain, $iColumnsMain) = 0 Then
        $ArrayMainForExcel = $aRowMain
        $ArrayDetailForExcel = $aRowDetail
        _ArrayDelete($aRowMain, 0)
        _ArrayDelete($aRowDetail, 0)

        _GUICtrlListView_DeleteAllItems(GUICtrlGetHandle($ListMain))
        _GUICtrlListView_AddArray($ListMain, $aRowMain)

        _GUICtrlListView_DeleteAllItems(GUICtrlGetHandle($ListDetail))
        _GUICtrlListView_AddArray($ListDetail, $aRowDetail)
    Else
        MsgBox(64, "SQL Error : " & $SQL_ERROR, "No data available as per the search condition, Please try again.")
        _GUICtrlListView_DeleteAllItems(GUICtrlGetHandle($ListMain))
        _GUICtrlListView_DeleteAllItems(GUICtrlGetHandle($ListDetail))
    EndIf

    _SQL_Close()

Edited July 17, 2009 by Nithyanandam

[ChrisL]

I assume your using my udf for this?

Anyhow after _SQL_Startup()

put _SQL_RegisterErrorHandler()

and that should trap the error

[Nithyanandam]

I assume your using my udf for this?

Anyhow after _SQL_Startup()

put _SQL_RegisterErrorHandler()

and that should trap the error

Yes, I am using your UDF... :-)

Happy to talk to you... Will try you suggestion and let u know...

[Nithyanandam]

I got this error now...

What I did is, I kept on hitting the Search button, as if I am doing Stress testing....

C:\Program Files\AutoIt3\Include\_sql.au3 (262) : ==> Error in expression.:

$hQuery = $hConHandle.Execute($vQuery)

$hQuery = ^ ERROR

Console error image---

Edited July 17, 2009 by Nithyanandam

[ChrisL]

You say you keep hitting the search button this implies that it's part of some larger code.

I would say that your hitting the search button while there is still a query going on. Are you using OnEventMode in your Gui?

It would seem that there is a query that is trying to run before _SQL_Startup() or without an object connection handle.

Do you keep using _Sql_Startup() and _Sql_Close() over and over?

Can you _SQL_Startup() at the beginning of the script and then register the error handler.

Then at the end of the whole script use _Sql_Close()

[Nithyanandam]

You say you keep hitting the search button this implies that it's part of some larger code.

I would say that your hitting the search button while there is still a query going on. Are you using OnEventMode in your Gui?

It would seem that there is a query that is trying to run before _SQL_Startup() or without an object connection handle.

Do you keep using _Sql_Startup() and _Sql_Close() over and over?

Can you _SQL_Startup() at the beginning of the script and then register the error handler.

Then at the end of the whole script use _Sql_Close()

I am using _SQL_Startup() for every function and closeing it inside that function as soon as I finished using it, and at one particular time only one query would be fired, since I am using Opt("GUIOnEventMode", 1). And the time taken to get the results is just a fraction.

Ok, then I have a 2500 line code, Is it ok to open a SQL link in the first and then close it while the application is closed.

Edited July 17, 2009 by Nithyanandam

[ChrisL]

Give it a go, But it does sound like your running a query when the connection is closed.

[Nithyanandam]

Cool, I am not comming accross any error till now after I made those changes...

My only question now is Is it ok to have the connect ON all the time....

Is it a good practise....

[ChrisL]

OK, try just leaving _SQL_Startup() at the top with

_SQL_RegisterErrorHandler()

Put _Sql_Connect() and _SQL_Close() back where they were in the function

[Nithyanandam]

OK, try just leaving _SQL_Startup() at the top with

_SQL_RegisterErrorHandler()

Put _Sql_Connect() and _SQL_Close() back where they were in the function

In case of two functions using the same connection then, if we close one in a Search function, then we wont have a connection at all after that...