use stored proc that return multiple columns in a query

[brenddie]

Is there a way of using a stored procedure that returns multiple
columns in query?

I have a stored proc that returns 2 columns and would like to use it
inside a query. Something like this:

select product_id,get_product_info(1) from products;

assuming get_product_info() returns price and description

This is a simplification of the problem and not the real problem I'm
trying to solve. The stored procedure I want to use has business logic
that cant be implemented just linking tables.
Currently the stored procedure is used in 4gl without issues.

So far I have tried different variations of the query but haven't have
luck.
Using isql/dbacces running the following:

execute procedure get_product_info(1) ;

684: Function (informix.get_product_info) returns too many values.

select id,get_product_info(id) from tmpproducts;

684: Function (informix.get_product_info) returns too many values.

SELECT * FROM TABLE(FUNCTION(get_product_info(1) )) as x1(p1,p2);

9786: Only iterative UDR allowed in this context.

scripts for the test:

create table 'informix'.tmpproducts (
id CHAR(20)
)
extent size 32 next size 32
lock mode page;

CREATE PROCEDURE get_product_info(product_id INTEGER ) RETURNING
MONEY(10,2), char(20);

DEFINE product_price MONEY(10,2);
DEFINE product_desc char(20);

IF(product_id = 1)THEN
LET product_price = "$10";
LET product_desc = "JUICE";
ELIF (product_id = 1)THEN
LET product_price = "$20";
LET product_desc = "BEER";
ELIF (product_id = 1)THEN
LET product_price = "$40";
LET product_desc = "NUTS";
ELIF (product_id = 1)THEN
LET product_price = "$80";
LET product_desc = "TOMATOES";
ELSE
LET product_price = "0";
LET product_desc = "ERROR";
END IF

RETURN product_price,product_desc;

END PROCEDURE;

[brenddie]

forgot to include version info:

IBM Informix Dynamic Server Version 10.00.UC6W5 -- On-Line -- Up 207
days 22:14:39 -- 1441100 Kbytes

[LIGHT SCANS]

I'm a bit rusty on stored procedures but I do have a "hack solution".
You can concatonate multiple columns so that they appear as 1. A SQL
example is:

SELECT column1 || column2 FROM table1 . . .

-L.S.

[brenddie]

I tough about that but as the stored proc is used elsewhere I cant
modify the return values.
Another alternative is witting a wrapper that will call the stored
proc and return one of the vales depending of an input flag. Then I
would have to make to separate calls to get price and description but
I think it will hit performance.

[Art Kagel]

Maybe I'm just being dense, but why a stored procedure for this product descriptive information in the first place?  Why not just convert the stored procedure to a table and query/join the table:

create table product_id_info( product_id int, price money(10,2), description char(20) );
insert into product_id_info values (1, 10.00, "JUICE" );
...
CREATE UNIQUE INDEX product_id_info_pk ON product_id_info( product_id);
ALTER TABLE product_id_info ADD CONSTRAINT PRIMARY KEY( product_id )
CONSTRAINT product_id_info_pk;

SELECT p.product_id, pii.price, pii.description
FROM product as p, product_id_info as pii
WHERE p.product_id = pii.product_id
    AND p.product_id = 20;

As to any existing code that's already dependent on the procedure, just rewrite it to fetch the data from the table instead of from the IF.. ELIF...END IF clause!

Sounds like you're suffering from the hammer syndrome.

Art

_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (a...@iiug.org)

Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly.  Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

[brenddie]

On Oct 6, 5:46 pm, "Art Kagel" <art.ka...@gmail.com> wrote:
> Maybe I'm just being dense, but why a stored procedure for this product
> descriptive information in the first place?  Why not just convert the stored
> procedure to a table and query/join the table:
>
> create table product_id_info( product_id int, price money(10,2), description
> char(20) );
> insert into product_id_info values (1, 10.00, "JUICE" );
> ...
> CREATE UNIQUE INDEX product_id_info_pk ON product_id_info( product_id);
> ALTER TABLE product_id_info ADD CONSTRAINT PRIMARY KEY( product_id )
> CONSTRAINT product_id_info_pk;
>
> SELECT p.product_id, pii.price, pii.description
> FROM product as p, product_id_info as pii
> WHERE p.product_id = pii.product_id
>     AND p.product_id = 20;
>
> As to any existing code that's already dependent on the procedure, just
> rewrite it to fetch the data from the table instead of from the IF..
> ELIF...END IF clause!
>
> Sounds like you're suffering from the hammer syndrome.
>

This is a very old production system I'm trying to maintain. All the
business logic was written on 4gl. Slowly some of the logic has been
converted to stored procedures so they can be used by external
applications without needing to rewrite the business logic on the
client application.
Maybe I chose a bad example by using a products scenario. The stored
proc in the example was something I wrote real quick to demonstrate
the errors Im getting. The real problem involves a more complicated
setup that involves a lot of business logic. Following the example of
a price/description, the price would be calculated following some
rules that are not represented in any tables. I wasn't around when
they decided to do it this way and I cant change most of this stuff
without breaking the rest of the system.

If I could execute a stored procedure that returns multiple columns
inside a query that would save me from messing with the legacy code or
having to rewrite the logic on the client application.

Thanks for your reply

[Dirk Gunsthövel]

Hi there,

Arts remark and warning is correct. Still there are situations
where you "inherited" such a procedure, cant redo the
whole design and must make it usable in selects. Often
wrappers for the individual values are ok - i.e. if performance
is not a big issue. If it is, there is no easy way.

The following works for me:

Lets assume your procedure name is myproc and it returns
two values a of type a_type and b of type b_type. Lets also assume
it has one input parameter inp of some type. You want to call
the procedure in a select on table mytable on col0. Some other
column col1 of this table is also to be selected.

