ADO.au3 UDF - BETA - Support Topic

[Skysnake]

On 2/1/2019 at 2:09 AM, faustf said:

the  func _DB_Sql_Execute ......   insert in ADO.au3 , i think is better 

The ADO UDF works very well as is. If you want this simplified approach, perhaps make a wrapper for the standard ADO function? 

 

[xrxca]

I've got record sets triggering the following COM Error:

ADO.au3 v.2.1.15 BETA (599) : ==> COM Error intercepted !
$oADO_Error.description is:     Rowset position cannot be restarted.
$oADO_Error.windescription:     Exception occurred.

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

This one was triggered on a call to _ADO_Recordset_ToString, with a result from a MySQL procedure call that returns a single row status result message, but also occurs on some simple queries as well.

I know exactly what the issue is, it is the $oRecordset.moveFirst() call.

I'd suggest that this:

If $oRecordset_Bookmark = Null Then
            $oRecordset.moveFirst()
        Else

be changed to:

If $oRecordset_Bookmark = Null Then
            If $oRecordset.Supports($ADO_adMovePrevious) Then $oRecordset.moveFirst()
        Else

This fixes the errors on my DSN's, but I've only tested a couple different drivers.

I changed all occurrences of .moveFirst in my copy of the udf (3 of them), to this format.

[mLipok]

Your proposal looks reasonabled.
Thanks.
Will look in my current DEV version.

btw.
I hope also @jchd will add his 2 cents.

[mLipok]

@xrxca 

Did you read other sites like this :
https://stackoverflow.com/questions/22356278/recordset-movefirst-rowset-position-cannot-be-restarted

specifically I want to ask you about this "Keith" answers:

Quote

Make sure you are not using a forward-only recordset. Recordsets are this way by default. Instead use a dynamic (adOpenDynamic) or static (adOpenStatic) cursor type.

 

[jchd]

Indeed not all ADO interface layers support all CursorTypes. Some support all inconditionally, some offer those the DB engine offers, some support only some (conditionally or not),  etc. The variable support for CursorLocation property makes things even muddier.

The same occurs with LockTypes. But all LockTypes can't be implemented only in the ADO wrapper: they can only reflect what the engine is capable of.

ADO specifies a fair number of recordset properties, methods and events.

Full cursor support can be counter-productive in that if the engine doesn't offer built-in efficient cursor support relying on the engine capabilities, the ADO has to implement it in its code, forcing the full recordset of a query to be stored in memory. Then it's too easy for inexperienced SQL programmer to forget that an inconspiscious (too broad) query may need yottabytes of RAM at the ADO level.

[Skysnake]

6 hours ago, jchd said:

ADO specifies a fair number of recordset properties, methods and events.

@mLipok

It may not be 2 cents, by I have a groszy

1. Document the ADO capabilities

2. Allows for user function extensions

 

[jchd]

1/ That's a huge task and ADO is already pretty well agnostically documented, for instance: https://www.w3schools.com/asp/ado_intro.asp

2/ What do you have in mind? Extend ADO objects, methods, properties & events ou useable SQL functions?
The former implies adding to the ADO code, which isn't always public.
The latter requires adding user functions to the target SQL engine.

[Skysnake]

Instead of trying to do everything for everybody, rather state the functionality of the ADO UDF clearly.  As in this example, there should be sufficient documentation to indicate that this is supported/not by the UDF.  It does not need a motivation.  A simple "this is the capability" statement.  It should be easy for the end-user to determine these UDF capabilities.

Whatever else the user requires, beyond the capabilities of the UDF, should then be the responsibility of that end-user, in such a way that the ADO UDF can be used as-is, with only additional functionality added to it.

So, yes the documentation part will be a challenge. I have in the past offered my assistance in this regard. I am still willing to contribute (a CHM for the ADO? )  and the ADO UDF must not prevent the user from extending its capabilities.

Good? 😎

Skysnake

[mLipok]

Will answer later.

Edited March 16, 2019 by mLipok

[jchd]

4 hours ago, Skysnake said:

Instead of trying to do everything for everybody, rather state the functionality of the ADO UDF clearly.

WARNING: I'm going to simplify things in the text wall below. I won't discuss the actual mess between ADO, ODBC, OLE DB and I'll group all this under the term ADO.

NOTE: ADO isn't always accessing a database. It goal is to provide high-level support to access data à la SQL. Said data might be a .CSV file, a filesystem, a bunch of industrial equipments, a relational (or not) DBMS, an Excel file, .. whatever.

ADO objects, methods, properties are not supported nor managed nor implemented by the AUtoIt ADO UDF! All machinery resides inside the ADO DLL you use.

@mLipok's UDF has very little room to "do everything for everybody". This AutoIt ADO UDF is -- like other AutoIt ADO UDFs -- just a relay between AutoIt coder and the specific ADO DLL (s)

he selected to use, itself dictated by the choice of the DB engine or data source itself.

There is zero possibility for this ADO.au3 to decide if object/method/property/parameter X is implemented or not in the DLL the coder uses. For instance, if method MoveFirst is not implemented in my_fancy_db_ado.dll there is no possibility for mLipok to substitute its own code. mLipok's UDF is generic (that's the whole purpose of ADO) and from its end, has zero knowledge about the guts inside the actual ADO DLL, which is dedicated to a particular DB engine.

Hence, mLipok documentation can only describe the higher-level AutoIt functions offered by his UDF, then refer to an agnostic documentation (like the one from W3School already mentionned) for the description of full-fledged generic ADO, which may have full or partial support in a given my_fancy_db_ado.dll.

As per adding functionality, I can't see how you can do that from outside the ADO DLL you use (for which you may not have source code).  Don't think "AutoItObject.au3": this is stuff to create your own objects, not expand functionality of objects implemented in a separate DLL.

To illustrate the inability of an AutoIt ADO UDF to work as you would want it to, consider the following SQL statement:

with recursive Rnd (n, x) as (
     values(1, Random())
       union all
     select n+1, Random() from Rnd
     where n < 10
)
select n, x from Rnd;

In your AutoIt code, you connect to some DB managed by engine YYY, using a DLL DDD you designate.
Then you invoke mLipok's ADO Execute function with this SQL statement; this yields a RecordSet object RRR. With RRR you read a number of rows using RRR.Movenext method (remember the RRR methods reside inside the DDD dll, so is beyond your control as well as mLipok's control).  At some point right in the middle or at EOF, you invoke method RRR.MoveFirst to re-read from the first row.  If this method isn't fully implemented in DDD how do you think mLipok can do it for you?

Even without a CTE (recursive or not), even without the non-determinism of Random() I used above, just think of a very simple plain select statement. In the middle of a number of .MoveNext, you invoke .MoveFirst and say that the ADO dll you use doesn't support it. The AutoIt ADO layer (wrapper) might store rows on the fly at each .MoveNext invokation so it would be theoritically possible to process .MoveFirst by its own.
But then, it would have to intercept and process by itself any subsequent change in cursor position, because the cursor inside the DLL is still pointing to the position it had before it processed your .MoveFirst and it would need to intercept .BOF, .EOF, ... as well and do the job of the DLL because of the loss of sync in the recordset between old DLL cursor and AutoIt ADO cursor.

Another point: you can't just re-issue the select statement because even if you could (a very big IF and you don't even have the right to do so at any rate), SQL makes no assumption about the order the rows fed to a recordset when an order clause by isn't specified.  It's uncommon for a DB engine to provide rows in a varying order when a select is issued twice in a row, but it would be perfectly legitimate to do so, for instance, to make best use of some cache. If you intend to process .MoveFirst (or its friends) outside the DLL, how would you do?

I hope this clarifies who does what. Thanks for your patience!

 

Edited March 16, 2019 by jchd

[Skysnake]

3 hours ago, jchd said:

I'm going to simplify things in the text wall below.

Thx @jchd

Thank you for the detail.  I think the important part is to state that ADO is very high level, and by way of exclusion, what it is not capable of, such as  ADO objects, methods, properties are not supported nor managed nor implemented , and that it is in the end merely a wrapper for which ever data driver (ODBC or otherwise?) being used...  This also means that the user must be very familiar with the capabilities of the chose data driver (DLL).

I use the ADO UDF daily and it works like a charm

[xrxca]

On 3/14/2019 at 4:24 PM, mLipok said:

@xrxca 

Did you read other sites like this :

Actually did a fair bit of research  (The rabbit-hole went straight on like a tunnel for some way, and then dipped suddenly down...)

On 3/14/2019 at 9:20 PM, jchd said:

Indeed not all ADO interface layers support all CursorTypes. Some support all inconditionally, some offer those the DB engine offers, some support only some (conditionally or not),  etc.

And this was my conclusion (and I rarely expect a 3rd party driver to support all features).  So I just wanted to suggest that if a recordset doesn't support the ability, it shouldn't throw an error in a function like _ADO_Recordset_ToString and since it's already checking compatibility for .Bookmark it made sense to me to also check before using .MoveFirst

 

[snoopie]

Thank you for this ADO interface. 

 

I'm using it with Postgres.

 

When executing an insert query with a return command something weird happens:

 

$Query = "INSERT INTO table VALUES (DEFAULT, 2, 3, 4) RETURNING id"
    
    $oRecordset = _ADO_Execute($oConnection, $Query, False)             ;Working as expected
    
    $aRecordset = _ADO_Execute($oConnection, $Query, True)              ;Creates three times the execution of $Query
    
    $sRecordset = _ADO_Recordset_ToString($oRecordset, "|", False)      ;Creates duplicate execution of $Query  
    
    $aRecordset = _ADO_Recordset_ToArray($oRecordset)                   ;Creates two more dublicates

 

[mLipok]

I'do not have access to Postgres currently, will try to check your case this week.
But what exactly you mean when you state "Creates duplicate execution" .
Is that mean that multiple records/rows were added instead single one per each _ADO_Execute() calling ?

[snoopie]

2 minutes ago, mLipok said:

I'do not have access to Postgres currently, will try to check your case this week.
But what exactly you mean when you state "Creates duplicate execution" .
Is that mean that multiple records/rows were added instead single one per each _ADO_Execute() calling ?

Yes, multiple rows appear in my database table.

 

I could imagine this happens on other SQL servers too, as soon you use an INSERT with RETURNING command.

Edited March 19, 2019 by snoopie
Line added

[jcobb]

I am having an issue with datetime fields not being correct in the array.  I am using MySQL

From my Script:
 

Local $SQL = "SELECT dob, last_updated FROM some_table;"

    Local $oRecordset = _ADO_Execute($oConnection, $SQL)

    Global $aRecordsetArray = _ADO_Recordset_ToArray($oRecordset, False)

    Global $resultSet = _ADO_RecordsetArray_GetContent($aRecordsetArray)

_ArrayDisplay($resultSet)

 

This query in MySQL returns:

1957-10-13 | 2019-05-22 08:35:14

 

_ArrayDisplay returns

19571013000000 | 20190522083514

 

I need to keep the format the same as the result in MySQL.  

 

Can anyone tell me how to get this to return properly in the array?

 

 

 

 

[jchd]

MySQL formats the timestamp this way for display, but the data itself fed by  _ADO_Recordset_ToArray is an unformatted string. You'll have to reformat it yourself.

[jcobb]

Thanks jchd, that is what I was thinking.

I am obviously new to AutoIT and this ADO UDF.  Can you tell me how I can reformat it inside the array?

I casted it as char in my MySQL Query, this fixed it.

 

Edited May 23, 2019 by jcobb

[jchd]

Try this:

; make up something to work with
Local $resultSet = [ _
    "19571013000000 | 20170522083514", _
    "19571013000000 | 20180620124506", _
    "19571013000000 | 20190522083514" _
]

