DIBSAM 4.34 Build 7, Win 7  6.1 (Build 7601 SP 1),  Delphi® 2010 Version 14.0.3593.25826

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.)

    Insert into "ResultFile.dat" select * From "ExistingFileX.dat";    
    ExecSql;    //executing this line produces the error

The only possible duplicate key I see is RecordID which is certainly not unique and over which I seem to have no control.

What's the fix?

Dave,

What's the exact error message?
The error message indicates in what index was the duplicate key found.

--
Fernando Dias
[Team Elevate]

    Insert into "ResultFile.dat" select * From "ExistingFileX.dat";    
    ExecSql;    //executing this line produces the error

I've included a screen shot."newtest8 racers" is the actual name of the Result file. (e,g, newtest8 racers.dat)

Dave,

I think i've run into something similar - i believe DBISAM auto creates
a primary unique index for the RecordID (and i could not just delete it
as it would recreate it).

Easiest workaround would be either have primary unique index on some
other field.

In this case that likely won't work as you are appending multiple tables
so as  a workaround I would try adding the recordHash field to the
existing index (RecordID+Recordhash matching on 2 rows should be lot
less likely ).

The other way around is to not use "select *" but instead select
individual fields (omitting RecordID) though that might be lot more work.

Raul



On 4/16/2013 11:01 AM, Dave Delage wrote:

> DIBSAM 4.34 Build 7, Win 7  6.1 (Build 7601 SP 1),  Delphi® 2010 Version 14.0.3593.25826
>
> 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.)
>
>       Insert into "ResultFile.dat" select * From "ExistingFileX.dat";
>       ExecSql;    //executing this line produces the error
>
> The only possible duplicate key I see is RecordID which is certainly not unique and over which I seem to have no control.
>
> What's the fix?
>

Easiest workaround would be either have primary unique index on some
other field.

In this case that likely won't work as you are appending multiple tables
so as  a workaround I would try adding the recordHash field to the
existing index (RecordID+Recordhash matching on 2 rows should be lot
less likely ).

The other way around is to not use "select *" but instead select
individual fields (omitting RecordID) though that might be lot more work.

I've tried indexing on another field, no help.
I've tried select field1, field 2, etc without the RecordID, no help
There are several indexes, not sure which is primary. I'm not sure what "primary" is, I just set the index name I want when I need it. These files are closed as they are joined so I would think indexing was either not important or automatically maintained for all of them.
The thing that really bothers me is that this has worked for more than 10 years now and suddenly it doesn't.

Dave


Unless the table you're inserting into is created on the fly then you can find the primary index by looking at the table in DBSys. Its usually the top one on the list without a name.

Once you know the primary index you can also try a bit of diagnostics eg

SELECT * FROM ResultFile WHERE [primary id field] NOT IN (SELECT [primary id field] FROM ExistingFileX)

If there are any unique indices you may have to check those as well.

If you're creating the table just before inserting then as Raul suggests create a primary index yourself.

Its a while since I used DBISAM and I only have an older version installed so I can't remember how it treats NULLs (or emptystrings) in the primary index.  I think it used to allow any number of NULL primary keys in earlier versions but newer ones will allow one but not a second so you may have records which have NULL primary keys.

Roy Lambert [Team Elevate]

Dave,

For each table there is only one Primary Index, and if you don't create one DBISAM is going to automatically create one for you, on RecordID. The Primary Index has no name and it's indicated as being Primary and Unique if you look at the Indexes tab in the Alter Table window in DbSys utility.

A primary index indexes tables on it's Primary Key - the field or set of fields that can uniquely identify a record in the table.It's advisable to always create a primary index - if you don't DBISAM is automatically going to create one for you, on RecordID.

This means that there can't be duplicates in this index, and as you don't have any control on the values of RecordID, you should create your own Primary Index on the field or set of fields that you know for sure will have no duplicates across the tables. If there is no such a set, just add a field containing the table name or a "table number" for example, or as Raul already suggested use RecordHash, but I always prefer to create indexes on fields that I can control.

--
Fernando Dias
[Team Elevate]

Fernando


>A primary index indexes tables on it's Primary Key - the field or set of fields that can uniquely identify a record in the table.It's advisable to always create a primary index - if you don't DBISAM is automatically going to create one for you, on RecordID.

From Dave's last post <<I've tried select field1, field 2, etc without the RecordID, no help>> so I think there is a primary key already there.

Roy

Roy,

Yes there is, the error message shows it but doesn't say on what fields, I don't know if it's on RecodrdID or not.

--
Fernando Dias
[Team Elevate]

Em 17/04/2013 10:04, Roy Lambert escreveu:
> Fernando
>
>
>> A primary index indexes tables on it's Primary Key - the field or set of fields that can uniquely identify a record in the table.It's advisable to always create a primary index - if you don't DBISAM is automatically going to create one for you, on RecordID.
>
>  From Dave's last post <<I've tried select field1, field 2, etc without the RecordID, no help>> so I think there is a primary key already there.
>
> Roy
>

Fernando

>Yes there is, the error message shows it but doesn't say on what fields, I don't know if it's on RecodrdID or not.

>>> A primary index indexes tables on it's Primary Key - the field or set of fields that can uniquely identify a record in the table.It's advisable to always create a primary index - if you don't DBISAM is automatically going to create one for you, on RecordID.
>>
>> From Dave's last post <<I've tried select field1, field 2, etc without the RecordID, no help>> so I think there is a primary key already there.

What I was implying was that since Dave had tried excluding RecordID from the list of fields copied that the primary key was probably not RecordID. Unfortunately if there is no primary key that sort of points to a bug in DBISAM since it will be being created as the data is copied and should be unique.

Roy