problem using Oracle built-in function SYSDATE through JDBC

[Ravi Sundaar]

Hi,

I am running into a strange problem with built-in functions through JDBC. I am using Oracle 7 in the backend. I am trying to use an Oracle built-in function through JDBC. I have the following two lines in my java code:

Statement testStatement = dbConnection.createStatement ();
 ResultSet r = testStatement.executeQuery ("select {fn sysdate()} from DUAL");

I get an SQLException and the stack trace looks like this:

java.sql.SQLException: Non supported SQL92 token at position 10: fn
 at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:329)
at oracle.jdbc.driver.OracleConnection.nativeSQL(OracleConnection.java:175)
 at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1156)
 at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:194)
 at testDate.main(testDate.java:20)

However, when I checked the Metadata object for the supported built-in functions, SYSDATE is one of them. Here is my code for that:

DatabaseMetaData MyMetadata = dbConnection.getMetaData ();
 String functions = MyMetadata.getTimeDateFunctions ();
 System.out.println ("Date functions: " + functions);

The following output clearly says that SYSDATE function is suported by the driver. And, I think, the format of my statement is ok since I am able to call stored procedures with the above format.

Date functions: ADD_MONTHS, LAST_DAY, MONTHS_BETWEEN, NEW_TIME, NEXT_DAY, ROUND, SYSDATE, TRUNC

Any thoughts?

Thanks,
Ravi.
 
 
 
 

[Tony Colston]

You can do this but you need to use different syntax. Change the SQL text to read

 

SELECT SYSDATE FROM DUAL

 

When you do that there will be 1 column in the ResultSet of the type java.sql.TimeStamp

 

column Count : 1
column name  : SYSDATE
column type  : 93 TimeStamp

 

If you have any questions please write.

 

Ravi Sundaar wrote in message <36127DAF...@thomtech.com>...

[Joseph Weinstein]

Ravi Sundaar wrote:

> ResultSet r = testStatement.executeQuery ("select {fn sysdate()} from
> DUAL");
> I get an SQLException

This is because the JDBC Extended SQL spec has no function 'sysdate()'.
You can try:
"select {fn curdate()} from dual",
"select {fn now()} from dual", or the Oracle-specific
"select sysdate() from dual"

The first two are standard JDBC SQL extensions. Be forewarned that a lot
of JDBC
drivers are fairly poor at implementing the JDBC spec on SQL extensions.
(WebLogic
drivers, however, are excellent at it).

> However, when I checked the Metadata object for the supported built-in
> functions,

> and SYSDATE is one of them.

This is a bug in the JDBC driver. The implementors mistook the meaning
of this method to
mean what DBMS-specific functions are available from the DBMS. The
correct meaning
of the call is: "Which of the JDBC Extended SQL functions does this
driver implement?

--

Joseph Weinstein Staff Engineer http://www.weblogic.com

See our newsgroups: http://www.weblogic.com/services/newsgroups.html

try weblogic free *with support*: http://www4.weblogic.com/register.html