Sign in to follow this  
Followers 0
Nithyanandam

SQL Query execution error Error

10 posts in this topic

#1 ·  Posted (edited)

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..

post-50561-12478573638805_thumb.gif

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()

post-50561-12478573638805_thumb.gif

Edited by Nithyanandam

Share this post


Link to post
Share on other sites



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...

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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---

post-50561-12478590551364_thumb.gif

Edited by Nithyanandam

Share this post


Link to post
Share on other sites

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()

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

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 by Nithyanandam

Share this post


Link to post
Share on other sites

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....

Share this post


Link to post
Share on other sites

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...

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0