_SQL.au3. ADODB.Connection

[ChrisL]

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.

This is full of complications like what if your second query doesn't have the same amount of rows returned?

You could do as you describe with creating a second array with an extra column and then copying the data in to the new array but can you not use a "JOIN" in your query to get the correct data in the first place?

[Clark]

Thanks Chris,

That did the job perfectly!

[Inverti]

I needed to connect to Postgres SQL, so I thought that I will simply do that through ODBC and I will set the DSN connection.

I wrote simple function which I think is useful for all the users who want to connect to any database with ODBC driver. It simply connects to named DSN, which should be previously set in Control Panel. It works great.

; #FUNCTION# ===================================================================
; Name ..........: _SQL_ODBCDSNConnect
; Description ...: Starts a ODBC DSN Database Connection
; Syntax.........:  _SQL_ODBCDSNConnect($ADODBHandle,$DSNName)
; Parameters ....: $ADODBHandle - ADODB.Connection handle.
;                  $DSNName - name of ODBC DSN to connect to 
; Return values .: On Success   - Returns $SQL_OK
;                  On Failure   - Returns $SQL_ERROR and $SQLErr is set.
;                  .Use _SQL_GetErrMsg() to get text error information
; Author ........: Arkadiusz Stanoszek
; Modified ......:
; Remarks .......:
; Related .......:
; Link ..........;
; Example .......; no
; ==============================================================================
Func _SQL_ODBCDSNConnect($ADODBHandle, $DSNName)

    $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

    $ADODBHandle.Open("DSN=" & $DSNName )

    If Not @error Then
        Return SetError($SQL_OK, 0, $SQL_OK)
    Else
        $SQLErr = "Connection Error"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf
EndFunc   ;==>_SQL_ODBCDSNConnect

Edited June 6, 2011 by Inverti

[Clark]

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

Well, it worked as far as opening the db is concerned, but I still cannot seem to execute an sql query without an undefined error occurring.

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

Opt ("trayIconDebug",1)

_SQL_RegisterErrorHandler() ; register the error handler to prevent hard crash on COM error
$oADODB = _SQL_Startup()

If $oADODB = $SQL_ERROR then Exit Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())       
$hDBHandle = _MySQLConnect("xstudio","xxx","xstudio_db","192.168.203.58")
if @error Then Exit Msgbox("Error number is " & @error)
If $oADODB = $SQL_ERROR then Exit Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())   
$query = "SELECT table_absence.user_id FROM xstudio_db.table_absence"
_SQL_Execute($hDBHandle,$query)
if $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())

This opens the database ok, but where I execute the query it fails, however _SQL_GetErrMsg() returns nothing. (I have tried it with various "select" statements)

Any ideas?

Clark

Edited June 8, 2011 by Clark

[Clark]

nm, I think the problem is deeper than I first thought.

I will play some more...

[buymeapc]

Hi, I'm trying to create a function that simply tests the SQL connection to see if it is functioning or not. I'm using Windows Authentication with the ASP.NET user within Windows. Every time I fire the function, I get a "Connection Error" from the error handler. Here's the function I'm using below. I send the server, a user, blank password and a False for the $auth variable. Could I ask for some help please? Thanks!

Func _SQLTestConnection($server, $user, $pass, $auth = True)
 Local $connStat
 ; Load the SQL error handler
 _SQL_RegisterErrorHandler();register the error handler to prevent hard crash on COM error
 $oADODB = _SQL_Startup()
 If $oADODB = $SQL_ERROR Then Msgbox(0 + 16 + 262144, "Error1", _SQL_GetErrMsg())
 $connStat = _SQL_Connect(-1, $server, "WebStratAdmin", $user, $pass, $auth)
 If $connStat = $SQL_ERROR Then
        Msgbox(0 + 16 + 262144, "Error2", _SQL_GetErrMsg())
        _SQL_Close()
 Else
  MsgBox(0, "Success?", $connStat)
    EndIf
EndFunc

[Syed23]

I am getting same error as mentioned above. Any help ?

Do we need to install any driver in order to work ?

Note: i am able to ping the DB Server without any issue.

Here is the Error log writtern on SCiTE:

###############################
err.description is:  [Microsoft][ODBC SQL Server Driver][DBNETLIB]Invalid connection.
err.windescription: Unspecified error
err.number is:  80020009
err.lastdllerror is:  0
err.scriptline is:  220
err.source is:  Microsoft OLE DB Provider for ODBC Drivers
err.helpfile is:  
err.helpcontext is:  0###############################
###############################
err.description is:  Operation is not allowed when the object is closed.
err.windescription:
err.number is:  80020009
err.lastdllerror is:  0
err.scriptline is:  373
err.source is:  ADODB.Connection
err.helpfile is:  C:WindowsHELPADO270.CHM
err.helpcontext is:  1240653###############################
!>08:01:37 AutoIT3.exe ended.rc:2
>Exit code: 2   Time: 19.836

Ok. Here is my example code where i am getting error:

#include"_sql.au3"
#include"Array.au3"
#include <Timers.au3>
Global $iRval
Global $ID = 3140

_SQL_RegisterErrorHandler()
$oADODB = _SQL_Startup()
If $oADODB = $SQL_ERROR Then
  MsgBox(0 + 16, "SQL Error", "Error: " & _SQL_GetErrMsg())
  Exit 3
;EndIf
Else
$connect = _sql_Connect(-1, "HostName", "DBName", "UserName", "password")
If $connect = $SQL_ERROR Then
  MsgBox(0 + 16 + 262144, "Error", _SQL_GetErrMsg())
  _SQL_Close()
  Exit 2
EndIf
EndIf
Dim $iRval, $aData, $iRows, $iColumns, $t, $Label4, $t_1
$t = TimerInit()
$iRval = _SQL_GetTable2D(-1, "select Pkg_Number, Pkg_Flag from QAA_PkgInfoData where Chg_ID= '" & $ID & "'", $aData, $iRows, $iColumns)
If $iRval = $SQL_OK Then
  $t = _Timer_Diff($t)

  _ArrayDisplay($iRval)
Else
  $t = 0
  MsgBox(48, "Test", "Unable to find the account in Database")
  Exit
EndIf
_SQL_Close()

Edited January 18, 2012 by Syed23

[Syed23]

do i have anyone to help me on this ?

[hannes08]

Hello Syed,

I'm getting the same error message when I'm trying to connect to a named SQL Server instance from a remote host.

E.g. connecting from my pc to a server with instance: "server01MSSQL"

I'm 100% sure that the username / password combination is okay.

What happens if you run your test script on the server itself?

Edited January 19, 2012 by hannes08

[Syed23]

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 function slightly. Please find the modified function below and try with this if anyone face the similar issue.

Func _SQL_Connect($ADODBHandle, $server, $db, $username, $password, $SQLAuth = True)
    $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
If $SQLAuth = True then
  $ADODBHandle.Open("DRIVER={SQL Server Native Client 10.0};SERVER=" & $server & ";DATABASE=" & $db & ";uid=" & $username & ";pwd=" & $password & ";") ;<==This line has been modified from [$ADODBHandle.Open("DRIVER={SQL Server};SERVER=" & $server & ";DATABASE=" & $db & ";uid=" & $username & ";pwd=" & $password & ";")]
Else
  $ADODBHandle.Properties("Integrated Security").Value = "SSPI"
  $ADODBHandle.Properties("User ID") = $username
  $ADODBHandle.Properties("Password") = $password
  $ADODBHandle.Open("DRIVER={SQL Server};SERVER=" & $server & ";DATABASE=" & $db )
EndIf

    If Not @error Then
        Return SetError($SQL_OK, 0, $SQL_OK)
    Else
        $SQLErr = "Connection Error"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf
EndFunc   ;==>_SQL_Connect

Note: Now all set free for me and works great!

Edited January 19, 2012 by Syed23

[GhostLine]

Hello guys,

I'm experimenting problems using _sql.au3. For some reasons, when I try to execute this :

#include <_sql.au3>

Local $server = "MAG0728K01"
Local $db = "TPCentralDB"
Local $userName = "sa"
Local $password = "password"

_SQL_Startup()
If _SQL_Connect(-1, $server, $db, $username, $password) = $SQL_ERROR Then
    MsgBox(0,"","Coin !")
EndIf

$requete = "SELECT * FROM [TPCentralDB].[dbo].[Computer]"
_SQL_Execute(-1, $requete)
_SQL_Close()

No error message. But if I change the SQL resquest to "UPGRADE [TPCentralDB].[dbo].[Computer]", I can read a popup "Error : the requested action with this object has failed."

I'm using a Ms SQL 2008 Server V2.

Thanks for your help !

[spudw2k]

I've never heard of the SQL command "UPGRADE" before.

[GhostLine]

Ahhh ! Perhaps you're right ...

It might be a synthax error !

If you don't mind, that's what I'm trying to do :

$requete1 = "[TPCentralDB].[dbo].[Computer].[bLocalCleanupIsRequested] ="& 1 &" WHERE [ComputerName] ="& $POS2_name &";"

(set the bLocalCleanupIsRequested field where ComputerName = the POS n°2 name to 1)

Am I right ?

Thanks !

Edited March 23, 2012 by GhostLine

[Xenobiologist]

Update "Table"

set yourColumns = yourNewValue

where ...

[GhostLine]

Something like :

#include <_sql.au3>

Local $server = "MAG0728K01"
Local $db = "TPCentralDB"
Local $userName = "sa"
Local $password = "password"

_SQL_Startup()

If _SQL_Connect(-1, $server, $db, $username, $password) = $SQL_ERROR Then
    MsgBox(0,"","Coin !")
EndIf

$req1 = "UPDATE [TPCentralDB].[dbo].[Computer]"
$req2 = "SET [TPCentralDB].[dbo].[Computer].[bLocalCleanupIsRequested] =1"
$req3 = "WHERE [ComputerName] ="& $POS2_name &";"

_SQL_Execute(-1, $req1)
_SQL_Execute(-1, $req2)
_SQL_Execute(-1, $req3)
_QSL_Close()

