ADO.au3 UDF - BETA - Support Topic

Skysnake · March 16, 2017

More

Calling function (slightly modified from zip)

Func _Example_3_ConnectionProperties($sConnectionString)

ConsoleWrite("_Example_3_ConnectionProperties " & @ScriptLineNumber & @CRLF )

    ; Create connection object
    Local $oConnection = _ADO_Connection_Create()

    ; Open connection with $sConnectionString
    $SQLisReady = _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then

        ConsoleWrite("2 $SQLisReady " & $SQLisReady & @CRLF)

        ConsoleWrite("2 @ERROR " & @error & @CRLF)

        ConsoleWrite("2 @extended " & @extended & @CRLF)

        Return SetError(@error, @extended, $ADO_RET_FAILURE)
    EndIf

    ; Get all connection properties to Array
    Local $aProperties = _ADO_Connection_PropertiesToArray($oConnection)

#cs
    ; Clean Up
    _ADO_Connection_Close($oConnection)
    $oConnection = Null
#CE
    ; Show connection properties
    _ArrayDisplay($aProperties, "ADO connection - List of properties", "", 0, Default, "Name|Type|Value|Attributes")

EndFunc   ;==>_Example_3_ConnectionProperties

Output

###############################
ADO.au3 v.2.1.13 BETA (970) : ==> COM Error intercepted !
$oADO_Error.description is:     FATAL:  password authentication failed for user "postgres"

$oADO_Error.windescription:     Exception occurred.

$oADO_Error.number is:  80020009
$oADO_Error.lastdllerror is:    0
$oADO_Error.scriptline is:  970
$oADO_Error.source is:  Microsoft OLE DB Provider for ODBC Drivers
$oADO_Error.helpfile is:    
$oADO_Error.helpcontext is:     0
###############################
2 $SQLisReady -1
2 @ERROR 0
2 @extended 0

It seems $SQLisReady returns 1 if it works, or -1 on fail.

However, what happens to the error codes?

I can see the pretty COM Error intercepted bloc, but I would like to trap it and show to the user...

Skysnake

Edited March 16, 2017 by Skysnake
Beautification

Skysnake · March 16, 2017

Found this in ADO.AU3

Func _ADO_COMErrorHandler($oADO_Error)

Added

$g_AdoErrDesc = $oADO_Error.description

Where $g_AdoErrDesc is a global variable, now I can tell what the connection error was

Awesome stuff man!

Skysnake

mLipok · March 17, 2017

I Look at this later

Skysnake · March 17, 2017

Here is an index from ADO.AU3

#cs
; complete list of all functions in the ADO.UDF -- 2017.03.17 v 2.1.13
__ADO_RecordsetArray_Display(ByRef $aRocordset, $sTitle = '', $iAlternateColors = Default)
__ADO_RecordsetArray_IsValid(ByRef $aRocordset)
_ADO_Recordset_Display(ByRef $vRocordset, $sTitle = '', $iAlternateColors = Default, $bFieldNamesInFirstRow = False)
_ADO_Recordset_Find(ByRef $oRecordset, $Criteria, $SkipRows = 0, $SearchDirection = $ADO_adSearchForward, $Start = $ADO_adBookmarkCurrent)
_ADO_Recordset_ToArray(ByRef $oRecordset, $bFieldNamesInFirstRow = False)
_ADO_Recordset_ToString(ByRef $oRecordset, $sDelim = "|", $bReturnColumnNames = True)
_ADO_RecordsetArray_GetContent(ByRef $aRocordset)
_ADO_RecordsetArray_GetFieldNames(ByRef $aRocordset)
__ADO_Command_IsValid(ByRef $oCommand)
__ADO_Connection_IsReady(ByRef $oConnection)
__ADO_Connection_IsValid(ByRef $oConnection)
__ADO_IsValidObjectType(ByRef $oObjectToCheck, $sRequiredProgID)
__ADO_MSSQL_CONNECTION_STRING_SQLAuth($sServer, $sDataBase, $sUserName, $sPassword, $sAppName = Default, $bUseProviderInsteadDriver = True)
__ADO_Recordset_IsNotEmpty(ByRef $oRecordset)
__ADO_Recordset_IsReady(ByRef $oRecordset)
__ADO_Recordset_IsValid(ByRef $oRecordset)
_ADO_Command_Create(ByRef $oConnection, $iCommandType = $ADO_adCmdText)
_ADO_Command_CreateParameter(ByRef $oCommand, $sName, $iSize, $vValue, $iType = $ADO_adChar, $iDirection = $ADO_adParamInputOutput)
_ADO_Command_Execute(ByRef $oCommand, $sQuery)
_ADO_Connection_Close(ByRef $oConnection)
_ADO_Connection_CommandTimeout(ByRef $oConnection, $iTimeOut = Default)
_ADO_Connection_Create()
_ADO_Connection_OpenConString(ByRef $oConnection, $sConnectionString)
_ADO_Connection_OpenMSSQL(ByRef $oConnection, $sServer, $sDBName, $sUserName, $sPassword, $sAppName = Default, $sWSID = Default, $bSQLAuth = True, $bUseProviderInsteadDriver = True)
_ADO_Connection_PropertiesToArray(ByRef $oConnection)
_ADO_Connection_Timeout(ByRef $oConnection, $iTimeOut = Default)
_ADO_Execute(ByRef $oConnection, $sQuery, $bReturnAsArray = False, $bFieldNamesInFirstRow = False)
_ADO_GetProvidersList()
_ADO_MSSQL_GetDriverVersion()
_ADO_MSSQL_GetProviderVersion()
_ADO_Recordset_Create()
_ADO_Version(ByRef $oConnection)
__ADO_ComErrorHandler_InternalFunction($oCOMError)
_ADO_COMErrorHandler($oADO_Error)
_ADO_COMErrorHandler_UserFunction($fnUserFunction = Default)
__ADO_EVENT__BeginTransComplete($iTransactionLevel, ByRef $oError, $i_adStatus, ByRef $oConnection)
__ADO_EVENT__CommitTransComplete(ByRef $oError, $i_adStatus, ByRef $oConnection)
__ADO_EVENT__ConnectComplete(ByRef $oError, $i_adStatus, ByRef $oConnection)
__ADO_EVENT__Disconnect($i_adStatus, ByRef $oConnection)
__ADO_EVENT__FetchComplete(ByRef $oError, $i_adStatus, ByRef $oRecordset)
__ADO_EVENT__FetchProgress($iProgress, $iMaxProgress, $i_adStatus, ByRef $oRecordset)
__ADO_EVENT__InfoMessage(ByRef $oError, $i_adStatus, ByRef $oConnection)
__ADO_EVENT__RollbackTransComplete(ByRef $oError, $i_adStatus, ByRef $oConnection)
__ADO_EVENT__WillConnect($sConnection_String, $sUserID, $sPassword, $iOptions, $i_adStatus, ByRef $oConnection)
__ADO_EVENT__WillExecute($sSource, $iCursorType, $iLockType, $iOptions, $i_adStatus, ByRef $oCommand, ByRef $oRecordset, ByRef $oConnection)
__ADO_EVENTS_INIT(ByRef $oConnection)
_ADO_EVENTS_SetUp($bInitializeEvents = Default)
__ADO_ConsoleWrite_Blue($sText)
__ADO_ConsoleWrite_Red($sText)
_ADO_ConsoleError($sDescription = '', $iError = @error, $iExtended = @extended)
_ADO_GetErrorDescription($sDescription = '', $bShowHumanReadableDescription = True, $iError = @error, $iExtended = @extended)
_ADO_MSDNErrorValueEnum_Description($iError, $iErrorMacro = @error, $iExtendedMacro = @extended)
_ADO_UDFVersion()
_Au3Date_to_SQLDate($sAu3Date)
_SQLDate_to_Au3Date($sDate, $bOnlyYMD = False)
_ADO_OpenSchema_Catalogs(ByRef $oConnection, $s_CATALOG_NAME = '')
_ADO_OpenSchema_Columns(ByRef $oConnection, $s_TABLE_CATALOG = '', $s_TABLE_SCHEMA = '', $s_TABLE_NAME = '', $s_COLUMN_NAME = '')
_ADO_OpenSchema_Indexes(ByRef $oConnection, $s_TABLE_CATALOG = '', $s_TABLE_SCHEMA = '', $s_INDEX_NAME = '', $s_TYPE = '', $s_TABLE_NAME = '')
_ADO_OpenSchema_Tables(ByRef $oConnection, $s_TABLE_CATALOG = '', $s_TABLE_SCHEMA = '', $s_TABLE_NAME = '', $s_TABLE_TYPE = '')
_ADO_OpenSchema_Views(ByRef $oConnection, $s_TABLE_CATALOG = '', $s_TABLE_SCHEMA = '', $s_TABLE_NAME = '')
_ADO_Schema_GetAllCatalogs(ByRef $oConnection)
_ADO_Schema_GetAllTables(ByRef $oConnection, $s_TABLE_CATALOG)
_ADO_Schema_GetAllViews(ByRef $oConnection, $s_TABLE_CATALOG)
_ADO_ConnectionString_Access($sFileFullPath, $sUser = Default, $sPassword = Default, $sDriver = Default)
_ADO_ConnectionString_Excel($sFileFullPath = Default, $sProvider = Default, $sExtProperties = Default, $HDR = Default, $IMEX = Default)
_ADO_ConnectionString_MySQL($sUser, $sPassword, $sDataBase, $sDriver = Default, $sServer = Default, $sPort = Default)
#ce

Suggestion

In the related comment field, place references to "partner" functions, such as

_Au3Date_to_SQLDate($sAu3Date) ; related to _SQLDate_to_Au3Date()
_SQLDate_to_Au3Date($sDate, $bOnlyYMD = False) related;  to _Au3Date_to_SQLDate()

Note also the use of $aRocordset - I dont think it makes much difference internally, but perhaps it should be $aRecordset

Thanks for this.

Skysnake

Edited March 17, 2017 by Skysnake
typo

mLipok · March 17, 2017

1 hour ago, Skysnake said:

Here is an index from ADO.AU3

I will do few fix, change .... and send you via PM new version, after that could you be so nice and create index again ?

mLipok · March 17, 2017

1 hour ago, Skysnake said:
In the related comment field, place references to "partner" functions, such as
_Au3Date_to_SQLDate($sAu3Date) ; related to _SQLDate_to_Au3Date()
_SQLDate_to_Au3Date($sDate, $bOnlyYMD = False) related;  to _Au3Date_to_SQLDate()
Note also the use of $aRocordset - I dont think it makes much difference internally, but perhaps it should be $aRecordset

Thanks for this.

both, done.

Thanks.

mLipok · March 17, 2017

13 hours ago, Skysnake said:

However, what happens to the error codes?

If @error Then

        ConsoleWrite("2 $SQLisReady " & $SQLisReady & @CRLF)

        ConsoleWrite("2 @ERROR " & @error & @CRLF)

        ConsoleWrite("2 @extended " & @extended & @CRLF)

        Return SetError(@error, @extended, $ADO_RET_FAILURE)
    EndIf

First ConsoleWrite() changes @error and @extended
This is normal AutoIt Functions behavior.

mLipok · March 17, 2017

13 hours ago, Skysnake said:

The Func __ADO_Command_IsValid(ByRef $oCommand) starts on line 529. It is listed as an internal command and used in several other functions.

I cannot get it to return anything other than -1.

On successful connection I get -1 Error & Extended 0 & 0

First please fix the ConsoleWrite problem (mentioned before).
Next please post some snippet showing how you using this UDF in this scenario when you get -1.

Skysnake · March 17, 2017

Noted. Thanks. On it.

mLipok · March 18, 2017

@Skysnake

For checking You could use UDF like this:

