Sometimes I just want to dump the contents of a table in a database and don't want to go to the trouble of writing a full blown program to do it so I tossed together a small routine for myself. Hope it helps someone.
I am sure everyone can do it better and i am used to that. PB 4.30 Final.
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
Amateur Radio/VK3HAF, (D-STAR/DMR and more), Arduino, ESP32, Coding, Crochet
Fangles, you could try using this to find out what is in the DB to as an option to dump all even if you don't know whats there. Try taking a look at the contents of:
SELECT * FROM sqlite_master
Paul Dwyer
“In nature, it’s not the strongest nor the most intelligent who survives. It’s the most adaptable to change” - Charles Darwin
“If you can't explain it to a six-year old you really don't understand it yourself.” - Albert Einstein
Fangles, you could try using this to find out what is in the DB to as an option to dump all even if you don't know whats there. Try taking a look at the contents of:
SELECT * FROM sqlite_master
I've done that before in my full sqlite retrieval module but haven't had time to rehack it for pb 4,3.
This is just a throwaway for anyone needing a fast dump. (Should I rephrase that???)
Don't want or need to look, just dump (damn, that sounds even ruder).
dump=export, maybe I should use that word:)
Amateur Radio/VK3HAF, (D-STAR/DMR and more), Arduino, ESP32, Coding, Crochet
reading your posts are a laxative all by themselves
Paul Dwyer
“In nature, it’s not the strongest nor the most intelligent who survives. It’s the most adaptable to change” - Charles Darwin
“If you can't explain it to a six-year old you really don't understand it yourself.” - Albert Einstein
“In nature, it’s not the strongest nor the most intelligent who survives. It’s the most adaptable to change” - Charles Darwin
“If you can't explain it to a six-year old you really don't understand it yourself.” - Albert Einstein
Geez
Had me going there for a bit, I though you were offended!
Paul Dwyer
“In nature, it’s not the strongest nor the most intelligent who survives. It’s the most adaptable to change” - Charles Darwin
“If you can't explain it to a six-year old you really don't understand it yourself.” - Albert Einstein
“In nature, it’s not the strongest nor the most intelligent who survives. It’s the most adaptable to change” - Charles Darwin
“If you can't explain it to a six-year old you really don't understand it yourself.” - Albert Einstein
In this version, you don't need to know any of the tables names. it will process all tables and columns and dump each table to a disk file with the base name of the current table.
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
Amateur Radio/VK3HAF, (D-STAR/DMR and more), Arduino, ESP32, Coding, Crochet
reading your posts are a laxative all by themselves
am I missing something here?
