Does autoinc work or doesn't it? 1

[FrankThynne]

I'm using Delphi 6 BDE queries. I hear rumours that autoinc "doesn't work" with Delphi, and I am not succeeding with them either, despite using AutoRefresh=True and AutoGenerateValue=arAutoInc. In particular I can't retrieve the value of an autoinc field after adding a row.

The problem seems to arise only when the query requires cached updates because of joined tables (I'm only updating one). Simpler queries don't show the problem.

The rumours suggest a long-standing problem, and the documentation about autorefresh and argeneratevalue suggests that Borland has tried to address it - but have they succeeded yet?

 

[svanels]

Auto increment fields are problematic, especially in a network environment. The client-server databases use an internal generator to address this issue and generate unique numbers (keyfields). In the Mastapp demo Borland shows a work-around for generating unique order numbers S. van Els
SAvanEls@cq-link.sr

 

[FrankThynne]

Thanks, S. van Els, for such a quick response - much better than I've had from other forums and newsgroups.

Your response strengthens the view that autoinc just "doesn't work" and it's a pity that the documentation doesn't say so. I should have paid more attention to the rumours!

I had already devised a work-round (unaware of MastApp's approach) which is similar to MastApp's but in which the app exclusively locks the table providing the keys while it gets a key value. This seemed easier than arranging transaction processing to provide a rollback in case two users simultaneously retrieved the same value. I don't think I've created a "deadly embrace".

The keys table has a row for each auto-key field in each table and automatically generates a starting key from the existing data in the table if it needs to.

I just feel that I shouldn't have needed to do all this!

 

[svanels]

Well the idea of locking the key table is very valid, but how does it behave in a true network environment? Have you tested it?
If you are working with Client-Server databases like Oracle, Interbase etc, they have the mechanisms of creating generators and triggers that provide the unique numbering required in key fields. So the Databse take care of generating the keyfields.
The key table approach is good for access and paradox, the so called desktop databases.

Best Regards S. van Els
SAvanEls@cq-link.sr

 

[Guest_imported]

You can create a stored procedure to insert records with an autoincrement field, then use the @@identity variable and an output parameter in the stored procedure to register the autoincrement field.

CREATE PROCEDURE [dbo].[spLoteMGen]
(@CurTalla char(2),
@HojaMuestraID int,
@CurLote int OUTPUT,
@NroMuestra int OUTPUT,
@TelaBase varchar(50),
@ConsMts float,
@ConsKgs float,
@AnchoTend float)
AS
BEGIN

insert into loteprod (TipoLoteProd, Fecha, cantidad, Unidad)
values ('MU', getdate(), 1, 'PR')
select @CurLote = @@Identity
....

in your delphi program you use

var
CurLote: integer


spLoteMGen.parambyname('@AnchoTend').asfloat:=rzDBAncho.Value;
spLoteMGen.parambyname('@TelaBase').asstring:=wwDBTela.field.asstring;
spLoteMGen.execproc;
CurLote:=spLoteMGen.parambyname('@CurLote').asinteger;

 

[svanels]

Is this SQL-server sintax or Oracle? Can you do this in Access or Paradox? S. van Els
SAvanEls@cq-link.sr

 

[FrankThynne]

Regarding testing, S van Els - no, not yet! I'm trying to anticipate multi-user needs. At this stage I'm working on a prototype and using the BDE seemed to be the quickest and easiest way. With hindsight, I think I was mistaken!

In the production version I expect to use a client-server approach. Your comments, prodillo may well prove useful. Meanwhile, I have tried (with difficulty!) to keep the untidy bits of db interfaces encapsulated.

The failure of the BDE to support a reliable serial key system for the prototype has not helped! It means that all programs using the same db have to understand how my key generation works and include my code or its equivalent. (Yes, I know it's the same as autoinc, but other programmers shouldn't need to know that!)

 

[svanels]

Frank I started out exactly like you and using paradox to play client-server, but then I tried Interbase and sticked with it. Paradox I use only for small single user applications. For a Network environment I use Interbase. If you are serious thinking about networking, check out Interbase, a lot of headaches like security, key generation and restricting access are handled gracefully by the database server. S. van Els
SAvanEls@cq-link.sr

 

[FrankThynne]

Once again S. vel Els your comments are very pertinent. I'm trying to work in an existing Paradox environment with just file sharing, and expect to migrate everything to something better later. So, when you see me again in this forum, please don't think I haven't heeded your advice! I have to live with the existing arrangements for the time being.