ADO.au3 UDF 2.1.15 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.15 BETA

Released

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

 

 

ADO 2.1.15 BETA.zip


1 person likes this



User Feedback

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


Skysnake

· Edited by Skysnake

  • 5
   2 of 2 members found this review helpful 2 / 2 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

2 people like this

Share this review


Link to review
  • Similar Content

    • sc4ry
      By sc4ry
      Dear all,
      I already have a formula which will select doublicates, but sometimes there might be entries with additional information starting after "-", which I now want to trim away.
      Current formula is:
      Select DISTINCT a.title, a.oid, a.size, a.disk from DB a, DB b where (a.title like b.title and a.oid <> b.oid)" & $ActiveDisks_Filter & " AND (a.comment <> 'Delete' AND b.comment <> 'Delete') Order by a.title ASC, a.size DESC; testwise I want to add a simple solution like left, but afterwards I get an error but I do not know why.
      Select DISTINCT a.title, a.oid, a.size, a.disk from DB a, DB b where (left(a.title,15) like b.title and a.oid <> b.oid) Order by a.title ASC, a.size DESC; Error: near "(": syntax error: Finally I would like to insert intstr to search for "-" but currently I am not able to extend my current code =/
      Thanks a lot for your help.
      EDIT: hey, I managed it, left was not working also it was somehow shown that the tool knowed the comment. I did it now with substr+instr
      (substr(a.title, 1, instr(a.title, ' - '))  
    • AndyS19
      By AndyS19
      I have code that does a WMI SQL query to find all defined printers, and I want to parse the returned object in several places.  However, after parsing it the first time, all other times fail to find any printer objects.
      Here is my test code:
      test() Func test() Local $oPrinters, $oPrinter, $err, $cnt, $oP, $query $query = "SELECT * FROM Win32_Printer" $oPrinters = doQuery($query) $err = @error LogMsg("+++: $err = " & $err & ", isObj($oPrinters) = " & IsObj($oPrinters)) If ($err == 0) Then LogMsg("FIRST LOOP") ; <=== FIRST LOOP $cnt = 0 $oP = $oPrinters LogMsg("+++: isObj($oP) = " & IsObj($oP)) For $oPrinter In $oP $cnt += 1 LogMsg("+++: isObj($oPrinter): " & IsObj($oPrinter) & ", $oPrinter.Name ==>" & $oPrinter.Name & "<==") Next LogMsg("+++: Found " & $cnt & " printers") LogMsg("SECOND LOOP") ; <== SECOND LOOP $cnt = 0 $oP = $oPrinters LogMsg("+++: isObj($oP) = " & IsObj($oP)) For $oPrinter In $oP $cnt += 1 LogMsg("+++: isObj($oPrinter): " & IsObj($oPrinter) & ", $oPrinter.Name ==>" & $oPrinter.Name & "<==") Next LogMsg("+++: Found " & $cnt & " printers") EndIf EndFunc ;==>test Func doQuery($sQuery, $lnum = @ScriptLineNumber) #forceref $lnum LogMsg("+++:" & $lnum & ": doQuery(" & '"' & $sQuery & '"' & ") entered") Local $oWMIService, $oResults, $errstr Local $wbemFlags = BitOR(0x20, 0x10) ; $wbemFlagReturnImmediately and wbemFlagForwardOnly $oWMIService = ObjGet("winmgmts:\\" & "localhost" & "\root\CIMV2") If (IsObj($oWMIService)) Then $oResults = $oWMIService.ExecQuery($sQuery, "WQL", $wbemFlags) If (IsObj($oResults)) Then LogMsg("+++: doQuery() returns @error = 0, Good: returning the object") Return (SetError(0, 0, $oResults)) ;;; Good: return the object Else $errstr = "" _ & "WMI Query failed." & @CRLF _ & "This is the query:" & @CRLF _ & " " & $sQuery LogMsg("+++: ====>" & $errstr & "<===") LogMsg("+++: doQuery() returns @error = 1") Return (SetError(1, 0, $errstr)) ; Error: Query faled EndIf Else $errstr = "" _ & "WMI Output" & @CRLF _ & "No WMI Objects Found for class: " & @CRLF _ & "Win32_PrinterDriver" & @CRLF _ & "using this query:" & @CRLF _ & " " & $sQuery LogMsg("+++: ====>" & $errstr & "<===") MsgBox(0, "ERROR", $errstr) ; Error: Cannot get $oWMIService object Exit (1) EndIf EndFunc ;==>doQuery Func LogMsg($msg, $lnum = @ScriptLineNumber) ConsoleWrite("+++:" & $lnum & ": " & $msg & @CRLF) EndFunc ;==>LogMsg Parsing the returned $oPrinters object shows 5 printers:
      +++:15: FIRST LOOP +++:18: +++: isObj($oP) = 1 +++:22: +++: isObj($oPrinter): 1, $oPrinter.Name ==>Microsoft XPS Document Writer<== +++:22: +++: isObj($oPrinter): 1, $oPrinter.Name ==>Microsoft Office Document Image Writer<== +++:22: +++: isObj($oPrinter): 1, $oPrinter.Name ==>Fax<== +++:22: +++: isObj($oPrinter): 1, $oPrinter.Name ==>Canon MG7100 series Printer WS<== +++:22: +++: isObj($oPrinter): 1, $oPrinter.Name ==>Canon MG6100 series Printer WS<== +++:24: +++: Found 5 printers Parsing it again, shows no printers:
      +++:26: SECOND LOOP +++:29: +++: isObj($oP) = 1 +++:35: +++: Found 0 printers  
    • GhostLine
      By GhostLine
      Hello guys !
      I'm stuck in something stupid (I guess), but since I've no clue on how to solve it, here I come
      I'm trying to collect the result of the query "select @@hostname" on MSSQL Server, but it doesn't work ... and I'm sure I'm connected to the database (I've successfully seen the database treeview). 

      #Region ;**** Directives created by AutoIt3Wrapper_GUI **** #AutoIt3Wrapper_UseX64=n #EndRegion ;**** Directives created by AutoIt3Wrapper_GUI **** #include<_sql.au3> #include<array.au3> Local $aData,$iRows,$iColumns ;Server ID and credentials for tCards Global $ServerAddressT = "10.200.88.1" Global $ServerUserNameT = "user" Global $ServerPasswordT = "password" Global $DatabaseNameT = "DataBASE" ;Connect to DB _SQL_RegisterErrorHandler();register the error handler to prevent hard crash on COM error $OADODB = _SQL_Startup() If $OADODB = $SQL_ERROR Then MsgBox(0 + 16 + 262144, "Error", _SQL_GetErrMsg()) If _sql_Connect(-1, $ServerAddressT, $DatabaseNameT, $ServerUserNameT, $ServerPasswordT) = $SQL_ERROR Then     MsgBox(0 + 16 + 262144, "Error 1", _SQL_GetErrMsg())     _SQL_Close()     Exit Else     If _SQL_Execute(-1,"select @@SERVERNAME") = $SQL_ERROR then         Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())     Else         _ArrayDisplay(_SQL_GetTable(-1,"select @@SERVERNAME",$aData,$iRows,$iColumns)) ;~         $toto = _SQL_Execute(-1,"select @@SERVERNAME") ;~         _ArrayDisplay($toto)     EndIf EndIf
    • Gorby7
      By Gorby7
      I have an Autoit-based client GUI that uses a single MS SQL Server database. I have no problem connecting to and executing queries against this database from my AutoIT code, with the below exception:
      One of the database's stored procedures I need to execute requires a parameter that is a table. I've set up the appropriate table_type on the database and everything works fine on the database itself when I execute the stored proc with a table variable for the parameter (no AutoIT involved). The problem is that I can't figure out how to pass a table parameter from AutoIT, assuming it's even possible. The code below shows the two methods I've tried, both methods return a RecordSet object that is at EOF.
      #include <Array.au3> Opt("MustDeclareVars", 0) $sDBSrv = "ITSQL01.domain.com" $sDBName = "INVDEV" $sADOName = "ADODB.Connection" $oSQLConn = ObjCreate($sADOName) $sConnStr = "Driver={SQL Server};Server=" & $sDBSrv & ";Database=" & $sDBName & ";Trusted_Connection=yes;" $oSQLConn.Open ($sConnStr) $rsoFacFriendly = $oSQLConn.Execute("SELECT TOP 5 FriendlyName FROM Facility") If Not $rsoFacFriendly.EOF Then $aFacFriendly = $rsoFacFriendly.GetRows() _ArrayDisplay($aFacFriendly) $rsoSPResults = $oSQLConn.Execute("EXEC spTableParameterPassTest " & $aFacFriendly) Select Case $rsoSPResults.EOF = False MsgBox(0, "sp exec1", "not end of file") $aSPResults = $rsoSPResults.GetRows() _ArrayDisplay($aSPResults) Case IsObj($rsoSPResults) = 0 MsgBox(0, "sp exec1", "rso isn't even an object") Case $rsoSPResults.EOF = True MsgBox(0, "sp exec1", "At end of file") Case Else MsgBox(0, "sp exec1", "Something else happened") EndSelect $rsoSPResults = 0 $rsoSPResults = $oSQLConn.Execute("EXEC spTableParameterPassTest " & $rsoFacFriendly) Select Case $rsoSPResults.EOF = False MsgBox(0, "sp exec2", "not end of file") $aSPResults = $rsoSPResults.GetRows() _ArrayDisplay($aSPResults) Case IsObj($rsoSPResults) = 0 MsgBox(0, "sp exec2", "rso isn't even an object") Case $rsoSPResults.EOF = True MsgBox(0, "sp exec2", "At end of file") Case Else MsgBox(0, "sp exec2", "Something else happened") EndSelect $rsoSPResults = 0 $rsoFacFriendly = 0 $oSQLConn.Close $oSQLConn = 0 Exit  
    • rynow
      By rynow
      romaSQL
      This autoIt UDF is built on the concept of Laravel Query & doctrine.
      RomaSQL provides a new, comfortable and easy to use way for SQL-queries in autoIt.
      Most of the common SQL-queries are supported already and more are coming soon.
      All of your support is much appreciated.
      Connections
      For the connection the object ADODB is used. Therefore the connection string is based on ODBC.
      You can also use OLEDB connection strings or other database connections.
      In order for this to work your add-ons have to be installed in the function: __4ern_SQL_Connection.
      I’d be very glad if you shared your modifications with me.
      Currently supported connections
      -       MySQL (odbc)
      -       Microsoft SQL Server (odbc)
      -       SQLite (odbc)
      -       Microsoft Access (odbc)
      Command reference
      $SQL_connect; establishing connection $SQL_returnType; return a Array or Dictionary ('oDict') Object (Default = Array) $SQL_setDefaultTable; Default Tablename $SQL_setDefaultKey; Default Colmn Key (Default = id) $SQL_debug; if True, show SQL Statment in Console $SQL_get $SQL_update $SQL_delete $SQL_insertInto $SQL_take $SQL_limit $SQL_table $SQL_select $SQL_distinct $SQL_where $SQL_orWhere $SQL_whereBetween $SQL_whereNotBetween $SQL_whereIn $SQL_whereNotIn $SQL_whereNull $SQL_whereNotNull $SQL_having $SQL_orHaving $SQL_havingBetween $SQL_havingNotBetween $SQL_havingIn $SQL_havingNotIn $SQL_havingNull $SQL_havingNotNull $SQL_groupBy $SQL_orderBy  
      Examples
      establishing connection
      ;-----/ ; SQLite Connection ;-----/ $SQL_setDatabase('sqlite') $SQL_connect('C:\project.db') ;-----/ ; Access Connection ; Database, User, Password ;-----/ $SQL_setDatabase('access') $SQL_connect('C:\project.mdb') ;or as Admin $SQL_connect('C:\project.mdb', '4ern', 'root') ;-----/ ; SQLServer Connection ; Database, User, Password, Server, Driver ;-----/ $SQL_setDatabase('sqlserver') $SQL_connect('myDB', '4ern', 'root', 'localhost') ;or with Driver $SQL_connect('myDB', '4ern', 'root', 'localhost', 'SQL Server') ;-----/ ; MySQL Connection ; Database, User, Password, Server, Driver ;-----/ $SQL_setDatabase('mysql') $SQL_connect('myDB', '4ern', 'root', 'localhost') ;or with Driver $SQL_connect('myDB', '4ern', 'root', 'localhost', 'MySQL ODBC 5.2 UNICODE Driver')  
      simple SQL query
      $SQL_table('albums') $aRet = $SQL_get() if IsArray($aRet ) then _ArrayDisplay($aRet ) else ConsoleWrite('Keine Ergebnisse' & @LF) endif  
      Select
      $SQL_table('albums') $SQL_select('id', 'Name', 'Artist', 'Song') ;or pass to an Array Local $aSelect = ['id', 'Name', 'Artist', 'Song'] $SQL_select($aSelect) $aRet = $SQL_get() if IsArray($aRet ) then _ArrayDisplay($aRet ) else ConsoleWrite('Keine Ergebnisse' & @LF) endif where
      $SQL_table('albums') $SQL_select('id', 'Name', 'Artist', 'Song', 'Votes') $SQL_where('Artist', 'adele') $SQL_where('Votes', '>=' ,'9') $SQL_orWhere('Artist', '=' ,'Rag'n'Bone Man') ;or pass to an 2dArray Local $aSelect = [['Artist','adele'],['Votes', '>=' ,'9']] $SQL_where($aSelect) $aRet = $SQL_get() if IsArray($aRet ) then _ArrayDisplay($aRet ) else ConsoleWrite('Keine Ergebnisse' & @LF) endif  
      If you need more examples, then tell me exactly what you need.
      I hope you like my UDF and find some use for it.
      ---
      ->DONWLOAD romaSQL