Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 10 of 11 total |
Finding the name of the primary key field(s) |
Wed, Sep 30 2009 3:47 AM | Permanent Link |
Peter | Hello
I am migrating >160 tables to EDB, and I need to clean up any records that have a NULL value in a primary key before I migrate the database. I have to do this at hundreds of sites, so it isn't a matter of checking them all in DBSys. I thought of using a TDBISAMTable to do the job - assuming that Fields[0] is always the primary key, checking the first record for NULL, deleting that record, then move on to the next table. Is there a better way? Could it be done in SQL perhaps? Regards & TIA Peter |
Wed, Sep 30 2009 4:13 AM | Permanent Link |
Steve Forbes Team Elevate | Hi Peter,
It would be easy if your primary keys all have the same name i.e. I call all mine "Id" regardless of table. You could then cycle through the tables issuing the following SQL .. 'DELETE FROM ' + TableName + ' WHERE Id IS NULL'; or, you could fill a 2D array with the Table name and PrimaryKey name for I := 0 to Pred(Array) do begin SQL := 'DELETE FROM ' + Array[I][0] + ' WHERE ' + Array[I][1] + ' IS NULL'; ExecSQL; end; All from the top-of-my-head so may not be 100% correct, but hopefully you get the picture. Good luck Steve "Peter" <peter@petercrain.com> wrote in message news:C0499DFD-DEE7-425E-A14C-D8B462AA64CF@news.elevatesoft.com... > Hello > > I am migrating >160 tables to EDB, and I need to clean up any records that > have a NULL value in a primary key before I migrate the database. I > have to do this at hundreds of sites, so it isn't a matter of checking > them all in DBSys. I thought of using a TDBISAMTable to do the job - > assuming > that Fields[0] is always the primary key, checking the first record for > NULL, deleting that record, then move on to the next table. > > Is there a better way? Could it be done in SQL perhaps? > > Regards & TIA > > Peter > |
Wed, Sep 30 2009 4:23 AM | Permanent Link |
Peter | Steve
Unfortunately the field names do not follow any convention, having been passed on from a Clipper system, then Paradox, the DBISAM. I have a list of the table names - I suppose I could also make a list of all the PK field names too. Thanks for the input. Regards Peter |
Wed, Sep 30 2009 6:42 AM | Permanent Link |
"Iztok Lajovic" | Peter,
try with something like the code below and modify it to reach your needs: procedure Tdiagr.bTestClick(Sender: TObject); var i,j: integer; names: string; begin names := ''; for j := 0 to myDatabase.DataSetCount - 1 do begin with TDBISAMTable(myDatabase.DataSets[j]) do begin IndexDefs.Update; for i := 0 to indexDefs.Count - 1 do begin if ixPrimary in indexDefs[i].Options then names := names + #13 + 'tableName: ' + quotedStr(name) + ', indexName: ' + quotedStr(indexDefs[i].name) + ', indexFields: ' + quotedStr(indexDefs[i].Fields); end; end; end; showMessage('primary indexes of the database are:' + #13 + names); end; Regards Iztok Lajovic "Peter" <peter@petercrain.com> je napisal v sporočilo news:C0499DFD-DEE7-425E-A14C-D8B462AA64CF@news.elevatesoft.com ... > Hello > > I am migrating >160 tables to EDB, and I need to clean up any records that > have a NULL value in a primary key before I migrate the database. I > have to do this at hundreds of sites, so it isn't a matter of checking > them all in DBSys. I thought of using a TDBISAMTable to do the job - > assuming > that Fields[0] is always the primary key, checking the first record for > NULL, deleting that record, then move on to the next table. > > Is there a better way? Could it be done in SQL perhaps? > > Regards & TIA > > Peter > |
Wed, Sep 30 2009 7:09 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Peter
Since all you'll be doing for each table is possibly removing the first record I think using a table and testing for fields[0] being null is going to be as good as anything else. Roy Lambert |
Wed, Sep 30 2009 7:58 AM | Permanent Link |
Peter | Roy
Yes, the other ideas have merit, but I guess I am safe in assuming that if the first record has NULL in Fields[0] then I should remove that record before the migration. Thanks to all. Should I start another thread on why ALTER TABLE "_Fee Bak" ADD FeeID AUTOINC AT 0; doesn't show the new FeeID column in the first position in DBSys? DBISAM 3, using D7. Peter |
Wed, Sep 30 2009 9:24 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Peter
Before you do clear the crap out of the .ini file and see if that fixes it. Roy Lambert [Team Elevate] |
Wed, Sep 30 2009 12:53 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Peter,
<< I am migrating >160 tables to EDB, and I need to clean up any records that have a NULL value in a primary key before I migrate the database. >> What type of column is NULL in the primary key ? If it is a VARCHAR column, then the DBISAM migrator for EDB can set that to a blank ('') string during the migration if you set the BlankNullStrings parameter to True. MIGRATE DATABASE FROM "DBISAM3" USING "BlankNullStrings" = TRUE, "Collation" = 'ANSI', "DatabaseName" = 'c:\sourcedata', "TablePasswords" = '', WITH DATA -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Sep 30 2009 8:01 PM | Permanent Link |
Peter | Tim
Almost all of the tables use an integer field for the primary field, so I am using the if Table1.Fields[0].Value = NULL then... idea. Thanks Peter |
Wed, Sep 30 2009 8:03 PM | Permanent Link |
Peter | Roy
I did that - even deleted the INI file, all to no avail. However the statement ALTER TABLE "_Fee" ADD FeeID AUTOINC AT 1; ...had the desired result. Who would have thought... Regards Peter |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Friday, March 29, 2024 at 03:30 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |