Direct SQL access in autoit (beta)

dougie · September 14, 2005

Hi All,

I am using the ODBCQuery.au3 to test select statments on various tables.

When selecting columns that are money/float, 0 zero is displayed instead of the actual value (e.g. 100.00)

The recordset for that field returns a DataType 6 (DB_CY) but the .value returns a zero.

Another example ot the same problem

$objConn = ObjCreate("ADODB.Connection")
$objConn.Open("Provider='sqloledb';Data Source='DouglasVV';Initial Catalog='TEBSchemes';User ID='sa';Password='smalltalk';")
$rsCustomers = $objConn.Execute("SELECT * FROM TEBSchemes..NewPremiums")
With $rsCustomers
    While Not .EOF
        ConsoleWrite($rsCustomers.Fields("Deducted").Value & " - " & .Fields("PayDate").Value & @LF)
        .MoveNext
    WEnd
    .Close
EndWith
$objConn.Close

Results
0 - 20050901
0 - 20050901
0 - 20050901
0 - 20050901

Can someone please help?

I am using the V3.1.1.75 beta of AutoIT3

Temporary solution - I cast the Money/Float to char.

It works but is not a satisfactory solution.

Thanks

Dougie

Edited September 14, 2005 by dougie

Trystian · November 23, 2005

If you don't want to, or can't setup your own DNS entry on your client machine, here is the DSN-Less connection string to connect directly to a mySQL database. You need to have the myODBC driver installed on the client. Get the driver HERE.

$sConn = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=mysql.yoursever.com;DATABASE=database;USER=username;PASSWORD=password;PORT=3306;OPTIO

N=3;"

Enjoy,

-Trystian

Kuliklaus · November 27, 2005

Hi all.

I have the same Problem like Dougie. Currencies are read as "0" instead of the real value.

I'm not able to change the SQL Table.

I think somebody found a solution in the meantime but didn't post it ? :">

Thanks

Kuliklaus

memnon · November 28, 2005

is it possible to use INSERT INTO in the sql query ?

i have only a csv file to test with, and if i want to insert a new row i get in error

INSERT INTO data.csv VALUES (test1,test2,test3)

isnt workin, ARG - already found it, sorry for interupting

INSERT INTO data.csv (Name,phone,address) VALUES ('test1',`test2','test3')

the rows are created if i press RUN, but after the creation i got errors on the console and the programm crashes - i cant look in the result...

>Running: (3.1.1.90):C:\Programme\AutoIt3\beta\autoit3.exe "D:\test4.au3"

D:\test4.au3 (138) : ==> The requested action with this object has failed.:

$adoRs.Open ($adoSql, $adoCon)

Edited November 28, 2005 by memnon

Kuliklaus · December 2, 2005

is it possible to use INSERT INTO in the sql query ?
i have only a csv file to test with, and if i want to insert a new row i get in error
INSERT INTO data.csv VALUES (test1,test2,test3)
isnt workin, ARG - already found it, sorry for interupting
INSERT INTO data.csv (Name,phone,address) VALUES ('test1',`test2','test3')
the rows are created if i press RUN, but after the creation i got errors on the console and the programm crashes - i cant look in the result...
>Running: (3.1.1.90):C:\Programme\AutoIt3\beta\autoit3.exe "D:\test4.au3"
D:\test4.au3 (138) : ==> The requested action with this object has failed.:
$adoRs.Open ($adoSql, $adoCon)

.... INSERT INTO only works as an SQL Query qith SQL Database Tables. CSV is a textfile and I think you have to try something else like reading the file in an array , replacing the data and writing again using filereadline / filewriteline ( or put your data in SQL Database, do your queries and then write to csv textfile again.)

Kuliklaus

dougie · December 3, 2005

Hi all.
I have the same Problem like Dougie. Currencies are read as "0" instead of the real value.
I'm not able to change the SQL Table.
I think somebody found a solution in the meantime but didn't post it ? :">
Thanks
Kuliklaus

Hi Kuliklaus,

Until this is fixed, I have been adding a character to the front of the number e.g.

rtrim('Z'+cast(m.Employee as char(8))) and then strip it out

$s_DREmployee = StringStripWS($qryDoRaise.Fields(3).Value,3)

$s_DREmployee = StringRight($s_DREmployee, StringLen($s_DREmployee)-1)

Regards

Dougie

SvenP · January 12, 2006

Hi All,
I am using the ODBCQuery.au3 to test select statments on various tables.
...
Can someone please help?
I am using the V3.1.1.75 beta of AutoIT3
Temporary solution - I cast the Money/Float to char.
It works but is not a satisfactory solution.
Thanks
Dougie

Hello Dougie,

Sorry for the looong delay. But the currency problem has been fixed in AutoIt beta version 3.1.1.101

Regards,

-Sven

dougie · January 13, 2006

Hello Dougie,
Sorry for the looong delay. But the currency problem has been fixed in AutoIt beta version 3.1.1.101
Regards,
-Sven

Hi Sven,

No problem o:)

Thanks for fixing it!

AutoIT is stablising on a daily basis - Can't wait for a stable version :lmao:

Dougie

January 13, 2006

do you know where to find a reference for the ADODB obj. ?
btw. it is possible to setup a data source thru the registry.
checkt out HKEY_LOCAL_MACHINE\SOFTWARE\ODBC

Anything is possible, however, I have only achieved this for SQL Server databases. You can see my previous post with script here: previous post

Access and Oracle databases require far more complex keys/subkeys, and often have information specifying version, engine, and driver information which may not be consistent across different OSes and systems. It's been a long time since I first looked up the topic so I don't remember all of the specifics with other database systems, but I know the rest aint easy!

April 17, 2006

If you don't want to, or can't setup your own DNS entry on your client machine, here is the DSN-Less connection string to connect directly to a mySQL database. You need to have the myODBC driver installed on the client. Get the driver HERE.

$sConn = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=mysql.yoursever.com;DATABASE=database;USER=username;PASSWORD=password;PORT=3306;OPTIO
N=3;"

Enjoy,
-Trystian

I assume you should be able to do much the same with SQL Server by changing the Driver name and possibly adding trusted_connection. If I was to implement such a connection into a typical AutoIt script, how would I do this? Do you have any simple sample scripts? Thanks!

Teldin · March 16, 2007

