Jump to content

_SQL.au3. ADODB.Connection


Recommended Posts

I found a small bug in _SQL_ExcelConnect() which produces the error "Could not find installable ISAM." After some searching, I found the answer at http://www.connectionstrings.com/excel - the "Extended Properties" values in the connection string need to be quoted.

IE. Change the line "Extended Properties=Excel 8.0;HDR=" & $HDR & ";")

to "Extended Properties='Excel 8.0;HDR=" & $HDR & "';")

Thanks for the great UDF,

Carl

Thank you Carl :blink:

Link to post
Share on other sites
  • 1 month later...
  • Replies 131
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Popular Posts

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 someo

I got the solution. The problem for i am getting the error message is we have upgraded our SQL server to 2008 version.The Function _SQL_Connect() is not suppporting SQL2008. so we need to modify the f

dear @StefanoVR 1. please read: >How to post code on the forum 2. Function header should be before function declaration ; #FUNCTION# ======================================================

Posted Images

Hello, i use _sql.au3 and i have problem with timeoutcommand.

#include <_sql.au3>

#include <array.au3>

#include <Excel.au3>

Opt ("trayIconDebug",1)

Global $oMyError = ObjEvent("AutoIt.Error","MyErrFunc") ; Initialize a COM error handler

Func MyErrFunc()

Msgbox(0,"AutoItCOM Test","info!" & @CRLF & @CRLF & _

"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 )

Endfunc

$oADODB = _SQL_Startup()

_sql_Connect(-1,"server","dbname","log","pass")

GlobAL $aData,$iRows,$iColumns

_SQL_CommandTimeout(-1,"6000000")

_SQL_ConnectionTimeout(-1,"6000000")

$iRval = _SQL_GetTable2D(-1,"select * from points;",$aData,$iRows,$iColumns)

_arrayDisplay($aData,"2D (" & $iRows & " Rows) (" & $iColumns & " Columns)" )

Sometimes this script is working! but often i have this error:

[Microsoft][ODBC SQL Server Driver]Sessiontimeout.

maybe i use this function not correct?

_SQL_CommandTimeout(-1,"6000000")

_SQL_ConnectionTimeout(-1,"6000000")

Link to post
Share on other sites
  • 1 month later...

I wonder what I'm doing wrong -- $SQL_OK is not 0 after a successful _SQL_Execute statement.

Simple code example:

(connected to the DB, running error handler, _sql.au3 included (latest version, I think))

$strsql="UPDATE Table1 Set Complete=" & 0 &" Where Transaction_ID=" & Int($Ar_trans[$n][0]) & ";";

$iRval = _SQL_Execute($DB, $strsql)

If $iRval= $SQL_OK then msgbox(0,"Hi",$iRval)

Even though the exec statement succeeds (the table is updated), it returns a blank string (not zero).

It works to check to see if $iRval=$SQL_Error, so I can make my code work, but I wonder if this is a bug in _sql.au3.

By the way, thanks for this fantastic udf. I had a script written for sqlite and this allowed me to change the entire script over to use with Sql express in a day or so. Thanks!

Link to post
Share on other sites
  • 3 weeks later...

First of all, thanks for this! I have a question. Can this be used with windows domain accounts instead of just SQL server accounts? I have no problem using _SQL_Connect with the SQL account 'sa' specified, but if I try to use a domain user that has access to SQL server, I get a connection error.

Link to post
Share on other sites
  • 2 months later...

Thanks for the _sql.au3 here gos my contribution for the _sql.au3

Getting TABLE NAME From the Data BASE

New Function _SQL_GetTableName() for SQL

; #FUNCTION# ===================================================================
; Name ..........: _SQL_GetTableName()
; Description ...: Get Table List Of Open Data Base
; Syntax.........:  _SQL_GetTableName([ $hConHandle = -1[,$Type = "TABLE" ]])
; Parameters ....: $hConHandle - An Open Database, Use -1 To use Last Opened Database
;                   $Type - Table Type  "TABLE" (Default), "VIEW", "SYSTEM TABLE", "ACCESS TABLE"
;                   $Type = "*" - Return All Tables in a  Array2D  $aTable[n][2]  $aTable[n][0] = Table Name $aTable[n][1] = Table  Type
;
; Return values .: On Success  - Returns a 1D Array Of Table Names / 2D Array is $Type = "*"
;               On Failure  - Returns $SQL_ERROR and $SQLErr is set.
;               .Use _SQL_GetErrMsg() to get text error information
; Author ........: Elias Assad Neto
; Modified ......:
; Remarks .......:
; Related .......:
; Link ..........;
; Example .......; no
; ==============================================================================
Func _SQL_GetTableName($hConHandle = -1, $Type = "TABLE")

    $SQLErr = ""
    If $hConHandle = -1 Then $hConHandle = $SQL_LastConnection

    Local $rs = $hConHandle.OpenSchema(20) ; adSchemaTables = 20

    If Not IsObj($rs) Then Return SetError($SQL_ERROR, 0, $SQL_ERROR); The Data Base is Not Open

    Local $oField = $rs.Fields("TABLE_NAME")
    local $aTable

    If $Type = "*" Then ; All Table
        Do ;Check for a user table object
            If UBound($aTable) = 0 Then
                Dim $aTable[1][2]
            Else
                ReDim $aTable[UBound($aTable)+1][2]
            EndIf
            $aTable[UBound($aTable)-1][0] = $oField.Value
            $aTable[UBound($aTable)-1][1] = $rs.Fields("TABLE_TYPE" ).Value
            $rs.MoveNext
        Until $rs.EOF
    Else ; Selected Table
        Do ;Check for a user table object
            If $rs.Fields("TABLE_TYPE" ).Value = $Type Then
                If UBound($aTable) = 0 Then
                    Dim $aTable[1]
                Else
                    ReDim $aTable[UBound($aTable)+1]
                EndIf
                $aTable[UBound($aTable)-1] = $oField.Value
            EndIf
            $rs.MoveNext
        Until $rs.EOF
    EndIf

    If UBound($aTable) = 0 Then
        $SQLErr = "Table Not Found"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR) ; Table Not Found
    EndIf

    Return $aTable
EndFunc   ;==>GetTableName

SQL GUI using "_sql.au3"

Requires : _sql.au3 Code can Be Download at the beginning of the topic

AdispEX.au3 Code is in the next code pane

;===============================================================================
; Program Name:     SQL AutoIT
; Description:      Graphical User Interfaze using _sql.au3
;
; Requirement(s):   AutoIt 3.3.x.x,  AdispEX.au3
; Return Value(s):  None
; Author(s):        Elias Assad Neto
; Version:          1.0
; Date Crated:      2010/12/17
; Modified:
;===============================================================================

#include <GuiEdit.au3>
#include <GuiComboBox.au3>

#include "_sql.au3"
#include "AdispEX.au3"  ; Adisp com recuros de receber o cabeçario por uma Matriz em $saTitle

Opt("TrayAutoPause", 0) ;0=no pause, 1=Pause
Opt("GUICloseOnESC", 0) ;1=ESC  closes, 0=ESC won't close

; Mouse cursor Constants
            ;   0           1       2           3       4       5           6           7           8           9             10            11          12      13          14          15              16              17      18      19          20          21          22
;Global Enum $crDefault, $crNone, $crArrow,  $crCross,  $crIBeam, $crSize, $crSizeNESW, $crSizeNS, $crSizeNWSE,  $crSizeWE,  $crUpArrow, $crHourGlass, $crDrag, $crNoDrop, $crHSplit,  $crVSplit,  $crMultiDrag, $crSQLWait, $crNo, $crAppStart, $crHelp,  $crHandPoint,  $crSizeAll
Global $crAppStart =  1, $crArrow =  2,  $crCross =  3,  $crDrag =  2, $crHandPoit =  0, $crHelp =  4,  $crHouerGlass =  15,  $crHSplit =  2, $crIBeam =  5, $crMultiDrag =  2,  $crNo =  7, $crNoDrop  =  7, $crSizeAll =  9, $crSizeNESW =  10,  $crSizeNS =  11, $crSizeNWSE  =  12, $crSizeWE =  13, $crSQLWait =  2,  $crUpArrow =  14, $crVSplit  =  2
Global Const $crCustom = 99 ;Custom icon specified by the MouseIcon property


Global $Delim = "¬&~"
Global $Title = "SQL UDF GUI"
Global $Connction, $Server, $Db, $Username, $Password, $sFileINI ; INI Variabels
Global $lastProfile = "", $ActualProfile = ""
Global $oQuery, $aResults ; Query variables
Global $OpenFilter

Global $dgb_t, $dgb_s





