Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Location
    florence-italy
    Posts
    9

    Question Unanswered: java.sql.date with PreparedStatement

    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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: java.sql.date with PreparedStatement

    Originally posted by Leonets
    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

    Are you sure that CARTA.DATA_RICEZIONE has the DATE type? If it's a TIMESTAMP you'll have to use setTimestamp() method...
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Oct 2003
    Location
    florence-italy
    Posts
    9

    Re: java.sql.date with PreparedStatement

    Originally posted by n_i
    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.
    ***********

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Oct 2003
    Location
    florence-italy
    Posts
    9
    Originally posted by Marcus_A
    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

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by Marcus_A
    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.
    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.
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Oct 2003
    Location
    florence-italy
    Posts
    9
    Originally posted by n_i
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •