_SQL.au3. ADODB.Connection

[ChrisL]

I searched around on here for some SQL stuff to use with an MSDE database and I ended up getting confused so I tried to simplfy it a bit, there are only a couple of functions so far but I guess someone may find it usefull. I have a database called test and a table called BBKS

This has been totally revamped now and it uses very similar syntax to the SQLITE3 functions included with Autoit3

It will break any scripts that used the previous _SQL.au3 file but the new file is much more user friendly

See the example below

expandcollapse popup

#include <_sql.au3>
#include <array.au3>

Opt ("trayIconDebug",1)

Msgbox(0,"","Start the Script and load the error handler")
     _SQL_RegisterErrorHandler();register the error handler to prevent hard crash on COM error

    $oADODB = _SQL_Startup()
    If $oADODB = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())


    If _sql_Connect(-1,"localhost","","sa","Superartcore") = $SQL_ERROR then 
        Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())
        _SQL_Close()
        Exit
    EndIf

    If _SQL_Execute(-1,"Create database My_SQL_Test;") = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())
    _SQL_Close()


Msgbox(0,"","Created datatbase logging out and back in again")
    $oADODB = _SQL_Startup()
    
    
    
    If _SQL_Connect(-1,"localhost","My_SQL_Test","sa","Superartcore") = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())
    If _SQL_Execute(-1, "CREATE TABLE BBKS (ID INT NOT NULL IDENTITY(1,1),ComputerName VARCHAR(20) UNIQUE,Status VARCHAR(10),Error VARCHAR(10)Primary Key (ID));") = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())
    If _SQL_Execute(-1,"INSERT INTO BBKS (ComputerName,Status,Error) VALUES ('"& @computername & "','On;li''ne','None');") = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())
    If _SQL_Execute(-1,"INSERT INTO BBKS (ComputerName,Status,Error) VALUES ('1"& @computername & "','On;li''ne','None');") = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())
    If _SQL_Execute(-1,"INSERT INTO BBKS (ComputerName,Status,Error) VALUES ('2"& @computername & "','Online','None');") = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())

; this one will cause an error because the computername is not unique!
    If _SQL_Execute(-1,"INSERT INTO BBKS (ComputerName,Status,Error) VALUES ('2"& @computername & "','Online','None');") = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error","Example Error this was meant to happen!" & @crlf & @crlf & _SQL_GETErrMsg())



Msgbox(0,"","Created table and added data so lets get some data out first as a 2dArray")

    Local $aData,$iRows,$iColumns;Variables to store the array data in to and the row count and the column count
    $iRval = _SQL_GetTable2D(-1,"SELECT * FROM BBKS;",$aData,$iRows,$iColumns)
    If $iRval = $SQL_OK then _arrayDisplay($aData,"2D  (" & $iRows & " Rows) (" & $iColumns & " Columns)" )




Msgbox(0,"","Next as a 1dArray")

    Local $aData,$iRows,$iColumns;Variables to store the array data in to and the row count and the column count
    $iRval = _SQL_GetTable(-1,"SELECT * FROM BBKS;",$aData,$iRows,$iColumns)
    If $iRval = $SQL_OK then _arrayDisplay($aData,"1D (" & $iRows & " Rows) (" & $iColumns & " Columns)"  )
    
    
    

Msgbox(0,"","And now the same data returned 1 row at a time")

    $hData = _SQL_Execute(-1,"SELECT * FROM BBKS;")

    Local $aNames;Variable to store the array data in to
    $iRval = _SQL_FetchNames ($hData, $aNames); Read out Column Names
    If $iRval = $SQL_OK then ConsoleWrite(StringFormat(" %-10s  %-10s  %-10s  %-10s ", $aNames[0], $aNames[1], $aNames[2], $aNames[3]) & @CR)
    _ArrayDisplay($aNames,"Column Names")

    Local $aRow;Variable to store the array data in to
    While _SQL_FetchData ($hData, $aRow) = $SQL_OK; Read Out the next Row
        ConsoleWrite(StringFormat(" %-10s  %-10s  %-10s  %-10s ", $aRow[0], $aRow[1], $aRow[2], $aRow[3]) & @CR)
        _ArrayDisplay($aRow,"Single Row of Data")
    WEnd





