Board index » jbuilder » stored procedures with parameters - newbie

stored procedures with parameters - newbie


2003-09-16 06:09:42 AM
jbuilder1
I'm really struggling to call a stored procedure in JB9.
I'm using a procedureDataSet and a parameterRow. I get "execution of
procedure failed" when I test the procedure. I've tried setting columnName
to @thing, thing, and :thing - I'm not really sure what the right syntax is.
Here's my relevant java and SQL:
private void jbInit() throws Exception {
column1.setColumnName("@thing");
column1.setDataType(com.borland.dx.dataset.Variant.STRING);
column1.setServerColumnName("thing");
column1.setSqlType(0);
procedureDataSet2.setDisplayErrors(true);
procedureDataSet2.setProvider(null);
procedureDataSet2.setTableName("");
procedureDataSet2.setStoreName("");
procedureDataSet2.setAccumulateResults(false);
procedureDataSet2.setProcedure(new
com.borland.dx.sql.dataset.ProcedureDescriptor(database1,
"wx_fabemployee_insert2", parameterRow1, false, Load.ALL));
database1.setConnection(new
com.borland.dx.sql.dataset.ConnectionDescriptor("jdbc:odbc:SQL_Intrix_Test",
"", "", false, "sun.jdbc.odbc.JdbcOdbcDriver"));
parameterRow1.setColumns(new Column[] {column1});
}
CREATE PROCEDURE wx_fabemployee_insert2
@thing varchar(80)
AS
...
And assuming I can get this to work, is this really the best way to call SPs
from a web app? The "parameter row" concept seems pretty clunky to me. And
it seems silly that I should have to set the column names. I already
selected the name of the stored procedure; why do I have to manually set the
parameter names? And there's room for improvement with the "execution
failed" message...
Thanks,
George
 
 

Re:stored procedures with parameters - newbie

In <3f6638a2$ XXXX@XXXXX.COM >George Wynne wrote:
Quote
I'm really struggling to call a stored procedure in JB9.

I'm using a procedureDataSet and a parameterRow. I get "execution of
procedure failed" when I test the procedure. I've tried setting
columnName to @thing, thing, and :thing - I'm not really sure what the
right syntax is.
I don't use the PDS much (as you note, a lot of work..<G>) but there
is a reason for that.. It must be generic, standardized and formalized
and if you have ever tried to do this you will find it can get messy
real fast..<G>
But.. your error.. You probably need to wrap excecution in a try
with a SQLException and then print out a stack trace.. It will probably
be more educational.. Depending your what your back end is doing, it
may be that the standardized handling of the procedure by DX is not
compatible with your database..
Personally I would use standard JDBC to call your procedure and then
load it into a TableDataSet for manipulation (resultsetToDataSet(..))
You will still find yourself writing a lot of code, but at least you can
control what is happening..
Quote
And there's room for
improvement with the "execution failed" message...
Blame the driver vendor..<G>(and see note above about viewing the
stack trace..)
John..
--
=============================================
TeamB are volunteer helpers. Please DO NOT REPLY VIA EMAIL!
Post all questions and replies to this newsgroup ONLY
For papers on DataExpress, Applets, JSP, and Web Development go to:
www.microps.com/mps/papers.htm
====================================================