I have a big problem with a DBISM 3 Table where I have a primary key with
some null values. The table structure is like this:


CREATE TABLE IF NOT EXISTS "DeliveryTypes"
(
  "Level" SMALLINT NOT NULL DEFAULT "1",
  "CountryID" CHARACTER(3) CHARCASE UPPER,
  "ID" CHARACTER(8) NOT NULL CHARCASE UPPER,
  "Description" CHARACTER(80),
PRIMARY KEY ("Level","CountryID","ID") COMPRESS FULL
LANGUAGE "German (Swiss)" SORT "Default Order"
USER MAJOR VERSION 4
);

As you can see the CountryID can be NULL or any other Value. EDB seems to
have a problem with this PK. How can I make a PK in EDB with nullables
fields in it? How can I migrate this table to EDB?

Regards
Rolf

Some additional Infos I did find out so far: The migration tool dosen't
check if any fields are nullable in DBISAM for the primary key. In my case
the migration tool should create a unique constraint instead a primary key
constraint for this situation. This is a show stopper bug, which does
completly hold me on to proceed, as I'm not able to migrate my DBISAM
Tables, as the migration stops imediatly at this table.

Regards
Rolf

"Rolf Frei [Team Elevate]"  schrieb im Newsbeitrag
news:BBAF19C7-E636-4A8B-B76F-EB9990C8594C@news.elevatesoft.com...

I have a big problem with a DBISM 3 Table where I have a primary key with
some null values. The table structure is like this:


CREATE TABLE IF NOT EXISTS "DeliveryTypes"
(
  "Level" SMALLINT NOT NULL DEFAULT "1",
  "CountryID" CHARACTER(3) CHARCASE UPPER,
  "ID" CHARACTER(8) NOT NULL CHARCASE UPPER,
  "Description" CHARACTER(80),
PRIMARY KEY ("Level","CountryID","ID") COMPRESS FULL
LANGUAGE "German (Swiss)" SORT "Default Order"
USER MAJOR VERSION 4
);

As you can see the CountryID can be NULL or any other Value. EDB seems to
have a problem with this PK. How can I make a PK in EDB with nullables
fields in it? How can I migrate this table to EDB?

Regards
Rolf

Rolf


NULLs are not allowed in the primary key. You'll have to put something in there to indicate its unknown.

If its any consolation I don't like it either, but apparently its part of the standard.

Roy Lambert [Team Elevate]

Roy

I know this, and it is fine as this is the same in other DBMS but there is
also asolution there. I need a null value in that field. Placing some other
data in it isn't an option at all. As in other DBMS like Informix, witch I
know very well, this is the same problem, but for this situation we can
create unique constraints, where null fields are allowed. This constraints
behave then the same as Primary keys. EDB does support that as well. I can
create the Table strucutre myself with an uniqe constraint instead a primary
key constraint and after that I am able to import the data from a CSV-File
just fine.

The problem is, that the DBISAM migrator doesn't do it this way, if the
DBISAM PK has null fields in it.

The migrator should do this:
ALTER TABLE "DeliveryTypes" ADD CONSTRAINT "PrimaryKey" UNIQUE ("Level",
"CountryID", "ID")';

instead of this:
ALTER TABLE "DeliveryTypes" ADD CONSTRAINT "PrimaryKey" PRIMARY KEY
("Level", "CountryID", "ID")';

As you can see EDB can already do what I need and what is practice in other
DBMS for this situation, but the migrator fails to do it correctly.

Regards
Rolf

Rolf


Sorry, I should have read the second of your posts before replying.

I can see three options:

1. wait for Tim to alter his code (if he's prepared to)
2. do some preprocessing and post on the table
3. alter the migrator code yourself.

Number 3 might be the best route since you have the source.

For the non-unicode version

edbmigratedbisam3 is the dll that's called. Its source is in the utilities folder (I don't place my stuff in the standard paths so can't say where it will be exactly). For me that's  C:\3rdParty\EDBUtils\libs\edbmigratedbisam3\

edbmigrate is the business end of the stuff and that's in C:\3rdParty\ElevateDB\code\source\

In edbmigrate at line 1056 is

TempSQL:='CONSTRAINT "PrimaryKey" PRIMARY KEY (';

and just below that at line 1065 is

TempSQL:='CONSTRAINT '+QuotedString(TempIndexName,'"')+' UNIQUE (';


copy line 1065 over line 1056, recompile  and that should do the job.


No promises though, and definitely no warranties!

Roy Lambert [Team Elevate]

Roy

Thanks for looking in it, but I did it myself already I know where it is
done but it's not that simple as you think. The UNIQUE constraint should
only come in action if any of the primary keys in DBISAM have nullable
fields. For normal cases it should of course generate a primary key
constraint. The problem is that it is not visible in the
FIndexDefs[I].Options if this is the case as any primary key in DBSIAM 3 has
ixUnique and ixPrimary set. As such the migrator can't see if it must do a
UNIQUE constraint or PK constraint. To solve this problem there must be done
something more complex with checking the fileds definition.

Hopefully Tim will fix this for the next version. At the moment I have done
some workarounds to get my data into EDB2. On the final application I will
do the migration myself anyway, as there will be new fields, RI, Triggers
and all the nice things. So I will then create a script which I will load
and after that I will copy the data with my own delphi code.

Regards
Rolf

"Roy Lambert"  schrieb im Newsbeitrag
news:0A03E360-CB68-414E-8B95-500981AFA20C@news.elevatesoft.com...

Rolf


Sorry, I should have read the second of your posts before replying.

I can see three options:

1. wait for Tim to alter his code (if he's prepared to)
2. do some preprocessing and post on the table
3. alter the migrator code yourself.

Number 3 might be the best route since you have the source.

For the non-unicode version

edbmigratedbisam3 is the dll that's called. Its source is in the utilities
folder (I don't place my stuff in the standard paths so can't say where it
will be exactly). For me that's
C:\3rdParty\EDBUtils\libs\edbmigratedbisam3\

edbmigrate is the business end of the stuff and that's in
C:\3rdParty\ElevateDB\code\source\

In edbmigrate at line 1056 is

TempSQL:='CONSTRAINT "PrimaryKey" PRIMARY KEY (';

and just below that at line 1065 is

TempSQL:='CONSTRAINT '+QuotedString(TempIndexName,'"')+' UNIQUE (';


copy line 1065 over line 1056, recompile  and that should do the job.


No promises though, and definitely no warranties!

Roy Lambert [Team Elevate]

Rolf

>Thanks for looking in it, but I did it myself already I know where it is
>done but it's not that simple as you think. The UNIQUE constraint should
>only come in action if any of the primary keys in DBISAM have nullable
>fields. For normal cases it should of course generate a primary key
>constraint. The problem is that it is not visible in the
>FIndexDefs[I].Options if this is the case as any primary key in DBSIAM 3 has
>ixUnique and ixPrimary set. As such the migrator can't see if it must do a
>UNIQUE constraint or PK constraint. To solve this problem there must be done
>something more complex with checking the fileds definition.

Fully agree. My suggestion was just a quick fix so you could at least get the data across.


>Hopefully Tim will fix this for the next version. At the moment I have done
>some workarounds to get my data into EDB2. On the final application I will
>do the migration myself anyway, as there will be new fields, RI, Triggers
>and all the nice things. So I will then create a script which I will load
>and after that I will copy the data with my own delphi code.

When I migrated my major app I did a lot of preprocessing using DBISAM before doing the migrate.  I did it that way round because I was far more confident with DBISAM rather than ElevateDB at that stage.

Roy Lambert [Team Elevate]

Rolf,

<< As you can see the CountryID can be NULL or any other Value. EDB seems to
have a problem with this PK. How can I make a PK in EDB with nullables
fields in it? How can I migrate this table to EDB? >>

If the issue is with VARCHAR/CHAR columns being empty (''), then you can use
the BlankNullStrings parameter to have the migrator use empty strings
instead of NULLs during the migration:

MIGRATE DATABASE FROM "DBISAM3"
USING
"BlankNullStrings" = TRUE,
"DatabaseDirectory" = 'c:\migratetest',
WITH DATA

If the issue is with other data types being NULL, then I'll have to see
about making an adjustment in the migrator for this.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

There are string and not string fields. "BlankNullStrings" will not help at
all, as this will break existing code, where it is important, that this
fields are NULL. So I think it would be the correct way to change the
migrator to produce UNIQUE KEY instead PRIMARY KEY in this sitation where
null fields are part of the primary key. Also if I'm not be able to use EDB
2 at all.

Regards
Rolf

"Tim Young [Elevate Software]"  schrieb im Newsbeitrag
news:D0465997-92A3-4606-8FC3-913FC749D394@news.elevatesoft.com...

Rolf,

<< As you can see the CountryID can be NULL or any other Value. EDB seems to
have a problem with this PK. How can I make a PK in EDB with nullables
fields in it? How can I migrate this table to EDB? >>

If the issue is with VARCHAR/CHAR columns being empty (''), then you can use
the BlankNullStrings parameter to have the migrator use empty strings
instead of NULLs during the migration:

MIGRATE DATABASE FROM "DBISAM3"
USING
"BlankNullStrings" = TRUE,
"DatabaseDirectory" = 'c:\migratetest',
WITH DATA

If the issue is with other data types being NULL, then I'll have to see
about making an adjustment in the migrator for this.

--
Tim Young
Elevate Software
www.elevatesoft.com

Rolf,

<< There are string and not string fields. "BlankNullStrings" will not help
at all, as this will break existing code, where it is important, that this
fields are NULL. So I think it would be the correct way to change the
migrator to produce UNIQUE KEY instead PRIMARY KEY in this sitation where
null fields are part of the primary key. Also if I'm not be able to use EDB
2 at all.  >>

I'll have to think about this some more.  I'm not sure how feasible it is to
scan entire incoming tables for NULL values, especially if those sources are
not DBISAM or other sources that I know for a fact can handle such scans
quickly.  Such a scan *must* be done prior to the creation of *any* of the
target tables, because it possibly affects the DDL for all tables (FKs).

--
Tim Young
Elevate Software
www.elevatesoft.com