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

amorosik · May 23, 2023

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:

B4X:

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

amorosik · May 23, 2023

Magma said:
@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...

"...For me it is better to loop.."
You could say better if other choice was also possible
But currently this is the only possible choice

"...or example: if the security and protocols mixxed that way you want..."
Security has nothing to do with functionality
It would be like considering a scalpel dangerous
In the hands of a skilled surgeon a sharp scalpel is indispensable

Magma · May 23, 2023

amorosik said:
"...For me it is better to loop.."
You could say better if other choice was also possible
But currently this is the only possible choice

"...or example: if the security and protocols mixxed that way you want..."
Security has nothing to do with functionality
It would be like considering a scalpel dangerous
In the hands of a skilled surgeon a sharp scalpel is indispensable

Sometimes... a fast result coming from loop (sometimes)...
or a Simple Fast Command hides some loops that we are not seeing it...

...I am talking only for network-database security... not for a surgery

Lately we need OAuth, auth generally, token, TLS, encryption, SSL, PGP...

all these technologies came to stay and we must use them... if want our data to be safe.. and private...

MrKim · May 31, 2023

amorosik said:
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

I doubt it will work. MSAccess/VBA can basically read every driver that is registered in windows and use it because MS got to set the standardn200 years ago. People write for windows so it works. They don't write the drivers for java so you need something specifically for java/B4J.

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

amorosik

Expert

amorosik

Expert

Magma

Expert

MrKim

Well-Known Member

Similar Threads

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

amorosik

Expert

amorosik

Expert

Magma

Expert

MrKim

Well-Known Member

Similar Threads

Privacy & Transparency

Privacy & Transparency