Recently I have converted from UDB 7.2 (NT) to UDB 8.1.4 (W2K) and
have noticed a critical error W.R.T. our VB/ADO apps.

If I use the 7.2 client, then everything (except calls to federated
views) works great. If I use the 8.1.4 client, then I cannot send a
null or blank date back to the database (invalid format). I also
cannot null out the value of a field that has contraints on it
because the null get converted to an empty string (supposedly by ADO)
and that is not a valid value in the contraint list.

I have opend a PMR with IBM, but they are saying the only reason that
these problems didn't show up in previous versions is because there
was an error in those versions that accidently allows those values to
pass and that the version 8 client is working as expected.

Has anyone else seen this and is there some kind of a work-around ??

On Thu, 05 Feb 2004 14:41:47 GMT, kurt.wood@NOSP@M.state.mn.us (Remove
the obvious for replies) wrote:
[color=blue]
>Recently I have converted from UDB 7.2 (NT) to UDB 8.1.4 (W2K) and
>have noticed a critical error W.R.T. our VB/ADO apps.[/color]

Interesting scenario for us, because we have a client with a 7.2
server who is experiencing massive memory leaks when trying to connect
with an 8.1.x client...

What version is the server DB2?
[color=blue]
>If I use the 7.2 client, then everything (except calls to federated
>views) works great. If I use the 8.1.4 client, then I cannot send a
>null or blank date back to the database (invalid format). I also
>cannot null out the value of a field that has contraints on it
>because the null get converted to an empty string (supposedly by ADO)
>and that is not a valid value in the contraint list.[/color]

There is a big difference between NULL and an empty string. ADO is an
ActiveX layer around ODBC, AFAIK. NULLs have been around since
prehistoric (DBMS) times. You need to distinguish between the two.

How are you defining a "NULL" value on the client side? If the
database column has a NOT NULL constraint on a certain field, there is
presumably a good reason for that.

Is it a matter of bind variables? Or are you constructing the SQL
completely as text?
[color=blue]
>I have opend a PMR with IBM, but they are saying the only reason that
>these problems didn't show up in previous versions is because there
>was an error in those versions that accidently allows those values to
>pass and that the version 8 client is working as expected.[/color]

They are probably right.
[color=blue]
>Has anyone else seen this and is there some kind of a work-around ??[/color]

The work-around is not to try to pass a null value to a column which
has a "not null" constraint. Is there a default value defined? If so,
just leave that column out of any INSERT statements.


--
Bob Hairgrove
NoSpamPlease@Home.com

On Thu, 05 Feb 2004 22:11:48 +0100, Bob Hairgrove
<wouldnt_you_like@to_know.com> wrote:
[color=blue]
>On Thu, 05 Feb 2004 14:41:47 GMT, kurt.wood@NOSP@M.state.mn.us (Remove
>the obvious for replies) wrote:
>[color=green]
>>Recently I have converted from UDB 7.2 (NT) to UDB 8.1.4 (W2K) and
>>have noticed a critical error W.R.T. our VB/ADO apps.[/color]
>
>Interesting scenario for us, because we have a client with a 7.2
>server who is experiencing massive memory leaks when trying to connect
>with an 8.1.x client...
>
>What version is the server DB2?[/color]
We are using DB2 ESE 8.1.4 on a W2K box.
BTW, I am not surprised you are seeing problems going the other way...
[color=blue]
>[color=green]
>>If I use the 7.2 client, then everything (except calls to federated
>>views) works great. If I use the 8.1.4 client, then I cannot send a
>>null or blank date back to the database (invalid format). I also
>>cannot null out the value of a field that has contraints on it
>>because the null get converted to an empty string (supposedly by ADO)
>>and that is not a valid value in the contraint list.[/color]
>
>There is a big difference between NULL and an empty string. ADO is an
>ActiveX layer around ODBC, AFAIK. NULLs have been around since
>prehistoric (DBMS) times. You need to distinguish between the two.
>[/color]
I have a firm grip on what null is and what an empty string is. In
this , we have a "nullable" field with a list of 4 possible values. If
, on the VB form, we delete the value in the control, then behind the
scenes we trap for an empty string, convert that to a null and try to
pass that to the database. This works fine on a 7.2 client, but with
an 8.1.4 client, our null is "reconverted" to an empty string.[color=blue]
>How are you defining a "NULL" value on the client side? If the[/color]
We define null by setting it to VBNullString (also VBNull and
VBNullChar)[color=blue]
>database column has a NOT NULL constraint on a certain field, there is
>presumably a good reason for that.
>
>Is it a matter of bind variables? Or are you constructing the SQL
>completely as text?[/color]
ODBC hanldes the low level operations for the ADO calls.
[color=blue][color=green]
>>I have opend a PMR with IBM, but they are saying the only reason that
>>these problems didn't show up in previous versions is because there
>>was an error in those versions that accidently allows those values to
>>pass and that the version 8 client is working as expected.[/color]
>
>They are probably right.[/color]
Maybe, but that would sure show poor QA/QC in allowing an error to
propagate thru 3 complete versions of DB2....
[color=blue][color=green]
>>Has anyone else seen this and is there some kind of a work-around ??[/color]
>
>The work-around is not to try to pass a null value to a column which
>has a "not null" constraint. Is there a default value defined? If so,
>just leave that column out of any INSERT statements.[/color]
ahhh, yup...[color=blue]
>
>--
>Bob Hairgrove
>NoSpamPlease@Home.com[/color]

