I have a moderately simple scenerio where I lose the value of one of my
output parameters in a stored procedure. There are minor differences
between the following 2 stored procedures that are attached. A 'diff'
displays them plainly enough. I think I can sum up the differences as
additions of output parameters and assignments in the file od.sql.10.
Calling the procedure in order_display.sql returns the expected
results. Calling the procedure in od.sql.10 also returns the expected
results in the additional output parameters, except the output parameter
'o_id' now returns a 0 (null?) value.
If anyone has tips on how I can track what's going on, I will try them.
Thanks,
Mark
/* Clause 2.9.3 */
/* Not complient. */
CREATE DBPROC order_display(IN c_uname VARCHAR(20), IN c_passwd VARCHAR(20),
OUT o_id FIXED(10),
OUT c_fname VARCHAR(15), OUT c_lname VARCHAR(15),
OUT c_phone VARCHAR(16), OUT c_email VARCHAR(50),
OUT o_date VARCHAR(30),
OUT o_sub_total FIXED(17, 2), OUT o_tax FIXED(17, 2), OUT o_total FIXED(17, 2),
OUT o_ship_type VARCHAR(10), OUT o_ship_date VARCHAR(30),
OUT o_status VARCHAR(15),
OUT bill_addr_street1 VARCHAR(40), OUT bill_addr_street2 VARCHAR(40),
OUT bill_addr_city VARCHAR(30), OUT bill_addr_state VARCHAR(20),
OUT bill_addr_zip VARCHAR(10), OUT bill_co_name VARCHAR(50),
OUT ship_addr_street1 VARCHAR(40), OUT ship_addr_street2 VARCHAR(40),
OUT ship_addr_city VARCHAR(30), OUT ship_addr_state VARCHAR(20),
OUT ship_addr_zip VARCHAR(10), OUT ship_co_name VARCHAR(50),
OUT cx_type VARCHAR(10), OUT cx_auth_id CHAR(15)) AS
VAR c_id FIXED(10); o_bill_addr_id FIXED(10); o_ship_addr_id FIXED(10);
BEGIN
SELECT c_id, c_fname, c_lname, c_phone, c_email
FROM tpcw.customer
WHERE c_uname = :c_uname
AND c_passwd = :c_passwd;
IF $rc = 0 THEN
BEGIN
FETCH INTO :c_id, :c_fname, :c_lname, :c_phone, :c_email;
SELECT o_id, CHAR(o_date, ISO), o_sub_total, o_tax, o_total,
o_ship_type, CHAR(o_ship_date, ISO), o_status, o_bill_addr_id,
o_ship_addr_id
FROM tpcw.orders, tpcw.customer
WHERE o_c_id = :c_id
ORDER BY o_date DESC;
IF $rc = 0 THEN
BEGIN
FETCH INTO :o_id, :o_date, :o_sub_total, :o_tax, :o_total,
:o_ship_type, :o_ship_date, :o_status, :o_bill_addr_id,
:o_ship_addr_id;
SELECT addr_street1, addr_street2, addr_city, addr_state, addr_zip,
co_name
INTO :bill_addr_street1, :bill_addr_street2, :bill_addr_city,
:bill_addr_state, :bill_addr_zip, :bill_co_name
FROM tpcw.address, tpcw.country
WHERE addr_id = :o_bill_addr_id
AND addr_co_id = co_id;
SELECT addr_street1, addr_street2, addr_city, addr_state, addr_zip,
co_name
INTO :ship_addr_street1, :ship_addr_street2, :ship_addr_city,
:ship_addr_state, :ship_addr_zip, :ship_co_name
FROM tpcw.address, tpcw.country
WHERE addr_id = :o_ship_addr_id
AND addr_co_id = co_id;
SELECT cx_type, cx_auth_id
INTO :cx_type, :cx_auth_id
FROM tpcw.cc_xacts
WHERE cx_o_id = :o_id;
SELECT ol_i_id, i_title, i_publisher, i_cost, ol_qty, ol_discount,
ol_comments
FROM tpcw.item, tpcw.order_line
WHERE ol_o_id = :o_id
AND ol_i_id = i_id;
END;
END;
END;
/* Clause 2.9.3 */
/* Not complient. */
CREATE DBPROC order_display(IN c_uname VARCHAR(20), IN c_passwd VARCHAR(20),
OUT o_id FIXED(10),
OUT c_fname VARCHAR(15), OUT c_lname VARCHAR(15),
OUT c_phone VARCHAR(16), OUT c_email VARCHAR(50),
OUT o_date VARCHAR(30),
OUT o_sub_total FIXED(17, 2), OUT o_tax FIXED(17, 2), OUT o_total FIXED(17, 2),
OUT o_ship_type VARCHAR(10), OUT o_ship_date VARCHAR(30),
OUT o_status VARCHAR(15),
OUT bill_addr_street1 VARCHAR(40), OUT bill_addr_street2 VARCHAR(40),
OUT bill_addr_city VARCHAR(30), OUT bill_addr_state VARCHAR(20),
OUT bill_addr_zip VARCHAR(10), OUT bill_co_name VARCHAR(50),
OUT ship_addr_street1 VARCHAR(40), OUT ship_addr_street2 VARCHAR(40),
OUT ship_addr_city VARCHAR(30), OUT ship_addr_state VARCHAR(20),
OUT ship_addr_zip VARCHAR(10), OUT ship_co_name VARCHAR(50),
OUT items FIXED(2),
OUT i_id1 FIXED(10), OUT i_title1 VARCHAR(60),
OUT i_publisher1 VARCHAR(60), OUT i_cost1 FIXED(17, 2),
OUT ol_qty1 FIXED(3), OUT ol_discount1 FIXED(17, 2),
OUT ol_comments1 VARCHAR(100),
OUT cx_type VARCHAR(10), OUT cx_auth_id CHAR(15)) AS
VAR c_id FIXED(10); o_bill_addr_id FIXED(10); o_ship_addr_id FIXED(10);
BEGIN
SET items = 0;
SET i_id1 = 0;
SET i_title1 = '';
SET i_publisher1 = '';
SET i_cost1 = 0;
SET ol_qty1 = 0;
SET ol_discount1 = 0;
SET ol_comments1 = '';
SELECT c_id, c_fname, c_lname, c_phone, c_email
FROM tpcw.customer
WHERE c_uname = :c_uname
AND c_passwd = :c_passwd;
IF $rc = 0 THEN
BEGIN
FETCH INTO :c_id, :c_fname, :c_lname, :c_phone, :c_email;
SELECT o_id, CHAR(o_date, ISO), o_sub_total, o_tax, o_total,
o_ship_type, CHAR(o_ship_date, ISO), o_status, o_bill_addr_id,
o_ship_addr_id
FROM tpcw.orders, tpcw.customer
WHERE o_c_id = :c_id
ORDER BY o_date DESC;
IF $rc = 0 THEN
BEGIN
FETCH INTO :o_id, :o_date, :o_sub_total, :o_tax, :o_total,
:o_ship_type, :o_ship_date, :o_status, :o_bill_addr_id,
:o_ship_addr_id;
SELECT addr_street1, addr_street2, addr_city, addr_state, addr_zip,
co_name
INTO :bill_addr_street1, :bill_addr_street2, :bill_addr_city,
:bill_addr_state, :bill_addr_zip, :bill_co_name
FROM tpcw.address, tpcw.country
WHERE addr_id = :o_bill_addr_id
AND addr_co_id = co_id;
SELECT addr_street1, addr_street2, addr_city, addr_state, addr_zip,
co_name
INTO :ship_addr_street1, :ship_addr_street2, :ship_addr_city,
:ship_addr_state, :ship_addr_zip, :ship_co_name
FROM tpcw.address, tpcw.country
WHERE addr_id = :o_ship_addr_id
AND addr_co_id = co_id;
SELECT cx_type, cx_auth_id
INTO :cx_type, :cx_auth_id
FROM tpcw.cc_xacts
WHERE cx_o_id = :o_id;
SELECT ol_i_id, i_title, i_publisher, i_cost, ol_qty, ol_discount,
ol_comments
FROM tpcw.item, tpcw.order_line
WHERE ol_o_id = :o_id
AND ol_i_id = i_id;
IF $rc = 0 THEN
BEGIN
FETCH INTO :i_id1, :i_title1, :i_publisher1, :i_cost1,
:ol_qty1, :ol_discount1, :ol_comments1;
END;
END;
END;
END;
