Copy table ** from problem for Ingres 9.2

[Ingres Forums]

Hi all!

Ingres change the orginal value spaces ' ' (The value only has spaces)
is changed to '' in the result.

\SQL

SET AUTOCOMMIT OFF
\p\g

SET NOJOURNALING
\p\g

SET SESSION AUTHORIZATION CURRENT_USER
\p\g

COPY TABLE "testTable" (
id=char(0)comma WITH NULL ('[NULL]') ,
chartest=char(0)comma WITH NULL ('[NULL]') ,
name=text(0)nl WITH NULL ('[NULL]'))
FROM
'c:/ingres_bulk.csv'
WITH
ROLLBACK=ENABLED,
ON_ERROR=CONTINUE;

COMMIT
\p\g

ROLLBACK
\p\g


ingres_bulk.csv file content is like this :

1,(a space), test1
2,a, test2
3,b,(a space)


DBtype is :　id ---->Integer , chartest---> char,　name---->varchar

the data in "chartest" column space is discarded,but in "name" column is
saved.

I have try the vcahr(0),char(0) ,and text(0),　the space are all
discarded.

Please help me , thank you !


--
roger666888
------------------------------------------------------------------------
roger666888's Profile: http://community.actian.com/forum/member.php?userid=115425
View this thread: http://community.actian.com/forum/showthread.php?t=14485

[Karl Schendel]

On Jun 7, 2012, at 4:53 AM, roger666888 wrote:

> Hi all!
>
> Ingres change the orginal value spaces ' ' (The value only has spaces)
> is changed to '' in the result.
>

...

> ingres_bulk.csv file content is like this :
>
> 1,(a space), test1
> 2,a, test2
> 3,b,(a space)
>
>
> DBtype is :　id ---->Integer , chartest---> char,　name---->varchar
>
> the data in "chartest" column space is discarded,but in "name" column is
> saved.

That's more or less the definition of char columns. They are padded to
the declared length with spaces, so upon retrieval all trailing spaces are
trimmed off. If you want to record trailing spaces you need to use
the varchar datatype for the column.

Karl

[Ingres Forums]

Hi Karl

It's means that if dbtype is "char" ,then the spaces will always be
trim not matter char(0) or test(0) I used in my CREATE TABLE SQL. Is it
? If it is , then I can only use varchar type ,no other solution ?

thanks and best regards

jjzhou

[Karl Schendel]

On Jun 12, 2012, at 11:00 AM, Ingres Forums wrote:

>
> Hi Karl
>
> It's means that if dbtype is "char" ,then the spaces will always be
> trim not matter char(0) or test(0) I used in my CREATE TABLE SQL. Is it
> ? If it is , then I can only use varchar type ,no other solution ?

Whether trailing spaces are trimmed depends on the specific operator.
For instance, equality testing always ignores trailing spaces, even for
varchar, while the LIKE operator treats trailing spaces as significant.
If you want 'a ' to be != 'a', you generally have to compared lengths
as well, or concatenate with a trailing marker.

The issue for char(n) (and cn) is that it's stored as a fixed length value,
padded with spaces to the declared length. There's no way to tell
which of those spaces were in the original value and which were padding.
varchar and text values are stored with a length counter, and are
not padded with trailing spaces.

Karl

[Ingres Forums]

Hi Karl，

Thanks again for your replying !

Now I have a sql like this :
"COPY TABLE "test_char" (

id=char(0)comma WITH NULL ('[NULL]') ,

test_char=char(0)comma WITH NULL ('[NULL]') ,
test_varchar=text(0)nl WITH NULL ('[NULL]') )
FROM
'E:/tuj/tIngresOutputBulkExec_Bug20194/ingres_bulk.csv'

WITH
ROLLBACK=ENABLED,
ON_ERROR=CONTINUE;

COMMIT
\p\g"

id ---->Integer , chartest---> char,　name---->varchar

It works well , but if I changed like this :
"COPY TABLE "test_char" (

id=char(0)comma WITH NULL ('[NULL]') ,

test_char=char(0)comma WITH NULL ('[NULL]') ,
test_varchar=varchar(0)nl )
FROM
'E:/tuj/tIngresOutputBulkExec_Bug20194/ingres_bulk.csv'

WITH
ROLLBACK=ENABLED,
ON_ERROR=CONTINUE;

COMMIT
\p\g"

when I executing the SQL , I got this error : "bad varchar length
specifier found while filling domain 'test_varchar' " for row 1.
Error processing row 2. Cannot conver column 'id' to tmple format,
....................

I looked up the document ,and found that :

text(0) : Read as variable-length character string terminated by the
specified delimiter. If a delimiter is not specified, the first comma,
tab, or newline encountered ends the value.

varchar(0): Read as a variable-length string, "preceded by a
5-character", right-justified length specifier. If a delimiter is
specified, additional input is discarded until the delimiter is found.

So I want to know what's the difference between the varchar(0) and
text(0) ,and could you please explain the "preceded by a 5-character"
detail for me ?

Thanks and best regards,

roger666888

[Paul White]

Hi Roger,

The 5 character prefix specifies the length of the varchar
You can best see the difference if you use a copy out.

