PureBasic Forums - English

Code: Select all

UseSQLiteDatabase()                                                                                 ; Use the SQLite database environment

Global DbHandle                                                                                        ; Make the database handle global

Declare  FastDataExport(QueryString.s, BaseFileName.s)                     ; Declare the export routine so we can put it anywhere

QueryString.s = "SELECT * FROM addresses"                                       ; Create your replaceable query string
;QueryString.s = "SELECT * FROM addresses WHERE first='John'     ; Create your replaceable query string
;QueryString.s = "SELECT * FROM addresses WHERE category='fireman'     ; Create your replaceable query string

BaseFileName.s =  "My Address Book"                                                    ; Whatever the base export name should be

DbName.s = OpenFileRequester("Open Database", "", "SQLite database file (*.db)|*.db|All files (*.*)|*.*", 0)

If DbName.s                                                                                                ; Did the user choose a file or cancel?
                                                                                                                     ; Open the existing database in Read Only mode
  DbHandle = OpenDatabase(#PB_Any, DbName.s, "", "", #PB_Database_SQLite)  ; DbHandle is checked in procedure
  FastDataExport(QueryString.s, BaseFileName.s)                                  ; Now attempt to export the data
EndIf                                                                                                            ; No valid database name or user cancelled

End

Procedure FastDataExport(QueryString.s, TableName.s)
  If DbHandle                                                                                                ; Was the database handle valid and not empty?
    FileId  = CreateFile(#PB_Any,  TableName.s + "_Export.Csv")         ;  Try to create the file to export to in the program directory
    If FileId                                                                                                     ;  Did we get a filename to open?
      If DatabaseQuery(DbHandle, QueryString.s)                                       ;  Now try to run the database query passed to this procedure
        NumColumns = DatabaseColumns(DbHandle)                                 ;  Get the number of columns that we have to parse
        If NumColumns                                                                                     ;  Did we get any columns returned?
          While NextDatabaseRow(DbHandle)                                               ;  Now select each returned row to operate on
            For NumStart = 0 To NumColumns                                               ;  Now process each column in the current row
              If NumStart <> NumColumns                                                        ; Have we reached the end yet?
                CurrentLine.s + Chr(34) + GetDatabaseString(DbHandle, NumStart) + Chr(34) + ","
              Else                                                                                                ; Yes we have, leave out the final delimiter
                CurrentLine.s + Chr(34) + GetDatabaseString(DbHandle, NumStart) + Chr(34) 
              EndIf                                                                                              ; Nothing else to test here
            Next                                                                                                  ; Next column to test
            WriteStringN(FileId, CurrentLine.s)                                                ;  Write the current string to the opened disk file with CR/LF
            CurrentLine.s = ""                                                                             ;  Zero the output string so we don't accumulate trash in it
          Wend                                                                                                  ;  Process the next row
          FinishDatabaseQuery(DbHandle)                                                     ;  Flush the current query
        EndIf                                                                                                     ;  We didn't get any columns in the table to process
      EndIf                                                                                                       ;  The current query failed to be executed
    EndIf                                                                                                         ;  We could not open or create a file to write to. Disk error?
  EndIf                                                                                                           ; Database handle empty or invalid
EndProcedure

Code: Select all

Structure ProgramData
  DbHandle.i
EndStructure

Global NewList TableName.s(), Program.ProgramData

UseSQLiteDatabase()

DbName.s = OpenFileRequester("Open Database", "", "SQLite database file (*.db)|*.db|All files (*.*)|*.*", 0)

If DbName.s
  Program\DbHandle = OpenDatabase(#PB_Any, DbName.s, "", "", #PB_Database_SQLite)
  If Program\DbHandle
    If DatabaseQuery(Program\DbHandle, "Select name FROM sqlite_master WHERE type='table' ORDER BY name")
      While NextDatabaseRow(Program\DbHandle)
        CurrentTable.s = GetDatabaseString(Program\DbHandle, 0)    ; Table name
        If TableName.s <> "sqlite_sequence"
          AddElement(TableName.s())
          TableName.s() = CurrentTable.s
        EndIf
      Wend
      FinishDatabaseQuery(Program\DbHandle)
    EndIf
    If ListSize(TableName.s())
      ForEach TableName.s()
        If TableName.s() <> "sqlite_sequence"      
          FileId  = CreateFile(#PB_Any,  TableName.s() + "_Export.Csv")
          If FileId
            If DatabaseQuery(Program\DbHandle, "SELECT * FROM '" + TableName.s() + "'")
              NumColumns = DatabaseColumns(Program\DbHandle)
              If NumColumns
                While NextDatabaseRow(Program\DbHandle)
                  For NumStart = 0 To NumColumns
                    If NumStart <> NumColumns
                      CurrentLine.s + Chr(34) + GetDatabaseString(Program\DbHandle, NumStart) + Chr(34) + ","
                    Else
                      CurrentLine.s + Chr(34) + GetDatabaseString(Program\DbHandle, NumStart) + Chr(34)
                    EndIf
                  Next
                  WriteStringN(FileId, CurrentLine.s)
                  CurrentLine.s = ""
                Wend
                FinishDatabaseQuery(Program\DbHandle)
              Else
                Debug "No columns found inside this table to process"
              EndIf
            Else
              Debug "Couldn't get the data from the current table"
            EndIf
            CloseFile(FileId)
          Else
            Debug "Could not open the export file to write to"
          EndIf
        Else
          Debug "We can't be bothered dumping the SQLite sequence file"
        EndIf
      Next
    Else
      Debug "There were no tables inside the opened database"
    EndIf
  Else
    Debug "The database was not able to be opened"
  EndIf
Else
  Debug "No database name supplied or user cancelled"
EndIf
End