Board index » delphi » New Apollo 4.0 user and have some questions

New Apollo 4.0 user and have some questions

I'm trying to create a unique index on a field.   The unique index is
created, but does not prevent a user from entering a value that is already
in the table with out an error.  I was expecting a key violation, but what
happens is that the duplicate value is accepted but is not within the index
"result set".   I don't want this to happen.  I'd like a key violation to
happen, so if the user enters a value in a uniquely indexed field (I can
trap it), or programmatically increment the number (as explained below) or
explain to the user.

    I'm pretty use to Paradox, but got tired of the index out of date
problems, and the forced mapping of an exact path for every user in a multi
user environment.  So I'm trying Apollo.   So the second question is:  Is
there a way to emulate the AutoInc field like Paradox within Apollo?  I was
thinking of using a Unique index and go to last record, get the value,
increment it by one and then post it, but I can see I'm going to have
problems with that as well (problem number one is the problem above, the
unique index does not raise a key violation to prevent duplicate ID's).

Any help would be greatly appreciated!

Curt
CKrue...@cts.com

 

Re:New Apollo 4.0 user and have some questions


In article <877583298.192...@wagasa.cts.com>,
  "Curt Krueger" <CKrue...@CTS.COM> wrote:

Quote
> I'm trying to create a unique index on a field.   The unique index is
> created, but does not prevent a user from entering a value that is already
> in the table with out an error.  I was expecting a key violation, but what
> happens is that the duplicate value is accepted but is not within the index
> "result set".   I don't want this to happen.  I'd like a key violation to
> happen, so if the user enters a value in a uniquely indexed field (I can
> trap it), or programmatically increment the number (as explained below) or
> explain to the user.

>     I'm pretty use to Paradox, but got tired of the index out of date
> problems, and the forced mapping of an exact path for every user in a multi
> user environment.  So I'm trying Apollo.

The problem is not in Apollo, but in dBASE - this behavior is natural to
any xBASE system - no key violations! The actual solution is to check the
presence of the key BEFORE posting a user-entered record. Either have two
TTables mapped to the same physical table and have the code like that:
if not Table2.FindKey[UserEnteredKey]  then {post record}  else raise
Exception.Create('Key violation!'); or have one, save current position
(using TTable.GetBookMark or Apollo.RecNo), check for the presence of the
key, and if the key is not found, return to the previous position.

Quote
>So the second question is:  Is
> there a way to emulate the AutoInc field like Paradox within Apollo?

You already got one - in xBASE physical record number never changes
(provided you don't pack the table). This is not a good was though.

Quote
>I was
> thinking of using a Unique index and go to last record, get the value,
> increment it by one and then post it

Might work

Quote
> but I can see I'm going to have
> problems with that as well (problem number one is the problem above, the
> unique index does not raise a key violation to prevent duplicate ID's).

Do it yourself as described above

Quote
> Any help would be greatly appreciated!

Also try my SKApollo from
http://www.geocities.com/Sunsetstrip/Stage/7431/delphi_apollo_skapoll...

It might make things easier a little bit.
I would think about posting a new version that will handle key violations.
Sincerely,

Syarzhuk Kazachehnka

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet

Re:New Apollo 4.0 user and have some questions


Quote
"Curt Krueger" <CKrue...@CTS.COM> wrote:

>I'm trying to create a unique index on a field.   The unique index is
>created, but does not prevent a user from entering a value that is already
>in the table with out an error.  I was expecting a key violation, but what
>happens is that the duplicate value is accepted but is not within the index
>"result set".   I don't want this to happen.  I'd like a key violation to
>happen, so if the user enters a value in a uniquely indexed field (I can
>trap it), or programmatically increment the number (as explained below) or
>explain to the user.

>    I'm pretty use to Paradox, but got tired of the index out of date
>problems, and the forced mapping of an exact path for every user in a multi
>user environment.  So I'm trying Apollo.   So the second question is:  Is
>there a way to emulate the AutoInc field like Paradox within Apollo?  I was
>thinking of using a Unique index and go to last record, get the value,
>increment it by one and then post it, but I can see I'm going to have
>problems with that as well (problem number one is the problem above, the
>unique index does not raise a key violation to prevent duplicate ID's).

>Any help would be greatly appreciated!

>Curt
>CKrue...@cts.com

This is standard Xbase behaviour. Xbase does not enforce unique
indices. Keyed searches or retrieves simply return the first record
with that key.
---------------------

remove no_spam_ on reply

Re:New Apollo 4.0 user and have some questions


Quote
>   "Curt Krueger" <CKrue...@CTS.COM> wrote:

> >So the second question is:  Is
> > there a way to emulate the AutoInc field like Paradox within Apollo?

Yes.  With SQL its fairly easy..

Quote
> >I was
> > thinking of using a Unique index and go to last record, get the value,
> > increment it by one and then post it
> > but I can see I'm going to have
> > problems with that as well (problem number one is the problem above,
the
> > unique index does not raise a key violation to prevent duplicate ID's).

True.  I imagine there might be other reasons why this would be unfavorable
as you might develop an incorrect value.

What I would do is use a query to generate the number for you.  This way it
works for non-indexed tables as well (or non-indexed fields).

The following SQL statement will find the next number in a table where the
column you are attempting this on is named "UNIQUE_NUM"...and the table is
named "TABLENAME"...

"SELECT MAX(UNIQUE_NUM) + 1 AS NEXT_NUM FROM TABLENAME"

If you open the query, then seek the return value "NEXT_NUM", you can
programmatically create your "auto-inc" field feature.  The following
ObjectPascal code would seek the value the query generated..

i := Query1.FieldByName('NEXT_NUM').Value;  (where i is an integer type)

I use this routine to generate the next available number, rather than rely
on Paradox's AutoInc field to do the job for me for several key reasons one
of which being..

If you delete a record that has an AutoInc field in it, that number can't
be used for another record.  If this field was used for your Invoice
number, your client(s) might think there is data missing somewhere since
there's a gap in the numbers where you deleted a record.

============================
Delphi Awareness Network
http://www.utech.net/users/40761
obj...@utech.net
============================

Re:New Apollo 4.0 user and have some questions


Quote
"ObjectPAL" <obj...@utech.net> wrote:
>What I would do is use a query to generate the number for you.  This way it
>works for non-indexed tables as well (or non-indexed fields).

>The following SQL statement will find the next number in a table where the
>column you are attempting this on is named "UNIQUE_NUM"...and the table is
>named "TABLENAME"...

>"SELECT MAX(UNIQUE_NUM) + 1 AS NEXT_NUM FROM TABLENAME"

>If you open the query, then seek the return value "NEXT_NUM", you can
>programmatically create your "auto-inc" field feature.  The following
>ObjectPascal code would seek the value the query generated..

>i := Query1.FieldByName('NEXT_NUM').Value;  (where i is an integer type)

>I use this routine to generate the next available number, rather than rely
>on Paradox's AutoInc field to do the job for me for several key reasons one
>of which being..

>If you delete a record that has an AutoInc field in it, that number can't
>be used for another record.  If this field was used for your Invoice
>number, your client(s) might think there is data missing somewhere since
>there's a gap in the numbers where you deleted a record.

You really should not reuse unique id values in any circumstances
whatsoever.

If your unique id was a foreign key (such as the link into invoice
items) and there were detail records linked to the deleted unique id
fields a new master record using the deleted value would now refer to
the deleted record's details.

In the invoice example you are describing, since an audit trail needs
to be kept in an accounting system, the deleted records need to  be
stored elsewhere or filtered out by queries. Even if this is done
those unique ids are still part of the system. Consistency should be
maintained throughout.

If you were the owner of a business you wouldn't wanted cancelled
entries to be deleted completely. That is like ripping pages out of a
manual bookkeeping system.
---------------------

remove no_spam_ on reply

Other Threads