I'm asking because I'm not so sure of the quote / doublequote of the SQL request (I'm not so familiar with this in AutoIT).

[Xenobiologist]

Maybe you should open a thread in support forum. I'm on a DB2 database, but this works for you too

expandcollapse popup

; Global Variables
Global $adUseServer = 2
Global $adUseClient = 3
; Initialize COM error handler
Global $oMyError = ObjEvent('AutoIt.Error', 'MyErrFunc')
Global $provider = 'IBMDADB2'
Global $IP = 'db2entsrv'
Global $port = 60140
Global $DSN = 'xxx'
Global $userID = 'xxx'
Global $password = 'xxx'
Global $connection_Obj = _connectDB($provider, $IP, $port, $DSN, $userID, $password)
Local $sqlRs = ObjCreate('ADODB.Recordset')
_displayTable($connection_Obj, 'SELECT * FROM [TPCentralDB].[dbo].[Computer]')
$sqlRs.open('Update [TPCentralDB].[dbo].[Computer] SET [bLocalCleanupIsRequested] = 1 WHERE [ComputerName] = ''???''', $connection_Obj) ; do not know what this is " & $POS2_name
_displayTable($connection_Obj, 'SELECT * FROM [TPCentralDB].[dbo].[Computer]')
$sqlRs.close
Func _connectDB($provider, $IP, $port, $DSN, $userID, $password)
Local $sqlCon = ObjCreate('ADODB.Connection')
$sqlCon.Mode = 16 ; Erlaubt im MultiUser-Bereich das öffnen anderer Verbindungen ohne Beschränkungen [Lesen/Schreiben/Beides]
$sqlCon.CursorLocation = $adUseClient ; client side cursor Schreiben beim Clienten
$sqlCon.Open('Provider=' & $provider & ';IP=' & $IP & ';Port=' & $port & ';DSN=' & $DSN & ';User ID=' & $userID & ';Password=' & $password)
If @error Then Return -1
Return $sqlCon
EndFunc   ;==>_connectDB
Func _getColumns($sqlCon, $SQL)
Local $sqlRs = ObjCreate('ADODB.Recordset')
If Not @error Then
  $sqlRs.open($SQL, $sqlCon)
  If Not @error Then
   For $i = 0 To $sqlRs.Fields.Count - 1
    ConsoleWrite($sqlRs.Fields($i).Name & @CRLF)
   Next
   $sqlRs.close
  EndIf
EndIf
EndFunc   ;==>_getColumns
Func _displayTable($sqlCon, $SQL)
Local $sqlRs = ObjCreate('ADODB.Recordset')
If Not @error Then
  $sqlRs.open($SQL, $sqlCon)
  If Not @error Then
   Local $header[$sqlRs.Fields.Count], $rows
   For $i = 0 To $sqlRs.Fields.Count - 1
    $header[$i] = $sqlRs.Fields($i).Name
   Next
   $rows = $sqlRs.GetRows()
   $sqlRs.close
  EndIf
EndIf
_ArrayDisplay_WithHeader($rows, $header)
EndFunc   ;==>_displayTable
Func MyErrFunc()
$HexNumber = Hex($oMyError.number, 8)
MsgBox(0, 'COM Test', 'We intercepted a COM Error !' & @CRLF & @CRLF & _
   'err.description is: ' & @TAB & $oMyError.description & @CRLF & _
   'err.windescription:' & @TAB & $oMyError.windescription & @CRLF & _
   'err.number is: ' & @TAB & $HexNumber & @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) ; to check for after this function returns
EndFunc   ;==>MyErrFunc
Func _ArrayDisplay_WithHeader(Const ByRef $avArray, $header, $sTitle = "Array: ListView Display", $iItemLimit = -1, $iTranspose = 0, $sSeparator = "", $sReplace = "|")
If Not IsArray($avArray) Then Return SetError(1, 0, 0)
; 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, $sHeader = "Row", $iBuffer = 64
Local $iColLimit = 250, $iLVIAddUDFThreshold = 4000, $iWidth = 640, $iHeight = 480
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
If $iItemLimit < 1 Then $iItemLimit = $iUBound
If $iUBound > $iItemLimit Then $iUBound = $iItemLimit
If $iLVIAddUDFThreshold > $iUBound Then $iLVIAddUDFThreshold = $iUBound
; Set header up
For $i = 0 To UBound($header) - 1
  $sHeader &= $sSeparator & $header[$i]
Next
; Convert array into text for listview
Local $avArrayText[$iUBound + 1]
For $i = 0 To $iUBound
  $avArrayText[$i] = "[" & $i & "]"
  For $j = 0 To $iSubMax
   ; 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
   ; Set max buffer size
   $vTmp = StringLen($vTmp)
   If $vTmp > $iBuffer Then $iBuffer = $vTmp
  Next
Next
$iBuffer += 1
; GUI Constants
Local Const $_ARRAYCONSTANT_GUI_DOCKBORDERS = 0x66
Local Const $_ARRAYCONSTANT_GUI_DOCKBOTTOM = 0x40
Local Const $_ARRAYCONSTANT_GUI_DOCKHEIGHT = 0x0200
Local Const $_ARRAYCONSTANT_GUI_DOCKLEFT = 0x2
Local Const $_ARRAYCONSTANT_GUI_DOCKRIGHT = 0x4
Local Const $_ARRAYCONSTANT_GUI_EVENT_CLOSE = -3
Local Const $_ARRAYCONSTANT_LVIF_PARAM = 0x4
Local Const $_ARRAYCONSTANT_LVIF_TEXT = 0x1
Local Const $_ARRAYCONSTANT_LVM_GETCOLUMNWIDTH = (0x1000 + 29)
Local Const $_ARRAYCONSTANT_LVM_GETITEMCOUNT = (0x1000 + 4)
Local Const $_ARRAYCONSTANT_LVM_GETITEMSTATE = (0x1000 + 44)
Local Const $_ARRAYCONSTANT_LVM_INSERTITEMA = (0x1000 + 7)
Local Const $_ARRAYCONSTANT_LVM_SETEXTENDEDLISTVIEWSTYLE = (0x1000 + 54)
Local Const $_ARRAYCONSTANT_LVM_SETITEMA = (0x1000 + 6)
Local Const $_ARRAYCONSTANT_LVS_EX_FULLROWSELECT = 0x20
Local Const $_ARRAYCONSTANT_LVS_EX_GRIDLINES = 0x1
Local Const $_ARRAYCONSTANT_LVS_SHOWSELALWAYS = 0x8
Local Const $_ARRAYCONSTANT_WS_EX_CLIENTEDGE = 0x0200
Local Const $_ARRAYCONSTANT_WS_MAXIMIZEBOX = 0x00010000
Local Const $_ARRAYCONSTANT_WS_MINIMIZEBOX = 0x00020000
Local Const $_ARRAYCONSTANT_WS_SIZEBOX = 0x00040000
Local Const $_ARRAYCONSTANT_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"
Local $iAddMask = BitOR($_ARRAYCONSTANT_LVIF_TEXT, $_ARRAYCONSTANT_LVIF_PARAM)
Local $tBuffer = DllStructCreate("char Text[" & $iBuffer & "]"), $pBuffer = DllStructGetPtr($tBuffer)
Local $tItem = DllStructCreate($_ARRAYCONSTANT_tagLVITEM), $pItem = DllStructGetPtr($tItem)
DllStructSetData($tItem, "Param", 0)
DllStructSetData($tItem, "Text", $pBuffer)
DllStructSetData($tItem, "TextMax", $iBuffer)
; Set interface up
Local $hGUI = GUICreate($sTitle, $iWidth, $iHeight, 10, 10, BitOR($_ARRAYCONSTANT_WS_SIZEBOX, $_ARRAYCONSTANT_WS_MINIMIZEBOX, $_ARRAYCONSTANT_WS_MAXIMIZEBOX))
Local $aiGUISize = WinGetClientSize($hGUI)
Local $hListView = GUICtrlCreateListView($sHeader, 0, 0, $aiGUISize[0], $aiGUISize[1] - 26, $_ARRAYCONSTANT_LVS_SHOWSELALWAYS)
Local $hCopy = GUICtrlCreateButton("Copy Selected", 3, $aiGUISize[1] - 23, $aiGUISize[0] - 6, 20)
GUICtrlSetResizing($hListView, $_ARRAYCONSTANT_GUI_DOCKBORDERS)
GUICtrlSetResizing($hCopy, $_ARRAYCONSTANT_GUI_DOCKLEFT + $_ARRAYCONSTANT_GUI_DOCKRIGHT + $_ARRAYCONSTANT_GUI_DOCKBOTTOM + $_ARRAYCONSTANT_GUI_DOCKHEIGHT)
GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_SETEXTENDEDLISTVIEWSTYLE, $_ARRAYCONSTANT_LVS_EX_GRIDLINES, $_ARRAYCONSTANT_LVS_EX_GRIDLINES)
GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_SETEXTENDEDLISTVIEWSTYLE, $_ARRAYCONSTANT_LVS_EX_FULLROWSELECT, $_ARRAYCONSTANT_LVS_EX_FULLROWSELECT)
GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_SETEXTENDEDLISTVIEWSTYLE, $_ARRAYCONSTANT_WS_EX_CLIENTEDGE, $_ARRAYCONSTANT_WS_EX_CLIENTEDGE)
; Fill listview
For $i = 0 To $iLVIAddUDFThreshold
  GUICtrlCreateListViewItem($avArrayText[$i], $hListView)
Next
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, $_ARRAYCONSTANT_LVM_INSERTITEMA, 0, $pItem)
  ; Set listview subitem text
  DllStructSetData($tItem, "Mask", $_ARRAYCONSTANT_LVIF_TEXT)
  For $j = 2 To $aItem[0]
   DllStructSetData($tBuffer, "Text", $aItem[$j])
   DllStructSetData($tItem, "SubItem", $j - 1)
   GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_SETITEMA, 0, $pItem)
  Next
Next
; ajust window width
$iWidth = 0
For $i = 0 To $iSubMax + 1
  $iWidth += GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_GETCOLUMNWIDTH, $i, 0)
Next
If $iWidth < 250 Then $iWidth = 230
WinMove($hGUI, "", Default, Default, $iWidth + 20)
; Show dialog
GUISetState(@SW_SHOW, $hGUI)
While 1
  Switch GUIGetMsg()
   Case $_ARRAYCONSTANT_GUI_EVENT_CLOSE
    ExitLoop
   Case $hCopy
    Local $sClip = ""
    ; Get selected indices [ _GUICtrlListView_GetSelectedIndices($hListView, True) ]
    Local $aiCurItems[1] = [0]
    For $i = 0 To GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_GETITEMCOUNT, 0, 0)
     If GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_GETITEMSTATE, $i, 0x2) Then
      $aiCurItems[0] += 1
      ReDim $aiCurItems[$aiCurItems[0] + 1]
      $aiCurItems[$aiCurItems[0]] = $i
     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
