Oninit cdi / Archive
Talk to an expert →

Stored Procedures and Parameters

Posted in 1999

Topics: Stored Procedures & SPL, Data Types & Schema Design, Platform-Specific Issues

benlag@aent.com — — source: Informix-list mailing list archive (1991-1998)

I am working with Informix 7.3 UC8-1 on HP-UX10.20. I have a request by a user for a stored procedure that returns customer ordering information given the confirmation number. The only problem is that Informix will not allow me to pass TEXT data type variable to the caller of the stored procedure. The variable needs to be a TEXT data type because orders vary in size. If anyone has ever resolved a problem like this before or can suggest an alternate method I would really appreciate it. Benny Lago Alliance Entertainment, Inc. benlag@aent.com Sent via Deja.com http://www.deja.com/ Before you buy.

↪ replying to benlag@aent.com
Carlson@WHSmith — — source: Informix-list mailing list archive (1991-1998)

If it's just a matter of size or length then could you use a VARCHAR? John Carlson Informix DBA WHSmith USA benlag@aent.com wrote: > > I am working with Informix 7.3 UC8-1 on HP-UX10.20. > I have a request by a user for a stored procedure that returns customer > ordering information given the confirmation number. The only problem is > that Informix will not allow me to pass TEXT data type variable to the > caller of the stored procedure. The variable needs to be a TEXT data > type because orders vary in size. > > If anyone has ever resolved a problem like this before or can suggest an > alternate method I would really appreciate it. > > Benny Lago > Alliance Entertainment, Inc. > benlag@aent.com > > Sent via Deja.com http://www.deja.com/ > Before you buy.

↪ replying to benlag@aent.com
Rudy Fernandes — — source: Informix-list mailing list archive (1991-1998)

TEXT is not a problem for SPs. The manual (SP chapter in SQL Syntax) is reasonably clear about it. Here's some basic sample code running on 7.31/10.20. CREATE PROCEDURE get_text ( l_record_id LIKE some_table.record_id ) RETURNING INTEGER, /* Error Code 0-Success, 100-Not found */ REFERENCES TEXT; /* Text data */ DEFINE l_text_data REFERENCES TEXT; /* Text Data */ DEFINE l_nrows INT; SELECT text_data INTO l_text_data FROM some_table WHERE record_id = l_record_id; LET l_nrows = DBINFO('sqlca.sqlerrd2'); IF l_nrows = 0 THEN RETURN 100, l_text_data; END IF; RETURN 0, l_text_data; END PROCEDURE; CREATE PROCEDURE put_text ( l_text_data LIKE some_table.text_data) RETURNING INTEGER; /* Serial value of inserted Row */ INSERT INTO some_table (create_user, create_tm, updt_user, updt_tm, text_data) VALUES ( USER, CURRENT, USER, CURRENT, l_text_data); RETURN DBINFO('sqlca.sqlerrd1'); /* Serial # of the inserted row */ END PROCEDURE; dbaccess can happily display get_text()'s output. However, your client program which uses these SPs has to use the loc_t variable to hold TEXT data. HTH Rudy benlag@aent.com wrote: > I am working with Informix 7.3 UC8-1 on HP-UX10.20. > I have a request by a user for a stored procedure that returns customer > ordering information given the confirmation number. The only problem is > that Informix will not allow me to pass TEXT data type variable to the > caller of the stored procedure. The variable needs to be a TEXT data > type because orders vary in size. > > If anyone has ever resolved a problem like this before or can suggest an > alternate method I would really appreciate it. > > Benny Lago > Alliance Entertainment, Inc. > benlag@aent.com > > Sent via Deja.com http://www.deja.com/ > Before you buy.