Apparently
SELECT myproc(col0), col1 FROM mytable WHERE ...
cant work as you know.

Now the following steps will get you what you want:

1. Define a rowtype myrowtype containing fields a of a_type
and b of b_type

2. Write a wrapper mywrapper for your stored procedure
returning myrowtype by calling the original procedure and setting
the values a and b in the myrowtype object being returned.

3. This wrapper procedure can now be used in selects:
SELECT mywrapper(col0), col1 FROM mytable
will work. But: most clients will have problems in getting
a rowtype value from the resultset.

4. Therefore, we rewrite the result to a simple resultset:
SELECT val.a, val.b, col1 FROM
TABLE (
MULTISET(
SELECT mywrapper(col0) AS val, col1 FROM mytable WHERE...
)
)

It is a good idea to hide this statement in a view.

Hope this helps.

Regards,
Dirk

--
--
-- Dipl.-Math. Dirk Gunsthövel
-- -professional services-
--
-- Dirk Gunsthövel IT Systemanalyse - GunCon
-- Hammer Str. 13
-- D-48153 Muenster
-- phone: +49 (0) 251 28446- 0
-- fax: +49 (0) 251 28446-55
-- web: http://www.GunCon.de
-- email: in...@GunCon.de
-- UStId: DE 189527667
--
-- 'One now understands why some animals eat their young.'
-- (Andrew in 'Bicentennial Man' 1999)

"brenddie" <bren...@gmail.com> schrieb im Newsbeitrag
news:18e77dff-49ba-4b97...@u46g2000hsc.googlegroups.com...

[brenddie]

This sounds exactly like what Im looking for. I created a custom data
type with the required fields. Then tried to create a wrapper but I'm
stuck trying to return the custom data type.

Following my previous example,

Created a data type

create row type 'informix'.product_info_type (
price MONEY(12,2),
description VARCHAR(100))

Then tried to create a wrapper around get_product_info()

CREATE PROCEDURE get_product_info_wrapper(product_id INTEGER )
RETURNING product_info_type;
DEFINE product_info_type type product_info_type;

CALL get_product_info(product_id)
RETURNING product_info_type.price, product_info_type.description;

RETURN product_info_type;

END PROCEDURE;

But I'm stuck trying to figure out the correct usage of row types. I
haven't worked with custom data types/rows before and cant find the
correct way of declaring and returning row types. This must be basic
stuff but I cant find any examples on the documentation or anywhere on
the internet.

How do you define, assign and return row types ?

Thanks

[Superboer]

hmmm....

create row type mytype ( a int, b char(18));

create function x( myin int)
returning mytype;

define ret mytype;
define anint int;
define achar char(18);

let ret = row(1,"novalue") :: mytype ;
select customer_num , lname into anint , achar
from customer where customer_num = myin;

let ret = row(anint,achar) :: mytype ;

return ret;
end function;

select x(customer_num) , * from customer

.... i would NOT put all the logic into spl, this is a sure way to
kill yourself and asking for problems,
it will/can cause memory issues locking etc etc.
please reconsider, use spls when needed, too much and you will be in
trouble.

Superboer.

[Art Kagel]

See my comment below in the procedure declaration:

Art

Two syntax problems in the line above:

1. the variable has the same name as the type, and
2. there is an extraneous keyword 'type' there.
3. need the keyword 'ROW'
   

So, the declaration should be something like:

  DEFINE product_info_var ROW product_info_type;
 

  CALL get_product_info(product_id)
  RETURNING product_info_type.price, product_info_type.description;

  RETURN product_info_type;

Which means this becomes:

   CALL get_product_info( product_id )
       RETURNING product_info_var.price, product_info_var.description;

   RETURN product_info_var;
 

END PROCEDURE;

But I'm stuck trying to figure out the correct usage of row types. I
haven't worked with custom data types/rows before and cant find the
correct way of declaring and returning row types. This must be basic
stuff but I cant find any examples on the documentation or anywhere on
the internet.

How do you define, assign and return row types ?


Thanks

_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

[brenddie]

Thanks both for your responses. I go it working using the
recommendations you provided.

following the previous example, the wrapper became

CREATE PROCEDURE get_product_info_wrapper(product_id INTEGER )
RETURNING product_info_type;

--DEFINE product_info_var ROW product_info_type;
DEFINE price MONEY(12,2);
DEFINE description VARCHAR(100);

CALL get_product_info(product_id)
RETURNING price,description;

RETURN ROW(price,description)::product_info_type;

END PROCEDURE;

and running a test
select id,get_product_info_wrapper(id) from tmpproducts;
id 4
(expression) ROW('$80.00','TOMATOES ')

id 3
(expression) ROW('$40.00','NUTS ')

id 1
(expression) ROW('$10.00','JUICE ')

id 2
(expression) ROW('$20.00','BEER ')

Tonight I'll start testing this on the real production functions. The
only weird thing is that running this from isql gives an error:
1820: Host variable type has been changed between fetches or puts.

dbaccess runs it without issue. I'll test later tonight using ODBC
and .NET to determine if a view will be needed.

Thanks all

[robsosno]

brenddie pisze:

Alternative approach is to use output parameters on condition that one
return record is enough (your example suggest that).

My example:
Create Procedure get_product_info(product_id integer, out product_price
money(10,2), out product_desc Char(20))
returning int;

... setting product_price and product_desc ...
return 1;
end procedure;

Then you can do following select:

select f1, f2
from products
where get_product_info(product_id, f1#money(10,2), f2#Char(20))=1

I don't remember if you should specify Char(20) or just Char. You have
an idea, remaining details are in documentation.

Regards,

Robsosno