#include-once
#AutoIt3Wrapper_Run_AU3Check=Y
#AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w- 4 -w 5 -w 6 -w 7
; #AutoIt3Wrapper_Run_Debug_Mode=Y
; #Tidy_Parameters=/sort_funcs /reel

Global $__g_sUDFLOG_PREFIX = '--> '
Global $__g_sUDFLOG_ERROR_PREFIX = '! '

_Log_Example()
Func _Log_Example()
    ; set function to where log report is passed
    _Log_SetOutputFunction(ConsoleWrite)

    ; Sending description to log output
    _Log('We are trying to read file.')

    ; fire @error
    FileRead('FILE PATH WHICH NOT EXIST')

    ; report @error but do not change it - store it and back it again
    _Log_Errors('File Reading Problem.')

    ; check again for @error
    If @error Then
        Return SetError(@error, @extended, MsgBox(0, 'Error Occured', 'File Reading Problem.'))
    EndIf
EndFunc   ;==>_Log_Example

; #FUNCTION# ====================================================================================================================
; Name ..........: _Log_Errors
; Description ...: Log description to the output but only if @error or @extended occurs
; Syntax ........: _Log_Errors($sText[, $iError = @error[, $iExtended = @extended]])
; Parameters ....: $sText               - a string value.
;                  $iError              - [optional] an integer value. Default is @error. Used to store @error
;                  $iExtended           - [optional] an integer value. Default is @extended. Used to store @extended
; Return values .: Restored @error and @extended
; Author ........: mLipok
; Modified ......:
; Remarks .......:
; Related .......: _Log
; Link ..........:
; Example .......: No
; ===============================================================================================================================
Func _Log_Errors($sText, $iError = @error, $iExtended = @extended)
    Local $fnFunction = _Log_SetOutputFunction()
    If ($iError Or $iExtended) And IsFunc($fnFunction) Then _
            $fnFunction($__g_sUDFLOG_ERROR_PREFIX & '@error=' & $iError & ' @extended=' & $iExtended & ' : ' & $sText & @CRLF)
    Return SetError($iError, $iExtended)
EndFunc   ;==>_Log_Errors

; #FUNCTION# ====================================================================================================================
; Name ..........: _Log
; Description ...: Log description to the output
; Syntax ........: _Log($sText[, $iError = @error[, $iExtended = @extended]])
; Parameters ....: $sText               - a string value.
;                  $iError              - [optional] an integer value. Default is @error. Used to store @error
;                  $iExtended           - [optional] an integer value. Default is @extended. Used to store @extended
; Return values .: Restored @error and @extended
; Author ........: mLipok
; Modified ......:
; Remarks .......:
; Related .......: _Log_Errors
; Link ..........:
; Example .......: No
; ===============================================================================================================================
Func _Log($sText, $iError = @error, $iExtended = @extended)
    Local $fnFunction = _Log_SetOutputFunction()
    Local $sDescription = _
            (($iError Or $iExtended) ? ($__g_sUDFLOG_ERROR_PREFIX & '[ ' & $iError & ' / ' & $iExtended & ' ] : ') : ($__g_sUDFLOG_PREFIX)) _
             & $sText & @CRLF
    If IsFunc($fnFunction) Then $fnFunction($sDescription)
    Return SetError($iError, $iExtended)
EndFunc   ;==>_Log

; #FUNCTION# ====================================================================================================================
; Name ..........: _Log_SetOutputFunction
; Description ...: Set the function to which Log description should be passed
; Syntax ........: _Log_SetOutputFunction([$fnFunction = Default])
; Parameters ....: $fnFunction          - [optional] a floating point value. Default is Default.
; Return values .: None
; Author ........: mLipok
; Modified ......:
; Remarks .......:
; Related .......:
; Link ..........:
; Example .......: No
; ===============================================================================================================================
Func _Log_SetOutputFunction($fnFunction = Default)
    Local Static $fnFunction_static = Null
    If $fnFunction = Default Then Return $fnFunction_static
    If Not IsFunc($fnFunction) Then Return SetError(1)

    $fnFunction_static = $fnFunction
    Return $fnFunction_static
EndFunc   ;==>_Log_SetOutputFunction

Edited March 18, 2017 by mLipok
Script changed

mLipok · March 20, 2017

New Version:

Quote

2017/03/20 '2.1.14 BETA'
.   Changed: Function: _ADO_COMErrorHandler - If @Compiled Then Return ....... - mLipok
.   Examples: Fixed bug in example for XLS - mLipok
.       thanks to ViciousXUSMC
.       https://www.autoitscript.com/forum/topic/180850-adoau3-udf-beta-support-topic/?do=findComment&comment=1307690
.   Examples: New Function: _ErrFunc($oError) - mLipok
.       ; HowTo: use your own COMErrorHandler instead internal ADO.au3 UDF COMError Handler - _ADO_COMErrorHandler
.   Examples: New Function: _ErrDescription($sDescription = Default) - mLipok
.       ; store description to use it outsided UDF in your own function
.   Added: #Au3Stripper_Ignore_Funcs=__ADO_EVENT__* - mLipok
.   Changed: _ADO_Execute automaticaly check __ADO_Recordset_IsNotEmpty($oRecordset) - mLipok
.   Changed: Function: _ADO_ConnectionString_Access() - added support for '.accdb' when $sDriver = Default - mLipok
.   Chnaged: $aRocordset >> $aRecordset - Skysnake
.
.
.   !!! REMARK - I'm not sure when this following changes was happend
.   Removed: Function: _ADO_OpenSchema_Views - MS SQL: Object or provider could not perform requested action - mLipok
.       REF: https://msdn.microsoft.com/en-US/library/ee275169(v=bts.10).aspx
.           For all DBMS only this four QueryType are common:
.               adSchemaColumns, adSchemaIndexes, adSchemaTables, adSchemaProviderTypes
.           The SchemaEnum values supported by the Microsoft® OLE DB Provider for DB2 and the Microsoft® ODBC Driver for DB2 can be one of the following constants:
.               adSchemaColumns, adSchemaIndexes, adSchemaTables, adSchemaProviderTypes + adSchemaProcedures + adSchemaProcedureParameters + adSchemaPrimaryKeys
.   Removed: Function: _ADO_Schema_GetAllViews - as _ADO_OpenSchema_Views() is also removed - mLipok
.   Changed: Function: __ADO_IsValidObjectType - in case of @error occured, @extended always return $ADO_EXT_INTERNALFUNCTION - mLipok
.   Changed: Function: _ADO_COMErrorHandler() - parameter $oADO_Error is now passed as ByRef - mLipok
.   Added: Function INDEX - Skysnake

Download it here:

Skysnake · March 20, 2017

Downloaded and tested new Beta.

The error report still refers to 2.1.13, but tested on PostgreSQL works as expected.

Should ADO.UDF line 2166 be updated?

Edited March 20, 2017 by Skysnake
Extra info

mLipok · March 20, 2017

2 hours ago, Skysnake said:

Should ADO.UDF line 2166 be updated?

Yes. The same 25 and 26. Thanks.

2 hours ago, Skysnake said:

The error report still refers to 2.1.13, but tested on PostgreSQL works as expected.

??

Skysnake · March 27, 2017

I have something for everyone to play with. It may come down at any time, and there are no guarantees. Use at your own peril. The login works. The example here is intended to show the SQL inside the ADO.UDF, not server management.

Global $oCon = Null ; $oConnection

Func _fMyADO_test()

