creating a table with a primary index on iblocal database

Hello All.

This letter might not be totally polite and diplomatic, so let's just start
by stating that delphi and object pascal in general is the best program that
i have seen the last 3 years. Even the best product can have some drawbacks
though....

I'm so mad that i contemplate getting a job at borland, just to help them fix
their -insert here all the four letter words that you have ever heard of-
documentation for delphi client/server/frustrate_the_developer 1.0.

Could someone please post the example, that those [insert your favorite
word in the line of morons,idiots,imbeciles] documentation writers at Borland
didn't put
into the documentation!!! : How to create an interbase table with an unique
index via the ttable method. It's not "Just use the create table mehthod"

I need to create a local interbase table on a database, i have success in
creating the table, but when trying to add a primary index on a field, i get
various error messages :

Addindex('','FIELDNAME',[ixprimary,ixunique])
- error index name must be specified
Addindex('FIELDNAME','FIELDNAME',[ixprimary,ixunique])
- indvalid array of index descriptors

I have tried with the table set to exclusive (otherwise the first try will
say that the table must be exclusive)

I have tried with the table active, and inactive. Same errors.

I have tried with fieldnames in all-uppercase. Same errors.

I have tried to use indexdefs.add and then create table afterwards.
The error message is more or less the same.

I have tried to sit all weekend and watch tour de france. The problem didn't go
away.

It _must_ be possible to creata a primary index on a interbase local table,
other than using execsql on a tquery.

before getting online and posting this, i tried to check indexdefs on a
table that was created allready with a primary index (using sql).

to my surprise, the index name was RDB$PRIMARY10 and the options was [Unique]
the fileldname was 'fieldname' (the name of the field i had a primary index
on).

I guess that the superior mentally powered gurus of hi-tech ,known as the
Borland developers , instantly recognizes the clear indication of a primary
index due to the lack of [ixprimary] in options. Also these magnificent people
know instintively which name to use (e.g) RDB$PRIMARY4 for another table, and
then they probably  eat lunch while toe-tapping in the primary index in whatnot
other interbase specific tables a primary index must be in.

unfortunately other soleless unworthy beings like myself had gotten the stupid
idea that a primary index would have a indexdefs[n].options like, say -
[ixprimary,ixunique].

I guess it's more than unwise to add indexes with names like rdb$primary1
yourself,
so the conclusion must be to add indexes via execsql, when working with
interbase.
Borland database engine does not translate an innocent
addindex (name,fieldname,[ixprimary]) to sql.
It just raises dbi error 10005 or dbi error 10022 depending on just how you
call.

For now, i'll try to use a tquery instead. Anyone who knows how to do it
in the ttable method - please respond. Also if You are positive that it
cannot be done.

After about 4 hrs (message still not send) i finally worked out the way to add
(in sql) a primary key to a table where the field allready exists.

Of course, the sql-referece does not mention this use of the syntax, but what
the heck - when is the last time, that i found the delphi documentation
usefull..?.

Alter table <tablename> add primary key (fieldname)

Simple, isn't it.

The manual should have stated something like

<operation> = {ADD <col_def> | ADD <table constraint> | add <tconstraint_opt>
etc.

The add <tconstraint_opt> was missing, in the sql-reference
So t tried all sorts of weird (but legal) syntax,
getting all sorts of really weird results (also legal),
but not my primary key.

Now, all i have to do is to make a tquery do an execsql.. that's easy..

So.. everything works now. using createtable for creating the table, and sql
for creating the primary key. Anyone knows of a smarter way (there must be
one...)..

Btw - i'm running the code on  my development machine. If i run it on
another computer, the iblocal engine will not even create tables, but that
is a license issue. I hope that this will be reconsidered in borland. Some
people may prefer to ship interbase local with single-user-projects instead
of paradox or dbase tables.

Fant...@image.dk