get the value of oracle function

[chsadaki]

Hi ...
I am writing an application using jbuilderx and oracle9i, i wrote a
function in oracle using pl/sql that returns a number, and i tested it
in oracle sql and everything is ok and it returns a value.
The problem is that i cant get the value from jbuilder, i wrote this
code :

QueryProvider qp = new QueryProvider();
qp.executeStatement(dbname,"{:xx = call
funcnamenn,:jj)}",parameterRowName);
double d = parameterRowName.getDouble("xx");

but each time i test the d variable it is =0.0

and the remaining code of the function is executed well , I just need
to get the return value

please anybody can help me in this???

thanx
shameram

 

[Bjorn Abelli]

I am writing an application using jbuilderx and oracle9i,
i wrote a function in oracle using pl/sql that returns a
number, and i tested it in oracle sql and everything is ok
and it returns a value. The problem is that i cant get the
value from jbuilder, i wrote this code :

1. It shouldn't be JBuilder that gets the value,
it should be your Java program...

JBuilder is "only" an IDE, which makes it possible for you to write, compile
and run/debug programs.

// Where's the code for "QueryProvider"?
// If it's what I guess, a class provided by Borland in JBuilder,
// I also guess that you really don't want to use that, but the
// ProcedureProvider instead.

QueryProvider qp = new QueryProvider();

As it looks, that class has encapsulated all the normally used JDBC
routines, so I guess that "dbname" is a DataSource, and "parameterRowName"
is an array of parameters to the procedure?

qp.executeStatement(
dbname,
"{:xx = call funcnamenn,:jj)}",
parameterRowName);

AFAIK, even if ":var" is the standard way to deal with local variables in
Oracle, not all JDBC-drivers does it, but all of them conforms to the old
"ODBC"-way with simple question marks, i.e.

"{? = call funcname(?, ?)}",

But as this isn't even standard JDBC, but a Borland class, maybe you should
read more closely in that documentation, to see what form should be used. If
I read Borland documentation right, it maybe looks more like:

"{:1.xx = call funcname2.nn, :3.jj)}",

As you're calling a stored function, it should be doing a prepareCall before
executing.

I don't think that really is happening inside a QueryProvider? From the name
I would rather guess that it doesn't deal with other things than ordinary
queries...

It's more likely that the ProcedureDriver does that...

double d = parameterRowName.getDouble("xx");

If the driver really works with the Oracle standard of variable naming ),
that should also be reflected when fetching values after execution:

parameterRowName.getDouble(":xx");

....or if you use the name setting indicated of Borland pages:

parameterRowName.getDouble(":1.xx");

However, it seems also essential that the parameter list is initiated
properly, i.e. that "parameterRowName" contains all three parameters (return
value, arg 1 and arg 2).

please anybody can help me in this???

You need to provide more information, but you would possibly get better
answers in a specific JBuilder group as you're using Borlands class
libraries, e.g.:

news://newsgroups.borland.com/borland.public.jbuilder.database


// Bjorn A

 

[chsadaki]

First of all thanx for ur answer.
I tried to use some of ur information in may application ,
but I found another way to get the value , it is not very clever but it
is working.
I wrote a procedure instead the function and i use an in out parameter
donc taraaa....
it is not very clever I know
thanx anyway