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