For $i = 0 To UBound($resultSet) - 1
    $resultSet[$i] = Execute("'" & StringRegExpReplace($resultSet[$i], "(\d{4})(\d\d)(\d\d)(?:(000000)|(\d\d)(\d\d)(\d\d))", "$1-$2-$3' & ('$4' = '' ? ' $5:$6:$7' : '') & '") & "'")
Next

_ArrayDisplay($resultSet)

 

[MrCheese]

Hi @mLipok

Really interesting work on this.

I downloaded v2.1.15 beta, and attempting to use your MSSQL example - this is a server I am running locally

Func _Example_MSSQL()

    Local $sDriver = 'SQL Server'
    Local $sDatabase = 'newserver' ; change this string to YourDatabaseName
    Local $sServer = 'localhost\SQLEXPRESS' ; change this string to YourServerLocation
    Local $sUser = 'XXXXX' ; change this string to YourUserName
    Local $sPassword = 'XXXXXX' ; change this string to YourPassword

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

    _Example_1_RecordsetToConsole($sConnectionString, "Select * from [RIM_Image_Info]")
    _Example_2_RecordsetDisplay($sConnectionString, "Select * from SOME_TABLE")
    _Example_3_ConnectionProperties($sConnectionString)

    _Example_4_MSSQL($sServer, $sDatabase, $sUser, $sPassword, "Select * from SOME_TABLE")

EndFunc   ;==>_Example_MSSQL

 

However, when I run it, I get this output, referring to the ADO.au3 UDF file:

"C:\Temp\CHW\ADO.au3"(381,89) : error: _ArrayDisplay() called with wrong number of args.
        _ArrayDisplay($aSelect, $sTitle, "", 0, '|', $sArrayHeader, Default, $iAlternateColors)
        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:\JRStuff\MyDocs\JR Programs\Macros\autoit-v3-SciTE\Include\Array.au3"(480,176) : REF: definition of _ArrayDisplay().
Func _ArrayDisplay(Const ByRef $aArray, $sTitle = Default, $sArrayRange = Default, $iFlags = Default, $vUser_Separator = Default, $sHeader = Default, $iMax_ColWidth = Default)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:\Temp\CHW\ADO.au3"(385,90) : error: _ArrayDisplay() called with wrong number of args.
        _ArrayDisplay($aRecordset, $sTitle, "", 0, Default, Default, Default, $iAlternateColors)
        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:\JRStuff\MyDocs\JR Programs\Macros\autoit-v3-SciTE\Include\Array.au3"(480,176) : REF: definition of _ArrayDisplay().
Func _ArrayDisplay(Const ByRef $aArray, $sTitle = Default, $sArrayRange = Default, $iFlags = Default, $vUser_Separator = Default, $sHeader = Default, $iMax_ColWidth = Default)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:\Temp\CHW\ADO_EXAMPLE.au3"(40,80) : warning: $sFileFullPath: possibly used before declaration.
    Local $sConnectionString = 'Driver={' & $sDriver & '};Dbq="' & $sFileFullPath &
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:\Temp\CHW\ADO_EXAMPLE.au3"(42,107) : warning: $sConnectionString already declared/assigned
    Local $sConnectionString = _ADO_ConnectionString_Access($sMDB_FileFullPath, $sUser, $sPassword, $sDriver)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:\Temp\CHW\ADO_EXAMPLE.au3"(40,80) : error: $sFileFullPath: undeclared global variable.
    Local $sConnectionString = 'Driver={' & $sDriver & '};Dbq="' & $sFileFullPath &
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Temp\CHW\ADO_EXAMPLE.au3 - 3 error(s), 2 warning(s)
!>15:03:25 AU3Check ended. Press F4 to jump to next error.rc:2

Any thoughts? What am I doing wrong? 

-------------

I fixed it by adding: '_DebugArrayDisplay' instead of '_ArrayDisplay' needed the debug.au3 though.

Edited August 11, 2019 by MrCheese