Jump to content
Sign in to follow this  
HansH

GUISQL.au3 UDF library

Recommended Posts

HansH

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/GUISQL.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 <GUISQL.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

Mmm, of course the library has nothing to do with a GUI, so I will adapt the name

later on. Any suggestions for an other name are welcome.

Hans

Edited by HansH

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  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.