num1g8rfan Posted August 23, 2007 Share Posted August 23, 2007 I've been utilizing AutoIt for some time now and am ready to begin utilizing it for some basic SQL queries. While I have seen some posts that 'started' to help me, I wasn't able to get a basic function to work. Here's what I want to do: I want to query a database and have the results of that query display or be available for further manipulation. The examples I've seen don't "spell it out" for me and I'm afraid I'm new to utilizing objects within AutoIt. Any assistance would be greatly appreciated (Remember...The simpler the better ) Link to comment Share on other sites More sharing options...
sandman Posted August 23, 2007 Share Posted August 23, 2007 What kind of database is it? MySQL? PostgreSQL? [center]"Yes, [our app] runs on Windows as well as Linux, but if you had a Picasso painting, would you put it in the bathroom?" -BitchX.com (IRC client)"I would change the world, but they won't give me the source code." -Unknownsite . blog . portfolio . claimidcode.is.poetry();[/center] Link to comment Share on other sites More sharing options...
num1g8rfan Posted August 23, 2007 Author Share Posted August 23, 2007 Microsoft SQL Databases Link to comment Share on other sites More sharing options...
John117 Posted August 23, 2007 Share Posted August 23, 2007 I know SQLite -Not sure if that will help though . . . let me know Link to comment Share on other sites More sharing options...
num1g8rfan Posted August 23, 2007 Author Share Posted August 23, 2007 I will need to create an .exe that will connect to a Microsoft SQL Database. I'm unfamiliar with SQL Lite... Link to comment Share on other sites More sharing options...
weaponx Posted August 23, 2007 Share Posted August 23, 2007 I know SQLite -Not sure if that will help though . . . let me knowIsn't SQL Lite for creating virtual DB's on your local machine? Link to comment Share on other sites More sharing options...
sandman Posted August 24, 2007 Share Posted August 24, 2007 AFAIK nobody has created an interface for Microsoft SQL DB. If possible, you could switch to MySQL (recommended) or SQLite (blegh! just a bad DB IMO). If you use MySQL, cdkid wrote a UDF for that. If you use SQLite, there's UDFs embedded for it in the latest release of AutoIt. [center]"Yes, [our app] runs on Windows as well as Linux, but if you had a Picasso painting, would you put it in the bathroom?" -BitchX.com (IRC client)"I would change the world, but they won't give me the source code." -Unknownsite . blog . portfolio . claimidcode.is.poetry();[/center] Link to comment Share on other sites More sharing options...
PsaltyDS Posted August 24, 2007 Share Posted August 24, 2007 I think MS SQL server should answer ADODB queries like this as well as any other DB: $SqlSvr = "localhost" $oADODB = ObjCreate("ADODB.Connection") If IsObj($oADODB) Then $sQuery = "Provider=SQLOLEDB;Data Source=" & $SqlSvr & ";" & "Trusted_Connection=Yes;Initial Catalog=Master" $oADODB.Open($sQuery) $sQuery = "CREATE TABLE StaffTable (EmplName TEXT,Department TEXT,PhoneNumber TEXT)" $oADODB.Execute($sQuery) Else ConsoleWrite("Debug: Error creating ADODB.Connection object." & @LF) EndIf Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law Link to comment Share on other sites More sharing options...
weaponx Posted August 24, 2007 Share Posted August 24, 2007 Isn't there a Microsoft SQL UDF here: http://www.autoitscript.com/forum/index.ph...&hl=sql.au3 ??? Link to comment Share on other sites More sharing options...
ChrisL Posted August 24, 2007 Share Posted August 24, 2007 I've been trying to get my head round this too. Here is my effort http://www.autoitscript.com/forum/index.php?showtopic=51952 [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...
num1g8rfan Posted August 24, 2007 Author Share Posted August 24, 2007 I'M SO CLOSE! ChrisL - Thanks for your contribution. I have a valid connection and can tell that the results exist, but I need some way to parse the through the results. I created a single query after the _SQLConnect call: $data = _SQLExecute(-1,"SELECT Username AS Expr1, Name AS Expr2 FROM Entity WHERE (Name = 'John Smith')") If Not @error then $aData = _SQLGetData2D($data) _ArrayDisplay($aData,"Display Array") Else Msgbox(0,"",$SQLErr) EndIf The script appears to be having a problem when the _ArrayDisplay function is calling out to the array include: C:\PROGRA~1\AutoIt3\Include\array.au3 (175) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.: Any ideas? Link to comment Share on other sites More sharing options...
ChrisL Posted August 24, 2007 Share Posted August 24, 2007 (edited) I'M SO CLOSE! ChrisL - Thanks for your contribution. I have a valid connection and can tell that the results exist, but I need some way to parse the through the results.I created a single query after the _SQLConnect call: $data = _SQLExecute(-1,"SELECT Username AS Expr1, Name AS Expr2 FROM Entity WHERE (Name = 'John Smith')")If Not @error then $aData = _SQLGetData2D($data) _ArrayDisplay($aData,"Display Array") Else Msgbox(0,"",$SQLErr)EndIfThe script appears to be having a problem when the _ArrayDisplay function is calling out to the array include: C:\PROGRA~1\AutoIt3\Include\array.au3 (175) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.: Any ideas?Which version of autoit are you using?I'm using 3.2.4.9, this version supports 2D arrays and older versions only supported 1D arrays in _arrayDisplay()Edit: Also if you go back to my example in the other forum I have added a function to output to a deliminated string Edited August 24, 2007 by ChrisL [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...
num1g8rfan Posted August 24, 2007 Author Share Posted August 24, 2007 I'm so excited, I could spit! Still having a problem though...My expected results were "John" (UserName) and "John Smith". Each having a header of "Expr1" and "Expr2" respectively. I only received "Expr2 John Smith". Why do you suppose? Link to comment Share on other sites More sharing options...
ChrisL Posted August 24, 2007 Share Posted August 24, 2007 Sorry I don't know I'm a bit of a noob when it comes to SQL. [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...
ChrisL Posted August 24, 2007 Share Posted August 24, 2007 Fixed it!! I was counting from 1 and I needed to count from 0 go back to my example post and redownload the _sql.au3 [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...
ChrisL Posted August 24, 2007 Share Posted August 24, 2007 Come on.. tell me it works for you then I can go and get a celebratory glass of Pepsi Max [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...
num1g8rfan Posted August 27, 2007 Author Share Posted August 27, 2007 Sorry for getting back so late. I had already debugged it and forgot to tell you. Thank you so much for your assistance. Link to comment Share on other sites More sharing options...
painn3ck Posted August 28, 2007 Share Posted August 28, 2007 Fixed it!! I was counting from 1 and I needed to count from 0 go back to my example post and redownload the _sql.au3 BTW - I'll buy next round for both of you! Perfect timing for a script I am working on. Everything works (it pulls and displays the data) but after it displays the data as a string I get this error: _sql.au3 (90) : ==> Array variable subscript badly formatted.: Redim $ret[1][.Fields.Count] Redim $ret[1][^ ERROR Anyone else getting this? Ideas? Link to comment Share on other sites More sharing options...
ChrisL Posted August 28, 2007 Share Posted August 28, 2007 BTW - I'll buy next round for both of you! Perfect timing for a script I am working on. Everything works (it pulls and displays the data) but after it displays the data as a string I get this error: _sql.au3 (90) : ==> Array variable subscript badly formatted.: Redim $ret[1][.Fields.Count] Redim $ret[1][^ ERROR Anyone else getting this? Ideas? What version of Autoit are you using? and show us the code you are using, because that error is from the _SQLGetData2D() function not the _SQLGetDataAsString() function [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...
painn3ck Posted August 29, 2007 Share Posted August 29, 2007 (edited) Nevermind (doh)!! Forgot to remove some extra junk code. (amazing what a couple hours of sleep will do) Works perfectly! Thanks again! Edited August 29, 2007 by painn3ck 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