ADO.au3 UDF - BETA - Support Topic

[Skysnake]

So far so good

Please indicate if my attempts in #315 above is the best way to process these functions?

I note that PREPARE still does not work. That is not a priority.

SQL works very well on 2.1.15. thank you

 

Skysnake

[Skysnake]

Dear @mLipok

Congratulations on a great piece of work.

 

I have implemented mainly ADO_Execute with SQL functions, where these are

• (a) simple inserts or updates (do not expect a return value)
• (b) select with expected result set (not blank)
• (c) select where the result set returned blank.

In all these cases the UDF performed remarkably well.  Thank you for the effort.

 

I have however stumbled over something new.  I do not think this is related to any previous questions, but I do not know how to fix it.

Servers drop idle connections.  Fine.

Question: how to monitor locally when that connection is dropped?  

I had an open connection, switched off the wifi and ADO_IsReady still returned True.  I am looking for a "connection closed by server"  test, which will allow for an auto-reconnect.  How will I know the connection is no longer valid before trying to use it?

Something like this:

https://www.postgresql.org/docs/9.5/static/libpq-connect.html

PQping reports the status of the server. It accepts connection parameters identical to those of PQconnectdb, described above. It is not necessary to supply correct user name, password, or database name values to obtain the server status; however, if incorrect values are provided, the server will log a failed connection attempt.

Interesting Microsoft uses a normal ping:

https://gallery.technet.microsoft.com/scriptcenter/60abecf9-b2f7-45a0-921a-15f296c46362

   strCommand = "%comspec% /c ping -n 3 -w 1000 " & strComputer & "" 

I have come up with this solution.  I am not sure if this is good/correct.  My Ping consistently return '1' - which means 'success' - it also means that the AutoIt Ping command can ping objects...

Local $OnlineStatus = __ADO_Connection_IsReady($g_DB)

    ; Ping the Connection Object with a timeout of 250ms.
    Local $iPing = Ping($g_DB, 250)
    ConsoleWrite($iPing & @CRLF)

 

Is this possible with ADO? 

*********************************

Update: Google reports the typical "keep connection Alive" strategy requires a periodic "Select 1" against the connection.  After testing I can now confirm that: the default AutoIt Ping command, works perfectly.

The example works.

**********************************

 

Thanks for everything you have done.

Skysnake

 

 

Edited June 13, 2017 by Skysnake
update on Ping ADO connection object

[Warsaw]

I have an Excel sheet that I am pulling information from but I can't seem to get the formulas. How would I get the formula from a cell or range of cells using the SQL query functions? 

[mLipok]

I'm  not sure if this is possible.
just try like simple Excel file - example should be included in this UDF.

[GMK]

Question: How can I connect with a 32-bit driver from Windows 10?

There is no 64-bit driver for Progress OpenEdge 10.2B, so it's not in HKLM\SOFTWARE\ODBC\ODBCINST.INI registry, which is where it appears to be looking. There is a 32-bit driver located in HKLM\SOFTWARE\WOW6432Node\ODBC\ODBCINST.INI.

Func _Example_OpenEdge()
    Local $sDriver = 'Progress OpenEdge 10.2B Driver'
    Local $sHost = '46539dbs'
    Local $sPort = '35028'
    Local $sDB = 'horizon'
    Local $sUID = 'report'
    Local $sPwd = 'xxxx'

    Local $sConnectionString = StringFormat('DRIVER={%s};HOST=%s;PORT=%s;DB=%s;UID=%s;PWD=%s;DIL=0', $sDriver, $sHost, $sPort, $sDB, $sUID, $sPwd)
    ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $sConnectionString = ' & $sConnectionString & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console

    Local $sQuery = 'SELECT SI_AGMT.SI_ACCT_NBR FROM NISCADMIN.SI_AGMT WHERE SI_AGMT.SI_AGMT_NBR = 9999999999'
    _Example_1_RecordsetToConsole($sConnectionString, $sQuery)
    _Example_2_RecordsetDisplay($sConnectionString, $sQuery)
    _Example_3_ConnectionProperties($sConnectionString)
EndFunc     ;==>_Example_OpenEdge

