SQL command not properly ended (in Oracle)

[Teresa Redmond]

Hi, all:

Problem this time: running java, connect to oracle via thin driver,
error on

rs = stmt.executeQuery(); <---- line 50 of UserTimer.java

where query =

String sql = "DELETE from portal.cart, portal.saved_data where (
to_date(cart.time_stamp, 'YYYY-MM-DD') < ? AND saved_data.cart_pkey =
cart.pkey)";

and ? = stmt.setTimestamp(1, TimeNow) and TimeNow = 2001-10-02 15:38

I've gotten this particular error so many times, for so many different
reasons, that I just can't think straight about it anymore. And to
top it all off, technet.oracle.com has changed their forums to .jsp
format, which take forever and ever to load, so that it takes 10
minutes just to get signed on and into a forum, which you cannot then
search. Sigh.

So, sorry to dump this off for now, but I really need a new set of
eyes for this...

Thanks in advance for any help whatever. :-)

Below is the whole error stuff:
java.sql.SQLException: ORA-00933: SQL command not properly ended

java.sql.SQLException: ORA-00933: SQL command not properly ended

at
oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:168)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
at
oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1405)
at
oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:822)
at
oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:1446)
at
oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1371)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1900)
at
oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:363)
at
oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:314)
at UserLog.UserTimer.CheckDB(UserTimer.java:50)
at UserLog.UserTimer.run(UserTimer.java:34)
at java.lang.Thread.run(Thread.java:484)

--
Teresa Redmond
Associate Software Engineer
Anteon Corporation
tred...@anteon.com

[Frank Brouwer]

Hi Teresa,

I wonder if the following is causing it:
1. Your part <AND saved_data.cart_pkey = cart.pkey)";> (the ';' is wrong,
but that's to easy isn't it?).
2. You should use the 'to_date' function for date formats always (but
that's to easy too).
3. The parameters (? in the queries) are filled wrong, try a 'sql-profiler'
to catch the sql send to the dbms.

I hope it helps......

Regards,

Frank.
"Teresa Redmond" <pixe...@aol.com> wrote in message
news:8n2mrt8r79np7ench...@4ax.com...

[Teresa Redmond]

On Thu, 4 Oct 2001 13:00:49 +0200, "Frank Brouwer"
<Frank....@Trimergo.com> quoth:

>Hi Teresa,
>
>I wonder if the following is causing it:
>1. Your part <AND saved_data.cart_pkey = cart.pkey)";> (the ';' is wrong,
>but that's to easy isn't it?).

That ";" is the Java code; too easy. It couldn't be that easy. :-)

>2. You should use the 'to_date' function for date formats always (but
>that's to easy too).
>3. The parameters (? in the queries) are filled wrong, try a 'sql-profiler'
>to catch the sql send to the dbms.

Not sure what a 'sql-profiler' is; I'm using the stmt.setTimestamp()
method to fill the ? in the query. I'd be happy to try something
else. The awful thing about this is that this all worked when there
was only one table to delete from... I was working yesterday evening
with the "as "ALIAS"" thing that the View SQL window does in the table
editor in DBA Studio, maybe that's the solution? I dunno...

[Sebastiano Pilla]

Teresa Redmond <pixe...@aol.com> wrote:

> rs = stmt.executeQuery(); <---- line 50 of UserTimer.java
>
> where query =
>
> String sql = "DELETE from portal.cart, portal.saved_data where (
> to_date(cart.time_stamp, 'YYYY-MM-DD') < ? AND saved_data.cart_pkey =
> cart.pkey)";
>
> and ? = stmt.setTimestamp(1, TimeNow) and TimeNow = 2001-10-02 15:38

[...]

> java.sql.SQLException: ORA-00933: SQL command not properly ended

What happens if you try the query in SQL*Plus? You get the same error
message or does it work?

Regards
Sebastiano Pilla

[Jim O'Donnell]

Might be that you should substitute
rs = stmt.executeQuery();
with
stmt.executeUpdate();

since you're trying to delete, not query.

--Jim

Teresa Redmond <pixe...@aol.com> wrote in message news:<8n2mrt8r79np7ench...@4ax.com>...