help - sql question.

[erezlevi]

hi,

although I have the MySql udf, I want to connect using the _sql.au3 udf. but I don't understand why it won't connect.

with the mysql examples I get the data fine using this code:

expandcollapse popup

#include "mysql.au3"

$SQLOBJ = _MySQLConnect("root","aiculed1","localhost","test")

Switch @ERROR
Case 0
MsgBox(0,"","Connection successful")
Case 1
MsgBox(0,"","Error","Error opening connection")
Case 2
MsgBox(0,"","MySQL ODBC Driver not installed.")
EndSwitch

;Retrieve array of all table names in database
$aTables = _GetTblNames($SQLOBJ)
If NOT @ERROR Then
;Dump array to console and show table count
ConsoleWrite("########## TABLES (" & _CountTables($SQLOBJ) & ") ##########" & @CRLF)
For $X = 1 to $aTables[0]
ConsoleWrite($aTables[$X] & @CRLF)
Next
Else
MsgBox(0,"Error","Something Went Wrong!")
EndIf

ConsoleWrite(@CRLF)

;Retrieve array of all column names in table
$ret2 = _GetColNames($SQLOBJ, "erez")
If NOT @ERROR Then
;Dump array to console and show column count
ConsoleWrite("########## COLUMNS (" & _GetColCount($SQLOBJ, "erez") & ")##########" & @CRLF)
For $X = 1 to $ret2[0]
ConsoleWrite($ret2[$X] & @CRLF)
Next
Else
MsgBox(0,"Error","Something Went Wrong!")
EndIf

$result = _MySQLExec($SQLOBJ, "SELECT * FROM erez;")
If NOT @ERROR Then
    
    With $result
        While NOT .EOF; is equal to "Do this until we run out of records"
            
          ;Write the value for every column
            For $X In .Fields
                ConsoleWrite($X.value & ",")
            Next
            .MoveNext;Move to the next row
            ConsoleWrite(@CRLF)
        WEnd
    EndWith
Else
    MsgBox(0,"Error","Something Went Wrong!")
EndIf

but, when trying to use the following code it does not work:

#include <_sql.au3>
#include <Array.au3>

_SQLRegisterErrorHandler();

$con = _SQLStartup()
If @error then Msgbox(0,"Error","Error starting ADODB.Connection")

_sqlConnect(-1,"localhost","test","root","aiculed1")
if @Error then _DisplayError($SQLErr)
    
$data = _SQLExecute(-1,"SELECT * FROM erez;")
If Not @error then 
    $aData = _SQLGetData2D($data)
    _arrayDisplay($aData,"SELECT * FROM erez;")
Else
    _DisplayError($SQLErr)
EndIf

Func _DisplayError($vText)
    Msgbox(0 + 16 +262144,"SQL Error",$vText)
EndFunc

why? what am I doing wrong here, isn't SQL a standard base or mySql needs special treatment? is there a way to connect to it using this script?

my porpuse is to connect to MSSQL eventually, but for testing I only have the mysql.

Edited October 20, 2008 by erezlevi

[ChrisL]

I would guess that if your using the _SQL.au3 I posted then the function in _SQL.au3

Func _SQLConnect($ConHandle,$server, $db, $username, $password)
    
    DIM $SQLErr
    If $ConHandle = -1 then $ConHandle = $sqlLastConnection
    $ConHandle.Open ("DRIVER={SQL Server};SERVER=" & $server & ";DATABASE=" & $db & ";uid=" & $username & ";pwd=" & $password & ";");<==Connect with required credentials
    If NOT @error then return 1
    Return SetError(1,0,0)
    
EndFunc

The Driver is looking for the MSSQL driver and not the MySql Driver

[ChrisL]

Try using this one maybe.

Func _MySQLConnect($ConHandle,$server, $db, $username, $password)
    
    DIM $SQLErr
    If $ConHandle = -1 then $ConHandle = $sqlLastConnection
    $ConHandle.Open ("DRIVER={MySQL ODBC 3.51 Driver};SERVER=" & $server & ";DATABASE=" & $db & ";uid=" & $username & ";pwd=" & $password & ";");<==Connect with required credentials
    If NOT @error then return 1
    Return SetError(1,0,0)
    
Endfunc

Edited October 20, 2008 by ChrisL

[franzp]

Hi,

i tried to connect to an mssql 2008 express. but i get this error message:

[Microsoft][sql Server Native Client 10.0][sql Server]Fehler bei der Anmeldung für den Benutzer 'user'.

the user exists with this credentials. I tried also the winlogin auth, but the same error message.

I tried this code: (changed only the DRIVER to {SQL Server Native Client 10.0}

expandcollapse popup

#include <Array.au3>
Global $sqlLastConnection;enables the use of -1 to access the last opened connection
Global $SQLErr
Global $MSSQLObjErr
Global $sqlIP = "DELL-XPS\SQLEXPRESS"
Global $sqlDB = "autoit"
Global $sqlTBL = "[autoit].[dbo].[liste]"
Global $sqluser = "user"
Global $sqlpasswd = "user"
   
_SQLRegisterErrorHandler();register the error handler to prevent hard crash on COM error
$con = _SQLStartup()
If @error Then MsgBox(0, "Error", "Error starting ADODB.Connection")
$sql = _SQLConnect(-1, $sqlIP, $sqlDB, $sqluser, $sqlpasswd)
If @error Then _DisplayError($SQLErr)
;$refresh_query = "Select LOCATIONID = TABLECODE, LOCATIONNAME = DESCRIPTION from   TABLECODES where  TABLETYPE = 10 order  by DESCRIPTION"
$refresh_query = "SELECT TOP 1000 [Anbieter]      ,[Name]     ,[Kennwort]     ,[Typ]      ,[Bemerkung]    ,[Sonstiges]  FROM [autoit].[dbo].[liste]"
ProgressSet(20)
$exec = _SQLExecute(-1, $refresh_query)
$locations = _SQLGetData2D($exec)
_SQLClose()
_ArrayDisplay($locations)
   
 ; ------------------------------------------------------------------------------
 ;
 ; AutoIt Version: 3.1.1 (beta)
 ; Language:       English
 ; Description: Functions to handle SQL databases.
 ; Author Chris Lambert
 ; ------------------------------------------------------------------------------
   
   
 ;#include-once
   
   Func _DisplayError($vText)
    MsgBox(0 + 16 + 262144, "SQL Error", $vText)
    ConsoleWrite($vText)
   EndFunc ;==>_DisplayError
   
   
   Func _SQLRegisterErrorHandler($Func = "_SQLErrFunc")
    If ObjEvent("AutoIt.Error") = "" Then $MSSQLObjErr = ObjEvent("AutoIt.Error", $Func)
   EndFunc ;==>_SQLRegisterErrorHandler
   
   
   Func _SQLUnRegisterErrorHandler()
    $MSSQLObjErr = ""
   EndFunc ;==>_SQLUnRegisterErrorHandler
   
   
   Func _SQLStartup()
    Dim $SQLErr
    $adCN = ObjCreate("ADODB.Connection");<==Create SQL connection
    If IsObj($adCN) Then
        $sqlLastConnection = $adCN
        Return $adCN
    Else
        Return SetError(1, 0, 0)
    EndIf
   EndFunc ;==>_SQLStartup
   
   
   Func _SQLConnect($ConHandle, $server, $db, $username, $password)
    Dim $SQLErr
    If $ConHandle = -1 Then $ConHandle = $sqlLastConnection
    $ConHandle.Open("DRIVER={SQL Server Native Client 10.0};SERVER=" & $server & ";DATABASE=" & $db & ";uid=" & $username & ";pwd=" & $password & ";");<==Connect with required credentials 
    If Not @error Then Return 1
    Return SetError(1, 0, 0)
   EndFunc ;==>_SQLConnect
   
   Func _SQLClose($ConHandle = -1)
    If $ConHandle = -1 Then $ConHandle = $sqlLastConnection
    $ConHandle.Close
   EndFunc ;==>_SQLClose
   
   Func _SQLExecute($ConHandle = -1, $query = "")
    Dim $SQLErr
    Local $ret
    
    If $ConHandle = -1 Then $ConHandle = $sqlLastConnection
    $ret = $ConHandle.Execute($query)
    
    If @error Then Return SetError(1, 0, 0)
    Return $ret
   EndFunc ;==>_SQLExecute
   
   Func _SQLGetDataAsString($objquery, $ReturnColumnNames = 1, $delim = "|")
    If Not IsObj($objquery) Then
        $SQLErr = "Data passed is an invalid object"
        Return SetError(1, 0, 0)
    EndIf
    
    Dim $ret
    Local $i
    With $objquery
        If $ReturnColumnNames Then
            For $i = 0 To .Fields.Count - 1;get the column names and put into 0 array element
                $ret &= .Fields($i).Name & $delim
            Next
            If StringRight($ret, 1) = $delim Then $ret = StringTrimRight($ret, 1)
            $ret &= @CRLF
        EndIf
        
        While Not .EOF
            For $i = 0 To .Fields.Count - 1
                $ret &= .Fields($i).Value & $delim
            Next
            If StringRight($ret, 1) = $delim Then $ret = StringTrimRight($ret, 1)
            $ret &= @CRLF
            .MoveNext; Move to next row
        WEnd
    EndWith
    Return $ret
   EndFunc ;==>_SQLGetDataAsString
   
   Func _SQLGetData2D($objquery, $ReturnColumnNames = 1)
    
    If Not IsObj($objquery) Then
        $SQLErr = "Data passed is an invalid object"
        Return SetError(1, 0, 0)
    EndIf
    
    Dim $ret
    Dim $SQLErr
    Local $i, $aTmp
    
    With $objquery
        
        $ret = .GetRows()
        
        If IsArray($ret) Then
            $Dims = UBound($ret, 2)
            $Rows = UBound($ret)
            
            If $ReturnColumnNames Then
                
                ReDim $ret[$Rows + 1][$Dims];Adjust the array to fit the column names and move all data down 1 row
                
                For $x = $Rows To 1 Step -1
                    For $y = 0 To $Dims - 1
                        $ret[$x][$y] = $ret[$x - 1][$y]
                    Next
                Next
        ;Add the coloumn names
                For $i = 0 To $Dims - 1;get the column names and put into 0 array element
                    $ret[0][$i] = .Fields($i).Name
                Next
            EndIf;$ReturnColumnNames
        Else
            SetError(2)
            $SQLErr = "Unable to retreive data"
        EndIf;IsArray()
        
   ;Old method not used anymore but left in commented out until the new method is proven to have no issues
   ;While NOT .EOF
   ;    ReDim $ret[UBound($ret, 1) + 1][Ubound($ret,2)]; get each row of data
   ;        For $i = 0 To .Fields.Count - 1
   ;            $ret[UBound($ret, 1) - 1][$i] = .Fields($i).Value
   ;        Next
   ;.MoveNext; Move to next row
   ;WEnd
    EndWith
    Return $ret
   EndFunc ;==>_SQLGetData2D
   
   
 ;custom error handler
   Func _SQLErrFunc()
   
    $SQLErr = $MSSQLObjErr.description
    SetError(1)
   
   EndFunc ;==>_SQLErrFunc

Edited May 1, 2009 by franzp

[ChrisMasc]

Hi franzp,

I have the same Problem like you with connecting to SQL2008 Express. Have you ever solved this Problem.

Thanks in advance,

best Regards

Chris

[ChrisL]

If you go back to the _SQL.au3 topic you can see there is a post #754624 that says:

Thank you!! Thank you!! Thank you!!

As a newb I had been struggling with getting ANY connection working. The _sql.au3 solved my problems (I am still working on why). I am testing with SQLExpress 2008 and did not change anything except the server/db name.

I think your problems maybe you need to reference a server/db or installation reference sqlexpress2008/db