Sign in to follow this  
Followers 0
erezlevi

help - sql question.

6 posts in this topic

#1 ·  Posted (edited)

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:

#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 by erezlevi

Share this post


Link to post
Share on other sites



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

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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}

#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 by franzp

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


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
Sign in to follow this  
Followers 0