JDBC weirdness

[Maxim]

Hi all!

My problem is very, very silly and stupid, I know, don't blame me for
that please...

When I call my query from SQL*Plus, I get the correct result:

SELECT check_passwd('user', 'password') FROM dual;

CHECK_PASSWD('USER','PASSWORD')
---------------------------------------
1

Now I need to to the same thing from java program, so I wrote the
following test:

Statement stmt = dbm.getConnection().createStatement();
ResultSet rs = stmt.executeQuery("SELECT check_passwd('user','password')
FROM dual");

assertTrue(rs.next());
assertTrue(rs.getInt(1) == 1);

....

However, the second assertion fails! rs.getInt(1) returns "0"!
check_passwd is the function that returns integer, either 0 or 1.
BTW, the same thing happens when I use PreparedStatement and setString()
instead of directly stuffing username and password into the query.

I'm porting the code from PostgreSQL to Oracle, and in Postgres
everything worked perfectly, so ... any ideas?

Thanks in advance!

--
Maxim Slojko

[Igor Kolomiyets]

What is a return type for check_password? And what would be returned if
instead of getInt(1) you call getString(2)?

Best regards,
Igor.

Maxim пишет:

Hi all!

My problem is very, very silly and stupid, I know, don't blame me for
that please...

When I call my query from SQL*Plus, I get the correct result:

SELECT check_passwd('user', 'password') FROM dual;

CHECK_PASSWD('USER','PASSWORD')
---------------------------------------
1

Now I need to to the same thing from java program, so I wrote the
following test:

Statement stmt = dbm.getConnection().createStatement();
ResultSet rs = stmt.executeQuery("SELECT check_passwd('user','password')
FROM dual");

assertTrue(rs.next());
assertTrue(rs.getInt(1) == 1);

....

However, the second assertion fails! rs.getInt(1) returns "0"!
check_passwd is the function that returns integer, either 0 or 1.
BTW, the same thing happens when I use PreparedStatement and setString()
instead of directly stuffing username and password into the query.

I'm porting the code from PostgreSQL to Oracle, and in Postgres
everything worked perfectly, so ... any ideas?

Thanks in advance!

--
Maxim Slojko

[Maxim]

Igor Kolomiyets wrote:

What is a return type for check_password? And what would be returned if
instead of getInt(1) you call getString(2)?
check_password returns integer, either 0 or 1. The function seem to be
fine, except that value is get corrupted somehow, if I use JDBC.
getString(1) returns "0" in that case as well.

Best regards,
Igor.

Maxim пишет:

Hi all!

My problem is very, very silly and stupid, I know, don't blame me for
that please...

When I call my query from SQL*Plus, I get the correct result:

SELECT check_passwd('user', 'password') FROM dual;

CHECK_PASSWD('USER','PASSWORD')
---------------------------------------
1

Now I need to to the same thing from java program, so I wrote the
following test:

Statement stmt = dbm.getConnection().createStatement();
ResultSet rs = stmt.executeQuery("SELECT
check_passwd('user','password') FROM dual");

assertTrue(rs.next());
assertTrue(rs.getInt(1) == 1);

....

However, the second assertion fails! rs.getInt(1) returns "0"!
check_passwd is the function that returns integer, either 0 or 1.
BTW, the same thing happens when I use PreparedStatement and
setString() instead of directly stuffing username and password into
the query.

I'm porting the code from PostgreSQL to Oracle, and in Postgres
everything worked perfectly, so ... any ideas?

Thanks in advance!

--
Maxim Slojko

[Igor Kolomiyets]

I'd debug PL/SQL function, JDBC does not seem to be a problem here
unless there is some collision of the datatypes occurs during the call.
Did you try to run this program with JDBC debuggin on?

Maxim пишет:

Igor Kolomiyets wrote:

What is a return type for check_password? And what would be returned
if instead of getInt(1) you call getString(2)?

check_password returns integer, either 0 or 1. The function seem to be
fine, except that value is get corrupted somehow, if I use JDBC.
getString(1) returns "0" in that case as well.

[Maxim]

Well, I've got rid of that mistake. Here is the part of explanation, as
I understand it. I would appreciate if you give me the correct one in
return :)

I have a table:
name nvarchar2(20)
passwd nvarchar2(20)
secure number(1)

When I insert something in that table, the BEFORE trigger is fired and
if secure is set to true (or null) password's md5 checksum is stored.
Otherwise plain password is stored (or md5 computed on client side).

My check_passwd function does the same thing. If the account is secure
it computes md5 of supplied password string and compares that value to
the stored one.

The problem appeared due to some chracter conversion issues. The account
was created by simple insert query from SQL*Plus. But was checked from
JDBC. So, when I called check_passwd from SQL*Plus everything was fine,
but when I called it from java it failed. I used only ASCII 7bit
characters in both username and password, so

how this can be?.. I know Oracle has character conversion issues, but
there are only latin letters...

Igor Kolomiyets wrote:

I'd debug PL/SQL function, JDBC does not seem to be a problem here
unless there is some collision of the datatypes occurs during the call.
Did you try to run this program with JDBC debuggin on?

Maxim пишет:

Igor Kolomiyets wrote:

What is a return type for check_password? And what would be returned
if instead of getInt(1) you call getString(2)?

check_password returns integer, either 0 or 1. The function seem to be
fine, except that value is get corrupted somehow, if I use JDBC.
getString(1) returns "0" in that case as well.

[Igor Kolomiyets]

What was the reason in using nvarchar2 instead of varchar2? I wouldn't
do this especially when only latin characters are used in the data. I am
almost 100% sure that if you change the data type from nvarchar2 to
varchar2 problem will disappear.

Maxim пишет:

Well, I've got rid of that mistake. Here is the part of explanation, as
I understand it. I would appreciate if you give me the correct one in
return :)

I have a table:
name nvarchar2(20)
passwd nvarchar2(20)
secure number(1)

When I insert something in that table, the BEFORE trigger is fired and
if secure is set to true (or null) password's md5 checksum is stored.
Otherwise plain password is stored (or md5 computed on client side).

My check_passwd function does the same thing. If the account is secure
it computes md5 of supplied password string and compares that value to
the stored one.

The problem appeared due to some chracter conversion issues. The account
was created by simple insert query from SQL*Plus. But was checked from
JDBC. So, when I called check_passwd from SQL*Plus everything was fine,
but when I called it from java it failed. I used only ASCII 7bit
characters in both username and password, so

how this can be?.. I know Oracle has character conversion issues, but
there are only latin letters...

Igor Kolomiyets wrote:

I'd debug PL/SQL function, JDBC does not seem to be a problem here
unless there is some collision of the datatypes occurs during the
call. Did you try to run this program with JDBC debuggin on?

Maxim пишет:

Igor Kolomiyets wrote:

What is a return type for check_password? And what would be returned
if instead of getInt(1) you call getString(2)?


check_password returns integer, either 0 or 1. The function seem to
be fine, except that value is get corrupted somehow, if I use JDBC.
getString(1) returns "0" in that case as well.