ADO.au3 UDF - BETA - Support Topic

[mLipok]

 

8 hours ago, Skysnake said:

 

 

8 hours ago, Skysnake said:

In case anyone was wondering, the ADO.UDF DOES return a data source not found error, but I was hoping for a simple test without the ADO overhead.

###############################
ADO.au3 v.2.1.15 BETA (1100) : ==> COM Error intercepted !
......
$oADO_Error.source is:  Microsoft OLE DB Provider for ODBC Drivers
......

 

 

This look like you are using existing provider: Microsoft OLE DB Provider for ODBC Drivers

So I need to ask you again:

How you create connections string ?
or
How you are using ADO.au3 to create connection ?

 

[Skysnake]

@mLipok, my connection string is correct.  That is not the issue. I am trying to find an easy way to determine if a specific ODBC source is available.  Assume the connection string is correct.  How do I easily return a "does data source exist" test?

Edited October 5, 2017 by Skysnake

[mLipok]

On 5.10.2017 at 9:12 AM, Skysnake said:

How do I easily return a "does data source exist" test?

Did you try this way:

https://stackoverflow.com/questions/164967/how-can-i-enumerate-the-list-of-dsns-set-up-on-a-computer-using-vba/165044#165044

 

[mLipok]

On 4.10.2017 at 11:30 AM, Skysnake said:

About 3 minutes on my computer... I was hoping for a quick check for a specific driver

Does not show my dBase driver

Am I looking in the wrong place?

 

huh...

Now I see what you are doing.

I just added DSN

 

here is what I get

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI]

[HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]
"SQL TEST"="SQL Server Native Client 11.0"

[HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\SQL TEST]
"Driver"="C:\\Windows\\system32\\sqlncli11.dll"
"Description"="testing"
"Server"="NOTEBOOK\\SQLEXPRESS"
"Database"="BAZA"
"Trusted_Connection"="Yes"

 

Now I ask you again how you are using connection string ? You should to know that using DSN is a different thing then using Driver or Provider.

 

[mLipok]

Today MS Update:  KB401681 brokes Provider=Microsoft.Jet.OLEDB.4.0;
You can use: Provider=Microsoft.ACE.OLEDB.12.0;

But for this you need to download and install:
https://www.microsoft.com/en-us/download/details.aspx?id=13255

 

[Skysnake]

Thank you. Will check and revert.

 

[mLipok]

On 12.10.2017 at 4:13 PM, mLipok said:

Today MS Update:  KB401681 brokes Provider=Microsoft.Jet.OLEDB.4.0;
You can use: Provider=Microsoft.ACE.OLEDB.12.0;

But for this you need to download and install:
https://www.microsoft.com/en-us/download/details.aspx?id=13255

 

I forget to add:  I mean broke but not at all, I find only  problems with XLS files.

[mLipok]

@Skysnake What about the problem with DSN connection ?
Did you check:
 

[HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]
"SQL TEST"="SQL Server Native Client 11.0"

[HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\SQL TEST]

[mLipok]

Releated topic/thread/discussions:

 

Edited June 6, 2018 by mLipok

[mLipok]

On 14.10.2017 at 9:16 AM, Skysnake said:

Thank you. Will check and revert.

 

any progress ?

 

[Skysnake]

On 10/16/2017 at 8:30 PM, mLipok said:

@Skysnake What about the problem with DSN connection ?
Did you check:

found it

 

; modified HelpFile example :)
#include <MsgBoxConstants.au3>

Local $sVar = RegRead("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ODBC Data Sources", "PostgreSQL35W") ; PostgreSQL35W ; Firebird ; Oracle
MsgBox($MB_SYSTEMMODAL, "Installed Postgres driver:", $sVar)

If NOT found, the snippet returns blank...

 

Thx

Edited November 27, 2017 by Skysnake

[Skysnake]

I use the ADO UDF and work with a local SQLite DB and a remote PostgreSQL db.

I have not declared a Foreign Data Wrapper (FDW).

At the moment I select from the SQLite table, then INSERT the $aResult[$i][x] into the remote db.

This is slow.  The reason for the insert this way is that the data needs to be modified slightly upon insert, as the DDL of the two tables differ.

 

However, I am looking for advice on a faster insert.  One idea I am playing with is to place all the data in a local array.  Then edit the array.  Then try to insert the entire array at once. 

I will write the script  I just need some ideas for inspiration... 

Thx

 

[mLipok]

Did  you check how long/slow/fast is  INSERT to remote DB ?
Check also ping to this remote HOST.

 

[Skysnake]

Google found this.

The original problem

The follow up reply

I reconfigured the ODBC connection setting (through the driver's windows interface) and set the protocol to "7.4-1" and it solved the slowdown problem.
(w00t!)
Which is fine by us as it was the default behavior before (I guess), and that's how our application is designed to handle transaction. 

Thank you, guys :)

The test case is pretty simple  :
--------------------------------------------------------
-- create and populate table
CREATE TABLE _slowlocks
(
  lib character varying(50),
  numbers integer
);
INSERT INTO _slowlocks VALUES ('one',0),('two',0),('three',0);
----------------------------------------------------------
--- In any language/program, run something like this : 
---------------
ExecuteSQL("BEGIN;")
FOR(i=0,i++,i<1000)
    ExecuteSQL("SELECT numbers FROM _slowlocks WHERE lib='two' FOR UPDATE;")
    ExecuteSQL("UPDATE _slowlocks SET numbers= numbers +1 WHERE lib='two';")
NEXT i
ExecuteSQL("COMMIT;")
----------------------------------------------------------

With protocol set to "7.4-2", it took 1 minute and 11 seconds
With protocol set to "7.4-1", it took  1.2 second ...

1. I get comparable speed results

2. I do not know how to set the protocol using the ADO UDF

3. I am attempting the transaction method 

 

Update

Setting a protocol -- Just found this

tcp:servername

Waiting for current process to complete, then I will modify my connection string.

This, however seems to be outdated: psqlODBC Configuration Options

 

Final

multi column inserts into four tables, rows inserted

table count

1. 186
2. 14696
3. 5635
4. 4

Total db size 23mb

 With transaction, 67 minutes. Without transaction 68 minutes.  

tcp:servername does not work ...

Ping statistics for x.x.x.x:
    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 191ms, Maximum = 201ms, Average = 194ms

Skysnake

Edited January 25, 2018 by Skysnake

[Skysnake]

Further to the post above, I tried bulk inserts.

same connection, same db, same date. Only 135 rows.

Single inserts in a FOR loop + 1 

;~ started inserting x records 07-Feb-18 5:48:48 PM
;~ done inserting x records 07-Feb-18 5:49:16 PM 
Consistently returns ~30seconds.

 

Bulk inserts, where a FOR loop +10 --- single query with 10 lines at a time...

;~ started inserting x records 07-Feb-18 6:08:48 PM
;~ done inserting 135 records 07-Feb-18 6:08:51 PM 

Consistently returns ~3seconds...

 

;~ started inserting 14696 records 08-Feb-18 11:42:37 AM
;~ done inserting 14696 records 08-Feb-18 11:52:07 AM

Bulk insert returned in TEN MINUTES!!!!

This used to take more than an hour!!!!

 

IIRC I  read somewhere that ADO / ODBC uses an Auto Commit --- this may guarantee data integrity at the cost of speed.  Generally this is the better option...

 

If anyone is interested I will produce a replicator...

 

Edited February 8, 2018 by Skysnake

[mLipok]

Thanks for this findings.

All this week I'm in a bussiness trip currently in a Train between Warsaw and Katowice.

I will keep this in my list to check.

 

[mLipok]

2 hours ago, Skysnake said:

If anyone is interested I will produce a replicator

I will be happy

 

[ioripalm]

Hi

I have a problem in Firebird Datebase.

$sQUERY1 Can be run well

expandcollapse popup

#include <ADO.au3>
Local $sDSN = 'FireBird' ; Default
;~ Local $sDatabase = @ScriptDir & '\ENROLPRO.GDB' ; db name
Local $sDatabase = 'KITEDM/3050:E:\Enrolpro\ENROLPRO.GDB' ; db name
Local $sServer = '192.168.0.5' ; Server IP
Local $sPort = '3050' ; Port
Local $sUser = 'SYSDBA' ; DEFAULT Username
Local $sPassword = 'masterkey' ; DEFAULT Password
Local $sConnectionString = 'DSN=' & $sDSN & ';DATABASE=' & $sDatabase & ';SERVER=' & $sServer & ';PORT=' & $sPort & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'

_ADO_ComErrorHandler_UserFunction(_ErrFunc)
$sQUERY1 = "SELECT * FROM INTAKE WHERE INTAKECODE = 'OCT2017'"
$sQUERY2 = "update or insert into CLASSROOM(CLASSROOMID,CLASSROOMCODE,CLASSROOMNAME,NUMBEROFSEATS,CLASSTYPEID,CLASSSTARTDATE,CLASSFINISHDATE,CAMPUSID,DISABLED,INTAKEID,COURSE_NBR,PROGCODE) " & _
        "values (448,'OCT17601-L7','ORG & ETHIC',26,8,'2017/10/23','2017/12/15',1,0,38,NULL,'DBML7');"

Local $oConnection = _ADO_Connection_Create() ; Create connection object
_ADO_Connection_OpenConString($oConnection, $sConnectionString)
$result = _ADO_Execute($oConnection, $sQUERY2,True)

If @error Then
    SetError(@error, @extended, $ADO_RET_FAILURE)
    ConsoleWrite('@error = ' & @error & @CRLF & '@extended = ' & @extended & @CRLF & $sQUERY1 & @CRLF)
EndIf

_ADO_Connection_Close($oConnection) ; Clean Up
MsgBox(1,1,$result[2])
_ArrayDisplay($result[2])
; User's COM error function. Will be called if COM error occurs
Func _ErrFunc($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 $sQUERY2 have a error

AddClass.au3 (1284) : ==> COM Error intercepted !
    err.number is:      0x80020009
    err.windescription: 发生意外。

    err.description is:     [ODBC Firebird Driver][Firebird]attempted update during read-only transaction
    err.source is:      Microsoft OLE DB Provider for ODBC Drivers
    err.helpfile is:    
    err.helpcontext is:     0
    err.lastdllerror is:    0
    err.scriptline is:  1284
    err.retcode is:     0x80004005

@error = 2
@extended = -2147352567

I confrom this database can be write.Could you help me find out where have a problem.

Thank you very much.

 

 

[Skysnake]

Hi @ioripalm, I use two different styles of syntax for ADO queries.

Execute (does not expect a return)

; like SQLite _SQLite_Execute :)
If Not _ADO_Execute($g_DB, $sSQL) = $ADO_ERR_SUCCESS Then _ ; insert, does not expect a return
        ConsoleWrite("0. $ADO_ERR_SUCCESS Error " & $ADO_ERR_SUCCESS & @CRLF)

ConsoleWrite("0. $ADO_ERR_SUCCESS Success " & $ADO_ERR_SUCCESS & @CRLF)

and where I do expect data to be returned...

; like SQLite_GetTable2d
$aResult = _ADO_Execute($g_DB, $query, True, True) ; select, expect table
For $a = 1 To (UBound($aResult) - 1) Step +1
    $sMsg = $aResult[1][0] ; grab only the first instance (do not loop array)
Next

Please the SQLite documentation for differences in syntax.  These examples follow a similar logic.

Skysnake

Edited February 21, 2018 by Skysnake

[ioripalm]

On 2018/2/21 at 0:39 AM, ioripalm said:

Hi

I have a problem in Firebird Datebase.

$sQUERY1 Can be run well

expandcollapse popup

#include <ADO.au3>
Local $sDSN = 'FireBird' ; Default
;~ Local $sDatabase = @ScriptDir & '\ENROLPRO.GDB' ; db name
Local $sDatabase = 'KITEDM/3050:E:\Enrolpro\ENROLPRO.GDB' ; db name
Local $sServer = '192.168.0.5' ; Server IP
Local $sPort = '3050' ; Port
Local $sUser = 'SYSDBA' ; DEFAULT Username
Local $sPassword = 'masterkey' ; DEFAULT Password
Local $sConnectionString = 'DSN=' & $sDSN & ';DATABASE=' & $sDatabase & ';SERVER=' & $sServer & ';PORT=' & $sPort & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'

_ADO_ComErrorHandler_UserFunction(_ErrFunc)
$sQUERY1 = "SELECT * FROM INTAKE WHERE INTAKECODE = 'OCT2017'"
$sQUERY2 = "update or insert into CLASSROOM(CLASSROOMID,CLASSROOMCODE,CLASSROOMNAME,NUMBEROFSEATS,CLASSTYPEID,CLASSSTARTDATE,CLASSFINISHDATE,CAMPUSID,DISABLED,INTAKEID,COURSE_NBR,PROGCODE) " & _
        "values (448,'OCT17601-L7','ORG & ETHIC',26,8,'2017/10/23','2017/12/15',1,0,38,NULL,'DBML7');"

Local $oConnection = _ADO_Connection_Create() ; Create connection object
_ADO_Connection_OpenConString($oConnection, $sConnectionString)
$result = _ADO_Execute($oConnection, $sQUERY2,True)

If @error Then
    SetError(@error, @extended, $ADO_RET_FAILURE)
    ConsoleWrite('@error = ' & @error & @CRLF & '@extended = ' & @extended & @CRLF & $sQUERY1 & @CRLF)
EndIf

_ADO_Connection_Close($oConnection) ; Clean Up
MsgBox(1,1,$result[2])
_ArrayDisplay($result[2])
; User's COM error function. Will be called if COM error occurs
Func _ErrFunc($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 $sQUERY2 have a error

AddClass.au3 (1284) : ==> COM Error intercepted !
    err.number is:      0x80020009
    err.windescription: 发生意外。

    err.description is:     [ODBC Firebird Driver][Firebird]attempted update during read-only transaction
    err.source is:      Microsoft OLE DB Provider for ODBC Drivers
    err.helpfile is:    
    err.helpcontext is:     0
    err.lastdllerror is:    0
    err.scriptline is:  1284
    err.retcode is:     0x80004005

@error = 2
@extended = -2147352567

I confrom this database can be write.Could you help me find out where have a problem.

Thank you very much.

 

 

I find out what happend: