Re: Oracle JDBC Question

In article <AAVZ4.286$Gb7.16053_at_bgtnsc05-news.ops.worldnet.att.net>,   "Nick N." <nicksan_at_worldnet.att.net> wrote:
> I have a prepared statement with the following SQL
>
> SELECT ? FROM MYTABLE WHERE TRADEDATE = TO_DATE(?, 'yyyy-mm-dd')
>
> // TRADEDATE is of type DATE
>
> I do this
>
> setString(1, '2000-06-02')
>
> and execute it.
>
> It doesn't work. I assumed that the ? would be replaced and the SQL
 would
> become
>
> SELECT '2000-06-02' FROM MYTABLE WHERE TRADEDATE = TO_DATE('2000-06-
 02',
> 'yyyy-mm-dd')
>
> When I change my SQL to this
>
> SELECT TRADEDATE FROM MYTABLE WHERE TRADEDATE = TO_DATE(?, 'yyyy-mm-
 dd')
>
> it works fine.
>
> Don't ask why but I want to run it like th first SQL statement for
 reasons I
> won't get into.
>
> I am totally lost here. Can anyone help me out?
>
> Thanks
> Nick
>
>

are you calling setString( 2, .... )??? You have 2 bind variables.

I just ran:

$ cat test2.java

import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;

class test2 {

public static void main (String args []) throws SQLException , FileNotFoundException, IOException {

    DriverManager.registerDriver

        (new oracle.jdbc.driver.OracleDriver());

    Connection conn = DriverManager.getConnection

           ("jdbc:oracle:oci8:_at_ORA8IDEV", "scott", "tiger");

    conn.setAutoCommit(false);

    Statement tmpstmt = conn.createStatement();

    PreparedStatement stmt = conn.prepareStatement

        ( "select ? from emp " +
           " where hiredate = to_date(?,'yyyy-mm-dd')" );

    stmt.setString( 1, "1981-02-20" );
    stmt.setString( 2, "1981-02-20" );

    ResultSet rset = stmt.executeQuery();

    while( rset.next() )
    {

        System.out.println( rset.getString(1) );     }
}  

}

$ java test2
1981-02-20

That date is one of the valid hiredates in scott.emp usually (unless you run demobld in 2000, then the hiredates are all 2081, 2080 and so on)

It works. Post your code if it doesn't and we'll have a look at it.

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.