#include <ButtonConstants.au3>
#include <ComboConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
Global $_h = 70 ; Para aumentar e diminuir tamanho das janelas entre $eSQL e $eResult
#region  ###  START Koda GUI section ###  Form=C:\Drive_D\Elias\My  Documents\Elias\Projects\AutoIT3_3.3.0.0\HSUD_IncVazios\SQL_Form.kxf
$SQL_Form  =  GUICreate($Title, 670, 582, 192, 124,  BitOR($WS_MAXIMIZEBOX,  $WS_MINIMIZEBOX, $WS_SIZEBOX, $WS_THICKFRAME,  $WS_SYSMENU, $WS_CAPTION,  $WS_OVERLAPPEDWINDOW, $WS_TILEDWINDOW,  $WS_POPUP, $WS_POPUPWINDOW,  $WS_GROUP, $WS_TABSTOP, $WS_BORDER,  $WS_CLIPSIBLINGS))
$MenuView = GUICtrlCreateMenu("&View")
$mTablesALL = GUICtrlCreateMenuItem("&ALL Tables", $MenuView)
$mTable = GUICtrlCreateMenuItem("&Tables", $MenuView)
$mTableViews = GUICtrlCreateMenuItem("&Views Tables", $MenuView)
$mTableSystem = GUICtrlCreateMenuItem("&System Tables", $MenuView)
$mTabelsOthers = GUICtrlCreateMenuItem("&Other Tables", $MenuView)
$MenuHelp = GUICtrlCreateMenu("&Help")
$mSQLTutorial = GUICtrlCreateMenuItem("S&QL Tutorial  F1", $MenuHelp)
$mAbout = GUICtrlCreateMenuItem("&About", $MenuHelp)
$lConection = GUICtrlCreateLabel("Connection", 8, 9, 58, 17)
GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT)
$cSQL = GUICtrlCreateCombo("", 65, 6, 145, 25, BitOR($CBS_DROPDOWNLIST, $CBS_AUTOHSCROLL))
GUICtrlSetData(-1, "SQL Connection|SQL JetConnect (file)|SQL AccessConnect (file)", "SQL Connection")
GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT)
$lServer = GUICtrlCreateLabel("Server", 8, 32, 35, 17)
GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT)
GUICtrlSetTip(-1, "Server /  IP")
$iServer = GUICtrlCreateInput("", 65, 30, 594, 21)
GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKRIGHT + $GUI_DOCKTOP + $GUI_DOCKHEIGHT)
$bFile = GUICtrlCreateButton("&File", 7, 28, 51, 25, $WS_GROUP)
GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT)
GUICtrlSetState(-1, $GUI_HIDE)
$lDB = GUICtrlCreateLabel("Db", 8, 56, 18, 17)
GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT)
GUICtrlSetTip(-1, "Data Base Optional")
$iDb = GUICtrlCreateInput("", 65, 54, 594, 21)
GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKRIGHT + $GUI_DOCKTOP + $GUI_DOCKHEIGHT)
$lUserName = GUICtrlCreateLabel("UserName", 8, 80, 54, 17)
GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT)
$iUserName = GUICtrlCreateInput("", 65, 78, 218, 21)
GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT)
$lPassword = GUICtrlCreateLabel("Password", 8, 104, 50, 17)
GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT)
$iPassword = GUICtrlCreateInput("", 65, 102, 218, 21)
GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT)
$lProfile = GUICtrlCreateLabel("Profile", 240, 10, 33, 17)
GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT)
$cProfile = GUICtrlCreateCombo("", 280, 7, 145, 25, BitOR($CBS_DROPDOWNLIST, $CBS_AUTOHSCROLL, $CBS_SORT))
GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT)
$bSaveDeleteProfile = GUICtrlCreateButton("&Save Profile", 437, 4, 75, 25, $WS_GROUP)
GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT)
$bOpenCloseSQL = GUICtrlCreateButton("&Open SQL", 295, 100, 67, 25, $WS_GROUP)
GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT)
$lDouble = GUICtrlCreateLabel("DoubleClick Execute SQL at cursor", 496, 112, 171, 17)
GUICtrlSetResizing(-1, $GUI_DOCKRIGHT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT)
$bSQLExecute = GUICtrlCreateButton("SQL&Execute", 592, 132, 75, 25, $WS_GROUP)
GUICtrlSetResizing(-1, $GUI_DOCKRIGHT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT)
GUICtrlSetTip(-1, "Executa a linha do cursor ou a parte selecionada  Atalho:Ctrl+Enter")
$bTables = GUICtrlCreateButton("&Tables", 592, 160, 75, 25, $WS_GROUP)
GUICtrlSetResizing(-1, $GUI_DOCKRIGHT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT)
GUICtrlSetTip(-1, "Mostra as Tabelas da Base de Dados")
$bView = GUICtrlCreateButton("&Views", 592, 188, 75, 25, $WS_GROUP)
GUICtrlSetResizing(-1, $GUI_DOCKRIGHT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT)
GUICtrlSetTip(-1, "Mostra as Views da Base de Dados")
$eSQL  =  GUICtrlCreateEdit("", 0, 132, 589, 89 + $_h,  BitOR($ES_AUTOVSCROLL,  $ES_AUTOHSCROLL, $ES_NOHIDESEL, $ES_WANTRETURN,  $WS_HSCROLL,  $WS_VSCROLL))
GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKRIGHT + $GUI_DOCKTOP + $GUI_DOCKHEIGHT)
$eResult = GUICtrlCreateEdit("", 0, 224 + $_h, 669, 337 - $_h)
GUICtrlSetResizing(-1, $GUI_DOCKTOP + $GUI_DOCKBOTTOM)
GUISetState(@SW_SHOW)
#endregion ### END Koda GUI section ###


Dim $SQL_Form_AccelTable[2][2] = [["{F1}", $mSQLTutorial],["^{ENTER}", $bSQLExecute]]
GUISetAccelerators($SQL_Form_AccelTable)

$Font = "Consolas"
$FSize = 10
GUICtrlSetFont($eSQL, $FSize, 400, 0, $Font)
GUICtrlSetFont($eResult, $FSize, 400, 0, $Font)
;GUICtrlSetColor(-1, 0x0000FF)

;If Not @Compiled Then GUISetIcon("SQL48x48.ico")

OnAutoItExitRegister("Saida")

_SQL_RegisterErrorHandler()
_SQL_Startup()
$GuiSize = WinGetPos($SQL_Form)
GUIRegisterMsg($WM_GETMINMAXINFO, "MY_WM_GETMINMAXINFO")
;GUIRegisterMsg($WM_GETMINMAXINFO, "") ;Unregister  ATENÇÃO Chamar essa Função ao Fechar a Janela

$SQLDebugTime = 0
$SQLTraceFlg = 0
$SQLTraceDisp = 0
$LDclickt = 0
$SQLTrace = ""

LeIni()

While 1
    $aMsg = GUIGetMsg(1)
    If $SQLTraceDisp And $SQLTrace <> "" Then
        _GUICtrlEdit_AppendText($eResult, $SQLTrace)
        $SQLTrace = ""
    EndIf
    $nMsg = $aMsg[0]
    If 0 And $nMsg <> 0 And $nMsg <> -11 Then
        ConsoleWrite("Control ID: " & $aMsg[0] & @CRLF)
        ;ConsoleWrite("W hWand   : " & $aMsg[1] & @CRLF)
        ;ConsoleWrite("C hWand   : " & $aMsg[2] & @CRLF)
        ;ConsoleWrite("X        : " & $aMsg[3] & @CRLF)
        ;ConsoleWrite("Y        : " & $aMsg[4] & @CRLF)
    EndIf
    Switch $nMsg
        Case 0 ; Nada
        Case -11 ; Mouse Move
        Case -8 ; Botão Esquerdo levantou
            If TimerDiff($LDclickt) < 500 Then ; Duble Click Esquerdo no Form
                $aMouse = GUIGetCursorInfo() ; 0-X 1-Y 2-Primary down  (1 if  pressed, 0 if not pressed) 4-ID of the control that the mouse  cursor is  hovering over (or 0 if none)
                If $aMouse[4] = $eSQL Then SQLExecute() ; Se DuploClick Sobre o Controle
            EndIf
            $LDclickt = TimerInit()
        Case $GUI_EVENT_CLOSE
            Exit
        Case $mTablesALL
            GUICtrlSetData($eResult, "") ; Limpa $eResult
            GetTableNames("") ; Mostra só as Todas as Tabelas
        Case $mTable
            GUICtrlSetData($eResult, "") ; Limpa $eResults
            GetTableNames("TABLE") ; Mostra só as Tabelas com Type = "TABLE"
        Case $mTableViews
            GUICtrlSetData($eResult, "") ; Limpa $eResults
            GetTableNames("VIEW") ; Mostra só as Tabelas com Type = "VIEW"
        Case $mTableSystem
            GUICtrlSetData($eResult, "") ; Limpa $eResults
            GetTableNames("SYSTEM TABLE") ; Mostra só as Tabelas com Type = "SYSTEM TABLE"
        Case $mTabelsOthers
            GUICtrlSetData($eResult, "") ; Limpa $eResults
            GetTableNames("TABLE;VIEW;SYSTEM TABLE", 1) ; Mostra só as Tabelas com Type <> "TABLE;VIEW;SYSTEM TABLE"
        Case $mSQLTutorial
            ShellExecute("http://www.w3schools.com/sql/default.asp") ; em protugues
        Case $cSQL
            GUIShowHideOpen() ; Atualiza dados e GUI para cada Tipo de conexão
            CkProfile()
        Case $mAbout
            About()
        Case $bFile
            $Server = FileOpenDialog($Title & " Escolha uma Base de Dados", "", $OpenFilter, 1 + 2)
            GUICtrlSetData($iServer, $Server)
            CkProfile()
        Case $iServer
            CkProfile()
        Case $bFile
            CkProfile()
        Case $iDb
            CkProfile()
        Case $iUserName
            CkProfile()
        Case $iPassword
            CkProfile()
        Case $cProfile
            SaveReadProfile() ; Le o Profile
        Case $bSaveDeleteProfile
            SaveDeleteProfile()
        Case $bOpenCloseSQL
            OpenCloseSQL()
        Case $bSQLExecute
            ;$st = GUICtrlGetState ($bSQLExecute)
            ;ConsoleWrite("$bSQLExecute: " & $bSQLExecute &  "    $eSQL: " & $eSQL & "    State: " & Hex($st) &  @CRLF)
            SQLExecute(1)
        Case $bTables
            GUICtrlSetData($eResult, "") ; Limpa $eResults
            GetTableNames("TABLE") ; Mostra só as Tabelas com Type = "TABLE"
        Case $bView
            GUICtrlSetData($eResult, "") ; Limpa $eResults
            GetTableNames("VIEW") ; Mostra só as Tabelas com Type = "VIEW"
        Case Else
            ;
    EndSwitch
WEnd

Func  Saida()  ; Cahmada quando se exeuta um Exit ou o programa é terminado  por algum  motivo, iniciada por OnAutoItExitRegister("Saida")
    GUIRegisterMsg($WM_GETMINMAXINFO, "") ; Unregister  ATENÇÃO Chamar essa Função ao Fechar a Janela
    $oQuery = 0
    $aResults = 0
    _SQL_Close()
EndFunc   ;==>Saida

Func  MY_WM_GETMINMAXINFO($hWnd,  $Msg, $wParam, $lParam) ; Limita o tamanho  minimo e maximo da janela  $GuiSize = WinGetPos(GUI) tamanho inicial
    Local $minmaxinfo = DllStructCreate("int;int;int;int;int;int;int;int;int;int", $lParam)
    DllStructSetData($minmaxinfo, 7, $GuiSize[2] - 132) ; min X
    DllStructSetData($minmaxinfo, 8, $GuiSize[3] - 200) ; min Y
    If 0 Then ; se quiser limitar tamanho maximo da janela
        DllStructSetData($minmaxinfo, 9, $GuiSize[2] + 300) ; max X
        DllStructSetData($minmaxinfo, 10, $GuiSize[3] + 300) ; max Y
    EndIf
    Return 0
EndFunc   ;==>MY_WM_GETMINMAXINFO

Func GUIShowHideOpen($Open = False)
    $Connction = GUICtrlRead($cSQL)
    If $Open Then
        $Server = GUICtrlRead($iServer)
        If $Server <> "" Then
            GUICtrlSetData($eResult, StringFormat("Opening SQL : %s\r\n", $Server))
        Else
            Return $SQL_ERROR
        EndIf
    EndIf
    $lProf = GUICtrlRead($cSQL)
    Select
        Case $Connction = "SQL Connection"
            If $Open Then
                $Db = GUICtrlRead($iDb)
                $Username = GUICtrlRead($iUserName)
                $Password = GUICtrlRead($iPassword)
                Return _SQL_Connect(-1, $Server, $Db, $Username, $Password)
            EndIf
            GUICtrlSetState($bFile, $GUI_HIDE)
            GUICtrlSetState($lServer, $GUI_SHOW)
            GUICtrlSetState($iDb, $GUI_ENABLE)
            GUICtrlSetState($iUserName, $GUI_ENABLE)
            GUICtrlSetState($iPassword, $GUI_ENABLE)
        Case $Connction = "SQL JetConnect (file)" Or $Connction = "SQL AccessConnect (file)"
            If $Connction = "SQL JetConnect (file)" Then
                If $Open Then Return _SQL_JetConnect(-1, $Server)
                $OpenFilter = "JetConnect (*.*)"
            Else
                If $Open Then Return _SQL_AccessConnect(-1, $Server)
                $OpenFilter = "Access (*.mdb)"
            EndIf
            GUICtrlSetState($lServer, $GUI_HIDE)
            GUICtrlSetState($bFile, $GUI_SHOW)
            GUICtrlSetState($iDb, $GUI_DISABLE)
            GUICtrlSetData($iDb, "")
            GUICtrlSetState($iUserName, $GUI_DISABLE)
            GUICtrlSetData($iUserName, "")
            GUICtrlSetState($iPassword, $GUI_DISABLE)
            GUICtrlSetData($iPassword, "")
    EndSelect
    GUICtrlSetData($eResult, "")
    SaveReadQuery()
EndFunc   ;==>GUIShowHideOpen


Func GetSQLLine($fsel = 0) ; Seleciona a linha de $eSQL onde esta o cursor e retorna a mesma
    $eSQLSel = _GUICtrlEdit_GetSel($eSQL) ; Pega Seleção em Arrar[2]
    $S = GUICtrlRead($eSQL)
    If _GUICtrlEdit_CanUndo($eSQL) Then SaveReadQuery(True) ; Atualiza Query
    If $fsel And $eSQLSel[0] < $eSQLSel[1] Then ; Se $fsel e algo selecionado, retorna a seleção
        $S = StringMid($S, $eSQLSel[0] + 1, $eSQLSel[1] - $eSQLSel[0])
        Return $S
    EndIf
    $aS = StringSplit($S, "", 2) ; monta a Matriz de $eSQL em $aS
    If $eSQLSel[0] >= UBound($aS) Then
        $eSQLSel[0] = UBound($aS) - 1
    EndIf
    If $eSQLSel[0] < 0 Then Return "" ; Nada Selecionado
    If $eSQLSel[0] < UBound($aS) Then
        $i = $eSQLSel[0]
        If $i > 0 Then
            For $i = $eSQLSel[0] - 1 To 1 Step -1 ; Acha o Inicio Da linha
                If $aS[$i] = @LF Or $aS[$i] = @CR Then
                    $i += 1
                    ExitLoop
                EndIf
            Next
        EndIf
        For $j = $eSQLSel[0] To UBound($aS) - 1 ; Acha o Fim da Linha
            If $aS[$j] = @LF Or $aS[$j] = @CR Then
                ExitLoop
            EndIf
        Next
    Else
        $i = 0
        $j = 0
    EndIf
    _GUICtrlEdit_SetSel($eSQL, $i, $j)
    $S = StringMid($S, $i + 1, $j - $i + 1)
    ;$S = StringStripWS($S,3)
    Return $S
EndFunc   ;==>GetSQLLine


Func OpenCloseSQL()
    If GUICtrlRead($bOpenCloseSQL) = "&Open SQL" Then
        GUICtrlSetData($eResult, "") ; Limpa janela de resultados
        If GUIShowHideOpen(True) = $SQL_OK Then
            GUICtrlSetData($bOpenCloseSQL, "&Close SQL")
            _GUICtrlEdit_AppendText($eResult, "OK Base Aberta" & @CRLF)
            For $i = $lConection To $bOpenCloseSQL - 1
                $R = GUICtrlSetState($i, $GUI_DISABLE)
            Next
            GetTableNames("TABLE")
        Else
            _GUICtrlEdit_AppendText($eResult, "ERRO ao tentar Abrir a Base de Dados" & @CRLF)
        EndIf
    Else
        $oQuery = 0
        $aResults = 0
        If _SQL_Close() = $SQL_OK Then ; fecha Conexão ADO
            GUICtrlSetData($bOpenCloseSQL, "&Open SQL")
            For $i = $lConection To $bOpenCloseSQL - 1
                $R = GUICtrlSetState($i, $GUI_ENABLE)
            Next
            GUIShowHideOpen()
            _SQL_Startup() ; Inicia novo Objeto ADO
        EndIf
    EndIf
EndFunc   ;==>OpenCloseSQL

Func GetTableNames($Type = "", $neq = 0) ; Pega todas as tabelas da base de dados e lista em $eResult
    $rs = $SQL_LastConnection.OpenSchema(20) ; adSchemaTables = 20
    If not IsObj($rs) Then
        If IsObj($MSSQLObjErr) Then $SQLErrDesc = $MSSQLObjErr.description
        _GUICtrlEdit_AppendText($eResult, @CRLF & "ERRO: ao Ler Tabelas " & $SQLErrDesc & @CRLF)
        Return ; Evita que oprograma termine caso a base não esteja aberta.
    EndIf
    $oField = $rs.Fields("TABLE_NAME")
    $S = ""
    Dim $aTables[10000][2]
    $aTables[0][0] = "Table List"
    $aTables[0][1] = "TYPE"
    $i = 1
    $MinSizeTb = 0
    $MinSizeTy = 0
    $All = $Type == ""
    $Type = ";" & $Type & ";"
    Do ;Check for a user table object
        If $All Or BitXOR(StringInStr($Type, ";" & $rs.Fields("TABLE_TYPE" ).Value & ";") > 0, $neq) Then
            $aTables[$i][0] = $oField.Value
            $aTables[$i][1] = $rs.Fields("TABLE_TYPE" ).Value
            If StringLen($aTables[$i][0]) > $MinSizeTb Then $MinSizeTb = StringLen($aTables[$i][0])
            If StringLen($aTables[$i][1]) > $MinSizeTy Then $MinSizeTy = StringLen($aTables[$i][1])
            $i += 1
        EndIf
        $rs.MoveNext
    Until $rs.EOF Or $i >= 10000
    $fM = GUICtrlRead($eSQL) == ""
    Local $T = ""
    Local $Q = ""
    If $fM Then $T = "Tables: "
    For $j = 0 To $i - 1
        If $fM And $j > 0 Then
            $T &= $aTables[$j][0] & ", "
            $Q &= "SELECT Count(*) FROM " & $aTables[$j][0]  &  @CRLF & "SELECT * FROM " & $aTables[$j][0] & @CRLF  &  @CRLF
        EndIf
        $S &= StringFormat("%-"  &  $MinSizeTy & "s %-" & $MinSizeTb & "s\r\n",  $aTables[$j][1],  $aTables[$j][0])
    Next
    _GUICtrlEdit_AppendText($eResult, $S & @CRLF)
    If $fM Then
        $S = StringMid($T, 1, StringLen($T) - 2) & @CRLF & @CRLF & $Q
        _GUICtrlEdit_AppendText($eSQL, $S)
    EndIf
EndFunc   ;==>GetTableNames

Func SQLExecute($fsel = 0)
    $sSQL = GetSQLLine($fsel)
    ;GUICtrlSetData($eResult,$sSQL)
    ;Return
    $oQuery = 0
    $aResults = 0
    GUICtrlSetData($eResult, "") ; Limpa janela de resultados
    If IsObj($SQL_LastConnection) Then
        GUISetCursor(15, 1) ; HourGlas
        _GUICtrlEdit_AppendText($eResult, "Execute: [" & $sSQL & "]" & @CRLF)
        ti("SQL_Execute (Delay):")
        $oQuery = _SQL_Execute(-1, $sSQL)
        $err = @error
        td()
        If $err Then
            Local $SQLErrDesc = ""
            If IsObj($MSSQLObjErr) Then $SQLErrDesc = $MSSQLObjErr.description
            _GUICtrlEdit_AppendText($eResult, @CRLF & "ERRO: ao Executar: " & $SQLErrDesc & @CRLF)
        Else
            Local $aNames
            ti("SQL_FetchNames (Delay):")
            If _SQL_FetchNames($oQuery, $aNames) = $SQL_OK Then
                td()
                Local $S = "Colunas: " ;& $aNames[0]
                For $i = 0 To UBound($aNames) - 1
                    $S &= "|" & $aNames[$i]
                Next
                $S &= "|"
                _GUICtrlEdit_AppendText($eResult, @CRLF & $S & @CRLF & @CRLF)
                ti("Monta Matriz 2D (Delay):")
                $aResults = $oQuery.GetRows()
                td()
                If UBound($aResults) = 1 And UBound($aResults, 2) = 1 Then
                    _GUICtrlEdit_AppendText($eResult, StringFormat("\r\nResult: [%s]\r\n\r\n", $aResults[0][0]))
                Else
                     _GUICtrlEdit_AppendText($eResult,  StringFormat("Matriz2D[%d][%d]\r\n",  UBound($aResults),  UBound($aResults, 2)))
                    Local $aTitulo[2]
                    $aTitulo[0] = "Resultado de: " & $sSQL
                    $aTitulo[1] = $aNames ; Cabeçalho
                    GUISetCursor() ; Normal
                    GUISetState(@SW_DISABLE)
                    Opt("GUICloseOnESC", 1) ;1=ESC  closes, 0=ESC won't close
                    _ADisplay($aResults, $aTitulo)
                    Opt("GUICloseOnESC", 0) ;1=ESC  closes, 0=ESC won't close
                    GUISetState(@SW_ENABLE)
                    WinActivate($SQL_Form)
                EndIf
            Else
                _GUICtrlEdit_AppendText($eResult, "ERRO: ao executar: _SQL_FetchNames($oQuery, $aNames)" & @CRLF)
            EndIf
        EndIf
    Else
        _GUICtrlEdit_AppendText($eResult, "ERRO: Base SQL não inicializado" & @CRLF)
    EndIf
    GUISetCursor() ; Normal
EndFunc   ;==>SQLExecute

#region INI
;Estrutura do INI
#cs
    [Init]
    lastProfile=xxxxxx
    [Profiles]
    ProfileName=Server\tDb\tUser\tPassword
    [Querys]
    ProfileName=Query\tQuery\t.....\tQuery
#ce


Func CkProfile($flg = False) ; Analisa se Profile alterado
    $ActualProfile = GUICtrlRead($cSQL) & $Delim  &  GUICtrlRead($iServer) & $Delim & GUICtrlRead($iDb) &  $Delim  & GUICtrlRead($iUserName) & $Delim &  GUICtrlRead($iPassword)
    If $flg Then
        $lastProfile = $ActualProfile
        GUICtrlSetData($bSaveDeleteProfile, "&Delete Profile")
    Else
        If $ActualProfile = $lastProfile Then
            GUICtrlSetData($bSaveDeleteProfile, "&Delete Profile")
            Return True
        Else
            If GUICtrlRead($bSaveDeleteProfile) = "&Delete Profile" Then _GUICtrlComboBox_SetCurSel($cProfile)
            GUICtrlSetData($bSaveDeleteProfile, "&Save Profile")
            Return False
        EndIf
    EndIf
EndFunc   ;==>CkProfile

Func SaveDeleteProfile()
    If GUICtrlRead($bSaveDeleteProfile) <> "&Save Profile" Then
        $Profile = GUICtrlRead($cProfile)
        If $Profile <> "" Then ; Delete Profile
            $FileINI = StringMid(@ScriptFullPath, 1, StringLen(@ScriptFullPath) - 4) & ".ini"
            IniDelete($FileINI, "Profiles", $Profile)
            IniDelete($FileINI, "Querys", $Profile)
            GUICtrlSetData($eSQL, "")
            GUICtrlSetData($eResult, "")
            SaveReadProfile(-1) ; Limpa os dados da tela
            LeIni()
        EndIf
    Else
        SaveReadProfile(True) ; Salva Profile
    EndIf
EndFunc   ;==>SaveDeleteProfile

Func SaveReadProfile($fSave = 0)
    CkProfile() ; Atualiza $lastProfile
    $FileINI = StringMid(@ScriptFullPath, 1, StringLen(@ScriptFullPath) - 4) & ".ini"
    If $fSave = 1 Then
        $Server = GUICtrlRead($iServer)
        $aServer = PathSplit($Server) ; Split FilePtah 0-ScriptFullPath 1-Drive 2-Path 3-Name 4-.ext
        $sProfileName = InputBox($Title, "Entre com o Nome do Perfil  Max 20  Char", StringMid($aServer[3] & $aServer[4], 1, 20), " 20")
        If @error = 0 And $sProfileName <> "" And $ActualProfile <> $lastProfile And $ActualProfile <> "" Then
            IniWrite($FileINI, "Init", "LastProfile", $sProfileName)
            $lastProfile = $ActualProfile
            IniWrite($FileINI, "Profiles", $sProfileName, $ActualProfile)
            _GUICtrlComboBox_ResetContent($cProfile)
            LeIni()
        Else
            MsgBox(0, $Title, "Profile não foi salvo")
        EndIf
        Return
    EndIf
    $selProfile = GUICtrlRead($cProfile)
    If $fSave = -1 Then $selProfile = "=Limpa"
    If $selProfile <> "" Then
        If $selProfile = "=Limpa" Then
            GUICtrlSetData($cProfile, "")
            $sProfile = GUICtrlRead($cSQL) & $Delim & $Delim & $Delim & $Delim
            GUICtrlSetData($bSaveDeleteProfile, "&Save Profile")
            $ActualProfile = ""
            $lastProfile = ""
            _GUICtrlComboBox_ResetContent($cProfile)
            GUICtrlSetData($eSQL, "")
            GUICtrlSetData($eResult, "")
        Else
            $sProfile = IniRead($FileINI, "Profiles", $selProfile, "")
        EndIf
        If $sProfile <> "" Then
            $aProfiles = StringSplit($sProfile, $Delim, 3)
            If UBound($aProfiles) = 5 Then
                If $selProfile = "=Limpa" Then
                    IniWrite($FileINI, "Init", "LastProfile", "")
                Else
                    IniWrite($FileINI, "Init", "LastProfile", $selProfile)
                EndIf
                ;ReDim $aProfiles[5] ; Pois o IniRead eliminas brancos a direita inclusive TAB's
                GUICtrlSetData($cSQL, $aProfiles[0])
                GUICtrlSetData($iServer, $aProfiles[1])
                GUICtrlSetData($iDb, $aProfiles[2])
                GUICtrlSetData($iUserName, $aProfiles[3])
                GUICtrlSetData($iPassword, $aProfiles[4])
                If $selProfile <> "=Limpa" Then CkProfile(True)
                GUIShowHideOpen()
            EndIf
        EndIf
    EndIf
EndFunc   ;==>SaveReadProfile

Func SaveReadQuery($fSave = False) ; Grava ou Le As Querys
    $FileINI = StringMid(@ScriptFullPath, 1, StringLen(@ScriptFullPath) - 4) & ".ini"
    $p = GUICtrlRead($cProfile)
    If $fSave Then
        If $p <> "" Then IniWrite($FileINI, "Querys", $p, StringReplace(GUICtrlRead($eSQL), @CRLF, $Delim)) ; Atualiza
    Else
        If $p <> "" Then  GUICtrlSetData($eSQL,  StringReplace(IniRead($FileINI, "Querys", $p,  ""), $Delim, @CRLF)) ;  Remonta para EditControl
    EndIf
EndFunc   ;==>SaveReadQuery

Func LeIni($flag = 0) ; Le todas as Inicializações
    GUICtrlSetData($eSQL, "")
    GUICtrlSetData($eResult, "")
    $FileINI = StringMid(@ScriptFullPath, 1, StringLen(@ScriptFullPath) - 4) & ".ini"
    Local $Profiles = ""
    $aProfiles = IniReadSection($FileINI, "Profiles")
    If @error = 0 And $aProfiles[0][0] > 0 Then
        $Profiles = $aProfiles[1][0] ; Key
        For $i = 2 To $aProfiles[0][0]
            $Profiles &= "|" & $aProfiles[$i][0] ; Key
        Next
    EndIf
    $lProfile = IniRead($FileINI, "Init", "lastProfile", "")
    GUICtrlSetData($cProfile, $Profiles, $lProfile)
    SaveReadProfile() ; Le o Profile selecionado
    If GUICtrlRead($cProfile) = "" Then
    EndIf
    SaveReadQuery() ; Le a Query do profile Selecionado
    CkProfile()
EndFunc   ;==>LeIni

Func WrtIni() ; Grava todas as Inicializações
    $FileINI = StringMid(@ScriptFullPath, 1, StringLen(@ScriptFullPath) - 4) & ".ini"
EndFunc   ;==>WrtIni


