Jump to content

ADO.au3 UDF 2.1.16 BETA

   (1 review)

1 Screenshot

About This File

I want to present BETA Version of my ADO.au3 UDF.

Support topic is here: http://www.autoitscript.com/forum/index.php?showtopic=180850

 

This UDF is modifed version of _sql.au3 UDF.

For that I want to thanks : ; Chris Lambert, eltorro, Elias Assad Neto, CarlH

 

 


What's New in Version 2.1.16 BETA

Released

2019/08/11 '2.1.16 BETA'
.   Added: $oRecordset.Supports($ADO_adMovePrevious) in _ADO_Recordset_ToString() - xrxca
.   Added: __ADO_MSSQL_CONNECTION_STRING_WinAuth() - mLipok
.   Added: _ADO_GetDSNList() - mLipok
.   Changed: _ADO_RecordsetArray_GetContent() - on succes @extended = UBound($aContent) - mLipok
.
.
.   !!!!! SCRIPT BREAKING CHANGE !!!!!
.   Removed: prameter $iAlternateColors from __ADO_RecordsetArray_Display() for compability wiht current AutoIt Version - mLipok
.   Removed: prameter $iAlternateColors from _ADO_Recordset_Display() for compability wiht current AutoIt Version - mLipok

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

2016/03/18 '2.1.13 BETA'
.   Changed: _ADO_COMErrorHandler - now showing also _ADO_UDFVersion()  - mLipok
.   New: Enums: $ADO_ERR_ISNOTREADYOBJECT - mLipok
.   Renamed: Function: __ADO_Connection_IsOpen >> __ADO_Connection_IsReady - mLipok
.   Changed: Function: __ADO_Connection_IsReady : new feature checking connection state and seting  $ADO_ERR_ISNOTREADYOBJECT - mLipok
.   New: Function: __ADO_Recordset_IsReady - mLipok
.           __ADO_Recordset_IsReady is a wrapper for __ADO_Recordset_IsValid
.               which also check for $oRecordset.state and set $ADO_ERR_ISNOTREADYOBJECT
.           __ADO_Recordset_IsReady is now used in few functions which uses $oRecordset
.   Changed: Function: __ADO_Recordset_IsNotEmpty : now using __ADO_Recordset_IsReady instead __ADO_Recordset_IsValid - mLipok
.           as __ADO_Recordset_IsReady is wrapper for __ADO_Recordset_IsValid
.           so now __ADO_Recordset_IsNotEmpty checking old and new feature
.
.   !!!!!!!!!!!!!!!!!!!!!!!!
.   Renamed: _ADO_ERROR_Description >> _ADO_MSDNErrorValueEnum_Description
.   New: Function: _ADO_GetErrorDescription - mLipok
.   New: Function: _ADO_ConsoleError - mLipok

2016/03/09 '2.1.12 BETA'
.   New: Enums: $ADO_ERR_ISCLOSEDOBJECT - mLipok
.   New: Function: __ADO_Connection_IsOpen - mLipok
.           __ADO_Connection_IsOpen is a wrapper for __ADO_Connection_IsValid  which also check for $oConnection.state and set $ADO_ERR_ISCLOSEDOBJECT
.           __ADO_Connection_IsOpen is now used in few functions which uses $oConnection
.   Changed: Function: __ADO_Recordset_IsNotEmpty - checking $oRecordset.state and return $ADO_ERR_ISCLOSEDOBJECT - mLipok
.   Changed: Function: _ADO_Command_Execute - mlipok
.           Now return recordset
.   Changed: ADO_EXAMPLE.au3 - _Example_MSSQL_COMMAND_StoredProcedure() - mLipok
.   Removed: Function: _ADO_Connection_OpenJet - mLipok
.           Look for: _ADO_Connection_OpenConString or _ADO_ConnectionString_Excel

2016/03/08 '2.1.11 BETA'
.   New: Function: _ADO_OpenSchema_Catalogs - mLipok
.   New: Function: _ADO_OpenSchema_Tables - mLipok
.   New: Function: _ADO_OpenSchema_Columns - mLipok
.   New: Function: _ADO_OpenSchema_Indexes - mLipok
.   New: Function: _ADO_OpenSchema_Views - mLipok
.   New: Function: _ADO_Schema_GetAllCatalogs - mLipok
.   New: Function: _ADO_Schema_GetAllTables - mLipok
.   New: Function: _ADO_Schema_GetAllViews - mLipok
.   Removed: Function: _SQL_GetTableName - mLipok
.   Removed: Function: _ADO_Connection_OpenExcel - mLipok
.       Look for: _ADO_Connection_OpenConString and _ADO_ConnectionString_Excel
.   Changed: ADO_EXAMPLE.au3 - _Example_MySQL() - mLipok
.   Changed: ADO_EXAMPLE.au3 - _Example_PostgreSQL() - mLipok
.   Renamed: Function: _ADO_Command >> _ADO_Command_Create - mLipok
.   Changed: Function: _ADO_Command_Create: Parameters removed - $sQuery - mLipok
.   New: Function: _ADO_Command_CreateParameter - mlipok
.   New: Function: _ADO_Command_Execute - mlipok    .
.   Added: ADO_EXAMPLE.au3 - _Example_MSSQL_COMMAND_StoredProcedure() - mLipok

EDIT: 2019-09-18 - uploaded again. I hope the download link will be fixed now.




User Feedback

You may only provide a review once you have downloaded the file.


Skysnake

· Edited by Skysnake

   5 of 5 members found this review helpful 5 / 5 members

 

I have been working with this ADO.AU3 UDF for more than a year.  

At the time of writing this, I am using beta 2.1.15.  ADO in general allows connection to various data sources.  The full documentation is available from Microsoft and a simple Google search will reveal the scope.

This ADO UDF is very powerful and incorporates ideas from various other UDFs available on the Forums, such as the SQL UDF and others.  The ADO UDF provides for an ADO connection using COM objects.  The design is such that little knowledge of either ADO or COM is required.  

Combining ADO with AutoIt creates a very powerful package.  The way this UDF is implemented it is not necessary to have deep ADO knowledge in order to connect and perform tasks.

Since it uses ADO, in order to implement, the following is required

  • a data source (such as a database)
  • ODBC DNS driver and entry in the Windows Control Panel
  • this ADO UDF 
  • #include of ADO.au3 in the user script

Note that the examples should work by default.  Note the connection values in each example.

 

I have personally tested the ADO UDF on PostgreSQL databases, both locally and online, DBFs both locally and remote and briefly tested connectivity to other data sources.  It all works as expected.

I do not use the power of ADO to the full extent, as my needs are limited to basic reporting.  The advantage is that standard SQL queries can be run over ADO to the data source - even to DBF files!

 

The most important changes in this version 2.1.15 beta are as described below.  

The COM error handling makes provision for standard SQL blank result sets.  This means that COM errors such as no-connection, faulty queries etc will result in a crash.  Valid SQL with valid SQL responses can be processed in a manner which does not lead to a crash, but processed as a normal result.  At the same time, a different implementation of the same UDF (this means you write your own part of the AutoIt code differently) means that such a blank result set may represent a blank ADO cursor, which must result in an ADO / COM error.

The advantage is that a single UDF can now be implemented in different scenario's, on various data sources, with different error handling processes.  

I recommend this ADO.AU3 UDF to anyone wishing to connect to any external data source.

Skysnake

Share this review


