urgent | | is inserted in text field with /
A user on IDS 9.4 loaded text containing pipe characters (||) into a TEXT column and saw extra backslashes before each pipe when viewing the data; changing the LOAD delimiter from | to # didn't help. Gaurav Saxena and Art Kagel explained there is nothing wrong: the backslashes are not stored in the column, they are escape characters added by dbaccess/unload-style output so the data can be reloaded unambiguously. Verifying with an ESQL/C, ODBC or JDBC program shows the clean value. It was also suggested the escaping of | when the delimiter isn't | may be worth reporting as a defect.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
The goal is to insert a string containing | | in a column of
text type
when we try to insert a query containing pipe ||in a text field , the result
of insertion shows that supplementary junk slash are added to pipe as
indicated below for IDS version 9.4
1°/ sample of the begenning of the query that we want to insert in a text field
select trim(substr(lpad(cpp_id,10,"
"),1,5)||"01"||substr(lpad(cpp_id,10,"0"),6,5))1|1|actor|pps2_6_5f||N|
2°/ result of the insertion
select trim(substr(lpad(cpp_id,10,"
"),1,5)\\\\|\\\\|"01"\\\\|\\\\|substr(lpad(cpp_id,10,"0"),6,5)),
"12","","",decode(cpp_etat,3,72,68) ,(((( ca_dt_creat - 2 units hour -
DBINFO("utc_to_datetime",946684800) )::INTERVAL SECOND(9) TO SECOND \\\\|\\\\|"")::int + 946684800) *1000) ::int8,(((( NVL(cpp_dt_modif,CURRENT) - 2
units hour - DBINFO("utc_to_datetime",946684800) )::INTERVAL SECOND(9)
TO SECOND \\\\|\\\\| "")::int + 946684800) *1000) ::int8 from cust_access,
cust_ppaid_v2 where ca_id = cpp_id and cpp_numtel <> "default" and
cpp_etat <> 0|
3°/ the tablE structure used is indicated below and the field whre the query
tex t is inserted is sqlselect
{ TABLE "informix".query row size = 131 number of columns = 7 index size
= 12 }
create table "informix".query
(
formid serial not null ,
projectid integer,
name char(30),
database char(30),
arrayname char(30),
lockflag char(1),
sqlselect text
) extent size 16 next size 16 lock mode page;
revoke all on "informix".query from "public";
4°/ question how could we insert the query text with || without suplementary /
?
Hi,
The question is how do you try to insert the value ? With a dbaccess
script ? Or a program ? Which programming language is used and how do
you fill the insert statement ?
Marcus
-----Original Message-----
From: MEHDI BELAJOUZA [mailto:belajouza@hotmail.com]
Sent: Saturday, December 29, 2007 10:31 PM
To: ids@iiug.org
Subject: urgent | | is inserted in text field with / [10811]
The goal is to insert a string containing | | in a column of text type
when we try to insert a query containing pipe ||in a text field , the
result of insertion shows that supplementary junk slash are added to
pipe as indicated below for IDS version 9.4
1/ sample of the begenning of the query that we want to insert in a text
field select trim(substr(lpad(cpp_id,10,"
"),1,5)||"01"||substr(lpad(cpp_id,10,"0"),6,5))1|1|actor|pps2_6_5f||N|
2/ result of the insertion
select trim(substr(lpad(cpp_id,10,"
"),1,5)\\\\|\\\\|"01"\\\\|\\\\|substr(lpad(cpp_id,10,"0"),6,5)),
"12","","",decode(cpp_etat,3,72,68) ,(((( ca_dt_creat - 2 units hour -
DBINFO("utc_to_datetime",946684800) )::INTERVAL SECOND(9) TO SECOND \\\\|\\\\|"")::int + 946684800) *1000) ::int8,(((( NVL(cpp_dt_modif,CURRENT) - 2
units hour - DBINFO("utc_to_datetime",946684800) )::INTERVAL SECOND(9)
TO SECOND \\\\|\\\\| "")::int + 946684800) *1000) ::int8 from cust_access,
cust_ppaid_v2 where ca_id = cpp_id and cpp_numtel <> "default" and
cpp_etat <> 0|
3/ the tablE structure used is indicated below and the field whre the
query tex t is inserted is sqlselect
{ TABLE "informix".query row size = 131 number of columns = 7 index size
= 12 } create table "informix".query (
formid serial not null ,
projectid integer,
name char(30),
database char(30),
arrayname char(30),
lockflag char(1),
sqlselect text
) extent size 16 next size 16 lock mode page; revoke all on
"informix".query from "public";
4/ question how could we insert the query text with || without
suplementary / ?
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
The insert is made with load of a file containing the indicated previous query text that contains the '|', stating that the DELIMITER is something other than the pipe '|'.in first step, load was made With the pipe as delimiter and supplementary \\\\ are found in the TEXT field. and in a second step a load test made with a delimiter as # and this test shown supplementary \\\\ in the TEXT field . thank you in advance for your help
Your data "|" will be inserted just fine.
I would like to know that how are you retrieving the data to find out there
are these extra '\\\\'. Note that the unload from dbaccess and dbexport will
do some formatting for example:
will add "\\\\ " while unloading the data like "" and the case like you have
mentioned (have not checked it but looks like it does for "|" too).
even if the data is not having "\\\\" in database table.
This is done to ensure that when you load from same unloaded file, these
utilities exactly know that what is to be sent to the server to create "as
is image" of the data. In the case, you mentioned, this formatting might be
required for differentiating between data "|" and delimiter "|".
There are other ways to retrieve the data, for example, If you use plain
ESQL/C program for selecting the data, you will not see these '\\\\' unless
you want it to.
Thanks and Regards,
Gaurav
"Marcus Haarmann"
<marcus.haarmann@
midoco.de> To
Sent by: ids@iiug.org
ids-bounces@iiug. cc
org
Subject
RE: urgent is inserted in text
30/12/2007 17:22 field with / [10812]
Please respond to
ids@iiug.org
Hi,
The question is how do you try to insert the value ? With a dbaccess
script ? Or a program ? Which programming language is used and how do
you fill the insert statement ?
Marcus
-----Original Message-----
From: MEHDI BELAJOUZA [mailto:belajouza@hotmail.com]
Sent: Saturday, December 29, 2007 10:31 PM
To: ids@iiug.org
Subject: urgent | | is inserted in text field with / [10811]
The goal is to insert a string containing | | in a column of text type
when we try to insert a query containing pipe ||in a text field , the
result of insertion shows that supplementary junk slash are added to
pipe as indicated below for IDS version 9.4
1/ sample of the begenning of the query that we want to insert in a text
field select trim(substr(lpad(cpp_id,10,"
"),1,5)||"01"||substr(lpad(cpp_id,10,"0"),6,5))1|1|actor|pps2_6_5f||N|
2/ result of the insertion
select trim(substr(lpad(cpp_id,10,"
"),1,5)\\\\|\\\\|"01"\\\\|\\\\|substr(lpad(cpp_id,10,"0"),6,5)),
"12","","",decode(cpp_etat,3,72,68) ,(((( ca_dt_creat - 2 units hour -
DBINFO("utc_to_datetime",946684800) )::INTERVAL SECOND(9) TO SECOND \\\\|\\\\|"")::int + 946684800) *1000) ::int8,(((( NVL(cpp_dt_modif,CURRENT) - 2
units hour - DBINFO("utc_to_datetime",946684800) )::INTERVAL SECOND(9)
TO SECOND \\\\|\\\\| "")::int + 946684800) *1000) ::int8 from cust_access,
cust_ppaid_v2 where ca_id = cpp_id and cpp_numtel <> "default" and
cpp_etat <> 0|
3/ the tablE structure used is indicated below and the field whre the
query tex t is inserted is sqlselect
{ TABLE "informix".query row size = 131 number of columns = 7 index size
= 12 } create table "informix".query (
formid serial not null ,
projectid integer,
name char(30),
database char(30),
arrayname char(30),
lockflag char(1),
sqlselect text
) extent size 16 next size 16 lock mode page; revoke all on
"informix".query from "public";
4/ question how could we insert the query text with || without
suplementary / ?
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
See you at the IIUG Informix 2008 Conference
The Power Conference for Informix Professionals
April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
http://www.iiug.org/conf
Registration Now Open!!
so how to eliminate these supplementary \\\\ when inserting through a load process. As indicated in my last post, even with a delimiter different from | as #, \\\\ is found in the text field.
No need, if you are going to load from the file, '\\\\' will not get inserted
unless you give "\\\\\\\\". Yes, you can submit a defect that when delimiter is
different than "|", utilities should not be unloading the same with '\\\\'. I
think there can possibly be more defects in this scope:
It is good idea to check following scenarios:
Scenario 1:
Load "|" with insert statement.
unload to a file. - a.unlchange the delimiter in env as well as file.
delete all rows.
load from the file.unload again. - b.unl
Scenario2:
set delimiter to #
Load "|" with insert statement.
unload to a file. - c.unldelete all rows.
load from file.
unload to a file. - d.unl
There can be more tests.
a.unl and b.unl should be same.
c.unl should not have '\\\\' preceding "|" -> I think you will find a defect
here.
c.unl and d.unl should be same.
even if you find the defect in Test3 and Test1 is ok, you can safely
insert. But yes, report a defect.
Also, one should try inserting '#' when delimiter is set to '#'. Is it
prefixed by '\\\\', if yes, things are fine.
Thanks and Regards,
Gaurav
"MEHDI BELAJOUZA"
<belajouza@hotmai
l.com> To
Sent by: ids@iiug.org
ids-bounces@iiug. cc
org
Subject
Re: RE: urgent is inserted in text
31/12/2007 15:56 field with / [10820]
Please respond to
ids@iiug.org
so how to eliminate these supplementary \\\\ when inserting through a load
process. As indicated in my last post, even with a delimiter different from
|
as #, \\\\ is found in the text field.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
See you at the IIUG Informix 2008 Conference
The Power Conference for Informix Professionals
April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
http://www.iiug.org/conf
Registration Now Open!!
MEHDI BELAJOUZA wrote:
> so how to eliminate these supplementary \\\\ when inserting through a load
> process. As indicated in my last post, even with a delimiter different from |
> as #, \\\\ is found in the text field.
>
>
Nothing to eliminate. What was said is that the backslashes (\\\\) are NOT
in the text field in the table at all! It's just that when you retrieve
the data and display it, or unload it to a file, the display code in the
tool you are using (presumably dbaccess) is adding the backslashes to
the display for clarity. To make yourself comfortable write a little
ESQL/C, ODBC, or JDBC program to fetch the data and display it yourself
to see what's REALLY in there.
Art S. Kagel