Jump to content
Sign in to follow this  
theguy0000

simplifying SQL

Recommended Posts

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? :whistle:

attachment removed, see second reply[/edit]

Edited by theguy0000

The cake is a lie.www.theguy0000.com is currentlyUP images.theguy0000.com is currentlyUP all other *.theguy0000.com sites are DOWN

Share this post


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

Share this post


Link to post
Share on other sites
theguy0000

lol good point :whistle:

sql.au3

Edited by theguy0000

The cake is a lie.www.theguy0000.com is currentlyUP images.theguy0000.com is currentlyUP all other *.theguy0000.com sites are DOWN

Share this post


Link to post
Share on other sites
Insolence

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

Good job, otherwise


"I thoroughly disapprove of duels. If a man should challenge me, I would take him kindly and forgivingly by the hand and lead him to a quiet place and kill him." - Mark TwainPatient: "It hurts when I do $var_"Doctor: "Don't do $var_" - Lar.

Share this post


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

Share this post


Link to post
Share on other sites
theguy0000

@Insolence thanks

@HansH sorry not sure what you mean

lol i just saw HansH's GUISQL.au3 :whistle:

Edited by theguy0000

The cake is a lie.www.theguy0000.com is currentlyUP images.theguy0000.com is currentlyUP all other *.theguy0000.com sites are DOWN

Share this post


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

Share this post


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


The cake is a lie.www.theguy0000.com is currentlyUP images.theguy0000.com is currentlyUP all other *.theguy0000.com sites are DOWN

Share this post


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

Share this post


Link to post
Share on other sites
theguy0000

"$oDBid=_SQLOpen()"

where would i put the username/password?


The cake is a lie.www.theguy0000.com is currentlyUP images.theguy0000.com is currentlyUP all other *.theguy0000.com sites are DOWN

Share this post


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

Hans

Share this post


Link to post
Share on other sites
theguy0000

i get it, thanks!


The cake is a lie.www.theguy0000.com is currentlyUP images.theguy0000.com is currentlyUP all other *.theguy0000.com sites are DOWN

Share this post


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

Share this post


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


AutoIt Links

File-String Hash Plugin Updated! 04-02-2008 Plugins have been discontinued. I just found out.

ComputerGetInfo UDF's Updated! 11-23-2006

External Links

Vortex Revolutions Engineer / Inventor (Web, Desktop, and Mobile Applications, Hardware Gizmos, Consulting, and more)

Share this post


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

Share this post


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


AutoIt Links

File-String Hash Plugin Updated! 04-02-2008 Plugins have been discontinued. I just found out.

ComputerGetInfo UDF's Updated! 11-23-2006

External Links

Vortex Revolutions Engineer / Inventor (Web, Desktop, and Mobile Applications, Hardware Gizmos, Consulting, and more)

Share this post


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

Share this post


Link to post
Share on other sites
theguy0000

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

MsgBox ("Autoit Version", @AutoItVersion)

The cake is a lie.www.theguy0000.com is currentlyUP images.theguy0000.com is currentlyUP all other *.theguy0000.com sites are DOWN

Share this post


Link to post
Share on other sites
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 B):o:graduated:

Try this slight mod :)

msgbox(0,"Autoit Version,@AutoItVersion)

Reading the help file before you post... Not only will it make you look smarter, it will make you smarter.

Share this post


Link to post
Share on other sites
theguy0000

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


The cake is a lie.www.theguy0000.com is currentlyUP images.theguy0000.com is currentlyUP all other *.theguy0000.com sites are DOWN

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  

×