pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feed'\' and varchar data type problem w/ MS Access and ODBC driver
7+ messages / 6 participants
[nested] [flat]
* '\' and varchar data type problem w/ MS Access and ODBC driver
@ 1998-08-12 23:03 Bryan Brunton <bryan@flesherfab.com>
0 siblings, 1 reply; 7+ messages in thread
From: Bryan Brunton @ 1998-08-12 23:03 UTC (permalink / raw)
To: pgsql-interfaces; 'Byron Nikolaidis' <byronn@insightdist.com>
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: [INTERFACES] '\' and varchar data type problem w/ MS Access a nd ODBC driver
@ 1998-08-12 23:54 Paul Lisewski <paull@techone.com.au>
0 siblings, 1 reply; 7+ messages in thread
From: Paul Lisewski @ 1998-08-12 23:54 UTC (permalink / raw)
To: Bryan Brunton <bryan@flesherfab.com>; pgsql-interfaces; 'Byron Nikolaidis' <byronn@insightdist.com>
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.
>
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [INTERFACES] '\' and varchar data type problem w/ MS Access and ODBC driver
@ 1998-08-13 02:16 Hiroki Kataoka <kataoka@interwiz.koganei.tokyo.jp>
parent: Bryan Brunton <bryan@flesherfab.com>
0 siblings, 0 replies; 7+ messages in thread
From: kataoka@interwiz.koganei.tokyo.jp @ 1998-08-13 02:16 UTC (permalink / raw)
To: pgsql-interfaces
Hello all,
From: Bryan Brunton <bryan@flesherfab.com>
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.
The following is a patch of correcting this '\' problem for PostgreSQL
ODBC Driver. I have already applied this patch, and no problem happened.
Hiroki Kataoka <kataoka@interwiz.koganei.tokyo.jp>
######## cut here ########
inline patch 1: convert.c (+2/-0 lines) download inline-1.patch
*** convert.c.orig Thu Aug 13 10:58:25 1998
--- convert.c Thu Aug 13 10:59:43 1998
***************
*** 964,969 ****
--- 964,971 ----
continue;
if (si[i] == '\'')
p[out++] = '\\';
+ else if (si[i] == '\\')
+ p[out++] = '\\';
p[out++] = si[i];
}
* Re: [INTERFACES] '\' and varchar data type problem w/ MS Access a nd ODBC driver
@ 1998-08-13 02:38 Thomas G. Lockhart <lockhart@alumni.caltech.edu>
parent: Paul Lisewski <paull@techone.com.au>
0 siblings, 2 replies; 7+ messages in thread
From: Thomas G. Lockhart @ 1998-08-13 02:38 UTC (permalink / raw)
To: Paul Lisewski <paull@techone.com.au>; +Cc: Bryan Brunton <bryan@flesherfab.com>; pgsql-interfaces; 'Byron Nikolaidis' <byronn@insightdist.com>
> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [INTERFACES] '\' and varchar data type problem w/ MS Access and ODBC driver
@ 1998-08-13 11:46 James Oden <joden@lee.k12.nc.us>
0 siblings, 0 replies; 7+ messages in thread
From: James Oden @ 1998-08-13 11:46 UTC (permalink / raw)
To: Bryan Brunton <bryan@flesherfab.com>; +Cc: pgsql-interfaces
>
>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.
If you want litterally three backslashes you may need to massage the data to
add an extra one before each one, so your three back slashes become six back
slashes. I am just guessing on this, but in postgreSQL's implementation of
SQL (and this may be just a SQL thing) the \ is used as an escape sequence,
much like it is in C. Someone else on the list earlier had a problem
recreating his databases from a dump because it did have \'s in it....james
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [INTERFACES] '\' and varchar data type problem w/ MS Access a nd ODBC driver
@ 1998-08-13 15:14 Tom Lane <tgl@sss.pgh.pa.us>
parent: Thomas G. Lockhart <lockhart@alumni.caltech.edu>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 1998-08-13 15:14 UTC (permalink / raw)
To: Thomas G. Lockhart <lockhart@alumni.caltech.edu>; +Cc: pgsql-interfaces
"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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [INTERFACES] '\' and varchar data type problem w/ MS Access a nd ODBC driver
@ 1998-08-13 16:08 Tom Lane <tgl@sss.pgh.pa.us>
parent: Thomas G. Lockhart <lockhart@alumni.caltech.edu>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 1998-08-13 16:08 UTC (permalink / raw)
To: Thomas G. Lockhart <lockhart@alumni.caltech.edu>; +Cc: pgsql-interfaces
"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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~1998-08-13 16:08 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-08-12 23:03 '\' and varchar data type problem w/ MS Access and ODBC driver Bryan Brunton <bryan@flesherfab.com>
1998-08-13 02:16 ` Hiroki Kataoka <kataoka@interwiz.koganei.tokyo.jp>
1998-08-12 23:54 RE: [INTERFACES] '\' and varchar data type problem w/ MS Access a nd ODBC driver Paul Lisewski <paull@techone.com.au>
1998-08-13 02:38 ` Re: [INTERFACES] '\' and varchar data type problem w/ MS Access a nd ODBC driver Thomas G. Lockhart <lockhart@alumni.caltech.edu>
1998-08-13 15:14 ` Re: [INTERFACES] '\' and varchar data type problem w/ MS Access a nd ODBC driver Tom Lane <tgl@sss.pgh.pa.us>
1998-08-13 16:08 ` Re: [INTERFACES] '\' and varchar data type problem w/ MS Access a nd ODBC driver Tom Lane <tgl@sss.pgh.pa.us>
1998-08-13 11:46 Re: [INTERFACES] '\' and varchar data type problem w/ MS Access and ODBC driver James Oden <joden@lee.k12.nc.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox