trouble with adding dates into mysql database

Hi,

This is my output when I put a debug attribute on it:

2006-02-21 21:02:18,631 INFO  org.orbeon.oxf.webapp.ProcessorService
null - /ritten - Received request
2006-02-21 21:02:18,804 INFO  org.orbeon.oxf.processor.DebugProcessor
null - true:
oxf:/add-action.xpl, line 23, column 61

<form xmlns:xforms="http://www.w3.org/2002/xforms">
    <ritnr>29</ritnr>
    <datum>2006-02-20</datum>
    <beginstand>117053</beginstand>
    <eindstand>117106</eindstand>
    <rittype>W</rittype>
    <routeid>1</routeid>
    <opmerking>Test 2</opmerking>
    <voertuigid>1</voertuigid>
    <action>add</action>
</form>
2006-02-21 21:02:18,876 INFO  org.orbeon.oxf.processor.DatabaseContext
null - Rolling back JDBC connection for datasource:
[com.mysql.jdbc.Driver|jdbc:mysql://localhost:3306/kilometer|danny].
2006-02-21 21:02:18,896 ERROR org.orbeon.oxf.webapp.ProcessorService
null - Exception at oxf:/list-ritten.xpl, line 22, column 72
java.sql.SQLException: Value '0000-00-00' can not be represented as
java.sql.Date
	at com.mysql.jdbc.ResultSet.getNativeDate(ResultSet.java:3513)
	at com.mysql.jdbc.ResultSet.getDate(ResultSet.java:1839)
	at com.mysql.jdbc.ResultSet.getDate(ResultSet.java:1817)

Seems to me that the input is OK.

- Danny

If you put a debug attribute on the "data" input of the SQL processor, what do you see?

-Erik

[hidden email] wrote:

I know, but that's just it I enter a date like 2006-02-20 and after the
submit I get this error. When I look at my database, there's some data
added, but it's just not the data from my form.

Danny

It appears your datum has the value 0000-00-00, which is not a valid
date.

-----Original Message-----
From: [hidden email] [[hidden email]]
Sent: Monday, February 20, 2006 3:56 PM
To: [hidden email]
Subject: [ops-users] trouble with adding dates into mysql database



Hi,


I'm trying to add dates from a xform instance into a mysql database. My
database column is of the type DATE.

This is the snippet of my sql:update statement:
<sql:update>
INSERT INTO ritten (DATUM, BEGINSTAND, EINDSTAND, RITTYPE, ROUTEID,
OPMERKING, VOERTUIGID) VALUES (
<sql:param select="/form/datum" type="xs:date"/>,
<sql:param select="/form/beginstand" type="xs:int"/>,
<sql:param select="/form/eindstand" type="xs:int"/>,
<sql:param select="/form/rittype" type="xs:string"/>,
<sql:param select="/form/routeid" type="xs:int"/>,
<sql:param select="/form/opmerking" type="xs:string"/>,
<sql:param select="/form/voertuigid" type="xs:int"/>
)
</sql:update>


When I try to add this I get 'weird' values in my database and I get an
error from OPS saying:

Value '0000-00-00' can not be represented as java.sql.Date


When I change the sql:update to static values like: INSERT INTO ....
VALUES ('2006-02-20',117106, 117159, 'W', 1, 'Test', 2) it adds the data
OK.


So my database accepts the data fine, but is there a qouting issue at
work here?

Any insights on this would be great!


Thanks,


Danny

--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: [hidden email]
For general help: [hidden email]
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
  

Danny,

The exception appears to come from getters (getDate()), which suggests that you are using <sql:get-column> or similar. In other words, the insert is possibly not the problem. Do you have bad data in the database in the first place?

Try to make sure your data is clean, then try running the insertions first and look with another tool at the content of the database (are dates ok in the database?). Then try to read the data with OPS, and see if that part causes problems.

-Erik

Danny Kruitbosch wrote:

Hi,

This is my output when I put a debug attribute on it:

2006-02-21 21:02:18,631 INFO  org.orbeon.oxf.webapp.ProcessorService
null - /ritten - Received request
2006-02-21 21:02:18,804 INFO  org.orbeon.oxf.processor.DebugProcessor
null - true:
oxf:/add-action.xpl, line 23, column 61

<form xmlns:xforms="http://www.w3.org/2002/xforms">
    <ritnr>29</ritnr>
    <datum>2006-02-20</datum>
    <beginstand>117053</beginstand>
    <eindstand>117106</eindstand>
    <rittype>W</rittype>
    <routeid>1</routeid>
    <opmerking>Test 2</opmerking>
    <voertuigid>1</voertuigid>
    <action>add</action>
</form>
2006-02-21 21:02:18,876 INFO  org.orbeon.oxf.processor.DatabaseContext
null - Rolling back JDBC connection for datasource:
[com.mysql.jdbc.Driver|jdbc:mysql://localhost:3306/kilometer|danny].
2006-02-21 21:02:18,896 ERROR org.orbeon.oxf.webapp.ProcessorService
null - Exception at oxf:/list-ritten.xpl, line 22, column 72
java.sql.SQLException: Value '0000-00-00' can not be represented as
java.sql.Date
    at com.mysql.jdbc.ResultSet.getNativeDate(ResultSet.java:3513)
    at com.mysql.jdbc.ResultSet.getDate(ResultSet.java:1839)
    at com.mysql.jdbc.ResultSet.getDate(ResultSet.java:1817)

Seems to me that the input is OK.

- Danny



Erik Bruchez wrote:

If you put a debug attribute on the "data" input of the SQL processor, what do you see?

-Erik

[hidden email] wrote:

I know, but that's just it I enter a date like 2006-02-20 and after the
submit I get this error. When I look at my database, there's some data
added, but it's just not the data from my form.

Danny

It appears your datum has the value 0000-00-00, which is not a valid
date.

-----Original Message-----
From: [hidden email] [[hidden email]]
Sent: Monday, February 20, 2006 3:56 PM
To: [hidden email]
Subject: [ops-users] trouble with adding dates into mysql database



Hi,


I'm trying to add dates from a xform instance into a mysql database. My
database column is of the type DATE.

This is the snippet of my sql:update statement:
<sql:update>
INSERT INTO ritten (DATUM, BEGINSTAND, EINDSTAND, RITTYPE, ROUTEID,
OPMERKING, VOERTUIGID) VALUES (
<sql:param select="/form/datum" type="xs:date"/>,
<sql:param select="/form/beginstand" type="xs:int"/>,
<sql:param select="/form/eindstand" type="xs:int"/>,
<sql:param select="/form/rittype" type="xs:string"/>,
<sql:param select="/form/routeid" type="xs:int"/>,
<sql:param select="/form/opmerking" type="xs:string"/>,
<sql:param select="/form/voertuigid" type="xs:int"/>
)
</sql:update>


When I try to add this I get 'weird' values in my database and I get an
error from OPS saying:

Value '0000-00-00' can not be represented as java.sql.Date


When I change the sql:update to static values like: INSERT INTO ....
VALUES ('2006-02-20',117106, 117159, 'W', 1, 'Test', 2) it adds the data
OK.


So my database accepts the data fine, but is there a qouting issue at
work here?

Any insights on this would be great!


Thanks,


Danny

--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: [hidden email]
For general help: [hidden email]
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws