• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

select decryptUDF('resource', colName) from table1 where condition

 
kumar han
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello Experts,
Please excuse me if post this question in a wrong forum.

I have a simple java program running on windows system. The programs connects to the database on AS400 and executes the following query using jdbc.

Query:
"select decryptUDF('resourceName', fName) from table1 where orderNum in (01)"

I'm trying to print the value from the resultset.
System.out.println(rs.getString("fName");

Result printed: *******
Result Expected: JohnDoe

Note: The value in fName is an encrypted value. The same query when executed in the query analyzer on AS400 gives the expected result.

Can someone shed some light on this.

Thanks,
JavaStudent

 
kumar han
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Update to print statement.
I also tried doing this but still the same result *******.
System.out.println(rs.getString(1));

Thanks,
JavaStudent
 
Wendy Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This other thread I think may answer your question fully

http://www.coderanch.com/t/573440/JDBC/java/incorrect-results-returning-DB
 
kumar han
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for providing the link Wendy!
The link was not helpful for my problem.

I'm actually able retrieve the result by using the below statement and resultset:
"select fName from table1 where orderNum in (01)"
System.out.println( resultSet.getString(1) );

The Result is: xxxxxxxy (which is an encrypted value under column fName)

Now I'm using the UDF to decrypt the encrypted valued of column fName:
"select decryptUDF('resourceName', fName) from table1 where orderNum in (01)"
System.out.println( resultSet.getString(1) );

But the Result is: ******** (which supposed to be JohnDoe the decrypted value of xxxxxxxy)

My question is how do I get JohnDoe instead of ******** ?
As I've already mentioned, the same SQL statments does the work when executed on the AS400 where the database sits on.
 
Wendy Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i can't find anything about decryptUDf out there on the web, what database is it? and do you have any documentation? two or more people reading it may understand what is going wrong.

and could you show us how the data is inserted (obv. leave out your encryption key)
 
kumar han
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Wendy,

I think you can not find anything about decryptUDF on the web 'cos it is an User Defined Function(UDF).
The database I connecting to is DB2 on iSeries AS400. Connecting from a java program running on Windows.

I have updated the column fName using the below statement:
"UPDATE table1 SET fName = encryptUDF('resourceName', fName) WHERE orderNum in (01)"

encryotUDF and decryptUDF are the User Defined Functions provided to me to encrypt or decrypt the field.

Thanks,
JavaStudent
 
Wendy Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am sorry but I am not sure we can help you with problems with your own user defined function, if you are sure you are doing the jdbc and result set correctly.
unless you can give us the contents of the function, but i susspect that is pretty confidential.
 
kumar han
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for trying to help me out Wendy!

The issue got resolved anyways.
The user will be able to decrypt the field value only if the USERID (with which I'm connecting to the database on AS400) is in the list of users who try to decrypt the field values.

Other than the above update everything in my code is correct. Now my programs runs as expected.


Thanks,
JavaStudent
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic