ADO.au3 UDF - BETA - Support Topic

[mLipok]

btw.
If you are using MS SQL DBMS then try this:

Local $oConnection = _ADO_Connection_Create()
    _ADO_Connection_OpenMSSQL($oConnection,$sServer,$sDatabase,$sUser,$sPassword)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

 

[FMS]

thanks for the support @mLipok .
everything is working and I'm still finding stuff out about your ADO .
tumbs up!

[mLipok]

10 minutes ago, FMS said:

I'm still finding stuff out about your ADO .

Good to you.

 

[Skysnake]

Just tested 2.1.13 BETA - Work in progress 2016/03/18 on PostgreSQL.

It works.  Just had to make sure that BOTH 32 & 64 bit drivers were installed.

Requires psqlODBC - PostgreSQL ODBC driver ;

Download http://www.postgresql.org/ftp/odbc/versions/msi/  

From http://www.postgresql.org/download/products/2-drivers-and-interfaces/

 

Just check the "EndFunc    ;==>_Example_PostgreSQL " for MySQL example in ADO_EXAMPLE.au3? 

Thx mLipok

 

[mLipok]

57 minutes ago, Skysnake said:

Just check the "EndFunc    ;==>_Example_PostgreSQL " for MySQL example in ADO_EXAMPLE.au3? 

I saw this before, and have it already fixed - as this is autmaticaly added by TIDY.

 

[mLipok]

I'm working on migration, my programs from MSSQL to PostgreSQL.
I have the first information about the significant difference that I'm a little buried plans:

In PostgreSQL if you want to have access to several CATALOG using the same SQL Instance you must to create multiple connection, one per each CATALOG.
You can not use queries like this following:

Select * from [Catalog].dbo.[TableName]
; also can not use this one:
Use [Catalog2]

http://stackoverflow.com/questions/10335561/use-database-name-command-in-postgresql

If you know other differences affecting the migration (I mean the use of SQL queries), please share your knowledge here.

Regards,
mLipok

 

EDIT: I'm working on some kind of wrapper which should be able to automaticaly change MSSQL Queries to valid PostgreSQL Queries.

Edited May 20, 2016 by mLipok

[Skysnake]

Dear @mLipok, I have just successfully connected to a Firebird database using ADO.UDF

http://firebirdsql.org/pdfmanual/Firebird-2.5-QuickStart.pdf  

Func _Example_Firebird()

    ;http://firebirdsql.org/pdfmanual/Firebird-2.5-QuickStart.pdf

    Local $sDSN = 'Firebird' ; Default
    Local $sDatabase = @ScriptDir & '\firebird\showDB_ado.fdb' ; db name 
    Local $sServer = 'localhost' ; 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 & ';'

    ConsoleWrite("_Example_Firebird " & $sConnectionString & @CRLF)
;   _ADO_Execute("create database " & $sDatabase & " page_size 8192 " & $sUser & $sPassword)
    _Example_3_ConnectionProperties($sConnectionString)

EndFunc   ;==>_Example_Firebird

Required:

Download and install
Firebird-2.5.6.27020_0_Win32.exe
which will create %programfiles%\firebird

Then

Windows Settings (Control Panel)
odbc Data Source
Add
    DSN    Firebird
    Database ...
    Database account    SYSDBA
    Password        masterkey
    Role

Also, open Firewall Port 3050.

I can not CREATE DATABASE using this method, but once a database DOES exist, the ADO UDF connects successfully.

See the above PDF page 25 for the CREATE statement.  I did this with the FIREBIRD iSQL TOOL.

I am hoping that there is a way to create a new table with the ADO UDF and that I merely miss something.  My understanding is that ADO assumes $oConnection and then the string, while the create command uses a different syntax? Guidance appreciated. 

[mLipok]

Thanks for good news and example 
I'll add this Example to my UDF, of course if you do not be angry on me for this.

I was searching answer to your problem and have some usefull links:

Firebird 1.5 : How to query in 2 or more databases

How to create a Firebird or InterBase database by using ADOX and Visual C# .NET (or VBScript)

http://allenbrowne.com/func-adox.html

And here:

When you create working example please post it here.

mLipok

 

Edited July 26, 2016 by mLipok
typo

[Skysnake]

Angry? Never!  Honoured that you will include my humble attempt.

Thanks for the links.  I am too tired to think now.  Will look again tomorrow.

It seems all these examples contain references to catalogue... perhaps somebody who understands the code below can translate it to AutoIt:

; http://allenbrowne.com/func-adox.html#CreateDatabaseAdox
Function CreateDatabaseAdox()
    'Purpose:   Create a database using ADOX.
    Dim cat As New ADOX.Catalog
    Dim strFile As String

    strFile = "C:\SampleADOX.mdb"
    cat.Create "Provider='Microsoft.Jet.OLEDB.4.0';" & _
        "Data Source='" & strFile & "'"
    Set cat = Nothing
    Debug.Print strFile & " created."
End Function

 

[mLipok]

Look at the last link I posted in my previous post

 

[Skysnake]

I am sorry. I Googled. I tried. I do not understand. 

I can successfully connect to an existing .fdb file, but I can not create a new .fdb file from the ADO udf.

 

However, in the meanwhile:

;http://stackoverflow.com/questions/21077286/return-a-value-if-select-returned-null
;RDB$DATABASE always has a single row,

    _Example_2_RecordsetDisplay($sConnectionString, "select * from RDB$DATABASE;")

Which means the Firebird iSQL tool works and the database is available.  

 

Now I just need to find the _ADO_? command to Execute SQL statements.

This did not work for me:

; my connection string
;DSN=Firebird;DATABASE=C:\AutoIt\ado_db\firebird\showDB_ado.fdb;SERVER=localhost;PORT=3050;UID=sysdba;PWD=masterkey;

    Local $sConnectionString = 'DSN=' & $sDSN & ';DATABASE=' & $sDatabase & ';SERVER=' & $sServer & ';PORT=' & $sPort & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'

Local  $new_fdb="CREATE TABLE drinks (    ID_seq INTEGER PRIMARY KEY,NAME VARCHAR(100) NOT NULL    ) ; "
   
Local $fb_query = "insert into drinks (name) values ('tequila');"

    _ADO_Execute($sConnectionString, $new_fdb)
    _ADO_Execute($sConnectionString, $fb_query)

_Example_2_RecordsetDisplay($sConnectionString, "select * from drinks;")
        
        ;---Returns this from Scite console:
        
###############################
ADO.au3 v.2.1.13 BETA (1158) : ==> COM Error intercepted !
$oADO_Error.description is:     [ODBC Firebird Driver][Firebird]Dynamic SQL Error
SQL error code = -204
Table unknown
DRINKS
At line 1, column 15
$oADO_Error.windescription:     Exception occurred.

$oADO_Error.number is:  80020009
$oADO_Error.lastdllerror is:    0
$oADO_Error.scriptline is:  1158
$oADO_Error.source is:  Microsoft OLE DB Provider for ODBC Drivers
$oADO_Error.helpfile is:    
$oADO_Error.helpcontext is:     0
###############################

 

Edited July 27, 2016 by Skysnake
full sentences

[Skysnake]

On 5/20/2016 at 10:35 PM, mLipok said:

If you know other differences affecting the migration (I mean the use of SQL queries), please share your knowledge here.

Hmm. This is beyond my level of PostGreSQL. Sorry. 

 

[mLipok]

I'm not familiar with FireBird so I ask you what exactly means this:

RDB$DATABASE

 

I ask as this look little strange for me in this SQL Statement:

elect * from RDB$DATABASE

Is this normal database name ?
What mean this RDB$ prefix ?

 

[mLipok]

On 26.07.2016 at 6:21 PM, Skysnake said:

See the above PDF page 25 for the CREATE statement.  I did this with the FIREBIRD iSQL TOOL.

If you are refering to this:

Quote

C:\Program Files\Firebird\Firebird_2_5\bin>isql#
Use CONNECT or CREATE DATABASE to specify a database

You must to think that FIREBIRD iSQL TOOL probably (just a guess) is not using normal ADO connection.

btw.
I'll work on ADOX example for FireBird (maybe this week).

[Skysnake]

