lvarchar manipulation
Question: in IDS 9.40, lvarchar columns can't be accessed in distributed (cross-server) queries — the poster asked whether this is a documented limitation (it is, per the SQL Reference manual, data types section). Suggestions included casting to char(2048) in the query or wrapping the table in a view that does the cast, but the poster reported the view approach didn't work. His own working solution was a stored function on the remote instance that FOREACHes the rows into a char(2048) variable and RETURNs WITH RESUME, called remotely as dbtest@instanceB:spRemoteExample().
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi all, I'm reviewing if i use lvarchar datatype. But I realized that I can't access fields of this type in distribuited queries. Is there any way to force a distribuited query to read data from this data type? or is it a limit as manual describe? I'm using IDS 9.40 regards
Rafael Padilla wrote: > Hi all, > > I'm reviewing if i use lvarchar datatype. > But I realized that I can't access fields of this type > in distribuited queries. > > Is there any way to force a distribuited query to read data from > this data type? or is it a limit as manual describe? here's an overview of why varchars are not a magical datatype which is superior to plain chars: http://www.geocities.com/ahammau/informix/varchar.html As to your specific question, if the manual describes it as a limitation, then it must be a limitation! Which book and page is it mentioned?
--> > But I realized that I can't access fields of this type --> > in distribuited queries. is it?? i would ask to file a feature req to make this possible. you could try to cast it to char eq select lvarcharcol::char(2048) , someothercols from somedb@somesever:sometable where somecol = someval superboer. "Andrew Hamm" <ahamm@mail.com> wrote in message news:<3873k1F5kg0fkU1@individual.net>... > Rafael Padilla wrote: > > Hi all, > > > > I'm reviewing if i use lvarchar datatype. > > But I realized that I can't access fields of this type > > in distribuited queries. > > > > Is there any way to force a distribuited query to read data from > > this data type? or is it a limit as manual describe? > > here's an overview of why varchars are not a magical datatype which is > superior to plain chars: > > http://www.geocities.com/ahammau/informix/varchar.html > > As to your specific question, if the manual describes it as a limitation, > then it must be a limitation! Which book and page is it mentioned?
Here is my suggestion.
create table x ( c1 serial, c2 lvarchar);
insert into x select 0,tabname from systables;
create view x_view( c1, c2 ) as select c1, c2::char(2048) from x;
select * from dbs@server:x_view;
Hope it helps,
John Miller
Rafael Padilla wrote:
> Hi all,
>
> I'm reviewing if i use lvarchar datatype.
> But I realized that I can't access fields of this type
> in distribuited queries.
>
> Is there any way to force a distribuited query to read data from
> this data type? or is it a limit as manual describe?
>
> I'm using IDS 9.40
>
> regards
"Andrew Hamm" <ahamm@mail.com> wrote in message news:<3873k1F5kg0fkU1@individual.net>... > Rafael Padilla wrote: > > Hi all, > > > > I'm reviewing if i use lvarchar datatype. > > But I realized that I can't access fields of this type > > in distribuited queries. > > > > Is there any way to force a distribuited query to read data from > > this data type? or is it a limit as manual describe? > > here's an overview of why varchars are not a magical datatype which is > superior to plain chars: > > http://www.geocities.com/ahammau/informix/varchar.html > > As to your specific question, if the manual describes it as a limitation, > then it must be a limitation! Which book and page is it mentioned? You can find it on SQL Reference Manual on page 2-27 Data types section regards
John Miller <jmiller@jfmiii.com> wrote in message news:<421F55A3.7020300@jfmiii.com>...
> Here is my suggestion.
>
>
> create table x ( c1 serial, c2 lvarchar);>
> insert into x select 0,tabname from systables;>
> create view x_view( c1, c2 ) as select c1, c2::char(2048) from x;>
>
> select * from dbs@server:x_view;>
>
>
> Hope it helps,
> John Miller
>
>
>
> Rafael Padilla wrote:
> > Hi all,
> >
> > I'm reviewing if i use lvarchar datatype.
> > But I realized that I can't access fields of this type
> > in distribuited queries.
> >
> > Is there any way to force a distribuited query to read data from
> > this data type? or is it a limit as manual describe?
> >
> > I'm using IDS 9.40
> >
> > regards
That didn't work...
I found this solution
In Instance A
I had to make a SP like this:
create table testtable( obs lvarchar );
CREATE FUNCTION spRemoteExample()DEFINE ltext char(2048);
FOREACH
SELECT field
INTO ltext
FROM testtable
RETURN ltext with resume;
END FOREACH;
END FUNCTION
so, in instance B
execute procedure dbtest@intanceB:spRemoteExample();
and this works fine...
Obviously, you can limit FOREACH with a WHERE and sending variables as parameters
regards!!!