Create a text file from the content of sqlite databases ?

Hi,

I’ve to write a small app that extracts the content of all the sqlite databases located in a single folder and put their contente in a single text file. The result in the text file should be something like that :

Text file creation date : dd/mm/yy
Database name : LaserDB.rsd
LaserDB.rsd Travaux 59 2010-03-02 100302e 1.4301 6.00 20002.2 20 0.41 0.358
LaserDB.rsd Travaux 60 2010-03-02 100302e 1.4301 6.00 20002.1 10 0.09 0.031
LaserDB.rsd Travaux 61 2010-03-02 100302e 1.4301 6.00 19966.2 105 0.08 0.026
LaserDB.rsd Travaux 100 2010-03-05 100305b 1.4301 5.00 465-270 1 0.62 4.928
LaserDB.rsd Travaux 101 2010-03-05 100305b 1.4301 5.00 460-315 1 0.65 5.687

Database name : magnificat.sqlite
magnificat.sqlite Hint 24 23 67
magnificat.sqlite Hint 60 12 12

End of file creation on jj/mm/yy

The first column of this text file is the database name, the second one is the table name, then the content of the record.
This file will enclosed as line as records in all the databases in the folder.

But in fact I still have the content of the first database. If my folder enclosed 4 sqlite databases, I’ve got 4 groups in my text file but this is all the time the content of the first database as you cab see below :

Text file creation date : dd/mm/yy
Database name : LaserDB.rsd
LaserDB.rsd Travaux 59 2010-03-02 100302e 1.4301 6.00 20002.2 20 0.41 0.358
LaserDB.rsd Travaux 60 2010-03-02 100302e 1.4301 6.00 20002.1 10 0.09 0.031
LaserDB.rsd Travaux 61 2010-03-02 100302e 1.4301 6.00 19966.2 105 0.08 0.026

Database name : magnificat.sqlite
magnificat.sqlite Travaux 59 2010-03-02 100302e 1.4301 6.00 20002.2 20 0.41 0.358
magnificat.sqlite Travaux 60 2010-03-02 100302e 1.4301 6.00 20002.1 10 0.09 0.031
magnificat.sqlite Travaux 61 2010-03-02 100302e 1.4301 6.00 19966.2 105 0.08 0.026

End of file creation on jj/mm/yy

Please help me to understand where is the bug in my code :

dim srcfolder as FolderItem 'Folder choosen by the user
dim nbfiles,i,j,nbcol as integer 'nbfile = # of in the selected folder. nbcol = # of columns in the current table
dim chaine as String 'to concatenate the data from the current record to add it in the text file

Dim ftext as FolderItem 'to manage the text file access
Dim fstream as TextOutputStream

dim baseLA as new REALSQLDatabase ’ current database
dim rsTable,rsColumn as RecordSet ’ to fetch all the records and columns of the current table

'1- Lest’s choos the folder where are located the databases files
'=======================
srcfolder=selectfolder
if srcfolder=nil then
msgbox (“cancel by user”)
exit
end if
msgbox "folder name : "+srcfolder.name

'folder is selected !

'2- Text file creation
'=======================
ftext=GetSaveFolderItem(“TEXT”,“extractDatabase.txt”)

if ftext =nil then
msgbox (“Cancel by user”)
exit
end if
fstream=TextOutputStream.Create(ftext)
fstream.Writeline “fichier cr le …”

’ 3- Let’s open all the files enclosed in the selected folder
'=======================
nbfiles =srcfolder.Count
for i=1 to nbfiles
fstream.writeline "Database name : "+srcfolder.Item(i).name
’ let’s open the database

BaseLA.databaseFile=GetFolderItem(srcfolder.Item(i).AbsolutePath)
if BaseLA.databaseFile=nil or not baseLA.DatabaseFile.Exists then
msgbox “databasefile=nil”
end if
'3.1 - Let’s connect to the database
'--------------------------
if BaseLA.Connect() = false then
MsgBox "Error database : " + str(BaseLA.ErrorCode) + EndOfLine + EndOfLine + BaseLA.ErrorMessage // there was an error connecting to the database
'fstream.Writeline "db connection error "+srcfolder.Item(i).Name
'fstream.Close
continue
end if

rsTable=BaseLA.TableSchema 'let’s retrieve the list of tables enclosed in this database

'3.2 Let’s parse each table of the current database
'--------------------------
while not rsTable.eof 'while there is tables in the current database

rsColumn=BaseLA.SQLSelect("select * from "+rsTable.IdxField(1).StringValue) 'Let’s retrieve all the records from the current database
if rsColumn <>nil then
'3.3 Let’s parse each column of the current record enclosed in rsColumn
'--------------------------
while not rsColumn.eof 'We fetch all the reconds from the current table

nbcol=rsColumn.FieldCount 'How many columns in this record
chaine=srcfolder.Item(i).name+chr(9)+rsTable.IdxField(1).StringValue 'db name + table name

for j=1 to nbcol 'All the columns content of the current record are concatenated
chaine=chaine+chr(9)+rsColumn.IdxField(j).StringValue
next

fstream.Writeline chaine 'let’s write the current record in the text file
rsColumn.movenext
wend 'let’s go to the next record of the current table

rsColumn.close 'we close the table
end if

fstream.Writeline “” 'add a newline in the text file before writing the next table content
rsTable.MoveNext
wend 'let’s go to the next table of the current db

rsTable.close 'we close the current table
next ’ go to the next database file

'4- Close the text file
fstream.Writeline “fichier termin le …”
fstream.Close

I think you need to close baseLA before you reuse it. Connect detects that the database is still open and simply returns, regardless of the fact that you have assigned a different file to it. The old one is still open, so that’s the one that gets used.

rsTable.close
BaseLA.close
next ' go get the next database file

Or, better, BaseLA = new SQliteDatabase. Create a fresh new object each time.

Thanks. It works fine when I add the “BaseLA.close”
But a quick question : If I enter BaseLA = new SQLiteDatabase.
Does it means that I’ve to free the memory of the previous object before creating a new object

No. It is freed automatically.

Perfect. Thanks again for your help