Is there an updated version of this, or anything like it? It looks really cool but not working for me (nothing happens when it's ran).

boomingranny · June 18, 2007

Hi,

When i ran the tool on my Windows XP Sp2 PC it got stuck in the ODBCsources loop, because instead of $s_VarNm returning null it returned a string similar to "there are no more datasources",

$s_Value returned null tho, so i suggest updating the func to:

Func ODBCsources($h_controlID, $s_RegEntry, $s_Type)
    Local $s_List, $i_dsncount, $s_VarNm, $s_Value, $i
    $i = 1
    $s_VarNm = RegEnumVal($s_RegEntry,  $i)
    $s_Value = RegRead($s_RegEntry, $s_Varnm)
    While $s_VarNm <> "" and $s_Value <> ""
        $s_VarNm = RegEnumVal($s_RegEntry,  $i)
        $s_Value = RegRead($s_RegEntry, $s_Varnm)
        If $s_Varnm <> "" and $s_Value <> "" Then
            GUICtrlCreateListViewItem($s_VarNm & "|" & $s_Type & "|" & $s_Value, $h_controlID)
            $i += 1
            $i_DSNcount += 1
        EndIf
    Wend
EndFunc

(perhaps a max results would be an idea too...)

Edited June 18, 2007 by boomingranny

wraithdu · August 25, 2008

Nice start on this script! I updated it to work on current versions of AutoIt, and added some error checking, and some useful resizing. It can save the query results to a 'query.log' file on the desktop now as well. I also added user/pass input fields for databases that require authentication (lilke mine). It's not 100% tidied up nor does it have all vars declared, but it's working. I left most of that in original state due to time constraints, but I'll do cleanup later (and leave it up to you as well).

** see next post **

Edited August 26, 2008 by wraithdu

wraithdu · August 26, 2008

Big update. Converted to OnEvent mode, recent files menu now fully functional, saves up to 10 recently opened queries. A selected recent entry will be moved to the top of the list when chosen.

For my use, I added a token to the query tab that will be replaced with the user input when the query is run. This could be changed or removed for your purposes.

#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <StaticConstants.au3>
#include <ListViewConstants.au3>
#include <EditConstants.au3>
#include <ButtonConstants.au3>
#include <GuiListView.au3>
#include <File.au3>

Opt("GUIOnEventMode", 1)
Opt("MustDeclareVars", 1)

Global $status = "Disconnected", $idNone = -1
Global $oMyError, $gui, $filemenu, $fileitem, $helpmenu, $saveitem, $infoitem, $exititem, $recentfilesmenu
Global $viewmenu, $viewstatusitem, $l_statusbar
Global $tab, $tab_conn, $tab_query, $tab_result, $dsn_list, $bt_conn, $bt_discon, $user, $pass, $ed_qry, $bt_run, $bt_clr, $result, $bt_new, $bt_save
Global $adoCon, $evt_name
Global $recent = @ScriptDir & "\recent.txt", $recentname, $recentfile, $recentarray[1][3] = [[0, "", ""]]

$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

$gui = GuiCreate("ODBC Query", 704, 488, -1, -1, BitOR($WS_OVERLAPPEDWINDOW, $WS_CLIPSIBLINGS))
$filemenu = GUICtrlCreateMenu("&File")
    $fileitem = GUICtrlCreateMenuitem("Open", $filemenu)
    $recentfilesmenu = GUICtrlCreateMenu("Recent Files", $filemenu)
    GUICtrlCreateMenuitem ("", $filemenu)
    $saveitem = GUICtrlCreateMenuitem("Save", $filemenu)
    $exititem = GUICtrlCreateMenuitem("Exit", $filemenu)
$viewmenu = GUICtrlCreateMenu("View")
    $viewstatusitem = GUICtrlCreateMenuitem("Statusbar", $viewmenu)
    GUICtrlSetState(-1, $GUI_CHECKED)
$helpmenu = GUICtrlCreateMenu("?")
    $infoitem = GUICtrlCreateMenuitem("About", $helpmenu)
$l_statusbar = GUICtrlCreateLabel($status, 1, 450, 700, 18, BitOr($SS_SIMPLE, $SS_SUNKEN))

_AddRecent()

$tab = GUICtrlCreateTab(1, 1, 700, 440)
GUICtrlSetResizing(-1, $GUI_DOCKBORDERS)

$tab_conn = GUICtrlCreateTabitem(" Connection ")
    GUICtrlSetState(-1, $GUI_SHOW)
    $dsn_list = GUICtrlCreateListView("DSN|Type|Description", 20, 50, 550, 350)
    GUICtrlSetResizing(-1, $GUI_DOCKBORDERS)
    ODBCsources($dsn_list, "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ODBC Data Sources" , "USER"  )
    ODBCsources($dsn_list, "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources", "SYSTEM")
    $bt_conn = GUICtrlCreateButton("Connect", 600, 70, 70, 20, $BS_DEFPUSHBUTTON)
    GUICtrlSetResizing(-1, $GUI_DOCKTOP + $GUI_DOCKRIGHT + $GUI_DOCKSIZE)
    $bt_discon = GUICtrlCreateButton("Disconnect", 600, 110, 70, 20)
    GUICtrlSetResizing(-1, $GUI_DOCKTOP + $GUI_DOCKRIGHT + $GUI_DOCKSIZE)
    GUICtrlSetState(-1, $GUI_DISABLE )
    GUICtrlCreateLabel("User", 20, 416)
    GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKBOTTOM + $GUI_DOCKSIZE)
    $user = GUICtrlCreateInput("", 50, 410, 104)
    GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKBOTTOM + $GUI_DOCKSIZE)
    GUICtrlCreateLabel("Pass", 170, 416)
    GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKBOTTOM + $GUI_DOCKSIZE)
    $pass = GUICtrlCreateInput("", 200, 410, 104, 21, BitOR($GUI_SS_DEFAULT_INPUT, $ES_PASSWORD))
    GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKBOTTOM + $GUI_DOCKSIZE)

$tab_query=GUICtrlCreateTabitem (" Query ")
    $ed_qry = GUICtrlCreateEdit("", 20, 50, 550, 350, 0x50200104, 0x00000200); edit control with wordwrap
    GUICtrlSetResizing(-1, $GUI_DOCKBORDERS)
    $bt_run = GUICtrlCreateButton("Run"  , 600,  70, 70, 20)
    GUICtrlSetResizing(-1, $GUI_DOCKTOP + $GUI_DOCKRIGHT + $GUI_DOCKSIZE)
    GUICtrlSetState(-1, $GUI_DISABLE)
    $bt_clr = GUICtrlCreateButton("Clear", 600, 110, 70, 20)
    GUICtrlSetResizing(-1, $GUI_DOCKTOP + $GUI_DOCKRIGHT + $GUI_DOCKSIZE)
    GUICtrlCreateLabel("Event Name (replaces EVENT_NAME_TOKEN)", 20, 416)
    GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKBOTTOM + $GUI_DOCKSIZE)
    $evt_name = GUICtrlCreateInput("", 260, 410, 104)
    GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKBOTTOM + $GUI_DOCKSIZE)

