[RESOLVED] Copy a structure db sql with CopyDatabase method of cConnection object (vbRichClient)

[VBDevelopper]

Good morning.
I need to copy a physical db sqlite into a memory db. I need to copy only the structure without data. I tried to use the CopyDatabase method of vbRichCliente.cConnection.
But I get "Sorry: no open database".
this is the code

Dim InMemDBCnn As vbRichClient5.cConnection
Set InMemDBCnn = New vbRichClient5.cConnection
InMemDBCnn.CopyDatabase App.Path & "\db\MyPhysical.db"

I have perhaps misunderstood the use?
Thanks

[Arnoutdv]

The CopyDataBase method is to copy a complete database, not only the structure.

The following Python sample shows how you can do it in code.
Steps:
Create new DB, retrieve all table names from source, for each table recreate the structure in the new db
https://gist.github.com/vgarvardt/3272957

[VBDevelopper]

Thank you for your replay. Very helpful

[Schmidt]

I need to copy a physical db sqlite into a memory db. I need to copy only the structure without data. I tried to use the CopyDatabase method of vbRichCliente.cConnection.
...
I have perhaps misunderstood the use?

The CopyDataBase-Method (when you look at it in the Object-Explorer - or alternatively with the "Intellisense-PopUp")
is defined as a Function which returns a cConnection.

So, it does make a "full-copy" (Schema and Data) from an existing File- or InMemory-DB Connection,
into a new (File- or InMemory-DB) - also already returning the new cConection-instance of the new DB (in opened state).

Meaning, you will have to call .CopyDataBase on a successfully *opened* "Source-Connection":

  Set SrcCnn = '... open your SrcCnn from a file-db for example
  '...
  'now make a "full copy" into an InMemory-Destination-Connection
  Dim DstCnn As cConnection
  Set DstCnn = SrcCnn.CopyDataBase() '<- the optional Params are left out, because their defaults are already set, to make an InMem-Copy

If you want your InMem-DB-Copy only "with the Schema" (only the Table-Defs and Index-Defs, without Data),
you could just loop over the Tables in the DstCnn, clearing them of all the Data:

    Dim DstCnn As cConnection
    Set DstCnn = SrcCnn.CopyDatabase()
    
    Dim Tbl As cTable
    For Each Tbl In DstCnn.DataBases(1).Tables
      DstCnn.Execute "Delete From " & Tbl.NameInBrackets
      Debug.Print Tbl.Name
    Next

But this could be somewhat inefficient, in case the Source-FileDB was a bit on the larger side (> 1MB or something).

So, alternatively you could just loop over the Tables (and also the defined Indexes) in the Source-DB -
and then re-create them "from scratch" in the Destination-Connection like this:

    Dim DstCnn As cConnection, Tbl As cTable, Idx As cIndex
    Set DstCnn = New_c.Connection(, DBCreateInMemory)

    For Each Tbl In SrcCnn.DataBases(1).Tables
      DstCnn.Execute Tbl.SQLForCreate
      For Each Idx In Tbl.Indexes
        DstCnn.Execute Idx.SQL
      Next
    Next

HTH

Olaf

[dreammanor]

If you want your InMem-DB-Copy only "with the Schema" (only the Table-Defs and Index-Defs, without Data),
you could just loop over the Tables in the DstCnn, clearing them of all the Data:

If a parameter such as OnlyCopySchema could be added to the CopyDataBase-method, that would be great.

[Schmidt]

If a parameter such as OnlyCopySchema could be added to the CopyDataBase-method, that would be great.

Probably not going to happen for several reasons:
- CopyDataBase suggests (MethodName-wise) a "full copying" of the DB (and it does so under the covers, by using the page-wise operating SQLite-Backup-API)
- the code to implement your own "Schema-Copy"-function (using the last snippet in post #4 above) is quite short
- the problem does (IMO) not really exist, because one can always ship ones App with a (Schema-wise complete, but data-wise empty) "DB-to-copy-from" in a resource-blob or -folder.

HTH

Olaf

[dreammanor]

Probably not going to happen for several reasons:
- CopyDataBase suggests (MethodName-wise) a "full copying" of the DB (and it does so under the covers, by using the page-wise operating SQLite-Backup-API)
- the code to implement your own "Schema-Copy"-function (using the last snippet in post #4 above) is quite short
- the problem does (IMO) not really exist, because one can always ship ones App with a (Schema-wise complete, but data-wise empty) "DB-to-copy-from" in a resource-blob or -folder.

HTH

Olaf

Well, what you said makes a lot of sense.

[VBDevelopper]

Thanks for all.