...Sorry, slight red herring there, my mistake. As I was trimming down my
build file to a postable size, I discovered that in reality my ddlToDatabase
was not working correctly (and I have default behaviour that uses hard-coded
SQL as default values for unsupported databases -- eg, teradata -- but was
getting used for my Oracle output here.) Thanks for the info on to_date --
I'm thrilled to know that I don't actually need it.
My *real* problem is that my Ant task (ddlToDatabase) is not working; I get the
following error, "No database model specified":
$ ant dist....
BUILD FAILED
C:\mywk\sample-db\test-build.xml:154: The following error occurred while
executing this line:
C:\mywk\sample-db\test-build.xml:123: No database model specified
My task looks something like:
----------------
<target name="dml-xml2sql" depends="init" description="Given an XML file, write
DB-specific SQL DML to a file.">
<ddlToDatabase validatexml="false" usedelimitedsqlidentifiers="false"
catalogpattern="bookstore" schemapattern="bookstore"
databasetype="oracle10">
<fileset dir=".">
<include name="c:/mywk/sample-db/src/schema/data-bookstore.xml"/>
</fileset>
<writeDataToFile failonerror="false"
outputFile="build/schema/data-bookstore-oracle10.sql"/>
</ddlToDatabase>
</target>
-----end-----------
My data looks like,
----------------
<?xml version='1.0' encoding='UTF-8'?>
<data>
<address id="29" street1="Dolores St and San Jose Ave" street2="Apt #4"
city="San Francisco" state="CA" zip="94110" country="USA" phone="41535005555" /
<publisher id="9" pub_name="McClanahan Inc." address_id="39" />
<pubtitle id="1" pubtitle="Guide to Backpacking" genre="Guide" pub_id="1"
price="32.12" sales="0.0" notes="All about backpacking." pubdate="1998-01-02
00:00:00.0" />
....
-------end--------
And my schema,
----------------
<database name="bookstore">
<table name="address">
<column name="id" primaryKey="true" required="true" type="VARCHAR"
size="10" autoIncrement="false"/>
...
<column name="pubdate" primaryKey="false" required="true"
type="TIMESTAMP" size="23,3" autoIncrement="false"/>
...
------end----------
I saw a posting in the archive with a similar problem... but didn't see a
solution (other than to use the Java API). I'm generating DDL & DML from XML,
without actually connecting to databases. (I'm coming back to DDL-Utils after
about a year -- and I did update my libs to the latest version -- and kind of
remember this error, but can't recall the resolution.)
Any help would be much appreciated --
Thanks,
-michael
----- Original Message ----
From: Thomas Dudziak <[EMAIL PROTECTED]>
To: [email protected]
Sent: Sunday, March 25, 2007 12:06:57 PM
Subject: Re: create DML for Oracle timestamp or date? (ie: with to_date)
> I have XML data that I am trying to convert into SQL DML for Oracle (my XML
> to DDL works fine). My XML data includes Timestamps (and/or Dates), and I
> believe that in order to insert this into Oracle, I am going to have to call
> the Oracle functions "to_timestamp" (or "to_date"). (If I am mistaken,
> please let me know.)
>
> My data looks like (summarized),
> <pubtitle id="1" pubtitle="Some Title" price="32.12" pubdate="1998-01-02
> 00:00:00.0" />
>
> My schema looks like (summarized),
> <table name="pubtitle">
> <column name="id" primaryKey="true" required="true" type="VARCHAR"
> size="10" autoIncrement="false"/>
> <column name="pubtitle" primaryKey="false" required="true"
> type="VARCHAR" size="80" autoIncrement="false"/>
> <column name="price" primaryKey="false" required="false" type="DOUBLE"
> size="53" autoIncrement="false"/>
> <column name="pubdate" primaryKey="false" required="true"
> type="TIMESTAMP" size="23,3" autoIncrement="false"/>
> </table>
>
> (Note the timestamps.)
>
> And unfortunately, my SQL results in:
> insert into pubtitle values( '1', 'Guide to Backpacking', '32.12',
> '1/2/1998' );
>
> But I think it needs to be something like this (either using to_date, or
> to_timestamp):
> insert into pubtitle values( '1', 'Guide to Backpacking', '32.12',
> TO_DATE('1/2/1998', 'MM/DD/YYYY'));
>
> Are there Ant tasks or properties (or even Java API) that can be used to
> create SQL suitable for insertion into Oracle, including dates / timestamps?
> Note that I just want the SQL, I can't use Java to read/populate the database
> directly.
That is odd. DdlUtils should generate an ISO-formatted timestamp
literal for which no TO_DATE function call is necessary. Could you
post the build file or code snippet where you use DdlUtils ?
Tom
____________________________________________________________________________________
We won't tell. Get more on shows you hate to love
(and love to hate): Yahoo! TV's Guilty Pleasures list.
http://tv.yahoo.com/collections/265
