Jump to content

Quering SQL Server with AutoIT


Recommended Posts

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

Link to comment
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.

Link to comment
Share on other sites

  • 2 weeks later...

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

Link to comment
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?

Link to comment
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

Link to comment
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.

Link to comment
Share on other sites

  • 1 year later...

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

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...