I simply can't insert a date via JDBC

[Thierry Guérin]

Hello,

I am currently undertaking a project that should link a java application and
an Oracle 8.0 database. I use JDK 1.2, JDBC 1.0, and the oracle thin driver
of classes111.zip (driver provided by oracle.com).

I encounter some problems with this kind of instruction :

int rows = executeUpdate("insert into TABLE (birth_date) values
('01-01-1977')");

This instruction NEVER works, whatever the format of date I input, although
it perfectly works when typed directly into a SQL console.

Here's the error log :

SQL State:
Error Code: 1843
Message: ORA-01843: not a valid month

java.sql.SQLException: ORA-01843: not a valid month

at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:181)
at oracle.jdbc.ttc7.Oall7.receive(Compiled Code)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1225)
at
oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:661)
at
oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:780)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithBatch(OracleStatement.java:8
22)
at
oracle.jdbc.driver.OracleStatement.doExecute(OracleStatement.java:1164)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java
:1210)
at
oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1227)
at LiaisonBase.insertionEmprunt(LiaisonBase.java:380)
at Actions.emprunt(Compiled Code)
at Essai.main(Essai.java:7)

Can someone help me please ?

Thierry - Schwing - Guérin

[Thomas Tuft Muller]

Thierry Guérin wrote in message <7d35qo$sug$1...@wfn.emn.fr>...

>Hello,
>
>I am currently undertaking a project that should link a java application
and
>an Oracle 8.0 database. I use JDK 1.2, JDBC 1.0, and the oracle thin driver
>of classes111.zip (driver provided by oracle.com).
>
>I encounter some problems with this kind of instruction :
>
> int rows = executeUpdate("insert into TABLE (birth_date) values
>('01-01-1977')");
>
>This instruction NEVER works, whatever the format of date I input, although
>it perfectly works when typed directly into a SQL console.
>

You have to tell Oracle the format of the date.

1. Use the PL/SQL TO_DATE function:

int rows = executeUpdate("insert into TABLE (birth_date) VALUES
TO_DATE('01-01-1977', 'MM-DD-YYYY')");

2. Or you can set the date format for the current session, and then use
TO_DATE without the second parameter:

stat.execute("BEGIN DBMS_SESSION.SET_NLS('nls_date_format',
'''MM-DD-YYYY''')");

(IMPORTANT: all quotes are single)

3. You can also use a prepared statment and set the date as a Timestamp:

String sql = ("insert into TABLE (birth_date) VALUES ?");
PreparedStatement stat = connection.prepareStatement();
Date d = (new SimpleDateFormat("MM-dd-yyyy')).parse("01-01-1977");
stat.setTimestamp(1, new Timestamp(d.getTime()));
stat.execute();

-Thomas

[Katak]

Hi,

do not know whether you know this or not. But I was thinking why don't you try
"insert into TABLE (birth_date) values to_date( '17-01-1999', 'dd-mm-yyyy')" or
is it
"insert into TABLE (birth_date) values to_date( 'dd-mm-yyyy', '17-01-1999')" ?
But it is one of these SQL statements.

>

[Stephen James Agneta]

On Sun, 21 Mar 1999 17:08:38 +0100, "Thierry Guérin"
<tgu...@eleve.emn.fr> wrote:

>Hello,
>
>I am currently undertaking a project that should link a java application and
>an Oracle 8.0 database. I use JDK 1.2, JDBC 1.0, and the oracle thin driver
>of classes111.zip (driver provided by oracle.com).
>
>I encounter some problems with this kind of instruction :
>
> int rows = executeUpdate("insert into TABLE (birth_date) values
>('01-01-1977')");
>
>This instruction NEVER works, whatever the format of date I input, although
>it perfectly works when typed directly into a SQL console.
>

Somebody else posted an excellent reply to this message. I wish to add
two things for you to keep in mind in the future.

1. You can set the date format for the entire database. I don't
recommend that but it is an option not yet mentioned. I forget how to
do this but you can look it up.

2. You mentioned that some SQL worked on the console. I assume you
mean SQL-Plus. Be very careful of SQL-Plus. It does many things behind
your back. You will be able to execute SQL in SQL-Plus that you wont
be able to execute via JDBC/ODBC or even OCI.

Take Care

[je...@clbooks.com]

Try using the Oracle ToText() data conversion function. With the code you've
included below, you are trying to insert a text literal into a date field.
Oracle (at least in 7.X) does not automatically convert this to a date format
for you. Your revised code should look like the following:

int rows = executeUpdate("insert into TABLE (birth_date) values ToDate('01-01-
1977', 'DD-MM-YYYY)");

Please bear in mind that I don't have my Oracle refereces with me at work, but
this should get ou on the right track.

- Jeff

In article <7d35qo$sug$1...@wfn.emn.fr>,

"Thierry Guérin" <tgu...@eleve.emn.fr> wrote:
> Hello,
>
> I am currently undertaking a project that should link a java application and
> an Oracle 8.0 database. I use JDK 1.2, JDBC 1.0, and the oracle thin driver
> of classes111.zip (driver provided by oracle.com).
>
> I encounter some problems with this kind of instruction :
>
> int rows = executeUpdate("insert into TABLE (birth_date) values
> ('01-01-1977')");
>
> This instruction NEVER works, whatever the format of date I input, although
> it perfectly works when typed directly into a SQL console.
>

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

[Ken North]

Try using the date escape clause:

insert into TABLE (birth_date) values {d '1977-01-01'}

For more information about escape clauses:

Chapter 11, Database Magic with Ken North (Prentice Hall)

Chapter 17, JDBC Database Access with Java (Addison Wesley)

==========================================================
Database Magic with Ken North
(the first book to cover Java in the database)
http://ourworld.compuserve.com/homepages/Ken_North/dbmagic.htm

Thierry Guérin wrote in message <7d35qo$sug$1...@wfn.emn.fr>...
>