Hi @mLipok, thx for the reply.

 The funny RDB$DATABASE is a standard, default Firebird database.  It is created during installation. It is designed to ensure that the install was correct and that there is actually something to connect to.  It always contains that one line.

So, in my example, it gives me something to test against.

 

If you can get an ADOX example working that would be great.

 

[mLipok]

20 hours ago, Skysnake said:

$oADO_Error.description is:     [ODBC Firebird Driver][Firebird]Dynamic SQL Error SQL error code = -204 Table unknown DRINKS

This error is a result of:

_ADO_Execute($sConnectionString, $fb_query)

What result you get for this:

_ADO_Execute($sConnectionString, $new_fdb)

 

[Skysnake]

Local $new_fdb = "CREATE TABLE drinks (    ID INTEGER PRIMARY KEY,NAME VARCHAR(100) NOT NULL    ) ; "

    Local $qResult = _ADO_Execute($sConnectionString, $new_fdb)
    ConsoleWrite("$qResult " & $qResult & " * " & $new_fdb & " * Line: " & @ScriptLineNumber & @CRLF)

; results in:

_Example_Firebird DSN=Firebird;DATABASE=C:\AutoIt\ado_db\firebird\showDB_ado.fdb;SERVER=localhost;PORT=3050;UID=sysdba;PWD=masterkey;
$qResult -1 * CREATE TABLE drinks (    ID INTEGER PRIMARY KEY,NAME VARCHAR(100) NOT NULL    ) ;  * Line: 56

Is this what you are looking for?

[mLipok]

No I was thinking about COM Error in console.

[Skysnake]

No COM error in console...

>"C:\Program Files (x86)\AutoIt3\SciTE\..\AutoIt3.exe" "C:\Program Files (x86)\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.au3" /run /prod /ErrorStdOut /in "C:\AutoIt\ado_db\ADO_FB_EXAMPLE.au3" /UserParams    
+>18:49:29 Starting AutoIt3Wrapper v.16.306.1237.0 SciTE v.3.6.2.0   Keyboard:00000409  OS:WIN_10/  CPU:X64 OS:X64  Environment(Language:0409)  CodePage:0  utf8.auto.check:4    # detect ascii high characters and if none found set default encoding to UTF8 and do not add BOM
+>         SciTEDir => C:\Program Files (x86)\AutoIt3\SciTE   UserDir => C:\Users\user\AppData\Local\AutoIt v3\SciTE\AutoIt3Wrapper   SCITE_USERHOME => C:\Users\user\AppData\Local\AutoIt v3\SciTE 
>Running AU3Check (3.3.14.2)  params:-d -w 1 -w 2 -w 3 -w- 4 -w 5 -w 6 -w 7  from:C:\Program Files (x86)\AutoIt3  input:C:\AutoIt\ado_db\ADO_FB_EXAMPLE.au3
+>18:49:29 AU3Check ended.rc:0
>Running:(3.3.14.2):C:\Program Files (x86)\AutoIt3\autoit3.exe "C:\AutoIt\ado_db\ADO_FB_EXAMPLE.au3"    
--> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop
_Example_Firebird DSN=Firebird;DATABASE=C:\AutoIt\ado_db\firebird\showDB_ado.fdb;SERVER=localhost;PORT=3050;UID=sysdba;PWD=masterkey;
$qResult -1 * CREATE TABLE drinks (    ID INTEGER PRIMARY KEY,NAME VARCHAR(100) NOT NULL    ) ;  * Line: 56
$qResult -1 Line: 67
+>18:49:31 AutoIt3.exe ended.rc:0
+>18:49:31 AutoIt3Wrapper Finished.
>Exit code: 0    Time: 2.333

 

[mLipok]

Local $new_fdb = "CREATE TABLE drinks (    ID INTEGER PRIMARY KEY,NAME VARCHAR(100) NOT NULL    ) ; "
Local $qResult = _ADO_Execute($sConnectionString, $new_fdb)
If @error Then ConsoleWrite('! ---> @error=' & @error & '  @extended=' & @extended & ' : TEST1' & @CRLF)
; ConsoleWrite("$qResult " & $qResult & " * " & $new_fdb & " * Line: " & @ScriptLineNumber & @CRLF)

What you get in console ?