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('u ser', 'password') FROM dual;

CHECK_PASSWD('U SER','PASSWORD' )
---------------------------------------
1

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

Statement stmt = dbm.getConnecti on().createStat ement();
ResultSet rs = stmt.executeQue ry("SELECT check_passwd('u ser','password' )
FROM dual");

assertTrue(rs.n ext());
assertTrue(rs.g etInt(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 PreparedStateme nt 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]

Re: JDBC weirdness

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 пишет:[color=blue]
> 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('u ser', 'password') FROM dual;
>
> CHECK_PASSWD('U SER','PASSWORD' )
> ---------------------------------------
> 1
>
> Now I need to to the same thing from java program, so I wrote the
> following test:
>
> Statement stmt = dbm.getConnecti on().createStat ement();
> ResultSet rs = stmt.executeQue ry("SELECT check_passwd('u ser','password' )
> FROM dual");
>
> assertTrue(rs.n ext());
> assertTrue(rs.g etInt(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 PreparedStateme nt 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[/color]

[Maxim]

Re: JDBC weirdness

Igor Kolomiyets wrote:[color=blue]
> What is a return type for check_password? And what would be returned if
> instead of getInt(1) you call getString(2)?[/color]

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.
[color=blue]
>
> Best regards,
> Igor.
>
> Maxim пишет:
>[color=green]
>> 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('u ser', 'password') FROM dual;
>>
>> CHECK_PASSWD('U SER','PASSWORD' )
>> ---------------------------------------
>> 1
>>
>> Now I need to to the same thing from java program, so I wrote the
>> following test:
>>
>> Statement stmt = dbm.getConnecti on().createStat ement();
>> ResultSet rs = stmt.executeQue ry("SELECT
>> check_passwd('u ser','password' ) FROM dual");
>>
>> assertTrue(rs.n ext());
>> assertTrue(rs.g etInt(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 PreparedStateme nt 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[/color][/color]

[Igor Kolomiyets]

Re: JDBC weirdness

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 пишет:[color=blue]
> Igor Kolomiyets wrote:
>[color=green]
>> What is a return type for check_password? And what would be returned
>> if instead of getInt(1) you call getString(2)?[/color]
>
>
> 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.
>[/color]

[Maxim]

Re: JDBC weirdness

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:[color=blue]
> 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 пишет:
>[color=green]
>> Igor Kolomiyets wrote:
>>[color=darkred]
>>> What is a return type for check_password? And what would be returned
>>> if instead of getInt(1) you call getString(2)?[/color]
>>
>>
>>
>> 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.
>>[/color][/color]

[Igor Kolomiyets]

Re: JDBC weirdness

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 пишет:[color=blue]
> 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:
>[color=green]
>> 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 пишет:
>>[color=darkred]
>>> 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.
>>>[/color][/color][/color]