Help with CallableStatement

[Rich Knight]

Hello,
I need to run the following query in my JSP:

SELECT myFunc(id) FROM users WHERE login = 'joe'

Where myFunc is a function stored in my Oracle database. When I execute the query in Toad, it provides the expected results. The Java API says to use CallableStatement, however, I'm totally confused on how to use it, if it is indeed the answer. CallableStatement seems to be for stored procs and not for stored functions? Any help would be appreciated, this is the last piece of my app and it's due tomorrow.
Thanks,
RK
[ February 20, 2002: Message edited by: Rich Knight ]

[Jamie Robertson]

if this worked from a SQL editor, it should work with a simple Statement.

String idString = "100";
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT myFunc(" + idString + ") FROM users WHERE login = 'joe'");

CallableStatements are used to call the stored procedure directly, where as in your case, the select statement should call the function, just as was done with your query in TOAD.
let me know if this is correct,
Jamie

[prabhat kumar]

stored Functions work with select statement since they have a return value. but if they have any in/out or out parameter then u can't access them via the select statement. for that purpose u jave to use callablestamement.
as below

 
// Call a function with no parameters
{
CallableStatement funcnone = conn.prepareCall
("begin ? := funcnone; end;");
funcnone.registerOutParameter (1, Types.CHAR);
funcnone.execute ();
System.out.println ("Return value is: " + funcnone.getString (1));
funcnone.close();
}

[Rich Knight]

Thanks for your replies.
prabhat,
Thanks for posting the code. If I have to call a function with parameters I use the setXXX(), correct? However, I don't see where I place my actual SQL query. I'm new to SQL so forgive me if it's obvious.

[Rich Knight]

Giving things a try, I tried the following code and it returned a "null" value for "returnVal". Am I in the ballpark? Thanks.

CallableStatement funcnone = con.prepareCall("SELECT myFunc(?) FROM users where upper(login) = upper('joe')");
    // register the in and out parameters
    funcnone.setString(1, "id");
    funcnone.registerOutParameter (2, Types.VARCHAR);
    // execute the procedure
    funcnone.execute ();
    // get the return values
    String returnVal = funcnone.getString(2);

[prabhat kumar]

things to remeber when using function or procedures indivisually..
1. use begin and end tags.
2. register the parameters first before stting the valus
---
now what u did wrong in ur code ..
..
1. there is only one parameter .. see only one Question mark

CallableStatement funcnone = con.prepareCall("SELECT myFunc(?) FROM users where upper(login) = upper('joe')");    //this is wrong
  

2.

funcnone.setString(1, "id");     

so u set the first parameter.
3. where is the second parameter?
there is no second question mark so there is value null simple as that.

funcnone.registerOutParameter (2, Types.VARCHAR);    // execute the procedure   
  

do �something like this

{CallableStatement funcnone = conn.prepareCall("begin ? := myFunc(?); end;");
funcnone.registerOutParameter (1, Types.VARCHAR);
funcnone.registerOutParameter (2, Types.VARCHAR);  
funcnone.setString(1, "JOE");    
// execute the procedure    
funcnone.execute ();
System.out.println ("Return value is: " + funcnone.getString (1));
funcnone.close();}
  

? r the placeholders for the values.
[ February 21, 2002: Message edited by: prabhat kumat ]

[Rich Knight]

Thanks a lot. I follow you, and see my mistakes. I now realize that I didn't pose my initial question accurately. 'myFunc' is a function that decrypts an ID. So when I pass it in 'joe' it decrypts Joe's ID and returns that value. What I'm confused about is how to build my statement incorporating the query, ie., how to tell 'myFunc' what table 'joe' resides ('users') in and what field ('id') of joe's record to decrypt. Does that make sense, or am I just totally missing it?
Thanks for your patience, here's the code for the function in question if it would help matters.

function myFunc(v_in  in  varchar2)
RETURN   varchar2
AS
p_name                  varchar2(100)   := 'MYFUNC ::> ';
l_in                    varchar2(2048);
l_out                   varchar2(2048);
l_byte_size             number;
l_new_byte_size         number;
--
raw_input               RAW(128) := UTL_RAW.CAST_TO_RAW(l_in);
key_string              VARCHAR2(16)  := 'scrambledata1234';
raw_key                 RAW(128) := UTL_RAW.CAST_TO_RAW(key_string);
encrypted_raw           RAW(2048);
encrypted_string        VARCHAR2(2048);
decrypted_raw           RAW(2048);
decrypted_string        VARCHAR2(2048);
error_in_input_buffer_length EXCEPTION;
PRAGMA EXCEPTION_INIT(error_in_input_buffer_length, -28232);
INPUT_BUFFER_LENGTH_ERR_MSG VARCHAR2(100) :=
'*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES - IGNORING EXCEPTION ***';
double_encrypt_not_permitted EXCEPTION;
PRAGMA EXCEPTION_INIT(double_encrypt_not_permitted, -28233);
DOUBLE_ENCRYPTION_ERR_MSG VARCHAR2(100) :=
      '*** CANNOT DOUBLE ENCRYPT DATA - IGNORING EXCEPTION ***';
