SP: Returning a string larger than 255?

I've searched the archives and can't find an answer so I'm hoping someone here has one for me! I'm writing a stored procedure (ver. 7.3) that concatenates strings and returns 1 long string. It appears as if I cannot return a string larger than 255. It also appears as if arrays are not supported. Has anyone any experience or a workaround for this? Can anyone point me in the right direction? TIA, Katrina Sent via Deja.com http://www.deja.com/ Before you buy.

Hi, don`t know, if this will help? i use IDS 7.31 and tested your problem. this code will run perfectly: CREATE PROCEDURE concat(f1 CHAR(400), f2 char(400) ) RETURNING char(800);DEFINE result CHAR(800); LET result = f1 || f2; RETURN result; END PROCEDURE; ingo katrina@montopolis.com schrieb: > I've searched the archives and can't find an answer so I'm hoping > someone here has one for me! > > I'm writing a stored procedure (ver. 7.3) that concatenates strings and > returns 1 long string. It appears as if I cannot return a string > larger than 255. It also appears as if arrays are not supported. > > Has anyone any experience or a workaround for this? Can anyone point > me in the right direction? > > TIA, > Katrina > > Sent via Deja.com http://www.deja.com/ > Before you buy.

Katrina, In response, I think we've run into the same kind of problem here in the 7.23 world (I say that I think that because I didn't completely test the issue, but I did determine that the >255 character string does present problems at least in some cases). And you are correct that arrays (much to my chagrin) are not supported in stored procedures. The only solution I was able to implement was to break the return string up into separate variables, which would defeat your purpose of concatenating the strings. I would ask, however, why you're using a stored procedure to do the string concatenation. Stored procedures are notoriously non-scalable, and not terribly efficient for string manipulation. I'd go with concatenating the strings in your application rather than in the stored procedure. This will not only solve your problem, but increase the speed and scalability of your application. Sorry I couldn't give you better news... -- Dan Michaelis Database Administrator dan@kax.com Sent via Deja.com http://www.deja.com/ Before you buy.

Katrina - You are correct, this limitation does exist. Howeve, it exists only for varchar() fields. For char() fields, the length can be quite substantially longer (many K). If you can trim the trailing blanks you get with char() fields somewhere, that might work for you. I also agree that a stored procedure is not the best way to contcatenate strings IF that is the primary function of the procedure. Also, although you cannot return Arrays as an actual Array data type, if you use the "WITH RESUME" clause on the "RETURN" statement placed in a loop, your stored procedure can return an almost unlimited number of records in a single result set, which your application could easily form into an Array. How did you finally solve this? Rich katrina@montopolis.com wrote: > I've searched the archives and can't find an answer so I'm hoping > someone here has one for me! > > I'm writing a stored procedure (ver. 7.3) that concatenates strings and > returns 1 long string. It appears as if I cannot return a string > larger than 255. It also appears as if arrays are not supported. > > Has anyone any experience or a workaround for this? Can anyone point > me in the right direction? > > TIA, > Katrina > > Sent via Deja.com http://www.deja.com/ > Before you buy. -- Richard C. Auslander Database Manager AirFlash, Inc. 1733 Woodside Rd., Suite #110 Redwood City, CA 94061 (650) 556-7928 www.airflash.com

Thanks for the responses! I've e-mailed the others privately but am posting this back to the group to get more ideas! We are attempting to do this via a stored procedure in an effort to have a universal method of retrieving this data and to bypass some limitations of our front-end. Here's some background: Our application is a PowerBuilder 6.5 app. This section of it was written 6 years ago (and since migrated). At that time BLOB's did not have search capabilities (ie. we couldn't search the blob). This was a requirement of the application so to circumvent this we chopped the text entered by the user into 70 char strings and saved them to a char field. We could then search the data. A given key could have 1 detail line (70 char field) and up. In reality we have a few that have 100 detail rows but not many more. In order to do the chopping we converted carriage returns to a ^. The app itself retrieves and updates these rows with no problem (with the front-end converting the ^'s back). Our problem arises with the reports. Due to the way the reports work (the retrieves) our data can only be retrieve and displayed line by line (no converting of the ^'s etc.). So it's pretty darn messy and doesn't match what the users entered as far as formatting. Without getting into a lengthy description of the limitations of these reports...we cannot really add any front-end code to the report to accomplish this. So.... The SP was to grab all the detail rows for the given key, strip out the ^, add the carriage return, concatenate the strings together and return one long string that we could then just place on the report. We can't do this because of the string length limitation. So, why don't we convert the app to save this text as a BLOB and all of our problems are solved? We can..but we first have to justify to the client (who ultimatley will pay for the effort) that we can't do this easily and quickly and via a SP. Now...I'm off to look into SP's returning BLOB's. I've never worked with them before so this is new territory.... 1. Can a SP concatenate strings into a BLOB? 2. Can a SP return a BLOB? Thanks everyone! - Katrina In article <86591q$nqq$1@nnrp1.deja.com>, Dan Michaelis <dan_michaelis@my-deja.com> wrote: > Katrina, > > In response, I think we've run into the same kind of problem here in the > 7.23 world (I say that I think that because I didn't completely test the > issue, but I did determine that the >255 character string does present > problems at least in some cases). And you are correct that arrays > (much to my chagrin) are not supported in stored procedures. The only > solution I was able to implement was to break the return string up into > separate variables, which would defeat your purpose of concatenating the > strings. > > I would ask, however, why you're using a stored procedure to do the > string concatenation. Stored procedures are notoriously non-scalable, > and not terribly efficient for string manipulation. I'd go with > concatenating the strings in your application rather than in the stored > procedure. This will not only solve your problem, but increase the > speed and scalability of your application. > > Sorry I couldn't give you better news... > > -- > Dan Michaelis > Database Administrator > dan@kax.com > > Sent via Deja.com http://www.deja.com/ > Before you buy. > Sent via Deja.com http://www.deja.com/ Before you buy.