On Thu, 05 Feb 2004 21:28:50 GMT, kurt.wood@NOSP@M.state.mn.us (Remove
the obvious for replies) wrote:
[color=blue][color=green]
>>There is a big difference between NULL and an empty string. ADO is an
>>ActiveX layer around ODBC, AFAIK. NULLs have been around since
>>prehistoric (DBMS) times. You need to distinguish between the two.
>>[/color]
>I have a firm grip on what null is and what an empty string is.[/color]

OK. I apologize if I sounded condescending ... I didn't mean to, just
trying to establish a common point of agreement. You might be
surprised to see how many people don't know the difference, especially
when coming from Access or VB.
[color=blue]
>In this , we have a "nullable" field with a list of 4 possible values. If
>on the VB form, we delete the value in the control, then behind the
>scenes we trap for an empty string, convert that to a null and try to
>pass that to the database. This works fine on a 7.2 client, but with
>an 8.1.4 client, our null is "reconverted" to an empty string.[/color]
[color=blue][color=green]
>>How are you defining a "NULL" value on the client side? If the[/color]
>We define null by setting it to VBNullString (also VBNull and
>VBNullChar)[/color]
[color=blue][color=green]
>>Is it a matter of bind variables? Or are you constructing the SQL
>>completely as text?[/color]
>ODBC hanldes the low level operations for the ADO calls.[/color]

Hmmm ... since nothing has changed on the VB/ADO side, I suppose that
it was passing an empty string all along. Only the DB2 client has
changed.

I wonder if there is a setting in the DB2 client or server which would
influence how the database handles empty strings? I believe that with
Oracle, for example, there is a parameter for this. I know because we
had a procedure with a little lookup table and querying for NULL
worked in our test environment, but failed in production because it
was either returning a single space or an empty string.

What about doing the updates through a stored procedure? Or a
BeforeUpdate/BeforeInsert trigger?
[color=blue][color=green][color=darkred]
>>>I have opend a PMR with IBM, but they are saying the only reason that
>>>these problems didn't show up in previous versions is because there
>>>was an error in those versions that accidently allows those values to
>>>pass and that the version 8 client is working as expected.[/color]
>>
>>They are probably right.[/color]
> Maybe, but that would sure show poor QA/QC in allowing an error to
>propagate thru 3 complete versions of DB2....[/color]

If it were a known issue, I would agree with that. Maybe it just never
seemed to matter?
[color=blue][color=green][color=darkred]
>>>Has anyone else seen this and is there some kind of a work-around ??[/color]
>>
>>The work-around is not to try to pass a null value to a column which
>>has a "not null" constraint. Is there a default value defined? If so,
>>just leave that column out of any INSERT statements.[/color]
>ahhh, yup...[/color]

OK, you did say the column is nullable ... I would (a) look for a
possible setting on the DB2 side of things, and (b) look into doing
the updates through a stored procedure.


--
Bob Hairgrove
NoSpamPlease@Home.com

On Fri, 06 Feb 2004 13:49:05 +0100, Bob Hairgrove
<wouldnt_you_like@to_know.com> wrote:
[color=blue]
>On Thu, 05 Feb 2004 21:28:50 GMT, kurt.wood@NOSP@M.state.mn.us (Remove
>the obvious for replies) wrote:
>[color=green][color=darkred]
>>>There is a big difference between NULL and an empty string. ADO is an
>>>ActiveX layer around ODBC, AFAIK. NULLs have been around since
>>>prehistoric (DBMS) times. You need to distinguish between the two.
>>>[/color]
>>I have a firm grip on what null is and what an empty string is.[/color]
>
>OK. I apologize if I sounded condescending ... I didn't mean to, just
>trying to establish a common point of agreement. You might be
>surprised to see how many people don't know the difference, especially
>when coming from Access or VB.
>[color=green]
>>In this , we have a "nullable" field with a list of 4 possible values. If
>>on the VB form, we delete the value in the control, then behind the
>>scenes we trap for an empty string, convert that to a null and try to
>>pass that to the database. This works fine on a 7.2 client, but with
>>an 8.1.4 client, our null is "reconverted" to an empty string.[/color]
>[color=green][color=darkred]
>>>How are you defining a "NULL" value on the client side? If the[/color]
>>We define null by setting it to VBNullString (also VBNull and
>>VBNullChar)[/color]
>[color=green][color=darkred]
>>>Is it a matter of bind variables? Or are you constructing the SQL
>>>completely as text?[/color]
>>ODBC hanldes the low level operations for the ADO calls.[/color]
>
>Hmmm ... since nothing has changed on the VB/ADO side, I suppose that
>it was passing an empty string all along. Only the DB2 client has
>changed.
>
>I wonder if there is a setting in the DB2 client or server which would
>influence how the database handles empty strings? I believe that with
>Oracle, for example, there is a parameter for this. I know because we
>had a procedure with a little lookup table and querying for NULL
>worked in our test environment, but failed in production because it
>was either returning a single space or an empty string.
>
>What about doing the updates through a stored procedure? Or a
>BeforeUpdate/BeforeInsert trigger?[/color]

I considered that. I thought if I passed a bogus date (say 12-31-1899)
thru and then I could use a trigger to change that to null. It would
work, but considering I would have to apply it to more that 300
tables, it doesn't seem to be the best solution.[color=blue]
>[color=green][color=darkred]
>>>>I have opend a PMR with IBM, but they are saying the only reason that
>>>>these problems didn't show up in previous versions is because there
>>>>was an error in those versions that accidently allows those values to
>>>>pass and that the version 8 client is working as expected.
>>>
>>>They are probably right.[/color]
>> Maybe, but that would sure show poor QA/QC in allowing an error to
>>propagate thru 3 complete versions of DB2....[/color]
>
>If it were a known issue, I would agree with that. Maybe it just never
>seemed to matter?
>[color=green][color=darkred]
>>>>Has anyone else seen this and is there some kind of a work-around ??
>>>
>>>The work-around is not to try to pass a null value to a column which
>>>has a "not null" constraint. Is there a default value defined? If so,
>>>just leave that column out of any INSERT statements.[/color]
>>ahhh, yup...[/color]
>
>OK, you did say the column is nullable ... I would (a) look for a
>possible setting on the DB2 side of things, and (b) look into doing
>the updates through a stored procedure.
>[/color]
Here again, I could use a stored proc, but this seems like more of a
large scale change than I was hoping for. I still beleive the DB2
client is malfunctioning to a degree.These same routines worked in UDB
5.x, 6.x and 7.x and similar ones run perfect with ADO and Oracle 8.
The only odd-ball is UDB 8.1.4 . Coincicence ? Maybe, but I am not
thinking so.[color=blue]
>
>--
>Bob Hairgrove
>NoSpamPlease@Home.com[/color]

settings link just in case they contain related info.

PATCH1 and PATCH2
and
CLI/ODBC Configuration Keywords Listing by Category
http://www-306.ibm.com/software/data...ad/v8/cli.html

If ado is built over odbc, maybe you can take a trace?

PM

In the configuration assistant (CA), there is a Optimize for ...
and vb is in the list.

Maybe it does set a parm to fix your problem.

I made a test and by default for vb, it uses
PATCH2=6
PATCH1=1024
LOBMAXCOLUMNSIZE=1048575
LONGDATACOMPAT=1


