B4J Question DELETE / INSERT INTO on external table Sqlite db via odbc

[amorosik]

I have a B4J procedure that works on a Firebird db connected via odbc, and everything works fine
Now I have created a new Sqlite db, containing some tables of the main db, identical structure, and I would like to enhance them by copying the data from the main db tables
I am using this code:

Sub Process_Globals
     Public     MyDB As SQL
     end sub

Sub rigenera_db_sqlite_per_tablet(tel_richiedente As String)As String
    Try
        MyDB.Initialize2(Db_Driver_Class,Db_Jdbc_Url,Db_Username,Db_Password)
        If MyDB.IsInitialized = True Then
            Dim db1_destinazione As String=" [ODBC;Driver={SQLite3 ODBC Driver};Dsn=Peppa_Sqlite;Database=c:\peppa\peppa.db]"
            Dim MyRs As ResultSet = MyDB.ExecQuery("delete from  ARTICOLI in " & db1_destinazione  )
            Dim MyRs As ResultSet = MyDB.ExecQuery("INSERT INTO ARTICOLI in " & db1_destinazione & " select * FROM ARTICOLI")
            MyRs.Close
            End If
        Catch
        stato_db= "DB CONNECTION ERROR => " & LastException
        End Try

End Sub

But at row "MyDB.ExecQuery("delete from ARTICOLI in " & db1_destinazione )" an error interrupts the code
Obviuosly Dsn=Peppa_Sqlite exist and it has been verified and is accessible correctly

Could you help me to compose a correct query for:
- delete the data of an external table, indicated via dsn odbc
- copy data from an internal table to an external table

 

[Daestrum]

I may be wrong , but I'm pretty sure neither an INSERT or DELETE query return a resultset.

 

[amorosik]

MyDB.ExecNonQuery("delete from " & db1_destinazione & ".ARTICOLI" )

java.sql.SQLSyntaxErrorException: Dynamic SQL Error; SQL error code = -104; Token unknown - line 1, column 14; [ [SQLState:42000, ISC error code:335544634]
at org.firebirdsql.gds.ng.FbExceptionBuilder$Type$1.createSQLException(FbExceptionBuilder.java:613)
at org.firebirdsql.gds.ng.FbExceptionBuilder.toFlatSQLException(FbExceptionBuilder.java:384)
at org.firebirdsql.gds.ng.wire.AbstractWireOperations.readStatusVector(AbstractWireOperations.java:152)


MyDB.ExecNonQuery("delete from ARTICOLI in " & db1_destinazione )

java.sql.SQLSyntaxErrorException: Dynamic SQL Error; SQL error code = -104; Token unknown - line 1, column 22; in [SQLState:42000, ISC error code:335544634]
at org.firebirdsql.gds.ng.FbExceptionBuilder$Type$1.createSQLException(FbExceptionBuilder.java:613)
at org.firebirdsql.gds.ng.FbExceptionBuilder.toFlatSQLException(FbExceptionBuilder.java:384)
at org.firebirdsql.gds.ng.wire.AbstractWireOperations.readStatusVector(AbstractWireOperations.java:152)


What is the correct syntax to delete data from an external table indicated via dsn odbc?

 

[amorosik]

Are we sure that we can indicate a table form Db1 and/or Db2, outside the main db???

Something like:

Db1="[ODBC;Dsn=peppe1;Database=C:\peppa1.db]"
Db2="[ODBC;Dsn=peppa2;Database=C:\peppa2.db]""

MyDB.ExecNonQuery("INSERT INTO " & Db1 & ".articoli  select * FROM " & Db2 & ".articoli")

(Db1.articoli and Db2.articoli same schema)

 

[DonManfred]

As far as i know you CAN NOT mix two (or more) different Databasesystems in one Query.

You can mix different MySQL-Databases (as an Example) if all Databases are on the same server and the user referenced with username and password has access to all of them.
Same is true for other Databases.

Use two different SQL-Objects.
One for the Firebird-Connection.
One for the SQLitedatabase.

Depending on what you want to transfer:
Fetch data from DB A (using DB A´s ODBC-Object) and store it in B (using DB B´s ODBC-Object).
Fetch data from DB B (using DB B´s ODBC-Object) and store it in A (using DB A´s ODBC-Object).

 

[amorosik]

As far as i know you CAN NOT mix two (or more) different Databasesystems in one Query.

You can mix different MySQL-Databases (as an Example) if all Databases are on the same server and the user referenced with username and password has access to all of them.
Same is true for other Databases.

Use two different SQL-Objects.
One for the Firebird-Connection.
One for the SQLitedatabase.

Depending on what you want to transfer:
Fetch data from DB A (using DB A´s ODBC-Object) and store it in B (using DB B´s ODBC-Object).
Fetch data from DB B (using DB B´s ODBC-Object) and store it in A (using DB A´s ODBC-Object).

Yes, this is the classic way, but slow
So I understand that with B4J systems it is not possible to use a table of an external db external to the main connection

 

[Erel]

If you find a jdbc driver that supports such feature then it would be possible. It is not handled by B4J.
However you are unlikely to find one.

 

[amorosik]

The main db is Firebird
The secondary db, the one on which I would like to write is Sqlite
I'm using the same odbc drivers that I use on Microsoft Access where I created an example to understand if the query with indication of the external db (Sqlite) was possible And on Access vba code the query works
This makes me think that the drivers used, with another programming environment, allow you to make the query with an external db
But maybe I didn't quite understand what you are writing to me, so if you are not referring to the odbc drivers to access Firebird and Sqlite please let me understand what you are referring to

 

[DarkoT]

As far as i know you CAN NOT mix two (or more) different Databasesystems in one Query.

You can mix different MySQL-Databases (as an Example) if all Databases are on the same server and the user referenced with username and password has access to all of them.
Same is true for other Databases.

Use two different SQL-Objects.
One for the Firebird-Connection.
One for the SQLitedatabase.

Depending on what you want to transfer:
Fetch data from DB A (using DB A´s ODBC-Object) and store it in B (using DB B´s ODBC-Object).
Fetch data from DB B (using DB B´s ODBC-Object) and store it in A (using DB A´s ODBC-Object).

I will go in different way - as already @DonManfred suggested... .

1. Open connection to Firebird
2. Open next connection to Sqlite database
3. Read data from Firebird and store into resultset
4. Write or delete data in SqlLite database

you can use transaction logic to be on safe site; but you cannot use transaction between two different connections...

 

[amorosik]

I will go in different way - as already @DonManfred suggested... .

1. Open connection to Firebird
2. Open next connection to Sqlite database
3. Read data from Firebird and store into resultset
4. Write or delete data in SqlLite database

you can use transaction logic to be on safe site; but you cannot use transaction between two different connections...

Yes of course, as I wrote to Don Manfred, it's the classic route
But if you need to copy data from a single table with 100 fields, you need to list all the individual field names
What if the tables to be copied were 30 instead of just one?
While having the possibility to use the 'Insert Into' and indicating an external db/table, if the db structure were the same there is no need to list any field names

 

[Daestrum]

You need to check if the databases you are using allow 'Linked Servers'.

 

[DarkoT]

Yes of course, as I wrote to Don Manfred, it's the classic route
But if you need to copy data from a single table with 100 fields, you need to list all the individual field names
What if the tables to be copied were 30 instead of just one?
While having the possibility to use the 'Insert Into' and indicating an external db/table, if the db structure were the same there is no need to list any field names

In this case I will suggest writing routine in B4J which will give you structure of table(s) from source database, create exact table with same structure in destination table... After this you will be able to read data from source database/table(s) and store data into destination database/table(s)... I cannot see another option... On some database (like ms sql server) you can link different server (add_linked_server) over ODBC; but I'm sure that is not possible to do with firebird or/and sqlite...

 

[amorosik]

I cannot see another option

but I'm sure that is not possible to do with firebird or/and sqlite...

The following line, when used from vba code, is fully functional
And this proves that the problem is not with the db servers or their odbc drivers

CurrentDb.execute "INSERT INTO [ODBC;Driver={SQLite3 ODBC Driver};Dsn=peppe1;Database=C:\peppa1.db].Articoli select * FROM Articoli "

 

[DonManfred]

or their odbc drivers

wrong assumption. Your code looks like VB is able to mix different databases. In fact vb IS using something that allow you to do that.
Seems to be part of the vb-driver. That does not mean it works everywhere.

 

[amorosik]

wrong assumption. Your code looks like VB is able to mix different databases. In fact vb IS using something that allow you to do that.
Seems to be part of the vb-driver. That does not mean it works everywhere.

Based on what information are you writing this?

 

[DonManfred]

Based on what information are you writing this?

Based on your code where you write that it works in VB.

Looks for me like a VB-Feature; not a "Every Coding-Language" one. that´s all.
Anyway: i´m out here now.

Find a working JAVA-Code and we can help you porting it to B4X.

 

[amorosik]

Let me understand, you are writing that what is requested (the cross-db query) cannot be obtained based on my example where I demonstrate, for the indicated databases and the available drivers, is it possible to obtain the requested result?

 

[Mahares]

I never used Firebird. In fact the only FIrebird I know is a nice automobile that they build in America. If what you are trying to do is tedious, why not export the Firebird table(s) to a CSV file and directly import the CSV into a SQLite table(s). Or is it too simple of a solution to consider a CSV Export/Import.

 

[amorosik]

I never used Firebird. In fact the only FIrebird I know is a nice automobile that they build in America. If what you are trying to do is tedious, why not export the Firebird table(s) to a CSV file and directly import the CSV into a SQLite table(s). Or is it too simple of a solution to consider a CSV Export/Import.

The Firebird db server does not deal with the described problem
But in any case I advise you to evaluate it because it is an excellent db server
The problem is not transferring data from one db to another
I already have the classic function that loops through the db rows and writes to the destination db
I wanted to understand precisely how the programming environment works, whether a function can be done or not

 

[Magma]

The Firebird db server does not deal with the described problem
But in any case I advise you to evaluate it because it is an excellent db server
The problem is not transferring data from one db to another
I already have the classic function that loops through the db rows and writes to the destination db
I wanted to understand precisely how the programming environment works, whether a function can be done or not

@amorosik I think that "every" programming environment who respects "itself" at the limits of security could not do that for our safety!

From the other hand ODBC drivers and any database Driver has to do more with the Database and the System architecture.

For me it is better to loop the records-rows and read and write the columns from one to other...
If we are talking for two databases in the same disk / system then you will go with your way... your thought

....
for example:
If the security and protocols mixxed that way you want... it will be easy to transfer files from pc to pc using simple smb through internet... note: that sometimes this works.. but is it the right and secure way ? other example... MDB (access data bases) are possible to edited, insert new records through simple-file-sharing but is also possible to work with ODBC drivers too...
How dangerous are all these... in our days...