Local $sDSN = 'PostgreSQL35W' ; Default
    Local $sDatabase = 'fred' ; db name --- default 'postgres'
    Local $sServer = '199.19.213.182' ; Server IP
    Local $sPort = '5432' ; Port
    Local $sUser = 'fred' ; PostGre Username
    Local $sPassword = 'autoit' ; PostGre User Password


    Local $sConnectionString = 'DSN=' & $sDSN & ';DATABASE=' & $sDatabase & ';SERVER=' & $sServer & ';PORT=' & $sPort & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'

    ; Create connection object
    Local $oConnection = _ADO_Connection_Create()

    ; Open connection with $sConnectionString
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    $oCon = $oConnection

    Local $Query, $aQresult, $Qresult

    ConsoleWrite("_Example_PostgreSQL " & $sConnectionString & @CRLF)
    ;_Example_1_RecordsetToConsole($sConnectionString, "Select * from trans where accnum='YWX0067' limit 100")
    ;_Example_2_RecordsetDisplay($sConnectionString, "Select * from pg_database   ") ; find all db names
    ;_Example_2_RecordsetDisplay($sConnectionString, "Select * from pg_taBLES where tableowner='fred' order by 2  ") ; find all db names

    ;_Example_3_ConnectionProperties($sConnectionString)


    ;   $Query = "create table t_Alpheus (text text, seq serial) ; "
    ;   $Qresult = _ADO_Execute($oCon, $Query)
    ;   ConsoleWrite("$Qresult " & $Qresult & @CRLF)


    ;_Example_2_RecordsetDisplay($sConnectionString, "Select * from pg_taBLES where tableowner='fred' order by 2  ") ; find all db names
    $Query = ''
    $Query = "insert into t_Alpheus (text) values ('new 64 This is text :)') ;" ; returning 'Okay' ; "
    $aQresult = _ADO_Execute($oCon, $Query, True)
    ;_ArrayDisplay($aQresult[1])
    ;_ArrayDisplay($aQresult[2])
    ;_ArrayDisplay($aQresult[3])
    ;$Qresult = _ArrayToString($aQresult[2])
    ConsoleWrite("1 $Query " & $Query & @CRLF)
    ConsoleWrite("1 $Qresult " & $Qresult & @CRLF)
    ConsoleWrite("1 $aQresult " & $aQresult & @CRLF)
    $aQresult = ''

    ;_Example_2_RecordsetDisplay($sConnectionString, "Select * from t_Alpheus   ")
#cs
    $Query = "Delete from t_Alpheus returning 'Okay' ; "
    ;$aQresult = _ADO_Execute($oCon, $Query, True)
    ;_ArrayDisplay($aQresult[2])
    $Qresult = _ArrayToString($aQresult[2])
    ;$Qresult &= $aQresult[2]
    ConsoleWrite("$Qresult " & $Qresult & @CRLF)
#CE
    $Query = "Select * from t_Alpheus ; "
    $aQresult = _ADO_Execute($oCon, $Query, True)
    $Qresult = _ArrayToString($aQresult[2])
    ConsoleWrite("2 $Query " & $Query & @CRLF)
    ConsoleWrite("2 $Qresult " & $Qresult & @CRLF)
    $aQresult = ''

    ;_Example_2_RecordsetDisplay($sConnectionString, "Select * from t_Alpheus   ")
    
    EndFunc ;  _fMyADO_test()

The postgres RETURNING function appears to cause this code to do multiple INSERTS.

But otherwise, the standard SQL works as expected.

Skysnake

Edited March 27, 2017 by Skysnake
beautification

Skysnake · March 28, 2017

Using the above connection, I run this query

ConsoleWrite("3 Test insert begin ---------------------- " & _Now() & @CRLF)
    $Query = ''
    $Query = "insert into t_log_access (uname) values ('Skysnake was here') ;"
    $aQresult = _ADO_Execute($oCon, $Query, True)
    ConsoleWrite("3a @ERROR " & @error & @CRLF)
    ConsoleWrite("3b @ERROR " & @error & @CRLF)
    ConsoleWrite("3c @extended " & @extended & @CRLF)
    _ArrayDisplay($aQresult, "Result of INSERT query") ; not an array
    ;$Qresult = _ArrayToString($aQresult[2])
    ConsoleWrite("3 $Query " & $Query & @CRLF)
    ConsoleWrite("3 $aQresult " & $aQresult & @CRLF)
    $aQresult = ''

    $Query = "Select * from t_log_access ; "
    $aQresult = _ADO_Execute($oCon, $Query, True)
    ConsoleWrite("4a @ERROR " & @error & @CRLF)
    ConsoleWrite("4b @ERROR " & @error & @CRLF)
    ConsoleWrite("4c @extended " & @extended & @CRLF)
    $Qresult = _ArrayToString($aQresult[2])
    ConsoleWrite("4 $Query " & $Query & @CRLF)
    ConsoleWrite("4 $Qresult " & $Qresult & @CRLF)
    $aQresult = ''
    ConsoleWrite("3 Test insert end ---------------------- " & _Now() & @CRLF)

Then I get this output

3 Test insert begin ---------------------- 28-Mar-17 9:41:35 AM
3a @ERROR 6
3b @ERROR 0
3c @extended 0
3 $Query insert into t_log_access (uname) values ('Skysnake was here') ;
3 $aQresult -1
4a @ERROR 0
4b @ERROR 0
4c @extended 0
4 $Query Select * from t_log_access ; 
4 $Qresult Skysnake was here|20170328064442|1
Skysnake was here|20170328064507|2
Skysnake was here|20170328065019|3
Skysnake was here|20170328065117|4
Skysnake was here|20170328065212|5
Skysnake was here|20170328065253|6
Skysnake was here|20170328065310|7
Skysnake was here|20170328073732|8
Skysnake was here|20170328073951|9
Skysnake was here|20170328074134|10
3 Test insert end ---------------------- 28-Mar-17 9:41:37 AM

Questions

result '3a' - why does @Error first return 6 and then '3b' 0?
3 $aQresult -1 returns '-1' on success, why?

I don't think there is anything wrong here, just trying to understand. Specifically I am looking for something like the 'Okay' returned by the SQLite functions...

If Not _SQLite_Exec(-1, "CREATE TEMP TABLE persons (Name, Age);") = $SQLITE_OK Then _

Does ADO.UDF return 'SQL_OK' ?

Thanks for this. Its brilliant.

Skysnake

Skysnake · April 2, 2017

Beta 2.1.14

ADO_EXAMPLE.au3 -

Func _Example_MSAccess()

    Local $sMDB_FileFullPath = Default ;'Here put FileFullPath to your Access File'
    Local $sDriver = Default
    Local $sUser = Default
    Local $sPassword = Default

    Local $sConnectionString = 'Driver={' & $sDriver & '};Dbq="' & $sFileFullPath & '";' & $sUser & $sPassword

    Local $sConnectionString = _ADO_ConnectionString_Access($sMDB_FileFullPath, $sUser, $sPassword, $sDriver)

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

EndFunc   ;==>_Example_MSAccess

Duplicate declaration of $sConnectionString

Skysnake

mLipok · April 2, 2017

Thanks.
mLipok

Skysnake · April 3, 2017

I am trying to get the same kind of "ADO_Ok" result from ADO as we get from the SQLite UDF.

So far:

; SQLite exmple html/libfunctions/_SQLite_GetTable2d.htm
    ; If Not _SQLite_Exec(-1, "CREATE TEMP TABLE persons (Name, Age);") = $ADO_ERR_SUCCESS Then _
    ;   MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg())

    ; like SQLite_Exec
    If Not _ADO_Execute($oCon, "CREATE temp TABLE persons (Name text, Age int);") = $ADO_ERR_SUCCESS Then
        ConsoleWrite("5a @ERROR " & @error & @CRLF)
        ConsoleWrite("5b @ERROR " & @error & @CRLF)
        ConsoleWrite("5c @extended " & @extended & @CRLF)
    Else
        MsgBox($MB_SYSTEMMODAL, "5. ADO Okay", $ADO_ERR_SUCCESS)
    EndIf

    ; like SQLite_GetTable2d
    $Query = "Select * from t_log_access order by 2 desc limit 5; "
    ;$aQresult = _ADO_Execute($oCon, $Query, True)

    ; Executing some query directly to Array of Arrays (instead to $oRecordset)
    Local $aResult, $Qresult0, $Qresult1, $Qresult2
    $aResult = _ADO_Execute($oCon, $Query, True, True)
    ConsoleWrite("4a @ERROR " & @error & @CRLF)
    ConsoleWrite("4b @ERROR " & @error & @CRLF)
    ConsoleWrite("4c @extended " & @extended & @CRLF)
    _ArrayDisplay($aResult, "4d Result of SELECT query") ; returns an array
    ; this result returns a single array (much like SQLite)
    ;$Qresult0 = _ArrayToString($aResult[0])
    ;$Qresult1 = _ArrayToString($aResult[1])
    ;$Qresult2 = _ArrayToString($aResult[2]) ; -- the Query array Result
    ConsoleWrite("4x $Query " & $Query & @CRLF)
    ConsoleWrite("4z $Qresult0 " & $Qresult0 & @CRLF)
    ConsoleWrite("4z $Qresult1 " & $Qresult1 & @CRLF)
    ConsoleWrite("4z $Qresult2 " & $Qresult2 & @CRLF)
    $aQresult = ''

    ; Executing some query directly to Array of Arrays (instead to $oRecordset)

    $aResult = _ADO_Execute($oCon, $Query, True, False) ; return an array, no column headers
    ConsoleWrite("6a @ERROR " & @error & @CRLF)
    ConsoleWrite("6b @ERROR " & @error & @CRLF)
    ConsoleWrite("6c @extended " & @extended & @CRLF)
    _ArrayDisplay($aResult, "6d Result of SELECT query") ; returns an array
    ;-- this result splits the 'returned' data into different rows of the result array
    $Qresult0 = _ArrayToString($aResult[0])
    $Qresult1 = _ArrayToString($aResult[1])
    $Qresult2 = _ArrayToString($aResult[2]) ; -- the Query array Result
    ConsoleWrite("6x $Query " & $Query & @CRLF)
    ConsoleWrite("6z $Qresult0 " & $Qresult0 & @CRLF)
    ConsoleWrite("6z $Qresult1 " & $Qresult1 & @CRLF)
    ConsoleWrite("6z $Qresult2 " & $Qresult2 & @CRLF)
    $aQresult = ''
    ConsoleWrite("4 Test insert end ---------------------- " & _Now() & @CRLF)

SQLite provides for the syntax

$iRval = _SQLite_GetTable2d(-1, "SELECT * FROM persons;", $aResult, $iRows, $iColumns)

Where $iRval returns the success ("SQLite_OK") on the query and $aResult the actual content of the result in array.

I managed to simulate SQLite_Exec with the first query above. I can't get this done with a result set.

Is there a different query I should use? Perhaps I miss something. The _ADO_Recordset??? functions all use an existing $aRecordset, I want to create mine from the query result AND report the function success.

Skysnake · April 3, 2017

ADO.UDF works with pgBouncer, a light weight connection pooler for Postgres.

get from GitHub

PostgreSQL wiki

Skysnake · April 3, 2017

Unsurprisingly, it also works with DBF files

Func _Example_MSdBase()
;tested with remote connection

    Local $sDBFDesc = 'dBase Files;'; Default ; 'specifiy ?.DBF in query'
    Local $sDriver = 'Microsoft dBASE Driver (*.dbf)'  ; 'from MS ODBC'

    Local $sConnectionString = 'Driver={' & $sDriver & '};datasource=' & $sDBFDesc & ';' 
    
ConsoleWrite("$sConnectionString " &$sConnectionString& @CRLF)

    _Example_2_RecordsetDisplay($sConnectionString, "Select * from w:\dbffolder\klienty.dbf where accnum like 'XYZ%'")
    _Example_3_ConnectionProperties($sConnectionString)

EndFunc   ;==>_Example_MSdBase

ADO.au3 UDF - BETA - Support Topic

Recommended Posts

Top Posters In This Topic

Top Posters In This Topic

Popular Posts

mLipok

mLipok

jchd

Posted Images

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Similar Content