A common problem I have had is to migrate data contained in an access database to other databases or formats.
I have used the tclodbc extension [http://www.solagem.fi/~rnurmi/tclodbc.html] to generate an XML file descibing the Access data and this file can then processed with XSLT to recreate the database on another platform.
Regards,
MArk mailto:mark@tellcare.com
--
The following tcl script demonstrates how the XML file is generated:
####################
#
# Packages
#
####################
package require tclodbc
##
#
# Procedures
#
##
#
# Certain characters must be encoded in XML
#
proc Encode {str} {
#
# Standard encodings
#
set enc(&) {&}
set enc(<) {<}
set enc(>) {>}
set enc(\") {"}
#
# HACK ALERT
#
# Single quotes cause problems in SQL INSERT statements
#
set enc(') {''}
return [string map [array get enc] $str]
}
#
# Take an Access file and dump its contents to an XML file
#
proc XMLfromAccess {dbFile xmlFile} {
#
# Assume that we are converting Access files
#
set driver "Microsoft Access Driver (*.mdb)"
#
# Connect to db
#
database db "DRIVER=$driver;DBQ=$dbFile"
#
# Start XML file
#
lappend xml ""
lappend xml ""
#
# Obtain the Schema details
#
lappend xml "\t"
foreach tableDef [db tables] {
set table(TABLE_QUALIFIER) [lindex $tableDef 0]
set table(TABLE_OWNER) [lindex $tableDef 1]
set table(TABLE_NAME) [lindex $tableDef 2]
set table(TABLE_TYPE) [lindex $tableDef 3]
set table(REMARKS) [lindex $tableDef 4]
#
# Only process normal tables
#
if {[string compare $table(TABLE_TYPE) TABLE]} {
continue
}
#
# TABLE tags start here
#
lappend xml "\t\t"
foreach key [array names table] {
lappend xml "\t\t\t<$key>$table($key)$key>"
}
foreach columnDef [db columns $table(TABLE_NAME)] {
set column(TABLE_QUALIFIER) [lindex $columnDef 0]
set column(TABLE_OWNER) [lindex $columnDef 1]
set column(TABLE_NAME) [lindex $columnDef 2]
set column(COLUMN_NAME) [lindex $columnDef 3]
set column(DATA_TYPE) [lindex $columnDef 4]
set column(TYPE_NAME) [lindex $columnDef 5]
set column(PRECISION) [lindex $columnDef 6]
set column(LENGTH) [lindex $columnDef 7]
set column(SCALE) [lindex $columnDef 8]
set column(RADIX) [lindex $columnDef 9]
set column(NULLABLE) [lindex $columnDef 10]
set column(REMARKS) [lindex $columnDef 11]
#
# COLUMN tags start here
#
lappend xml "\t\t\t"
foreach key [array names column] {
lappend xml "\t\t\t\t<$key>$column($key)$key>"
}
lappend xml "\t\t\t"
}
unset column
foreach indexDef [db indexes $table(TABLE_NAME)] {
set index(TABLE_QUALIFIER) [lindex $indexDef 0]
set index(TABLE_OWNER) [lindex $indexDef 1]
set index(TABLE_NAME) [lindex $indexDef 2]
set index(NON_UNIQUE) [lindex $indexDef 3]
set index(INDEX_QUALIFIER) [lindex $indexDef 4]
set index(INDEX_NAME) [lindex $indexDef 5]
set index(TYPE) [lindex $indexDef 6]
set index(SEQ_IN_INDEX) [lindex $indexDef 7]
set index(COLUMN_NAME) [lindex $indexDef 8]
set index(COLLATION) [lindex $indexDef 9]
set index(CARDINALITY) [lindex $indexDef 10]
set index(PAGES) [lindex $indexDef 11]
set index(FILTER_CONDITION) [lindex $indexDef 12]
#
# INDEX tags
#
lappend xml "\t\t\t"
foreach key [array names index] {
lappend xml "\t\t\t\t<$key>$index($key)$key>"
}
lappend xml "\t\t\t"
}
unset index
lappend xml "\t\t
"
}
unset table
lappend xml "\t"
#
# Obtain the Table data
#
lappend xml "\t"
foreach tableDef [db tables] {
set table(TABLE_QUALIFIER) [lindex $tableDef 0]
set table(TABLE_OWNER) [lindex $tableDef 1]
set table(TABLE_NAME) [lindex $tableDef 2]
set table(TABLE_TYPE) [lindex $tableDef 3]
set table(REMARKS) [lindex $tableDef 4]
#
# Only process normal tables
#
if {[string compare $table(TABLE_TYPE) TABLE]} {
continue
}
#
# Execute SELECT
#
db statement data "SELECT * FROM $table(TABLE_NAME)"
data execute
#
# Save column specification
#
foreach colSpec [db columns $table(TABLE_NAME)] {
lappend typeList [lindex $colSpec 3]
lappend typeList [lindex $colSpec 5]
}
array set type $typeList
#
# Fetch each row
#
lappend xml "\t\t"
while {[data fetch row]} {
lappend xml "\t\t\t"
foreach key [array names row] {
lappend xml "\t\t\t\t[Encode $row($key)]"
}
lappend xml "\t\t\t
"
}
unset row
lappend xml "\t\t
"
}
lappend xml "\t"
lappend xml ""
#
# Write the XML file
#
set fp [open $xmlFile w]
puts $fp [join $xml "\n"]
close $fp
#
# Cleanup
#
db disconnect
}
##
#
# Main Program
#
##
foreach file [glob *.mdb] {
XMLfromAccess $file [file rootname $file].xml
}
--
The following example XSLT stylesheet shows how the tables and indexes can be created and the data loaded.
CREATE TABLE ();
INTEGER
,
CREATE UNIQUE INDEX ON ();
INSERT INTO () VALUES ();
,
''
''
,