Board index » delphi » Generator Problem

Generator Problem


2003-11-24 04:35:00 PM
delphi177
Hi all,
I am using delphi 7, ibx 7.08, interbase 6.2
My database has got 50 tables and 50 triggers.
My primary keys are based on generator/id mechanism.
I have some problems about generator.
Sometimes i get Primary key error.
Because generator's values is decreasing.
Example
Table's id field value is 6125
Generator's value is 6000.
So database gives Key violation error.
But i don't decrease generator's value.
What happen.
I cannot understand anything.
What is the problem.
--
ibrahim Bulut
Software/Database Developer
Republic Of Turkey
 
 

Re:Generator Problem

ibrahim bulut writes:
Quote
I am using delphi 7, ibx 7.08, interbase 6.2
Multi-posted; answered in borland.public.interbase.general. Please do
not multi-post.
-Craig
--
Craig Stuntz [TeamB] . Vertex Systems Corp. . Columbus, OH
Delphi/InterBase Weblog : delphi.weblogs.com
InterBase in Multi-tier World -- Use multiple RDMs, IB features in
your appserver: delphi.weblogs.com/stories/storyReader$195
 

Re:Generator Problem

Hi,
We have a strange problem in reading generator values.
Normally when inserting into a Customer table we are retrieving the id value
by:
DataModule2.SQQ_1.Active := false;
DataModule2.SQQ_1.Active := true;
i_id := DataModule2.SQQ_1GEN_ID.AsInteger; // retrieves the
generator value
DataModule2.SDC_PE_ASIAKASASIAKAS_ID.Value := i_id; // places the
value to the id-field;
SQQ_1 is a TSQLQuery component and the command text it contains is:
'select gen_id(ASIAKAS_ID_GEN,1) from rdb$database' .
Oddly, in some machines this does not work. It retrieves nothing, and apply
updates gives (naturally) an error. The problem machines have been newly
installed XP professionals. It works though in many XP professional
machines.
Interbase console retrieves the values flawlessly even if the application
does not work.
Application is developed with D6.
Pekka Paunio
 

Re:Generator Problem

Pekka Paunio writes:
Quote

Normally when inserting into a Customer table we are retrieving the
id value by:

DataModule2.SQQ_1.Active := false;
DataModule2.SQQ_1.Active := true;
i_id := DataModule2.SQQ_1GEN_ID.AsInteger; // retrieves the
generator value
DataModule2.SDC_PE_ASIAKASASIAKAS_ID.Value := i_id; // places
the value to the id-field;

SQQ_1 is a TSQLQuery component and the command text it contains is:

'select gen_id(ASIAKAS_ID_GEN,1) from rdb$database' .

Oddly, in some machines this does not work. It retrieves nothing,
If this is not working, then you should be getting an exception of some
sort. Do you have this code surrounded by a try/except block? If so, you
should be able to set a breakpoint in the except block, or reraise the
exception or show some other message so you can see it.
--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: www.logicfundamentals.com/RADBooks.html
It used to be that other people's achievements were considered an
inspiration, not a grievance.
 

Re:Generator Problem

Are you sure the users that have the problem have rights to
RDB$DATABASE? When you test with IBConsole you are logged in as sysdba.
--
Bill Todd (TeamB)
 

Re:Generator Problem

Bill Todd writes:
Quote
Are you sure the users that have the problem have rights to
RDB$DATABASE? When you test with IBConsole you are logged in as sysdba.

That might be the answer. How do you grant/check rights to rdb$database.
EMS for example don't show system tables in its grant manager.
-Pekka
 

Re:Generator Problem

Use GRANT and REVOKE just as you would for any other table. See the
blindmeta.sql, readmeta.sql and writemeta.sql scripts in the InterBase
examples\security directory.
--
Bill Todd (TeamB)
 

Re:Generator Problem

Bill Todd writes:
Quote
Use GRANT and REVOKE just as you would for any other table. See the
blindmeta.sql, readmeta.sql and writemeta.sql scripts in the InterBase
examples\security directory.

I tried to grant the rights with 'Grant select, insert, uppdate, delete
on rdb$database to WFC, WFC_1, WFC_2 ' .
It does not solve the problem though. An additional remark is that the
call to the generator actually increases the generator value but does
not return the value to the application.
The actual call for the value does not raise an exception but the insert
does. This has following behaviour, if the generator returns a value 21X
(for example 217) it complains that '21= is not a valid floating point
value), if the generator value is 22x it complains about '22=' and so
on. Some times there is '?' instead of '=';
The SQl query components field definition says that the field 'GEN_ID'
is of type 'TFMTBCDField', which i find odd.
-Pekka
 

Re:Generator Problem

Kostas Terzides writes:
Quote
SQQ_1GEN_ID is a persistent field obejct. What datatype does it have?
Are you using any Params for SQL_1 query and what datatype do they have?

As an alternative create a stored procedure and call this to get a
generator value (this way you are sure that the parameter is ftInteger):

CREATE PROCEDURE GET_GEN_PROC
RETURNS (AVALUE INTEGER
)
AS
BEGIN
AValue =gen_id(ASIAKAS_ID_GEN, 1);
END


SQQ_1GEN_ID has a field type 'TFMTBCDField'. It refuses to have an
integer type.
I'll try to use stored procedure.
-Pekka
 

Re:Generator Problem

There seemed to be too much strange problems, even the procedure method
did not work (returned null), so we changed tactics.
I placed a view that returns the max(asiakas_id) into the database and
made the SQQ_1 to return that value just before post. The new ASIAKAS_ID
would be that value + 1. Works well.
-Pekka
 

Re:Generator Problem

It will not work in a multi-user environment. Sooner or later two users
will get the same value. A generator is the only safe way to get a
unique number.
You said, "An additional remark is that the call to the generator
actually increases the generator value but does not return the value to
the application." What insert are you talking about? Can you show us
the code that causes the error?
Are you using InterBase 7.5?
Do you have all of the Delphi 6 service packs and patches installed?
I really do not understand how you can get different results for the
same EXE on different machines when they are all connected to the same
server. I assume you have checked to ensure that the same version of
the dbExpress driver is installed on all of the machines. That could
make a difference.
--
Bill Todd (TeamB)
 

Re:Generator Problem

Bill Todd writes:
Quote
It will not work in a multi-user environment. Sooner or later two users
will get the same value. A generator is the only safe way to get a
unique number.

You said, "An additional remark is that the call to the generator
actually increases the generator value but does not return the value to
the application." What insert are you talking about? Can you show us
the code that causes the error?

Are you using InterBase 7.5?

Do you have all of the Delphi 6 service packs and patches installed?

I really do not understand how you can get different results for the
same EXE on different machines when they are all connected to the same
server. I assume you have checked to ensure that the same version of
the dbExpress driver is installed on all of the machines. That could
make a difference.

We are using IB6.01 and Delphi 6.0 and dbExpress. We use the older, D6
version of dbexpint.dll (116K).
We are only doing an insert into a customer table. they are not
connected to a same server. These test machines use local IB, standard
Borland IB6.01 opensource installation
The code that causes problem is:
...
DataModule2.SQQ_1.Active := false;
DataModule2.SQQ_1.Open; <- this open rolls the generator
i_id := DataModule2.SQQ_1GEN_ID.AsInteger; <- does not return
proper value if generator value is i.e. 217, i_id contains '21=' as
mentioned in earlier posts.
DataModule2.SDC_PE_ASIAKASASIAKAS_ID.Value := i_id;
DataModule2.SDC_PE_ASIAKAS.Post; <- gives exeption ''20='not a
valid floating point value...'
Unit2.DataModule2.SDC_PE_ASIAKAS.ApplyUpdates(0);
...
and the exeption we encounter has to do with the post command and this
is because we do not get a proper generator value.
The code works fine on a development machine and on some older test
machines. Newer ones seem to have this problem.
I fully understand the problems with max(asiakas_id) but we do have to
get things working and the application does not get heavy loads.
-Pekka
 

Re:Generator Problem

Pekka Paunio writes:
Quote
Bill Todd writes:

>It will not work in a multi-user environment. Sooner or later two users
>will get the same value. A generator is the only safe way to get a
>unique number.
>
>You said, "An additional remark is that the call to the generator
>actually increases the generator value but does not return the value to
>the application." What insert are you talking about? Can you show us
>the code that causes the error?
>
>Are you using InterBase 7.5?
>
>Do you have all of the Delphi 6 service packs and patches installed?
>
>I really do not understand how you can get different results for the
>same EXE on different machines when they are all connected to the same
>server. I assume you have checked to ensure that the same version of
>the dbExpress driver is installed on all of the machines. That could
>make a difference.
>


We are using IB6.01 and Delphi 6.0 and dbExpress. We use the older, D6
version of dbexpint.dll (116K).

We are only doing an insert into a customer table. they are not
connected to a same server. These test machines use local IB, standard
Borland IB6.01 opensource installation

The code that causes problem is:
...
DataModule2.SQQ_1.Active := false;
DataModule2.SQQ_1.Open; <- this open rolls the generator
i_id := DataModule2.SQQ_1GEN_ID.AsInteger; <- does not return
proper value if generator value is i.e. 217, i_id contains '21=' as
mentioned in earlier posts.
DataModule2.SDC_PE_ASIAKASASIAKAS_ID.Value := i_id;
DataModule2.SDC_PE_ASIAKAS.Post; <- gives exeption ''20='not a
valid floating point value...'
Unit2.DataModule2.SDC_PE_ASIAKAS.ApplyUpdates(0);
...

and the exeption we encounter has to do with the post command and this
is because we do not get a proper generator value.

The code works fine on a development machine and on some older test
machines. Newer ones seem to have this problem.

I fully understand the problems with max(asiakas_id) but we do have to
get things working and the application does not get heavy loads.

-Pekka

I am confused about something:
Is i_id variable a variant? If it is an integer, then the exception
should have raised in .AsInteger line. ('21=' is not a valid integer
value is it?). May be if it is a variant, could you change it to an
integer and check the results?
 

Re:Generator Problem

If you are using a version of InterBase less than 6.0.1.6 you are using
a pre-release version that has a bug that can corrupt your database.
Download 6.0.1.6 or later from mers.com .
If you are not using the current version of any software you should
always mention that in your message. You are likely to get incorrect
information if you do not.
I am not sure what you mean by "the older Delphi 6 version of dbexpint.dll".
I seem to recall a problem similar to the one you describe that was
fixed in a driver update.
--
Bill Todd (TeamB)
 

Re:Generator Problem

Pekka Paunio writes:
Quote

The actual call for the value does not raise an exception but the
insert does. This has following behaviour, if the generator returns a
value 21X (for example 217) it complains that '21= is not a valid
floating point value), if the generator value is 22x it complains
about '22=' and so on. Some times there is '?' instead of '=';
This is an old problem and has nothing to do with Interbase itself or with
permissions, it is a bug in the dbExpress components. Make sure you have
applied all patches to Delphi 6 and any that may be available specifically for
dbExpress - I am sure this was fixed but I cannot tell you which update or
exactly when.
--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: www.logicfundamentals.com/RADBooks.html
Light is faster than sound, which is why some folks appear bright
before they speak.