ADO.au3 UDF - BETA - Support Topic

[mLipok]

@Valnurat ask me again in weekend about transaction example for ADO.au3 UDF.

I will be busy on ohter projects by few next days, but I will be in touch here.

 

[Skysnake]

Hmm, No. I believe that @Valnurat is looking in the wrong place.  The problem is NOT with the ADO UDF, but in how you use it.

Your screenshots seem to show either (a) some error in your script above the line you point to, ie you changed something above this line, which is causing this error, or (b) your ODBC driver is not defined.

Start with the ODBC driver for MS SQL Server.  This will be in the Windows Control Panel (Settings) under ODBC data sources.  It will display a name.  Mine shows that "SQL Server" in installed, but not available.  Check "Add" to add.

If you comment out the line

; _Example_MSSQL() ; -- commented out, no function call

then (a) the function will not be called.  But if you still get the error it means the error is not in the ADO UDF but somewhere else in your code.

I hope this helps.

Skysnake

[Skysnake]

@Valnurat - I have read this with a lot of mixed feelings.

You seem to foresee static lines in your DB which will be continuously "updated".  May I suggest a different approach?

A log table - add all the items with an INSERT and add a "systemtimestamp" column.  So regardless of how many users login, or when each such users logs in, that event is logged.

Later, simply SELECT a report on max systemtimestamp for each user...

This solves the race condition problem above.  It really becomes irrelevant when different users log in.

Problem solved? Or problem miss understood?

-Skysnake

[Valnurat]

@Skysnake I'm not sure what we are talking about here. For now my connection is working with "SQL Server". I have this in my code:
 

Global $sDriver = 'SQL Server'
Global $sDatabase = 'SM' ; change this string to YourDatabaseName
Global $sServer = 'se0400' ; change this string to YourServerLocation
Global $sUser = 'SMU' ; change this string to YourUserName
Global $sPassword = 'password' ; change this string to YourPassword
Global $sTableName = 'SMU.ComputerAsset'

and for now this is working.

[Valnurat]

But I do have another issue. I get this when I update my db:

 

###############################
ADO.au3 v.2.1.13 BETA (1155) : ==> COM Error intercepted !
$oADO_Error.description is:     [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated.
$oADO_Error.windescription:     Exception occurred.

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

I don't get it all the time.

[Skysnake]

Hmm. Toggle the ANSI / Unicode setting on the ODBC driver?

What exactly is the text of your query? Unescaped strings in your query?

[Valnurat]

I think it's because I have whitespace in the end of my string.

Do you know how I can delete that?

[Valnurat]

My query is:

$sQUERY = "Update " & $sTableName & _
                        " Set Username = '" & StringStripWS($aFile[0],2) & _
                        "', Mail = '" & StringStripWS($sMail,2) & _
                        "', Manager = '" & StringStripWS($sManager,2) & _
                        "', Location = '" & StringStripWS($sLocation,2) & _
                        "', Department = '" & StringStripWS($sDepartment,2) & _
                        "', CostCenter = '" & StringStripWS($sCostcenter,2) & _
                        "', DisplayName = '" & StringStripWS($sDisplay,2) & _
                        "', ComputerOwner = '" & StringStripWS($sSamAccountName,2) & _
                        "'  where ComputerName = '" & StringStripWS($sComputerName,2) & "'"

 

[mLipok]

8 hours ago, Valnurat said:

$oADO_Error.description is:     [Microsoft][ODBC SQL Server Driver]

String or binary data would be truncated.

 

 

One of updated field is shorter than data you trying to put/update.
You should check schema for this table and check how long is each field, after that you should control how long strings you are trying to set.

Regards,
mLipok

Edited February 23, 2017 by mLipok

[Valnurat]

Yes, I forgot to clear a variable first before re-using it again.

 

Now, I run into another issue. Why do I not see everything in a _arraydisplay, when I'm doing this:

Local $aFile[1], $sShipdate = "",$sFQDN = "", $sDisplay = "", $sLocation = "", $sMail = "", $sDepartment = "", $sType = "", $sManager = "", $sCostcenter = "", $sSamAccountName = ""
            $sComputerName = StringReverse($aFiles[$i])
            $sComputerName = StringReverse(StringMid($sComputerName,5,StringInStr($sComputerName,'\')-5))
            $sQUERY = "Select * from " & $sTableName
            ConsoleWrite($sQUERY)
            $oRecordset = _ADO_Execute($oConnection, $sQUERY)
            $aRecordsetArray = _ADO_Recordset_ToArray($oRecordset, False)
            $aRecordset_inner = _ADO_RecordsetArray_GetContent($aRecordsetArray)
            _ArrayDisplay($aRecordset_inner,"")

I only see the 1st column that contains "computername".

[Valnurat]

How do you error check when you have executred a query? I have tried this, but I don't get any error. I know in the for..to it will search for a computer that is not in the DB, but I don't get any error.

$sQUERY = "Select Computername, Username, Shipdate from " & $sTableName & " where ComputerName = '" & $sComputerName & "'"
ConsoleWrite($sQUERY)
$oRecordset = _ADO_Execute($oConnection, $sQUERY)
If @error Then
    MsgBox($MB_ICONERROR, 'EXECUTE', '@error = ' & @error & @CRLF & '@extended = ' & @extended)
    ConsoleWrite(SetError(@error, @extended, $ADO_RET_FAILURE))
    Exit
EndIf

 

[mLipok]

MsgBox is setting new @error and @extended values.
The same with ConsoleWrite

But at the end this all is useless as you are exiting Script instead returning function.

 

[Valnurat]

Yes, I will exit it, but first it will show a messagebox with @error from the last command.

How else should I show an error if I can't use msgbox?

[mLipok]

you can use: _ADO_COMErrorHandler_UserFunction() (check examples)

or try to use such a snippet:

Func _ConsoleWrapper($sData, $iError = @error, $iExtended = @extended)
    ; do what you want
    Return SetError($iError, $iExtended, 1) 
EndFunc

 

[Valnurat]

How do you use _ADO_ComErrorHandler_UserFunction

 

I have this in my code already.

; SetUP internal ADO.au3 UDF COMError Handler
_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler)

[mLipok]

Using this following snippet from example for ObjEvent() function from HelpFile :

; User's COM error function. Will be called if COM error occurs
Func _ErrFunc($oError)
    ; Do anything here.
    ConsoleWrite(@ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & @CRLF & _
            @TAB & "err.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) & @CRLF & _
            @TAB & "err.windescription:" & @TAB & $oError.windescription & @CRLF & _
            @TAB & "err.description is: " & @TAB & $oError.description & @CRLF & _
            @TAB & "err.source is: " & @TAB & @TAB & $oError.source & @CRLF & _
            @TAB & "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            @TAB & "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
            @TAB & "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            @TAB & "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            @TAB & "err.retcode is: " & @TAB & "0x" & Hex($oError.retcode) & @CRLF & @CRLF)
EndFunc   ;==>_ErrFunc

 

You can say to ADO.au3 to use this function, just like in following snippet:

_ADO_ComErrorHandler_UserFunction(_ErrFunc)

 

[Valnurat]

I'm sorry, but I don't understand how to use it.

I have done this:

_ADO_Execute($oConnection, $sQUERY)
If @error Then
    SetError(@error, @extended, $ADO_RET_FAILURE)
    ConsoleWrite('@error = ' & @error & @CRLF & '@extended = ' & @extended & @CRLF & $sQUERY & @CRLF )
EndIf

and I get an @error = 2 and an @extended = -2147352567

 

Where can I find the codes for these errors?

[mLipok]

Here is a conceptual example showing what you should do:

......
......
......
_ADO_ComErrorHandler_UserFunction(_ErrFunc)
.......
.......
.......
    _ADO_Execute($oConnection, $sQUERY)
    If @error Then
        SetError(@error, @extended, $ADO_RET_FAILURE)
        ConsoleWrite('@error = ' & @error & @CRLF & '@extended = ' & @extended & @CRLF & $sQUERY & @CRLF )
    EndIf
......
......
......
; User's COM error function. Will be called if COM error occurs
Func _ErrFunc($oError)
    ; Do anything here.
    ConsoleWrite(@ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & @CRLF & _
            @TAB & "err.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) & @CRLF & _
            @TAB & "err.windescription:" & @TAB & $oError.windescription & @CRLF & _
            @TAB & "err.description is: " & @TAB & $oError.description & @CRLF & _
            @TAB & "err.source is: " & @TAB & @TAB & $oError.source & @CRLF & _
            @TAB & "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            @TAB & "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
            @TAB & "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            @TAB & "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            @TAB & "err.retcode is: " & @TAB & "0x" & Hex($oError.retcode) & @CRLF & @CRLF)
EndFunc   ;==>_ErrFunc

 

[Valnurat]

Ok, I have this in my code:

; SetUP internal ADO.au3 UDF COMError Handler
_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler)

_Main()

Func _Main()
code
.....
ect

Do I add it just after _ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler) or just before I call _ADO_Execute($oConnection, $sQUERY)

EDIT: I solved it. Thank you.

Edited March 6, 2017 by Valnurat

[Skysnake]

This is an amazing tool. Thank you.

 

Feedback on ; Version .......: 2.1.13 BETA - Work in progress 2016/03/18

1. The UDF lacks an index of functions included.

2. 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

Output 

_Example_3_ConnectionProperties 2119
$Ex3_result 0
$sqlready -1
@ERROR0
@extended0

On unsuccessful connection I get

$Ex3_result -1 (this is the result of a failed attempt as well)
$sqlready -1
@ERROR0
@extended0

; I only toggle the pwd, this code remains the same
Local $Ex3_result = _Example_3_ConnectionProperties($sConnectionString)
_ArrayDisplay($Ex3_result)
ConsoleWrite("$Ex3_result " &$Ex3_result&  @CRLF)
SetError(  0 , 0)

Local $sqlready = __ADO_Connection_IsReady($sConnectionString)
_ArrayDisplay($sqlready)
ConsoleWrite("$sqlready " &$sqlready&  @CRLF)
ConsoleWrite("@ERROR"& @error&@CRLF)

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

Output on fail

_Example_3_ConnectionProperties 2119
###############################
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
###############################
$Ex3_result -1
$sqlready -1
@ERROR0
@extended0

Am I missing something?

 

Skysnake