Android Question Copying table from a database to another

[DT1111]

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]

not an expert but try this .. I'm assuming your table names are string variables

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

 

[DT1111]

Hi mangojack

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

 

[mangojack]

shouldn't it be .. "ExecQuery"

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

also .. 'NonExecQuery' should be 'ExecNonQuery'

Thats it from me ... Cheers

 

[DT1111]

Thanks mj but that didn't solve the problem either.

 

[Erel]

See the SQLite commands in this answer: http://stackoverflow.com/questions/10471003/copying-table-in-one-db-to-another-db-in-ios-by-sqlite3

 

[Mahares]

Here is the complete code you need to accomplish your result:

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

 

[DT1111]

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.

    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?

    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.

 

[Mahares]

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:

txt="INSERT INTO BU. " & DBTableNameBU & " (City, Country) VALUES(?,?)"
SQL1.ExecNonQuery2(txt, Array As Object("Brasilia","Brazil"))    'insert into backup table in backup database

 

[3394509365]

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?

 

[Erel]

Please start a new thread for this question.

 

[Mikonios]

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

            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

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