Creating SQL Statements from form definitions
I had an iteresting discussion with a customer this week. They use Domino and dotNet for their web applications. Their decission criteria when to use what: if the data of the application needs to be fed into their data warehouse at a later point of time, they use dotNet since storage there typically ends up in an RDBMS. The biggest problem they face, in their own voice: " Our users are pretty spoiled from Domino. They expect days as turnaround time for applications. Using dotNet it takes at least three times longer."
So I asked why they don't use DECS to connect to the RDBMS. They could develop the application in Notes/Domino and once the app does what the user wants just add the tables in the RDBMS and link them up using DECS. They asked back if there is a way to generate the table or at least the create table statement from Domino directly. The short answer: Yes, you can, however you need to make decissions on datatypes and field length. The long answer: you need Domino Designer (for the Tools - DXL Utilities - Transformer ... menu) and a little XSLT stylesheet.
So I asked why they don't use DECS to connect to the RDBMS. They could develop the application in Notes/Domino and once the app does what the user wants just add the tables in the RDBMS and link them up using DECS. They asked back if there is a way to generate the table or at least the create table statement from Domino directly. The short answer: Yes, you can, however you need to make decissions on datatypes and field length. The long answer: you need Domino Designer (for the Tools - DXL Utilities - Transformer ... menu) and a little XSLT stylesheet.
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
</xsl:stylesheet>
<xsl:output xmlns:xml="http://www.w3.org/XML/1998/namespace" method="text" version="1.0" encoding="UTF-8" xml:space="preserve" omit-xml-declaration="yes"> </xsl:output>
<xsl:template match="/">
</xsl:template>
<!-- We only look at forms for the time being, when I have enough time I add basic support for subforms, at least the fixed ones -->
<xsl:apply-templates select="//d:form"> </xsl:apply-templates>
<xsl:template match="d:form">
CREATE TABLE
</xsl:template>
<xsl:value-of select="$formName"> </xsl:value-of>
( DocID CHAR(32) NOT NULL,
<xsl:apply-templates select="//d:field[@kind!='computedfordisplay']"> </xsl:apply-templates>
PRIMARY KEY (DocID));
<!-- Here we do map the various field types. You want to revisit the form -->
<xsl:template match="d:field">
</xsl:template>
<xsl:value-of select="@name"> </xsl:value-of>
<!-- Sample of a specia case consideration for number fields -->
<xsl:template match="d:field[@type='number']">
</xsl:template>
<xsl:value-of select="@name"> </xsl:value-of>
<xsl:choose>
</xsl:choose>
<xsl:when test="d:numberformat/@format='fixed'">
DECIMAL(31,
</xsl:when>
<xsl:value-of select="d:numberformat/@digits"> </xsl:value-of>
),
<!-- Generated with xml2html.xslt, Linkwerk.com -->
Posted by Stephan H Wissel on 10 April 2008 | Comments (7) | categories: Blog Show-N-Tell Thursday
stw