$tab_result=GUICtrlCreateTabitem (" Result ")
    $result = GUICtrlCreateListView("Result", 20, 50, 550, 350)
    GUICtrlSetResizing(-1, $GUI_DOCKBORDERS)
    $bt_new = GUICtrlCreateButton("New query"  , 600,  70, 80, 20)
    GUICtrlSetResizing(-1, $GUI_DOCKTOP + $GUI_DOCKRIGHT + $GUI_DOCKSIZE)
    $bt_save = GUICtrlCreateButton("Save", 600, 110, 80, 20)
    GUICtrlSetResizing(-1, $GUI_DOCKTOP + $GUI_DOCKRIGHT + $GUI_DOCKSIZE)
GUICtrlCreateTabitem ("")

GUISetOnEvent($GUI_EVENT_CLOSE, "_GUIClose")

GUICtrlSetOnEvent($saveitem, "_SaveQuery")
GUICtrlSetOnEvent($exititem, "_GUIClose")
GUICtrlSetOnEvent($fileitem, "_OpenFile")
GUICtrlSetOnEvent($viewstatusitem, "_ToggleStatusbar")
GUICtrlSetOnEvent($infoitem, "_About")
GUICtrlSetOnEvent($bt_conn, "_Connect")
GUICtrlSetOnEvent($bt_discon, "_Disconnect")
GUICtrlSetOnEvent($bt_run, "_RunQuery")
GUICtrlSetOnEvent($bt_clr, "_ClearQuery")
GUICtrlSetOnEvent($bt_new, "_NewQuery")
GUICtrlSetOnEvent($bt_save, "_SaveResults")
GUICtrlSetOnEvent($tab, "_Tab")

GUISetState ()

While 1
    Sleep(1000)
WEnd

Func _GUIClose()
    If GUICtrlRead($l_statusbar) <> "Disconnected" Then
        $adoCon.Close
    EndIf
    Exit
EndFunc

