informix stored procedure access from Java using Weblogic jdbc driver
I am now facing a problem with using weblogic driver for informix, to access a
stored procedure in Informix.
The informix stored procedure that I need to call from my Java app is: (It is
fixed, and I can not change it):
----------------------------------------
-- Sccsid @(#) getPortfolioMovie.sql 1.4 10/20/97 13:45:15
drop procedure getPtfMovie;
CREATE PROCEDURE getPtfMovie (userId int)
RETURNING char(5),
money(16,2),
int,
money(16,2),
float,
money(16,2),
money(16,2),
int;
define symbol char(5);
define avgPrice money(16,2);
define qty int;
define lastPrice money(16,2);
define change float;
define currentValue money(16,2);
define gain money(16,2);
define type_x varchar(5);
define pricePaid money(16,2);
define YDPrice money(16,2);
define comm varchar(16);
define prod_status_x char(2);
define securityId int;
set lock mode to wait;
select value into comm
from game_variable
where name = 'CommissionPercent';
foreach cursorl for
select s.ticker_symbol
,s.security_id
,p.cumul_price_paid
,p.quantity
,s.current_price
,s.yesterdays_price
,s.type
,m.prod_status
into symbol
,securityId
,pricePaid
,qty
,lastPrice
,YDPrice
,type_x
,prod_status_x
from portfolio p
,security s
,movie_stock m
where p.user_id = userId
and p.security_id = s.security_id
and p.security_id = m.security_id
order by m.prod_status
,s.ticker_symbol
-- and s.type = 'MST'
{
foreach cursorl for
select p.security_id
,p.cumul_price_paid
,p.quantity
into securityId
,pricePaid
,qty
from portfolio p
where p.user_id = userId
select s.ticker_symbol
,s.security_id
,s.current_price
,s.yesterdays_price
,s.type
into symbol
,securityId
,lastPrice
,YDPrice
,type_x
from security s
where security_id = securityId;}
if (type_x = "SBO") then
continue foreach;
end if;
let avgPrice = pricePaid / qty;
let change = lastPrice - YDPrice;
let currentValue = qty * lastPrice;
let gain = currentValue - pricePaid;
return symbol, avgPrice, qty, lastPrice, change, currentValue, gain,
securityId with resume;
end foreach
end procedure;
---------------------------------------------
I use the following pieces of Java code to access the stored procedure:
// CREATE CALLABLE STATEMENT
CallableStatement callstmt;
try {
callstmt = con.prepareCall("{call getPortfolioMovie(?)}");
} catch (Exception e){
System.out.println(e);
}
PreparedStatement prepstmt = callstmt;
// SET INT PARAMETER
try {
prepstmt.setInt(pos, value);
} catch (Exception e){
System.out.println(e);
}
// EXECUTE CALLABLE STATEMENT SQL
boolean ret = false;
ResultSet results = null;
int updateCount = 0;
try {
ret = callstmt.execute();
if (ret == true){
results = callstmt.getResultSet();
}
else{
updateCount = callstmt.getUpdateCount();
}
return ret;
} catch(Exception e){
System.out.println(e);
}
---------------------------------------
and here is the error message that I get as soon as the
"ret = callstmt.execute();"
code line is executed.
the error message that I got is:
-------------------
execute() Failed:
Message: Informix error: -674 minor code=[-111] [No error info returned
from server.] See Informix error codes for explanation
Vendor: -674
State:
-------------------------
Thanks in advance.
Pax
-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum