|
| Débuté par Guenter Predl, 07 juin 2004 00:01 - 3 réponses |
| |
| | | |
|
| |
Posté le 07 juin 2004 - 00:01 |
Hi friends,
HyperFile 8 now supports the NULL value. 'Nice feature', I thought. To my experience (other database), assigning a NULL to any key item will exclude this value from being used in the associated index. Consequently, there is no problem to keep this item as a unique key, because those multiple NULL values do not count as multiple key values, since they are excluded from the index anyway.
Seems to be a little different with HyperFile 8. Any file using the NULL values has to have the check box 'NULL supported' checked in the File Description. OK, my file has. Next, I'm using a unique key (EAN/UPC-number, a string of 13 bytes). In the File Items Window, for this item the check-box 'NULL by default' is checked. Next, be aware that RAD does not set the 'NULL if empty' check-box und 'Details' in the associated edit control.
I expected the following behaviour: If the field was left empty on input, the item would be disregarded within the index and therefore I could have any number of empty strings (they'd be assigned the value NULL automatically due to that 'NULL if empty' and 'NULL by default' check-boxes), simply they wouldn't count. But, if the user inputs a second EAN/UPC while an identical number is already in the file, there we get a duplicates error, just because its a unique key. No, it doesn't work this way.
Actual behaviour: Two 'empty' EAN/UPC-numbers are not tolerated - they return a duplicates error just like any duplicate EAN/UPC would do. So, what's the difference to the way we had it without that NULL?
Best regards,
Guenter |
| |
| |
| | | |
|
| | |
| |
Posté le 07 juin 2004 - 11:09 |
Hi friends,
HyperFile 8 now supports the NULL value. 'Nice feature', I thought. To my experience (other database), assigning a NULL to any key item will exclude this value from being used in the associated index. Consequently, there is no problem to keep this item as a unique key, because those multiple NULL values do not count as multiple key values, since they are excluded from the index anyway.
Seems to be a little different with HyperFile 8. Any file using the NULL values has to have the check box 'NULL supported' checked in the File Description. OK, my file has. Next, I'm using a unique key (EAN/UPC-number, a string of 13 bytes). In the File Items Window, for this item the check-box 'NULL by default' is checked. Next, be aware that RAD does not set the 'NULL if empty' check-box und 'Details' in the associated edit control.
I expected the following behaviour: If the field was left empty on input, the item would be disregarded within the index and therefore I could have any number of empty strings (they'd be assigned the value NULL automatically due to that 'NULL if empty' and 'NULL by default' check-boxes), simply they wouldn't count. But, if the user inputs a second EAN/UPC while an identical number is already in the file, there we get a duplicates error, just because its a unique key. No, it doesn't work this way.
Actual behaviour: Two 'empty' EAN/UPC-numbers are not tolerated - they return a duplicates error just like any duplicate EAN/UPC would do. So, what's the difference to the way we had it without that NULL?
Best regards,
Guenter
Hello Guenter,
don't worry. HF plays here in the same league as MS SQL Server. MS SQL Server allow only 1 NULL row in a table if the column containing the NULL value is part of the PK or a UNIQUE CONSTRAINT. Only multiple NULL values in a INDEX are allowed.
Raimund
http://www.invitec.com |
| |
| |
| | | |
|
| | |
| |
Posté le 07 juin 2004 - 11:46 |
Hi friends,
HyperFile 8 now supports the NULL value. 'Nice feature', I thought. To my experience (other database), assigning a NULL to any key item will exclude this value from being used in the associated index. Consequently, there is no problem to keep this item as a unique key, because those multiple NULL values do not count as multiple key values, since they are excluded from the index anyway.
Seems to be a little different with HyperFile 8. Any file using the NULL values has to have the check box 'NULL supported' checked in the File Description. OK, my file has. Next, I'm using a unique key (EAN/UPC-number, a string of 13 bytes). In the File Items Window, for this item the check-box 'NULL by default' is checked. Next, be aware that RAD does not set the 'NULL if empty' check-box und 'Details' in the associated edit control.
I expected the following behaviour: If the field was left empty on input, the item would be disregarded within the index and therefore I could have any number of empty strings (they'd be assigned the value NULL automatically due to that 'NULL if empty' and 'NULL by default' check-boxes), simply they wouldn't count. But, if the user inputs a second EAN/UPC while an identical number is already in the file, there we get a duplicates error, just because its a unique key. No, it doesn't work this way.
Actual behaviour: Two 'empty' EAN/UPC-numbers are not tolerated - they return a duplicates error just like any duplicate EAN/UPC would do. So, what's the difference to the way we had it without that NULL?
Best regards,
Guenter
Hello Guenter, don't worry. HF plays here in the same league as MS SQL Server. MS SQL Server allow only 1 NULL row in a table if the column containing the NULL value is part of the PK or a UNIQUE CONSTRAINT. Only multiple NULL values in a INDEX are allowed. Raimund
Hi Raimund,
thank you for detailing the behaviour of MS-SQL-Server regarding NULL index values!
I just documented the current behaviour of HF8 for all of you who are trying to use the NULL value and are experiencing some, uh, misbehaviour. I'm using the 'Search'-option of this forum quite often and amazingly can find solutions to many of my ad-hoc-problems. This posting should add to that hidden database of solutions buried within the forum.
Btrieve (and most probably Pervasive SQL too) works like expected. NULLs are excluded from the index and therefore one can maintain a unique key though having numerous NULLs within the index. Besides maintaining a unique key, the idea is to exclude these values from being read when traversing the database on that index. Only records with non-NULL key-values are read, thus the speed of reading large portions of a database can be increased enormously. All other methods (Filters etc.) require the database software to actually READ the record and discard it. This method just reads those index values that make sense.
Ranter's comment: Implementing NULL values is quite a job. I cannot imagine how it comes that some programmers sit down and implement a feature into their product - with considerable effort (time/money) - and dont understand the real-world usage of it. OK, now I feel better ..
Best regards,
Guenter |
| |
| |
| | | |
|
| | |
| |
Posté le 08 juin 2004 - 20:02 |
Got a response from PC Soft today and received a reference number too. Will be fixed whenever there's time ..
Best regards,
Guenter |
| |
| |
| | | |
|
| | | | |
| | |
|