ADO.au3 UDF - BETA - Support Topic

mLipok · October 4, 2017

On 10/4/2017 at 9:30 AM, Skysnake said:

Expand

On 10/4/2017 at 8:47 AM, 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
......
Expand

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 · October 5, 2017

@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 · October 11, 2017

On 10/5/2017 at 7:12 AM, Skysnake said:

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

Expand

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 · October 11, 2017

On 10/4/2017 at 9: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?

Expand

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 · October 12, 2017

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 · October 14, 2017

Thank you. Will check and revert.

mLipok · October 14, 2017

On 10/12/2017 at 2: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

Expand

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

mLipok · October 16, 2017

@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 · November 10, 2017

Releated topic/thread/discussions:

Edited June 6, 2018 by mLipok

mLipok · November 10, 2017

On 10/14/2017 at 7:16 AM, Skysnake said:

Thank you. Will check and revert.

Expand

any progress ?

Skysnake · November 27, 2017

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

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

Expand

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 · January 22, 2018

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 · January 22, 2018

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

Skysnake · January 25, 2018

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

186
14696
5635
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 · February 7, 2018

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 · February 7, 2018

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 · February 7, 2018

On 2/7/2018 at 4:19 PM, Skysnake said:

If anyone is interested I will produce a replicator

Expand

I will be happy

ioripalm · February 20, 2018

Hi

I have a problem in Firebird Datebase.

$sQUERY1 Can be run well

#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 · February 21, 2018

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 · February 23, 2018

On 2/20/2018 at 11:39 AM, ioripalm said:

Hi

I have a problem in Firebird Datebase.

$sQUERY1 Can be run well

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

Expand

I find out what happend:

ADO.au3 UDF - BETA - Support Topic

Recommended Posts

Top Posters In This Topic

Top Posters In This Topic

Popular Posts

mLipok

mLipok

jchd

Posted Images

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Similar Content