ADO.au3 UDF - BETA - Support Topic

[Skysnake]

I have not yet managed to successfully declare and use a cursor using AutoIt. Perhaps this is the right track? Is there perhaps a table/dataset that we can test on, so we have the same underlying data to compare results?

[Lecdev]

mLipok I think in my opinion you are almost right. As far as I know mssql should always support bookmarks but not all data sources will and ado could be used for something else.

maybe best method is to prefer bookmarks and use adBookmarkFirst but if its not supported to then check support for adMovePrevious and if that is ok you can use moveFirst, if neither are supported I think there is nothing more you can do except explicitly close the recordset before the next query. You have done this way with _ADO_Recordset_ToString but not in _ADO_Recordset_ToArray

 

I found the truth table by the way it is here in the details for BOF, EOF properties.

https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/bof-eof-properties-ado?view=sql-server-ver15

 

 

[Lecdev]

1 hour ago, Skysnake said:

I have not yet managed to successfully declare and use a cursor using AutoIt. Perhaps this is the right track? Is there perhaps a table/dataset that we can test on, so we have the same underlying data to compare results?

I haven't tried this yet but the way I read on msdn it should basically be like this and shouldn't need much more?

Func _ADO_Recordset_Open_New_Custom(ByRef $oActiveConnection, $sSQL_QueryStatement, $iCurType = 0, $iLockType = 1)
    Local Const $oADO_COMErrorHandler = ObjEvent("AutoIt.Error", __ADO_ComErrorHandler_WrapperFunction)
    If @error Then Return SetError($ADO_ERR_COMHANDLER, @error, $ADO_RET_FAILURE)
    #forceref $oADO_COMErrorHandler

    __ADO_Connection_IsReady($oActiveConnection)
    If @error Then
        Return SetError(@error, @extended, $ADO_RET_FAILURE)
    ElseIf Not IsString($sSQL_QueryStatement) Then
        Return SetError($ADO_ERR_INVALIDPARAMETERTYPE, $ADO_EXT_PARAM2, $ADO_RET_FAILURE)
    ElseIf $sSQL_QueryStatement = "" Then
        Return SetError($ADO_ERR_INVALIDPARAMETERVALUE, $ADO_EXT_PARAM2, $ADO_RET_FAILURE)
    EndIf

    Local $oRecordset = ObjCreate("ADODB.Recordset")
    If @error Then Return SetError($ADO_ERR_COMERROR, @error, $ADO_RET_FAILURE)

    $oRecordset.ActiveConnection = $oActiveConnection ; should be an already open active connection
    $oRecordset.DataSource = $sSQL_QueryStatement ; should be a query statement that returns data else the recordset will stay closed
    $oRecordset.CursorType = $iCurType ; from CursorTypeEnum (default 0 is forward-only) declare your custom cursor here, it cannot be done on an already open recordset
    $oRecordset.LockType = $iLockType ; from LockTypeEnum (default 1 is read-only)
    ;
    $oRecordset.Open
    ;
    Return SetError($ADO_ERR_SUCCESS, $ADO_EXT_DEFAULT, $oRecordset) ; sould return the recordset with the custom cursor?
EndFunc

 

[mLipok]

I will go deeply into this issue at night.
Currently I'm busy at work.

Edited August 30, 2021 by mLipok

[Viszna]

Hello
I have a problem with ADo and more specifically with access to the database.
Until now, I have been using a database to read 1 record while the script is running and finally save 1 record.
Runs 1-5 times a day and works OK
2-5 minutes pass from _ReadDBO() to _WriteDBO ($sSQL)
In simple terms, the program looked like this:

;START script
_ReadDBO()
...
...
...
_WriteDBO($sSQL)
Exit
;END script

Func _ReadDBO()
    _ADO_Connection_Create()
    _ADO_Connection_OpenConString($oConnection, $MultiBot_sConnectionString)
    _ADO_Execute($oConnection, $sSQL)
    _ADO_Connection_Close($oConnection)
EndFunc


Func _WriteDBO($sSQL)
    _ADO_Connection_Create()
    _ADO_Connection_OpenConString($oConnection, $MultiBot_sConnectionString)
    _ADO_Execute($oConnection, $sSQL)
    _ADO_Connection_Close($oConnection)
EndFunc

Now I started using ADO in a script that executes _ReadDBO() and _WriteDBO($sSQL) every 1-2 seconds and the script is run on 5 computers (automation of the business process in the database, downloads the data from the database to be processed and saves the results to database)

Problems with access to the database started, message:
"User 'userDBO' already has more than 'max_user_connections' active connections" -MySQL base

My question:
Does the function call:
_ADO_Connection_Create () and _ADO_Connection_OpenConString ($oConnection,  $MultiBot_sConnectionString)
should be at the beginning of the program and should NOT be called with every query to the database?
See the example below:

