TRIM function Issue

Posted in 2010

Topics: Stored Procedures & SPL, Transactions, Locking & Isolation

DHARMENDRA SHARMA — Wed, 03 Feb 2010 01:31:09 — source: IIUG Forums & Mailing Lists

Hi Team, I have created following database function. It works fine if I run it on Informix 11.5 but not on 9.4. I tracked down the error and found out that it is realted to a TRIM function. In my case, when the length of lv_ColumnList goes beyond 255 characters, I get an error. Otherwise, it works fine on both the servers. As I mentioned, it works okay on 11.5 eventhough the length of lv_ColumnList is more than 255 characters. Any other suggestion/alernate way to get the same result n 9.4: Here is an error " 881: Resulting string length must be less than or equal to 255." --Just in case if you would like to know what this function does. --I am trying to get the list of columns for a given table in a --comma separated format. CREATE Function GetColumnList(pv_table_name char(200)) Returning char(6000) as rv_ColumnList; DEFINE lv_ColumnList CHAR(6000); DEFINE lv_colName char(100); DEFINE lv_rec_count smallint; SET ISOLATION TO DIRTY READ; LET lv_ColumnList = NULL; LET lv_colName = NULL; LET lv_rec_count = 0; FOREACH SELECT colName INTO lv_colName FROM syscolumns WHERE tabid IN (SELECT tabid FROM systables WHERE tabname = pv_table_name ) ORDER BY colno IF lv_rec_count = 0 THEN LET lv_ColumnList = TRIM(lv_colName); ELSE LET lv_ColumnList = TRIM(lv_ColumnList) || "," || TRIM(lv_colName); END IF LET lv_rec_count = lv_rec_count + 1; END FOREACH; RETURN lv_ColumnList; End Function; Thanks in advance. Dharmendra

↪ replying to DHARMENDRA SHARMA

Art Kagel — Wed, 03 Feb 2010 02:15:27 — source: IIUG Forums & Mailing Lists

You'll have to check the manuals, or search the forum history to verify this, but IB that in 9.4 string concatenation results in SPL defaulted to type VARCHAR which is limited to 255 characters. In 11.50 these operations result in an LVARCHAR which is limited to 32K just like CHAR. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Feb 2, 2010 at 8:31 PM, DHARMENDRA SHARMA < dharmendrasharma@hotmail.com> wrote: > Hi Team, > > I have created following database function. It works fine if I run it on > Informix 11.5 but not on 9.4. I tracked down the error and found out that > it > is realted to a TRIM function. In my case, when the length of lv_ColumnList > goes beyond 255 characters, I get an error. Otherwise, it works fine on > both > the servers. As I mentioned, it works okay on 11.5 eventhough the length of > lv_ColumnList is more than 255 characters. > > Any other suggestion/alernate way to get the same result n 9.4: > > Here is an error " 881: Resulting string length must be less than or equal > to > 255." > > --Just in case if you would like to know what this function does. > --I am trying to get the list of columns for a given table in a > --comma separated format. > > CREATE Function GetColumnList(pv_table_name char(200))> > Returning char(6000) as rv_ColumnList; > > DEFINE lv_ColumnList CHAR(6000); > DEFINE lv_colName char(100); > DEFINE lv_rec_count smallint; > > SET ISOLATION TO DIRTY READ;> > LET lv_ColumnList = NULL; > LET lv_colName = NULL; > LET lv_rec_count = 0; > FOREACH SELECT colName INTO lv_colName > > FROM syscolumns WHERE tabid IN > > (SELECT tabid FROM systables WHERE tabname = pv_table_name ) > > ORDER BY colno > > IF lv_rec_count = 0 THEN > > LET lv_ColumnList = TRIM(lv_colName); > > ELSE > > LET lv_ColumnList = TRIM(lv_ColumnList) || "," || TRIM(lv_colName); > > END IF > > LET lv_rec_count = lv_rec_count + 1; > END FOREACH; > > RETURN lv_ColumnList; > > End Function; > > Thanks in advance. > > Dharmendra > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --002354530644581855047ea8ca54

↪ replying to Art Kagel

John Miller iii — Wed, 03 Feb 2010 07:56:20 — source: IIUG Forums & Mailing Lists

There was an improvement to version 10 that allowed the TRIM() function to work on strings longer than 255 bytes. John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 02/02/2010 06:15:28 PM: > You'll have to check the manuals, or search the forum history to verify > this, but IB that in 9.4 string concatenation results in SPL defaulted to > type VARCHAR which is limited to 255 characters. In 11.50 these operations > result in an LVARCHAR which is limited to 32K just like CHAR. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > IIUG Board of Directors (art@iiug.org) > > See you at the 2010 IIUG Informix Conference > April 25-28, 2010 > Overland Park (Kansas City), KS > www.iiug.org/conf > > Disclaimer: Please keep in mind that my own opinions are my own opinions and > do not reflect on my employer, Advanced DataTools, the IIUG, nor any other > organization with which I am associated either explicitly, implicitly, or by > inference. Neither do those opinions reflect those of other individuals > affiliated with any entity with which I am affiliated nor those of the > entities themselves. > > On Tue, Feb 2, 2010 at 8:31 PM, DHARMENDRA SHARMA < > dharmendrasharma@hotmail.com> wrote: > > > Hi Team, > > > > I have created following database function. It works fine if I run it on > > Informix 11.5 but not on 9.4. I tracked down the error and found out that > > it > > is realted to a TRIM function. In my case, when the length of lv_ColumnList > > goes beyond 255 characters, I get an error. Otherwise, it works fine on > > both > > the servers. As I mentioned, it works okay on 11.5 eventhough the length of > > lv_ColumnList is more than 255 characters. > > > > Any other suggestion/alernate way to get the same result n 9.4: > > > > Here is an error " 881: Resulting string length must be less than or equal > > to > > 255." > > > > --Just in case if you would like to know what this function does. > > --I am trying to get the list of columns for a given table in a > > --comma separated format. > > > > CREATE Function GetColumnList(pv_table_name char(200))> > > > Returning char(6000) as rv_ColumnList; > > > > DEFINE lv_ColumnList CHAR(6000); > > DEFINE lv_colName char(100); > > DEFINE lv_rec_count smallint; > > > > SET ISOLATION TO DIRTY READ;> > > > LET lv_ColumnList = NULL; > > LET lv_colName = NULL; > > LET lv_rec_count = 0; > > FOREACH SELECT colName INTO lv_colName > > > > FROM syscolumns WHERE tabid IN > > > > (SELECT tabid FROM systables WHERE tabname = pv_table_name ) > > > > ORDER BY colno > > > > IF lv_rec_count = 0 THEN > > > > LET lv_ColumnList = TRIM(lv_colName); > > > > ELSE > > > > LET lv_ColumnList = TRIM(lv_ColumnList) || "," || TRIM(lv_colName); > > > > END IF > > > > LET lv_rec_count = lv_rec_count + 1; > > END FOREACH; > > > > RETURN lv_ColumnList; > > > > End Function; > > > > Thanks in advance. > > > > Dharmendra > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --002354530644581855047ea8ca54 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Glossary

isolation level