Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 11 total
Thread Finding the name of the primary key field(s)
Wed, Sep 30 2009 3:47 AMPermanent 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 AMPermanent Link

Steve Forbes

Team Elevate 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 AMPermanent 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 AMPermanent 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 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate 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 AMPermanent 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 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate 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 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email 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 PMPermanent 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 PMPermanent 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 2Next Page »
Jump to Page:  1 2
Image