Link to review
  • Similar Content

    • By Nas
      Hi everyone,
      I am trying to make a script that runs a query and show it to me to see if everything is right and then decide if I finish it or not so I made a little script as below :
      #include <ADO.au3> #include <Array.au3> #include <MsgBoxConstants.au3> #include <AutoItConstants.au3> _ADO_EVENTS_SetUp(True) _ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler) Local $sDriver = 'SQL Server' Local $sDatabase = 'DataBase' ; change this string to YourDatabaseName Local $sServer = 'Localhost' ; change this string to YourServerLocation Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & ';PWD=' & ';' ;~ Global $Query = _ ;~ "BEGIN TRAN" & @CRLF & _ ;~ "UPDATE Table" & @CRLF & _ ;~ "SET HOUR = 4" & @CRLF & _ ;~ "WHERE CUST = 'TEST'" & @CRLF & _ ;~ "SELECT * FROM Table" & @CRLF & _ ;~ "WHERE CUST = 'TEST'" & @CRLF & _ ;~ "ROLLBACK TRAN" Global $Query = _ "BEGIN TRAN" & @CRLF & _ "SELECT * FROM Table" & @CRLF & _ "WHERE CUST = 'TEST'" & @CRLF & _ "ROLLBACK TRAN" _Query_Display($sConnectionString, $Query) Func _Query_Display($sConnectionString, $sQUERY) ; 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) ; Executing some query directly to Array of Arrays (instead to $oRecordset) Local $aRecordset = _ADO_Execute($oConnection, $sQUERY, True) ; Clean Up _ADO_Connection_Close($oConnection) $oConnection = Null ; Display Array Content with column names as headers _ADO_Recordset_Display($aRecordset, 'Query Result') EndFunc ;==> _Query_Display When I ran this script it works great, but when I run the query below :
      Global $Query = _ "BEGIN TRAN" & @CRLF & _ "UPDATE Table" & @CRLF & _ "SET HOUR = 4" & @CRLF & _ "WHERE CUST = 'TEST'" & @CRLF & _ "SELECT * FROM Table" & @CRLF & _ "WHERE CUST = 'TEST'" & @CRLF & _ "ROLLBACK TRAN" It doesn't show anything, when I take those begin and rollback it does what it should but still not showing me anything at all, is there a way around it that you know of?
      Thank you.
    • By Nas
      Hello Guys,
       
      I am running the Ado example script, if I run this line, it runs without errors :
      Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUser & ';PWD=' & $sPassword & ';' But when I try the windows auth one , it failed everytime :
      Func _Example_MSSQL_WindowsAuthorization() Local $sDatabase = 'AdventureWorks2016' Local $sServer = 'SQLSRV' _Example_5_MSSQL_WinAuth($sServer, $sDatabase, "Select * from Person.Address") EndFunc here is the error :
       
      ADO.au3 v.2.1.16 BETA (1204) : ==> COM Error intercepted !
      $oADO_Error.description is:     Provider cannot be found. It may not be properly installed.
      $oADO_Error.windescription:     Exception occurred.
      $oADO_Error.number is:     80020009
      $oADO_Error.lastdllerror is:     0
      $oADO_Error.scriptline is:     1204
      $oADO_Error.source is:     ADODB.Connection
      $oADO_Error.helpfile is:     C:\Windows\HELP\ADO270.CHM
      $oADO_Error.helpcontext is:     1240655
      By the way, I have 64x windows server 2016 with SQL Server , I already installed the odbc my sql connector, any thought on this will be greatly appreciate it.
    • By argumentum
      $sQueryUpdateTime = "select intUpdateTime from tblStudies " . $where . " ORDER BY intUpdateTime DESC limit 1"; $rs = mysqli_query($conn, $sQueryUpdateTime); $row = mysqli_fetch_assoc($rs); the above used to take 300+ ms. to query. Then I set it as index and takes 30 ms. Cool.
      $sQuery = "select * from tblStudies " . $where . " ORDER BY StudyDate DESC limit $offset,$rows"; // takes 30 ms. on the indexed int $sQuery = "select * from tblStudies " . $where . " ORDER BY StudyDate DESC , PatientName ASC limit $offset,$rows"; // takes 300 ms. due to "PatientName" been a text field, even as I did index it So my observation is that "PatientName" takes a long time to sort, even tho "$rows = 20". Sorting text in 20 rows should be fast.
      ..tho, I find that any 2nd argument in the ORDER BY is just slow.
      Is there a way to query this in a way to have a faster result back ?
      Thanks
      PS: added ADD INDEX `StudyDate_2` (`StudyDate`, `PatientBirthDate`) USING BTREE;  and searched by those two with not much speed change ( StudyDate and PatientBirthDate are integer ).
    • By buymeapc
      Hello,
      I was looking for a way to use the CLR functions to check a SQL 2016 database state, but I've been unable to find an example that I can get to work. Any help would be appreciated. Here's what I have, which always errors when I try to set the connection string and I'm not sure why. Thanks!
      #include ".\Includes\CLR.au3" #include ".\Includes\CLR Constants.au3" JustATest() Func JustATest() Local $oAssembly = _CLR_LoadLibrary("System.Data") ConsoleWrite("$oAssembly: " & IsObj($oAssembly) & @CRLF) Local $oSQLConn = _CLR_CreateObject($oAssembly, "System.Data.SqlClient.SqlConnection") ConsoleWrite("$oSQLConn: " & IsObj($oSQLConn) & @CRLF) $oSQLConn.ConnectionString = "Server=serverip;Database=dbname;UID=user;PWD=aaaa;" $oSQLConn.Open EndFunc Edit: I am using ptrex's CLR UDF from here:
    • By nooneclose
      Good morning, I am trying to figure out if it is possible to check if these invoice lines exist or not. 
      Here are the table and column name: INVOICELINE.INVOICELINENUM (a required field if created)
      Here is a picture of what I am talking about. (Do not worry about security. the picture is from a demo test site so all information is fake)
       
      is there any way to check if these fields exist or not? (they do not exist unless the user clicks on "New Row")
      Example: line 11 does not exist right now. How would I go about to see if it did or not? 
      This is what I have so far:
      SELECT DISTINCT iv.invoicenum, iv.description FROM invoice AS iv JOIN invoiceline AS ivl ON iv.invoicenum = ivl.invoicenum AND iv.siteid = ivl.siteid /* = 'nothing' and yet somehow not null? */ WHERE iv.invoicenum NOT IN (SELECT invoicelinenum FROM invoiceline WHERE invoicelinenum IS NOT NULL) I get 0 results where I should get more than 0. 
×
×
  • Create New...