I am trying to get a sense of requirements and best practices for Java
stored procedures in DB2 V7.2 for Windows.
1. Is it required or recommended that any of the following be closed before
leaving the stored procedure: open Statement and PreparedStateme nt objects;
open ResultSet objects; open JDBC connections? Although the stored procedure
will still run succesfully if these open objects and connections are not
closed, are there any negative performance or other consequences from not
closing these things manually?
2. What is the best way to do error handling? For example, if a
PreparedStateme nt object's executeQuery() method fails for some reason, what
should be returned to the client? I think that the procedure should pass
back at least the SQLCode, SQLState, and SQLMessage and would strongly
prefer to pass back a programmer-written message identifying where the error
took place, e.g. during executeQuery() of statement such-and-such. Do best
practices dictate that anything more, such as a stacktrace, be passed back
as well or that information of this kind be written to an external file?
3. Is there a practical limit on how many parameters and/or result sets can
be passed back to the client? Will there be any performance or other
advantages in limiting the number of parameters and result sets to a
particular number? If yes, what is that number? I'm just writing small
stored procedures for now but I can easily imagine writing much bigger ones.
However, I don't want to overdo things and make a stored procedure the
solution to every problem.
4. I understand that stored procedures can access other programs on the
database server. That gives me thoughts of generating faxes or PDFs,
exporting data to spreadsheets, etc. etc. What sorts of things have people
done that take advantage of this capability? Can anyone point me to examples
of these uses?
--
Rhino
---
rhino1 AT sympatico DOT ca
"If you want the best seat in the house, you'll have to move the cat."
stored procedures in DB2 V7.2 for Windows.
1. Is it required or recommended that any of the following be closed before
leaving the stored procedure: open Statement and PreparedStateme nt objects;
open ResultSet objects; open JDBC connections? Although the stored procedure
will still run succesfully if these open objects and connections are not
closed, are there any negative performance or other consequences from not
closing these things manually?
2. What is the best way to do error handling? For example, if a
PreparedStateme nt object's executeQuery() method fails for some reason, what
should be returned to the client? I think that the procedure should pass
back at least the SQLCode, SQLState, and SQLMessage and would strongly
prefer to pass back a programmer-written message identifying where the error
took place, e.g. during executeQuery() of statement such-and-such. Do best
practices dictate that anything more, such as a stacktrace, be passed back
as well or that information of this kind be written to an external file?
3. Is there a practical limit on how many parameters and/or result sets can
be passed back to the client? Will there be any performance or other
advantages in limiting the number of parameters and result sets to a
particular number? If yes, what is that number? I'm just writing small
stored procedures for now but I can easily imagine writing much bigger ones.
However, I don't want to overdo things and make a stored procedure the
solution to every problem.
4. I understand that stored procedures can access other programs on the
database server. That gives me thoughts of generating faxes or PDFs,
exporting data to spreadsheets, etc. etc. What sorts of things have people
done that take advantage of this capability? Can anyone point me to examples
of these uses?
--
Rhino
---
rhino1 AT sympatico DOT ca
"If you want the best seat in the house, you'll have to move the cat."
Comment