nevodj Posted April 29, 2011 Share Posted April 29, 2011 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 More sharing options...
bwochinski Posted April 29, 2011 Share Posted April 29, 2011 (edited) I wrote a script to query an MS SQL server a couple years ago and had good success using the Edited April 29, 2011 by bwochinski Link to comment Share on other sites More sharing options...
nevodj Posted April 29, 2011 Author Share Posted April 29, 2011 scary looking script... thought it would be easier :| Link to comment Share on other sites More sharing options...
bwochinski Posted April 29, 2011 Share Posted April 29, 2011 Oh it's not so bad 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 More sharing options...
nevodj Posted May 9, 2011 Author Share Posted May 9, 2011 Thanks heaps, got it going after a bit of stuffing around! works well. Link to comment Share on other sites More sharing options...
nevodj Posted May 9, 2011 Author Share Posted May 9, 2011 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 More sharing options...
ChrisL Posted May 9, 2011 Share Posted May 9, 2011 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? [u]Scripts[/u]Minimize gui to systray _ Fail safe source recoveryMsgbox UDF _ _procwatch() Stop your app from being closedLicensed/Trial software system _ Buffering Hotkeys_SQL.au3 ADODB.Connection _ Search 2d Arrays_SplashTextWithGraphicOn() _ Adjust Screen GammaTransparent Controls _ Eventlogs without the crap_GuiCtrlCreateFlash() _ Simple Interscript communication[u]Websites[/u]Curious Campers VW Hightops Lambert Plant Hire Link to comment Share on other sites More sharing options...
nevodj Posted May 10, 2011 Author Share Posted May 10, 2011 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 More sharing options...
nevodj Posted May 10, 2011 Author Share Posted May 10, 2011 Don't worry, i've got it! Link to comment Share on other sites More sharing options...
ChrisL Posted May 10, 2011 Share Posted May 10, 2011 Don't worry, i've got it!Go on then.... tell us what it was. It might help somebody in the future who has the same problem! [u]Scripts[/u]Minimize gui to systray _ Fail safe source recoveryMsgbox UDF _ _procwatch() Stop your app from being closedLicensed/Trial software system _ Buffering Hotkeys_SQL.au3 ADODB.Connection _ Search 2d Arrays_SplashTextWithGraphicOn() _ Adjust Screen GammaTransparent Controls _ Eventlogs without the crap_GuiCtrlCreateFlash() _ Simple Interscript communication[u]Websites[/u]Curious Campers VW Hightops Lambert Plant Hire Link to comment Share on other sites More sharing options...
nevodj Posted May 11, 2011 Author Share Posted May 11, 2011 nothing to do with AutoIT.... the user account on the server didnt have permission to iexplore.exe! Link to comment Share on other sites More sharing options...
Zedna Posted May 11, 2011 Share Posted May 11, 2011 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. Resources UDF ResourcesEx UDF AutoIt Forum Search Link to comment Share on other sites More sharing options...
OrthodoxyUSA Posted May 9, 2013 Share Posted May 9, 2013 Local $vString, $seqnoIf _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 More sharing options...
annaharris Posted May 9, 2013 Share Posted May 9, 2013 The code that you have mentioned looks quite weird and I am unable to understand it. Please share it in a precise manner. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now