[ psqlodbc-Bugs-1003103 ] copy_and_convert_field(...) destroys bind info for wide char varchar columns

Bugs item #1003103, was opened at 2007-04-15 14:39
You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1003103&group_id=1000125

Category: None
Group: None
Status: Open
Resolution: None
Priority: 3
Submitted By: Thomas Zehbe (thomasz)
Assigned to: Nobody (None)
Summary: copy_and_convert_field(...) destroys bind info for wide char varchar columns

Initial Comment:
I'm using psqlodbc 08.02.0200 on SuSE Linux 9.3, Postgres 8.0.1 with libiodbc 3.52.5 and wxWidgets.

psqlodbcs config params are:
./configure --enable-pthreads --enable-unicode --with-iodbc CFLAGS="-ggdb -O0

The problem occured is that for varchar colums an insert is possible up to the max length, but an update only up to the count of characters of the value wich was last read.
F. E. a colum containg "12345" can't be updated to "12345aaa". It's always cut to "12345".

I found that ResolveOneParam(...) sets "used" to the number of last readed chars, instead of SQL_NTS (in fact "-3"). So on update only this number of chars is converted.

At the end of copy_and_convert_field I Found this statement:
if (pcbValue)
*((SQLLEN *) pcbValueBindRow) = len;
During the first read *pcbValue is "-3" for SQL_NTS columns, but the assignment sets it to the count of chars read.
I changed it to
if (pcbValue)
if (*pcbValue >= 0)
*((SQLLEN *) pcbValueBindRow) = len;
so SQL_NTS is retained in *pcbValue.

Now update works

Regards,
Thomas

----------------------------------------------------------------------

>Comment By: Hiroshi Inoue (hinoue)
Date: 2007-05-08 21:42

Message:
Sorry for the late reply.
I can't see my reply whcih I believe I have sent.

> looks like this:
> igDbDate date;
> date.Set(b.mydate);
> SetColNull(_T("mydate"), !date.IsValid());

Is there any chance to call SetColNull(_T("..."), false)
for varchar columns also ?

regards,
Hiroshi Inoue

----------------------------------------------------------------------

Comment By: Thomas Zehbe (thomasz)
Date: 2007-05-06 06:46

Message:
Most of the columns are defined NUT NULL. Only some date and
datatime columns may contain NULL. In this case the code
looks like this:
igDbDate date;
date.Set(b.mydate);
SetColNull(_T("mydate"), !date.IsValid());
with b.mydate is of type DATE_STRUCT and the check is done
just before insert/update.

For the NOT NULL varchar columns the buffer structures are
allways initialized like this:
wxStrcpy (mycolumn, _T(""));
before they are used to copy the actual values of the
wxTextCtrls to them when the user presses the save button.

Does that answer your question?

Regards,
Thomas

----------------------------------------------------------------------

Comment By: Hiroshi Inoue (hinoue)
Date: 2007-05-04 22:01

Message:
How does your application set the column NULL ?

regards,
Hiroshi Inoue

----------------------------------------------------------------------

Comment By: Thomas Zehbe (thomasz)
Date: 2007-05-04 12:56

Message:
>You seem to be misunderstaning ODBC specs. Though wxDbTable
> shares column buffers and parameter ones, it's neither a
> requirement nor a recommendation of ODBC.

To be honest, I never read the specs. Do I understand right:
column buffers are for data retrieval only and parameter
buffers contain the data to be used for updates/inserts?

Did a quick google search for the odbc specs, but didn't
find them. Do you have a link?

I'm wondering that wxWidgets use use dbc licke this an
doesn't at least supply the binding structures descripting
fields twice.

>Oops my mistake, what I meant was wxDbTable::SetColNull().
No PRoblem!
>You had better call SetColNull(false/true) every time
> after you changed the contents of column buffers.
>SetColNull() sets the colDef's CbValue to SQL_NTS when
> the parameter is false otherwise sets it to SQL_NULL_DATA.

>The most poper way may be to call wxDbTable::SetColumn()
>to change the content of columns because wxDbTable can
>detect all changes.
That would be hard to implement in my current code and
SetColumn() isn't an "official" call.
As all of my tables are subclasse of a single tableClass
defined by me, I used the method described in the
wxListArchive item you mentioned earlier. Setting pcbValue
to SQL_NTS for DB_DATA_TYPE_VARCHAR cols in Update() works
fine (and until now with no visible side effects).

But I will post an item on the wxList again.

Many thanks for spending your time and your help on this topic!

Regards,
Thomas

----------------------------------------------------------------------

Comment By: Hiroshi Inoue (hinoue)
Date: 2007-05-04 02:25

Message:
> WhatI don't understand is that in ResolveOneParam at line
> 3336 inteprets this parameter. The variable used is set
> in line 3231,

> where the content of apara->used is equal to the number
> of bytes last read. And apara->used in fact is a
> pointer to memory affected by the last read.

You seem to be misunderstaning ODBC specs. Though wxDbTable
shares column buffers and parameter ones, it's neither a
requirement nor a recommendation of ODBC.

> I couldn't find SetNullColNull() in psqlODBC, libiodbc
> and wxWidgets. I found SetColumn() in wxWidgets,
> but it is not documented.

Oops my mistake, what I meant was wxDbTable::SetColNull().
You had better call SetColNull(false/true) every time
after you changed the contents of column buffers.
SetColNull() sets the colDef's CbValue to SQL_NTS when
the parameter is false otherwise sets it to SQL_NULL_DATA.

