_SQL.au3. ADODB.Connection

[t0nZ]

For now I had no time, but I'am interested.

The main difference for me is that if you configure the odbc connector you can force driver level read only behaviour.

I let you know  if I try...

[Rijswijker]

Hi,

I am not able to execute the following sql query.

_SQL_Execute(-1,"INSERT INTO KKSPERRE (HashNr, TeilKkNr, KkSperrbehandlung, KkGes) VALUES('1234', CONVERT(varbinary, '12345678901234'), 0, 'VIEL')")

I get the error.

"C:\Users\Administrator\Desktop\_sql.au3" (402) : ==> The requested action with this object has failed.:
$hQuery = $hConHandle.Execute($vQuery)
$hQuery = $hConHandle^ ERROR

I think the issue is in the CONVERT() part, i am not able to test it without the convert part because it is an existing running database.
If i run the query in the SQL Management Studio the query is executing fine.

How can i solve this with this library?
Thanks in advance

[mLipok]

Did you try my ADO.au3 UDF   ?

Did you set up COM Error Handler ?
What you get from COM Error Handler ?

 

[mLipok]

9 hours ago, Rijswijker said:

I think the issue is in the CONVERT() part, i am not able to test it without the convert part because it is an existing running database.
If i run the query in the SQL Management Studio the query is executing fine.

This mean that your QUERY is correct. The problem has other nature.
Maybe you are using inproper driver/provider in your connection string ?

 

[Rijswijker]

3 hours ago, mLipok said:

Did you try my ADO.au3 UDF   ?

Did you set up COM Error Handler ?
What you get from COM Error Handler ?

 

I tried now, but i get an error:

###############################
ADO.au3 (769) : ==> COM Error intercepted !
$oADO_Error.description is:     [Microsoft][ODBC SQL Server Driver][DBNETLIB]SSL Security error
$oADO_Error.windescription:     Exception occurred.

$oADO_Error.number is:  80020009
$oADO_Error.lastdllerror is:    0
$oADO_Error.scriptline is:  769
$oADO_Error.source is:  Microsoft OLE DB Provider for ODBC Drivers
$oADO_Error.helpfile is:    
$oADO_Error.helpcontext is:     0
###############################

Is the SQL driver to old from the Autoit engine?

[mLipok]

I think you have problem with your connection string.
And could you try using SQLNCLI  provider ?

 

[Rijswijker]

I still get no database connection, here the part of my code.

Code:

Local $sDriver = 'SQLNCLI'
    Local $sDatabase = 'PARK_DB'
    Local $sServer = '172.16.240.148'
    Local $sUser = 'administrator'
    Local $sPassword = 'password'

    ; Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'
    ; 2nd attempt
    Local $sConnectionString = 'PROVIDER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'

    ; Create connection object
    Local $oConnection = _ADO_Connection_Create()

    ; Open connection with $sConnectionString
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)

Error(s):

###############################
ADO.au3 (769) : ==> COM Error intercepted !
$oADO_Error.description is:     [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
$oADO_Error.windescription:     Exception occurred.

$oADO_Error.number is:  80020009
$oADO_Error.lastdllerror is:    0
$oADO_Error.scriptline is:  769
$oADO_Error.source is:  Microsoft OLE DB Provider for ODBC Drivers
$oADO_Error.helpfile is:    
$oADO_Error.helpcontext is:     0
###############################

2nd attempt
###############################
ADO.au3 (769) : ==> COM Error intercepted !
$oADO_Error.description is:     Provider cannot be found. It may not be properly installed.
$oADO_Error.windescription:     Exception occurred.

$oADO_Error.number is:  80020009
$oADO_Error.lastdllerror is:    0
$oADO_Error.scriptline is:  769
$oADO_Error.source is:  ADODB.Connection
$oADO_Error.helpfile is:    C:\Windows\HELP\ADO270.CHM
$oADO_Error.helpcontext is:     1240655
###############################

 

[mLipok]

SQLNCLI is an abbreviation, you should check the name here:

 

btw.

It should be instaled, as this is not a standard Windows component.

Edited August 17, 2018 by mLipok

[Rijswijker]

Thanks the application is working when i run it on the SQL Server it self.
The tool should be running on the server it self so the problem is solved for now.

However, I still have a question, how can I find out which driver is using the SQL Server?
I now use the Driver "SQL Server Native Client 11.0", and I can connect from the server itself.
But from a remote computer I can not connect to the SQL Server using the same driver, how could that be?

Maybe the (small) difference in the versions?
I am not really at home in this, but how can you update a driver or install it on the client?
Is this only possible to install an SQL Server on the client?

I hope you can answer my questions, thanks in advance!

[mLipok]

This is related to connection string and firewall on sql server.

 

 

Edited August 17, 2018 by mLipok

[skynet0504]

How to use it ??? 

_sql.au3

[mLipok]

Start using ADO.au3

[Holy_Boy]

Hi everyone.

I have a script that does a SQL query and returns it to a label, I have a few servers that has the same DB, so i want to query the first server and if it doesnt respond i wont to query the 2nd ad so on, what is the best way to do it? I created a if else statement but the third one dosnt seem to work and also the timeout is very long, i tried to "use _SQL_ConnectionTimeout(-1,2)" but im not sure if i use it the right way

here is the code im using to connect to SQL

expandcollapse popup

connect()
Func connect()
_sql_startup()
if @error Then
   MsgBox(0,"Error","Cannot Start Sql Service ")
   Exit
EndIf


_SQL_RegisterErrorHandler()

Local $server1,$server2,$server3
;global $server="S1"
;Global $database="tempDB"
Global $username="ab"
global $password="abc000"
$server=IniRead($conf,"Server1","SN","")
$database=IniRead($conf,"Server1","DB","")
 $server1=_SQL_Connect(-1,$server,$database,$username,$password)
 _SQL_ConnectionTimeout(-1,2)

 if $server1=$SQL_ERROR Then
    ;$server="S2"
    $server=IniRead($conf,"Server2","SN","")
    $database=IniRead($conf,"Server2","DB","")
    $server2=_SQL_Connect(-1,$server,$database,$username,$password)
    _SQL_ConnectionTimeout(-1,2)
 Else
    If $server2=$SQL_ERROR Then
    ;$server="S3"
    $server=IniRead($conf,"Server3","SN","")
    $database=IniRead($conf,"Server3","DB","")
    $server3=_SQL_Connect(-1,$server,$database,$username,$password)
    EndIf



   EndIf
   GUICtrlCreateLabel($server,@desktopwidth-30,@desktopheight-15)
EndFunc

 

[mLipok]

what kind of DBA it is ? MS SQL ? MySQL ? Postgres ? ... ?
 

 

[Andreik]

@mLipok It doesn't really matter. Since it's using ADODB all drivers should implement ConnectionTimeout.

@Holy_Boy Yes, _SQL_ConnectionTimeout() is the way to set connection timeout if you use this UDF. I think there are some other UDFs for databases, you might want to check AutoIt wiki. You can find such UDF in mLipok signature as well.

[mLipok]

1 hour ago, Andreik said:

@mLipok It doesn't really matter. Since it's using ADODB all drivers should implement ConnectionTimeout.

I was thinking about connection string to replicated server.

But the question has lack of information.

Edited July 30, 2023 by mLipok

[mLipok]

You use wrong logic here:

23 hours ago, Holy_Boy said:

 Else
    If $server2=$SQL_ERROR Then

try this:

 ElseIf $server2=$SQL_ERROR Then

Edited July 31, 2023 by mLipok

[Holy_Boy]

On 7/30/2023 at 7:08 AM, mLipok said:

what kind of DBA it is ? MS SQL ? MySQL ? Postgres ? ... ?
 

 

Hi, sorry for my late response

Yes it's MS sql server express 

[Holy_Boy]

On 7/30/2023 at 11:23 AM, Andreik said:

@mLipok It doesn't really matter. Since it's using ADODB all drivers should implement ConnectionTimeout.

@Holy_Boy Yes, _SQL_ConnectionTimeout() is the way to set connection timeout if you use this UDF. I think there are some other UDFs for databases, you might want to check AutoIt wiki. You can find such UDF in mLipok signature as well.

Thanks for your response

I know that _SQL_ConnectionTimeout() should set the timeout amount, but for some reason it takes about 30 seconds (which I think is the default) until it tries the second server when the first is disconnected.

Regarding other UDFs for sql, maybe I'll change it in the future, but I wanna try to make it work with this one first 

[Andreik]

Quote

Settings and Return Values

Sets or returns a Long value that indicates, in seconds, how long to wait for the connection to open. Default is 15.

MSDN says the default ConnectionTimeout is 15 seconds. I don't see a particular reason why it takes 30 seconds in your case but you never know with these DB providers. Some of them might not even implement ConnectionTimeout. What provider do you use? Let us see the connection string.