FW: LVARCHAR data type
The problem here is inserting into an LVARCHAR from 4GL. From a stored
procedure the insert works OK if the DEFINE for text is LVARCHAR or LIKE
tab2.text. 4GL knows nothing about tyep LVARCHAR. From 4GL we must
pass the text variable to a stored procedure as type LVARCHAR, strip off
the trailing spaces in the procedure and then insert the LVARCHAR type.
Either with the function below from Ravi, or
substr(text,1,length(text)).
Many thanks Ravi.
Regards,
Bill
> -----Original Message-----
> From: rkusenet [SMTP:rkusenet@sympatico.ca]
> Sent: Thursday, January 13, 2005 2:00 PM
> To: Bill Dare
> Subject: Re: LVARCHAR data type
>
> oops.
> I realize that trim function can not run on lvarchar.
> I think you have to write a wrapper routine in SPL. The logic should
> be
> as follows
> create procedure proc2()
> define text lvarchar(100);> let text = "test with trailing spaces ";
> let text = sp_remove_tr_spaces(text) ;
> insert into tab2 values(0,text);> end procedure;
> drop function sp_remove_tr_spaces ;
> create function sp_remove_tr_spaces(p_str lvarchar(100))
> returning lvarchar(100) ;> define ret_str lvarchar(100) ;
> define i integer ;
>
> for i = length(p_str) to 1
> if ( substr(p_str,i,1) <> ' ' ) then
> exit for ;
> end if ;
> end for ;
>
> let ret_str = substr(p_str,1,i);
> return ret_str ;
> end function ;
>
> I tested it with dbaccess and trailing blanks and it works fine.
> test it.
>
> ----- Original Message -----
> From: "Bill Dare" <dareb@jevic.com>
> To: "rkusenet" <rkusenet@sympatico.ca>
> Sent: Thursday, January 13, 2005 1:26 PM
> Subject: RE: LVARCHAR data type
>
>
> No go. If I make text an lvarchar I get
>
> 880: Trim character and trim source must be string types.
> Error in line 1> Near character position 1
>
> when I execute the procedure.
>
> If I make text a char it is not trimming. Still inserts 100 spaces.
>
> drop procedure proc2;
> create procedure proc2()> define text char(100);
> let text = "test";
> let text = trim(text);
> insert into tab2 values(0,text);> end procedure;
>
> This procedure stores this:
>
> 1|test
> |4|
>
> Thanks for the excellent suggestions.
>
> Bill
>
>
> > -----Original Message-----
> > From: rkusenet [SMTP:rkusenet@sympatico.ca]
> > Sent: Thursday, January 13, 2005 1:08 PM
> > To: Bill Dare
> > Subject: Re: LVARCHAR data type
> >
> > OK this is what I understand.
> >
> > 4GL calls the SP with the values.
> > The SP inserts the values into the table.
> > Because 4GL pads trailing blanks, the SP inserts it into the table
> as it is.
> >
> > I don't have access to 4GL, but you can try this.
> >
> > In the stored procedure add this line
> > let text = "the text supplied by 4gl with trailign spaces "
> ;
> > let text = trim(text) ;
> >
> > then insert it.
> >
> > ----- Original Message -----
> > From: "Bill Dare" <dareb@jevic.com>
> > To: "rkusenet" <rkusenet@sympatico.ca>
> > Sent: Thursday, January 13, 2005 12:57 PM
> > Subject: RE: LVARCHAR data type
> >
> >
> > Thanks. That works OK for the SPL but not 4GL. 4GL doesn't know
> what a lvarchar is. I
> > can define the variable as LIKE tab2.text but it still inserts 100
> characters space
> > padded. Tried inserting from the 4GL executing a stored procedure
> and it still inserts
> > all 100 characters.
> >
> > Bill
> >
> >
> > > -----Original Message-----
> > > From: rkusenet [SMTP:rkusenet@sympatico.ca]
> > > Sent: Thursday, January 13, 2005 12:56 PM
> > > To: Bill Dare
> > > Subject: Re: LVARCHAR data type
> > >
> > > The problem is in this
> > >
> > > define text char(100);
> > >
> > > change it to
> > > define text lvarchar(100) ;
> > >
> > > and it will be OK.
> > >
> > > I tested this on 9.21.
> > >
> > > Ravi.
> > >
> > >
> > >
> > >
> >
> >
>
>
sending to informix-list