ADO.au3 UDF - BETA - Support Topic

[mLipok]

Temporary I'm really very busy, Could you PM to me tomrrow ?

[Skysnake]

Excellent, thx

 

[Skysnake]

On my personal wish list.

 

Query execution and error reporting:

Local $Query, $aQresult, $aResult, $Qresult

$Query = ''
    $Query = "INSERT INTO persons VALUES ('Cindy','21');"


;SQLite example from HELPFILE

If Not _SQLite_Exec(-1, $Query) = $SQLITE_OK Then _
        MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg())
        
        
; My interpretation of this for ADO
    
    $aResult = _ADO_Execute($oCon, $Query, True, True)
    If @error Then
        SetError(@error, @extended, $ADO_RET_FAILURE)
        $ADOErr = @error
        $ADOExtErr = @extended
        MsgBox($MB_SYSTEMMODAL, "ADO SQL Error: " & $ADO_RET_FAILURE, $g_AdoErrDesc & " * " & @CRLF & '@error = ' & $ADOErr & @CRLF & '@extended = ' & $ADOExtErr & @CRLF & $Query & @CRLF)
        _FileWriteLog($LogErr, @CRLF & '------------------------------------------------ SQL ERROR ' & "Error: " & $ADOErr & " * " & "Extended: " & $ADOExtErr & " * " & "ADO Returned " & $ADO_RET_FAILURE & @CRLF & $Query & @CRLF)
        $ADOErr = 0
        $ADOExtErr = 0
    EndIf
    
    
    ; I would like to do it like this
    
    If Not _ADO_Execute($oCon, $Query) = $ADO_OK Then _
        MsgBox($MB_SYSTEMMODAL, "ADO Error", _ADO_ErrMsg())

Thank you

Skysnake

[mLipok]

1 hour ago, Skysnake said:

 

    ; I would like to do it like this
    
    If Not _ADO_Execute($oCon, $Query) = $ADO_OK Then _
        MsgBox($MB_SYSTEMMODAL, "ADO Error", _ADO_ErrMsg())

Thank you

Skysnake

 

Did you try this way:

If _ADO_Execute(...) = $ADO_RET_FAILURE Then _
    MsgBox($MB_SYSTEMMODAL, "ADO Error", _ADO_ErrMsg())

But could you say me where you store result of _ADO_Execute() in case when _ADO_Execute(...) <> $ADO_RET_FAILURE

??

 

[Skysnake]

thank you, will have a try at this

the format of the SQLite query is different.  Note the "getTable" syntax

; Query
$iRval = _SQLite_GetTable2d(-1, "SELECT * FROM persons;", $aResult, $iRows, $iColumns)
If $iRval = $SQLITE_OK Then
...
EndIf

So... 

$iRval = _ADO_GetTable2d($oCon, "SELECT * FROM persons;", $aResult, $iRows, $iColumns)
If $iRval = $ADO_RET_FAILURE Then
;... (error report)
Else
;good result
EndIf

I know the UDF does not have an ADO_GetTable2D function, this is merely an example

Edited May 17, 2017 by Skysnake
spelling

[mLipok]

I propose you to try this coding style with ADO.au3:

Local $vResult = _SQL_Execute(.....)
If @error >= $ADO_ERR_RECORDSETEMPTY Then
    ; just manage this here
ElseIf @error Then
    ; show error message to End User
Else
    ; process the acquired data
EndIf

 

[Skysnake]

you mean ADO_Execute?

Yes!  I can do that!  empty Record Sets in SQL are common

Thx

 

[Skysnake]

Brilliant.  When can I get a copy of the new version UDF please?

 

[Skysnake]

As suggested...

Func _ADO_ErrMsg($Q) ; my internal function :)
    Local $LogErr = @ScriptDir & "\ErrorLog.txt"
    SetError(@error, @extended, $ADO_RET_FAILURE)
    Local $ADOErr = @error
    Local $ADOExtErr = @extended
    MsgBox($MB_SYSTEMMODAL, "ADO Error: " & $ADO_RET_FAILURE, "Version " & _ADO_UDFVersion() &@CRLF&$g_AdoErrDesc & " * " & @CRLF & '@error = ' & @error & @CRLF & '@extended = ' & @extended & @CRLF & $Q & @CRLF)
    _FileWriteLog($LogErr, @CRLF & '------------------------------------------------ SQL ERROR ' & "Error: " & $ADOErr & " * " & "Extended: " & $ADOExtErr & " * " & "ADO Returned " & $ADO_RET_FAILURE & @CRLF & $Q & @CRLF)
EndFunc   ;==>_ADO_ErrMsg

Then

 

Local $Query, $aQresult, $aResult, $Qresult

    $Query = ''
    $Query = "insert into t_log_access (uname) values ('Skysnake was here') ;"

    ;   If _ADO_Execute($oCon, $Query, True, True) = $ADO_RET_FAILURE Then _
    ;    MsgBox($MB_SYSTEMMODAL, "ADO Error", _ADO_ErrMsg($Query))

    Local $vResult = _ADO_Execute($oCon, $Query, True, True)
    If @error >= $ADO_ERR_RECORDSETEMPTY Then
        ; just manage this here
        MsgBox($MB_SYSTEMMODAL, "1. ADO ", "Not an ADO error")
    ElseIf @error Then
        ; show error message to End User
        MsgBox($MB_SYSTEMMODAL, "1. ADO Error", _ADO_ErrMsg($Query)) ;==> error here?
    Else
        ; process the acquired data
        MsgBox($MB_SYSTEMMODAL, "1. ADO ", "All is well :) ")
    EndIf

Based on your suggestion... 

Since this is a simple insert, why does it return an error?

[Skysnake]

ADO and SQL

The way I read the ADO documentation, a data object must be created for processing.  A "blank" ADO object represents and error, because it is impossible to process this any further.

SQL accepts that a query may return a blank result.  This is a valid SQL return value.

Now, the way I see it, the ADO_ functions all support the typical ADO response.

However, it is possible (and Microsoft documentation refers to this) that standard SQL can be executed over ADO.  

However, at the moment, ADO intercepts a valid SQL response and returns an error.

This means that it is not possible to return a blank SQL response with ADO.

My suggestion is as follows:

Use the ADO_Execute_Command() function for ADO functions, where the user must create the commands and strict ADO error checking applies.

Use the ADO_Execute() function for a less strict environment.  All command can be executed here, but error checking is reduced.  SQL blank results can be processed here, as this does not result in an ADO error.  This would means that if an ADO command is executed an error could slip through, but allows for error-free SQL in the ADO UDF.

Alternatively, I must create my own wrapper, based on your code, but without the ADO Error checking...

Your thoughts please?

Skysnake

Edited May 21, 2017 by Skysnake

[mLipok]

Which @error numbers/variables ($ADO_ERR_*) returned by ADO.au3 UDF is in your opinion not properly used in this UDF ?

EDIT:
Please: name them by using $ADO_ERR_* naming convention - I will investigate this.

 

Edited May 21, 2017 by mLipok

[Skysnake]

Thank you, I will make a new example tomorrow.

        $ADO_ERR_RECORDSETEMPTY, _ ;        The Recordset is Empty is 13, right?

Skysnake

[mLipok]

I was looking also on $ADO_ERR_NOCURRENTRECORD 

ElseIf $oRecordset.bof And $oRecordset.eof Then ; no current record
        Return SetError($ADO_ERR_NOCURRENTRECORD, $ADO_EXT_DEFAULT, $ADO_RET_FAILURE)

 

I make an investigation and:
I found that $ADO_ERR_RECORDSETEMPTY and $ADO_ERR_NOCURRENTRECORD is only used inside of this following function:

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)
    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

and

at the end of this following function:

Return SetError($ADO_ERR_RECORDSETEMPTY, $ADO_EXT_DEFAULT, $ADO_RET_FAILURE)
EndFunc   ;==>_ADO_Recordset_ToArray

...............................................................

First let's take a look on __ADO_Recordset_IsNotEmpty()

This is used in:

Func _ADO_Recordset_Display(ByRef $vRocordset, $sTitle = '', $iAlternateColors = Default, $bFieldNamesInFirstRow = False)
    Local $vResult = $ADO_RET_FAILURE
    If UBound($vRocordset) Then
        $vResult = __ADO_RecordsetArray_Display($vRocordset, $sTitle)
        Return SetError(@error, @extended, $vResult)
    ElseIf __ADO_Recordset_IsNotEmpty($vRocordset) = $ADO_RET_SUCCESS Then
        Local $aRecordset_GetRowsResult = _ADO_Recordset_ToArray($vRocordset, $bFieldNamesInFirstRow)
        $vResult = __ADO_RecordsetArray_Display($aRecordset_GetRowsResult, $sTitle, $iAlternateColors)
        Return SetError(@error, @extended, $vResult)
    EndIf

    Return SetError(@error, @extended, $ADO_RET_FAILURE) ; @error and @extended returned from __ADO_Recordset_IsNotEmpty
