Обсуждение: PostgreSQL+ Beta bug?
Dear Authors of PostgreSQL ODBC driver,
I am trying to use PostgreSQL+ Beta driver on Windows 2000.
It works very well with one exception. When I try to
insert a char value including 0 then the remaing characters
are lost. And example:
simple table: create table t (fc varchar(5))
The program code (runable in MS VC++ 6.0):
// connect and allocate a statement handle hstmt
HSTMT hstmt;
RETCODE rc;
char fc[5];
SQLINTEGER fcl;
rc = SQLPrepare(hstmt, "insert into t values(?)", SQL_NTS);
rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_VARCHAR, 5, 0, fc, sizeof(fc), &fcl);
fc[0]=1;
fc[1]=0;
fc[2]='a';
fcl=3; /* string length */
rc = SQLExecute(hstmt);
// disconnect
-----
After this please check in psql:
select ascii(substring(fc,1,1)) from t; - 1, OK
select ascii(substring(fc,2,1)) from t; - 0, OK
select ascii(substring(fc,3,1)) from t; - 0 - lost value 'a'
Please let me know if this is a known bug and what is the possibility
of fixing this.
Best regards,
Bozena Potempa
OTC
Poland
Boソena Potempa wrote:
>
> Dear Authors of PostgreSQL ODBC driver,
>
> I am trying to use PostgreSQL+ Beta driver on Windows 2000.
> It works very well with one exception. When I try to
> insert a char value including 0 then the remaing characters
> are lost. And example:
>
> simple table: create table t (fc varchar(5))
>
> The program code (runable in MS VC++ 6.0):
> // connect and allocate a statement handle hstmt
> HSTMT hstmt;
> RETCODE rc;
> char fc[5];
> SQLINTEGER fcl;
>
> rc = SQLPrepare(hstmt, "insert into t values(?)", SQL_NTS);
> rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
> SQL_VARCHAR, 5, 0, fc, sizeof(fc), &fcl);
> fc[0]=1;
> fc[1]=0;
> fc[2]='a';
> fcl=3; /* string length */
> rc = SQLExecute(hstmt);
> // disconnect
> -----
>
> After this please check in psql:
> select ascii(substring(fc,1,1)) from t; - 1, OK
> select ascii(substring(fc,2,1)) from t; - 0, OK
> select ascii(substring(fc,3,1)) from t; - 0 - lost value 'a'
>
> Please let me know if this is a known bug and what is the possibility
> of fixing this.
As long as you are using text(char or varchar) type, you can't
store strings other than null terminated ones.
Please use BYTEA type instead and bind the parameter using
SQL_C_BINARY and SQL_VARBINARY.
regards,
Hiroshi Inoue
http://w2422.nsk.ne.jp/~inoue/
> -----Original Message----- > From: Bo?ena Potempa [mailto:Bozena.Potempa@otc.pl] > > >From: Hiroshi Inoue [mailto:Inoue@tpf.co.jp] > >Sent: Saturday, November 16, 2002 12:38 AM > > [..] > >As long as you are using text(char or varchar) type, you can't > >store strings other than null terminated ones. > >Please use BYTEA type instead and bind the parameter using > >SQL_C_BINARY and SQL_VARBINARY. > > But that's true only in ODBC, so I think it _is_ a bug > in ODBC driver. It is perfectly possible to store 0 in > varchar or char fields. Please try from psql: > > INSERT INTO t values (chr(0)||chr(1)||chr(0)); > and then check: > select ascii(substring(fc,1,1)) from t; - 0 > select ascii(substring(fc,2,1)) from t; - 1 > select ascii(substring(fc,3,1)) from t; - 0 Which version of PostgreSQL server are you connecting ? Here I see all 0 i.e. select ascii(substring(fc,1,1)) from t; - 0 select ascii(substring(fc,2,1)) from t; - 0 select ascii(substring(fc,3,1)) from t; - 0 regards, Hiroshi Inoue
>From: Hiroshi Inoue [mailto:Inoue@tpf.co.jp] >Sent: Saturday, November 16, 2002 12:38 AM [..] >As long as you are using text(char or varchar) type, you can't >store strings other than null terminated ones. >Please use BYTEA type instead and bind the parameter using >SQL_C_BINARY and SQL_VARBINARY. But that's true only in ODBC, so I think it _is_ a bug in ODBC driver. It is perfectly possible to store 0 in varchar or char fields. Please try from psql: INSERT INTO t values (chr(0)||chr(1)||chr(0)); and then check: select ascii(substring(fc,1,1)) from t; - 0 select ascii(substring(fc,2,1)) from t; - 1 select ascii(substring(fc,3,1)) from t; - 0 Works perfect! What do you think about this? Best reagards, Bozena Potempa
Bo?ena Potempa wrote:
>
> >Which version of PostgreSQL server are you connecting ?
>
> 7.2.3.
I can't your case in 7.2.3 but in 7.2.1 the result is
0 0 0 not 0 1 0.
regards,
Hiroshi Inoue
http://w2422.nsk.ne.jp/~inoue/
>Which version of PostgreSQL server are you connecting ? 7.2.3. Bozena Potempa
Hiroshi, > >Which version of PostgreSQL server are you connecting ? >> >> 7.2.3. > >I can't your case in 7.2.3 but in 7.2.1 the result is > 0 0 0 not 0 1 0. It is more complicated then I thought. I have 2 test installations of PostgreSQL: one on Linux and the second one on Windows NT, both 7.2.3 version. The Linux installation works with 0 stored in varchar field (the test results are as described in my previous e-mail). The Windows installation does not support zeroes in strings! Now I am not sure which behaviour is correct (and planned by PostgreSQL developers). I am new to PostgreSQL, but I know some other databases better and all of them allows 0 in strings. Could you please ask somebody with 7.2.3 on Linux to run my test and confirm the results? Best regards, Bozena Potempa
> -----Original Message----- > From: Bo?ena Potempa [mailto:Bozena.Potempa@otc.pl] > > Hiroshi, > > > >Which version of PostgreSQL server are you connecting ? > >> > >> 7.2.3. > > > >I can't your case in 7.2.3 but in 7.2.1 the result is > > 0 0 0 not 0 1 0. > > It is more complicated then I thought. > I have 2 test installations of PostgreSQL: one on Linux > and the second one on Windows NT, both 7.2.3 version. > The Linux installation works with 0 stored in varchar > field (the test results are as described in my previous > e-mail). The Windows installation does not support zeroes > in strings! Hmm the difference may come from (non-)multibyte mode. > Now I am not sure which behaviour is correct (and planned > by PostgreSQL developers). As far as I see PostgreSQL couldn't handle non-null-terminated strings correctly. regards, Hiroshi Inoue
Bo?ena Potempa wrote:
>
> [..]
> >Hmm the difference may come from (non-)multibyte mode.
>
> I don't think so. My PostgreSQL on Linux installation is standard,
> without support for multibyte characters, without support for
> any national characterset and sorting. Just installed
> 'as is' on the fresh RedHat 7.3.
Isn't your PostgreSQL on Windows NT with multibyte suppoort ?
My environment is with multibyte support and so it doesn't
support non-null-terminated string.
> >> Now I am not sure which behaviour is correct (and planned
> >> by PostgreSQL developers).
> >
> >As far as I see PostgreSQL couldn't handle non-null-terminated
> >strings correctly.
>
> OK. But I think, that even if some earlier versions of PostgreSQL
> do not support 0s in strings then later versions will support them.
> So ODBC driver should not cut strings containing zeroes, but
According to an ODBC related document, the behavior of ODBC
functions in such case is undefined and it is driver-specific
if a driver handles this correctly.
> insert/read data entirely making usage of provided length.
> Is it implemented like that?
No and how can I implement it ?
Must I convert '\0' to "|| chr(0) ||" where there's no
guarantee that it works ?
regards,
Hiroshi Inoue
http://w2422.nsk.ne.jp/~inoue/
Bo?ena Potempa wrote:
>
> [..]
> >According to an ODBC related document, the behavior of ODBC
> >functions in such case is undefined and it is driver-specific
> >if a driver handles this correctly.
>
> OK. If it is driver-specific then why don't allow users to
> to use this feature if it is implemented in the server?
If it is implemented in the server I would implement it.
However, it isn't implemented in the server and so
there's no recommended way to store 0s in strings now.
Even if 0s are stored in strings accidentally like your
example shows, there are few code which take them into
account and they would be lost before long. As for me
I've never minded 0s in strings in the backend coding.
I don't think your request is unreasonable but what
you should request the change first seems from the
backend side.
regards,
Hiroshi Inoue
http://w2422.nsk.ne.jp/~inoue/
[..] >According to an ODBC related document, the behavior of ODBC >functions in such case is undefined and it is driver-specific >if a driver handles this correctly. OK. If it is driver-specific then why don't allow users to to use this feature if it is implemented in the server? >> insert/read data entirely making usage of provided length. >> Is it implemented like that? > >No and how can I implement it ? >Must I convert '\0' to "|| chr(0) ||" where there's no >guarantee that it works ? Why not? Your statement suggests that parameter values are hard-coded into SQL statements by ODBC driver. Is that true? If you encode 0 as "|| chr(0) ||" it will change nothing: if the server is able to store something after 0 then it will be stored, otherwise not. If you afraid then maybe this behaviour can be implemented as an option to the ODBC data source (like "LF<->CR/LF Conversion")? Is it much work to add such option? Best regards, Bozena Potempa P.S. 0s in strings are important for some of our customers, storing very strange things in their old Clipper applications.
>If it is implemented in the server I would implement it. >However, it isn't implemented in the server and so >there's no recommended way to store 0s in strings now. >Even if 0s are stored in strings accidentally like your >example shows, there are few code which take them into >account and they would be lost before long. As for me >I've never minded 0s in strings in the backend coding. > >I don't think your request is unreasonable but what >you should request the change first seems from the >backend side. I will ask on pgsql-hackers and then return to ODBC. Thank you very much for your help. Bozena Potempa
[..] >Hmm the difference may come from (non-)multibyte mode. I don't think so. My PostgreSQL on Linux installation is standard, without support for multibyte characters, without support for any national characterset and sorting. Just installed 'as is' on the fresh RedHat 7.3. >> Now I am not sure which behaviour is correct (and planned >> by PostgreSQL developers). > >As far as I see PostgreSQL couldn't handle non-null-terminated >strings correctly. OK. But I think, that even if some earlier versions of PostgreSQL do not support 0s in strings then later versions will support them. So ODBC driver should not cut strings containing zeroes, but insert/read data entirely making usage of provided length. Is it implemented like that? And again: could you ask somebody with 7.2.3 on Linux to run my test and confirm the results? Best regards, Bozena Potempa