Msgbox(0,"","And now the same data returned as a string")

    Local $vString
    If _Sql_GetTableAsString(-1,"SELECT * FROM BBKS;",$vString) = $SQL_OK then 
        Msgbox(0,"Data as a String",$vString)
    Else
        Msgbox(0 + 16 +262144,"SQL Error",_SQL_GetErrMsg() )
    EndIf




Msgbox(0,"","Now just a single row")

    Local $aRow;Variable to store the row array data in
    $iRval = _SQL_QuerySingleRow(-1,"SELECT * FROM BBKS;",$aRow)
    If $iRval = $SQL_OK then _arrayDisplay($aRow,"1 Row"  )




Msgbox(0,"","Now drop the tables and the database")
    If _SQL_Execute(-1, "DROP TABLE BBKS;") = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())

    If _SQL_Close() <> $SQL_OK then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg() )


;Just being lazy and not putting any error checking in now!
    $oADODB = _SQL_Startup()
    _SQL_Connect(-1,"localhost","","sa","Superartcore")
    _SQL_Execute(-1,"DROP database My_SQL_Test;")
    _SQL_Close()

Msgbox(0,"","Example Finished")

Previous downloads: 4176

_SQL.au3 updated with some tweaks by eltorro, CarlH and Elias (Thanks)

Updated 28/08/2010

Updated 29/04/2011

_sql_Old.au3

_sql.au3

Edited April 29, 2011 by ChrisL

[IvanCodin]

I am very new to programming. I have been looking on the forum for a way to open a database and query the contents. I want to make the script portable so I created a System SDN to make the query sql based and portable. I only need to read the data. I been stumped as to how i can do this. Most of the people on the form have more advanced skills than I do and i am having trouble grasping what I need to do to read the database. You post is the closest I have seen to what I want to use. I run the script and I get an error . There is no login and password required. Here is your code I modified:

#include <array.au3>

#include <_sql.au3>

$con = _SQLStartup()

If @error then Msgbox(0,"Error","Error starting ADODB.Connection")

_SQLConnect(-1,"localhost","alarm","","")

if @Error then Msgbox(0,"",$SQLErr)

I get a duplicate function error.

Seggestions?

Me

[IvanCodin]

I worked on this for quite and discovered the following. The include file _sql.au3 existed but was not your cide. I fixed this. I chnaged the connect string to

I changed my code to this:

#include <array.au3>

#include <_sql.au3>

$con = _SQLStartup()

If @error then Msgbox(0,"Error","Error starting ADODB.Connection")

_SQLConnect(-1," ","customers"," "," ") ;<-- This is the System DSN I created for the database.

if @Error then Msgbox(0,"",$SQLErr)

$data = _SQLExecute(-1,"SELECT * FROM CompanyName;")

If Not @error then

$aData = _SQLGetData2D($data)

_arrayDisplay($aData)

Else

Msgbox(0,"",$SQLErr)

EndIf

$data = _SQLExecute(-1,"SELECT ID FROM CompanyName;")

If Not @error then

$sData = _SqlGetDataAsString($data)

Msgbox(0,"Data as a string",$sData)

Else

Msgbox(0,"",$SQLErr)

EndIf

_SQLClose()

When it runs I do not see anything on the screen. The application in the toolbar is all that occurs.

Suggestions.

Me

[Hostage]

I am very new to programming. I have been looking on the forum for a way to open a database and query the contents. I want to make the script portable so I created a System SDN to make the query sql based and portable. I only need to read the data. I been stumped as to how i can do this. Most of the people on the form have more advanced skills than I do and i am having trouble grasping what I need to do to read the database. You post is the closest I have seen to what I want to use. I run the script and I get an error . There is no login and password required. Here is your code I modified:

#include <array.au3>

#include <_sql.au3>

$con = _SQLStartup()

If @error then Msgbox(0,"Error","Error starting ADODB.Connection")

_SQLConnect(-1,"localhost","alarm","","")

if @Error then Msgbox(0,"",$SQLErr)

I get a duplicate function error.

Seggestions?

Me

What format is your database in? Access?

[Klaatu]

This is what I use. It's written to communicate with a specific Access database, but I tried to write it to be as generic (to Access databases) as possible. Here's the code. If anyone has specific questions about it I'd be glad to try to answer. Hope this is helpful to someone else, as it took me a long time to come up with it.

expandcollapse popup

#include-once

Global $__oInvConn, $__iInvCount = 0

;**********************************************************************
Func _InvConn()
   If $__iInvCount = 0 Then
      SetError(1)
   EndIf
   Return $__oInvConn
EndFunc   ;==>_InvConn

;**********************************************************************
; opens the database
; returns true if db was opened successfully
;**********************************************************************
Func _InvOpen()
   Local Const $sDatabase = "C:\Database.mdb"

   If $__iInvCount = 0 Then
      ObjEvent("AutoIt.Error", "__InvCOMErr")
      $__oInvConn = ObjCreate("ADODB.Connection")
      $__oInvConn.Provider = "Microsoft.Jet.OLEDB.4.0"
      $__oInvConn.Open($sDatabase)
      If Not @Error Then
         $__iInvCount += 1
         Return True
      Else
         Return False
      EndIf
   Else
      $__iInvCount += 1
      Return True
   EndIf
EndFunc   ;==>_InvOpen

;**********************************************************************
; closes the database
;**********************************************************************
Func _InvClose()
   If $__iInvCount > 0 Then
      $__iInvCount -= 1
      If $__iInvCount = 0 Then
         $__oInvConn.Close
         ObjEvent("AutoIt.Error", "")
      EndIf
   EndIf
EndFunc   ;==>_InvClose

;**********************************************************************
; Returns results of a query into the database
; returns the recordset object itself
; assumes the database is already open
; CursorType: 0 = forward-only recordset
;             2 = updatable recordset
;**********************************************************************
Func _InvQueryRS($sQuery, $iCursorType = 0)
   Local $oRs

   If $__iInvCount = 0 Then
      SetError(2)
   Else
      $oRs = ObjCreate("ADODB.Recordset")
      $oRs.CursorType = $iCursorType; adOpenForwardOnly = 0
      $oRs.LockType = 3; adLockOptimistic  = 3
      $oRs.Open($sQuery, $__oInvConn)
      If @Error Then
         SetError(1)
      EndIf
   EndIf
   Return $oRs
EndFunc   ;==>_InvQueryRS

;**********************************************************************
; Returns results of a query into the database
; returns an array of strings
; each string is a character separated list of fields returned by the query.
; the default separator character is the comma
; as usual, element 0 is count, like stringsplit
;**********************************************************************
Func _InvQuery($sQuery, $sSeparator = ',')
   Local $oRs, $aResults[1] = [0], $sRecords
   Local Const $adClipString = 2

   If _InvOpen() Then
      $oRs = _InvQueryRS($sQuery)
      If Not @Error Then
         $sRecords = $oRs.GetString($adClipString, $oRs.RecordCount, $sSeparator, @CR, '')
         While StringLen($sRecords) > 0 And StringRight($sRecords, 1) = @CR
            $sRecords = StringTrimRight($sRecords, 1)
         Wend
         $aResults = StringSplit($sRecords, @CR)
         $oRs.Close
      EndIf
      _InvClose()
   EndIf
   Return $aResults
EndFunc   ;==>_InvQuery

;**********************************************************************
Func __InvCOMErr()
   Local Const $oCOMError = @COM_EventObj
   Local $sHexNum = Hex($oCOMError.Number, 8)
   MsgBox(0, @ScriptName, "We intercepted a COM Error !" & @CRLF & _
         "On line " & $oCOMError.scriptline & " of the script." & @CRLF & _
         "Number is: " & $sHexNum & @CRLF & _
         "Windescription is: " & $oCOMError.WinDescription, 10)
   SetError(1)
EndFunc   ;==>__InvCOMErroÝ÷ Ø   ÝêÞßÛìZ^¡ø­²ëNé]±©ÝjYZ­ëÞ¯+)yÈËh'Zµ¦Ú±ë(Ýý²z-ºÇºX§Ûŧ-~涧ɺÉély»­¶¬¶»¶êÞ¡ûayìZ^Çb­ç-+¬i¹^jëh×6   Local Const $sCurrentDateTime = @MON & '/' & @MDAY & '/' & @YEAR & ' ' & @HOUR & ':' & @MIN & ':00'
   Local Const $sQuery = "SELECT [Computers].[Node Name], [Computers].[LastUpTime] FROM [Computers] " & _
         "WHERE (([Computers].[Office] > 0) AND ([Computers].[Node Name] LIKE 'WKSTN-%')) " & _
         "ORDER BY [Computers].[Node Name];"
   Local $oRs, $sNode
   
   If _InvOpen() Then
      $oRs = _InvQueryRS($sQuery, 2)
      With $oRs
         While Not .EOF
            $sNode = .Fields.Item(0).Value
            If _IsPingable($sNode) Then
               .Fields.Item(1).Value = $sCurrentDateTime
               .Update
            EndIf
            .MoveNext
         Wend
         .Close
      EndWith
      _InvClose()
   EndIf

[IvanCodin]

Your code has an include once.au3. is this also available?

me

[IvanCodin]

What format is your database in? Access?

My database is an Access database. However I wanted to create a SysteM DSN so that if the database changes, it will, I'll be able to create another DSN and not chnage my code.

I am really begining to become frustrated with what would appear to be simple.

I want to query a database. No updates to the database read only.

Search for a customer

Based on the selected customer 5 files are set to varibales.

The varibales create a MS RAS connection I run and connect me to the cusotmers system.

I don't think I am an idiot but this sure has pushed my limits of patience. My coding experiance is definately lacking but I can get shell script working in Linux no problem.

ANY help here is greatly appreciated.

PS I am amazed as to what you guys can code and do with this software. I think I found my new toy!!!!

Me

[Klaatu]

Your code has an include once.au3. is this also available?

You're kidding, right?

[Hostage]

My database is an Access database. However I wanted to create a SysteM DSN so that if the database changes, it will, I'll be able to create another DSN and not chnage my code.

I am really begining to become frustrated with what would appear to be simple.

I want to query a database. No updates to the database read only.

Search for a customer

Based on the selected customer 5 files are set to varibales.

The varibales create a MS RAS connection I run and connect me to the cusotmers system.

I don't think I am an idiot but this sure has pushed my limits of patience. My coding experiance is definately lacking but I can get shell script working in Linux no problem.

ANY help here is greatly appreciated.

PS I am amazed as to what you guys can code and do with this software. I think I found my new toy!!!!

Me

Global $ODBC_Access="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\\fwpsrv\fdrive\FWP Database\New Good.mdb"

$newCon = ObjCreate ("ADODB.Connection")

$newCon.Open ($ODBC_Access & ";")

$rs = $newCon.Execute("SELECT * FROM [TABLE] WHERE [Customer Name Field Name] LIKE '%NAME%'")

while not $rs.EOF

$name = $rs.fieldS("Customer Name Field Name").value

$address = $rs.fields("Customer Address Field Name").value

$eye_color = $Rs.fields("Customer Eye Color Field Name").value

;do your stuff here

$rs.MoveNext

wend

$rs.Close

$rs = ""

