I am not aware of the concept 'typed parameters markers'

I am trying to execute this prepared statement:

select S0.MOD_ID AS MODULO_ID from MODULI S0
LEFT OUTER JOIN CARTA S1 ON (S1.CARTA_MOD_ID = S.MOD_ID)
WHERE (S1.DATA_RICEZIONE) >= ?

Then I use

pst.setDate(new java.sql.Date(ecc..))

and then

pst.execute();

I have this error:
Invalid conversion

I searched in this forum and I found that this QUERY gave the same error:
PreparedStatement stmt = conn.prepareStatement ("select ACI.NAME
FROM ACI WHERE ACI.NAME = Upper(?);");

and this was the suggested solution:
PreparedStatement stmt = conn.prepareStatement ("select ACI.NAME FROM ACI WHERE ACI.NAME = Upper(CAST(? AS VARCHAR(10)));");
I am trying to browse db2 docs but I've not found a similar thing for date:
CAST(? AS DATE)



the same SQL works if I don't use a PreparedStament like this

select S0.MOD_ID AS MODULO_ID from MODULI S0
LEFT OUTER JOIN CARTA S1 ON (S1.CARTA_MOD_ID = S.MOD_ID)
WHERE ( char(S1.DATA_RICEZIONE) >= '01-01-2001')

thks in advance for any tips !!

Leonardo

You can use CAST(character-string AS DATE) so long as the character string is in the form of YYYY-MM-DD or MM-DD-YYYY (slashes or periods may also be used as separators). The manual does not show all possibilities of the CAST function.

Are you sure that CARTA.DATA_RICEZIONE has the DATE type? If it's a TIMESTAMP you'll have to use setTimestamp() method...

absolutely.

I checked ten times.

Data_Ricezione is a DATE

***********
describe table fidescarte.carta


Nome Schema Nome

colonna tipo tipo Lungh. Scala Nulli

------------------------------ --------- ------------------ -------- ----- ------

ID SYSIBM INTEGER 4 0 No
CARTA_MOD_ID SYSIBM INTEGER 4 0 No
DATA_INS SYSIBM TIMESTAMP 10 0 No
DATA_MOD SYSIBM TIMESTAMP 10 0 No
NUM_MODULI SYSIBM INTEGER 4 0 Sì
NUM_DOCUMENTI SYSIBM INTEGER 4 0 No
DATA_INVIO SYSIBM DATE 4 0 No
DATA_RICEZIONE SYSIBM DATE 4 0 No
NOTE SYSIBM VARCHAR 250 0 Sì

8 record selezionato/i.
***********

I don't know much about Java, but any date that is used in a SQL statement (unless referenced directly as a DB2 table column) must be in character string format of length 10. The internal representation of date stored in DB2 is 4 bytes (2 numeric digits per byte), but you cannot access it that way in a SQL statement.

1)

If my SQL is
VARCHAR(S0.MOD_DATA_COMPILAZIONE) >= CAST(? as VARCHAR(10))
and my JAVA executes
pst.setString(1,'01-01-2001')
I have this error
CLI0112E Errore nell'assegnazione. SQLSTATE=22005


2)

If my SQL is
VARCHAR(S0.MOD_DATA_COMPILAZIONE) >= CAST(? as VARCHAR(10))
and my JAVA executes
pst.setDate(1, new java.sql.Date(System.currentTimemillis() ))
I have this error
CLI0102E Conversione non valida. SQLSTATE=07006


3)

If my SQL is
VARCHAR(S0.MOD_DATA_COMPILAZIONE) >= CAST(? as DATE)
and my JAVA executes
pst.setDate(1, new java.sql.Date(System.currentTimemillis() ))
I have this error
CLI0102E Conversione non valida. SQLSTATE=07006

4)

If my SQL is
VARCHAR(S0.MOD_DATA_COMPILAZIONE) >= CAST(? as DATE)
and my JAVA executes
pst.setString(1,'01-01-2001')
I have this error
CLI0102E Conversione non valida. SQLSTATE=07006

5)

If my SQL is
S0.MOD_DATA_COMPILAZIONE) >= CAST(? as DATE)
and my JAVA executes
pst.setString(1,'01-01-2001')
I have this error
CLI0102E Conversione non valida. SQLSTATE=07006

6)

If my SQL is
S0.MOD_DATA_COMPILAZIONE) >= CAST(? as DATE)
and my JAVA executes
pst.setDate(1, new java.sql.Date(System.currentTimemillis() ))
I have this error
CLI0102E Conversione non valida. SQLSTATE=07006

The method setDate() should work for a DATE comparison. I suspect there may be something wrong with the driver stack; e.g. db2 client having fixpack different from the server; or wrong JDBC driver; or something like that.

both db2client and server have:

db2 8.1.2.169

and I am using

sqllib\java\db2java.zip as driver