ADO.au3 UDF - BETA - Support Topic

[Skysnake]

This page shows how to use ADO with either the typical ADO RecordSet result, or using SQL.

From what I tested with the _ADO_Execute() function, above, this works as expected with SQL.  The other example functions included appear to use the the RecordSet method.

I prefer the SQL method. 

[Skysnake]

I think this may be a problem.

Tested with Beta 2.1.13 and 2.1.14

The SQL standard allows for a SELECT query to return nothing.  However, I get an error with ADO.UDF when I select with a nothing result. IIRC its called a blank result set.

This SQL is valid in pgAdmin3 and works in SQLite.

Here is the pgA3 screen shot

And here is the script.  It fails. But, when I use this with the ADO.UDf, it returns this error

Local $query = "SELECT 'x' as y where 1=0 " _
             & " ; "

    Local $aResult = _ADO_Execute($oConnection, $query, True, True)
    If @error Then
        SetError(@error, @extended, $ADO_RET_FAILURE)
        MsgBox($MB_SYSTEMMODAL, "ADO SQL Error: " & $ADO_RET_FAILURE, '@error = ' & @error & @CRLF & '@extended = ' & @extended & @CRLF & $query & @CRLF)
    EndIf

    _ArrayDisplay($aResult)

Console

###############################
ADO.au3 v.2.1.13 BETA (811) : ==> COM Error intercepted !
$oADO_Error.description is:     Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
$oADO_Error.windescription:     Exception occurred.

$oADO_Error.number is:  80020009
$oADO_Error.lastdllerror is:    0
$oADO_Error.scriptline is:  811
$oADO_Error.source is:  ADODB.Recordset
$oADO_Error.helpfile is:    C:\windows\HELP\ADO270.CHM
$oADO_Error.helpcontext is:     1240645
###############################

I am not sure about the ADO specification, but SQL allows this.  So how to get ADO to let the 'nothing found' result through?

Skysnake

Edited April 23, 2017 by Skysnake

[mLipok]

I'll answer today.
Currently going to the Cinema.

Cheers,

mLipok

[jchd]

@Skysnake,

Correct. When I modified my own ADO layer based on older ADO UDF as well as updating the SQLite UDF, I had to initialize the resulting array as [0][.ncols] and test for EOF before invoking .rows method. I didn't look at the current ADO code but I suspect this is the same issue.

[mLipok]

@jchd could you pm to me your version or share here on the forum ?

 

Edited April 23, 2017 by mLipok

[jchd]

No problem:

AdoSQL.au3

We need to do: If not .EOF then ... .GetRows

Without that test, things fall apart when the resultset is empty.

Edited April 23, 2017 by jchd

[mLipok]

_ADO_Execute function is using:

If $bReturnAsArray Then
        Local $aRecordsetAsArray = _ADO_Recordset_ToArray($oRecordset, $bFieldNamesInFirstRow)
        Return SetError(@error, @extended, $aRecordsetAsArray)
    EndIf

_ADO_Recordset_ToArray function is using:

__ADO_Recordset_IsNotEmpty($oRecordset)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

and __ADO_Recordset_IsNotEmpty() function is using:

ElseIf $oRecordset.bof = -1 And $oRecordset.eof = True Then ; no current record
        Return SetError($ADO_ERR_NOCURRENTRECORD, $ADO_EXT_DEFAULT, $ADO_RET_FAILURE)
    ElseIf $oRecordset.RecordCount = 0 Then
        Return SetError($ADO_ERR_RECORDSETEMPTY, $ADO_EXT_DEFAULT, $ADO_RET_FAILURE)
    EndIf

So I was thought this is enought.
As we see now , this is not enought.
It requires reflection.

EDIT:
Why there is -1 ???
https://msdn.microsoft.com/en-us/library/ee266233(v=bts.10).aspx

Was it intentional ? I'll check it .

 

Edited April 23, 2017 by mLipok

[mLipok]

@Skysnake change this line:
 

ElseIf $oRecordset.bof = -1 And $oRecordset.eof = True Then ; no current record

to this:

ElseIf $oRecordset.bof And $oRecordset.eof Then ; no current record

And try  again your testing script.

 

[mLipok]

1 hour ago, mLipok said:

Why there is -1 ???
https://msdn.microsoft.com/en-us/library/ee266233(v=bts.10).aspx

Was it intentional ? I'll check it .

Now I see why, in the following link :
https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/bof-eof-properties-ado

I found this:

Quote

The BOF property returns True (-1) if the current record position is before the first record and False (0) if the current record position is on or after the first record.

This description was misleading (at least for me), as this is clearly stated here:

Quote

Return Value

The BOF and EOF properties return Boolean values.

 

Edited April 23, 2017 by mLipok

[Skysnake]

 

Sorry, still the same

My test script

Local $query = "SELECT 'x' as y where 1=0 " _
             & " ; "

    Local $aResult = _ADO_Execute($oConnection, $query, True, True)
    If @error Then
        SetError(@error, @extended, $ADO_RET_FAILURE)
        MsgBox($MB_SYSTEMMODAL, "ADO SQL Error: " & $ADO_RET_FAILURE, '@error = ' & @error & @CRLF & '@extended = ' & @extended & @CRLF & $query & @CRLF)
    EndIf

    _ArrayDisplay($aResult)

Func __ADO_Recordset_IsNotEmpty(ByRef $oRecordset)
    ; Error handler, automatic cleanup at end of function
    Local $oADO_COM_ErrorHandler = ObjEvent("AutoIt.Error", __ADO_ComErrorHandler_InternalFunction)
    If @error Then Return SetError($ADO_ERR_COMHANDLER, @error, $ADO_RET_FAILURE)
    #forceref $oADO_COM_ErrorHandler

    __ADO_Recordset_IsReady($oRecordset)
    If @error Then
        Return SetError(@error, @extended, $ADO_RET_FAILURE)
    ;~ was  ElseIf $oRecordset.bof = -1 And $oRecordset.eof = True Then ; no current record
    ElseIf $oRecordset.bof And $oRecordset.eof Then ; no current record
        Return SetError($ADO_ERR_NOCURRENTRECORD, $ADO_EXT_DEFAULT, $ADO_RET_FAILURE)
    ElseIf $oRecordset.RecordCount = 0 Then
        Return SetError($ADO_ERR_RECORDSETEMPTY, $ADO_EXT_DEFAULT, $ADO_RET_FAILURE)
    EndIf

    Return SetError($ADO_ERR_SUCCESS, $ADO_EXT_DEFAULT, $ADO_RET_SUCCESS)
EndFunc   ;==>__ADO_Recordset_IsNotEmpty

###############################
ADO.au3 v.2.1.13 BETA (812) : ==> COM Error intercepted !
$oADO_Error.description is:     Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
$oADO_Error.windescription:     Exception occurred.

$oADO_Error.number is:  80020009
$oADO_Error.lastdllerror is:    0
$oADO_Error.scriptline is:  812
$oADO_Error.source is:  ADODB.Recordset
$oADO_Error.helpfile is:    C:\windows\HELP\ADO270.CHM
$oADO_Error.helpcontext is:     1240645
###############################

Is this correct?   = 0 does NOT necessarily indicate an error...

ElseIf $oRecordset.RecordCount = 0 Then

 

Note that UDF 2.1.4 contains this on line 2165

Func _ADO_UDFVersion()
    Return '2.1.13 BETA'
EndFunc   ;==>_ADO_UDFVersion

Also, is

$ADO_ERR_RECORDSETEMPTY

an error?

Thank you kindly for your prompt response.

Skysnake

Edited April 24, 2017 by Skysnake

[jchd]

The BOF test is only sensible if you use anything but forward-only cursor (cursor = 0). It may look smart to "benefit" from seemingly-free up-down cursors so to be able to step forward or backward within the resultset, but one must fully understand what that means in practice. I must admit that over time I've changed my opinion about cursortype != 0

All in all, to be a portable and efficient (behind the scene) ADO wrapper, I now personally advocate for cursortype = 0 exclusively, removing the need for BOF test at the same time.

[mLipok]

show me result for this snippet:

#include "ADO.au3"

_Example()
Func _Example()
    Local $query = "SELECT 'x' as y where 1=0 " _
             & " ; "

;~  Local $aResult = _ADO_Execute($oConnection, $query, True, True)
    Local $oRecordset = _ADO_Execute($oConnection, $query, False, False)
    If @error Then
        SetError(@error, @extended, $ADO_RET_FAILURE)
        MsgBox($MB_SYSTEMMODAL, "ADO SQL Error: " & $ADO_RET_FAILURE, '@error = ' & @error & @CRLF & '@extended = ' & @extended & @CRLF & $query & @CRLF)
    EndIf
    ConsoleWrite("! BOF=" & $oRecordset.bof & @CRLF)
    ConsoleWrite("! EOF=" & $oRecordset.eof & @CRLF)

;~  _ArrayDisplay($aResult)
EndFunc   ;==>_Example

 

[Skysnake]

ConsoleWrite("show me result for this snippet: "  & @CRLF)
    Local $query = "SELECT 'x' as y where 1=0 " _
             & " ; "

;~  Local $aResult = _ADO_Execute($oConnection, $query, True, True)
    Local $oRecordset = _ADO_Execute($oConnection, $query, False, False)
    If @error Then
        SetError(@error, @extended, $ADO_RET_FAILURE)
        MsgBox($MB_SYSTEMMODAL, "ADO SQL Error: " & $ADO_RET_FAILURE, '@error = ' & @error & @CRLF & '@extended = ' & @extended & @CRLF & $query & @CRLF)
    EndIf
    ConsoleWrite("! BOF=" & $oRecordset.bof & @CRLF)
    ConsoleWrite("! EOF=" & $oRecordset.eof & @CRLF)
ConsoleWrite("show me result for this snippet: "  & @CRLF)

Result:

show me result for this snippet: 
! BOF=True
! EOF=True
show me result for this snippet:

 

[Skysnake]

More...

this returns errors everywhere

Example from official PostgreSQL documentation PostgreSQL PREPARE...EXECUTE

expandcollapse popup

#comments-start
        ; FROM https://www.postgresql.org/docs/9.3/static/sql-prepare.html
        ; PREPARE -- prepare a statement for execution
        PREPARE fooplan (int, text, bool, numeric) AS
        INSERT INTO foo VALUES($1, $2, $3, $4);
        EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);
    #comments-end

    ConsoleWrite(" prepare a statement for execution - start " & @CRLF)
    ConsoleWrite(" create table " & @CRLF)
    $query = "CREATE table if not exists foo (id serial, name text, TF bool, nums numeric(15,2) ) " _
             & " ; "

    Local $aResult = _ADO_Execute($oConnection, $query, True, True)
    If @error Then
        SetError(@error, @extended, $ADO_RET_FAILURE)
        MsgBox($MB_SYSTEMMODAL, "ADO SQL Error: " & $ADO_RET_FAILURE, '@error = ' & @error & @CRLF & '@extended = ' & @extended & @CRLF & $query & @CRLF)
    EndIf
    ConsoleWrite(" prepare " & @CRLF)
    $query = "PREPARE fooplan (int, text, bool, numeric) AS " _
             & " INSERT INTO foo VALUES($1, $2, $3, $4) " _
             & " ; "

    $aResult = _ADO_Execute($oConnection, $query, True, True)
    If @error Then
        SetError(@error, @extended, $ADO_RET_FAILURE)
        MsgBox($MB_SYSTEMMODAL, "ADO SQL Error: " & $ADO_RET_FAILURE, '@error = ' & @error & @CRLF & '@extended = ' & @extended & @CRLF & $query & @CRLF)
    EndIf

    ConsoleWrite(" execute " & @CRLF)
    $query = "EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00) " _
             & " ; "

    $aResult = _ADO_Execute($oConnection, $query, True, True)
    If @error Then
        SetError(@error, @extended, $ADO_RET_FAILURE)
        MsgBox($MB_SYSTEMMODAL, "ADO SQL Error: " & $ADO_RET_FAILURE, '@error = ' & @error & @CRLF & '@extended = ' & @extended & @CRLF & $query & @CRLF)
    EndIf

    ConsoleWrite(" select " & @CRLF)
    $query = "SELECT * from foo " _
             & " ; "

    $aResult = _ADO_Execute($oConnection, $query, True, True)
    If @error Then
        SetError(@error, @extended, $ADO_RET_FAILURE)
        MsgBox($MB_SYSTEMMODAL, "ADO SQL Error: " & $ADO_RET_FAILURE, '@error = ' & @error & @CRLF & '@extended = ' & @extended & @CRLF & $query & @CRLF)
    EndIf
    _ArrayDisplay($aResult)
    ConsoleWrite(" prepare a statement for execution - end " & @CRLF)

CREATE TABLE

returns an @error=6

while it works in pgAdmin3, simply returing 'skipping...already exists'

PREPARE returns this

prepare 
###############################
ADO.au3 v.2.1.13 BETA (1256) : ==> COM Error intercepted !
$oADO_Error.description is:     The # of binded parameters < the # of parameter markers
$oADO_Error.windescription:     Exception occurred.

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

while pgAdmin3 returns "Query completed successfully with no result"

and EXECUTE returns this

execute 
###############################
ADO.au3 v.2.1.13 BETA (1256) : ==> COM Error intercepted !
$oADO_Error.description is:     Could not send Query(connection dead);
Could not send Query(connection dead)
$oADO_Error.windescription:     Exception occurred.

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

While pgAdmin3 returns "Query returned successfully: one row affected, 47 ms execution time."

Skysnake

[mLipok]

Will look on this today.

[mLipok]

Quick fix is to comment this two following lines:
 

;~  ElseIf $oRecordset.status <> $ADO_adRecOK Then
;~      Return SetError($ADO_ERR_ISNOTREADYOBJECT, $oRecordset.status, $ADO_RET_FAILURE)

But I need to investigate little more and take good decision before I release new version.

 

[Skysnake]

What can I do to help?

[mLipok]

Put msdn to my head

 

[Skysnake]

Next question

Is my connection (still) alive?

; test to see if connection is ready: Func __ADO_Connection_IsReady(ByRef $oConnection)
    Local $ADO_Alive
    $ADO_Alive = __ADO_Connection_IsReady(ByRef $oConnection)
    ConsoleWrite("At " & _Now() & " connection returned " & $ADO_Alive & @CRLF)

Returns this:

>Running AU3Check (3.3.14.2)  params:-d -w 1 -w 2 -w 3 -w- 4 -w 5 -w 6 -w 7  from:C:\Program Files (x86)\AutoIt3  input:C:\AutoIt\ado_db\ADO_EXAMPLE.au3
"C:\AutoIt\ado_db\ADO_EXAMPLE.au3"(256,40) : error: syntax error
    $ADO_Alive = __ADO_Connection_IsReady(ByRef
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:\AutoIt\ado_db\ADO_EXAMPLE.au3"(256,58) : error: __ADO_Connection_IsReady() called with Const or expression on ByRef-param(s).
    $ADO_Alive = __ADO_Connection_IsReady(ByRef $oConnection)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:\AutoIt\ado_db\ADO.au3"(660,50) : REF: definition of __ADO_Connection_IsReady().
Func __ADO_Connection_IsReady(ByRef $oConnection)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\AutoIt\ado_db\ADO_EXAMPLE.au3 - 2 error(s), 0 warning(s)
!>11:21:43 AU3Check ended. Press F4 to jump to next error.rc:2
+>11:21:43 AutoIt3Wrapper Finished.
>Exit code: 2    Time: 0.8416

Without the ByRef, I get this

At 26-Apr-17 11:23:41 AM connection returned 1

Does "1" mean alive?

Skysnake

[mLipok]

Currently have many things to do at work.
Will look at this problem today when I back to home.