Func PathSplit($szPath) ; Split FilePtah 0-ScriptFullPath 1-Drive 2-Path 3-Name 4-.ext
    ; Set local strings to null (We use local strings in case one of the arguments is the same variable)
    Local $drive = ""
    Local $Dir = ""
    Local $fname = ""
    Local $ext = ""
    Local $pos

    ; Create an array which will be filled and returned later
    Local $array[5]
    $array[0] = $szPath; $szPath can get destroyed, so it needs set now

    ; Get drive letter if present (Can be a UNC server)
    If StringMid($szPath, 2, 1) = ":" Then
        $drive = StringLeft($szPath, 2)
        $szPath = StringTrimLeft($szPath, 2)
    ElseIf StringLeft($szPath, 2) = "\\" Then
        $szPath = StringTrimLeft($szPath, 2) ; Trim the \\
        $pos = StringInStr($szPath, "\")
        If $pos = 0 Then $pos = StringInStr($szPath, "/")
        If $pos = 0 Then
            $drive = "\\" & $szPath; Prepend the \\ we stripped earlier
            $szPath = ""; Set to null because the whole path was just the UNC server name
        Else
            $drive = "\\" & StringLeft($szPath, $pos - 1) ; Prepend the \\ we stripped earlier
            $szPath = StringTrimLeft($szPath, $pos - 1)
        EndIf
    EndIf

    ; Set the directory and file name if present
    Local $nPosForward = StringInStr($szPath, "/", 0, -1)
    Local $nPosBackward = StringInStr($szPath, "\", 0, -1)
    If $nPosForward >= $nPosBackward Then
        $pos = $nPosForward
    Else
        $pos = $nPosBackward
    EndIf
    $Dir = StringLeft($szPath, $pos)
    $fname = StringRight($szPath, StringLen($szPath) - $pos)

    ; If $szDir wasn't set, then the whole path must just be a file, so set the filename
    If StringLen($Dir) = 0 Then $fname = $szPath

    $pos = StringInStr($fname, ".", 0, -1)
    If $pos Then
        $ext = StringRight($fname, StringLen($fname) - ($pos - 1))
        $fname = StringLeft($fname, $pos - 1)
    EndIf

    ; Set the strings and array to what we found
    $array[1] = $drive
    $array[2] = $Dir
    $array[3] = $fname
    $array[4] = $ext
    Return $array
EndFunc   ;==>PathSplit

#endregion INI

Func ti($S = "TimerInit Scriptline: ", $ScriptLineNumber = @ScriptLineNumber) ; Inicializa Timer
    If StringInStr($S, "TimerInit Scriptline:") = 1 Then
        $dgb_s = $S & $ScriptLineNumber
    Else
        $dgb_s = $S
    EndIf
    $dgb_t = TimerInit()
EndFunc   ;==>ti

Func td($S = "TimerDiff Scriptline: ", $ScriptLineNumber = @ScriptLineNumber) ; Mostra Timer
    Local $tt = TimerDiff($dgb_t)
    If StringInStr($dgb_s, "TimerInit Scriptline:") = 1 And StringInStr($S, "TimerDiff Scriptline:") = 1 Then
        $S = $dgb_s & " to " & $S & $ScriptLineNumber
    ElseIf StringInStr($S, "TimerDiff Scriptline:") = 1 Then
        $S = $dgb_s
    EndIf
    If $tt < 1 Then
        $tt = Round($tt * 1000) & " µs"
    ElseIf $tt > 1000 Then ; segundos
        $tt = Round($tt / 1000, 2) & " seg"
    ElseIf $tt > 60000 Then ;minutos
        $seg = $tt / 1000 ; Segundos
        $min1 = $seg / 60
        $min = Int($min1)
        $seg = Int(($min1 * 100) - (Int($min1) * 100)) * 60 / 100
        $tt = StringFormat("%d:%05.2f min:seg", $min, $seg)
    Else ; ms
        $tt = Round($tt, 3) & " ms"
    EndIf
    _GUICtrlEdit_AppendText($eResult, $S & " " & $tt & @CRLF)
EndFunc   ;==>td

Func About()
    #region ### START Koda GUI section ###  Form=C:\Drive_D\Elias\My  Documents\Elias\Projects\AutoIT3_3.3.0.0\SQL_UDF\About_Form.kxf
    $About_Form = GUICreate("About_Form", 326, 238, 302, 218)
    $GroupBox1 = GUICtrlCreateGroup("", 8, 8, 305, 185, -1, $WS_EX_TRANSPARENT)
    $Label1 = GUICtrlCreateLabel("SQL AutoIt", 152, 24, 56, 17)
    $Label2 = GUICtrlCreateLabel("Version 1.0", 152, 48, 57, 17)
    $Label3 = GUICtrlCreateLabel("Copyright:    Elias Assad Neto Informática ME", 16, 136, 217, 17)
    $Label4 = GUICtrlCreateLabel("e_mail: eliasan@ig.com.br", 16, 160, 127, 17)
    GUICtrlCreateGroup("", -99, -99, 1, 1)
    $bOk = GUICtrlCreateButton("&OK", 112, 208, 75, 25, $WS_GROUP)
    ;FileInstall("SQL256x256_btColor.gif", @ScriptDir & "\SQL256x256_btColor.tmp", 1)
    ;$Pic1 = GUICtrlCreatePic(@ScriptDir &  "\SQL256x256_btColor.tmp",  24, 24, 100, 100, BitOR($SS_NOTIFY,  $WS_GROUP, $WS_CLIPSIBLINGS))
    ;FileDelete(@ScriptDir & "\SQL256x256_btColor.tmp")
    GUISetState(@SW_SHOW)
    #endregion ### END Koda GUI section ###

    While 1
        $nMsg = GUIGetMsg()
        Switch $nMsg
            Case $GUI_EVENT_CLOSE, $bOk
                GUIDelete($About_Form)
                Return
        EndSwitch
    WEnd
EndFunc   ;==>About

AdispEX.au3

#include-once


; Todas as "_ARRAYCONSTANT_" substituidas por "_ADisp_"

;Variaves do indicador de prograsso chamado atraves dProgresso()
Global $_ADisp_hGUI ;(Elias)
Global $_ADisp_prog = 0 ;(Elias) Contador de progresso
Global $_ADisp_MaxProg = 1 ;(Elias) Maximo progresso
Global $_ADisp_hProg = "" ;(Elias)
Global $_ADisp_Exit = False ;(Elias)
Global $_ADisp_ck = False ;(Elias)

Global $Debug  ; (Elias) se não for definata em outros modulos fica como false serve para mostra na console informação de debug
If @Compiled Then $Debug = False ; (Elias)

;  #FUNCTION#  ====================================================================================================================
; Name...........: _ADisplay dirivado da _ArrayDisplay para Substituir a antiga _ADisplay que rodava em AutoIT 3.2.8.1
; Description ...: Displays given 1D or 2D array array in a listview.
;  Syntax.........:  _ArrayDisplay(Const ByRef $avArray[, $saTitle =  "Array: ListView  Display"[, $iItemLimit = -1[, $iTranspose = 0[,  $sSeparator = ""[,  $sReplace = "|"]]]]])
; Parameters ....: $avArray    - Array to display
;                  $sTitle      - [optional] Title to use for window Ou Array[2]  onde Title  =  $saTitle[0] $aCab = $saTitle[1] (EX)
;                  $iItemLimit - [optional] Maximum number of listview items (rows) to show
;                                       Adicionado  <=-2, 2 ou 3 Indica Cabecario na primeira linha, (Elias)
;                                       para maximo numero de itens é considerado ABS se valores maiores q 3 ou -3 (Elias)
;                  $iTranspose - [optional] If set differently than default, will transpose the array if 2D
;                  $sSeparator - [optional] Change Opt("GUIDataSeparatorChar") on-the-fly
;                  $sReplace   - [optional] String to replace any occurrence of $sSeparator with in each array element
; Return values .: Success - 1
;                  Failure - 0, sets @error:
;                  |1 - $avArray is not an array
;                  |2 - $avArray has too many dimensions (only up to 2D supported)
; Author ........: randallc, Ultima
; Modified.......: Gary Frost (gafrost) / Ultima: modified to be self-contained (no longer depends on "GUIListView.au3")
; Modified.......: Elias Assad / 15/03/2009 / Ultima: Modificado para ter scrollbars Cabeçario etc...
; Remarks .......:
; Related .......:
; Link ..........;
; Example .......; Yes
;   ===============================================================================================================================
Func  _ADisplay(Const  ByRef $avArray, $saTitle = "Array: ListView Display",  $iItemLimit =  -1, $iTranspose = 0, $sSeparator = "", $sReplace = "|")
    If Not IsArray($avArray) Then Return SetError(1, 0, 0)

    $_ADisp_Exit = False ;(Elias)
    $_ADisp_ck = False ;(Elias)

    Local $cab = 0 ;(Elias) Se tem Cabeçario para manter  compatibilidade  com a antiga _ADiplay  o Valor <=-2, 2 ou 3 em  $iItemLimit informa q  tem cabeçario
    Local $aCab = ""

    If UBound($saTitle) <> 0 Then ; Monta cabeçalho baseado em array passado por $saTaitle
        If UBound($saTitle) >= 2 Then
            $sTitle = $saTitle[0]
            Local $aCab = $saTitle[1]
        Else
            Local $sTitle = "Array: ListView Display"
        EndIf
    Else
        Local $sTitle = $saTitle
    EndIf

    ; Dimension checking
    Local $iDimension = UBound($avArray, 0), $iUBound = UBound($avArray, 1) - 1, $iSubMax = UBound($avArray, 2) - 1
    If $iDimension > 2 Then Return SetError(2, 0, 0)

    ; Separator handling
;~  If $sSeparator = "" Then $sSeparator = Chr(1)
    If $sSeparator = "" Then $sSeparator = Chr(124)

    ; Declare variables                                                 "
    Local $i, $j, $vTmp, $aItem, $avArrayText, $CopySel = "Copy  Selection",  $sHeader = "Row", $iBuffer = 64, $WaitProc = "Wait  Processing "
    Local $iColLimit = 250, $iWidth = 640, $iHeight = 480
    ;Local $iLVIAddUDFThreshold = 4000 ; Removido na mais utilizado na versãp AutiIT 3.3.0.0
    Local $iOnEventMode = Opt("GUIOnEventMode", 0), $sDataSeparatorChar = Opt("GUIDataSeparatorChar", $sSeparator)

    ; Swap dimensions if transposing
    If $iSubMax < 0 Then $iSubMax = 0
    If $iTranspose Then
        $vTmp = $iUBound
        $iUBound = $iSubMax
        $iSubMax = $vTmp
    EndIf

    ; Set limits for dimensions
    If $iSubMax > $iColLimit Then $iSubMax = $iColLimit
    ;   If $iItemLimit = 1 Then $iItemLimit = $iLVIAddUDFThreshold  ; Removido na mais utilizado na versãp AutiIT 3.3.0.0
    ;   If $iItemLimit < 1 Then $iItemLimit = $iUBound
    ;If $iItemLimit >= 1 And $iItemLimit <= 3 Then $iItemLimit  =  $iLVIAddUDFThreshold ; Removido na mais utilizado na versãp  AutiIT  3.3.0.0
    If $iItemLimit = -2 Or $iItemLimit = 2 Or $iItemLimit = 3 Then $cab = 1 ;(Elias)
    If $iItemLimit <= -3 Then $iItemLimit = Abs($iItemLimit) ;(Elias)
    If $iItemLimit < 1 Then $iItemLimit =  $iUBound
    If $iUBound > $iItemLimit Then $iUBound = $iItemLimit
    ;If $iLVIAddUDFThreshold > $iUBound Then $iLVIAddUDFThreshold  =  $iUBound ; Removido na mais utilizado na versãp AutiIT 3.3.0.0

    ; Set header up
    For $i = 0 To $iSubMax
        If $cab Then ;(Elias)
            $sHeader &= $sSeparator & $avArray[0][$i] ;(Elias)
        Else
            If UBound($aCab) > 0 And $i < UBound($aCab) Then
                $sHeader &= $sSeparator & $aCab[$i]
            Else
                $sHeader &= $sSeparator & "Col " & $i
            EndIf
        EndIf ;(Elias)
    Next

    ; GUI Constants
    Local Const $_ADisp_GUI_DOCKBORDERS = 0x66
    Local Const $_ADisp_GUI_DOCKBOTTOM = 0x40
    Local Const $_ADisp_GUI_DOCKHEIGHT = 0x0200
    Local Const $_ADisp_GUI_DOCKLEFT = 0x2
    Local Const $_ADisp_GUI_DOCKRIGHT = 0x4
    Local Const $_ADisp_GUI_EVENT_CLOSE = -3
    Local Const $_ADisp_LVIF_PARAM = 0x4
    Local Const $_ADisp_LVIF_TEXT = 0x1
    Local Const $_ADisp_LVM_GETCOLUMNWIDTH = (0x1000 + 29)
    Local Const $_ADisp_LVM_GETITEMCOUNT = (0x1000 + 4)
    Local Const $_ADisp_LVM_GETITEMSTATE = (0x1000 + 44)
    Local Const $_ADisp_LVM_INSERTITEMA = (0x1000 + 7)
    Local Const $_ADisp_LVM_SETEXTENDEDLISTVIEWSTYLE = (0x1000 + 54)
    Local Const $_ADisp_LVM_SETITEMA = (0x1000 + 6)
    Local Const $_ADisp_LVS_EX_FULLROWSELECT = 0x20
    Local Const $_ADisp_LVS_EX_GRIDLINES = 0x1
    Local Const $_ADisp_LVS_SHOWSELALWAYS = 0x8
    Local Const $_ADisp_WS_EX_CLIENTEDGE = 0x0200
    Local Const $_ADisp_WS_MAXIMIZEBOX = 0x00010000
    Local Const $_ADisp_WS_MINIMIZEBOX = 0x00020000
    Local Const $_ADisp_WS_SIZEBOX = 0x00040000
    Local Const $_ADisp_tagLVITEM = "int Mask;int Item;int  SubItem;int  State;int StateMask;ptr Text;int TextMax;int Image;int  Param;int  Indent;int GroupID;int Columns;ptr pColumns"

    ;(Elias) Constantes acrecentadas
    Local Const $_ADisp_GUI_DOCKWIDTH = 0x00000100 ;(Elias)
    Local Const $_ADisp_WS_VSCROLL = 0x00200000 ;(Elias)
    Local Const $_ADisp_WS_HSCROLL = 0x00100000 ;(Elias)
    Local Const $_ADisp_WS_BORDER = 0x00800000 ;(Elias)
    Local Const $_ADisp_PBS_SMOOTH = 0x1 ;(Elias)
    Local Const $_ADisp_GUI_SHOW = 0x10 ;(Elias)
    Local Const $_ADisp_GUI_HIDE = 0x20 ;(Elias)

    ;(Elias) Events and messages
    Local Const $_ADisp_GUI_EVENT_MINIMIZE = -4
    Local Const $_ADisp_GUI_EVENT_RESTORE = -5
    Local Const $_ADisp_GUI_EVENT_MAXIMIZE = -6
    Local Const $_ADisp_GUI_EVENT_PRIMARYDOWN = -7
    Local Const $_ADisp_GUI_EVENT_PRIMARYUP = -8
    Local Const $_ADisp_GUI_EVENT_SECONDARYDOWN = -9
    Local Const $_ADisp_GUI_EVENT_SECONDARYUP = -10
    Local Const $_ADisp_GUI_EVENT_MOUSEMOVE = -11
    Local Const $_ADisp_GUI_EVENT_RESIZED = -12
    Local Const $_ADisp_GUI_EVENT_DROPPED = -13


    Local $iAddMask = BitOR($_ADisp_LVIF_TEXT, $_ADisp_LVIF_PARAM)
    Local $tBuffer = DllStructCreate("char Text[" & $iBuffer & "]"), $pBuffer = DllStructGetPtr($tBuffer)
    Local $tItem = DllStructCreate($_ADisp_tagLVITEM), $pItem = DllStructGetPtr($tItem)
    DllStructSetData($tItem, "Param", 0)
    DllStructSetData($tItem, "Text", $pBuffer)
    DllStructSetData($tItem, "TextMax", $iBuffer)

    ; Set interface up
    Local $_ADisp_hGUI = GUICreate($sTitle, $iWidth, $iHeight,  Default,  Default, BitOR($_ADisp_WS_SIZEBOX,  $_ADisp_WS_MINIMIZEBOX,  $_ADisp_WS_MAXIMIZEBOX))

    Local $aiGUISize = WinGetClientSize($_ADisp_hGUI)
    ;   Local $hListView = GUICtrlCreateListView($sHeader, 0, 0, $aiGUISize[0], $aiGUISize[1] - 26, $_ADisp_LVS_SHOWSELALWAYS)
    Local $hListView = GUICtrlCreateListView($sHeader, 0, 0,  $aiGUISize[0],  $aiGUISize[1] - 26, BitOR($_ADisp_WS_HSCROLL,  $_ADisp_WS_VSCROLL,  $_ADisp_WS_BORDER, $_ADisp_LVS_SHOWSELALWAYS))  ;(Elias)  Scrooll Bars  adicionadas
    ;   Local $hCopy = GUICtrlCreateButton("Copy Selected", 3, $aiGUISize[1] - 23, $aiGUISize[0] - 6, 20)
    Local $hCopy = GUICtrlCreateButton($CopySel, 3, $aiGUISize[1] - 23, 100, 20) ;(Elias) Fixa tamanho do Botão
    GUICtrlSetResizing(-1, $_ADisp_GUI_DOCKLEFT+$_ADisp_GUI_DOCKWIDTH);(Elias)
    ;GUICtrlCreateButton ( "text"       , left, top [, width [, height [, style [, exStyle]]]] )
    GUICtrlSetResizing($hListView, $_ADisp_GUI_DOCKBORDERS)
    ;   GUICtrlSetResizing($hCopy, $_ADisp_GUI_DOCKLEFT  +  $_ADisp_GUI_DOCKRIGHT + $_ADisp_GUI_DOCKBOTTOM +  $_ADisp_GUI_DOCKHEIGHT)
    GUICtrlSendMsg($hListView, $_ADisp_LVM_SETEXTENDEDLISTVIEWSTYLE, $_ADisp_LVS_EX_GRIDLINES, $_ADisp_LVS_EX_GRIDLINES)
    GUICtrlSendMsg($hListView, $_ADisp_LVM_SETEXTENDEDLISTVIEWSTYLE, $_ADisp_LVS_EX_FULLROWSELECT, $_ADisp_LVS_EX_FULLROWSELECT)
    GUICtrlSendMsg($hListView, $_ADisp_LVM_SETEXTENDEDLISTVIEWSTYLE, $_ADisp_WS_EX_CLIENTEDGE, $_ADisp_WS_EX_CLIENTEDGE)

    ;(Elias) Barra de progresso
    $_ADisp_hProg = GUICtrlCreateProgress(120, $aiGUISize[1] - 23 + 10/2, 100, 20 - 10, $_ADisp_PBS_SMOOTH) ;(Elias)
    GUICtrlSetResizing(-1, $_ADisp_GUI_DOCKLEFT+$_ADisp_GUI_DOCKWIDTH);(Elias)
    ;GUICtrlSetResizing($_ADisp_hProg, $_ADisp_GUI_DOCKLEFT + $_ADisp_GUI_DOCKBOTTOM) ;(Elias)
    ;GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKBOTTOM + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT) ;(Elias)
    Local $_ADisp_Itens, $_ADisp_Itens, $_ADisp_hAguarde
    If $iDimension = 2 Then
        $_ADisp_Itens = UBound($avArray, 1)*UBound($avArray, 2)
        $_ADisp_hAguarde = GUICtrlCreateLabel($WaitProc  &  UBound($avArray, 1)*UBound($avArray, 2) & " Itens....",  240,  $aiGUISize[1] - 20, 200, 20) ;(Elias)
    Else
        $_ADisp_Itens = UBound($avArray, 1)
        $_ADisp_hAguarde = GUICtrlCreateLabel($WaitProc &  $_ADisp_Itens  & " Itens....", 240, $aiGUISize[1] - 20, 200, 20)  ;(Elias)
    EndIf
    GUICtrlSetResizing(-1, $_ADisp_GUI_DOCKLEFT+$_ADisp_GUI_DOCKWIDTH);(Elias)
    ;                                                               ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ')  :  $_ADisp_Itens = ' & $_ADisp_Itens & @crlf & '>Error  code:  ' & @error & @crlf) ;### Debug Console

    ; Show dialog (Elias) Mostra antes de iniciar preenchimento
    GUISetState(@SW_SHOW, $_ADisp_hGUI) ;(Elias)
    GUICtrlSetState($hListView, $_ADisp_GUI_HIDE) ;(Elias)

    $_ADisp_prog = 0 ;(Elias)
    $_ADisp_MaxProg = $iUBound ;(Elias)
    If $_ADisp_Itens > 8999 Then ;(Elias) Todo If
        _ADisp_SetTimer($_ADisp_hGUI,"_ADisp_ShowProgress",300)
    Else
        GUICtrlSetState($_ADisp_hProg,$_ADisp_GUI_HIDE)
        GUICtrlSetState($_ADisp_hAguarde,$_ADisp_GUI_HIDE)
    EndIf

    GUISetCursor(15,1) ; HourGlass ;(Elias)

    Local $td = TimerInit()
    ; Convert array into text for listview
    Local $avArrayText[$iUBound + 1]
    ;   For $i = 0 To $iUBound
    For $i = $cab To $iUBound ;(Elias) Processa linhas
        ;       $avArrayText[$i] = "[" & $i & "]"
        $avArrayText[$i] = "[" & $i & "]" ;(Elias)
        For $j = 0 To $iSubMax ; Processa colunas
            ; Get current item
            If $iDimension = 1 Then
                If $iTranspose Then
                    $vTmp = $avArray[$j]
                Else
                    $vTmp = $avArray[$i]
                EndIf
            Else
                If $iTranspose Then
                    $vTmp = $avArray[$j][$i]
                Else
                    $vTmp = $avArray[$i][$j]
                EndIf
            EndIf

            ; Add to text array
            $vTmp = StringReplace($vTmp, $sSeparator, $sReplace, 0, 1)
