Hello everyone!
Again thank you for the prompt response. I have finally managed to write everything I needed, including the java code to call the procedures and all seems to be working just fine. BUT... since I am still pretty much exploring the unknown I am worried about the use of the DECLARE GLOBAL TEMPORARY TABLE. I've read the IBM explanation for it
from here, and I'm not sure what a session means. The code will run within a servlet and I intend to use a database connection pool. Does that mean that if I reuse a connection previously owned by user A for user B, user B will get the information retrieved for user A? If so, how can I fix it? Maybe my scenario is wrong, so in other words: is there anyway one user can get the data that was retrieved for another user?
I've uploaded the code, maybe others will find it usefull. It is working and if anyone has any ideas of improving it, please post them. Just don't be too harsh, it is my first try.
Passing some knowledge for others:
Biggest problem was trying to obtain a value from a very dynamic SQL (found in subroutine.txt) without using a cursor. I came to the conclusion it's not possible. If anyone can prove me wrong, please post an example. I tried making a function for it, turns out functions can't be this complicated (case statements, prepare statement or execute and quite a few others are NOT allowed in the function body. read
this for more information). So instead I just made another stored procedure that returns a varchar. Using a cursor to return just ONE value out of a select it is a bit of an overkill, but I can't find another way to do it. First I forgot the WITH RETURN part for the cursor, so be carefull, don't make the same mistake.
Thank you very much for all your help. Hope some of this will help others as well.
Kind regards,
Iulia
Java code for calling the stored procedure
Code:
Connection con = null;
CallableStatement cs = null;
int contor = 0;
try
{
con = Page.getDS().getConnection();
cs = con.prepareCall("{CALL SP_TAXE_DESCRIERE (?, ?, ?, ?, ? )}");
cs.setInt(1, 100);
cs.setInt(2, 100);
cs.registerOutParameter(3, Types.VARCHAR);
cs.registerOutParameter(4, Types.CHAR);
cs.registerOutParameter(5, Types.INTEGER);
cs.execute();
ResultSet rs = cs.getResultSet();
while(rs.next())
{
String name = rs.getString("COL_NAME");
String value = rs.getString("COL_VALUE");
String mu = rs.getString("COL_MU");
}
}
catch (SQLException e)
{
e.printStackTrace(System.out);
try
{
System.out.println("status "+cs.getString(4)+" error code"+cs.getInt(5));
}
catch(SQLException s)
{
s.printStackTrace(System.out);
}
}
finally
{
try
{
con.close();
}
catch(Exception ignored)
{
}
}
dBforums
>
Stored procedure, function, very dynamic SQL and casting
03-20-09, 07:15
final draft
Linear Mode
