Jump to content

Recommended Posts

Posted

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

Posted

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]

Posted

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?

Posted

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]

Posted

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
Posted

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?

Posted (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)

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
Posted

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?

Posted

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?

Posted

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
Posted (edited)

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

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
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...