Sign in to follow this  
Followers 0
nevodj

Quering SQL Server with AutoIT

14 posts in this topic

Hello,

I need to execute a simple SQL query on a Microsoft SQL Server 08 database using AutoIT.

Can this be done with SQLite?

I got it to work using SQLCMD.exe, the microsft SQL command line utility, but it wont work on machines that don't have SQL Client installed. I don't want to have to install SQL Server on every machine, so i wondered if i could get this query to work using SQLite or something else.

This is my code at the moment:

Local $foo = Run("sqlcmd -S termserver -U sa -P sa -d Database" , @SystemDir, @SW_HIDE, $STDIN_CHILD + $STDOUT_CHILD)

StdinWrite ($foo, "SELECT X_TRACEURL FROM SALESORD_HDR WHERE SEQNO = " & $seqno & "" & @CRLF & "GO")

StdinWrite($foo)

I was wondering whether there is a simple way to execute this query without using the sqlcmd.exe tool, as it requires SQL Server to be installed on every machine.

Be glad if anyone can help me.

thanks

nevodj

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

I wrote a script to query an MS SQL server a couple years ago and had good success using the

Edited by bwochinski

Share this post


Link to post
Share on other sites

scary looking script... thought it would be easier :|

Share this post


Link to post
Share on other sites

Oh it's not so bad :unsure:

Mostly the examples in the first post there are just demonstrating all the different ways you can return the results from the database. You can get one row at a time, or return the result set as an array, or as a string, or only a single row. The error checking all throughout makes it look a bit more intimidating as well.

As an example, to connect, run the query you listed above, and return it as a string, the below should work:

#include <_sql.au3>

_SQL_RegisterErrorHandler();register the error handler to prevent hard crash on COM error

$oADODB = _SQL_Startup()

If $oADODB = $SQL_ERROR Then
    Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())
EndIf

If _sql_Connect($oADODB,"Termserver","Database","sa","sa") = $SQL_ERROR then
    Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())
    _SQL_Close()
    Exit
EndIf

Local $vString
If _Sql_GetTableAsString($oADODB,"SELECT X_TRACEURL FROM SALESORD_HDR WHERE SEQNO = " & $seqno & ";",$vString) = $SQL_OK then
    Msgbox(0,"Data as a String",$vString)
Else
    Msgbox(0 + 16 +262144,"SQL Error",_SQL_GetErrMsg() )
EndIf

_SQL_Close($oADODB)

And mostly to prove the point, removing all the error checking makes it a bit easier to read:

#include <_sql.au3>

Local $vString
_SQL_RegisterErrorHandler() ;register the error handler to prevent hard crash on COM error

$oADODB = _SQL_Startup()
_sql_Connect($oADODB,"Termserver","Database","sa","sa")

_Sql_GetTableAsString($oADODB,"SELECT X_TRACEURL FROM SALESORD_HDR WHERE SEQNO = " & $seqno & ";",$vString)
Msgbox(0,"Data as a String",$vString)

_SQL_Close($oADODB)

FYI, I haven't tested any of this, but it's pretty much straight from the examples.

Share this post


Link to post
Share on other sites

Thanks heaps, got it going after a bit of stuffing around! works well.

Share this post


Link to post
Share on other sites

Hey,

One more question on this, when i run it on a local workstation, it talks back to the server and executes the query fine.

When i try to run it on the actual server 'termserver' which my SQL is running on, it's getting hung up for some reason?

I tried adding this to my script but it didnt make any difference:

If @ComputerName = "TERMSERVER" Then

$server = "localhost"

Else

$server = "termserver"

EndIf

Got any ideas why it wouldnt be executing?

thanks

James

Share this post


Link to post
Share on other sites

While bwochinski pointed out you can run without error checking, it's there for a reason, so make sure your using error checking.

If _sql_Connect($oADODB,"Termserver","Database","sa","sa") = $SQL_ERROR then

Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())

_SQL_Close()

Exit

EndIf

Also, what happens if you run the script locally on Termserver?

Share this post


Link to post
Share on other sites

Hello ChrisL

I am using error checking, but not getting any errors, the sript simply jams.

What i'm wondering, is there other exe's it wraps into the main .exe when i compile it?

I mean, does the script call any other exe's, is it dependant on any other programs when it executes the query?

Because when i try to run it on the administrator account on termserver, it runs fine, but when i run it on a user account it doesnt.

Our server has policy restrictions on execuatables, and each executable has to be added to the policy before it can be run by a user.

So if it's calling another executable that can't run, that could be the problem.

Is this the case?

Cheers

James

Share this post


Link to post
Share on other sites

Don't worry, i've got it!

Share this post


Link to post
Share on other sites

nothing to do with AutoIT.... the user account on the server didnt have permission to iexplore.exe! :unsure:

Share this post


Link to post
Share on other sites

What i'm wondering, is there other exe's it wraps into the main .exe when i compile it?

I mean, does the script call any other exe's, is it dependant on any other programs when it executes the query?

This UDF uses ADO DB which is standard part of Windows. In fact it is DLL file(s) registered also in system registry.

Share this post


Link to post
Share on other sites

#13 ·  Posted

Local $vString, $seqno

If _Sql_GetTableAsString($oADODB,"SELECT X_TRACEURL FROM SALESORD_HDR WHERE SEQNO = & $seqno & ";",$vString) = $SQL_OK then

Msgbox(0,"Data as a String",$vString)

Else

Msgbox(0 + 16 +262144,"SQL Error",_SQL_GetErrMsg() )

EndIf

Share this post


Link to post
Share on other sites

#14 ·  Posted

The code that you have mentioned looks quite weird and I am unable to understand it. Please share it in a precise manner.

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