Func _AddRecent()
    Local $name, $file, $cID, $path
    
    If FileExists($recent) Then
        $file = StringSplit(FileRead($recent), @CRLF, 1)
        If $file[1] <> "" Then
            For $i = 1 To $file[0]
                $path = _PathFull(@ScriptDir & "\" & $file[$i])
                If FileExists($path) Then
                    $name = StringTrimLeft($file[$i], StringInStr($file[$i], "\", 0, -1))
                    $cID = GUICtrlCreateMenuItem($name, $recentfilesmenu)
                    GUICtrlSetOnEvent(-1, "_RecentFile")
                    $recentarray[0][0] += 1
                    ReDim $recentarray[$recentarray[0][0] + 1][3]
                    $recentarray[$recentarray[0][0]][0] = $cID
                    $recentarray[$recentarray[0][0]][1] = $name
                    $recentarray[$recentarray[0][0]][2] = $path
                EndIf
            Next
            _WriteRecentFile()
        EndIf
    EndIf
    If Not FileExists($recent) Or $file[1] == "" Then
        $idNone = GUICtrlCreateMenuItem("(none)", $recentfilesmenu)
        GUICtrlSetState(-1, $GUI_DISABLE)
    EndIf
EndFunc

Func _OpenFile()
    Local $file
    
    $file = FileOpenDialog("Open saved query...", @ScriptDir, "SQLfiles (*.sql)", 1 + 2, "", $gui)
    If Not @error Then
        GUICtrlSetData($ed_qry, FileRead($file))
        _UpdateArrayAndMenu($file)
    EndIf
EndFunc

Func _SaveQuery()
    Local $file, $oFile
    
    $file = FileSaveDialog("Save query...", @ScriptDir, "SQLfiles (*.sql)", 2 + 16, "", $gui)
    If Not @error Then
        If StringRight($file, 4) <> ".sql" Then $file &= ".sql"
        $oFile = FileOpen($file, 2 + 8)
        FileWrite($oFile, GUICtrlRead($ed_qry))
        FileClose($oFile)
        _UpdateArrayAndMenu($file)
    EndIf
EndFunc

Func _UpdateArrayAndMenu($file)
    Local $name, $idx, $cID, $str = "", $i
    
    If $idNone <> -1 Then
        GUICtrlDelete($idNone)
        $idNone = -1
    EndIf
    $name = StringTrimLeft($file, StringInStr($file, "\", 0, -1))
    $idx = _ArraySearch($recentarray, $name, 0, 0, 0, 0, 1, 1)
    If $idx <= 0 Then; add to array and menu
        If UBound($recentarray) >= 11 Then; remove menu item and last row in array
            GUICtrlDelete($recentarray[10][0])
            ReDim $recentarray[10][3]
            $recentarray[0][0] -= 1
        EndIf
        $cID = GUICtrlCreateMenuItem($name, $recentfilesmenu, 0)
        GUICtrlSetOnEvent(-1, "_RecentFile")
        $recentarray[0][0] += 1
        _ArrayInsert2D($recentarray, 1, $cID & "|" & $name & "|" & $file)
    ElseIf $idx > 1 Then; item found, move to top of menu and array
        GUICtrlDelete($recentarray[$idx][0])
        $cID = GUICtrlCreateMenuItem($recentarray[$idx][1], $recentfilesmenu, 0)
        GUICtrlSetOnEvent(-1, "_RecentFile")
        _ArrayInsert2D($recentarray, 1, $cID & "|" & $recentarray[$idx][1] & "|" & $recentarray[$idx][2])
        _ArrayDelete($recentarray, $idx + 1)
; Else, already first entry, no change
    EndIf
    _WriteRecentFile()
EndFunc

Func _WriteRecentFile()
    Local $i, $str, $file
    
; write recent.txt
    For $i = 1 To $recentarray[0][0]
        $str &= _PathGetRelative(@ScriptDir, $recentarray[$i][2]) & @CRLF
    Next
    $str = StringTrimRight($str, 2)
    $file = FileOpen($recent, 2)
    FileWrite($file, $str)
    FileClose($file)
EndFunc

Func _ArrayInsert2D(ByRef $avArray, $iIdx = 0, $sData = "")
    Local $i, $j, $avData
    
    If UBound($avArray, 0) <> 2 Then Return SetError(1)
    $avData = StringSplit($sData, "|")
    If UBound($avData) - 1 > UBound($avArray, 2) Then Return SetError(2)
    ReDim $avArray[UBound($avArray) + 1][UBound($avArray, 2)]
    For $i = UBound($avArray) - 1 To $iIdx + 1 Step -1
        For $j = 0 To UBound($avArray, 2) - 1
            $avArray[$i][$j] = $avArray[$i - 1][$j]
        Next
    Next
; clear old row
    For $j = 0 To UBound($avArray, 2) - 1
        $avArray[$iIdx][$j] = ""
    Next
; new data
    For $j = 1 To UBound($avData) - 1
        $avArray[$iIdx][$j - 1] = $avData[$j]
    Next
EndFunc

Func _RecentFile()
    Local $cID = @GUI_CtrlId, $idx
    
    $idx = _ArraySearch($recentarray, $cID)
    If $idx <> -1 Then; load query, update menu and array
        GUICtrlSetData($ed_qry, FileRead($recentarray[$idx][2]))
        _UpdateArrayAndMenu($recentarray[$idx][2])
    EndIf
EndFunc

Func _ToggleStatusbar()
    If BitAnd(GUICtrlRead($viewstatusitem), $GUI_CHECKED) == $GUI_CHECKED Then
        GUICtrlSetState($viewstatusitem, $GUI_UNCHECKED)
        GUICtrlSetState($l_statusbar, $GUI_HIDE)
    Else
        GUICtrlSetState($viewstatusitem, $GUI_CHECKED)
        GUICtrlSetState($l_statusbar, $GUI_SHOW)
    EndIf
EndFunc

Func _About()
    Msgbox(0,"Info","ODBC Query version 0.1")
EndFunc

Func _Connect()
    Local $dsnarray, $dsnraw, $dsn
    
    $dsnarray = StringSplit(GUICtrlRead(GUICtrlRead($dsn_list)), "|")
    If $dsnarray[0] > 1 Then
        $dsnraw = $dsnarray [1]
        $dsn = "DSN=" & $dsnraw
        If GUICtrlRead($user) <> "" Then $dsn &= ";uid=" & GUICtrlRead($user)
        If GUICtrlRead($pass) <> "" Then $dsn &= ";pwd=" & GUICtrlRead($pass)
        $adoCon = ObjCreate("ADODB.Connection")
        If Not @error Then
            $adoCon.Open($dsn)
            If Not @error Then
                GUICtrlSetState($bt_conn  , $GUI_DISABLE)
                GUICtrlSetState($bt_discon, $GUI_ENABLE)
                GUICtrlSetState($bt_run, $GUI_ENABLE)
                GUICtrlSetData($l_statusbar,"Connected to " & $dsnraw)
                GUICtrlSetState($tab_query, $GUI_SHOW); show query tab
                GUICtrlSetState($ed_qry, $GUI_FOCUS); select edit field
            EndIf
        EndIf
    EndIf
EndFunc

Func _Disconnect()
    $adoCon.Close
    GUICtrlSetState($bt_conn, $GUI_ENABLE)
    GUICtrlSetState($bt_discon, $GUI_DISABLE)
    GUICtrlSetState($bt_run, $GUI_DISABLE)
    GUICtrlSetData($l_statusbar, "Disconnected")
EndFunc

Func _RunQuery()
    Local $qry = GUICtrlRead($ed_qry)
    
    If $qry == "" Then Return
    GUICtrlSetState($tab_result, $GUI_SHOW); show result tab
    GuiSwitch($gui, $tab_result)
    GuiCtrlDelete($result)
    ODBCquery($qry)
    GUICtrlCreateTabItem("")
    GUICtrlSetState($result, $GUI_FOCUS)
    GUISetState (@SW_SHOW, $gui)
EndFunc

Func _ClearQuery()
    GUICtrlSetData($ed_qry, ""); clear data
    GUICtrlSetState($ed_qry, $GUI_FOCUS)
EndFunc

Func _NewQuery()
    GUICtrlSetState($tab_query, $GUI_SHOW ); show query tab
    GUICtrlSetState($ed_qry, $GUI_FOCUS ); select edit field
EndFunc

Func _Tab()
    Switch GUICtrlRead($tab)
        Case 0; connections tab
            GUICtrlSetState($bt_conn, $GUI_DEFBUTTON)
        Case 1; query tab
            GUICtrlSetState($tab_query, $GUI_SHOW ); show query tab
            GUICtrlSetState($ed_qry, $GUI_FOCUS ); select edit field
            GUICtrlSetState($bt_run, $GUI_DEFBUTTON)
        Case 2; results tab
            GUICtrlSetState($bt_save, $GUI_DEFBUTTON)
    EndSwitch
EndFunc

Func _SaveResults()
    Local $line = "", $ret, $i
    
    $ret = _GUICtrlListView_GetItemCount($result)
    If $ret > 0 Then
        For $i = 0 To $ret - 1
            $line &= _GUICtrlListView_GetItemTextString($result, $i)
        Next
        FileWrite(@DesktopDir & "\query.log", $line & @CRLF & @CRLF)
    EndIf
EndFunc

Func ODBCsources($h_controlID, $s_RegEntry, $s_Type)
    Local $s_List, $i_dsncount = 1, $s_VarNm, $s_Value
    
    While 1
        $s_VarNm = RegEnumVal($s_RegEntry,  $i_dsncount)
        If @error Then ExitLoop
        $s_Value = RegRead($s_RegEntry, $s_Varnm)
        While 1
            $s_VarNm = RegEnumVal($s_RegEntry,  $i_dsncount)
            If @error Then ExitLoop
            $s_Value = RegRead($s_RegEntry, $s_Varnm)
            GUICtrlCreateListViewItem($s_VarNm & "|" & $s_Type & "|" & $s_Value, $h_controlID)
            $i_dsncount += 1
        WEnd
        _GUICtrlListView_SetColumnWidth($dsn_list, 0, $LVSCW_AUTOSIZE_USEHEADER)
        _GUICtrlListView_SetColumnWidth($dsn_list, 1, $LVSCW_AUTOSIZE_USEHEADER)
        _GUICtrlListView_SetColumnWidth($dsn_list, 2, $LVSCW_AUTOSIZE_USEHEADER)
    WEnd
EndFunc

Func ODBCquery($s_Qry)
    Local $result, $cmboVal = "", $adoSQL, $adoRs, $cols = "", $n, $colum, $i
    
    $adoSQL = StringReplace($s_Qry, "EVENT_NAME_TOKEN", StringStripWS(GUICtrlRead($evt_name), 8), 0, 1)
    $adoRs = ObjCreate("ADODB.Recordset")
    $adoRs.CursorType = 2
    $adoRs.LockType = 3
    $adoRs.Open($adoSQL, $adoCon)

    With $adoRs
    ; Get information about Fields collection
        For $n = 0 To .Fields.Count - 1
           $cols = $cols & .Fields($n).Name & "|"
        Next
        $result = GUICtrlCreateListView($cols, 20, 50, 550, 350, $LVS_REPORT, $LVS_EX_GRIDLINES)
        GUICtrlSetResizing(-1, $GUI_DOCKBORDERS)
        
        If .RecordCount Then
            While Not .EOF
                For $colum = 0 To .Fields.Count - 1
                    $cmboVal = $cmboVal & .Fields($colum).Value & "|"
                Next
                $cmboVal = StringTrimRight($cmboVal, 1)
                GUICtrlCreateListViewItem($cmboVal, $result)
                $cmboVal = ""
                .MoveNext
            WEnd
            For $i = 0 To _GUICtrlListView_GetColumnCount($result)
                _GUICtrlListView_SetColumnWidth($result, $i, $LVSCW_AUTOSIZE_USEHEADER)
            Next
        EndIf
    EndWith
    $adoRs.Close
EndFunc

; This is my custom defined error handler
Func MyErrFunc()
    MsgBox(0 + 48, "COM Error", "err.description is: "  & @TAB & $oMyError.description & @CRLF & _
                    "err.windescription:"    & @TAB & $oMyError.windescription & @CRLF & _
                    "err.number is: "        & @TAB & hex($oMyError.number,8)  & @CRLF & _
                    "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
                    "err.scriptline is: "    & @TAB & $oMyError.scriptline   & @CRLF & _
                    "err.source is: "        & @TAB & $oMyError.source       & @CRLF & _
                    "err.helpfile is: "    & @TAB & $oMyError.helpfile     & @CRLF & _
                    "err.helpcontext is: "  & @TAB & $oMyError.helpcontext _
                    )
    SetError(1)
Endfunc

Func _PathGetRelative($sFrom, $sTo)
    Local $asFrom, $asTo, $iDiff, $sRelPath, $i
    
    If StringRight($sFrom, 1) <> "\" Then $sFrom &= "\"; add missing trailing \ to $sFrom path
    If StringRight($sTo, 1) <> "\" Then $sTo &= "\"; add trailing \ to $sTo
    If $sFrom = $sTo Then Return SetError(1, 0, StringTrimRight($sTo, 1)); $sFrom equals $sTo
    $asFrom = StringSplit($sFrom, "\")
    $asTo = StringSplit($sTo, "\")
    If $asFrom[1] <> $asTo[1] Then Return SetError(2, 0, StringTrimRight($sTo, 1)); drives are different, rel path not possible
; create rel path
    $i = 2
    $iDiff = 1
    While 1
        If $asFrom[$i] <> $asTo[$i] Then
            $iDiff = $i
            ExitLoop
        EndIf
        $i += 1
    WEnd
    $i = 1
    $sRelPath = ""
    For $j = 1 To $asTo[0]
        If $i >= $iDiff Then
            $sRelPath &= "\" & $asTo[$i]
        EndIf
        $i += 1
    Next
    $sRelPath = StringTrimLeft($sRelPath, 1)
    $i = 1
    For $j = 1 To $asFrom[0]
        If $i > $iDiff Then
            $sRelPath = "..\" & $sRelPath
        EndIf
        $i += 1
    Next
    If StringRight($sRelPath, 1) == "\" Then $sRelPath = StringTrimRight($sRelPath, 1); remove trailing \
    Return $sRelPath
EndFunc;  ==> _PathGetRelative

Sign In

Direct SQL access in autoit (beta)

Recommended Posts

dougie

Trystian

Kuliklaus

memnon

Kuliklaus

dougie

SvenP

dougie

Guest BinaryVision

Guest BinaryVision

Teldin

boomingranny

wraithdu

wraithdu

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Browse

AutoIt Resources

Release

Beta