6 Forces the driver to return a message indicating that scrollable
cursors are not supported. This setting is needed by some applications (such
as Visual Basic) that make use of LOBs or that do not need or want
scrollable cursors to be used, even though they have been explicitly
requested by the application.



1024 Returns SQL_SUCCESS instead of SQL_NO_DATA_FOUND from the
SQLExecute() and SQLExecDirect() functions if the executed UPDATE or DELETE
statement affected no rows. This value may be needed by some Microsoft
Visual Basic applications.


Humm....
Well, there is nothing here that would help in this case.

PM

Remove the obvious for replies wrote:
[color=blue]
> Recently I have converted from UDB 7.2 (NT) to UDB 8.1.4 (W2K) and
> have noticed a critical error W.R.T. our VB/ADO apps.
>
> If I use the 7.2 client, then everything (except calls to federated
> views) works great. If I use the 8.1.4 client, then I cannot send a
> null or blank date back to the database (invalid format). I also
> cannot null out the value of a field that has contraints on it
> because the null get converted to an empty string (supposedly by ADO)
> and that is not a valid value in the contraint list.
>
> I have opend a PMR with IBM, but they are saying the only reason that
> these problems didn't show up in previous versions is because there
> was an error in those versions that accidently allows those values to
> pass and that the version 8 client is working as expected.
>
> Has anyone else seen this and is there some kind of a work-around ??[/color]

You may find something useful on this site (which I discovered recently),
under the "ADO enumerations" link -

http://www.connectionstrings.com

We were able to use this to solve a number of problems we had with ADO and
DB2, by picking the right enumerations to set.

HTH

Phil

Thanks for trying , I have been thru all the CLI settings. In fact, we
have found that PATH2=6 and PATCH1=1408 seem to work the best in
previous versions.


On Fri, 6 Feb 2004 11:03:22 -0500, "PM \(pm3iinc-nospam\)"
<PM(pm3iinc-nospam)@sympatico.ca> wrote:
[color=blue]
>In the configuration assistant (CA), there is a Optimize for ...
>and vb is in the list.
>
>Maybe it does set a parm to fix your problem.
>
>I made a test and by default for vb, it uses
>PATCH2=6
>PATCH1=1024
>LOBMAXCOLUMNSIZE=1048575
>LONGDATACOMPAT=1
>
>
> 6 Forces the driver to return a message indicating that scrollable
>cursors are not supported. This setting is needed by some applications (such
>as Visual Basic) that make use of LOBs or that do not need or want
>scrollable cursors to be used, even though they have been explicitly
>requested by the application.
>
>
>
> 1024 Returns SQL_SUCCESS instead of SQL_NO_DATA_FOUND from the
>SQLExecute() and SQLExecDirect() functions if the executed UPDATE or DELETE
>statement affected no rows. This value may be needed by some Microsoft
>Visual Basic applications.
>
>
>Humm....
>Well, there is nothing here that would help in this case.
>
>PM
>
>[/color]

Thanks for the info, but it did not tell me anything that would help.

On Fri, 06 Feb 2004 23:03:12 GMT, Philip Nelson <gulfvuk@yahoo.co.uk>
wrote:
[color=blue]
>Remove the obvious for replies wrote:
>[color=green]
>> Recently I have converted from UDB 7.2 (NT) to UDB 8.1.4 (W2K) and
>> have noticed a critical error W.R.T. our VB/ADO apps.
>>
>> If I use the 7.2 client, then everything (except calls to federated
>> views) works great. If I use the 8.1.4 client, then I cannot send a
>> null or blank date back to the database (invalid format). I also
>> cannot null out the value of a field that has contraints on it
>> because the null get converted to an empty string (supposedly by ADO)
>> and that is not a valid value in the contraint list.
>>
>> I have opend a PMR with IBM, but they are saying the only reason that
>> these problems didn't show up in previous versions is because there
>> was an error in those versions that accidently allows those values to
>> pass and that the version 8 client is working as expected.
>>
>> Has anyone else seen this and is there some kind of a work-around ??[/color]
>
>You may find something useful on this site (which I discovered recently),
>under the "ADO enumerations" link -
>
>http://www.connectionstrings.com
>
>We were able to use this to solve a number of problems we had with ADO and
>DB2, by picking the right enumerations to set.
>
>HTH
>
>Phil[/color]