Copy from old db into new and create table in one hit

Fangbeast · Post by **Fangbeast** » Mon Apr 20, 2009 11:51 am

; Creates a new database or use an existing one. Attaches another database to it and copies the table structure and data to the main database opened. Then detaches the second database.

; Turn on the SQLite engine support in PureBasic

UseSQLiteDatabase()

; Create a global alias for the data structure

Global DatabaseHandle, CurDir.s = GetCurrentDirectory()

; All your procedures have to be declared if they are going to be called out of sequence

Declare   CopyFromDatabase()

; Create the database if you can. Give an error if not, then exit

Procedure CopyFromDatabase(NewTable.s, OldTable.s)
  ; Open or create the new database in the current directory
  FileHandle = OpenFile(#PB_Any, CurDir.s + "MainDatabase.ptk") ; Opens if exists, creates if not.
  ; Did we get a valid file handle?
  If FileHandle     ; Let PB create file handles, don't try to force them or you will lose track
    ; Okay, we got a file handle, close the file now
    CloseFile(FileHandle)
    ; Try to open the database file using the database commands
    DatabaseHandle = OpenDatabase(#PB_Any, CurDir.s + "MainDatabase.ptk", "", "") ; Let PB create database handles too
    ; Did we get a database handle?
    If DatabaseHandle
      ; Attach the needed database for copying from (or anything else)
      DatabaseUpdate(DatabaseHandle, "ATTACH DATABASE '" + CurDir.s + "Flooble.ptk' AS ExternalDb")
      ; Autocreate the new table from the contents of the old table and copy the data to it
      DatabaseUpdate(DatabaseHandle, "CREATE TABLE " + NewTable.s + " As Select * FROM ExternalDb." + OldTable.s)
      ; Detach the old database
      DatabaseUpdate(DatabaseHandle, "DETACH DATABASE '" + CurDir.s + "Flooble.ptk'")
      ; Oh dear, we have a conniption
    Else    ; We couldn't get a database handle, opening failed!
      MessageRequester("Database open error", "There was a serious problem attempting to connect to the system database, try another one", #PB_MessageRequester_Ok|#MB_ICONEXCLAMATION)
      End
    EndIf 
  Else    ; We couldn't open or create the physical database file!!
    MessageRequester("Database open error", "There was a serious problem attempting to open the system database, try another one", #PB_MessageRequester_Ok|#MB_ICONSTOP)
    End
  EndIf
EndProcedure

; Run the procedure with the needed table names. Lots of other ways this could be done of course.

CopyFromDatabase("contacts", "addresses")

End