erezlevi Posted October 20, 2008 Share Posted October 20, 2008 (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: 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 Link to comment Share on other sites More sharing options...
ChrisL Posted October 20, 2008 Share Posted October 20, 2008 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 [u]Scripts[/u]Minimize gui to systray _ Fail safe source recoveryMsgbox UDF _ _procwatch() Stop your app from being closedLicensed/Trial software system _ Buffering Hotkeys_SQL.au3 ADODB.Connection _ Search 2d Arrays_SplashTextWithGraphicOn() _ Adjust Screen GammaTransparent Controls _ Eventlogs without the crap_GuiCtrlCreateFlash() _ Simple Interscript communication[u]Websites[/u]Curious Campers VW Hightops Lambert Plant Hire Link to comment Share on other sites More sharing options...
ChrisL Posted October 20, 2008 Share Posted October 20, 2008 (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 October 20, 2008 by ChrisL [u]Scripts[/u]Minimize gui to systray _ Fail safe source recoveryMsgbox UDF _ _procwatch() Stop your app from being closedLicensed/Trial software system _ Buffering Hotkeys_SQL.au3 ADODB.Connection _ Search 2d Arrays_SplashTextWithGraphicOn() _ Adjust Screen GammaTransparent Controls _ Eventlogs without the crap_GuiCtrlCreateFlash() _ Simple Interscript communication[u]Websites[/u]Curious Campers VW Hightops Lambert Plant Hire Link to comment Share on other sites More sharing options...
franzp Posted May 1, 2009 Share Posted May 1, 2009 (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} 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 Link to comment Share on other sites More sharing options...
ChrisMasc Posted April 19, 2010 Share Posted April 19, 2010 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 Link to comment Share on other sites More sharing options...
ChrisL Posted April 19, 2010 Share Posted April 19, 2010 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 [u]Scripts[/u]Minimize gui to systray _ Fail safe source recoveryMsgbox UDF _ _procwatch() Stop your app from being closedLicensed/Trial software system _ Buffering Hotkeys_SQL.au3 ADODB.Connection _ Search 2d Arrays_SplashTextWithGraphicOn() _ Adjust Screen GammaTransparent Controls _ Eventlogs without the crap_GuiCtrlCreateFlash() _ Simple Interscript communication[u]Websites[/u]Curious Campers VW Hightops Lambert Plant Hire Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now