Jump to content

Recommended Posts

Posted

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?

Skysnake

Why is the snake in the sky?

Posted

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

 

 

Posted
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

 

Posted (edited)

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

Edited by mLipok

Signature beginning:
Please remember: "AutoIt"..... *  Wondering who uses AutoIt and what it can be used for ? * Forum Rules *
ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * IE on Windows 11 * How to ask ChatGPT for AutoIt Codefor other useful stuff click the following button:

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST APIErrorLog.au3 UDF - A logging Library * Include Dependency Tree (Tool for analyzing script relations) * Show_Macro_Values.au3 *

 

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * 

OpenOffice/LibreOffice/XLS Related: WriterDemo.au3 * XLS/MDB from scratch with ADOX

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskSchedulerIE Embedded Control Versioning (use IE9+ and HTML5 in a GUI) * PDF Related:How to get reference to PDF object embeded in IE * IE on Windows 11

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

I also encourage you to check awesome @trancexx code:  * Create COM objects from modules without any demand on user to register anything. * Another COM object registering stuffOnHungApp handlerAvoid "AutoIt Error" message box in unknown errors  * HTML editor

winhttp.au3 related : * https://www.autoitscript.com/forum/topic/206771-winhttpau3-download-problem-youre-speaking-plain-http-to-an-ssl-enabled-server-port/

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2023-04-24

  • 2 weeks later...
Posted

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 😉

Posted (edited)

@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 by Skysnake

Skysnake

Why is the snake in the sky?

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

Signature beginning:
Please remember: "AutoIt"..... *  Wondering who uses AutoIt and what it can be used for ? * Forum Rules *
ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * IE on Windows 11 * How to ask ChatGPT for AutoIt Codefor other useful stuff click the following button:

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST APIErrorLog.au3 UDF - A logging Library * Include Dependency Tree (Tool for analyzing script relations) * Show_Macro_Values.au3 *

 

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * 

OpenOffice/LibreOffice/XLS Related: WriterDemo.au3 * XLS/MDB from scratch with ADOX

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskSchedulerIE Embedded Control Versioning (use IE9+ and HTML5 in a GUI) * PDF Related:How to get reference to PDF object embeded in IE * IE on Windows 11

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

I also encourage you to check awesome @trancexx code:  * Create COM objects from modules without any demand on user to register anything. * Another COM object registering stuffOnHungApp handlerAvoid "AutoIt Error" message box in unknown errors  * HTML editor

winhttp.au3 related : * https://www.autoitscript.com/forum/topic/206771-winhttpau3-download-problem-youre-speaking-plain-http-to-an-ssl-enabled-server-port/

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2023-04-24

Posted

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

Schowek01.thumb.jpg.8100943cccd925953f97b05b4de9728b.jpg

 

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

Posted
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 ?

Signature beginning:
Please remember: "AutoIt"..... *  Wondering who uses AutoIt and what it can be used for ? * Forum Rules *
ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * IE on Windows 11 * How to ask ChatGPT for AutoIt Codefor other useful stuff click the following button:

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST APIErrorLog.au3 UDF - A logging Library * Include Dependency Tree (Tool for analyzing script relations) * Show_Macro_Values.au3 *

 

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * 

OpenOffice/LibreOffice/XLS Related: WriterDemo.au3 * XLS/MDB from scratch with ADOX

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskSchedulerIE Embedded Control Versioning (use IE9+ and HTML5 in a GUI) * PDF Related:How to get reference to PDF object embeded in IE * IE on Windows 11

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

I also encourage you to check awesome @trancexx code:  * Create COM objects from modules without any demand on user to register anything. * Another COM object registering stuffOnHungApp handlerAvoid "AutoIt Error" message box in unknown errors  * HTML editor

winhttp.au3 related : * https://www.autoitscript.com/forum/topic/206771-winhttpau3-download-problem-youre-speaking-plain-http-to-an-ssl-enabled-server-port/

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2023-04-24

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

  • 1 month later...
Posted

You can always use:

_ADO_Schema_GetAllCatalogs()

But it could depend....
 

Which DataBase engine you use ? MS SQL ?

Signature beginning:
Please remember: "AutoIt"..... *  Wondering who uses AutoIt and what it can be used for ? * Forum Rules *
ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * IE on Windows 11 * How to ask ChatGPT for AutoIt Codefor other useful stuff click the following button:

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST APIErrorLog.au3 UDF - A logging Library * Include Dependency Tree (Tool for analyzing script relations) * Show_Macro_Values.au3 *

 

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * 

OpenOffice/LibreOffice/XLS Related: WriterDemo.au3 * XLS/MDB from scratch with ADOX

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskSchedulerIE Embedded Control Versioning (use IE9+ and HTML5 in a GUI) * PDF Related:How to get reference to PDF object embeded in IE * IE on Windows 11

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

I also encourage you to check awesome @trancexx code:  * Create COM objects from modules without any demand on user to register anything. * Another COM object registering stuffOnHungApp handlerAvoid "AutoIt Error" message box in unknown errors  * HTML editor

winhttp.au3 related : * https://www.autoitscript.com/forum/topic/206771-winhttpau3-download-problem-youre-speaking-plain-http-to-an-ssl-enabled-server-port/

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2023-04-24

Posted (edited)

Hi,mLipokI'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 by Hadin
Missing Description Problem
  • Developers
Posted (edited)

@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 by Jos

SciTE4AutoIt3 Full installer Download page   - Beta files       Read before posting     How to post scriptsource   Forum etiquette  Forum Rules 
 
Live for the present,
Dream of the future,
Learn from the past.
  :)

Posted (edited)

@Hadin

change your execution so that it has four express params?

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

 

Edited by Skysnake

Skysnake

Why is the snake in the sky?

Posted

@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 ?
 

Signature beginning:
Please remember: "AutoIt"..... *  Wondering who uses AutoIt and what it can be used for ? * Forum Rules *
ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * IE on Windows 11 * How to ask ChatGPT for AutoIt Codefor other useful stuff click the following button:

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST APIErrorLog.au3 UDF - A logging Library * Include Dependency Tree (Tool for analyzing script relations) * Show_Macro_Values.au3 *

 

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * 

OpenOffice/LibreOffice/XLS Related: WriterDemo.au3 * XLS/MDB from scratch with ADOX

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskSchedulerIE Embedded Control Versioning (use IE9+ and HTML5 in a GUI) * PDF Related:How to get reference to PDF object embeded in IE * IE on Windows 11

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

I also encourage you to check awesome @trancexx code:  * Create COM objects from modules without any demand on user to register anything. * Another COM object registering stuffOnHungApp handlerAvoid "AutoIt Error" message box in unknown errors  * HTML editor

winhttp.au3 related : * https://www.autoitscript.com/forum/topic/206771-winhttpau3-download-problem-youre-speaking-plain-http-to-an-ssl-enabled-server-port/

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2023-04-24

Posted

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,

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...