Jump to content
Sign in to follow this  
num1g8rfan

Basic SQL Query in AutoIt

Recommended Posts

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 :) )

Share this post


Link to post
Share on other sites
sandman

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]

Share this post


Link to post
Share on other sites
num1g8rfan

Microsoft SQL Databases

Share this post


Link to post
Share on other sites
John117

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

Share this post


Link to post
Share on other sites
num1g8rfan

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

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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.


[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]

Share this post


Link to post
Share on other sites
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

:)


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

Share this post


Link to post
Share on other sites
ChrisL
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?

Share this post


Link to post
Share on other sites
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 by ChrisL

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
ChrisL
ChrisL
ChrisL
num1g8rfan

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

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
painn3ck

Nevermind (doh)!! Forgot to remove some extra junk code. :)

(amazing what a couple hours of sleep will do)

Works perfectly! Thanks again!

Edited by painn3ck

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  

×