I've used mysql extensively, but now I'm converting to Oracle. My
inserts are working, but my queries are sensitive to the semi-colon
line termination. Is there a way to correct this without rewriting all
my queries?
For example, the following works for JDBC with mysql and Oracle's SQL
Command line tool. But using JDBC with Oracle throws an error:
ORA-00911: invalid character
SELECT distinct (clientName) FROM tm.ClientSessionInfo;
However, removing the semi-colon allows the query to work from JDBC
with Oracle:
SELECT distinct (clientName) FROM tm.ClientSessionInfo
I'm using oracle.jdbc.driver.OracleDriver 1.4 and Oracle 10.
The code is:
String query ="SELECT distinct (clientName) FROM tm.ClientSessionInfo;"
statement = connection.prepareStatement( query );
results = statement.executeQuery( );
Do I have to rewrite all my queries without semi-colons or is there
some other solution?
Thanks
Chris Riesbeck - 14 Dec 2006 18:56 GMT
> I've used mysql extensively, but now I'm converting to Oracle. My
> inserts are working, but my queries are sensitive to the semi-colon
[quoted text clipped - 11 lines]
>
> SELECT distinct (clientName) FROM tm.ClientSessionInfo
Remove the semicolons. They should not have been in your SQL strings to
begin with in your JDBC code. They used by command line interfaces but
redundant (or erroneous) in JDBC statements. If you drop them, then the
above queries should work in both systems, and others.
There are however many real incompatibilities between various
implementations of SQL. This is why Hibernate has "dialects" for each
major DBMS. JDBC's escape syntax can help if you want to stick with
straight JDBC.
http://java.sun.com/j2se/1.4.2/docs/guide/jdbc/getstart/statement.html