#1  
Old 20th June 2022, 08:49 PM
sorin sorin is offline
Junior Member
 
Join Date: Sep 2009
Posts: 8
Default Migration from v3 to v4: Epic failure

Hi,

We are using NexusDB in a Delphi application. We tried to migrate from version 3 of NexusDB to version 4.
We have at least a problem with the NexusDB4 version regarding the Null interpretation in a SQL Where Clause.
If we consider a table - Table1 having this content
ID Name Address
1 John Doe UK
2 Sam Monk (Null)
3 Pete Rose US

and we use the same SQL query:

Select * from Table1 where not (Address <> '')

In NexusDB version 3 we receive 1 record:
2 Sam Monk
but in NexusDB version 4, we receive no records. To be compliant with version 3 we have to modify the SQL like this:
Select * from Table1 where not (Address <> '') or (Address is null).
This is STOP GAME!
We can't do such checks in all our codes.

The question is if there is another solution - may be a setup of a NexusDB4 parameter - to be NexusDB3 compliant in this case of Null involving SQL syntax.

Thank you for your answer
Best regards,

PS NexusDB v3 is compatible with the latest Delphi version?
  #2  
Old 20th June 2022, 11:10 PM
Thorsten Engler [NDA]
 
Posts: n/a
Default Re: Migration from v3 to v4: Epic failure

NexusDB4 behaves correct in accordance with ISO:9075 (aka the SQL standard)
here.

NULL <> '' is UNKNOWN

NOT UNKNOWN is UNKNOWN

An SQL select statement with WHERE clause includes all rows for which the WHERE
clause evaluates to TRUE.

If NexusDB ever included that ROW in the resultset for that query, it was a
bug, which has been fixed by now.

sorin wrote:

>
> Hi,
>
> We are using NexusDB in a Delphi application. We tried to migrate from
> version 3 of NexusDB to version 4.
> We have at least a problem with the NexusDB4 version regarding the Null
> interpretation in a SQL Where Clause.
> If we consider a table - Table1 having this content
> ID Name Address
> 1 John Doe UK
> 2 Sam Monk (Null)
> 3 Pete Rose US
>
> and we use the same SQL query:
>
> Select * from Table1 where not (Address <> '')
>
> In NexusDB version 3 we receive 1 record:
> 2 Sam Monk
> but in NexusDB version 4, we receive no records. To be compliant with
> version 3 we have to modify the SQL like this:
> Select * from Table1 where not (Address <> '') or (Address is null).
> This is STOP GAME!
> We can't do such checks in all our codes.
>
> The question is if there is another solution - may be a setup of a
> NexusDB4 parameter - to be NexusDB3 compliant in this case of Null
> involving SQL syntax.
>
> Thank you for your answer
> Best regards,
>
> PS NexusDB v3 is compatible with the latest Delphi version?


  #3  
Old 21st June 2022, 10:02 AM
Eivind Bakkestuen [NDD]
 
Posts: n/a
Default Re: Migration from v3 to v4: Epic failure

> PS NexusDB v3 is compatible with the latest Delphi version?

Sorry, not even close. You should evaluate usage of this particular
WHERE constuct in other queries, if they may run into the same problem.

--
Eivind Bakkestuen [NDD]
  #4  
Old 22nd June 2022, 04:59 AM
Ad Franse
 
Posts: n/a
Default Re: Migration from v3 to v4: Epic failure

Op 20-06-2022 om 11:49 schreef sorin:

> ID Name Address
> 1 John Doe UK
> 2 Sam Monk (Null)
> 3 Pete Rose US
>
> and we use the same SQL query:
>
> Select * from Table1 where not (Address <> '')
>
>

select *
from Table1
where coalesce( address, '' ) = ''


example :

drop table if exists table1;
create table table1
(
id autoinc primary key
, name varchar( 32)
, address varchar( 32)
);
insert into table1 (name, address) values ( 'John Doe', 'UK' );
insert into table1 (name, address) values ( 'Sam Monk', NULL );
insert into table1 (name, address) values ( 'Pete Rose', 'US' );

select *
from Table1
where coalesce( address, '' ) = '';

Ad Franse
  #5  
Old 25th June 2022, 02:03 AM
Clément Doss
 
Posts: n/a
Default Re: Migration from v3 to v4: Epic failure

On 20/06/2022 06:49, sorin wrote:
> Hi,
>
> We are using NexusDB in a Delphi application. We tried to migrate from
> version 3 of NexusDB to version 4.
> We have at least a problem with the NexusDB4 version regarding the Null
> interpretation in a SQL Where Clause.
> If we consider a table - Table1 having this content
> ID Name Address
> 1 John Doe UK
> 2 Sam Monk (Null)
> 3 Pete Rose US
>


Another option Would be to add DEFAULT to Address?

drop table if exists table1;
create table table1
(
id autoinc primary key
, name varchar( 32)
, address varchar( 32) default ''
);

In this case no change to your queries are required.


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Migration to Berlin David Guest nexusdb.public.support 8 18th November 2017 06:42 AM
OT - D2007 to 10.1 migration Stefan Paege nexusdb.public.discussions 3 19th July 2016 09:05 AM
OT: XE to XE2 migration John Turner nexusdb.public.discussions 15 14th June 2012 02:47 AM
NX2 / NX3 Migration obeltrami nexusdb.public.support 1 16th December 2011 12:06 AM
migration issue. roberto s. chiroy nexusdb.public.support 2 4th August 2004 11:02 AM


All times are GMT +11. The time now is 06:25 PM.


Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.