GUID default value

[Vladimir]

Greetings


We are developing a complex system, which will run on Oracle, MS SQL
Server and MS Access databases. All the databases are to be replicated.

We are using a single common database scheme. For each table we have a
GUID column, which is filled once a new record is inserted. We have
default values for the GUID column in Oracle DB and MS SQL.


Could you please tell me which is the correct column data type for GUID

in MS Access and how can we set a default value for the column?


Here is a sample for MS SQL Server


create table PRODUCT_SRC (
ID int identity,
CR_OPE_ID int null default
dbo.GET_CURRENT_USER_ID(),
OPE_ID int null default
dbo.GET_CURRENT_USER_ID(),
CH_DATE datetime not null default
GETDATE(),
CR_DATE datetime not null default
GETDATE(),
STATUS char(1) not null default 'A',
GUID uniqueidentifier not null default newid(),
NAME varchar(100) not null,
constraint PK_PRODUCT_SRC primary key (ID)
)
go


and Oracle


create table PRODUCT_SRC (
ID INTEGER not null,
CR_OPE_ID INTEGER,
OPE_ID INTEGER,
CH_DATE DATE default SYSDATE

not null,
CR_DATE DATE default SYSDATE

not null ,
STATUS CHAR(1) default 'A' not
null,
GUID RAW(32) default
sys_guid(),
NAME VARCHAR2(100) not null,
constraint PK_PRODUCT_SRC primary key (ID)
);


Thanks, Vladimir.

[David W. Fenton]

Post by Vladimir
We are developing a complex system, which will run on Oracle, MS
SQL Server and MS Access databases. All the databases are to be
replicated.
We are using a single common database scheme. For each table we
have a GUID column, which is filled once a new record is
inserted. We have default values for the GUID column in Oracle DB
and MS SQL.
Could you please tell me which is the correct column data type
for GUID
in MS Access and how can we set a default value for the column?

There is only one kind of GUID field in Jet, and that is an
Autonumber GUID (called a "Replication ID").

I don't use Jet DDL since it's not as full-featured as DAO, so I
can't give you the Jet DDL to create an Autonumber field with
Replication ID as the Field Size.

I would point out that you are likely to have massive issues if you
try to use Access to connect to Jet tables with GUIDs in them, as
VBA and Access have major conversion problems with GUIDs, and the
default Access replication conflict resolver can't deal with them.
This iarticle outlines the basic problems:

Replication and GUIDs, the Good, the Bad, and the Ugly
http://trigeminal.com/usenet/usenet011.asp?1033

I have always completely avoided GUIDs in Access because of these
problems.

If, however, you are using pure Jet and not using VBA or Access at
all, the problems are much more manageable (though you're still
going to have to surmount the issues involved with whatever data
access interface you're using, ADO, DAO or ODBC).

[Vladimir]

Hi David

Thanks for you answer.

Actually, we are not using Access, just pure Jet engine.
Unfortunately, I can not avoid GUIDs since there are going to be about
10 separate databases and the replication logic is quite complex.
I have been able to create "Replication ID" fields, but I couldn't make
defaults for them. So, I guess, we will have to generate GUIDs in the
application.

---

Sincerely, Vladimir.

[David W. Fenton]

Post by Vladimir
Actually, we are not using Access, just pure Jet engine.
Unfortunately, I can not avoid GUIDs since there are going to be
about 10 separate databases and the replication logic is quite
complex. I have been able to create "Replication ID" fields, but I
couldn't make defaults for them. So, I guess, we will have to
generate GUIDs in the application.

What kind of default do you want? A Jet Replication ID is a form of
AutoNumber, so there *can't* be a default value.

And, unless you're talking about millions of replicas in each of 10
replicas, then random autonumber alone should be sufficient to
handle your unique identifier needs. I understand that you're
needing to maintain schema compatibility across 3 database engines,
but my point is that you may not need a GUID for success at all. I
have had Access replicated apps running since 1997 that have never
produced a PK collision using random autonumbers.

Just looking again at your DDL for the other schemas, you're using
built-in functions in SQL Server and Oracle. Jet's Replication ID
already generates unique GUIDs by default. Have you checked what it
does create by default?

Keep in mind that all Jet Autonumbers are really just a special form
of default value, one that you do *not* define with the standard
DefaultValue property of the field. So maybe you don't *need* to
define a default value, because that is taken care of just by
creating an AutoNumber of type Replication ID.

[Vladimir]

Hi David.

Thank you for your answer.

As I have said earlier, I've created a table with a "Replication ID"
field and I've inserted a couple of rows into the table. The
"Replication ID" field remained empty, although, I'd expect it to have
some value generated (a brand new GUID).

Anyway, we have encountered quite some limitations with mdb, such as
number of indexes, inability to execute triggers (this appeared to be
vital), default GUID issue, etc.

So we consider switching to MSDE 2000 or SQL 2005 Express.

Anyway, thanks for your help.

---

Sincerely, Vladimir,
MCSD.NET

[David W. Fenton]

Post by Vladimir
As I have said earlier, I've created a table with a "Replication
ID" field and I've inserted a couple of rows into the table. The
"Replication ID" field remained empty, although, I'd expect it to
have some value generated (a brand new GUID).

Something is wrong, then. I just tested. I created a two-column
table, with a GUID PK and a test field. As soon as I type into the
test field, the GUID is created.

Post by Vladimir
Anyway, we have encountered quite some limitations with mdb, such
as number of indexes, inability to execute triggers (this
appeared to be vital), default GUID issue, etc.

If you're running up against the limit on number of indexes, then
sounds like you've got a very denormalized table structure. Perhaps
you could get by with ADO temporary indexes?

As to triggers, expecting Jet to have them is a product of
ignorance. It has never had them and never will, and there are very
good reasons for that, because there is no server-side process
handling the interface between connections and the data file. You
couldn't possibly expect Jet to provide triggers if you considered
what it is.

I don't see that there *is* a problem with GUID default values. You
must be doing something wrong.

Post by Vladimir
So we consider switching to MSDE 2000 or SQL 2005 Express.

That's just SQL Server with a small number of features removed and
throttled at 5 simultaneous connections, so it would be very easy
to do so. But if what you're trying to implement does not manage
its connections frugally or really needs to support lots of users,
you could easily run into real bottlenecks because of the
limitation of 5 simultaneous active connections.

And setup and installation of MSDE is quite complex in comparison
to Jet.

[Vladimir]

Post by David W. Fenton
Something is wrong, then. I just tested. I created a two-column
table, with a GUID PK and a test field. As soon as I type into the
test field, the GUID is created

Yes, I've tried it again, using MS Access "create table" constuctor.
I guess, we have difficulties creating required fields through ODBC,
from our database modelling tool.

Post by David W. Fenton
If you're running up against the limit on number of indexes, then
sounds like you've got a very denormalized table structure. Perhaps
you could get by with ADO temporary indexes?

On the contrary, we have highly normalized structure.
With a lot of foreign keys, and key indexes.

Post by David W. Fenton
As to triggers, expecting Jet to have them is a product of
ignorance. It has never had them and never will, and there are very
good reasons for that, because there is no server-side process
handling the interface between connections and the data file. You
couldn't possibly expect Jet to provide triggers if you considered
what it is.

Yes, I knew jet doesn't support them. We thought we would be able to
overcome this limitation. But, it appeared, that triggers are not the
only problem.
So, it's more productive for us to switch for a more functional DBMS,
rather than
to rewrite a big part of our application to make it support jet.

Post by David W. Fenton
That's just SQL Server with a small number of features removed and
throttled at 5 simultaneous connections, so it would be very easy
to do so. But if what you're trying to implement does not manage
its connections frugally or really needs to support lots of users,
you could easily run into real bottlenecks because of the
limitation of 5 simultaneous active connections.

We suppose to utilize it as a single-user-dbms.

Post by David W. Fenton
And setup and installation of MSDE is quite complex in comparison
to Jet.

That's true. But we are going to gain alot from SQL 2005 Express, for
the
application will have to support two DBMS engines (Oracle,
MS SQL 2000 - MS SQL 2005 Exp) instead of three.

Thanks for you answers,
Sincerely, Vladimir

[David W. Fenton]

Post by Vladimir

Post by David W. Fenton
Something is wrong, then. I just tested. I created a two-column
table, with a GUID PK and a test field. As soon as I type into

the

Post by Vladimir

Post by David W. Fenton
test field, the GUID is created

Yes, I've tried it again, using MS Access "create table"
constuctor. I guess, we have difficulties creating required

fields

Post by Vladimir
through ODBC, from our database modelling tool.

Are you using the right DDL? I don't use DDL because I prefer DAO
(which knows more about the Jet object model), but ODBC doesn't
make
DAO available to you.

Have you tried manually creating the table in Access and seeing if
the results are what you need? If so, then it's just a matter of
figuring out what the Jet DDL is to create that table. It obviously
exists -- it has to, or Access couldn't do it.

Post by Vladimir

Post by David W. Fenton
If you're running up against the limit on number of indexes, then
sounds like you've got a very denormalized table structure.
Perhaps you could get by with ADO temporary indexes?

On the contrary, we have highly normalized structure.
With a lot of foreign keys, and key indexes.

Sounds like an awfully wide table if it needs more than 32 indexes,
and a candidate for some form of decomposition.

I guess since you're using ODBC, ADO's temporary indexes are not
available to you.

But they can be replicated with Jet transactions. I don't know,
though, if ODBC to Jet supports transactions.

Post by Vladimir

Post by David W. Fenton
As to triggers, expecting Jet to have them is a product of
ignorance. It has never had them and never will, and there are
very good reasons for that, because there is no server-side
process handling the interface between connections and the data
file. You couldn't possibly expect Jet to provide triggers if you
considered what it is.

Yes, I knew jet doesn't support them. We thought we would be able
to overcome this limitation. But, it appeared, that triggers are
not the only problem.
So, it's more productive for us to switch for a more functional
DBMS, rather than
to rewrite a big part of our application to make it support jet.

Well, it's too bad you need triggers and can't abstract that
functionality into a layer that is only used with a Jet back end.

Post by Vladimir

Post by David W. Fenton
That's just SQL Server with a small number of features removed

and

Post by Vladimir

Post by David W. Fenton
throttled at 5 simultaneous connections, so it would be very easy
to do so. But if what you're trying to implement does not manage
its connections frugally or really needs to support lots of

users,

Post by Vladimir

Post by David W. Fenton
you could easily run into real bottlenecks because of the
limitation of 5 simultaneous active connections.

We suppose to utilize it as a single-user-dbms.

Post by David W. Fenton
And setup and installation of MSDE is quite complex in comparison
to Jet.

That's true. But we are going to gain alot from SQL 2005 Express,
for the
application will have to support two DBMS engines (Oracle,
MS SQL 2000 - MS SQL 2005 Exp) instead of three.

Well, good luck. Many of my clients wouldn't want an application
that added the overhead of MSDE/SQL Server.