Jump to content

_SQL.au3. ADODB.Connection


Recommended Posts

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?

Link to post
Share on other sites
  • 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

  • 4 weeks later...

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 by Inverti
Link to post
Share on other sites

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 by Clark
Link to post
Share on other sites
  • 5 months later...

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
Link to post
Share on other sites
  • 2 months later...

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 by Syed23

Thank you,Regards,[font="Garamond"][size="4"]K.Syed Ibrahim.[/size][/font]

Link to post
Share on other sites

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 by hannes08
Regards,Hannes[spoiler]If you can't convince them, confuse them![/spoiler]
Link to post
Share on other sites

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 by Syed23

Thank you,Regards,[font="Garamond"][size="4"]K.Syed Ibrahim.[/size][/font]

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

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 !

Link to post
Share on other sites

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 by GhostLine
Link to post
Share on other sites

Update "Table"

set yourColumns = yourNewValue

where ...

Scripts & functions Organize Includes Let Scite organize the include files

Yahtzee The game "Yahtzee" (Kniffel, DiceLion)

LoginWrapper Secure scripts by adding a query (authentication)

_RunOnlyOnThis UDF Make sure that a script can only be executed on ... (Windows / HD / ...)

Internet-Café Server/Client Application Open CD, Start Browser, Lock remote client, etc.

MultipleFuncsWithOneHotkey Start different funcs by hitting one hotkey different times

Link to post
Share on other sites

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

Link to post
Share on other sites

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

; 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

Scripts & functions Organize Includes Let Scite organize the include files

Yahtzee The game "Yahtzee" (Kniffel, DiceLion)

LoginWrapper Secure scripts by adding a query (authentication)

_RunOnlyOnThis UDF Make sure that a script can only be executed on ... (Windows / HD / ...)

Internet-Café Server/Client Application Open CD, Start Browser, Lock remote client, etc.

MultipleFuncsWithOneHotkey Start different funcs by hitting one hotkey different times

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

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! :D

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.

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

I have a local server using AppServ v2.6.0,

Posted Image

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.

[font="'trebuchet ms', helvetica, sans-serif;"]Please mark the answer of your question if you found it.[/font]

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

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 by spudw2k
Link to post
Share on other sites
  • 4 months later...

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