XML files

The xml files will be simple, because if for no other reason I am not all that a proficint XMLer.

Delivery Route XSD

Before XML

Well, not wanting to wait on doing first things first - like creating xml data schemas...how about a throw back to the day. Meaning that for the moment I think we want to create some text files that will load our truck database. I have gone ahead and created an admin.odb file, with a basic structure the same as the driver.odb database. In this I will just add text tables that are identical to the tables to be exported to each driver.odb file. These would be Customers, Categories, Products, Routes and Employees.

With the HSQL engine exporting a table to a text file could not be easier. These couple of basic lines wll do this just fine. <syntachighlight lang="oobas">> Sub Main

   BasicLibraries.LoadLibrary("Drews")
   ' the library just supplies the getConnection routine
   exportTables()

End Sub

sub exportTables dim conn

   conn = getConnection( "admin" )
   buildTextTable( Conn, "Categories" )
   buildTextTable( Conn, "Products" )
   buildTextTable( Conn, "Employees" )
   buildTextTable( Conn, "Customers" )
   buildTextTable( Conn, "Routes" )
   conn.dispose

end sub

sub buildTextTable( aConn as variant, aTblName as variant ) dim aTbl as variant dim stmnt dim strTableName as string dim strCmd as string

   aTbl = aConn.Tables.GetByName( aTblName )
   stmnt = aConn.createStatement
   strTableName = "txt_" & aTbl.Name
   

rem first remove the table from ODB file rem this does not seem to actually DROP the table

   if aConn.Tables.HasByName( strTableName ) then
       aConn.Tables.DropByname( strTableName )
   end if
   

rem drop the table rem which should free any links to the csv file

   stmnt.executeUpdate( "DROP TABLE " & strTableName & " IF EXISTS")
   

rem now delete the csv file

   tmpStr =  "c:/Documents and Settings/andrew/My Documents/QueenCity/" & lcase( strTablename ) & ".csv"
   if fileexists( tmpStr ) then
   	kill tmpStr
   end if    
   

rem finally rebuild it

   stmnt.Escapeprocessing = False
   stmnt.execute( "SELECT * INTO TEXT """ & strTableName _
                   & """ FROM """ & aTbl.Name & """" )

end sub </syntachighlight>

Well, almost just fine. When the routine is done there are four updated files in the same directory as the admin.odb file: txt_categories.csv, txt_customers.csv, txt_products.csv and txt_employees.csv, txt_routes.csv. ( one note on this - if the table being exported is empty, then that file will not be created. )

There is a subtle bug in this code...or in Base? If the file admin.odb is not open when this libraries main function is run it runs without a problem, as many times as you care to run it.

However - if admin.odb is open ( this is for an embedded database at least ) then the following happens. The first time you run this code it runs fine. You get the text tables and the text files all created. Now the second time you run it an error will occur - stating that the insert into routine failed, because the text table already exists. Here is how to fix it - after the routine is run you must refresh the table view in the Base main window. If you don't do this the line aConn.Tables.HasByName fails to find the file. I suppose filing an issue is in order, mean time I will look for the proper command to use in a dispatch statment to refresh the table list, before we start as a workaround.

 This is because the direct execution of an SQL statement bypasses the layers which "caches" the table names.
 That's hard to fix ... The recommended workaround is to be consistent: Either use the tables API to both create
 tables and check for their existence (creation is not possible for TEXT TABLEs, so this is not an option), or
 use direct SQL statements for both (as the DROP ... statement already does).
 It would be nice to have the hasByName recognize that a table had been created with direct SQL, but that's an
 rather advanced feature which I wouldn't give much chances to be implemented in the medium term ...
 (Frank Schönheit)

EDIT - had another bug. Needed to delete the csv file between runs also.

And there is the problem of directories. HSQL reqquires that the csv file be in the same directory as the database or a subdirectory. In other words you will find an ugly hard coded path in the code above. I need to fix that.

That done, we can create our export files just that easily.

Next the mirror of these - the import routines for the driver.odb file.

Here is a first cut at this. It works, but needs to understand paths also.

Sub Main
	BasicLibraries.LoadLibrary("Drews")
	importTables()
End Sub
 
sub importTables()
dim conn as variant
 
	on local error goto errorImportTables
	conn = getConnection( "driver" )
	importTable( conn, "Customers", "txt_customers.csv" )
	importTable( conn, "Categories", "txt_categories.csv" )
	importTable( conn, "Products", "txt_products.csv" )
 
	errorImportTables:
		conn.dispose
end sub
 
sub importTable( aConn as variant, aTableName as string, aFileName as string )
dim stmnt as variant
dim strTableName as string
dim strCmd as string
 
	strTableName = "txt_" & aTableName
	stmnt = aConn.CreateStatement
	strCmd = "DELETE FROM """ & aTableName & """"
	stmnt.executeUpdate( strCmd )
 
	strCmd = "SET TABLE """ & strTableName & """" _
		& " SOURCE """  & aFileName & """"
	stmnt.executeUpdate( strCmd )
 
	strCmd = "INSERT INTO """ & aTableName & """" _
		& "( SELECT * FROM """ & strTableName & """ ) " 
	stmnt.executeUpdate( strCmd )
 
end sub