Return trailing spaces-stored procedure
A user on IDS 7.3 wanted a stored procedure to return a CHAR(5) argument with its trailing spaces intact ('ab ' not 'ab'), noting LENGTH() reported 2. Responders explained CHAR values are always blank-padded to their defined length per ANSI, LENGTH() ignores trailing blanks by definition, and declaring the parameter/return as VARCHAR (or LVARCHAR) preserves real trailing spaces. Art Kagel added that the procedure does return the spaces and that the client (e.g. ESQL/C 'string' host variables, ODBC, or the AGS Server Studio front end) is likely stripping them. No confirmation from the poster is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
I have a problem with trailing spaces.
I need to retrieve from stored procedure a data with trailing spaces.
So, if SP are:
create procedure myproc(x char(5))
returning char(5);return x;
end procedure
and when I call sp:
execute procedure myproc('ab ')
I need from SP to return string 'ab ' and not 'ab'. I my case returned value
are allways padded. Actually, if I check a data ni myproc variable x is with
length of 2 not 5.
I use Informix DB ver: 7.3
Thanks in advanced
Using a varchar will preserve the space at the end.
create procedure myproc(x varchar(5))
returning varchar(5);return x;
end procedure ;
select myproc('ab ')||"JOHN"
from systables where tabid = 99
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 09/16/2010 02:03:23 AM:
> From:
>
> "SPASO LAZAREVIC" <spasobn@gmail.com>
>
> To:
>
> ids@iiug.org
>
> Date:
>
> 09/16/2010 02:04 AM
>
> Subject:
>
> Return trailing spaces-stored procedure [21305]
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Hi,
> I have a problem with trailing spaces.
> I need to retrieve from stored procedure a data with trailing spaces.
> So, if SP are:
>
> create procedure myproc(x char(5))
> returning char(5);> return x;
> end procedure
>
> and when I call sp:
>
> execute procedure myproc('ab ')>
> I need from SP to return string 'ab ' and not 'ab'. I my case returned
value
> are allways padded. Actually, if I check a data ni myproc variable x is
with
> length of 2 not 5.
>
> I use Informix DB ver: 7.3
> Thanks in advanced
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Thanks John for your reply, but the trick is that I have many parameters in stored procedure, so for some parameters I need to return them 'as is' from sp. Function LENGTH doesn't do what I expect because function not including any trailing blank spaces. Some parameters that I call sp are for example 'abc ' and I need to return from sp the same data without changes: 'abc '. Please do not judge for these reason, cause it's business demand. Thanks
On Thu, Sep 16, 2010 at 10:03 AM, SPASO LAZAREVIC <spasobn@gmail.com> wrote:
> Hi,
> I have a problem with trailing spaces.
> I need to retrieve from stored procedure a data with trailing spaces.
> So, if SP are:
>
> create procedure myproc(x char(5))
> returning char(5);> return x;
> end procedure
>
> and when I call sp:
>
> execute procedure myproc('ab ')>
> I need from SP to return string 'ab ' and not 'ab'. I my case returned
> value
> are allways padded. Actually, if I check a data ni myproc variable x is
> with
> length of 2 not 5.
>
> I use Informix DB ver: 7.3
> Thanks in advanced
>
>
By definition, the LENGTH() function ignores the trailing space(s):
http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.glsug.doc/id
s_gug_147.htm
As John already mentioned a VARCHAR will keep the the trailing spaces.
Regards.
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--0015175cb54285849804905d4de0
The procedure IS returning 'ab '. Your front-end is stripping the
trailing spaces.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
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 Thu, Sep 16, 2010 at 5:03 AM, SPASO LAZAREVIC <spasobn@gmail.com> wrote:
> Hi,
> I have a problem with trailing spaces.
> I need to retrieve from stored procedure a data with trailing spaces.
> So, if SP are:
>
> create procedure myproc(x char(5))
> returning char(5);> return x;
> end procedure
>
> and when I call sp:
>
> execute procedure myproc('ab ')>
> I need from SP to return string 'ab ' and not 'ab'. I my case returned
> value
> are allways padded. Actually, if I check a data ni myproc variable x is
> with
> length of 2 not 5.
>
> I use Informix DB ver: 7.3
> Thanks in advanced
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001636310869c4198b04905d6bea
Thanks Art, I figure out, but I need to prove it. For backup purpose, beside from returning value from sp, I insert data into db table and I got the same result. Data are without trailing blanks. In db I create field with varchar and char type. Some other system call my sp so I expect answer from them to find what they receive (with or without trailing blanks). I user AGS Server Studio for manipulating sp's and for viewing results from my sp.
On Thu, Sep 16, 2010 at 10:53 AM, Art Kagel <art.kagel@gmail.com> wrote:
> The procedure IS returning 'ab '. Your front-end is stripping the
> trailing spaces.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> IIUG Board of Directors (art@iiug.org)
>
> 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 Thu, Sep 16, 2010 at 5:03 AM, SPASO LAZAREVIC <spasobn@gmail.com>
> wrote:
>
> > Hi,
> > I have a problem with trailing spaces.
> > I need to retrieve from stored procedure a data with trailing spaces.
> > So, if SP are:
> >
> > create procedure myproc(x char(5))
> > returning char(5);> > return x;
> > end procedure
> >
> > and when I call sp:
> >
> > execute procedure myproc('ab ')> >
> > I need from SP to return string 'ab ' and not 'ab'. I my case returned
> > value
> > are allways padded. Actually, if I check a data ni myproc variable x is
> > with
> > length of 2 not 5.
> >
> > I use Informix DB ver: 7.3
> > Thanks in advanced
>
>
Actually the value returned is always padded to the CHAR definition length.
I'm not sure what the problem is:
- Is it "chopping" the trailing spaces or...
- It it adding the extra spaces?
If it's doing the last one, it's correct. This is accordingly to the ANSI
definition and will not change either we like it or not.
As John mentioned, the VARCHAR will preserve the spaces inserted and will
NOT pad.
Evidences:
(better seen with monspaced font, but a copy and execution will be even
better)
panther@pacman.onlinedomus.net:fnunes-> cat test.sh
#!/bin/ksh
dbaccess stores <<EOF 2>/dev/null
DROP PROCEDURE test_char;
DROP PROCEDURE test_varchar;
CREATE PROCEDURE test_char ( x CHAR(5)) RETURNING CHAR(5)RETURN x;
END PROCEDURE;
CREATE PROCEDURE test_varchar ( x VARCHAR(5)) RETURNING VARCHAR(5)RETURN x;
END PROCEDURE;
SELECT test_char('abc ')||"HELLO FROM CHAR" FROM systables WHERE tabid = 1;
SELECT test_varchar('abc ')||"HELLO FROM VARCHAR" FROM systables WHERE tabid
= 1;
SELECT
"abc "::CHAR(5)||"HELLO FROM CHAR",
"abc "::VARCHAR(5)||"HELLO FROM VARCHAR"
FROM systables
WHERE tabid = 1
EOF
panther@pacman.onlinedomus.net:fnunes-> ./test.sh
(expression)
abc HELLO FROM CHAR
(expression)
abc HELLO FROM VARCHAR
(constant) (constant)
abc HELLO FROM CHAR abc HELLO FROM VARCHAR
panther@pacman.onlinedomus.net:fnunes->
Regards,
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--0016369f9e30e7d97a04905df825
If you create a small ESQL/C program and fetch the result column/field into a 'fixchar' or 'char' type host variable VARCHAR and LVARCHAR type columns and function returns will retain any "real" trailing spaces but CHAR type columns or returns will be space padded to their full defined length (the difference is that the char type host variable will be NULL terminated so you have to allow an extra byte in the length of the variable for the NULL) but if you fetch the data into a 'string' type host variable then the trailing spaces in a CHAR type column or return field will be stripped. For a VARCHAR or LVARCHAR type column or function return field the "real" spaces will not be stripped from any of these types. IB that ODBC behaves as if the char host variables were ESQL/C "string" types. That will mean that, as others have pointed out, you may have to use VARCHAR or LVARCHAR to see the trailing spaces in the host variables. However, other host languages, say Visual Basic or Perl, may treat the incoming data differently and there, most likely, lies your problem. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) 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 Thu, Sep 16, 2010 at 6:27 AM, SPASO LAZAREVIC <spasobn@gmail.com> wrote: > Thanks Art, I figure out, but I need to prove it. > > For backup purpose, beside from returning value from sp, I insert data into > db > table and I got the same result. Data are without trailing blanks. > > In db I create field with varchar and char type. > > Some other system call my sp so I expect answer from them to find what they > receive (with or without trailing blanks). > > I user AGS Server Studio for manipulating sp's and for viewing results from > my > sp. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016362835ceb9440704905fb43f