The most poper way may be to call wxDbTable::SetColumn()
to change the content of columns because wxDbTable can
detect all changes.

regards,
Hiroshi Inoue

----------------------------------------------------------------------

Comment By: Thomas Zehbe (thomasz)
Date: 2007-05-03 07:56

Message:
> Please note pcbValue(BindRow) is an output parameter not
>an i-o one.
WhatI don't understand is that in ResolveOneParam at line
3336 inteprets this parameter. The variable used is set in
line 3231, where the content of apara->used is equal to the
number of bytes last read. And apara->used in fact is a
pointer to memory affected by the last read.

>ISTM the proper way is to call SetNullColNull() when the
>column buffers are changed or change the column contents
>via SetColumn() function.

I couldn't find SetNullColNull() in psqlODBC, libiodbc and
wxWidgets. I found SetColumn() in wxWidgets, but it is not
documented.

In line 3320 of convert.c used==SQL_NTS is checked, but only
if it is in win_unicode mode. This correlates to the fact,
the as long as I didn't use unicode mode, everything works
as only the buffer pointer is copied in the SQL_C_CHAR case.

From my point of view I still find that it makes no sense to
return the number of bytes read for a VARCHAR database
column, because it is initially bind as SQL_NTS. It should
be sufficient for an application to provide a buffer long
enough for the null terminated string and to indicate that
there is a null terminated string in this buffer (using
SQL_NTS) and not the actual number of bytes.

Can you agree? If not, where is my misunderstanding?

Regards,
Thomas

----------------------------------------------------------------------

Comment By: Hiroshi Inoue (hinoue)
Date: 2007-04-23 01:30

Message:
> Is it according to the specs that *pcbValueBindRow==
SQL_NTS when entering copy_and_convert_field and is
set to the number of SQL_WCHARs just read before exiting?

Please note pcbValue(BindRow) is an output parameter not
an i-o one.
ISTM the proper way is to call SetNullColNull() when the
column buffers are changed or change the column contents
via SetColumn() function.

regards,
Hiroshi Inoue

regards,
Hiroshi Inoue

----------------------------------------------------------------------

Comment By: Thomas Zehbe (thomasz)
Date: 2007-04-22 12:06

Message:
There's no answer from wxList until now.

I did a further examination. When I define a tables column
using SetColDefs() for this column a wxDbColDefs object is
initialized. In case of type varchar the public member
"CbValue" of this object is set to -3 (SQL_NTS).

I put a watchpoint in gdb on this addess, and found that it
is only changed by convert.c in the statement mentioned
when I openend this track item, as (SQLLEN *)
pcbValueBindRow in facts points to this adress.

Does this behaviour proves an incorrect use of psqlodbc? Is
it according to the specs that *pcbValueBindRow==SQL_NTS
when entering copy_and_convert_field and is set to the
number of SQL_WCHARs just read before exiting?

Regards,
Thomas

----------------------------------------------------------------------

Comment By: Thomas Zehbe (thomasz)
Date: 2007-04-19 07:13

Message:
Many thanks for your hint!

I put your information immediately on the wxList and will
explore the problem myself, too. Maybe it'll take some time
as I'm a bit under pressure actually.

I'll report the results.

Thanks again and regards,
Thomas

----------------------------------------------------------------------

Comment By: Hiroshi Inoue (hinoue)
Date: 2007-04-19 02:30

Message:
AFAIC it's not the problem of psqlodbc driver.
I found a similar report at
http://lists.wxwidgets.org/archive/wx-users/msg20409.html
.
Hmm it was more than 4 years ago. Was it solved ?

regards,
Hiroshi Inoue

----------------------------------------------------------------------

Comment By: Thomas Zehbe (thomasz)
Date: 2007-04-18 10:24

Message:
Sorry for racting late. But I didn't get an email notifier.
Seems that I have to take a look.

I'n not sure what you mean with ODBC API sequences. So let
me give a first try. I attach a file with an code example.

The lines which provide the buffer on a per column base look
like that:
SetColDefs (idx++, wxT("prodbezeichnung"),
DB_DATA_TYPE_VARCHAR, b.prodbezeichnung, SQL_C_WXCHAR,
sizeof(b.prodbezeichnung),FALSE,TRUE);

It is the wxWidget interface defined as
wxDbTable::SetColDefs(). I provide only a single buffer for
every column. I believe that wxWidgets handles the param
buffers. It must be the case when I use a where clause typed
as a string.

In wxDbTable::Open() wxWidgets first (indirectly) calls
PGAPI_BindParameter for every column wich should be read and
afterwords for every column wich should be updatable.

In my apps typically all coulumns are readable, but not all
are updatebale (primary key and all columns in a view wich
don't belong to the 'main' tabkle are not updatetable.

Hope this gives a first hint what happens.

Thanks and regards
Thomas

----------------------------------------------------------------------

Comment By: Hiroshi Inoue (hinoue)
Date: 2007-04-16 23:43

Message:
Hmm don't use the same buffers for both columns and
parameters ? If so, please treat them carefully.

regards,
Hiroshi Inoue

----------------------------------------------------------------------

Comment By: Hiroshi Inoue (hinoue)
Date: 2007-04-16 04:04

Message:
Could you tell me the ODBC API sequences in your app ?

regards,
Hiroshi Inoue

----------------------------------------------------------------------

You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1003103&group_id=1000125