Space-padded Strings and Stored Procedures

I'm running Informix Dynamic Server Version 7.30.UC7 (straight from onstat!). I have a stored procedure to perform an audit trailing function. It takes in an ID, performs a lookup on the appropriate table using this ID to get a "name", and then enters this name into the audit table, thusly: CREATE PROCEDURE valueadded(vid INT) DEFINE valuename VARCHAR(255); DEFINE details VARCHAR(255); SELECT fullname INTO valuename FROM value WHERE valueid=vid; LET details="Added " || valuename || " value"; INSERT INTO audit (occurred, comment) VALUES (CURRENT, details);END PROCEDURE; My problem is that the "fullname" field in the "value" table is a char 128 - and thus gets padded out with spaces. This means that there is a tremendous amount of surplus white space before the word 'value' in the log. [Obviously this example is rather artificial, but I can't give exact code snippets] Is there any way of "trimming" this space? It's no problem to me when I use Java or Perl (the main apps running on the DB) because I can .trim() or s/\\s+$// respectively, but I can't find a similar mechanism to do this in Stored Procedures - and for performance reasons I don't want to change the value table to use a varchar for the fullname attribute. Anyone have any good ideas? Many thanks in advance, Paul

Paul Harman wrote: > I'm running Informix Dynamic Server Version 7.30.UC7 (straight from > onstat!). > > I have a stored procedure to perform an audit trailing function. It takes in > an ID, performs a lookup on the appropriate table using this ID to get a > "name", and then enters this name into the audit table, thusly: > > CREATE PROCEDURE valueadded(vid INT) > DEFINE valuename VARCHAR(255);> DEFINE details VARCHAR(255); > > SELECT fullname INTO valuename FROM value WHERE valueid=vid; > LET details="Added " || valuename || " value"; > INSERT INTO audit (occurred, comment) VALUES (CURRENT, details);> END PROCEDURE; > > My problem is that the "fullname" field in the "value" table is a char 128 - > and thus gets padded out with spaces. This means that there is a tremendous > amount of surplus white space before the word 'value' in the log. > > [Obviously this example is rather artificial, but I can't give exact code > snippets] > > Is there any way of "trimming" this space? It's no problem to me when I use > Java or Perl (the main apps running on the DB) because I can .trim() or > s/\\s+$// respectively, but I can't find a similar mechanism to do this in > Stored Procedures - and for performance reasons I don't want to change the > value table to use a varchar for the fullname attribute. > > Anyone have any good ideas? Many thanks in advance, > > Paul have you tried CLIPPED ? example: CREATE PROCEDURE valueadded(vid INT) DEFINE valuename VARCHAR(255); DEFINE details VARCHAR(255); SELECT fullname INTO valuename FROM value WHERE valueid=vid; LET details="Added " || valuename || " value" CLIPPED; INSERT INTO audit (occurred, comment) VALUES (CURRENT, details);END PROCEDURE; -- Compliments of QueriX -------------------------------------------------------------------------------------------------- QueriX 4GL Compilers are Informix 4GL Compatible, and Connection to other RDBMS such as Oracle. Hydra 4GL Compiler (Compatible with I4GL) Compile once, run everywhere Phoenix Windows GUI. (Front End to 4GL) Chimera Java GUI The only GUI you will ever need... (Front End to 4GL) Arachne Web Technology (Front End to 4GL on the Web) For more details visit: http://www.querix.com/ ---------------------------------------------------------------------------------------------------

The function you are looking for is "TRIM". For example: LET details="Added " || TRIM(valuename) || " value"; -- Irwin Goldstein Objective Software Systems, Inc. http://www.objectsoft.com In article <w7So4.263$7R.3870@news.colt.net>, "Paul Harman" <paul@kasterborus.demon.co.uk> wrote: > I'm running Informix Dynamic Server Version 7.30.UC7 (straight from > onstat!). > > I have a stored procedure to perform an audit trailing function. It takes in > an ID, performs a lookup on the appropriate table using this ID to get a > "name", and then enters this name into the audit table, thusly: > > CREATE PROCEDURE valueadded(vid INT) > DEFINE valuename VARCHAR(255);> DEFINE details VARCHAR(255); > > SELECT fullname INTO valuename FROM value WHERE valueid=vid; > LET details="Added " || valuename || " value"; > INSERT INTO audit (occurred, comment) VALUES (CURRENT, details);> END PROCEDURE; > > My problem is that the "fullname" field in the "value" table is a char 128 - > and thus gets padded out with spaces. This means that there is a tremendous > amount of surplus white space before the word 'value' in the log. > > [Obviously this example is rather artificial, but I can't give exact code > snippets] > > Is there any way of "trimming" this space? It's no problem to me when I use > Java or Perl (the main apps running on the DB) because I can .trim() or > s/\\s+$// respectively, but I can't find a similar mechanism to do this in > Stored Procedures - and for performance reasons I don't want to change the > value table to use a varchar for the fullname attribute. > > Anyone have any good ideas? Many thanks in advance, > > Paul > > -- Irwin Goldstein Objective Software Systems, Inc. http://www.objectsoft.com Sent via Deja.com http://www.deja.com/ Before you buy.