Roy,

Yes you are right. However, if there was a bug in 4.34, it has been fixed as I cant reproduce that behavior here with 4.35.1. Also there is no need to exclude RecordId, it's already excluded from a 'select *' and RecordId values, as you already said, are generated as new records are inserted.

So, if DBISAM says that there are duplicated key values, it's most likely because there are, but we can't tell for sure because we don't know what fields are in the PK nor what data is being inserted, only Dave can check it out.

--
Fernando Dias
[Team Elevate]

Fernando


I'm back at 4.25 but you can probably say - how does DBISAM treat nulls in the primary key these days?

Roy Lambert

Roy,

The same way it always did, an empty string is considered to be equal to NULL and it's allowed to have one - but only one - occurrence of an empty string as primary key.

--
Fernando Dias
[Team Elevate]

Fernando

>The same way it always did, an empty string is considered to be equal to NULL and it's allowed to have one - but only one - occurrence of an empty string as primary key.

Thanks. That's my guess then Dave's got some null primary keys. It will be interesting to find out what the root cause is.

Roy

Dave,

<< I am attempting to join several identically structured files with
absolutely independent data into one larger file. All files work perfectly
well alone. All files have been "Repaired" and are the current rev4.

This code, which used to work pre the DBISAM/Delphi updates, now works when
the first file is inserted into an empty result file but produces the #9729
error when a second file is inserted into the now not-empty result file.
(I've left out the file selection and loop stuff all of which has been
verified and worked in the old version as well.) >>

Please email me the relevant tables and the SQL that you're using that
reproduces the problem.  I'll take a look and tell you what's going on.

Tim Young
Elevate Software
www.elevatesoft.com

Thanks all.  Good info.

Here's why it used to work and now doesn't from dbisam4rsdelphi2010.pdf

"Auto Primary Index  ---  In version 3.x and earlier you could have a table without a primary index. In version 4, if you do not define a primary index when creating or restructuring a table, DBISAM will automatically add a primary index on the system RecordID field mentioned above."

AND, looking in dbsys all my tables have RecordID as the Primary. Oops

And, all the tables already exist and usually have a unique identifier but that's not guaranteed. So, I'm off to see "adding and deleting indexes" , removing RecordID as the primary and installing RecordHash as the primary. And setting it up to do so on the fly and hoping it works.

Actually that should not be the case  - as Roy and Fernando pointed out
the RecordID is not part of the "select *" fields list so DBISAM would
simply auto-populate it for you uniquely.

In 4.35 b2 i can do :
"Insert into "desttable" select * From "sourcetable";

as many times as i want without any duplicate errors so and as expected
i just get the desttable with new rows appended (desttable has just 1
index - the auto created one).

So check your dbisam version and indexes.

Raul




On 4/17/2013 1:15 PM, Dave Delage wrote:
> Thanks all.  Good info.
>
> Here's why it used to work and now doesn't from dbisam4rsdelphi2010.pdf
>
> "Auto Primary Index  ---  In version 3.x and earlier you could have a table without a primary index. In version 4, if you do not define a primary index when creating or restructuring a table, DBISAM will automatically add a primary index on the system RecordID field mentioned above."
>
> AND, looking in dbsys all my tables have RecordID as the Primary. Oops
>
> And, all the tables already exist and usually have a unique identifier but that's not guaranteed. So, I'm off to see "adding and deleting indexes" , removing RecordID as the primary and installing RecordHash as the primary. And setting it up to do so on the fly and hoping it works.
>

Finally had a chance to get back to this, the solution was to add this to each file prior to the ExecSQL.

           DeleteIndex('');   //delete the existing primary (usually RecordID) otherwise the next line will fail
           AddIndex('', 'RecordHash', [ixPrimary]);   //add primary on RecordHash

I had a Try/Except block so that the AddIndex did not crash if the new primary was already there in the second or third time around the loop, but the DeleteIndex takes care of that exception. This runs well with the original SQL so there were no other changes necessary. All my files are small so the extra processing time is inconsequential. Thanks to all for the help.