• Welcome to SQLitening Support Forum. Please login.
 
January 17, 2022, 09:52:49 PM

News:

Welcome to the SQLitening support forums!


copy table from one db to another

Started by Bern Ertl, February 25, 2009, 12:03:06 PM

Previous topic - Next topic

David Kenny

May 02, 2009, 04:51:45 PM #15 Last Edit: May 02, 2009, 04:55:20 PM by David Kenny
Fred,

I am trying to copy a table from a remote DB to a local DB.
Does it make sense that I can't get this to work using slAttach and SQLite statements?

I can copy the table from remote DB to remote DB, or from local DB to local DB using information gleaned from Bern's posts and my own tinkering.
I'm thinking I will need to read the remote table into memory, switch to local, and then write the local table manually.

I can show my test routines with their results if that is helpful.

Thanks,

David

Fred Meier

Yes, you can access data in both remote and local database in the same
program by using slPushDatabase, slPopDatabase and slSetProcessMods with the "L" modchar. 

Attach will not work as you determined.  The code can be a little hard to
follow and you need to be careful because misuse of the slSetProcessMods
with the "L" modchar may cause a GPF. 

Even though the code looks cumbersome it is very fast.  Both slPopDatabase
and slSetProcessMods with the "L" modchar are processed in the local
SQLitening.Dll only, so no additional network traffic. 

Below is a rut that copies the records in table T2 from a remote database
to a local one. 

Sub CopyTable

   Local lsSaveVAlue as String
   Local lsRemoteDB, lsLocalDB as String

   ' Connect, open, and save remote database handle
   slConnect
   slOpen "Test.Sld"
   slPushDatabase lsRemoteDB

   ' Switch libs, open, and save local database handle
   slSetProcessMods "L0"         ' switch to local lib         
   slOpen "C:\SQLitening\Test\TestLocal.Sld
   slPushDatabase lsLocalDB

   ' Create table on local and begin tran
   slPopDatabase lsLocalDB
   slExe "Create Table if not exists T2 (F1)"
   slExe "Delete from T2"
   slExe "Begin"

   ' Switch to remote and select from input table
   slPopDatabase lsRemoteDB
   slSetProcessMods "L1"         ' switch to remote lib     
   slSel "Select F1 from T1"
   do while slGetRow
      lsSaveValue = slFN("F1")

      ' Switch to local and insert
      slPopDatabase lsLocalDB
      slSetProcessMods "L0"         ' switch to local lib     
      slExe "Insert into T2 values(" & lsSaveValue & ")"

      ' Switch to remote for next record
      slPopDatabase lsRemoteDB
      slSetProcessMods "L1"         ' switch to remote lib     
   loop

   ' Switch to local and end tran
   slPopDatabase lsLocalDB
   slSetProcessMods "L0"         ' switch to local lib     
   slExe "End"

End Sub

David Kenny

Fred,

Thanks for the confirmation about using slAttach. And a big thank you for the CopyTable code!  ;D

I modified it to copy any table sent to it.  I hope this is useful to someone else.

Sub CopyTable (RemoteDBName As String,LocalDBName As String ,TblName As String)
          'Remember DB names can include relative paths (and absolute path in the case of a local DB) (see SLQLightening docs)
          'This routine copies from the Remote to the Local DB but could be modified to go the other direction.
         
   Local lsSaveVAlue    As String
   Local lsRemoteDB, lsLocalDB As String
   Local TblDef         As String
   Local ColumnCount    As Integer
   Local I              As Integer
   
   ' Connect, open, and save remote database handle
   slConnect
   slOpen RemoteDBName
   slSel ("Select * from sqlite_master where type='table' and name='" & TblName & "'")
   slGetRow
   TblDef=slFN("SQL")  'Get the original table definition SQL (this way you get primary key, unique fields, etc.)
   slCloseSet

   slSel ("Select * from " & TblName, 0)
   slCloseSet                           
   slPushDatabase lsRemoteDB

   ' Switch libs, open, and save local database handle
   slSetProcessMods "L0"         ' switch to local lib         
   slOpen LocalDBName , "C"
   slPushDatabase lsLocalDB

   ' Create table on local and begin tran
   slPopDatabase lsLocalDB
   slExe "Drop Table If Exists " & TblName
   slExe TblDef         'Create Table using SQL that created the original table
   slExe "Begin"

   ' Switch to remote and select from input table
   slPopDatabase lsRemoteDB
   slSetProcessMods "L1"         ' switch to remote lib     
   slSel ("Select * from " & TblName, 0)
   ColumnCount=slGetColumnCount
   Do While slGetRow
        lsSaveValue=""
        For I=1 To ColumnCount
            lsSaveValue += slF(I) & ","
        Next I                         
        lsSaveValue=Left$(lsSaveValue,-1) 'Clean up the trailing comma.
        ' Switch to local and insert
        slPopDatabase lsLocalDB
        slSetProcessMods "L0"         ' switch to local lib
        slExe "Insert into " & TblName & " values(" & lsSaveValue & ")"
       
        ' Switch to remote for next record
        slPopDatabase lsRemoteDB
        slSetProcessMods "L1"         ' switch to remote lib     
   Loop
   slCloseSet
   ' Switch to local and end tran
   slPopDatabase lsLocalDB
   slSetProcessMods "L0"         ' switch to local lib     
   slExe "End"

End Sub


This Sub now looks up the original SQL code (used to create the Source table) and uses it to create the Destination table.  I needed that to make sure the copied table included the Primary Key, Unique fields, etc. 

David

Bern Ertl

David, if you are looking to create an exact duplicate of a remote database (as opposed to just copying a single table or so), you might be better off writing a SQLiteningProcs rut to .ZIP up the database file and return it to the client for extraction.

David Kenny

That's a good tip Bern.  I didn't need the whole DB this time, but your idea is excellent.

David

Fredrick Ughimi

February 25, 2013, 02:04:49 PM #20 Last Edit: February 25, 2013, 03:11:53 PM by Fredrick Ughimi
What about copying a table from one database to another table in another database in Local Mode?
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

Ah! So simple.

Copy tables from local to local mode:


slOpen ("HospitalProDB.db3") 
    slAttach ("StHospitalProDB.db3", "StDB", "0")
    slExe "CREATE TABLE tblDrugsSetup AS SELECT * FROM StDB.tblDrugsSetup"


This link helped:

http://stackoverflow.com/questions/10471003/copying-table-in-one-db-to-another-db-in-ios-by-sqlite3
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet