Jump to content

Recommended Posts

On 10/18/2020 at 9:15 PM, mLipok said:

haha...

I just bougth this book:

https://www.ebay.com/itm/302366891714

s-l500.jpg

I hope soon I find time to provide new features and examples based on the knowledge which I gather thanks to this above mentioned book.

 

btw.
Today I also buy this one:
s-l500.jpg

 

I hope it will be delivered to me sometime in a month, so it is possible that after the summer vacation, sometime in about half a year, I will have the opportunity to present new curiosities.

 

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

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 *

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

"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: 2021-10-17

Link to post
Share on other sites
  • 1 month later...
  • Replies 529
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Popular Posts

I want to present BETA Version of my ADO.au3 UDF. This is modifed version of _sql.au3 UDF.   For that I want to thanks : ; Chris Lambert, eltorro, Elias Assad Neto, CarlH   T

I hope soon I find time to provide new features and examples based on the knowledge which I gather thanks to this above mentioned book.   btw. Today I also buy this one:   I hope

Remark 1: Wiki page are talking about ADO in general - I mean not related to my UDF. Wiki examples for ADO are very similar as I changed many of them to use the same variable names as in my ADO

Posted Images

@mLipok

OK. Downloaded the most resent beta and things are almost back to normal.

I am getting an error 

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

 

Console

error: _ADO_COMErrorHandler(): undefined function.

_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler)

 

Note that all of this worked until I started the update 😐

 

Found this 

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

 

 

Edited by Skysnake

Skysnake

Why is the snake in the sky?

Link to post
Share on other sites
Posted (edited)

@Skysnake in shortcut.... do you need my help or all ADO works on your side ?

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

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 *

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

"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: 2021-10-17

Link to post
Share on other sites

@mLipok I finally got it to work. May I suggest a "current recommended download" option? Or similar?  My problem was that I started at the top of the list, when I should just have gone down to the most recent. :(

All is well now.

Thanks for asking.

Skysnake

Why is the snake in the sky?

Link to post
Share on other sites
Posted (edited)

This is how "Upload new version" and "Download" features works.
Nothing more.
The only things which I could do would be video tutorial on YT "How to download files from AutoIt forum Download section" ;)

 

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

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 *

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

"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: 2021-10-17

Link to post
Share on other sites
  • 5 months later...

Has anyone got any examples working with BLOBs ( Varbinary(max) ) on MSSQL with ADO?

I've done with SQLite which is allot easier for this specifically and now i have a few things to migrate to sql server, my brain is melting.

Link to post
Share on other sites

Please create SQL statement to create Example database.

Then I provide Example how to put PDF file as a blob.

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

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 *

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

"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: 2021-10-17

Link to post
Share on other sites
#include <ADO.au3>

_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler_Function)

_BLOB_Example()



Func _BLOB_Example()
    _MSSQL_CreateDatabaseWithNameAndPath()
    _MSSQL_CreateBlobTable()
    Local $hFileOpened = FileOpen("SomePdf.pdf", 16) ; opened in  $FO_BINARY mode
    Local $bFileData = FileRead($hFileOpened) ; whole file in binary
    FileClose($hFileOpened)
    _MSSQL_InsertFileBinary($bFileData)
    $bFileData = Null
    _MSSQL_ReadFileBinary($bFileData)
    $hFileOpened = FileOpen("SomePdfNewCopy.pdf", 26) ; opened in  ($FO_BINARY, $FO_OVERWRITE, $FO_CREATEPATH) mode
    FileWrite($hFileOpened, $bFileData)
    If Not @Compiled Then ConsoleWrite("Done, Exiting in 2 Seconds")
    ;
    Sleep(2000)
    Exit
    ;
EndFunc   ;==>_BLOB_Example

Func _MSSQL_CreateDatabaseWithNameAndPath($NewDbName = "Test_db", $DirectoryPath = @ScriptDir, $Server = "localhost\SQLEXPRESS", $User = "sa", $Pwd = "")
    If $NewDbName = "" Then Return SetError(-1, -2, $ADO_RET_FAILURE)
    If $DirectoryPath = "" Then Return SetError(-2, -2, $ADO_RET_FAILURE)
    ; note *** directories must exist first, use DirGetSize() to ensure the path exists then if not create with DirCreate()
    Local $sConnectionString = 'DRIVER={SQL Server};SERVER=' & $Server & ';DATABASE=master;UID=' & $User & ';PWD=' & $Pwd & ';'
    Local $commandstr = "select name from master.dbo.sysdatabases where name='" & $NewDbName & "'"
    Local $CreateNew = True
    ; 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)
    ; check if database exists already
    Local $oRecordset_Final = _ADO_Execute($oConnection, $commandstr)
    Local $aRowsResult = _ADO_Recordset_ToArray($oRecordset_Final)
    If IsArray($aRowsResult) Then
        Local $aRecordsetContent = _ADO_RecordsetArray_GetContent($aRowsResult)
        If IsArray($aRecordsetContent) Then
            If $aRecordsetContent[0][0] = $NewDbName Then
                $CreateNew = False ; already exists
            EndIf
        EndIf
    EndIf
    If $CreateNew Then ; doesnt exist, create it
        $commandstr = "CREATE DATABASE " & $NewDbName & " ON PRIMARY " & _
                "( NAME = " & $NewDbName & ", " & _
                "FILENAME = '" & $DirectoryPath & "\" & $NewDbName & ".mdf', " & _
                "SIZE = 2MB, MAXSIZE = 100MB) " & _
                "LOG ON (NAME = " & $NewDbName & "_log, " & _
                " FILENAME = '" & $DirectoryPath & "\" & $NewDbName & "_log.ldf', " & _
                " SIZE = 2MB, " & _
                " MAXSIZE = 100MB )"
        Local $oCommand = _ADO_Command_Create($oConnection)
        If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)
        Local $Result = _ADO_Command_Execute($oCommand, $commandstr)
        If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)
        If Not @Compiled Then ConsoleWrite("succesfully created database !" & @CRLF)
    Else
        If Not @Compiled Then ConsoleWrite("database exists already ! No need to create !" & @CRLF)
    EndIf
    ; Clean Up
    $oRecordset_Final = Null
    _ADO_Connection_Close($oConnection)
    $oConnection = Null
EndFunc   ;==>_MSSQL_CreateDatabaseWithNameAndPath

Func _MSSQL_CreateBlobTable($DbName = "Test_db", $Server = "localhost\SQLEXPRESS", $User = "sa", $Pwd = "")
    Local $sConnectionString = 'DRIVER={SQL Server};SERVER=' & $Server & ';DATABASE=' & $DbName & ';UID=' & $User & ';PWD=' & $Pwd & ';'
    Local $oConnection = _ADO_Connection_Create()
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    Local $oRecordset_Tables = _ADO_OpenSchema_Tables($oConnection, $DbName, Default, "FILE_BIN_DATA")
    If @error >= $ADO_ERR_RECORDSETEMPTY Then
        _ADO_Execute($oConnection, "CREATE TABLE FILE_BIN_DATA (ROWID int IDENTITY(1,1) NOT NULL" & _
                ", File_Name Varchar(60) NOT NULL" & _
                ", File_Data Varbinary(max) NULL" & _
                ", CONSTRAINT [PK_FILE_BIN_DATA] PRIMARY KEY CLUSTERED " & _
                "(" & _
                "   ROWID ASC " & _
                ")) " & _
                "ON [PRIMARY]")

        If @error Then
            If Not @Compiled Then ConsoleWrite('! ---> @error=' & @error & '  @extended=' & @extended & ' : CREATE TABLE' & @CRLF)
        EndIf
        $oRecordset_Tables = _ADO_OpenSchema_Tables($oConnection, $DbName, Default, "FILE_BIN_DATA")
    EndIf
    If Not @Compiled Then ConsoleWrite(_ADO_Recordset_ToString($oRecordset_Tables))
    ; Clean Up
    $oRecordset_Tables = Null
    _ADO_Connection_Close($oConnection)
    $oConnection = Null
EndFunc   ;==>_MSSQL_CreateBlobTable

Func _MSSQL_InsertFileBinary(ByRef $bFileBinaryIn)
    ;; do some insert command/ stream
EndFunc   ;==>_MSSQL_InsertFileBinary

Func _MSSQL_ReadFileBinary(ByRef $bFileBinaryOut)
    ; do some select command/ stream
EndFunc   ;==>_MSSQL_ReadFileBinary

 

here is some code, looking forward to some help.

Link to post
Share on other sites
Posted (edited)
On 8/27/2021 at 10:32 PM, Lecdev said:

here is some code, looking forward to some help.

Working on..... stay tuned ;)

 

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

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 *

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

"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: 2021-10-17

Link to post
Share on other sites
Posted (edited)

To understand it well, I initially refactored it to my approach ( a way of writing code ).
This is not a working version of your example yet, but only an intermediate version.

#include <FileConstants.au3>
#include <MsgBoxConstants.au3>
#include "ADO.au3"

_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler_Function)

_BLOB_Example()
If @error Then ConsoleWrite("! ---> @error=" & @error & "  @extended=" & @extended & _
        " : Error in example" & @CRLF)

Exit

Func _BLOB_Example()
    _MSSQL_CreateDatabaseWithNameAndPath()
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    _MSSQL_CreateBlobTable()
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; read whole file in binary
    Local $hFileOpened = FileOpen("SomePdf.pdf", $FO_BINARY)
    Local $bFileData = FileRead($hFileOpened)
    FileClose($hFileOpened)

    _MSSQL_InsertFileBinary($bFileData)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    $bFileData = Null

    _MSSQL_ReadFileBinary($bFileData)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; write binary content to new file
    $hFileOpened = FileOpen("SomePdfNewCopy.pdf", $FO_BINARY + $FO_OVERWRITE + $FO_CREATEPATH)
    FileWrite($hFileOpened, $bFileData)
    If Not @Compiled Then ConsoleWrite("Done, Exiting in 2 Seconds")

    ; final check - open file in associated program
    If $IDYES = MsgBox($MB_YESNO + $MB_TOPMOST + $MB_ICONQUESTION + $MB_DEFBUTTON1, "Question", _
            "Do you wan try to open saved file ?") Then
        ShellExecute("SomePdfNewCopy.pdf")
    EndIf


EndFunc   ;==>_BLOB_Example

Func _MSSQL_CreateDatabaseWithNameAndPath($NewDbName = "Test_db", $DirectoryPath = @ScriptDir, $Server = "localhost\SQLEXPRESS", $User = "sa", $Pwd = "")
    ; parameters validation
    If $NewDbName = "" Then Return SetError(-$ADO_ERR_INVALIDPARAMETERVALUE, -$ADO_EXT_PARAM1, $ADO_RET_FAILURE)
    If $DirectoryPath = "" Then Return SetError(-$ADO_ERR_INVALIDPARAMETERVALUE, -$ADO_EXT_PARAM2, $ADO_RET_FAILURE)

    If $Pwd = "" Then $Pwd = "AutoIt" ; this is my temp passwd for this example project

    ; note *** directories must exist first, use DirGetSize() to ensure the path exists then if not create with DirCreate()

    ; Create connection and open with $sConnectionString
    Local $oConnection = _ADO_Connection_Create()
    Local $sConnectionString = 'DRIVER={SQL Server};SERVER=' & $Server & ';DATABASE=master;UID=' & $User & ';PWD=' & $Pwd & ';'
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; check if database exists already
    Local $commandstr = _
            "SELECT" & @CRLF & _
            "   [name]" & @CRLF & _
            "FROM" & @CRLF & _
            "   [master].dbo.[sysdatabases]" & @CRLF & _
            "WHERE" & @CRLF & _
            "   [name]='" & $NewDbName & "'" & @CRLF & _
            ""
    Local $oRecordset_Final = _ADO_Execute($oConnection, $commandstr)
    Local $aRowsResult = _ADO_Recordset_ToArray($oRecordset_Final)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    Local $aRecordsetContent = _ADO_RecordsetArray_GetContent($aRowsResult)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    Local $CreateNew = Not ($aRecordsetContent[0][0] == $NewDbName ) ; If already exists then do not $CreateNew ... $CreateNew = False

    If Not $CreateNew Then ; if exist
        If Not @Compiled Then ConsoleWrite("database exists already ! No need to create !" & @CRLF)
    Else ; doesnt exist, create it
        $commandstr = _
                "CREATE DATABASE" & @CRLF & _
                "   " & $NewDbName & @CRLF & _
                "ON PRIMARY" & @CRLF & _
                "   (" & @CRLF & _
                "       NAME=" & $NewDbName & @CRLF & _
                "       ,FILENAME='" & $DirectoryPath & "\" & $NewDbName & ".mdf'" & @CRLF & _
                "       ,SIZE=2MB" & @CRLF & _
                "       ,MAXSIZE=100MB" & @CRLF & _
                "   ) " & @CRLF & _
                "LOG ON " & @CRLF & _
                "   (" & @CRLF & _
                "       NAME=" & $NewDbName & "_log" & @CRLF & _
                "       ,FILENAME='" & $DirectoryPath & "\" & $NewDbName & "_log.ldf'" & @CRLF & _
                "       ,SIZE=2MB" & @CRLF & _
                "       ,MAXSIZE=100MB" & @CRLF & _
                "   )"
        Local $oCommand = _ADO_Command_Create($oConnection)
        If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

        Local $Result = _ADO_Command_Execute($oCommand, $commandstr)
        If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)
        #forceref $Result

        If Not @Compiled Then ConsoleWrite("succesfully created database !" & @CRLF)
    EndIf

    ; Clean Up
    $oRecordset_Final = Null
    _ADO_Connection_Close($oConnection)
    $oConnection = Null
EndFunc   ;==>_MSSQL_CreateDatabaseWithNameAndPath

Func _MSSQL_CreateBlobTable($DbName = "Test_db", $Server = "localhost\SQLEXPRESS", $User = "sa", $Pwd = "")
    ; create connection and open with desired connection string
    Local $oConnection = _ADO_Connection_Create()
    Local $sConnectionString = 'DRIVER={SQL Server};SERVER=' & $Server & ';DATABASE=' & $DbName & ';UID=' & $User & ';PWD=' & $Pwd & ';'
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; check if table already exist
    Local $oRecordset_Tables = _ADO_OpenSchema_Tables($oConnection, $DbName, Default, "FILE_BIN_DATA")
    If @error >= $ADO_ERR_RECORDSETEMPTY Then
        ; if table not exist create them
        Local $s_SQLQuery_CreateTable = _
                "CREATE TABLE" & @CRLF & _
                "   FILE_BIN_DATA" & @CRLF & _
                "   (" & @CRLF & _
                "       ROWID int IDENTITY(1,1) NOT NULL" & @CRLF & _
                "       ,File_Name Varchar(60) NOT NULL" & @CRLF & _
                "       ,File_Data Varbinary(max) NULL" & @CRLF & _
                "       ,CONSTRAINT [PK_FILE_BIN_DATA] PRIMARY KEY CLUSTERED " & @CRLF & _
                "           (ROWID ASC)" & @CRLF & _
                "   ) " & @CRLF & _
                "ON [PRIMARY]"

        _ADO_Execute($oConnection, $s_SQLQuery_CreateTable)
        If @error Then
            If Not @Compiled Then ConsoleWrite('! ---> @error=' & @error & '  @extended=' & @extended & ' : CREATE TABLE' & @CRLF)
        Else
            $oRecordset_Tables = _ADO_OpenSchema_Tables($oConnection, $DbName, Default, "FILE_BIN_DATA")
        EndIf
    EndIf

    ; show info about the table
    If Not @Compiled Then ConsoleWrite(_ADO_Recordset_ToString($oRecordset_Tables))

    ; Clean Up
    $oRecordset_Tables = Null
    _ADO_Connection_Close($oConnection)
    $oConnection = Null

EndFunc   ;==>_MSSQL_CreateBlobTable

Func _MSSQL_InsertFileBinary(ByRef $bFileBinaryIn)
    #forceref $bFileBinaryIn
    ;; do some insert command/ stream
EndFunc   ;==>_MSSQL_InsertFileBinary

Func _MSSQL_ReadFileBinary(ByRef $bFileBinaryOut)
    #forceref $bFileBinaryOut
    ; do some select command/ stream
EndFunc   ;==>_MSSQL_ReadFileBinary

 

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

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 *

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

"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: 2021-10-17

Link to post
Share on other sites
Posted (edited)

as so far I get:

Quote

[Microsoft][ODBC SQL Server Driver]

The CREATE DATABASE statement failed. The primary file must be at least 5 MB to accommodate a copy of the model database.

 

so I customize a SQL query...... WIP

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

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 *

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

"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: 2021-10-17

Link to post
Share on other sites
Posted (edited)

I hope that the following quite complementary example will prove to be helpful not only for you, but also for other's AutoIt coders.

;~ https://www.autoitscript.com/forum/topic/180850-adoau3-udf-beta-support-topic/?do=findComment&comment=1488346

#AutoIt3Wrapper_Run_AU3Check=Y
#AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6 -w 7

; I use AutoIt 3.3.15.4 (Beta version) because:
;   ConsoleWrite() does not change the values of @error and @extended,
;       which is beneficial in the case of easy code management and tracking @errors.
; In other way I need to use my _Log() from Errorlog.au3 UDF
; or use other techniques to properly manage @error with throwing them to console with full tracking events
#AutoIt3Wrapper_AutoIt3Dir="z:\AutoItPortable\AutoIt_3_3_15_4_beta"

#include <FileConstants.au3>
#include <MsgBoxConstants.au3>
#include "ADO.au3"

_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler_Function)

_Main()
Exit

Func _main()
    Local $oConnection = _MSSQL_CreateExampleConnection()
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    _Example($oConnection)

    _ADO_Connection_Close($oConnection)
    $oConnection = Null
EndFunc   ;==>_main

Func _Example(ByRef $oConnection)
    _MSSQL_CreateDatabaseWithNameAndPath($oConnection)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    _MSSQL_CreateBlobTable($oConnection)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    _BLOB_Example($oConnection)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : Error occured in _BLOB_Example()" & @CRLF)

EndFunc   ;==>_Example


Func _BLOB_Example(ByRef $oConnection)
    ; read whole file in binary
    Local $hFileOpened = FileOpen(@ScriptDir & "\SomePdf.pdf", $FO_BINARY)
    Local $d_FileData_ToStore = FileRead($hFileOpened)
    FileClose($hFileOpened)

    _MSSQL_InsertFileBinary($oConnection, $d_FileData_ToStore, 'Test_db', 'Information: SomePdf.pdf')
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    Local $d_FileData_Restored
    _MSSQL_ReadFileBinary($oConnection, $d_FileData_Restored, "Test_db")
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; write binary content to new file
    Local $hFileSave = FileOpen(@ScriptDir & "\SomePdfNewCopy.pdf", $FO_BINARY + $FO_OVERWRITE + $FO_CREATEPATH)
    FileWrite($hFileSave, $d_FileData_Restored)
    FileClose($hFileSave)

    ; final check - open file in associated program
    If $IDYES = MsgBox($MB_YESNO + $MB_TOPMOST + $MB_ICONQUESTION + $MB_DEFBUTTON1, "Question", _
            "Do you wan try to open saved file ?") Then
        ShellExecute("SomePdfNewCopy.pdf")
    EndIf

EndFunc   ;==>_BLOB_Example

Func _MSSQL_CreateDatabaseWithNameAndPath(ByRef $oConnection, $NewDbName = "Test_db", $DirectoryPath = @ScriptDir)
    ; parameters validation
    If $NewDbName = "" Then Return SetError(-$ADO_ERR_INVALIDPARAMETERVALUE, -$ADO_EXT_PARAM1, $ADO_RET_FAILURE)
    If $DirectoryPath = "" Then Return SetError(-$ADO_ERR_INVALIDPARAMETERVALUE, -$ADO_EXT_PARAM2, $ADO_RET_FAILURE)

    ; note *** directories must exist first, use DirGetSize() to ensure the path exists then if not create with DirCreate()

    ; check if database exists already
    Local $s_SQLQuery_Check_DB_Exist = _
            "SELECT" & @CRLF & _
            "   [name]" & @CRLF & _
            "FROM" & @CRLF & _
            "   [master].dbo.[sysdatabases]" & @CRLF & _
            "WHERE" & @CRLF & _
            "   [name]='" & $NewDbName & "'" & @CRLF & _
            ""
    Local $oRecordset_Final = _ADO_Execute($oConnection, $s_SQLQuery_Check_DB_Exist, False)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; checking if is any result
    Local $aRowsResult = _ADO_Recordset_ToArray($oRecordset_Final)
    #forceref $aRowsResult
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If Not @error Then
        If Not @Compiled Then ConsoleWrite("database exists already ! No need to create them" & @CRLF)
        Return
    ElseIf @error < $ADO_ERR_RECORDSETEMPTY Then
        Return SetError(@error, @extended, $ADO_RET_FAILURE)
    Else
        If Not @Compiled Then ConsoleWrite("database not exist - will be created" & @CRLF)
    EndIf

    #Region - this part is not needed
    #cs
        Local $aRecordsetContent = _ADO_RecordsetArray_GetContent($aRowsResult)
        If @error And Not @compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
                " : " & @CRLF)
        If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

        $CreateNew = Not ($aRecordsetContent[0][0] == $NewDbName ) ; If already exists then do not $CreateNew ... $CreateNew = False
    #ce
    #EndRegion - this part is not needed

    Local $commandstr = _
            "CREATE DATABASE" & @CRLF & _
            "   " & $NewDbName & @CRLF & _
            "ON PRIMARY" & @CRLF & _
            "   (" & @CRLF & _
            "       NAME=" & $NewDbName & @CRLF & _
            "       ,FILENAME='" & $DirectoryPath & "\" & $NewDbName & ".mdf'" & @CRLF & _
            "       ,SIZE=5MB" & @CRLF & _
            "       ,MAXSIZE=100MB" & @CRLF & _
            "   ) " & @CRLF & _
            "LOG ON " & @CRLF & _
            "   (" & @CRLF & _
            "       NAME=" & $NewDbName & "_log" & @CRLF & _
            "       ,FILENAME='" & $DirectoryPath & "\" & $NewDbName & "_log.ldf'" & @CRLF & _
            "       ,SIZE=5MB" & @CRLF & _
            "       ,MAXSIZE=100MB" & @CRLF & _
            "   )"
    Local $oCommand = _ADO_Command_Create($oConnection)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    Local $Result = _ADO_Command_Execute($oCommand, $commandstr)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)
    #forceref $Result

    If Not @Compiled Then ConsoleWrite("succesfully created database !" & @CRLF)

    ; Clean Up
    $oRecordset_Final = Null
EndFunc   ;==>_MSSQL_CreateDatabaseWithNameAndPath

Func _MSSQL_CreateBlobTable(ByRef $oConnection, $DbName = "Test_db")

    ; check if table already exist
    Local $oRecordset_Tables = _ADO_OpenSchema_Tables($oConnection, $DbName, Default, "FILE_BIN_DATA")
    If @error >= $ADO_ERR_RECORDSETEMPTY Then
        ; if table not exist create them
        Local $s_SQLQuery_CreateTable = _
                "CREATE TABLE" & @CRLF & _
                "   [" & $DbName & "].dbo.[FILE_BIN_DATA]" & @CRLF & _
                "   (" & @CRLF & _
                "       ROWID int IDENTITY(1,1) NOT NULL" & @CRLF & _
                "       ,File_Name Varchar(60) NOT NULL" & @CRLF & _
                "       ,File_Data Varbinary(max) NULL" & @CRLF & _
                "       ,CONSTRAINT [PK_FILE_BIN_DATA] PRIMARY KEY CLUSTERED " & @CRLF & _
                "           (ROWID ASC)" & @CRLF & _
                "   ) " & @CRLF & _
                "ON [PRIMARY]"

        _ADO_Execute($oConnection, $s_SQLQuery_CreateTable)
        If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
                " : " & @CRLF)
        If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

        ; get again info about table
        $oRecordset_Tables = _ADO_OpenSchema_Tables($oConnection, $DbName, Default, "FILE_BIN_DATA")
    EndIf

    ; show info about the table
    If Not @Compiled Then ConsoleWrite(_ADO_Recordset_ToString($oRecordset_Tables))

    ; Clean Up
    $oRecordset_Tables = Null
EndFunc   ;==>_MSSQL_CreateBlobTable

Func _MSSQL_InsertFileBinary(ByRef $oConnection, ByRef $dFileBinaryIn, $DbName = "Test_db", $s_File_Name ='')
    Local $s_SQLQuery_InsertBinaryContent = _
            "INSERT INTO" & @CRLF & _
            "   [" & $DbName & "].dbo.[FILE_BIN_DATA]" & @CRLF & _
            "       (" & @CRLF & _
            "           File_Name" & @CRLF & _
            "           ,File_Data" & @CRLF & _
            "       )" & @CRLF & _
            "OUTPUT" & @CRLF & _
            "   inserted.[ROWID]" & @CRLF & _
            "VALUES(" & @CRLF & _
            "           '" & $s_File_Name & "'" & @CRLF & _
            "           ," & $dFileBinaryIn & @CRLF & _
            "       )" & @CRLF & _
            ""
    # !!! WARNING !!! this following line has an issue - the inserted data are inserted 3 times !!!
;~  Local $aRowsResult = _ADO_Execute($oConnection, $s_SQLQuery_InsertBinaryContent, True)
    # BECAUSE OF THIS ISSUE:   !!!!  use $bReturnAsArray = False .... like in following line
    Local $oRecordset = _ADO_Execute($oConnection, $s_SQLQuery_InsertBinaryContent, False)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    #forceref $oRecordset

    ; .... you can get new ROWID from :   OUTPUT inserted.[ROWID]
    ; ....
EndFunc   ;==>_MSSQL_InsertFileBinary

Func _MSSQL_ReadFileBinary(ByRef $oConnection, ByRef $dFileBinaryOut, $DbName = "Test_db")
    Local $s_SQLQuery_GetBinaryContent = _
            "SELECT" & @CRLF & _
            "   TOP 1 [File_Data]" & @CRLF & _
            "FROM" & @CRLF & _
            "   [" & $DbName & "].dbo.[FILE_BIN_DATA]" & @CRLF & _
            ""
    Local $aRowsResult = _ADO_Execute($oConnection, $s_SQLQuery_GetBinaryContent, True)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)
    Local $aRecordsetContent = _ADO_RecordsetArray_GetContent($aRowsResult)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    $dFileBinaryOut = $aRecordsetContent[0][0]

EndFunc   ;==>_MSSQL_ReadFileBinary

Func _MSSQL_CreateExampleConnection($Server = "localhost\SQLEXPRESS", $User = "sa", $Pwd = "")
    If $Pwd = "" Then $Pwd = "AutoIt" ; this is my temp passwd for this example project

    ; Create connection and open with $sConnectionString
    Local $oConnection = _ADO_Connection_Create()
    Local $sConnectionString = 'DRIVER={SQL Server};SERVER=' & $Server & ';DATABASE=master;UID=' & $User & ';PWD=' & $Pwd & ';'
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    Return $oConnection
EndFunc   ;==>_MSSQL_CreateExampleConnection

 

Feel warned about:

# !!! WARNING !!! this following line has an issue - the inserted data are inserted 3 times !!!
;~  Local $aRowsResult = _ADO_Execute($oConnection, $s_SQLQuery_InsertBinaryContent, True)
    # BECAUSE OF THIS ISSUE:   !!!!  use $bReturnAsArray = False .... like in following line
    Local $oRecordset = _ADO_Execute($oConnection, $s_SQLQuery_InsertBinaryContent, False)

 

image.png.aadde58c9ff61a9a689f95b16c00ba47.png

 

This is a bug that I have noticed before, so far I don't know why it occurs.

I will gladly use any help in solving it.

 

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

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 *

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

"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: 2021-10-17

Link to post
Share on other sites
6 hours ago, mLipok said:

I hope that the following quite complementary example will prove to be helpful not only for you, but also for other's AutoIt coders.

;~ https://www.autoitscript.com/forum/topic/180850-adoau3-udf-beta-support-topic/?do=findComment&comment=1488346

#AutoIt3Wrapper_Run_AU3Check=Y
#AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6 -w 7

; I use AutoIt 3.3.15.4 (Beta version) because:
;   ConsoleWrite() does not change the values of @error and @extended,
;       which is beneficial in the case of easy code management and tracking @errors.
; In other way I need to use my _Log() from Errorlog.au3 UDF
; or use other techniques to properly manage @error with throwing them to console with full tracking events
#AutoIt3Wrapper_AutoIt3Dir="z:\AutoItPortable\AutoIt_3_3_15_4_beta"

#include <FileConstants.au3>
#include <MsgBoxConstants.au3>
#include "ADO.au3"

_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler_Function)

_Main()
Exit

Func _main()
    Local $oConnection = _MSSQL_CreateExampleConnection()
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    _Example($oConnection)

    _ADO_Connection_Close($oConnection)
    $oConnection = Null
EndFunc   ;==>_main

Func _Example(ByRef $oConnection)
    _MSSQL_CreateDatabaseWithNameAndPath($oConnection)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    _MSSQL_CreateBlobTable($oConnection)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    _BLOB_Example($oConnection)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : Error occured in _BLOB_Example()" & @CRLF)

EndFunc   ;==>_Example


Func _BLOB_Example(ByRef $oConnection)
    ; read whole file in binary
    Local $hFileOpened = FileOpen(@ScriptDir & "\SomePdf.pdf", $FO_BINARY)
    Local $d_FileData_ToStore = FileRead($hFileOpened)
    FileClose($hFileOpened)

    _MSSQL_InsertFileBinary($oConnection, $d_FileData_ToStore, 'Test_db', 'Information: SomePdf.pdf')
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    Local $d_FileData_Restored
    _MSSQL_ReadFileBinary($oConnection, $d_FileData_Restored, "Test_db")
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; write binary content to new file
    Local $hFileSave = FileOpen(@ScriptDir & "\SomePdfNewCopy.pdf", $FO_BINARY + $FO_OVERWRITE + $FO_CREATEPATH)
    FileWrite($hFileSave, $d_FileData_Restored)
    FileClose($hFileSave)

    ; final check - open file in associated program
    If $IDYES = MsgBox($MB_YESNO + $MB_TOPMOST + $MB_ICONQUESTION + $MB_DEFBUTTON1, "Question", _
            "Do you wan try to open saved file ?") Then
        ShellExecute("SomePdfNewCopy.pdf")
    EndIf

EndFunc   ;==>_BLOB_Example

Func _MSSQL_CreateDatabaseWithNameAndPath(ByRef $oConnection, $NewDbName = "Test_db", $DirectoryPath = @ScriptDir)
    ; parameters validation
    If $NewDbName = "" Then Return SetError(-$ADO_ERR_INVALIDPARAMETERVALUE, -$ADO_EXT_PARAM1, $ADO_RET_FAILURE)
    If $DirectoryPath = "" Then Return SetError(-$ADO_ERR_INVALIDPARAMETERVALUE, -$ADO_EXT_PARAM2, $ADO_RET_FAILURE)

    ; note *** directories must exist first, use DirGetSize() to ensure the path exists then if not create with DirCreate()

    ; check if database exists already
    Local $s_SQLQuery_Check_DB_Exist = _
            "SELECT" & @CRLF & _
            "   [name]" & @CRLF & _
            "FROM" & @CRLF & _
            "   [master].dbo.[sysdatabases]" & @CRLF & _
            "WHERE" & @CRLF & _
            "   [name]='" & $NewDbName & "'" & @CRLF & _
            ""
    Local $oRecordset_Final = _ADO_Execute($oConnection, $s_SQLQuery_Check_DB_Exist, False)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; checking if is any result
    Local $aRowsResult = _ADO_Recordset_ToArray($oRecordset_Final)
    #forceref $aRowsResult
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If Not @error Then
        If Not @Compiled Then ConsoleWrite("database exists already ! No need to create them" & @CRLF)
        Return
    ElseIf @error < $ADO_ERR_RECORDSETEMPTY Then
        Return SetError(@error, @extended, $ADO_RET_FAILURE)
    Else
        If Not @Compiled Then ConsoleWrite("database not exist - will be created" & @CRLF)
    EndIf

    #Region - this part is not needed
    #cs
        Local $aRecordsetContent = _ADO_RecordsetArray_GetContent($aRowsResult)
        If @error And Not @compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
                " : " & @CRLF)
        If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

        $CreateNew = Not ($aRecordsetContent[0][0] == $NewDbName ) ; If already exists then do not $CreateNew ... $CreateNew = False
    #ce
    #EndRegion - this part is not needed

    Local $commandstr = _
            "CREATE DATABASE" & @CRLF & _
            "   " & $NewDbName & @CRLF & _
            "ON PRIMARY" & @CRLF & _
            "   (" & @CRLF & _
            "       NAME=" & $NewDbName & @CRLF & _
            "       ,FILENAME='" & $DirectoryPath & "\" & $NewDbName & ".mdf'" & @CRLF & _
            "       ,SIZE=5MB" & @CRLF & _
            "       ,MAXSIZE=100MB" & @CRLF & _
            "   ) " & @CRLF & _
            "LOG ON " & @CRLF & _
            "   (" & @CRLF & _
            "       NAME=" & $NewDbName & "_log" & @CRLF & _
            "       ,FILENAME='" & $DirectoryPath & "\" & $NewDbName & "_log.ldf'" & @CRLF & _
            "       ,SIZE=5MB" & @CRLF & _
            "       ,MAXSIZE=100MB" & @CRLF & _
            "   )"
    Local $oCommand = _ADO_Command_Create($oConnection)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    Local $Result = _ADO_Command_Execute($oCommand, $commandstr)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)
    #forceref $Result

    If Not @Compiled Then ConsoleWrite("succesfully created database !" & @CRLF)

    ; Clean Up
    $oRecordset_Final = Null
EndFunc   ;==>_MSSQL_CreateDatabaseWithNameAndPath

Func _MSSQL_CreateBlobTable(ByRef $oConnection, $DbName = "Test_db")

    ; check if table already exist
    Local $oRecordset_Tables = _ADO_OpenSchema_Tables($oConnection, $DbName, Default, "FILE_BIN_DATA")
    If @error >= $ADO_ERR_RECORDSETEMPTY Then
        ; if table not exist create them
        Local $s_SQLQuery_CreateTable = _
                "CREATE TABLE" & @CRLF & _
                "   [" & $DbName & "].dbo.[FILE_BIN_DATA]" & @CRLF & _
                "   (" & @CRLF & _
                "       ROWID int IDENTITY(1,1) NOT NULL" & @CRLF & _
                "       ,File_Name Varchar(60) NOT NULL" & @CRLF & _
                "       ,File_Data Varbinary(max) NULL" & @CRLF & _
                "       ,CONSTRAINT [PK_FILE_BIN_DATA] PRIMARY KEY CLUSTERED " & @CRLF & _
                "           (ROWID ASC)" & @CRLF & _
                "   ) " & @CRLF & _
                "ON [PRIMARY]"

        _ADO_Execute($oConnection, $s_SQLQuery_CreateTable)
        If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
                " : " & @CRLF)
        If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

        ; get again info about table
        $oRecordset_Tables = _ADO_OpenSchema_Tables($oConnection, $DbName, Default, "FILE_BIN_DATA")
    EndIf

    ; show info about the table
    If Not @Compiled Then ConsoleWrite(_ADO_Recordset_ToString($oRecordset_Tables))

    ; Clean Up
    $oRecordset_Tables = Null
EndFunc   ;==>_MSSQL_CreateBlobTable

Func _MSSQL_InsertFileBinary(ByRef $oConnection, ByRef $dFileBinaryIn, $DbName = "Test_db", $s_File_Name ='')
    Local $s_SQLQuery_InsertBinaryContent = _
            "INSERT INTO" & @CRLF & _
            "   [" & $DbName & "].dbo.[FILE_BIN_DATA]" & @CRLF & _
            "       (" & @CRLF & _
            "           File_Name" & @CRLF & _
            "           ,File_Data" & @CRLF & _
            "       )" & @CRLF & _
            "OUTPUT" & @CRLF & _
            "   inserted.[ROWID]" & @CRLF & _
            "VALUES(" & @CRLF & _
            "           '" & $s_File_Name & "'" & @CRLF & _
            "           ," & $dFileBinaryIn & @CRLF & _
            "       )" & @CRLF & _
            ""
    # !!! WARNING !!! this following line has an issue - the inserted data are inserted 3 times !!!
;~  Local $aRowsResult = _ADO_Execute($oConnection, $s_SQLQuery_InsertBinaryContent, True)
    # BECAUSE OF THIS ISSUE:   !!!!  use $bReturnAsArray = False .... like in following line
    Local $oRecordset = _ADO_Execute($oConnection, $s_SQLQuery_InsertBinaryContent, False)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    #forceref $oRecordset

    ; .... you can get new ROWID from :   OUTPUT inserted.[ROWID]
    ; ....
EndFunc   ;==>_MSSQL_InsertFileBinary

Func _MSSQL_ReadFileBinary(ByRef $oConnection, ByRef $dFileBinaryOut, $DbName = "Test_db")
    Local $s_SQLQuery_GetBinaryContent = _
            "SELECT" & @CRLF & _
            "   TOP 1 [File_Data]" & @CRLF & _
            "FROM" & @CRLF & _
            "   [" & $DbName & "].dbo.[FILE_BIN_DATA]" & @CRLF & _
            ""
    Local $aRowsResult = _ADO_Execute($oConnection, $s_SQLQuery_GetBinaryContent, True)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)
    Local $aRecordsetContent = _ADO_RecordsetArray_GetContent($aRowsResult)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    $dFileBinaryOut = $aRecordsetContent[0][0]

EndFunc   ;==>_MSSQL_ReadFileBinary

Func _MSSQL_CreateExampleConnection($Server = "localhost\SQLEXPRESS", $User = "sa", $Pwd = "")
    If $Pwd = "" Then $Pwd = "AutoIt" ; this is my temp passwd for this example project

    ; Create connection and open with $sConnectionString
    Local $oConnection = _ADO_Connection_Create()
    Local $sConnectionString = 'DRIVER={SQL Server};SERVER=' & $Server & ';DATABASE=master;UID=' & $User & ';PWD=' & $Pwd & ';'
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    Return $oConnection
EndFunc   ;==>_MSSQL_CreateExampleConnection

 

Feel warned about:

# !!! WARNING !!! this following line has an issue - the inserted data are inserted 3 times !!!
;~  Local $aRowsResult = _ADO_Execute($oConnection, $s_SQLQuery_InsertBinaryContent, True)
    # BECAUSE OF THIS ISSUE:   !!!!  use $bReturnAsArray = False .... like in following line
    Local $oRecordset = _ADO_Execute($oConnection, $s_SQLQuery_InsertBinaryContent, False)

 

image.png.aadde58c9ff61a9a689f95b16c00ba47.png

 

This is a bug that I have noticed before, so far I don't know why it occurs.

I will gladly use any help in solving it.

 