@@ Debug(44) : $sConnectionString = DRIVER={Progress OpenEdge 10.2B Driver};HOST=46539dbs;PORT=35028;DB=horizon;UID=report;PWD=xxxx;DIL=0
>Error code: 0
###############################
ADO.au3 v.2.1.15 BETA (1100) : ==> 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:  1100
$oADO_Error.source is:  Microsoft OLE DB Provider for ODBC Drivers
$oADO_Error.helpfile is:    
$oADO_Error.helpcontext is:     0
###############################

EDIT: One simple line fixed the problem: #AutoIt3Wrapper_UseX64=n

Edited July 19, 2017 by GMK
Found the solution!

[mLipok]

On 19.07.2017 at 7:10 PM, GMK said:

EDIT: One simple line fixed the problem: #AutoIt3Wrapper_UseX64=n

You should remember that any 64 Bit APP will need to use 64Bit drivers and components.
When you use IE64Bit you should use AutoIt 64Bit  / Java64 etc ..... I'm not sure if you also need 64Printer Drivers in such cases.

For example if I want to use AutoIt 64Bit then I must use: IE 64Bit, Debenu 64Bit ADO 64Bit, Chilkat 64Bit .............

 

[drr]

Hi,

I'm trying to use SSPI authentication with _ADO_Connection_OpenMSSQL function by setting the last parameter to False:

_ADO_Connection_OpenMSSQL($oConnection, $sServer, $sDatabase, $sUser, $sPassword, 'AppName', @ComputerName & '_' & 'Program_UID',False)

Unfortunately it produces error:

"C:\Program Files (x86)\AutoIt3\Include\ADO.au3" (1147) : ==> Variable must be of type "Object".:
$oConnection.Properties("Integrated Security").Value = "SSPI"
$oConnection.Properties("Integrated Security")^ ERROR

Is there anything that I might be doing wrong here? Any help appreciated.

[Skysnake]

Hi, and welcome

I had a similar issue with a Postgres connection.  I could also not get additional values to work in the connection string, but once connected I could "send" those additional request over the open connection.

Worth a try?

Skysnake

[mLipok]

I will look at this soon.

 

[drr]

On 7/25/2017 at 8:00 PM, mLipok said:

I will look at this soon.

 

Hi mLipok

Did you have a chance to look at the problem?

Thanks!

[tarretarretarre]

Howcome you pass the ADODB connections as reference, objects are pointers and will not be affected by ByRef unless you want to destryo the object.

I am using this with AutoItObject and then i have to do this 

 

local $handle = $someObj.handle
_Ado_xxx($handle) 

instead of just 

 

_Ado_xxx($someObj.handle)

 

[mLipok]

I do not quite understand your problem, try to re-wording or be more specyfic.
 

[ioripalm]

Can I connect a firebird database without ODBC?

NOW I use ODBC Setup a firebird can connect a network database, and use ADO.au3 can get data.

How to without ODBC connect a firebird database?Thank you !!!!

Edited September 27, 2017 by ioripalm

[ptrex]

Possible if you use PowerShell and the FireBird .net Provider see here : http://firebirdsql.org/en/net-provider/

 

[Skysnake]

14 hours ago, ioripalm said:

How to without ODBC connect a firebird database?Thank you !!!!

Hi. The ADO requires the Windows ODBC driver to be set up.  If you do not want to use the ODBC then the ADO UDF probably will not work for you. 

[Skysnake]

Question

How to detect / determine if requested ODBC driver is not available? Not installed?  Is there a specific function / error code to check for?

Skysnake

[Skysnake]

In case anyone was wondering, the ADO.UDF DOES return a data source not found error, but I was hoping for a simple test without the ADO overhead.

###############################
ADO.au3 v.2.1.15 BETA (1100) : ==> 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:  1100
$oADO_Error.source is:  Microsoft OLE DB Provider for ODBC Drivers
$oADO_Error.helpfile is:    
$oADO_Error.helpcontext is:     0
###############################

 

[mLipok]

Could you show your ConnectionString ? .... of course with "hiden" XXXX passwords.

Edited October 4, 2017 by mLipok

[mLipok]

8 minutes ago, Skysnake said:

$oADO_Error.source is: Microsoft OLE DB Provider for ODBC Drivers

Did you try to use: _ADO_GetProvidersList() ?

 

[Skysnake]

About 3 minutes on my computer... I was hoping for a quick check for a specific driver

Does not show my dBase driver

Am I looking in the wrong place?

Edited October 4, 2017 by Skysnake