DB2 Universal JDBC Driver and {fn CURDATE()}

[charl]

Hi,

My apologies in advance if my terminology is slightly confused, I am
new to all this.

We have previously been running some SQL on DB2 (v7) utilising a type 3
driver, and have made the change to DB2 v8 with a type 2 DB2 Universal
JDBC Driver.
SQL that ran successfully under the old setup now throws the following
error...

(Simplified) SQL:
'SELECT * FROM OURTABLE WHERE EFF_FROM_DT <= {fn CURDATE()} AND
EFF_UNTL_DT >= {fn CURDATE()}'

Error:
SQL0007N The character "{" following ") AND EFF_FROM_DT <=" is not
valid

I have read all I can see on the IBM help page and can't find any
reference to changes to embedded SQL support in either v8 or as a
result of the JDBC driver change.

Many thanks for any help,
Charl

[Serge Rielau]

charl wrote:

Hi,

My apologies in advance if my terminology is slightly confused, I am
new to all this.

We have previously been running some SQL on DB2 (v7) utilising a type 3
driver, and have made the change to DB2 v8 with a type 2 DB2 Universal
JDBC Driver.
SQL that ran successfully under the old setup now throws the following
error...

(Simplified) SQL:
'SELECT * FROM OURTABLE WHERE EFF_FROM_DT <= {fn CURDATE()} AND
EFF_UNTL_DT >= {fn CURDATE()}'

Error:
SQL0007N The character "{" following ") AND EFF_FROM_DT <=" is not
valid

I have read all I can see on the IBM help page and can't find any
reference to changes to embedded SQL support in either v8 or as a
result of the JDBC driver change.

Many thanks for any help,
Charl
The {..} notation is client specific. Apparently the Type 2 driver
doesn't know what it means.
Is theer anything preventing you from using regular SQL?

SELECT * FROM OURTABLE WHERE EFF_FROM_DT <= CURRENT DATE AND EFF_UNTL_DT >= CURRENT DATE

If you want the function CURDATE that can also be easily done:

CREATE FUNCTION CURRDATE()
RETURNS DATE CONTAINS SQL NON DETERMINISTIC NO EXTERNAL ACTION
RETURN CURRENT DATE

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[charl]

a-ha!
that's related to the 'Ability to call uncatalogued stored procedures'
bit in here
(http://publib.boulder.ibm.com/infoce...rjvjcdif.htm)?
many thanks,
charl