Thanks mLipok I will try this soon. Hopefully my original example worked well enough to start with I wrote it at 4:30am without any sleep before posting, looks like your modal db is bigger than mine, maybe this is mssql engine version? i am using mssql 2019? 2mb works for me but looks like you need 5mb.

 

For the above mentioned warning, without yet testing my best guess is that the recordset object Isn't cleaned up in the _ADO_Execute function when you use $bReturnAsArray = True and can't be cleaned up later because the object handle Isn't returned, instead the array is. I had noticed this in the UDF function so I avoided using it this way in my scripts because then I can always clean up the object. I thought it might be a risk of the recordset object being left open and ado perhaps returning the handle to the same object the next time which then maybe increments the size of the original recordset object and adds further field objects to that original. Rather than working out how to test this theory I just chose to always use $bReturnAsArray = false in my scripts, maybe you can try adding the clean up of the recordset object to the _ADO_Execute function if $bReturnAsArray is true before returning with the array and see if the problem persists?

 

Thanks

 

 

Link to post
Share on other sites
13 hours ago, Lecdev said:

For the above mentioned warning, without yet testing my best guess is that the recordset object Isn't cleaned up in the _ADO_Execute function when you use $bReturnAsArray = True and can't be cleaned up later because the object handle Isn't returned, instead the array is. I had noticed this in the UDF function so I avoided using it this way in my scripts because then I can always clean up the object. I thought it might be a risk of the recordset object being left open and ado perhaps returning the handle to the same object the next time which then maybe increments the size of the original recordset object and adds further field objects to that original. Rather than working out how to test this theory I just chose to always use $bReturnAsArray = false in my scripts, maybe you can try adding the clean up of the recordset object to the _ADO_Execute function if $bReturnAsArray is true before returning with the array and see if the problem persists?

Thanks for yours opinion.

I just made some investigation.... in function:

Func _ADO_Recordset_ToArray(ByRef $oRecordset, $bFieldNamesInFirstRow = False)
    ..........
    ..........
    ..........
    $oRecordset.moveFirst()
    ..........
    ..........
    ..........
    $oRecordset.moveFirst()
    ..........
    ..........
    ..........
EndFunc


The first moveFirst() adds new record (the second one, I mean first duplicate).

The second moveFirst() adds the next new record (the third one, I mean second duplicate).

 

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

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 *

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

"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: 2021-10-17

Link to post
Share on other sites
Posted (edited)

Currently _ADO_Recordset_ToArray() looks like the following code:

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

    ; save current Recordset rows position to $oRecordset_Bookmark
    Local $oRecordset_Bookmark = Null
    If $oRecordset.Supports($ADO_adBookmark) Then $oRecordset_Bookmark = $oRecordset.Bookmark

    $oRecordset.moveFirst()
    Local $aRecordset_GetRowsResult = $oRecordset.GetRows()
    If @error Then Return SetError($ADO_ERR_COMERROR, @error, $ADO_RET_FAILURE) ; Trap COM error, report and return

    If UBound($aRecordset_GetRowsResult) Then
        Local $aResult[0]

        ; Restore Recordset row position from stored $oRecordset_Bookmark
        If $oRecordset_Bookmark = Null Then
            $oRecordset.moveFirst()
        Else
            $oRecordset.Bookmark = $oRecordset_Bookmark
        EndIf

        Local $iColumns_count = UBound($aRecordset_GetRowsResult, $UBOUND_COLUMNS)
        Local $iRows_count = UBound($aRecordset_GetRowsResult)

        If $bFieldNamesInFirstRow Then
            ; Adjust the array to fit the column names and move all data down 1 row
            ReDim $aRecordset_GetRowsResult[$iRows_count + 1][$iColumns_count]

            ; Move all records down
            For $iRow_idx = $iRows_count To 1 Step -1
                For $y = 0 To $iColumns_count - 1
                    $aRecordset_GetRowsResult[$iRow_idx][$y] = $aRecordset_GetRowsResult[$iRow_idx - 1][$y]
                Next
            Next

            ; Add the coloumn names
            For $iCol_idx = 0 To $iColumns_count - 1 ; get the column names and put into 0 array element
                $aRecordset_GetRowsResult[0][$iCol_idx] = $oRecordset.Fields($iCol_idx).Name
            Next
            $aResult = $aRecordset_GetRowsResult
            Return SetError($ADO_ERR_SUCCESS, $iRows_count + 1, $aResult)
        EndIf

        ReDim $aResult[$ADO_RS_ARRAY_ENUMCOUNTR]
        Local $aFiledNames_Temp[$iColumns_count]

        For $iCol_idx = 0 To $iColumns_count - 1 ; get the column names and put into 0 array element
            $aFiledNames_Temp[$iCol_idx] = $oRecordset.Fields($iCol_idx).Name
        Next
        $aResult[$ADO_RS_ARRAY_GUID] = $ADO_RS_GUID
        $aResult[$ADO_RS_ARRAY_FIELDNAMES] = $aFiledNames_Temp
        $aResult[$ADO_RS_ARRAY_RSCONTENT] = $aRecordset_GetRowsResult
        Return SetError($ADO_ERR_SUCCESS, $iRows_count, $aResult)

    EndIf

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

 

now I'm thinking about changing this function to such:

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

    ; save current Recordset rows position to $oRecordset_Bookmark
    Local $oRecordset_Bookmark = Null
    If $oRecordset.Supports($ADO_adBookmark) Then $oRecordset_Bookmark = $oRecordset.Bookmark

;~  $oRecordset.moveFirst()
    Local $aRecordset_GetRowsResult = $oRecordset.GetRows()
;~  Local $aRecordset_GetRowsResult = $oRecordset.GetRows($ADO_adGetRowsRest, $ADO_adBookmarkFirst)
    If @error Then Return SetError($ADO_ERR_COMERROR, @error, $ADO_RET_FAILURE) ; Trap COM error, report and return

    If UBound($aRecordset_GetRowsResult) Then
        Local $aResult[0]

        ; Restore Recordset row position from stored $oRecordset_Bookmark
        If $oRecordset_Bookmark = Null Then
;~          $oRecordset.moveFirst()
        Else
            $oRecordset.Bookmark = $oRecordset_Bookmark
        EndIf

        Local $iColumns_count = UBound($aRecordset_GetRowsResult, $UBOUND_COLUMNS)
        Local $iRows_count = UBound($aRecordset_GetRowsResult)

        If $bFieldNamesInFirstRow Then
            ; Adjust the array to fit the column names and move all data down 1 row
            ReDim $aRecordset_GetRowsResult[$iRows_count + 1][$iColumns_count]

            ; Move all records down
            For $iRow_idx = $iRows_count To 1 Step -1
                For $y = 0 To $iColumns_count - 1
                    $aRecordset_GetRowsResult[$iRow_idx][$y] = $aRecordset_GetRowsResult[$iRow_idx - 1][$y]
                Next
            Next

            ; Add the coloumn names
            For $iCol_idx = 0 To $iColumns_count - 1 ; get the column names and put into 0 array element
                $aRecordset_GetRowsResult[0][$iCol_idx] = $oRecordset.Fields($iCol_idx).Name
            Next
            $aResult = $aRecordset_GetRowsResult
            Return SetError($ADO_ERR_SUCCESS, $iRows_count + 1, $aResult)
        EndIf

        ReDim $aResult[$ADO_RS_ARRAY_ENUMCOUNTR]
        Local $aFiledNames_Temp[$iColumns_count]

        For $iCol_idx = 0 To $iColumns_count - 1 ; get the column names and put into 0 array element
            $aFiledNames_Temp[$iCol_idx] = $oRecordset.Fields($iCol_idx).Name
        Next
        $aResult[$ADO_RS_ARRAY_GUID] = $ADO_RS_GUID
        $aResult[$ADO_RS_ARRAY_FIELDNAMES] = $aFiledNames_Temp
        $aResult[$ADO_RS_ARRAY_RSCONTENT] = $aRecordset_GetRowsResult
        Return SetError($ADO_ERR_SUCCESS, $iRows_count, $aResult)

    EndIf

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

 

The main differences are here:

Func _ADO_Recordset_ToArray(ByRef $oRecordset, $bFieldNamesInFirstRow = False)
    ....
    ....
    ....
;~  $oRecordset.moveFirst()
    Local $aRecordset_GetRowsResult = $oRecordset.GetRows()
;~  Local $aRecordset_GetRowsResult = $oRecordset.GetRows($ADO_adGetRowsRest, $ADO_adBookmarkFirst)
    ....
    ....
        If $oRecordset_Bookmark = Null Then
;~          $oRecordset.moveFirst()
        Else
            $oRecordset.Bookmark = $oRecordset_Bookmark
        EndIf
    ....
    ....
    ....
EndFunc   ;==>_ADO_Recordset_ToArray

The main purposes is to use:

Quote
adBookmarkFirst 1 Starts at the first record.

instead:

Quote
adBookmarkCurrent 0 Starts at the current record.

references:
https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/bookmarkenum?view=sql-server-ver15

 

Please feel free to express your views on this.

Edited by mLipok
proposals modified

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

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 *

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

"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: 2021-10-17

Link to post
Share on other sites

 

 

I'm not so sure that removing the MoveFirst method support is correct, it shouldn't be adding records, it is supposed to throw an error if there is no data hence needing to check BOF and EOF first and shouldn't do anything more than set the internal pointer to the first already existing record in the recordset if it does contain a record. There is a truth table on msdn somewhere to show the results of the move methods in different circumstances I will try to find it.

 

also in some deeper research on recordset clean up I have found these notes mentioned in msdn;

"

If You Explicitly Open It, Explicitly Close It:
Pooling problems are frequently caused by an application that does not clean up its connections. Connections are placed in the pool at the time that the connection is closed and not before. To avoid this, always explicitly close an object you open. If you don't explicitly close it, chances are it won't ever be released to the pool.

Even if the language you use has effective and reliable garbage collection, an instance of an open ADO Connection or Recordset object going out of scope does not equate to the Close method of that object being implicitly invoked. You must close it explicitly.