That will connect straight to the database using Microsoft Jet. You can look up adodb (that is the microsoft library we're using for database access) connection strings for DSN connections.

#include-once just means the file will only be included once.

[Schyzo]

Very nice ! I'm starting in autoit and that's exactly what I needed, Thanks much Chris !

Sch

[Schyzo]

Hi again ChrisL, I'm almost done with my code but now I'm stuck where I want to pass variables to your _SQL.au3 function. If I hardcode the server, database, user and password it works fine but I'm trying to make a script that will let me connect to any server and database using GUI input. Here's a couple tries that ended up with either errors or no connection.

CODE

; from my GUI code

$GuiServer = GuiCtrlCreateInput("", 20, 50, 200, 20)

$GuiDatabase = GuiCtrlCreateInput("", 20, 100, 200, 20)

; put the results in variables

$server = GUICtrlRead($GuiServer, 1)

$db = GUICtrlRead($GuiDatabase, 1)

$username = ("u" & GUICtrlRead($GuiDatabase, 1)) ; user name is the database name precedeed by 'u'

$password = ("hardcodedpassword")

;tried to create variables as objects after those above didn't work

$oserver = ObjCreate($server)

$odb = ObjCreate($db)

$ousername = ObjCreate($username)

$opassword = ObjCreate($password)

;list of different tries

_SQLConnect(-1, "'" & GUICtrlRead($GuiServer, 1) & "'", "'" & GUICtrlRead($GuiDatabase, 1) & "'", "'u" & GUICtrlRead($GuiDatabase, 1) & "'","hardcodedpassword")

_SQLConnect(-1, $server, $db, $username,$password)

_SQLConnect(-1, $oserver, $odb, $ousername,$opassword)= ';

Any help would be greatly appreciated ! I'm so close to being done I can smell the cigar !lol

Schyzo

[Schyzo]

Forget that last post, this line did the trick... god knows why it didn't work the 1st 15 times lol !

_SQLConnect(-1, $server, $db, $username,$password)

Sch

[Oldschool]

This is what I use. It's written to communicate with a specific Access database, but I tried to write it to be as generic (to Access databases) as possible. Here's the code. If anyone has specific questions about it I'd be glad to try to answer. Hope this is helpful to someone else, as it took me a long time to come up with it.

That is pretty good code.

It's working perfect on my end.

$var = _InvOpen()
MsgBox(0, "", $var, 1)
$amntWon = "SELECT SUM([session].[amount_won]) FROM [session] WHERE [session].[player_id] ="&22&";"
$sSeparator = Chr(13)
$data = _InvQuery($amntWon, $sSeparator)
MsgBox(0, "", $data[1], 1)
_InvClose()

Do you by chance know how to translate this quiry so it does not error out? It works perfect in Access, but AutoIt does not like it...

SELECT (players.player_id) FROM (players) WHERE (( players.screen_name) = "MyName");

I tried the working syntax for $amntWon, but it does not work for this query.

$PlayerID = "SELECT [players].[player_id] FROM [players] WHERE [players].[screen_name] ="&$PlayerName&";"

I need to put this in there to determine (player_id)

Edited October 25, 2007 by Oldschool

[Oldschool]

I figured it out....

$PlayerID = "SELECT [players].[player_id] FROM [players] WHERE [players].[screen_name] ="&Chr(34)&$PlayerName&Chr(34)&";"

[BrettF]

I figured it out....

$PlayerID = "SELECT [players].[player_id] FROM [players] WHERE [players].[screen_name] ="&Chr(34)&$PlayerName&Chr(34)&";"

Or...

$PlayerID = 'SELECT [players].[player_id] FROM [players] WHERE [players].[screen_name] ="'&$PlayerName&'";'

Edited October 25, 2007 by Bert

[diikee]

Was this meant to be for ACCESS database or SQLSERVER 2000 database??

[Zedna]

I searched around on here for some SQL stuff to use with an MSDE database and I ended up getting confused so I tried to simplfy it a bit, there are only a couple of functions so far but I guess someone may find it usefull. I have a database called test and a table called BBKS

Nice.

Just one big speed optimization:

In _SQLGetData2D() use $objquery.RecordCount to make Redim just once before While NOT .EOF loop (and not inside this loop)

[ChrisL]

Nice.

Just one big speed optimization:

In _SQLGetData2D() use $objquery.RecordCount to make Redim just once before While NOT .EOF loop (and not inside this loop)

Thanks Zedna.. I'll have a look when the part for my laptop arrives later today muttley

[ChrisL]

Nice.

Just one big speed optimization:

In _SQLGetData2D() use $objquery.RecordCount to make Redim just once before While NOT .EOF loop (and not inside this loop)

That didn't work because of the way the data was raised I think. It always returns -1

This is what I found regarding the -1

This property will return -1 for a forward-only cursor; the actual count for a static or keyset cursor; and -1 or the actual count for a dynamic cursor.

I have discovered an even better way

$ret = $objquery.GetRows() it automatically builds a 2d array, if the user wants the column names then I can add a parameter to increase the size of the array and shift all the data down 1 row and put the column names at the top. I'll upload it in a bit

[jokke]

Hiya ChrisL!

Thanks for this great script, i tried using a "raw" com object for a connection (did not work out to well after a while, when it started to do wierd things), using this script with the error handling made the debugging aswell as the enduser experience much greater.