simplifying SQL

[theguy0000]

its a little pointless, but i had real triuble understanding how to do things with SQL databases using autoit when i first started, this simplifies it a lot.

an example script would be:

#include "sql.au3"
_SQLConnect ( 'server', 'database', 'username', 'password' )
_SQLExecute ( 'SELECT * FROM `table` LIMIT 1' )
_SQLClose()

[edit]easy, huh?

attachment removed, see second reply[/edit]

Edited August 31, 2005 by theguy0000

[therks]

Uhm... why didn't you just attach a copy of sql.au3, instead of a script that will create the file in a directory that some people might not even be using?

[theguy0000]

lol good point

sql.au3

Edited December 14, 2005 by theguy0000

[Insolence]

The way you're commenting is inefficient and hard to read, try just putting the comments above the code to-be executed

Good job, otherwise

[HansH]

So where does the output goes from execute ?

If you want to do something with ado recordsets for results look at http://www.autoitscript.com/fileman/users/HansH/ODBCquery.au3

The only difference with ODBC is your connection string, code will be the same

[theguy0000]

@Insolence thanks

@HansH sorry not sure what you mean

lol i just saw HansH's GUISQL.au3

Edited August 31, 2005 by theguy0000

[HansH]

@HansH sorry not sure what you mean

<{POST_SNAPBACK}>

My fault, if you do $adCN.Execute ( $query )

where are the result rows going to ?

I extracted all the ADO sql calls from my program and put them in http://www.autoitscript.com/fileman/users/HansH/SQL.au3

You can find a sample GUI program here

Just change the include from GUISQL.au3 to SQL.au3

Hans

[theguy0000]

mm...you're going to have to explain this to me a bit more...im not too experienced in SQL, this is a simple UDF library

[HansH]

mm...you're going to have to explain this to me a bit more...im not too experienced in SQL, this is a simple UDF library

<{POST_SNAPBACK}>

Well a UDF library can be simple, but sometimes it is complicated within, because it has to test for error conditions en functions must be flexible.

For instance support for multiple open databases, so you can copy tables between databases.

Download the SQL.au3 and put it in the include directory

Underneath a basic test program

Just replace the query with a real one

#include <SQL.au3>

Dim $oDBid, $qryid, $result
$oDBid=_SQLOpen()               ; open database or _SQLopen("DRIVER={SQL Server}")
if not @error then              ; if open is successfull
    $qryid = _SQLQuery($oDBid, "select * from something")  ; send query
    While not @error                                    ; retrieve all rows
        $result &= _SQLGetRow($qryid) & @LF             ; append each row
    WEnd
    _SQLclose($oDBid)                                   ; close connection
    msgbox(64,"Result",$result)                         ; show all rows
Endif

Tell me what you don't understand.

Hans

[theguy0000]

"$oDBid=_SQLOpen()"

where would i put the username/password?

[HansH]

"$oDBid=_SQLOpen()"

where would i put the username/password?

<{POST_SNAPBACK}>

if you do: _SQLopen() , windows will show a dialog were you can select your datasource.

If the datasource requires an user/password it will prompt you for that.

if you do: _SQLopen("DRIVER={SQL Server};SERVER=" & $server & ";DATABASE=" & $db & ";uid=" & $username & ";pwd=" & $password & ";") it will connect to that server

or _SQLopen("DRIVER={SQL Server};") it will prompt you for uid and password if required.

or _SQLopen("c:\test\test.mdb") to connect to an access db

Simple and flexible

Hans

[theguy0000]

i get it, thanks!

[Jamie]

i get it, thanks!

I don't!

When I run anything that includes sql.au3 it generates an error:

'Line 102 (file "C:\AutoIT\sql.au3"):

If Not .EOF Then

If Not.E^ERROR

Error: Unable to parse line

... can anyone help please ?!

Jamie.

Edited November 7, 2005 by Jamie

[JSThePatriot]

I don't!

When I run anything that includes sql.au3 it generates an error:

'Line 102 (file "C:\AutoIT\sql.au3"):

If Not .EOF Then

If Not.E^ERROR

Error: Unable to parse line

... can anyone help please ?!

Jamie.

Are you running the latest AutoIt Beta? If not try installing that.

JS

[Jamie]

Yeah, I think that was my initial problem. So I now have SciTE installed plus the latest beta. I'm doing a 'beta' compile on:

#include "sql.au3"

$sqlCurrentGameID = "select max(game_id) from game_players";
$playerPosition = 6;
$totalPlayers = 10;
$sqlCurrentPlayerTotal = "select count(*) from session group by player_id";

_SQLConnect ( 'server', 'database', 'username', 'password' );
_SQLExecute ( $sqlCurrentGameID );
_SQLClose();

And get the warnings:

C:\Documents and Settings\Jamie\Desktop\AutoIT\sql.au3(14,36) : WARNING: $adCN: possibly used before declaration.

$adCN.Close ;==>Close the database

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

C:\Documents and Settings\Jamie\Desktop\AutoIT\sql.au3(9,35) : WARNING: $adCN: declared global in function only. Prefer top of file.

Global $adCN ;<==Declare variable

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

C:\Documents and Settings\Jamie\Desktop\AutoIT\testDB.au3 - 0 error(s), 1 warning(s)

Is this normal ?!

Jamie.

[JSThePatriot]

Yeah, I think that was my initial problem. So I now have SciTE installed plus the latest beta. I'm doing a 'beta' compile on:

#include "sql.au3"

$sqlCurrentGameID = "select max(game_id) from game_players";
$playerPosition = 6;
$totalPlayers = 10;
$sqlCurrentPlayerTotal = "select count(*) from session group by player_id";

_SQLConnect ( 'server', 'database', 'username', 'password' );
_SQLExecute ( $sqlCurrentGameID );
_SQLClose();

And get the warnings:

Is this normal ?!

Jamie.

As for the warnings... it appears that you are or in the SQL.au3 the variable is being defined as a global. I dont think this will cause the program to crash, but is a 'bad' programming practice.

JS

[cruizzer]

I don't!

When I run anything that includes sql.au3 it generates an error:

'Line 102 (file "C:\AutoIT\sql.au3"):

If Not .EOF Then

If Not.E^ERROR

Error: Unable to parse line

... can anyone help please ?!

Jamie.

Hi

I have the same problem.. and i'm running the latest AutoIt Beta.

Cruizzer

[theguy0000]

Run this program for me. tell me what the MsgBox says.

MsgBox ("Autoit Version", @AutoItVersion)

[flyingboz]

Run this program for me. tell me what the MsgBox says.

MsgBox ("Autoit Version", @AutoItVersion)

I can tell you w/o running it that it will throw an error

Try this slight mod

msgbox(0,"Autoit Version,@AutoItVersion)

[theguy0000]

oh yeah, sorry, I haven't used AutoIt in a long time now.