EndFunc   ;==>_ADO_Recordset_Display

and:

Func _ADO_Recordset_Find(ByRef $oRecordset, $Criteria, $SkipRows = 0, $SearchDirection = $ADO_adSearchForward, $Start = $ADO_adBookmarkCurrent)
    ; 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_IsNotEmpty($oRecordset)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    $oRecordset.Find($Criteria, $SkipRows, $SearchDirection, $Start)
    If @error Then Return SetError($ADO_ERR_COMERROR, @error, $ADO_RET_FAILURE)

    Return SetError($ADO_ERR_SUCCESS, $ADO_EXT_DEFAULT, $ADO_RET_SUCCESS)

EndFunc   ;==>_ADO_Recordset_Find

and:

Func _ADO_Recordset_ToArray(ByRef $oRecordset, $bFieldNamesInFirstRow = False)
    ; 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_IsNotEmpty($oRecordset)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)
......

and:

Func _ADO_Recordset_ToString(ByRef $oRecordset, $sDelim = "|", $bReturnColumnNames = True)
    ; 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_IsNotEmpty($oRecordset)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)
.....

 

of course this following function:

Func _ADO_Execute(ByRef $oConnection, $sQuery, $bReturnAsArray = False, $bFieldNamesInFirstRow = False)
......
    If $bReturnAsArray Then
        Local $aRecordsetAsArray = _ADO_Recordset_ToArray($oRecordset, $bFieldNamesInFirstRow)
        Return SetError(@error, @extended, $aRecordsetAsArray)
    EndIf
.......

also use __ADO_Recordset_IsNotEmpty() but only in case when you want to get Array as a result.

This is very important to check for $ADO_ERR_RECORDSETEMPTY  and $ADO_ERR_NOCURRENTRECORD before any function will try to return array.
If SQL / ADO is not get any data from data store then you can not get as a result any type of array, for this case this two $ADO_ERR_... Is essential.

Of course this is not needed when you want to get Recordset as an $oRecordset  I mean object , but each time when you want to get result as a variables you should check if recordset is not empty and has any current record.

So IMHO each time one of this functions:
_ADO_Recordset_ToArray()
_ADO_Recordset_ToString()

is called then this should be checked .

 

The same calling: _ADO_Recordset_Find()

it is important to use: __ADO_Recordset_IsNotEmpty($oRecordset) before using  Find methode on Recordset object.

 

...............................................................

as to this case:

Return SetError($ADO_ERR_RECORDSETEMPTY, $ADO_EXT_DEFAULT, $ADO_RET_FAILURE)
EndFunc   ;==>_ADO_Recordset_ToArray

this should be rethinked .....

 

...............................................................

I hope my statement is clear and consistent.
If anybody finds mistakes in my reasoning I will gladly listen to the arguments.

Regards,
mLipok

Edited May 21, 2017 by mLipok

[mLipok]

1 hour ago, Skysnake said:

$ADO_ERR_RECORDSETEMPTY, _ ;        The Recordset is Empty is 13, right?

try this:

#include <ADO_CONSTANTS.au3>

ConsoleWrite($ADO_ERR_SUCCESS & @CRLF)
ConsoleWrite($ADO_ERR_GENERAL & @CRLF)
;....
ConsoleWrite($ADO_ERR_RECORDSETEMPTY & @CRLF)
ConsoleWrite($ADO_ERR_NOCURRENTRECORD & @CRLF)

 

[Skysnake]

Dear @mLipok

Thank you for all of this.  I helps me understand what is the logic behind all the functions.

I went back to test the blank result set with the AutoIt SQLite functions... I get this

; Query
$iRval = _SQLite_GetTable2d(-1, "SELECT 'x' as y where 1=0;", $aResult, $iRows, $iColumns)
If $iRval = $SQLITE_OK Then
    _SQLite_Display2DResult($aResult)
    EndIf

;-----------------------------------------------
; Console output

_SQLite_LibVersion=3.18.0
 y

pgAdmin3 also simply returns 'y unknown'

 

I have this news:

ConsoleWriteError("--------- 8th select example example ---------" & @CRLF)
; reset @error
SetError(0,0,0)
; like SQLite_GetTable2d
$Query = "Select 'x' as y where 1=0; "

$aResult = _ADO_Execute($oCon, $Query, True, True)
            If @error >= $ADO_ERR_RECORDSETEMPTY Then
                ; just manage this here
                ConsoleWrite("SQL blank result set " & _ADO_UDFVersion() & @CRLF)
            ElseIf @error Then
                ; show error message to End User
                ConsoleWrite("A serious error " & _ADO_UDFVersion() & @CRLF)
            Else
                ; process the acquired data
                ConsoleWrite("Proceed " & _ADO_UDFVersion() & @CRLF)
            EndIf
ConsoleWrite(_ArrayDisplay($aResult))

A serious error 2.1.13 BETA ;==> old version returns COM error

--------- 8th select example example ---------
SQL blank result set 2.1.15 BETA ;==> pre-BETA works as expected

 

ConsoleWriteError("--------- 7th select example example ---------" & @CRLF)
; reset @error
SetError(0,0,0)
; like SQLite_GetTable2d
$Query = "Select uname as uname, clocktime as time, seq as id from t_log_access where uname ilike '%mlipok%' order by 2 desc limit 5; "

$aResult = _ADO_Execute($oCon, $Query, True, True)
If @error <> $ADO_ERR_SUCCESS and @error <= $ADO_ERR_RECORDSETEMPTY Then
    ; a serious error
    ConsoleWrite("7.1 a serious error " & @error  & _ADO_UDFVersion() & @CRLF)
Else
    ; includes SQL blank result set
    ; process the acquired data
    ConsoleWrite("Proceed " & @error & @CRLF)
EndIf
ConsoleWrite(_ArrayDisplay($aResult) & @CRLF)

Returns console output

--------- 7th select example example ---------
Proceed 13

 

Also, this works very well, this type of Execute query does not expect a result set:

ConsoleWriteError("--------- 1st execute example ---------" & @CRLF)
; like SQLite _SQLite_Execute :)
If Not _ADO_Execute($oCon, "CREATE temp TABLE persons (Name text, Age int);") = $ADO_ERR_SUCCESS Then _
        ConsoleWrite("0. $ADO_ERR_SUCCESS Error " & $ADO_ERR_SUCCESS & @CRLF)

ConsoleWrite("0. $ADO_ERR_SUCCESS Success " & $ADO_ERR_SUCCESS & @CRLF)

 

 

Please release

Skysnake

[mLipok]

new version:
 

Quote

2017/05/28 '2.1.15 BETA'
.    Fixed: Function: __ADO_Recordset_IsNotEmpty() - mLipok
.        Thanks to @Skysnake for reporting
.    Added: many description to functions - mLipok
.    Added: many description to functions - thanks to Skysnake
.    Refactored: _ADO_Recordset_Display - mLipok
.    Changed: __ADO_Command_IsValid() - return values are now boolean - mLipok
.    Changed: __ADO_Connection_IsReady() - return values are now boolean - mLipok
.    Changed: __ADO_Connection_IsValid() - return values are now boolean - mLipok
.    Changed: __ADO_IsValidObjectType() - return values are now boolean - mLipok
.    Changed: __ADO_Recordset_IsNotEmpty() - return values are now boolean - mLipok
.    Changed: __ADO_Recordset_IsReady() - return values are now boolean - mLipok
.    Changed: __ADO_Recordset_IsValid() - return values are now boolean - mLipok
.    Changed: __ADO_RecordsetArray_IsValid() - return values are now boolean - mLipok

 

Download link:

 

 

[mLipok]

Fixed download (properly uploaded ADO 2.1.15 BETA.zip)

[Skysnake]

***** 

FIVE STARS from me!!!

 

I have downloaded.  Expect feedback

Skysnake

 

[mLipok]

Thanks.
 

9 minutes ago, Skysnake said:

Expect feedback

I'm just waiting for that.

[mLipok]

@Skysnake 

Have all your questions and issues been explained in my recent comments and included in this last update ?