create table test_char (
id int,
test_char char(10),
test_varchar varchar(10) );
\p\g

insert into test_char values (NULL,NULL,NULL);
insert into test_char values (0,'','');
insert into test_char values (1,'abc','zyx');
insert into test_char values (2,'abcd','zyxw');
insert into test_char values (3,'abcd ','zyxw ');
\p\g

COPY TABLE test_char (
id=char(0)comma WITH NULL ('[NULL]') ,
test_char=char(0)comma WITH NULL ('[NULL]') ,
test_varchar=text(0)nl WITH NULL ('[NULL]') )

INTO 'test_char1.csv';
\p\g

COPY TABLE test_char (
id=char(0)comma WITH NULL ('[NULL]') ,
test_char=char(0)comma WITH NULL ('[NULL]') ,

test_varchar=varchar(0)nl WITH NULL ('[NULL]') )
INTO 'test_char2.csv';
\p\g

drop table test_char;
\p\g

==================

e:\paul>sql mydb < test_char.sql
...
e:\paul>type test_char1.csv
[NULL] ,[NULL] ,[NULL]
0, ,
1,abc ,zyx
2,abcd ,zyxw
3,abcd ,zyxw

e:\paul>type test_char2.csv
[NULL] ,[NULL] , 6[NULL]
0, , 0
1,abc , 3zyx
2,abcd , 4zyxw
3,abcd , 5zyxw

e:\paul>





-----Original Message-----
From: info-ingr...@kettleriverconsulting.com
[mailto:info-ingr...@kettleriverconsulting.com] On Behalf Of Ingres
Forums
Sent: Wednesday, 13 June 2012 3:49 PM
To: info-...@kettleriverconsulting.com
Subject: Re: [Info-Ingres] Copy table ** from problem for Ingres 9.2


Hi Karlo<

Thanks again for your replying !

Now I have a sql like this :
"COPY TABLE "test_char" (
id=char(0)comma WITH NULL ('[NULL]') ,
test_char=char(0)comma WITH NULL ('[NULL]') ,
test_varchar=text(0)nl WITH NULL ('[NULL]') )
FROM

id'E:/tuj/tIngresOutputBulkExec_Bug20194/ingres_bulk.csv'

WITH
ROLLBACK=ENABLED,
ON_ERROR=CONTINUE;

COMMIT
\p\g"

id ---->Integer , chartest---> char,cname---->varchar

It works well , but if I changed like this :
"COPY TABLE "test_char" (
id=char(0)comma WITH NULL ('[NULL]') ,
test_char=char(0)comma WITH NULL ('[NULL]') ,
test_varchar=varchar(0)nl )
FROM
'E:/tuj/tIngresOutputBulkExec_Bug20194/ingres_bulk.csv'
WITH
ROLLBACK=ENABLED,
ON_ERROR=CONTINUE;

COMMIT
\p\g"

when I executing the SQL , I got this error : "bad varchar length
specifier found while filling domain 'test_varchar' " for row 1.
Error processing row 2. Cannot conver column 'id' to tmple format,
...................

[Ingres Forums]

Hello. I have a question similar in nature to this. It relates to
copying in from a file. What I can't seem to get a solution around
importing variable length text containing the <CR><LF> characters. That
is, the data exported from another database contains a text field type
with values including <newline>. Your assist is appreciated. I am
reading about forming the formatting of in/out file on copy, but can't
seem to find good examples that include any <newline> file input
delimiting. Thanks...


--
adamcox27
------------------------------------------------------------------------
adamcox27's Profile: http://community.actian.com/forum/member.php?userid=117353

[Ingres Forums]

I have found that there is a limited byte count at 25329 bytes. When
using above copy statement, 25330 bytes between last comma and the
newline causes truncation.

--
adamcox27
------------------------------------------------------------------------
adamcox27's Profile: http://community.actian.com/forum/member.php?userid=117353

[Ingres Forums]

.. will try long varchar(0)...

[Karl Schendel]

On Mar 20, 2013, at 2:06 PM, Ingres Forums wrote:

>
> Hello. I have a question similar in nature to this. It relates to
> copying in from a file. What I can't seem to get a solution around
> importing variable length text containing the <CR><LF> characters. That
> is, the data exported from another database contains a text field type
> with values including <newline>.

COPY doesn't treat newline specially, unless the format delimiter is
nl or (the last) csv. I gather that you have input like:
this is all
one big column value, and not two.

So, what DOES mark the end of the text in your input file?
If there is some particular value-ending character you can use it as
the delimiter. If the value is (double)quoted you can use csv, assuming
that you're using Ingres 10 (but apparently you aren't). A last resort
would be to preprocess the input with some utility such as awk or perl
or whatever, figure out the length of the value, and prepend that length
as a 5-digit number (leading space padded) to the value. Then, you can use
varchar(0) format to read the value.

As for your other post about a 25330 character limit, that makes no
sense to me. The varchar limit ought to be 32000. I can't think of
any reason for anything bad to happen at exactly 25330 characters,
and either something is very strange with your test setup, or you
have uncovered some sort of bug.

Karl