Jump to content

Basic SQL Query in AutoIt


Recommended Posts

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

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

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

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

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

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

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

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
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...