Android Question Copying table from a database to another

DT1111

Member
Licensed User
Longtime User
Hi

I have tried the following code attempting to copy a table from a main database (main.db) into a backup database (backup.db).

SQL.NonExecQuery("SELECT * INTO CustomersBackup IN 'backup.db' FROM Customers")

and the result was an error which says 'SQLiteException: near "INTO".'

Is the SQL syntax correct in b4a?

Thanks.
 

mangojack

Well-Known Member
Licensed User
Longtime User
not an expert but try this .. I'm assuming your table names are string variables
B4X:
    SQL.NonExecQuery("SELECT * INTO " & CustomersBackup & " IN 'backup.db' FROM " & Customers &")
 
Upvote 0

DT1111

Member
Licensed User
Longtime User
Hi mangojack

CustomersBackup and Customers are the name of the respective tables and they are not string variables.
 
Upvote 0

mangojack

Well-Known Member
Licensed User
Longtime User
shouldn't it be .. "ExecQuery"
B4X:
SQL.ExecQuery("SELECT * INTO CustomersBackup IN 'backup.db' FROM Customers")

also .. 'NonExecQuery' should be 'ExecNonQuery'

Thats it from me ... Cheers :)
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
Here is the complete code you need to accomplish your result:
B4X:
Dim DBFilePath As String    = File.DirRootExternal  
      Dim DBFileName As String="main.db"
    
      If SQL1.IsInitialized =False Then
        SQL1.Initialize(DBFilePath,DBFileName,False)  'initialize main database
     End If     

     Dim txt As String
     Dim DBTableName As String="Customers"
     Dim DBFileNameBU As String="Backup.db"
     Dim DBTableNameBU As String="CustomersBackup"

     txt="ATTACH DATABASE '" & File.Combine(DBFilePath,DBFileNameBU)  & "' AS BU"
     SQL1.ExecNonQuery(txt)    'attach to the backup database
    
     txt="DROP TABLE IF EXISTS BU." & DBTableNameBU
     SQL1.ExecNonQuery(txt)  'Delete backup table if it exists
    
     txt="CREATE TABLE IF NOT EXISTS BU." & DBTableNameBU & " AS SELECT * " _
     & "FROM " & DBTableName 
     SQL1.ExecNonQuery(txt)    'create backup table in backup database
 
Upvote 0

DT1111

Member
Licensed User
Longtime User
Thanks very much Mahares and Erel

The codes work very well! I have modified the following to copy only certain group of data. It works.

B4X:
    txt="CREATE TABLE IF NOT EXISTS BU." & DBTableNameBU & " AS SELECT * " _
    & "FROM " & DBTableName & " WHERE Type = 1"
    SQL1.ExecNonQuery(txt)    'create backup table in backup database

Now I want to take it a step further where I want to later append or add another set of data (eg. Type = 2) to the backup table. So deleting backup table is not necessary in this case. Am I right?

B4X:
    txt="DROP TABLE IF EXISTS BU." & DBTableNameBU
    SQL1.ExecNonQuery(txt)  'Delete backup table if it exists

How do I append new data to the backup table?

Thanks again.
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
Do not delete the backup table if you are going to insert records in it. To insert records in the back up table you can do something like this example:
B4X:
txt="INSERT INTO BU. " & DBTableNameBU & " (City, Country) VALUES(?,?)"
SQL1.ExecNonQuery2(txt, Array As Object("Brasilia","Brazil"))    'insert into backup table in backup database
 
Upvote 0

3394509365

Active Member
Licensed User
Longtime User
I can, by selecting the name of a table in my db I can stick it under a different name in the same db and taking away all the data. virtually have two tables with the same name but different?
 
Upvote 0

Mikonios

Active Member
Licensed User
Longtime User
Thank you for sharing Mahares.
Only a small detail. If applied to a table with BLOB fields, the definition of the BLOB field is lost. Closing the databases and reopen an error it is generated on the .db

Fixed a table by schema creation and insertion in direct mode ::

B4X:
            Dim Qry, Qry0, Qry1, Qry2 As String
            ' Make Table with Estruct -------------------------------------
                ' Get SchemaDeSource
                Dim Schema As String = TableSchemaImport(SrcDir, SrcFile, TblName)
                
            ' ATTACH Tabla EnDestino -------------------------------------
                ' https://www.b4x.com/android/forum/threads/copying-table-from-a-database-to-another.42139/
            
                ' ATTACH DATABASE '/storage/emulated/0/..../..../.....db/....' AS 'tmp'
                    Qry0 = "ATTACH DATABASE '" & File.Combine(SrcDir, SrcFile)  & "' AS 'tmp';"
                ' CREATE TABLE IF NOT EXISTS [10DatosProv] AS SELECT * FROM tmp.[10DatosProv]
                     'Qry1 = "CREATE TABLE IF NOT EXISTS [" & TblName & "] AS SELECT * FROM tmp.[" & TblName & "];"
                     Qry1 = "INSERT INTO [" & TblName & "] (SELECT * FROM tmp.[" & TblName & "]);"
                ' DETACH DATABASE tmp
                    Qry2 = "DETACH DATABASE tmp;"

SQLNT.ExecNonQuery(Schema) ' CREATE TABLE
SQLNT.ExecNonQuery(Qry0) ' ATTACH DATABASE
SQLNT.ExecNonQuery(Qry1) ' ISRT DATOS
SQLNT.ExecNonQuery(Qry2) ' DETACH DATABASE
TableCreaIdx(SrcDir, SrcFile, TrgDir, TrgFile, TblName, SQLCT, SQLNT)
SQLNT.close
SQLNS.close


B4X:
Sub TableSchemaImport(BBDDDir As String, BBDDFile As String, TableName As String)

    Dim MiResult As String
    Dim Qry As String = "SELECT sql FROM sqlite_master WHERE type='table' AND name='" & TableName & "'"

    If Main.Cifrado Then
        Dim SQLCT As SQLCipher    :    SQLCT.Initialize(BBDDDir, BBDDFile, True, Main.DBPass, "")
        Dim Schema As List        =     ExecuteMemoryTableC(SQLCT, Qry, Null, 1)
            SQLCT.close
    Else
        Dim SQLNT As SQL        :     SQLNT.Initialize(BBDDDir, BBDDFile, False)
        Dim Schema As List        =     ExecuteMemoryTable(SQLNT, Qry, Null, 1)
            SQLNT.close
    End If
    
    If Schema.Size >= 1 Then
        Dim Results() As String = Schema.Get(0)
        MiResult = Results(0)
    Else
        MiResult = "SINDATO"
    End If

    Return MiResult
End Sub
 

Attachments

  • FormatOk.png
    FormatOk.png
    66.2 KB · Views: 322
  • FormatNotOk.png
    FormatNotOk.png
    88.9 KB · Views: 299
Last edited:
Upvote 0
Top