;           $avArrayText[$i] &= $sSeparator & $vTmp
            $avArrayText[$i] &= $sSeparator & _ADisp_NormData($vTmp,1)  ;(Elias)  Se data AAAA/MM/DD --> DD/MM/AAAA

            ; Set max buffer size
            $vTmp = StringLen($vTmp)
            If $vTmp > $iBuffer Then $iBuffer = $vTmp
        Next
        $_ADisp_prog +=1 ;(elias) linhas processadas fase 1
        If $_ADisp_ck Then
            If GUIGetMsg() = $_ADisp_GUI_EVENT_CLOSE Then ;{Elias) Sai no meio do loop
                $_ADisp_Exit = True
                $_ADisp_ck = False
                ExitLoop
            EndIf
            $_ADisp_ck = False
        EndIf
    Next
    $iBuffer += 1
    If $Debug Then ConsoleWrite("Convert array into text for listview:  "  & TimerDiff($td) / 1000 & " seg" & " Prog ="  &  $_ADisp_prog & @CRLF)

    $td = TimerInit()
    ; Fill listview
;   For $i = 0 To $iLVIAddUDFThreshold
    If Not $_ADisp_Exit Then
    For $i = $cab To $iUBound ;(Elias)
        GUICtrlCreateListViewItem($avArrayText[$i], $hListView)
        $_ADisp_prog +=1 ;(elias) linhas processadas fase 2
        If $_ADisp_ck Then
            If GUIGetMsg() = $_ADisp_GUI_EVENT_CLOSE Then ;{Elias) Sai no meio do loop
                $_ADisp_Exit = True
                $_ADisp_ck = False
                ExitLoop
            EndIf
            $_ADisp_ck = False
        EndIf
    Next
    EndIf
#cs ;(Elias) não precisa na versão 3.3.0.0 em diante
    For $i = ($iLVIAddUDFThreshold + 1) To $iUBound
        $aItem = StringSplit($avArrayText[$i], $sSeparator)
        DllStructSetData($tBuffer, "Text", $aItem[1])

        ; Add listview item
        DllStructSetData($tItem, "Item", $i)
        DllStructSetData($tItem, "SubItem", 0)
        DllStructSetData($tItem, "Mask", $iAddMask)
        GUICtrlSendMsg($hListView, $_ADisp_LVM_INSERTITEMA, 0, $pItem)

        ; Set listview subitem text
        DllStructSetData($tItem, "Mask", $_ADisp_LVIF_TEXT)
        For $j = 2 To $aItem[0]
            DllStructSetData($tBuffer, "Text", $aItem[$j])
            DllStructSetData($tItem, "SubItem", $j - 1)
            GUICtrlSendMsg($hListView, $_ADisp_LVM_SETITEMA, 0, $pItem)
        Next
        $_ADisp_prog +=1 ;(elias) linhas processadas fase 2a
    Next
#ce
    If $Debug Then ConsoleWrite("Fill listview: " & TimerDiff($td)  /  1000 & " seg" & " Prog =" & $_ADisp_prog & @CRLF)

    $td = TimerInit()
    ; ajust window width
    $iWidth = 0
    Local $liWidth ;(Elias) Ultimo ajuste para reajustar para testar  posteriormente se mudado o tamanho da celula
    For $i = 0 To $iSubMax + 1
        $iWidth += GUICtrlSendMsg($hListView, $_ADisp_LVM_GETCOLUMNWIDTH, $i, 0)
    Next
    If $iWidth < 250 Then $iWidth = 230
    WinMove($_ADisp_hGUI, "", Default, Default, $iWidth + 20 + 20)
    If $Debug Then ConsoleWrite("Ajust window width: " & TimerDiff($td) / 1000 & " seg" & @CRLF)

    GUICtrlDelete($_ADisp_hAguarde)

    GUICtrlSetState($hListView, $_ADisp_GUI_SHOW)

    ; Show dialog
    GUISetState(@SW_SHOW, $_ADisp_hGUI)

    _ADisp_KillTimer($_ADisp_hGUI)
    GUICtrlSetState($_ADisp_hProg , $_ADisp_GUI_HIDE) ;(Elias)

    Local $iwcount, $testWidth ;(Elias)

    GUISetCursor() ; Normal ;(Elias)

    While 1
        If $testWidth Then ;(Elias) o If inteiro Reajusta janela se celula mudificada
                ; ajust window width
                $iWidth = 0
                For $i = 0 To $iSubMax + 1
                    $iWidth += GUICtrlSendMsg($hListView, $_ADisp_LVM_GETCOLUMNWIDTH, $i, 0)
                Next
                If $iWidth < 250 Then $iWidth = 230
                if $liWidth <> $iWidth Then
                    If $Debug Then ConsoleWrite("$iwcount=" & $iwcount & @CRLF)
                    WinMove($_ADisp_hGUI, "", Default, Default, $iWidth + 20 + 20)
                    $liWidth = $iWidth
                    $iwcount = 10000
                EndIf
            $iwcount += 1
            If $iwcount > 100 Then $testWidth = False
        EndIf
        Switch GUIGetMsg()
            Case 0  ;(Elias)
                ContinueLoop
            Case $_ADisp_GUI_EVENT_CLOSE
                ExitLoop
            Case $_ADisp_GUI_EVENT_PRIMARYUP ;(Elias) Pode ter havido um reajuste da tela verifica
                $testWidth = True
                $iwcount = 0
            Case $hCopy
                Local $sClip = ""

                ; Get selected indices [ _GUICtrlListView_GetSelectedIndices($hListView, True) ]
                Local $aiCurItems[1] = [0]
                For $i = 0 To GUICtrlSendMsg($hListView, $_ADisp_LVM_GETITEMCOUNT, 0, 0)
                    If GUICtrlSendMsg($hListView, $_ADisp_LVM_GETITEMSTATE, $i, 0x2) Then
                        $aiCurItems[0] += 1
                        ReDim $aiCurItems[$aiCurItems[0] + 1]
                        $aiCurItems[$aiCurItems[0]] = $i + $cab
                    EndIf
                Next

                ; Generate clipboard text
                If Not $aiCurItems[0] Then
                    For $sItem In $avArrayText
                        $sClip &= $sItem & @CRLF
                    Next
                Else
                    For $i = 1 To UBound($aiCurItems) - 1
                        $sClip &= $avArrayText[$aiCurItems[$i]] & @CRLF
                    Next
                EndIf
                ClipPut($sClip)
        EndSwitch
    WEnd
    _ADisp_KillTimer($_ADisp_hGUI)
    GUIDelete($_ADisp_hGUI)

    Opt("GUIOnEventMode", $iOnEventMode)
    Opt("GUIDataSeparatorChar", $sDataSeparatorChar)

    Return 1
EndFunc   ;==>_ArrayDisplayCab

Func _ADisp_ShowProgress()
    ;If $Debug Then ConsoleWrite($_ADisp_prog & @CRLF)
    $_ADisp_ck = True
    GUICtrlSetData($_ADisp_hProg, ($_ADisp_prog/$_ADisp_MaxProg)*100)
EndFunc

Func  _ADisp_NormData($dd,  $_f = 0) ; $_f = = rtorna $dd  $_f = 1 Comverte  data ANSI em  DD/MM/AAAA  $_f = 2 Converde DD/MM/AAAA em Data ANSI
    ; Normaliza a Data  AAAA/MM/DD para DD/MM/AAAA Ou Vice Versa Se é Data
    ; tb ajusta o dia e mes para dois digitos  AAAA/M/D para AAAA/0M/0D e D/M/AAAA para 0D/0M/AAAA
    Local $_i, $_a
    If ($_f =1 or $_f = 2) and (StringLen($dd) >= 8) and (StringLen($dd) <= 10) Then
        $_a = StringSplit($dd,"/")  ; cria uma matriz com anos mes e dias separados

        If @error = 1 or $_a[0] <> 3 Then Return ($dd) ; não é data separada por "/"
        For $_i = 1 To 3
            If StringLen($_a[$_i]) = 1 Then $_a[$_i] = "0" & $_a[$_i] ; ajusta dia e mes para 2 digitos
        Next

        If ($_f = 1 and StringLen($_a[3]) = 4) Or ($_f = 2 and StringLen($_a[1]) = 4) Then
            $dd = $_a[1] & "/" & $_a[2] & "/" & $_a[3] ; DD/MM/AAAA
        Else
            $dd = $_a[3] & "/" & $_a[2] & "/" & $_a[1] ; AAAA/MM/DD
        EndIf
    EndIf
    Return ($dd)
EndFunc   ;==>NormData


Func _ADisp_SetTimer($hWnd, $sCallBack = "", $iElapse = 250, $iTimerID = -1)
    If Not IsHWnd($hWnd) Then SetError(-1, -1, 0) ; Não tem asociação com uma Janela
    Local Const $_WM_TIMER = 0x0113
    If $iTimerID <= -1 Then $iTimerID = -1
    $iTimerID += 1000
    Local $retval = 1 ; Ok
    If $sCallBack <> "" Then $retval = GUIRegisterMsg($_WM_TIMER, $sCallBack) ; Seta a Chamada de retorno
    If $retval = 0 Then Return SetError(-2, -1, 0) ; Falhou ao Registar CallBack
    $retval = DllCall("User32.dll", "int", "SetTimer", "hwnd", $hWnd, "int", $iTimerID, "int", $iElapse, "int", 0)
    If @error Then Return SetError(-3, -1, 0) ; Falhou ao Ativar/Alterar o Timer
EndFunc

Func _ADisp_KillTimer($hWnd, $iTimerID = -1)
    If $iTimerID <= -1 Then $iTimerID = -1
    $iTimerID += 1000
    Local $retval = DllCall("User32.dll", "int", "KillTimer", "hwnd", $hWnd, "int", $iTimerID)
    If @error Then Return SetError(-1, -1, 0) ; Falhou ao terminar o Timer
EndFunc
Edited by Elias
Link to post
Share on other sites

Hello, i use _sql.au3 and i have problem with timeoutcommand.

Sometimes this script is working! but often i have this error:

[Microsoft][ODBC SQL Server Driver]Sessiontimeout.

maybe i use this function not correct?

_SQL_CommandTimeout(-1,"6000000")

_SQL_ConnectionTimeout(-1,"6000000")

In _sql.au3 is no Function _SQL_ConnectionTimeout() And _SQL_CommandTimeout

Below is the coding that can be added to _sql.au3

The Sintax is:

_SQL_CommandTimeout(-1,60) ; For 60 seconds

or

$CommandTimeout = _SQL_CommandTimeout() ; Returns the actual timeout , normally 30 seconds

or

$CommandTimeout = _SQL_CommandTimeout(-1,60) Set timeout to 60 seconds e returns las timeout (normally 15 seconds)

_SQL_ConnectionTimeout(-1,60) ; For 60 seconds

;#Reference Informations  ========================================================
;
; MSDN ADO    http://msdn.microsoft.com/en-us/library/ms807027.aspx
;
;Method                Description
;Open                Opens a connection to a data store.
;Close                Closes a connection and any dependent objects.
;Execute            Executes the specified query, SQL statement, stored procedure, or provider-specific text.
;BeginTrans            Begins a new transaction.
;CommitTrans        Saves any changes and ends the current transaction. It may also start a new transaction.
;RollbackTrans        Cancels any changes made during the current transaction and ends the transaction. It may also start a new transaction.
;
;The following        table lists some of the more commonly used properties of the Connection object.
;Property            Description
;ConnectionString    Contains the information used to establish a connection to a data store.
;ConnectionTimeout    Indicates how long to wait while establishing a connection before terminating the attempt and generating an error. (seconds)
;CommandTimeout        Indicates how long to wait while executing a command before terminating the attempt and generating an error. (seconds)
;State                Indicates whether a connection is currently open, closed, or connecting. (Open = 1 Closed = 0)
;Provider            Indicates the name of the provider used by the connection.
;Version            Indicates the ADO version number.
;
;=================================================================================

; #FUNCTION# ===================================================================
; Name ..........: _SQL_CommandTimeout()
; Description ...: Indicates how long to wait while executing a command before terminating the attempt and generating an error.
; Syntax.........:  _SQL_CommandTimeout([ $hConHandle = -1[, $iTimeOut = -1 ]])
; Parameters ....: $hConHandle - An Open Database, Use -1 To use Last Opened Database
;                  $TimeOut     - TimeOut in seconds
; Return values .: On Success   - TimeOut in seconds (if $iTimeOut >= 0 Then Set New TimeOut and Returns the Last TimeOut)
;                  On Failure  - Returns $SQL_ERROR and $SQLErr is set.
;                  .Use _SQL_GetErrMsg() to get text error information
; Author ........: Elias Assad Neto
; Modified ......:
; Remarks .......:
; Related .......:
; Link ..........;
; Example .......; no
; ==============================================================================
Func _SQL_CommandTimeout($ADODBHandle = -1,$iTimeOut = -1)

    $SQLErr = ""
    If $ADODBHandle = -1 Then $ADODBHandle = $SQL_LastConnection

    If Not IsObj($ADODBHandle) Then
        $SQLErr = "Invalid ADODB.Connection object, use _SQL_Startup()"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf

    $lTimeOut  = $ADODBHandle.CommandTimeout

    If @error Then
        $SQLErr = "Unable to retrive data"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf

    If $iTimeOut >= 0 Then
        $ADODBHandle.CommandTimeout = $iTimeOut
    EndIf

    If Not @error Then
        Return SetError($SQL_OK, 0, $lTimeOut)
    Else
        $SQLErr = "Unable to set data"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf
EndFunc   ;==>_SQL_CommandTimeout

; #FUNCTION# ===================================================================
; Name ..........: _SQL_ConnectionTimeout()
; Description ...: Indicates how long to wait while establishing a connection before terminating the attempt and generating an error.
; Syntax.........:  _SQL_ConnectionTimeout([ $hConHandle = -1[, $iTimeOut = -1 ]])
; Parameters ....: $hConHandle - An Open Database, Use -1 To use Last Opened Database
;                  $TimeOut     - TimeOut in seconds
; Return values .: On Success  - TimeOut in seconds (if $iTimeOut >= 0 Then Set New TimeOut and Returns the Last TimeOut)
;                  On Failure  - Returns $SQL_ERROR and $SQLErr is set.
;                  .Use _SQL_GetErrMsg() to get text error information
; Author ........: Elias Assad Neto
; Modified ......:
; Remarks .......:
; Related .......:
; Link ..........;
; Example .......; no
; ==============================================================================
Func _SQL_ConnectionTimeout($ADODBHandle = -1,$iTimeOut = -1)

    $SQLErr = ""
    If $ADODBHandle = -1 Then $ADODBHandle = $SQL_LastConnection

    If Not IsObj($ADODBHandle) Then
        $SQLErr = "Invalid ADODB.Connection object, use _SQL_Startup()"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf

    $lTimeOut  = $ADODBHandle.ConnectionTimeout

    If @error Then
        $SQLErr = "Unable to retrive data"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf

    If $iTimeOut >= 0 Then
        $ADODBHandle.ConnectionTimeout = $iTimeOut
    EndIf

    If Not @error Then
        Return SetError($SQL_OK, 0, $lTimeOut)
    Else
        $SQLErr = "Unable to set data"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf
EndFunc   ;==>_SQL_ConnectionTimeout
Edited by Elias
Link to post
Share on other sites
  • 1 month later...
  • 1 month later...
  • 3 weeks later...

i get an error trying to connect to mssql server over internet, any help.

Can i make a secure connection from my autoit script, as the server uses secure connection

C:\Program Files\AutoIt3\Include\_sql.au3 (217) : ==> The requested action with this object has failed.:
$ADODBHandle.Open("DRIVER={SQL Server};SERVER=" & $server & ";DATABASE=" & $db & ";uid=" & $username & ";pwd=" & $password & ";")
$ADODBHandle.Open("DRIVER={SQL Server};SERVER=" & $server & ";DATABASE=" & $db & ";uid=" & $username & ";pwd=" & $password & ";")^ ERROR
>Exit code: 1    Time: 19.099
Link to post
Share on other sites

i get an error trying to connect to mssql server over internet, any help.

Can i make a secure connection from my autoit script, as the server uses secure connection

C:\Program Files\AutoIt3\Include\_sql.au3 (217) : ==> The requested action with this object has failed.:
$ADODBHandle.Open("DRIVER={SQL Server};SERVER=" & $server & ";DATABASE=" & $db & ";uid=" & $username & ";pwd=" & $password & ";")
$ADODBHandle.Open("DRIVER={SQL Server};SERVER=" & $server & ";DATABASE=" & $db & ";uid=" & $username & ";pwd=" & $password & ";")^ ERROR
>Exit code: 1    Time: 19.099
Link to post
Share on other sites
  • 2 weeks later...

I am also looking for a solution for using .SQL files. Any ideas?

seems pretty straightforward... this works for me

#include<array.au3>
#include<_sql.au3>
Dim $resultset,$iRows,$iColumns
$adoc=_SQL_Startup()
$db=_SQL_Connect($adoc,"tr-backup1","ccdb","sa","pw")
If _SQL_GetErrMsg() Then
    MsgBox(0,"error","could not connect to database")
    Exit
EndIf
$vquery = FileRead("z:\db\test.sql")
_SQL_GetTable2D($adoc, $vquery,$resultset,$iRows,$iColumns)
_ArrayDisplay($resultset)

my z:\db\test.sql contains

SELECT

supervisor,

agent

FROM

vroster

ORDER BY agent

Edited by cameronsdad
Link to post
Share on other sites

Thanks for the _sql.au3 here gos my contribution for the _sql.au3

Getting TABLE NAME From the Data BASE

New Function _SQL_GetTableName() for SQL

Thanks, I've added this function in to the UDF and the latest UDF is available on the first post

Link to post
Share on other sites

i get an error trying to connect to mssql server over internet, any help.

Can i make a secure connection from my autoit script, as the server uses secure connection

C:\Program Files\AutoIt3\Include\_sql.au3 (217) : ==> The requested action with this object has failed.:
$ADODBHandle.Open("DRIVER={SQL Server};SERVER=" & $server & ";DATABASE=" & $db & ";uid=" & $username & ";pwd=" & $password & ";")
$ADODBHandle.Open("DRIVER={SQL Server};SERVER=" & $server & ";DATABASE=" & $db & ";uid=" & $username & ";pwd=" & $password & ";")^ ERROR
>Exit code: 1    Time: 19.099

There isn't a lot of information to go on here but can you use...

Local $server = "https://user:password@interneturl.com"
   Local $db = "databaseName"
   Local $userName = "myUserName"
   Local $password = "myPassword"
   _SQL_Connect(-1, $server, $db, $username, $password)
Link to post
Share on other sites

my connection is secure, and to a particular port.

$ADODBHandle.Open("Data Source=subdomain.host.com,17818;Integrated Security=True;User ID=my_username;Password=my_password;Encrypt=True;TrustServerCertificate=True");

This fails to connect

The requested action with this object has failed.:
Edited by TheCurrent
Link to post
Share on other sites

Does this work with MySQL?

(Someone asked the same question earlier but there was not response.)

As long as the ODBC Driver for MySQL is installed it should.

http://dev.mysql.com/downloads/connector/odbc/

Link to post
Share on other sites

As long as the ODBC Driver for MySQL is installed it should.

http://dev.mysql.com/downloads/connector/odbc/

It is, but I am unclear of how the connection string should look for this UDL.

I currently have _sql_Connect(-1,"192.168.203.58:3096","xstudio_db","xstudio","password") but it comes up with a connect error. I know my ODBC works as I use it with Excel.

I have also tried _sql_Connect(-1,"XStudio","xstudio_db","xstudio","password") where XStudio is the locally defined ODBC name, but that doesn't work either. I have also tried leaving off the port number in the first string.

Any help appreciated.

regards

Clark

Link to post
Share on other sites

Hi,

Local $avArray,$iRows,$iColumns
$iRval = _SQL_GetTable2D(-1,"select * from comp_pune",$avArray,$iRows,$iColumns) ; ==> Fetch data from comp_pune table
If $iRval = $SQL_OK then _ArrayDisplay($avArray, "Records from comp_pune table")

PFA above code snip for reference.

_SQL_GetTable2D () displays a ‘excel-like’ sheet with all data as in comp_pune(Refer snap for details). Now, my challenge is to

1. Add a ‘column’ i.e. add a new column to that ‘excel-like’ sheet.

2. Execute a SQL query in the AutoIt script and populate the field of the new column.

Can someone give me ideas on this? Thanks in advance.

post-64194-0-35413900-1304934896_thumb.p

Learning is Lifelong!
Link to post
Share on other sites

It is, but I am unclear of how the connection string should look for this UDL.

I currently have _sql_Connect(-1,"192.168.203.58:3096","xstudio_db","xstudio","password") but it comes up with a connect error. I know my ODBC works as I use it with Excel.

I have also tried _sql_Connect(-1,"XStudio","xstudio_db","xstudio","password") where XStudio is the locally defined ODBC name, but that doesn't work either. I have also tried leaving off the port number in the first string.

Any help appreciated.

regards

Clark

Try this by cdkid instead of _SQL_Connect when using MySql use _MySQLConnect below ...

#cs
    Function Name:    _MySQLConnect
    Description:      Initiate a connection to a MySQL database.
    Parameter(s):     $username - The username to connect to the database with.
    $password - The password to connect to the database with. $Database - Database to connect to.
    $server - The server your database is on.
    $driver (optional) the ODBC driver to use (default is "{MySQL ODBC 3.51 Driver}"
    Requirement(s):   Autoit 3 with COM support
    Return Value(s):  On success returns the connection object for subsequent functions. On failure returns 0 and sets @error
    @Error = 1
    Error opening connection
    @Error = 2
    MySQL ODBC Driver not installed.
    Author(s):        cdkid
#ce

Func _MySQLConnect($sUsername, $sPassword, $sDatabase, $sServer, $sDriver = "{MySQL ODBC 5.1 Driver}", $iPort=3306)
    Local $v = StringMid($sDriver, 2, StringLen($sDriver) - 2)
    Local $key = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", $val = RegRead($key, $v)
    If @error or $val = "" Then
        SetError(2)
        Return 0
    EndIf
    $ObjConn = ObjCreate("ADODB.Connection")
    $Objconn.open ("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";PORT="&$iPort)
    If @error Then
        SetError(1)
        Return 0
    Else
        Return $ObjConn
    EndIf
EndFunc   ;==>_MySQLConnect
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...