Basic SQL Query in AutoIt

[num1g8rfan]

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 )

[sandman]

What kind of database is it? MySQL? PostgreSQL?

[num1g8rfan]

Microsoft SQL Databases

[John117]

I know SQLite -Not sure if that will help though . . . let me know

[num1g8rfan]

I will need to create an .exe that will connect to a Microsoft SQL Database. I'm unfamiliar with SQL Lite...

[weaponx]

I know SQLite -Not sure if that will help though . . . let me know

Isn't SQL Lite for creating virtual DB's on your local machine?

[sandman]

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.

[PsaltyDS]

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

[weaponx]

Isn't there a Microsoft SQL UDF here: http://www.autoitscript.com/forum/index.ph...&hl=sql.au3 ???

[ChrisL]

I've been trying to get my head round this too. Here is my effort http://www.autoitscript.com/forum/index.php?showtopic=51952

[num1g8rfan]

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?

[ChrisL]

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?

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

[num1g8rfan]

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?

[ChrisL]

Sorry I don't know I'm a bit of a noob when it comes to SQL.

[ChrisL]

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

[ChrisL]

Come on.. tell me it works for you then I can go and get a celebratory glass of Pepsi Max

[num1g8rfan]

Sorry for getting back so late. I had already debugged it and forgot to tell you. Thank you so much for your assistance.

[painn3ck]

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?

[ChrisL]

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

[painn3ck]

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