Trying to Query and Write data to an SQL Enterprise DB

[wisem2540]

I have been playing around with the _SQL UDF from ChrisL, but the last post in that thread was well over a year ago.  At this point, I am not even able to get a valid connection to the DB.  I was able to create an ODBC connection to the Database, so I know the info I was given is correct.  I would like to proceed without having the ODBC pre-defined.  Any direction would be most helpful

[mLipok]

Show your code snippet, then sombody will be able to answer to you what you doing wrong.

mLipok

 

[wisem2540]

I am simply using the example from the UDF to initiate the connection.....  it fails.

Msgbox(0,"","Start the Script and load the 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,"Error",_SQL_GetErrMsg())


      If _sql_Connect(-1,"Server","DBName","User","Pass") = $SQL_ERROR then
        Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())
        _SQL_Close()
        Exit
    EndIf

 

[mLipok]

Which UDF you download ?
Original or my modyfication ?

 

[wisem2540]

Most likely the original.  I can go back and search for your post and give that a go

[mLipok]

The last version is in the end of this following post:

 

 

 

Edited January 21, 2016 by mLipok

[wisem2540]

Do you mean this?  This appears to only cover connecting, and not anything else.  Should we assume that the other functions in the original are ok?

; #FUNCTION# ====================================================================================================================
; Name ..........: _SQL_CONNECTION_STRING_SQLAuth
; Description ...:
; Syntax ........: _SQL_CONNECTION_STRING_SQLAuth($sServer, $sDataBase, $sUserName, $sPassword[, $bUseProviderInsteadDriver = True])
; Parameters ....: $sServer             - A string value.
;                  $sDataBase           - A string value.
;                  $sUserName           - A string value.
;                  $sPassword           - A string value.
;                  $bUseProviderInsteadDriver- [optional] A binary value. Default is True.
; Return values .: $__sSQL_Last_ConnectionString
; Author ........: mLipok
; Modified ......:
; Remarks .......: TODO - description
; Related .......:
; Link ..........:
; Example .......: No
; ===============================================================================================================================
Func _SQL_CONNECTION_STRING_SQLAuth($sServer, $sDataBase, $sUserName, $sPassword, $bUseProviderInsteadDriver = True)
    If $bUseProviderInsteadDriver then
        $__sSQL_Last_ConnectionString = "PROVIDER=" & _SQL_PROVIDER_VERSION() & ";SERVER=" & $sServer & ";DATABASE=" & $sDataBase & ";uid=" & $sUserName & ";pwd=" & $sPassword & ";"
    Else
        $__sSQL_Last_ConnectionString = "DRIVER={" & _SQL_DRIVER_VERSION() & "};SERVER=" & $sServer & ";DATABASE=" & $sDataBase & ";uid=" & $sUserName & ";pwd=" & $sPassword & ";"
    EndIf
    Return $__sSQL_Last_ConnectionString
EndFunc   ;==>_SQL_CONNECTION_STRING_SQLAuth

 

[mLipok]

Just give a try to the entire modified UDF.

[mLipok]

2 hours ago, wisem2540 said:

I am simply using the example from the UDF to initiate the connection.....  it fails.

Msgbox(0,"","Start the Script and load the 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,"Error",_SQL_GetErrMsg())


      If _sql_Connect(-1,"Server","DBName","User","Pass") = $SQL_ERROR then
        Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())
        _SQL_Close()
        Exit
    EndIf

 

 

Can you show conole output ?
I'm courious what ErrorHandler says.

 

[wisem2540]

Before I started using your UDF, it would just say it could not connect.  I have since switched to the UDF you pointed me to and now I receive this.....

"Operation is not allowed when the object is closed."

I am sure this is something I am doing.  But at this point, I am still trying to simply connect to the DB

[mLipok]

2 minutes ago, wisem2540 said:

Before I started using your UDF, it would just say it could not connect.  I have since switched to the UDF you pointed me to and now I receive this.....

"Operation is not allowed when the object is closed."

I am sure this is something I am doing.  But at this point, I am still trying to simply connect to the DB

So show how you try to use it and show console output.

 

[wisem2540]

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

Msgbox(0,"","Start the Script and load the 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,"Error",_SQL_GetErrMsg())

    Msgbox(0,"","trying to connect")
    _SQL_CONNECTION_STRING_SQLAuth("server","DB","user","pass")

 

 

##############################
err.description is:     Operation is not allowed when the object is closed.
err.windescription:    Exception occurred.

err.number is:     80020009
err.lastdllerror is:     0
err.scriptline is:     114
err.source is:     ADODB.Connection
err.helpfile is:     C:\Windows\HELP\ADO270.CHM
err.helpcontext is:     1240653###############################
+>16:44:23 AutoIt3.exe ended.rc:0
+>16:44:23 AutoIt3Wrapper Finished.
>Exit code: 0    Time: 3.904

Edited January 21, 2016 by wisem2540

[jchd]

Is your server named "server"?

Is the database named "DB"?

Is there an allowed username named "user"?

Is his passphrase equal to "pass"?

[wisem2540]

no....  I obviously am not going to share the personal DB info....

[mLipok]

Try this:

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

_example()
Func _example()
    Msgbox(0,"","Start the Script and load the 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,"Error",_SQL_GetErrMsg())

    Msgbox(0,"","trying to connect")
    _SQL_Connect($oADODB, "server","DB","user","pass")
EndFunc

 

[wisem2540]

Thanks for the fast reply. So I am getting login failed.  As I said before, I am confident in the username and password because it works for creating an ODBC connection.  Maybe just a syntax problem?  I tried server\username in the user field and that did not work either

 

###############################
err.description is:     Cannot open database "DBName" requested by the login. The login failed.
err.windescription:    Exception occurred.

err.number is:     80020009
err.lastdllerror is:     0
err.scriptline is:     207
err.source is:     Microsoft SQL Server Native Client 10.0
err.helpfile is:     
err.helpcontext is:     0###############################
###############################
err.description is:     Operation is not allowed when the object is closed.
err.windescription:    Exception occurred.

 

[mLipok]

Quote

Cannot open database "DBName" requested by the login. The login failed.

here:

_SQL_Connect($oADODB, "server","DB","user","pass")

Do you use existing "DB" name ?

Edit:

This following code works for me:
 

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

_example()
Func _example()
    _SQL_RegisterErrorHandler()
    $oADODB = _SQL_Startup()
    If $oADODB = $SQL_ERROR Then MsgBox(0 + 16 + 262144, "Error", _SQL_GetErrMsg())
    _SQL_Connect($oADODB, "localhost\SQLExpress", "", "user", "passwd")
    Local $aResult ,$iRows, $iCols
    _SQL_GetTable2D($oADODB,'Select * From [MyDataBaseName].dbo.[MyTableName]',$aResult ,$iRows, $iCols)
    _ArrayDisplay($aResult)
EndFunc   ;==>_example

 

Edited January 22, 2016 by mLipok

[wisem2540]

  The Info I got from the DBA showed a space in the DBname.  Removing the space solved my immediate issue.  I noticed the error when comparing the ODBC connection I made as a test.  Now I will try a few queries

[wisem2540]

Alright, I was able to query and return the data I need.  But I do not see a function in your UDF for updating the data.  Is there one

[mLipok]

_SQL_Execute($hConHandle = -1, $vQuery = "")