Syed23 Posted August 5, 2011 Share Posted August 5, 2011 (edited) Hi Friends, I have downloaded _SQL.au3 from this forum. Currently i have Database server name , user credentials to connect but i am getting confused with the below function how to make it to work. can some one help me on this? Also does Auto it support ADO - ODBC? or do we need to install any driver or something ? please help me! expandcollapse popup; #FUNCTION# =================================================================== ; Name ..........: _SQL_Connect ; Description ...: Starts a Database Connection ; Syntax.........: _SQL_Connect($ADODBHandle,$server, $db, $username, $password) ; Parameters ....: $ADODBHandle - ADODB.Connection handle. ; $server - The server to connect to. ; $db - The database to open. ; $username - username for database access. ; $password - password for database user. ; 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 ........: Chris Lambert ; Modified ......: ; Remarks .......: ; Related .......: ; Link ..........; ; Example .......; no ; ============================================================================== 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};SERVER=" & $server & ";DATABASE=" & $db & ";uid=" & $username & ";pwd=" & $password & ";") ;<==Connect with required credentials 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 Edited August 5, 2011 by Syed23 Thank you,Regards,[font="Garamond"][size="4"]K.Syed Ibrahim.[/size][/font] Link to comment Share on other sites More sharing options...
hannes08 Posted August 5, 2011 Share Posted August 5, 2011 Hi Syed, try: _SQL_RegisterErrorHandler() $oADODB = _SQL_Startup() If $oADODB = $SQL_ERROR Then MsgBox(0 + 16, "SQL Error", "Error: " & _SQL_GetErrMsg()) Exit 3 EndIf If _sql_Connect(-1, "host", "database", "user", "password") = $SQL_ERROR Then MsgBox(0 + 16 + 262144, "Error", _SQL_GetErrMsg()) _SQL_Close() Exit 2 EndIf Regards,Hannes[spoiler]If you can't convince them, confuse them![/spoiler] Link to comment Share on other sites More sharing options...
Syed23 Posted August 5, 2011 Author Share Posted August 5, 2011 (edited) Hi Syed, try: _SQL_RegisterErrorHandler() $oADODB = _SQL_Startup() If $oADODB = $SQL_ERROR Then MsgBox(0 + 16, "SQL Error", "Error: " & _SQL_GetErrMsg()) Exit 3 EndIf If _sql_Connect(-1, "host", "database", "user", "password") = $SQL_ERROR Then MsgBox(0 + 16 + 262144, "Error", _SQL_GetErrMsg()) _SQL_Close() Exit 2 EndIf Thanks for the quick reply! i have a quick question, in this Example what is the Host = local machine name ? or what ? Edited August 5, 2011 by Syed23 Thank you,Regards,[font="Garamond"][size="4"]K.Syed Ibrahim.[/size][/font] Link to comment Share on other sites More sharing options...
Syed23 Posted August 5, 2011 Author Share Posted August 5, 2011 (edited) Hi Hannes123, I got the below error message. Could you please tell me why it happens? ############################### err.description is: [Microsoft][ODBC SQL Server Driver][sql Server]Cannot open database requested in login 'Server Name'. Login fails. 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:\Windows\HELP\ADO270.CHM err.helpcontext is: 1240653############################### !>17:04:51 AutoIT3.exe ended.rc:2 >Exit code: 2 Time: 6.387 Edited August 5, 2011 by Syed23 Thank you,Regards,[font="Garamond"][size="4"]K.Syed Ibrahim.[/size][/font] Link to comment Share on other sites More sharing options...
hannes08 Posted August 5, 2011 Share Posted August 5, 2011 Host is the IP or DNS name of the PC/Server running the SQL Database server service. Regards,Hannes[spoiler]If you can't convince them, confuse them![/spoiler] Link to comment Share on other sites More sharing options...
Syed23 Posted August 5, 2011 Author Share Posted August 5, 2011 (edited) quote name='Hannes123' timestamp='1312544608' post='916031'] Host is the IP or DNS name of the PC/Server running the SQL Database server service. Edited August 5, 2011 by Syed23 Thank you,Regards,[font="Garamond"][size="4"]K.Syed Ibrahim.[/size][/font] Link to comment Share on other sites More sharing options...
hannes08 Posted August 5, 2011 Share Posted August 5, 2011 Well, I'd say you've forgotten to set the database name. _sql_Connect(-1, "servername", "my_database", "username", "password") Regards,Hannes[spoiler]If you can't convince them, confuse them![/spoiler] Link to comment Share on other sites More sharing options...
Syed23 Posted August 5, 2011 Author Share Posted August 5, 2011 Well, I'd say you've forgotten to set the database name. _sql_Connect(-1, "servername", "my_database", "username", "password") you are correct it worked now Thanks a lot i will get back to you soon with another question Thank you,Regards,[font="Garamond"][size="4"]K.Syed Ibrahim.[/size][/font] Link to comment Share on other sites More sharing options...
Syed23 Posted August 5, 2011 Author Share Posted August 5, 2011 Hannes123, Now i am executing the SQL query with the function _SQLExecute() the code executes without error but i don't how to display the result..... please help me! Thank you,Regards,[font="Garamond"][size="4"]K.Syed Ibrahim.[/size][/font] Link to comment Share on other sites More sharing options...
hannes08 Posted August 5, 2011 Share Posted August 5, 2011 Hello Syed, depending on what you are doing, you'll need _SQLExecute or _SQL_Query* functions. Use _SQLExecute for statements like UPDATE, INSERT or DELETE. Use _SQL_Query* functionS for SELECT statements. Typically you'll recieve a 1 or 2 dimensional array containing the results. Regards,Hannes[spoiler]If you can't convince them, confuse them![/spoiler] Link to comment Share on other sites More sharing options...
Syed23 Posted August 5, 2011 Author Share Posted August 5, 2011 Hello Syed,depending on what you are doing, you'll need _SQLExecute or _SQL_Query* functions.Use _SQLExecute for statements like UPDATE, INSERT or DELETE.Use _SQL_Query* functionS for SELECT statements. Typically you'll recieve a 1 or 2 dimensional array containing the results.Hi Hannes, As you suggested i found a function _SQL_QuerySingleRow(). But the problem here is it returns only a single row of the data. But where i have multiples rows on it. How to overcome this? Also i have found a query like parsing with XML like"SQL Query" FOR XML AUTO.... is there a way to parse or save it as a XML ? Thank you,Regards,[font="Garamond"][size="4"]K.Syed Ibrahim.[/size][/font] Link to comment Share on other sites More sharing options...
hannes08 Posted August 5, 2011 Share Posted August 5, 2011 Here's a little example of what I did: Dim $iRval, $aData, $iRows, $iColumns, $t $t = TimerInit() $iRval = _SQL_GetTable2D($oADODB,"SELECT * FROM FDB WHERE MAC='001122334455';",$aData,$iRows,$iColumns) If $iRval = $SQL_OK then $t = _Timer_Diff($t) GUICtrlSetData($Label4,"Query took " & Round($t_1,2)/1000 & " Seconds") For $i = 1 To $iRows ConsoleWrite:($aData[$i][0] & "|" & $aData[$i][2] & "|" & $aData[$i][1] & @CRLF) Next Else $t = 0 EndIf Regards,Hannes[spoiler]If you can't convince them, confuse them![/spoiler] Link to comment Share on other sites More sharing options...
Syed23 Posted August 5, 2011 Author Share Posted August 5, 2011 Here's a little example of what I did: Dim $iRval, $aData, $iRows, $iColumns, $t $t = TimerInit() $iRval = _SQL_GetTable2D($oADODB,"SELECT * FROM FDB WHERE MAC='001122334455';",$aData,$iRows,$iColumns) If $iRval = $SQL_OK then $t = _Timer_Diff($t) GUICtrlSetData($Label4,"Query took " & Round($t_1,2)/1000 & " Seconds") For $i = 1 To $iRows ConsoleWrite:($aData[$i][0] & "|" & $aData[$i][2] & "|" & $aData[$i][1] & @CRLF) Next Else $t = 0 EndIf Hey Hannes123, Thanks a lot This what i wanted and it works perfectly Thank you,Regards,[font="Garamond"][size="4"]K.Syed Ibrahim.[/size][/font] 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