GUIDelete($hGUI)
Opt("GUIOnEventMode", $iOnEventMode)
Opt("GUIDataSeparatorChar", $sDataSeparatorChar)
Return 1
EndFunc   ;==>_ArrayDisplay_WithHeader

[t0nZ]

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 function slightly. Please find the modified function below and try with this if anyone face the similar issue.

Func _SQL_Connect($ADODBHandle, $server, $db, $username, $password, $SQLAuth = True)
    $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
If $SQLAuth = True then
$ADODBHandle.Open("DRIVER={SQL Server Native Client 10.0};SERVER=" & $server & ";DATABASE=" & $db & ";uid=" & $username & ";pwd=" & $password & ";") ;<==This line has been modified from [$ADODBHandle.Open("DRIVER={SQL Server};SERVER=" & $server & ";DATABASE=" & $db & ";uid=" & $username & ";pwd=" & $password & ";")]
Else
$ADODBHandle.Properties("Integrated Security").Value = "SSPI"
$ADODBHandle.Properties("User ID") = $username
$ADODBHandle.Properties("Password") = $password
$ADODBHandle.Open("DRIVER={SQL Server};SERVER=" & $server & ";DATABASE=" & $db )
EndIf

    If Not @error Then
        Return SetError($SQL_OK, 0, $SQL_OK)
    Else
        $SQLErr = "Connection Error"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf
EndFunc ;==>_SQL_Connect

Note: Now all set free for me and works great!

I can confirm it works for M$ sql server 2008r2.

I have updated my _sql_au3 udf adding this function with another name in respect to the original function.

But I wonder if this new sql_connect works with older M$ sql servers......

For now I keep two separate functions for connecting to sql servers.

[EKY32]

I have a local server using AppServ v2.6.0,

I tried all the ways to connect starting with _sql_Connect(-1,"localhost","","root","toor"), the username and password are correct and i just have a connection error:

err.description is: [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.

err.windescription: Unspecified error

err.number is: 80020009

err.lastdllerror is: 0

err.scriptline is: 220

err.source is: Microsoft OLE DB Provider for ODBC Drivers

Where is the error?

Thank you.

[spudw2k]

My guess (based on the error msg) is it looks like the UDF is attempting to use a SQL Server ODBC driver to connect to a MySql db. I don't think a MySql ODBC driver is included with windows, or that UDF supports them. Some work could probably be done to incorporate it if such an ODBC driver exists.

Edited September 6, 2012 by spudw2k

[GregThompson]

I'm running this and getting all the results I would expect in a 1 dimensional array, however when I go 2D (my preference) 1 of the columns is completely blank of data and I don't know why? Do 2D array's have an element size limit?