Jump to content

SQL.au3 UDF library


Recommended Posts

This UDF library has some basic functions for connecting and querying SQL databases.

The UDF library can be found here : http://www.autoitscript.com/fileman/users/HansH/SQL.au3

If you have remarks or improvements, let me know, so I can adapt it and do the documentation according the UDF standards

Underneath a small example how to use this.

#include <GUIConstants.au3>
#include <SQL.au3>

; ==============================================================
; Define a general COM error handler for intercepting COM errors
; ==============================================================
; Variable $sCOMErrMsg will contain:  "Errorno:Error message"
Global $sCOMErrMsg=""
$oCOMError = ObjEvent("AutoIt.Error","_COMErrHandler"); Install a COM error handler 

; DBid of connection
Global $oDBid=0

; ========================================
; GUI controls
; ========================================
GuiCreate("SQL test", 500, 400, 400,300 )
GUICtrlCreateLabel("DSN :", 12, 22)
$inp_DSN = GUICtrlCreateCombo("", 50, 20, 320, 20)
GUICtrlSetData(-1,_SQLGetDSN())               ; fill it with known entries
$bt_DB   = GUICtrlCreateButton("Connect" , 400, 20, 80, 20)

$inp_qry = GUICtrlCreateEdit("" , 10, 50,360, 120)
$bt_run  = GUICtrlCreateButton("Run" , 400, 50, 80, 20)
$lst_qry = GUICtrlCreateListView("Result", 10, 180, 480, 200)
GUISetState ()

While 1
    $msg = GUIGetMsg()
    if $msg <> 0 then
        Select
        Case $msg = $GUI_EVENT_CLOSE
            ExitLoop
         Case $msg = $bt_DB       ; DB open or close
            if $oDBid = 0 Then
               $oDBid=_SQLOpen(GuiCtrlRead($inp_DSN))
                if @error then 
                    MsgBox(64,"Error",$sCOMErrMsg);
                Else
                    GUICtrlSetData($bt_DB,"Disconnect")
                Endif
            Else
               _SQLclose($oDBid)
               GUICtrlSetData($bt_DB,"Connect")
               $oDBid=0
            Endif
         Case $msg = $bt_run          ; run query, max 1000 rows allowed
            $qryid = _SQLQuery($oDBid, GUICtrlRead($inp_qry), 1000)
            if not @error then
                Dim $qryhdr, $qryresult
                $qryhdr = _SQLGetFields($qryid)
                GUICtrlDelete($lst_qry);
                $lst_qry = GUICtrlCreateListView($qryhdr, 10, 180, 480, 200)
                Do
                    $qryresult = _SQLGetRow($qryid)
                    if not @error Then GUICtrlCreateListViewItem($qryresult,$lst_qry)
                Until @error
            EndIf
       EndSelect
    EndIf
WEnd
if $oDBid <> 0 Then _SQLclose($oDBid)     ; if still open, close the database
GUIDelete()
Exit

; This is a general COM error handler
Func _COMErrHandler() 
   $sCOMErrMsg=hex($oCOMError.number,8) & ":" & $oCOMError.description
   SetError(1)             ; something to check for when this function returns 
Endfunc

Have fun

Hans

Link to post
Share on other sites

Uploaded latest version of library and demo program

Changes:

- Added _SQLschema() for catalog information about tables

- expanded example program to show table information

When double click on a table the column information is shown.

Have fun

Hans

Link to post
Share on other sites
  • 5 years later...

File not downloadble.

- SQL.au3 - UDF library.

- miniSQL.au3 - Mini Demo application.

It's probably been deleted in the 6 years since this was posted. Also, AutoIt3 has built in SQL database access in the SQLite.au3 file that comes in the install now.

If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

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

    No registered users viewing this page.

×
×
  • Create New...