Обсуждение: RE: [INTERFACES] '\' and varchar data type problem w/ MS Access a nd ODBC driver

I have also found this to be true. I initially thought it was a problem
in the ODBC driver, but after investigating a little further, found that
it actually was the backend. You can test this via psql.
The existance of even a single '\' character will cause the data to be
corrupted. It seems to take the next character and converts it to binary
data of some sort. I have not found a workaround for this eg. '\\' does
not convert to a single '\'

Regards,

Paul Lisewski


> -----Original Message-----
> From:    Bryan Brunton [SMTP:bryan@flesherfab.com]
> Sent:    Thursday, 13 August 1998 9:04
> To:    'pgsql-interfaces@postgresql.org'; 'Byron Nikolaidis'
> Subject:    [INTERFACES] '\' and varchar data type problem w/ MS
> Access and ODBC driver
>
>
> The backslash character '\' seems to cause problems when using the
> varchar
> data type.  MS Access will refuse to update a varchar field if 3
> backslashes
> have been inserted.
>
>
>
>

> You can test this via psql.
> The existance of even a single '\' character will cause the data to be
> corrupted. It seems to take the next character and converts it to
> binary data of some sort. I have not found a workaround for this eg.
> '\\' does not convert to a single '\'

tgl=> create table tx (v varchar(20));
CREATE
tgl=> insert into tx values ('123\'456');
tgl=> insert into tx values ('123\\456');
tgl=> select * from tx;
v
--------
123'456
123\\456
(2 rows)

One "problem" with the backend is it escapes characters on both input
_and_ output, which has always struck me as at least partially defeating
the usefulness of escaping input.

As you notice in the above example, it does not escape all characters on
output, but only a few. But I don't know where you see binary data!?
What version are you running?

                       - Tom

"Thomas G. Lockhart" <lockhart@alumni.caltech.edu> writes:
> One "problem" with the backend is it escapes characters on both input
> _and_ output, which has always struck me as at least partially defeating
> the usefulness of escaping input.

I agree, that is a bug.  Output data should not be escaped.

COPY IN/OUT data needs to be handled slightly differently from data seen
in SQL commands and SELECT results.  In particular, COPY likes to
represent null values as \N, which can be confused with a valid string
value if you don't escape backslash.

It seems to me the rules should be:

* String data in SQL commands: quotes and backslashes must be escaped
with a backslash.  This is pretty much forced by SQL string constant
syntax.  (BTW, does the backend accept C-like syntax for control
characters in strings, eg, "\n" for newline or "\0" for a null
character?  Should it?)

* Tuples returned by SELECT: no escaping, ever.  Null values are
signaled separately so there's no need for \N to mean something special.
(If you have null characters in a string, you may have some difficulty
using C string functions on it, but that's your problem.  The FE/BE
protocol for tuple data doesn't care, nor does libpq.)

* COPY IN/OUT: backslashes have to be escaped with a backslash (ie,
doubled) so that \N is unambiguously a NULL column.  Also, it should
be possible to backslash a TAB to make it a data character rather than
a column separator.  (Or perhaps better, support C-like \t, \n, etc.)

I have not looked at the code to see how close this is to reality,
but I think that's what we need to be doing.

            regards, tom lane

"Thomas G. Lockhart" <lockhart@alumni.caltech.edu> writes:
> One "problem" with the backend is it escapes characters on both input
> _and_ output, which has always struck me as at least partially defeating
> the usefulness of escaping input.

Actually, further experimentation shows that the backend does no such
thing, at least not in tuples returned by SELECT.

psql --- specifically, PQprint() in libpq --- is contributing the
"helpful" doubling of backslashes on printout.  What comes back from
the backend has the number of backslashes it should.

It looks like libpgtcl is contributing some weird backslash response
of its own, which means that things act different but just as broken
if you try to investigate the behavior from Tcl.

Right now I think the backend is behaving properly for SQL command input
and tuple data output.  The bizarreness is in the front ends.

COPY IN/OUT might have some glitches, haven't tried that yet.

            regards, tom lane

>>>>> "TL" == Tom Lane <tgl@sss.pgh.pa.us> writes:

  TL> COPY IN/OUT data needs to be handled slightly differently from
  TL> data seen in SQL commands and SELECT results. In particular,
  TL> COPY likes to represent null values as \N, which can be confused
  TL> with a valid string value if you don't escape backslash.

I am struggling with this right at the moment (I'm new to databases,
I'm struggling with everything). Is it possible to use functions in
COPY input? Specifically, I am trying to do this

   COPY attributes FROM 'home/ecm/prog/database/attributes.data';

where the file contains entries like (with tabs)

   nextval('attribute_id_sequence')        Control and Monitoring  \N

I get an ERROR: pg_atoi: error in "nextval(attribute_id_sequence)":
can't parse "nextval(attribute_id_sequence)". Should I be using
auto-generated numbers and setting the sequence to an initial value
instead?

--
Eric Marsden
emarsden @ mail.dotcom.fr
It's elephants all the way down

Eric Marsden <emarsden@mail.dotcom.fr> writes:
> Is it possible to use functions in
> COPY input? Specifically, I am trying to do this
>    COPY attributes FROM 'home/ecm/prog/database/attributes.data';
> where the file contains entries like (with tabs)
>    nextval('attribute_id_sequence')        Control and Monitoring  \N

Nope, won't work.  COPY handles literal data only --- if it were smart
enough to do SQL expressions then we wouldn't have to have this weird \N
kluge for null values.  (I think there are a few tiny exceptions, like
you could put "now" as the value for a datetime field and the expected
thing would happen during COPY IN.  That's because the literal-string-
to-data-value parser for type datetime implements that conversion all
by itself.  But nextval() is definitely an SQL expression.)

You should use a series of INSERT commands if you need to do SQL
calculations while inserting data.  For instance, I have an app that
uses nextval() to assign ID numbers like yours, and it issues tons
of commands like this:

INSERT INTO FEHistory_3 (accountID, instrumentID, orderType,
numContracts, orderTime, simStatus, realStatus, sequenceNo, orderPrice,
orderDivisor, ifDonePrice) VALUES(13, 120, 'S', 1, '1998-07-16 18:00:00 GMT',
'A', '-', nextval('FEHistory_3_Seq'), 10603, 100, 10574)

            regards, tom lane