Failing to close an open Connection or Recordset object is probably the single most frequent cause of connection creep and the single largest cause of incorrect diagnoses of pooling failure.

"

and details here:

https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/close-method-ado?view=sql-server-ver15

suggesting that if you are leaving open the connection and re-using you should use the close method on the recordset before nulling the object var and also that closing the connection object doesn't close the command objects either, it does however close a recordset but it is common to leave connections open throughout several queries.

 

it may be necessary to add the following for certain situations;

Func _ADO_Recordset_Close(ByRef $oRecordset)
    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_Recordset_IsValid($oRecordset)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    $oRecordset.Close
    Return SetError($ADO_ERR_SUCCESS, $ADO_EXT_DEFAULT, $ADO_RET_SUCCESS)
EndFunc   ;==>_ADO_Recordset_Close

Func _ADO_Command_Close(ByRef $oCommand)
    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_Command_IsValid($oCommand)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    $oCommand.Close
    Return SetError($ADO_ERR_SUCCESS, $ADO_EXT_DEFAULT, $ADO_RET_SUCCESS)
EndFunc   ;==>_ADO_Command_Close

 

I also found that the execute method always returns a recordset with a forward only cursor so moveFirst likely doesn't work in the end. instead to do this you have to open your own recordset. https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/execute-method-ado-connection?view=sql-server-ver15#remarks

You could use recordset.supports(adMovePrevious) to check if the recordset is forward only or check the recordset.CurserType property which may be able to change the type but not sure if this works on a read-only Recordset once already opened.

 

 

Link to post
Share on other sites

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
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By dsm-sas
      Hi,
      I am looking for the possibility to extract a xls (not xlsx)-Excelfile to a csv . My problem: I do NOT have any Appliaction from MS-OFFICE. All searchings (since 3 days now) allways wants me to install ms-office or excel.
      I CAN'T DO THIS !!!
      Any help?!
      Ths'x alot
    • By Arual
      We have AutoIt code that performs a connection to an MS SQL database running SQL server 2012, tls 1.0. It has worked successfully for years.
      Today, our IT department migrated our database to a new server that is running SQL server 2016, tls 1.2. Now our connection string is no longer working.
      Here is the original that was working in SQL server 2012
      $sConnStr = "DRIVER={SQL Server};SERVER=servername,port;DATABASE=dbname;UID=user;PWD=pass" When that would run on the new server in SQL server 2016. We are getting the error [Microsoft][ODBC SQL Server Driver][DBNETLIB]SSL Security error
      We tried changing the connection string to 
      $sConnStr = "DRIVER={SQL Server Native Client 11.0};SERVER=servername,port;DATABASE=dbname;UID=user;PWD=pass" The new error we received is [Microsoft]
      TCP Provider: An existing connection was forcibly closed by the remote host. We can log in successfully using SSMS using the server name, port and user/pass.
      Any suggestions on what we need to change in order to get the connection to work again?
    • By mLipok
      In the past there was many questions about how to: "Automatic file upload using without user interaction"

      https://www.autoitscript.com/forum/topic/92907-ie-8-input-namenomfic-typefile-idnomfic/
      https://www.autoitscript.com/forum/topic/116899-cant-automate-input-typefile-tag-in-ie/?tab=comments#comment-815478
      https://www.autoitscript.com/forum/topic/14883-input-typefile/
      https://www.autoitscript.com/forum/topic/188708-how-to-set-the-value-of-an-input-typefile-element/
      https://www.autoitscript.com/forum/topic/91513-how-can-i-auto-set-file-path-for-input-file-in-ie/
      https://www.autoitscript.com/forum/topic/116899-cant-automate-input-typefile-tag-in-ie/
      https://www.autoitscript.com/forum/topic/169190-how-to-script-file-upload-button/
      https://www.autoitscript.com/forum/topic/145327-how-to-deal-with-ie-window-for-upload-a-fileinput-typefile/
      https://www.autoitscript.com/forum/topic/140482-internet-explorer-input-file-problem/
       
      I found solution here: 
      https://stackoverflow.com/questions/33253517/upload-a-file-via-input-input-in-html-form-with-vba
      and:
      https://www.motobit.com/tips/detpg_uploadvbsie/
      And I translate this code to AutoIt3 code:
      ; Upload file using http protocol And multipart/form-data ; v1.01 ; 2001 Antonin Foller, PSTRUH Software Global $oErrorHandler = ObjEvent("AutoIt.Error", _ErrFunc) do_vbsUpload() Func do_vbsUpload() #cs ; We need at least two arguments (File & URL) ConsoleWrite('- ' & @ScriptLineNumber & @CRLF) If $CmdLine[0] < 2 Then InfoEcho() ConsoleWrite('- ' & @ScriptLineNumber & @CRLF) ; Are some required objects missing? If StringInStr(CheckRequirements(), "Error") > 0 Then InfoEcho() ConsoleWrite('- ' & @ScriptLineNumber & @CRLF) Local $s_FileName, $s_DestURL, $s_FieldName $s_FieldName = "FileField" ; Default field name For $i_argCounter = 1 To $CmdLine[0] ConsoleWrite('+ '& $i_argCounter& ' >> ' & $CmdLine[$i_argCounter] & @CRLF) Select Case $i_argCounter = 1 ;~ $s_FileName = $CmdLine[$i_argCounter] $s_FileName = @ScriptFullPath Case $i_argCounter = 2 $s_DestURL = $CmdLine[$i_argCounter] Case $i_argCounter = 3 $s_FieldName = $CmdLine[$i_argCounter] EndSelect Next UploadFile($s_DestURL, $s_FileName, $s_FieldName) #ce UploadFile('http://www.dobeash.com/test.html', @ScriptFullPath, 'fileExample') EndFunc ;==>do_vbsUpload ; ******************* upload - begin ; Upload file using input type=file Func UploadFile($s_DestURL, $s_FileName, $s_FieldName) ; Boundary of fields. ; Be sure this string is Not In the source file Const $Boundary = "---------------------------0123456789012" ; Get source file As a binary data. Local $d_FileContents = GetFile($s_FileName) ; Build multipart/form-data document Local $s_FormData = BuildFormData($d_FileContents, $Boundary, $s_FileName, $s_FieldName) ; Post the data To the destination URL IEPostBinaryRequest($s_DestURL, $s_FormData, $Boundary) EndFunc ;==>UploadFile ; Build multipart/form-data document with file contents And header info Func BuildFormData($d_FileContents, $Boundary, $s_FileName, $s_FieldName) Const $s_ContentType = "application/upload" ; The two parts around file contents In the multipart-form data. Local $s_Pre = "--" & $Boundary & @CRLF & mpFields($s_FieldName, $s_FileName, $s_ContentType) Local $s_Po = @CRLF & "--" & $Boundary & "--" & @CRLF ; Build form data using recordset binary field Const $i_adLongVarBinary = 205 Local $oRS = ObjCreate("ADODB.Recordset") ; https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/append-method-ado?view=sql-server-ver15 $oRS.Fields.Append("b", $i_adLongVarBinary, StringLen($s_Pre) + BinaryLen($d_FileContents) + StringLen($s_Po)) $oRS.Open() $oRS.AddNew() ; Convert Pre string value To a binary data Local $i_LenData = StringLen($s_Pre) $oRS("b").AppendChunk(StringToMB($s_Pre) & StringToBinary(Chr(0))) $s_Pre = $oRS("b").GetChunk($i_LenData) $oRS("b") = "" ; Convert Po string value To a binary data $i_LenData = StringLen($s_Po) $oRS("b").AppendChunk(StringToMB($s_Po) & StringToBinary(Chr(0))) $s_Po = $oRS("b").GetChunk($i_LenData) $oRS("b") = "" ; Join Pre & $d_FileContents & Po binary data $oRS("b").AppendChunk($s_Pre) $oRS("b").AppendChunk($d_FileContents) $oRS("b").AppendChunk($s_Po) $oRS.Update() Local $s_FormData = $oRS("b") $oRS.Close() Return $s_FormData EndFunc ;==>BuildFormData ; sends multipart/form-data To the URL using IE Func IEPostBinaryRequest($s_URL, $s_FormData, $Boundary) ; Create InternetExplorer Local $oIE = ObjCreate("InternetExplorer.Application") ; You can uncoment Next line To see form results $oIE.Visible = True ; Send the form data To $s_URL As POST multipart/form-data request $oIE.Navigate($s_URL, '', '', $s_FormData, _ "Content-Type: multipart/form-data; boundary=" & $Boundary & @CRLF) While $oIE.Busy Wait(1, "Upload To " & $s_URL) WEnd ; Get a result of the script which has received upload ;~ On Error Resume Next Local $s_IE_InnerHTML = $oIE.Document.body.innerHTML MsgBox(0, 'TEST #' & @CRLF & @ScriptLineNumber, $s_IE_InnerHTML) $oIE.Quit() Return $s_IE_InnerHTML EndFunc ;==>IEPostBinaryRequest ; Infrormations In form field header. Func mpFields($s_FieldName, $s_FileName, $s_ContentType) Local $s_MPTemplate = _ ; template For multipart header 'Content-Disposition: form-data; name="{field}";' & _ 'FileName="{file}"' & @CRLF & _ 'Content-Type: {ct}' & @CRLF & @CRLF & _ '' Local $s_Out $s_Out = StringReplace($s_MPTemplate, "{field}", $s_FieldName) $s_Out = StringReplace($s_Out, "{file}", $s_FileName) $s_Out = StringReplace($s_Out, "{ct}", $s_ContentType) Return $s_Out EndFunc ;==>mpFields Func Wait($i_Seconds, $s_Message) MsgBox(64, '', $s_Message, $i_Seconds) EndFunc ;==>Wait ; Returns file contents As a binary data Func GetFile($s_FileName) Local $oStream = ObjCreate("ADODB.Stream") $oStream.Type = 1 ; Binary $oStream.Open() $oStream.LoadFromFile($s_FileName) Local $d_GetFile = $oStream.Read() $oStream.Close() Return $d_GetFile EndFunc ;==>GetFile ; Converts OLE string To multibyte string Func StringToMB($S) Local $I, $B For $I = 1 To StringLen($S) $B &= StringToBinary(Asc(StringMid($S, $I, 1))) Next Return $B EndFunc ;==>StringToMB ; ******************* upload - end ; ******************* Support ; Basic script info Func InfoEcho() Local $sMsg = _ "Upload file using http And multipart/form-data" & @CRLF & _ "Copyright (C) 2001 Antonin Foller, PSTRUH Software" & @CRLF & _ "use" & @CRLF & _ "[cscript|wscript] fupload.vbs file $s_URL [fieldname]" & @CRLF & _ " file ... Local file To upload" & @CRLF & _ " $s_URL ... $s_URL which can accept uploaded data" & @CRLF & _ " fieldname ... Name of the source form field." & @CRLF & _ @CRLF & CheckRequirements() & @CRLF & _ "" ConsoleWrite('! ' & $sMsg & @CRLF) EndFunc ;==>InfoEcho ; Checks If all of required objects are installed Func CheckRequirements() Local $sMsg = _ "This script requires some objects installed To run properly." & @CRLF & _ CheckOneObject("ADODB.Recordset") & @CRLF & _ CheckOneObject("ADODB.Stream") & @CRLF & _ CheckOneObject("InternetExplorer.Application") & @CRLF & _ "" Return $sMsg ; $sMsgBox $sMsg EndFunc ;==>CheckRequirements ; Checks If the one object is installed. Func CheckOneObject($sClassName) Local $sMsg ObjCreate($sClassName) If @error = 0 Then $sMsg = "OK" Else $sMsg = "Error:" & @error EndIf Return $sClassName & " - " & $sMsg EndFunc ;==>CheckOneObject ; ******************* Support - end ; User's COM error function. Will be called if COM error occurs Func _ErrFunc(ByRef $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  
      But I miss something and the code not works as intendend.
      Please join and contribute, in solving this issue, as this will be handy for entire community.
      @mLipok
       
      btw.
      I think that this may be realated to ChrB() which I simply translate to StringToBinary()
      Especialy this :
      StringToBinary(Chr(0))) could be the main issue.
      But for now I'm tired and going to sleep.
      Hope maybe tomorrow somebody solve this issue.
       
    • By robertocm
      An example of updating an excel file with a join between excel range and access tables.
      #include <Excel.au3> #include <Array.au3> #include <MsgBoxConstants.au3> ;#include <WinAPIFiles.au3> ;Permitir unha única instancia da aplicación #include <Misc.au3> ;_Singleton("ADO_Update_Excel_From_Access", 0) If _Singleton("ADO_Update_Excel_From_Access", 1) = 0 Then MsgBox($MB_SYSTEMMODAL, "Warning", "An occurrence of test is already running") Exit EndIf Opt("MustDeclareVars", 1) Opt("TrayIconDebug", 1) OnAutoItExitRegister("OnAutoItExit") #===== CONFIG ===== Global $sFilePath = @ScriptDir & "\test.xlsx" Global $sFilePath2 = @ScriptDir & "\test.mdb" Global $testmdb = "[;Database=" & $sFilePath2 & ";PWD=123]" ;~ Global $excel = "[Excel 12.0 Xml;HDR=NO;IMEX=1;DATABASE=" & $sFilePath& "]" ;Global $testsqlserver = "[odbc;Driver={SQL Server};SERVER=10.0.0.99;DATABASE=MyDatabaseName;UID=MyUser;PWD=MyPassword]" #===== ADODB ===== Global $cn, $rst, $sSQL, $SubSQL ;Help: COM Error Handling ;_ErrADODB From spudw2k ;https://www.autoitscript.com/forum/topic/105875-adodb-example/ Global $errADODB = ObjEvent("AutoIt.Error","_ErrADODB") Global Const $iCursorType = 3 ;0 adOpenForwardOnly, 3 adOpenStatic Global Const $iLockType = 3 ;1 adLockReadOnly, 3 adLockOptimistic Global Const $iOptions = 1 ; Options, 1 Evaluates as a textual definition of a command or stored procedure call ; 2 adCmdTable $cn = ObjCreate("ADODB.Connection") ; Create a connection object $rst = ObjCreate("ADODB.Recordset") ; Create a recordset object ;Global $sADOConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & $sFilePath2 & ";Jet OLEDB:Database Password=123" ;Global $sADOConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & $sFilePath2 & ";Jet OLEDB:Database Password=123" ;Global $sADOConnectionString = 'Driver={Microsoft Access Driver (*.mdb)};Dbq=' & $sFilePath2 & ';uid=;pwd=MyPassword;' ;~ ;Global $sADOConnectionString = 'Provider=SQLOLEDB;Data Source=10.0.0.99;Initial Catalog=MyDatabaseName;User Id=MyUser;Password=MyPassword;' ;~ ;Or if you’re using native client: ;~ ;stConnect = "Provider=SQLNCLI10;Data Source=... ;http://www.connectionstrings.com/ ;Xlsx files: Excel 2007 (and later) files with the Xlsx file extension ;[Also valid for] Using the Office 2007 OLEDB driver (ACE 12.0) to connect to older 97-2003 Excel workbooks ;cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & RutaXls & ";Extended Properties=Excel 12.0 Xml;" Global $sADOConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & $sFilePath & ";Extended Properties=""Excel 12.0 Xml;HDR=NO"";" ;Global $sADOConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & $sFilePath & ";Extended Properties=""Excel 12.0;HDR=NO;IMEX=1"";" ;Global $sADOConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & $sFilePath & ";Extended Properties=Excel 8.0;" ;Global $sADOConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & $sFilePath & ";ReadOnly=0;" ;https://www.w3schools.com/asp/prop_rs_cursorlocation.asp ;A Recordset object inherits this setting from the associated Connection object. ;This property is read-only on an open Recordset object, and read/write on a Connection object or on a closed Recordset object. $cn.CursorLocation = 2 ;2 adUseServer, 3 adUseClient $cn.CommandTimeout = 30 ;https://stackoverflow.com/questions/31941487/open-adodb-connection-to-excel-file-in-read-only-mode ;try Mode = adModeRead instead ;By the way, do not put adModeRead in the connections string, but just before openning your connection, add this line: rsConn.Mode = adModeRead ;I tried your suggestion, however since in VBA we do not have direct access to the ADODB built-in constants, I set rsCon.Mode = 1 ;as defined in the file adovbs.inc located in the folder "C:\Program Files\Common Files\System\ado" ;and although I watched the rsCon.Mode value being set to adModeRead while debugging, I still have the same problem and the application tries to access the file in Write/Edit mode. ;https://www.w3schools.com/asp/prop_rec_mode.asp ;$cn.Mode = 1 ;Read-only $cn.Open($sADOConnectionString) ; Open the connection ;MsgBox(0, "", $cn.ConnectionString) $sSQL = "UPDATE (([Sheet1$A2:C11] a" _ & " INNER JOIN " & $testmdb & ".[Order_Details] b ON a.F1 = b.ID)" _ & " INNER JOIN " & $testmdb & ".[Orders] c ON b.ID = c.ID)" _ & " INNER JOIN " & $testmdb & ".[Customers] d ON c.CustomerID = d.ID" _ & " SET a.F2 = c.OrderDate, a.F3 = d.CompanyName;" $cn.Execute($sSQL, Default, 1 + 0x80) ;adCmdText = 1 , adExecuteNoRecords = 0x80 $sSQL = "SELECT F2, F3, Sum(Quantity * UnitPrice) As Amount" _ & " FROM [Sheet1$A2:C11] AS a INNER JOIN " & $testmdb & ".[Order_Details] b ON a.F1 = b.ID" _ & " GROUP BY F2, F3" _ & " ORDER BY F2;" $rst.Open($sSQL, $cn, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query If Not $rst.EOF = True Then Local $rstArray = $rst.GetRows() _ArrayDisplay($rstArray, "Test", "", $ARRAYDISPLAY_NOROW, "", "F1|F2|F3") $rst.Close $rst = 0 ;Release the recordset object ;$cmd = 0 $cn.Close ;Close the connection $cn = 0 ;Release the connection object Global $RecCount = UBound($rstArray) #===== EXCEL ===== Global $oMyError = ObjEvent("AutoIt.Error", "ErrFunc") ;Install a custom error handler Global $iEventError ; to be checked to know if com error occurs. Must be reset after handling. ;_DebugSetup() ;_DebugCOMError() ;water: force the Excel UDF to always start up a new instance by using: _Excel_Open(False, Default, Default, Default, True) ;Global $oAppl = _Excel_Open(True, False, False, Default, True) Global $oAppl = _Excel_Open() ;_Excel_Open(Default, Default, False, Default, Default) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Open" & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;https://www.autoitscript.com/forum/topic/185789-solved-excel_bookopen-without-wait/?do=findComment&comment=1334509 ;Restaurar en cada arquivo (algún Application.Run pudo cambiar) ;$oAppl.EnableEvents = False $oAppl.DisplayAlerts = False ;~ ;Arquivo non bloqueado ;~ Global $iFileExists ;~ For $j = 0 To 60 ;~ $iFileExists = FileExists($sFilePath2) ;~ If $iFileExists Then ;~ While _WinAPI_FileInUse($sFilePath2) ;~ Sleep(1000) ;~ WEnd ;~ ExitLoop ;~ Else ;~ Sleep(1000) ;~ EndIf ;~ Next ;Create a new workbook with only 1 worksheet Global $oWorkbook = _Excel_BookNew($oAppl, 1) ;If @error Then Exit MsgBox(0, "Excel UDF: _Excel_BookNew Example 1", "Error creating new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;MsgBox(0, "Excel UDF: _Excel_BookNew Example 1", "Workbook has been created successfully with only 1 worksheets.") ;~ Global $oWorkbook = _Excel_BookOpen($oAppl, $sFilePath5, False, True) ;~ ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_BookOpen: " & $sFilePath & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;~ ;Create a new workbook with only 1 worksheet ;~ ;Global $oWorkbook = _Excel_BookNew($oAppl, 1) ;~ ;If @error Then Exit MsgBox(0, "Excel UDF: _Excel_BookNew Example 1", "Error creating new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;~ ;MsgBox(0, "Excel UDF: _Excel_BookNew Example 1", "Workbook has been created successfully with only 1 worksheets.") ;Sleep(3000) ;~ Global $sMessage, $sMessage2 ;~ SplashTextOn("TitleFoo", $sMessage, 580, 60, 900, 840, 1 + 4, "", 16) ;~ For $i = 1 To 10 ;~ $sMessage = $sMessage & "." ;~ $sMessage2 = @TAB & "Pausa " & $sMessage ;~ ControlSetText("TitleFoo", "", "Static1", $sMessage2) ;~ Sleep(1000) ;~ Next $oWorkbook.UpdateLinks = 2 ;xlUpdateLinksNever ;Global $oSheets = $oWorkbook.Sheets Global $oSheet = $oWorkbook.ActiveSheet ;Global $oSheet = $oWorkbook.Sheets("Sheet1") ;MsgBox(0, "", $oSheet.Name) $oSheet.Range("A1:C1").Font.Bold = True $oSheet.Range("A1:A" & $RecCount + 1).NumberFormat = "dd/mm" $oSheet.Range("B1:B" & $RecCount + 1).NumberFormat = "@" Global $oPageSetup = $oSheet.PageSetup With $oPageSetup .PrintTitleRows = "$1:$1" .PrintTitleColumns = "" .PrintArea = "" .LeftHeader = "&D" .CenterHeader = "Report" .RightHeader = "&P of &N" ;.LeftFooter = "&F {&A}" .CenterFooter = "" .RightFooter = "" .LeftMargin = 28 .RightMargin = 28 .TopMargin = 28 .BottomMargin = 28 .HeaderMargin = 15 .FooterMargin = 15 .PrintHeadings = False .PrintGridlines = True .PrintComments = -4142 .CenterHorizontally = False .CenterVertically = False .Orientation = 1 ;2 .Draft = False .FirstPageNumber = -4105 .Order = 1 .BlackAndWhite = True .Zoom = 100 EndWith ;https://www.autoitscript.com/forum/topic/195252-_excel_rangewrite-doesnt-write-array-from-adodb-getrows/ Global $TrstArray = $rstArray _ArrayTranspose($TrstArray) $oSheet.Range("A2:C" & $RecCount + 1).Value = $TrstArray Global $aArray2D[1][4] = [["Date", "Client", "Amount"]] _Excel_RangeWrite($oWorkbook, $oSheet, $aArray2D, $oSheet.Cells(1, 1)) ;Global $aArray1D[11] = ["ID", "Udes", "Descrip", "Matricula", "Kilos", "Proveedor", "Corredor", "Fecha", "Contrato", "Restan", "Tanque"] ;$oSheet.Range("A1:K1").value = $aArray1D ;Global $aArray2D[1][6] = [[$rstArray[$i][1], $rstArray[$i][2], $rstArray[$i][3], $rstArray[$i][4], $rstArray[$i][5], $rstArray[$i][6]]] ;_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aArray2D, $oSheet.Cells($UltimaFila, 1).Resize(1, 6)) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_RangeWrite: " & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;Global $aArray2D[3][5] = [[11, 12, 13, 14, 15], [21, 22, 23, 24, 25], [31, 32, 33, 34, 35]] ;_Excel_RangeWrite($oWorkbook, Default, $aArray2D, "B1") ;Local $aArray1D[13] = ["Ped", "Archivo", "Abono", "NomCli", "H+I", "ACIDEZ", "CERAS", "E+U", "aa", "aa", "aa", "aa", "aa"] ;$oSheet.Range("A1:M1").value = $aArray1D ;$oSheet.Cells(1, 1).Resize(1, 13).value = $aArray1D ;_Excel_BookSaveAs($oWorkbook, $sFilePath, $xlOpenXMLWorkbook, True) ;$xlOpenXMLWorkbook 51 ;$xlExcel8 56 ;_Excel_BookClose($oWorkbook, False) ;_Excel_BookClose($oWorkbook, True) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_BookClose: " & $sFilePath & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;~ While _WinAPI_FileInUse($sFilePath5) ;~ Sleep(1000) ;~ Wend ;~ Sleep(3000) ;$oAppl.EnableEvents = True $oAppl.DisplayAlerts = True ;https://www.autoitscript.com/forum/topic/136414-excel-close-problem/?do=findComment&comment=953433 ;$oAppl.Application.Quit ;$oAppl = "" ;https://www.autoitscript.com/forum/topic/166043-close-the-entire-application-of-excel/ ;https://www.autoitscript.com/forum/topic/166043-close-the-entire-application-of-excel/?do=findComment&comment=1262478 ;Run(@ComSpec & " /c " & 'taskkill /im excel.exe /f /t', "", @SW_HIDE) ;https://www.autoitscript.com/forum/topic/166043-close-the-entire-application-of-excel/?do=findComment&comment=1262830 ;water / Ok. Let's see if the problem is caused by open/close or by working with a workbook. Could you please try: ;#include <Excel.au3> ;$oExcel = _Excel_Open(False, False, False, False, True) ;$oExcelClose = _Excel_Close($oExcel, False, True) ;~ _Excel_Close($oAppl, False, Default) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Close" & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;~ Sleep(1000) ;~ ;Check excel closed ;~ Local $aProcesses = ProcessList("Excel.exe") ;~ ;_ArrayDisplay($aProcesses) ;~ If $aProcesses[0][0] > 0 Then ;~ ;https://www.autoitscript.com/forum/topic/166043-close-the-entire-application-of-excel/?do=findComment&comment=1263191 ;~ ;@water, thanks for your help so far, at least we pinned down that it's not a UDF bug. :) ;~ ;For now I will use a crude workaround by closing the most recent Excel.exe instance: ;~ ProcessClose($aProcesses[$aProcesses[0][0]][1]) ;~ Sleep(100) ;just to allow some time for the process to definitely close (if it does close) ;~ EndIf Else $rst.Close $rst = 0 ; Release the recordset object $cn.Close ; Close the connection $cn = 0 ; Release the connection object ;Disconnect MsgBox(262144, "", "Empty Recordset", 5) EndIf ;This is a custom error handler Func ErrFunc() Local $HexNumber = Hex($oMyError.number, 8) ;~ MsgBox(0, "", "We intercepted a COM Error !" & @CRLF & _ ;~ "Number is: " & $HexNumber & @CRLF & _ ;~ "WinDescription is: " & $oMyError.windescription) ConsoleWrite("-> We intercepted a COM Error !" & @CRLF & _ "-> err.number is: " & @TAB & $HexNumber & @CRLF & _ "-> err.source: " & @TAB & $oMyError.source & @CRLF & _ "-> err.windescription: " & @TAB & $oMyError.windescription & _ "-> err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF) $iEventError = 1 ; Use to check when a COM Error occurs EndFunc ;==>ErrFunc Func _ErrADODB() Msgbox(0,"ADODB COM Error","We intercepted a COM Error !" & @CRLF & @CRLF & _ "err.description is: " & @TAB & $errADODB.description & @CRLF & _ "err.windescription:" & @TAB & $errADODB.windescription & @CRLF & _ "err.number is: " & @TAB & hex($errADODB.number,8) & @CRLF & _ "err.lastdllerror is: " & @TAB & $errADODB.lastdllerror & @CRLF & _ "err.scriptline is: " & @TAB & $errADODB.scriptline & @CRLF & _ "err.source is: " & @TAB & $errADODB.source & @CRLF & _ "err.helpfile is: " & @TAB & $errADODB.helpfile & @CRLF & _ "err.helpcontext is: " & @TAB & $errADODB.helpcontext, 5) Local $err = $errADODB.number If $err = 0 Then $err = -1 ;Devolver datos error Local $sFilePath = @DesktopDir & "\error.txt" ;Open the file for write access. Local $hFileOpen = FileOpen($sFilePath, 2) ;If $hFileOpen = -1 Then ;MsgBox(0, "", "An error occurred when reading/writing the file.") ;EndIf FileWrite($hFileOpen, "ADODB COM Error" & Chr(1) & _ "err.description is: " & @TAB & $errADODB.description & Chr(1) & _ "err.windescription:" & @TAB & $errADODB.windescription & Chr(1) & _ "err.number is: " & @TAB & hex($errADODB.number,8) & Chr(1) & _ "err.lastdllerror is: " & @TAB & $errADODB.lastdllerror & Chr(1) & _ "err.scriptline is: " & @TAB & $errADODB.scriptline & Chr(1) & _ "err.source is: " & @TAB & $errADODB.source & Chr(1) & _ "err.helpfile is: " & @TAB & $errADODB.helpfile & Chr(1) & _ "err.helpcontext is: " & @TAB & $errADODB.helpcontext _ ) ;Close the handle returned by FileOpen. FileClose($hFileOpen) $rst = 0 ;$cmd = 0 $cn.Close $cn = 0 ;Disconnect Exit EndFunc Func OnAutoItExit() $rst = 0 ;Release the recordset object If IsObj($cn) Then If $cn.State > 0 Then $cn.Close ;adStateOpen Close the connection $cn = 0 ; Release the connection object EndIf EndFunc  
      example_files.zip
    • By shino54
      Hello,
      I would like a query to know if an entry exists
      Thank you in advance.
      $sQuery = "SELECT Alger FROM garage where auto='BL1879'" $result1 = $result.Fields("Alger").Value if $result1="" Then MsgBox(0, "ERROR", "BAD not exist") Else MsgBox(0, "Success!", "OK exist") EndIf Exit  
×
×
  • Create New...