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 [L1 ] 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,
--
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 "<?xml version=\"1.0\"?>"
lappend xml "<ODBC file=\"$dbFile\">"
#
# Obtain the Schema details
#
lappend xml "\t<SCHEMA>"
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<TABLE name=\"$table(TABLE_NAME)\">"
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<COLUMN name=\"$column(COLUMN_NAME)\">"
foreach key [array names column] {
lappend xml "\t\t\t\t<$key>$column($key)</$key>"
}
lappend xml "\t\t\t</COLUMN>"
}
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<INDEX name=\"$index(INDEX_NAME)\">"
foreach key [array names index] {
lappend xml "\t\t\t\t<$key>$index($key)</$key>"
}
lappend xml "\t\t\t</INDEX>"
}
unset index
lappend xml "\t\t</TABLE>"
}
unset table
lappend xml "\t</SCHEMA>"
#
# Obtain the Table data
#
lappend xml "\t<DATA>"
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<TABLE name=\"$table(TABLE_NAME)\">"
while {[data fetch row]} {
lappend xml "\t\t\t<ROW>"
foreach key [array names row] {
lappend xml "\t\t\t\t<COLUMN name=\"$key\" type=\"$type($key)\">[Encode $row($key)]</COLUMN>"
}
lappend xml "\t\t\t</ROW>"
}
unset row
lappend xml "\t\t</TABLE>"
}
lappend xml "\t</DATA>"
lappend xml "</ODBC>"
#
# 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.
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text"/>
<!--
Match the whole XML document
-->
<xsl:template match="/">
<xsl:apply-templates select="//SCHEMA/TABLE"/>
<xsl:apply-templates select="//DATA/TABLE/ROW"/>
</xsl:template>
<!--
CREATE TABLE .. and call the INDEX template
-->
<xsl:template match="TABLE">
CREATE TABLE <xsl:value-of select="@name"/> (<xsl:apply-templates select="COLUMN" mode="schema_table"/>);
<!--
Create all indexes associated with a table
-->
<xsl:apply-templates select="INDEX"/>
</xsl:template>
<!--
CREATE TABLE .. (?)
-->
<xsl:template match="COLUMN" mode="schema_table">
<xsl:value-of select="@name"/>
<xsl:text> </xsl:text>
<!--
Type matchings
-->
<xsl:choose>
<xsl:when test="TYPE_NAME='COUNTER'">INTEGER</xsl:when>
<xsl:otherwise><xsl:value-of select="TYPE_NAME"/></xsl:otherwise>
</xsl:choose>
<xsl:if test="position()!=last()">,</xsl:if>
</xsl:template>
<!--
CREATE INDEX ..
-->
<xsl:template match="INDEX">
<xsl:if test="string-length(@name)>0">
CREATE <xsl:choose><xsl:when test="NON_UNIQUE=0">UNIQUE</xsl:when></xsl:choose> INDEX <xsl:value-of select="INDEX_NAME"/> ON <xsl:value-of select="TABLE_NAME"/> (<xsl:value-of select="COLUMN_NAME"/>);
</xsl:if>
</xsl:template>
<!--
INSERT INTO ..
-->
<xsl:template match="ROW">
INSERT INTO <xsl:value-of select="../@name"/> (<xsl:apply-templates select="COLUMN" mode="data_table_row1"/>) VALUES (<xsl:apply-templates select="COLUMN" mode="data_table_row2"/>);
</xsl:template>
<!--
INSERT INTO .. (?)
-->
<xsl:template match="COLUMN" mode="data_table_row1">
<xsl:value-of select="@name"/>
<xsl:if test="position()!=last()">,</xsl:if>
</xsl:template>
<!--
INSERT INTO .. (..) VALUES (?)
-->
<xsl:template match="COLUMN" mode="data_table_row2">
<!--
Some types need to be quoted
-->
<xsl:choose>
<xsl:when test="@type='CHAR'">'<xsl:value-of select="."/>'</xsl:when>
<xsl:when test="@type='VARCHAR'">'<xsl:value-of select="."/>'</xsl:when>
<xsl:otherwise><xsl:value-of select="."/></xsl:otherwise>
</xsl:choose>
<xsl:if test="position()!=last()">,</xsl:if>
</xsl:template>
</xsl:stylesheet>