;START script
_ADO_Connection_Create()    ; move from Func
_ADO_Connection_OpenConString($oConnection, $MultiBot_sConnectionString) ; move from Func
Do
    _ReadDBO()
    ...
    ...
    ...
    _WriteDBO($sSQL)
Until 0
_ADO_Connection_Close($oConnection) ; move from Func
Exit
;END script

Func _ReadDBO()
    _ADO_Execute($oConnection, $sSQL)
EndFunc


Func _WriteDBO($sSQL)
    _ADO_Execute($oConnection, $sSQL)
EndFunc

I have never encountered such an error message before, even when I was practicing with ADO and calling the base every 1 second.

P.S.
@mLipok Thank you very much for your contribution to AutoIT - very much respect indeed, and sorry for my "translate.google" English 😉

[Skysnake]

@Viszna  welcome, I think your problem is discussed here:

https://dba.stackexchange.com/questions/47131/how-to-get-rid-of-maximum-user-connections-error

I do not know how to terminate your MySQL server side connection. The server is probably keeping your connection open (for a limited time) after you have closed it on your side.

Also look at connection pooling?

Your suggestion of opening one connection and running all queries on that for the day, may be the quickest solution. But this is not guaranteed to provide a long term solution.

S

Edited September 10, 2021 by Skysnake

[mLipok]

On 9/10/2021 at 8:27 AM, Viszna said:

Does the function call:
_ADO_Connection_Create () and _ADO_Connection_OpenConString ($oConnection,  $MultiBot_sConnectionString)
should be at the beginning of the program and should NOT be called with every query to the database?

Absolutely.

But in your case, that's not exactly the problem.

On 9/10/2021 at 8:27 AM, Viszna said:

Now I started using ADO in a script that executes _ReadDBO() and _WriteDBO($sSQL) every 1-2 seconds and the script is run on 5 computers (automation of the business process in the database, downloads the data from the database to be processed and saves the results to database)

Problems with access to the database started, message:
"User 'userDBO' already has more than 'max_user_connections' active connections" -MySQL base

 

On 9/10/2021 at 8:50 AM, Skysnake said:

I think your problem is discussed here:

https://dba.stackexchange.com/questions/47131/how-to-get-rid-of-maximum-user-connections-error

I think this is exactly the same issue which concerns @Viszna .
 

On 9/10/2021 at 8:50 AM, Skysnake said:

I do not know how to terminate your MySQL server side connection. The server is probably keeping your connection open (for a limited time) after you have closed it on your side.

I think that "max_user_connections" is set to 1 and when @Viszna runs the same scirpt on 5 computers then it is quite likely that he is trying to use more connections at the same time.

[Viszna]

Hello,
Sorry to just write back, but I was out.
I cannot change the database configuration.
max_user_connections = 20 ?? look at the screen

 

How I "threw" _ADO_Connection to the beginning of the program, outside the loop, the script is very stable, no errors, much faster.

Currently, I call the database several times a second and everything is OK

regards
Viszna

[mLipok]

29 minutes ago, Viszna said:

Currently, I call the database several times a second and everything is OK

From 1 or from 5 computers at once ?

[Viszna]

15 hours ago, mLipok said:

From 1 or from 5 computers at once ?

2 computers non-stop simultaneously and sometimes 3 computers (only temporarily 1-2 minutes)

[polps]

Hi all,

Is there a function to get the list of all user database?

 

[mLipok]

You can always use:

_ADO_Schema_GetAllCatalogs()

But it could depend....
 

Which DataBase engine you use ? MS SQL ?

[polps]

Tanks mLipok

Yes sorry, I forget to specify... Ms Sql Server

[Skysnake]

Looks like this is the answer to your query

https://stackoverflow.com/questions/147659/get-list-of-databases-from-sql-server

Run a normal SELECT in an ADO connection?

SELECT name FROM master.dbo.sysdatabases

 

[polps]

Yes, it works.

Thanks a lot!

 

[Hadin]

Hi，mLipok，I'm in trouble and I need your help：

I want to use Sqlserver or PostgreSQL to upload and download large files for management.

The Sqlserver uses varbinary(max), the PostgreSQL uses BYTEA,

and the Autoit is fileopen(file，16) .  After binary files are read and uploaded, all small files can be used. However, if a single file is larger than 85 MB, an unknown error occurs and the memory is insufficient. For example, if a large file is successfully uploaded, an error is reported during ADO query. We haven't found a way to do it yet, thank you.

#include <..\USclude\ADO.au3>
    #include <Array.au3>

    Global Enum $sDriver,$sServer,$sPort,$sDatabase,$sUser,$sPassword,$AppName
    Global Static $ConStr[7]

    $ConStr[$sDriver]=      'PostgreSQL Unicode'
    $ConStr[$sServer]=      '192.168.1.1'
    $ConStr[$sPort]=        '5432'
    $ConStr[$sDatabase]=    'Main_DB'
    $ConStr[$sUser]=        'postgres'
    $ConStr[$sPassword]=    'password'
    $ConStr[$AppName]=      'ADO_TestApp'

    Local $sConnectionString =  'Driver={' &    $ConStr[$sDriver] & '};'& _
                                'DATABASE=' &   $ConStr[$sDatabase] & ';'& _
                                'SERVER=' &     $ConStr[$sServer] & ';'& _
                                'PORT=' &       $ConStr[$sPort] & ';'& _
                                'UID=' &        $ConStr[$sUser] & ';'& _
                                'PWD=' &        $ConStr[$sPassword]& ';'& _
                                'APP='&         $ConStr[$AppName]&';'

    Local $SqlConnet = _ADO_Connection_Create()
    If @error Then Exit(ConsoleWrite("Error"&@CRLF))
    _ADO_Connection_OpenConString($SqlConnet, $sConnectionString)

    Local $Ruturn=_ADO_Execute($SqlConnet,"select abinary,aVersion from TB_a_Test WHERE filesname like '%MyExcel.docx%';",True)
    _ArrayDisplay($Ruturn)

It should not be the database problem, but the setting or method limitation of the ado. Ask for help.thanks verymuch！

Edited November 8, 2021 by Hadin
Missing Description Problem

[Jos]

@Hadin,

Please do not cross-post your question in multiple threads and define here hat the problem is you are having as the above post doesn't tell us that!

Your other post is removed.

Jos

Edited November 8, 2021 by Jos

[Skysnake]

@Hadin

change your execution so that it has four express params?

$aResult = _ADO_Execute($g_DB, $sSQL, True, True)   ; select, expect table

 

Edited November 8, 2021 by Skysnake

[mLipok]

@Hadin does this problem only occur while you are using:

6 hours ago, Hadin said:

_ArrayDisplay($Ruturn)

?

and:

6 hours ago, Hadin said:

For example, if a large file is successfully uploaded, an error is reported during ADO query.

What COM ERROR Handler said to you ?
 

[Hadin]

Hi,mLipok,Skysnake,Thanks for the reply:

Here's how I got a problem:

#include <..\USclude\ADO.au3>
   #include <Array.au3>

    Global Enum $sDriver,$sServer,$sPort,$sDatabase,$sUser,$sPassword,$AppName
    Global Static $ConStr[7]

    $ConStr[$sDriver]=      'PostgreSQL Unicode'
    $ConStr[$sServer]=      '192.168.1.1'
    $ConStr[$sPort]=        '5432'
    $ConStr[$sDatabase]=    'Main_DB'
    $ConStr[$sUser]=        'postgres'
    $ConStr[$sPassword]=    'password'
    $ConStr[$AppName]=      'ADO_TestApp'

    Local $sConnectionString =  'Driver={' &    $ConStr[$sDriver] & '};'& _
                                'DATABASE=' &   $ConStr[$sDatabase] & ';'& _
                                'SERVER=' &     $ConStr[$sServer] & ';'& _
                                'PORT=' &       $ConStr[$sPort] & ';'& _
                                'UID=' &        $ConStr[$sUser] & ';'& _
                                'PWD=' &        $ConStr[$sPassword]& ';'& _
                                'APP='&         $ConStr[$AppName]&';'

   Local $SqlConnet = _ADO_Connection_Create()
   If @error Then Exit(ConsoleWrite("Error"&@CRLF))
   _ADO_Connection_OpenConString($SqlConnet, $sConnectionString)

   Local $SelectPath=@DesktopDir&'\85MB.docx';one large-capacity file
    Local $FileHw=FileOpen($SelectPath,16)
    Local $FileBinary=FileRead($FileHw)
    FileClose($FileHw)

    ;$FileBinary:  StringLen($FileBinary)/1024/1024=159.64MB

    Local $DB_Statement="insert into TB_a_Test(InsertTime,WIBinary)values(now(),'"&$FileBinary&"')"
    Local $oRecordset = _ADO_Execute($SqlConnet, $DB_Statement)

The upload error occurs only when the file size is greater than 60 MB and the error is reported when the function _ADO_Execute is running [Local $oRecordset = $oConnection.Execute($sQuery)].

;;in Postgresql errorcode：
! 1668 == 0
###############################
ADO.au3 v.2.1.19 BETA (1379) : ==> COM Error intercepted !
$oADO_Error.description is:     No memory available to store statement
$oADO_Error.windescription:     ****

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

;;in SQL Server errorcode:
! 1668 == 0
###############################
ADO.au3 v.2.1.19 BETA (1379) : ==> COM Error intercepted !
$oADO_Error.description is:     [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()).
$oADO_Error.windescription:     *****

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

Is this method not applicable to large-capacity files that are read in binary mode and saved to the database? Currently, small-capacity files are not affected,