--
BEGIN
--
     BEGIN
--
                If  v_in is null
                Then
                        l_out := null;
                        Return l_out;
                End If;
--
                l_in := v_in;
/*
                dbms_output.enable;
                dbms_output.put_line('Input String : '||l_in);
*/
--
                dbms_obfuscation_toolkit.DESDecrypt(input_string => l_in,
                        key_string => key_string, decrypted_string => decrypted_string);
--
                l_out := decrypted_string;
--
/*
                dbms_output.put_line('Successfully Decrypted!');
                dbms_output.put_line('Output String : '||l_out);
*/
--
     EXCEPTION
        WHEN others THEN
               l_out := to_char(sqlcode)||'::'||sqlerrm;
               dbms_output.put_line(p_name||' '||l_out);
     END;
--
     Return rtrim(l_out);
--
END UnScramble;

[Jamie Robertson]

Rob: did you try using the statement object instead of CallableStatement? your problem is different than just calling a stored procedure. So you can not just use CallableStatement interface on your sql statement. Your function is embedded in your sql statement, so it is the query that will call the stored procedure at the database end of things.
It is like executing the following:
"select UPPER(name) from customer where...". You would use a statement, even though the SQL statement calls the function UPPER() which is a DB Stored procedure. You don't have to use callable statements and the like because the DB takes care of the calls from the SQL query. Since you are able to execute myFunc from a query using TOAD, you should have no problem using the same query with myFunc in it using a Statement or PreparedStatement.

String idString = "100";
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT myFunc(" + idString + ") FROM users WHERE login = 'joe'");
while (rs.next())
{
System.out.println(rs.getInt(1));
}
// OR
int idNum = 100;
PreparedStatement pstmt = connection.prepareStatement("SELECT myFunc(?) FROM users WHERE login = 'joe'");
pstmt.setInt(1, idNum); 
ResultSet rs = stmt.executeQuery();
while (rs.next())
{
System.out.println(rs.getInt(1));
}

let me know if this works,
If it doesn't, then you'll have to do some messy workarounds.
Jamie
[ February 21, 2002: Message edited by: Jamie Robertson ]
[ February 21, 2002: Message edited by: Jamie Robertson ]

[Rich Knight]

Jamie,
Yes, it was my initial thought to just use Statement, however I got an "Invalid column name" exception when I tried to access the returned value via the "<%=rs.getString("id")%>" expression below (commenting it out removes the error, but of course that renders the page useless). I'll give your suggestions a try. Thanks.

  
<%
    String strQuery = "SELECT myFunc(id) FROM users WHERE upper(login_name) = upper('joe')";
    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery(strQuery);
%>
<%=rs.getString("id")%> // invalid column name exception thrown here

[ February 21, 2002: Message edited by: Rich Knight ]

[Jamie Robertson]

Sorry for calling you Rob
anyways, this is what I wanted to know!

Yes, it was my initial thought to just use Statement, however I got an "Invalid column name" exception when I tried to access the returned value via the "<%=rs.getString("id")%>" expression below (commenting it out removes the error, but of course that renders the page useless)...

This is a totally different error than you think, and a simple one at that( don't hit your head too hard on the table when you fix this ). When you call rs.getString("id") it throws the error because the column name is no longer "id". it is "myFunc(id)" (I think, you'd have to use resultsetMetaData.getColumnNames to be sure). Therefore, you are trying to reference an invalid column name. To fix this, change the rs.getString("id") to rs.getString(1) and it will work.
Jamie

[prabhat kumar]

however if ur goal is to get only the encrypted value then above suggestion is good .. only it needs a bit of modifications to suit ur needs ..

 
String loginid = "JOE";
Statement stmt = connection.createStatement();
//id is the name of table column 
ResultSet rs = stmt.executeQuery("SELECT myFunc(id) as decid FROM users WHERE login = '" + loginid + "'");
while (rs.next())
{
System.out.println(rs.getString(1));
//or
System.out.println(rs.getString("decid"));
}
// OR 
String loginid = "JOE";
PreparedStatement pstmt = connection.prepareStatement("SELECT myFunc(id) as decid FROM users WHERE login = ?");
pstmt.setString(1, loginid); 
ResultSet rs = stmt.executeQuery();
while (rs.next())
{System.out.println(rs.getString(1));}

hope it may solve ur problem
[ February 21, 2002: Message edited by: prabhat kumat ]

[Rich Knight]

Thanks a lot Jamie and prabhat, it works! Hind sight is 20/20 but I can't believe I went on that wild goose chase! Yall have been a huge help. As I get more into java I hope that I can be as much of a help to others. Thanks again!