Oracle 10g Express jdbc thin driver setString() problem

Hi

I installed Oracle 10g Express and created a simple table and added
data.

LOGIN{
LOGIN_ID Char (10)
PASSWORD Char (10)
}

I am connecting to the database through Oracle thin jdbc driver.

In Java, I create a PreparedStatement (not OraclePreparedStatement)
like this:

PreparedStatement ps = con.preparestatement ("Select PASSWORD from
Login where LOGIN_ID=?");

When I bind the param like this:
ps.setString (1,"DEMO");
ResultSet rs = ps.executeQuery();

The query return 0 rows.

If I use the parameter directly in the query like this, it works:
PreparedStatement ps = con.preparestatement ("Select PASSWORD from
Login where LOGIN_ID='DEMO'");

The database NLS Character Set is showing UNICODE, not Default. I am
suspecting this is the problem. Is there any way to make the parameter
binding work?

I do not care for Unicode. Is there anyway to change the charset to
DEFAULT, assuming parameter binding would work on DEFAULT?

Any help is appreciated. I am using the latest jdbc thin driver.

I found this in the README.TXT of the jdbc driver:

Oracle JDBC Drivers release
10.1.0.2.0 (10g) README


* If the database character set is AL32UTF8, you may see errors

under the following circumstances:

- accessing LONG and VARCHAR2 datatypes.

- binding data with setString() and setCharacterStream().

My database NLS Charset is AL32UTF8. So, what is the workaround?

I am sure I have used ps.setString before on Oracle 9i. Do I have to
go back to 9i?

Thanks..

On Tue, 10 Jul 2007, srini.venigalla@gmail.com wrote:
> I found this in the README.TXT of the jdbc driver:
>
> Oracle JDBC Drivers release
> 10.1.0.2.0 (10g) README
>
>
> * If the database character set is AL32UTF8, you may see errors
>
> under the following circumstances:
>
> - accessing LONG and VARCHAR2 datatypes.
>
> - binding data with setString() and setCharacterStream().

That is just one nasty bug. Not being able to trust setString()?
Yikes!!!


> My database NLS Charset is AL32UTF8. So, what is the workaround?

You mentioned that you do not care for Unicode? Does that translate
into being able to say you have the power to have the character set
changed? Then do that.

> I am sure I have used ps.setString before on Oracle 9i. Do I have to
> go back to 9i?

I can think of a workaround, but you'd rather not go there, unless you
are ready to hide the hack behind a new implementer of
OraclePreparedStatement, committing to instantiating your
PreparedStatements with this new class and overriding the setString()
method with a hack. See if you can get the character set changed to
something else.

--
Galen Boyer

Before assuming it is a bug with the drivers, have you checked to make sure the client is using the same character set as the database? What is the
value of the NLS_LANG variable of the client environment?


--- Rich


On Tue, 10 Jul 2007 17:45:52 -0700, Srini <srini.venigalla@gmail.com> wrote:

>I found this in the README.TXT of the jdbc driver:
>
>Oracle JDBC Drivers release
>10.1.0.2.0 (10g) README
>
>
>* If the database character set is AL32UTF8, you may see errors
>
> under the following circumstances:
>
> - accessing LONG and VARCHAR2 datatypes.
>
> - binding data with setString() and setCharacterStream().
>
>My database NLS Charset is AL32UTF8. So, what is the workaround?
>
>I am sure I have used ps.setString before on Oracle 9i. Do I have to
>go back to 9i?
>
>Thanks..

On Jul 10, 5:39 pm, Srini <srini.veniga...@gmail.com> wrote:
> Hi
>
> I installed Oracle 10g Express and created a simple table and added
> data.
>
> LOGIN{
> LOGIN_ID Char (10)
> PASSWORD Char (10)
>
> }
>
> I am connecting to the database through Oracle thin jdbc driver.
>
> In Java, I create a PreparedStatement (not OraclePreparedStatement)
> like this:
>
> PreparedStatement ps = con.preparestatement ("Select PASSWORD from
> Login where LOGIN_ID=?");
>
> When I bind the param like this:
> ps.setString (1,"DEMO");
> ResultSet rs = ps.executeQuery();
>
> The query return 0 rows.
>
> If I use the parameter directly in the query like this, it works:
> PreparedStatement ps = con.preparestatement ("Select PASSWORD from
> Login where LOGIN_ID='DEMO'");
>
> The database NLS Character Set is showing UNICODE, not Default. I am
> suspecting this is the problem. Is there any way to make the parameter
> binding work?
>
> I do not care for Unicode. Is there anyway to change the charset to
> DEFAULT, assuming parameter binding would work on DEFAULT?
>
> Any help is appreciated. I am using the latest jdbc thin driver.

Hi. The issue is your DDL. If you change the column definitions to
VARCHAR2,
your JDBC will work. Otherwise, make sure you blank-pad your parameter
values to be the exact length of your fixed-length columns.
If you send the SQL with the values written in, the DBMS will
silently
assume you want to pad out the quoted values, and will make matches,
but once you ask the DBMS to make a compiled query plan with parameter
markers, such as with a PreparedStatement, the values you send will be
used as they are, no padding.
HTH,
Joe Weinstein at BEA Systems