Thanks for answer. All I've done was a simple test.Now things are clearly for me.
Thanks also to Mercea Paul and Alan McDonald .

----- Original Message ----
From: Woody <woody-tmw@...>
To: firebird-support@yahoogroups.com
Sent: Monday, April 30, 2007 10:02:35 PM
Subject: Re: [firebird-support] Generators questions













From: "Ionut Ichim" <ionut_ykm@yahoo. com>

> I've created a table (table1) with IDcod as pk numeric(18,0) ,then a

> generator

> CREATE GENERATOR GEN_N;

> SET GENERATOR GEN_N TO 1;

> I've inserted 3 records for IDcod values 5,6,7.

> With IBExpert Personal,in a script I put this :

> SELECT gen_id(gen_N, 1) FROM table1;

> Result :

> 2 3 4

> run again

> 5 6 7

> Why generator creates such result, for each record a value generated.

> Where is the value i look for ?

> Wouldn't it be slow for a big database ?

> Why generator creates values that can duplicate pk,or I have to use

> generator from the start ?

You are mixing your apples and coconuts. :)



A generator is a detached counter which you can use to generator primary

keys for one or more tables. However, there is no way to "connect" it with a

table directly. It is done through client side code or triggers. You haven't

mentioned any of that, nor did you mention how you got the key values when

you inserted data into the table.



Every time you execute the statement gen_id(generator_ name, 1) you are

telling the generator to increment and return the current value. But it has

no way of knowing "where" you use that value. That's up to you. In the

select statement above, the gen_id command is executed for each record in

TABLE1. Since you inserted 3 records, it returns 3 results, each one an

increment higher than the last.



Some components allow you to specify key fields and their associated

generators that you want to use when inserting new records. They handle all

the background stuff of incrementing the generator and getting the value and

then putting it in the key field of the new record. If you aren't using

components that do that, you need to control it yourself.



Method 1: Call the gen_id command to get the next increment when you insert

a new record using a separate query. Use the resultant number as the value

for the key field by assigning it in code.



Method 2: Create a BeforeInsert trigger in the database on that table. Check

to see if the key field is null and if so, assign it the next generator

value using gen_id.



Method 2 means that you won't have access to the newly inserted key field's

value until you commit the record and refresh it.



HTH

Woody (TMW)














<!--

#ygrp-mlmsg {font-size:13px;font-family:arial, helvetica, clean, sans-serif;}
#ygrp-mlmsg table {font-size:inherit;font:100%;}
#ygrp-mlmsg select, input, textarea {font:99% arial, helvetica, clean, sans-serif;}
#ygrp-mlmsg pre, code {font:115% monospace;}
#ygrp-mlmsg * {line-height:1.22em;}
#ygrp-text{
font-family:Georgia;
}
#ygrp-text p{
margin:0 0 1em 0;}
#ygrp-tpmsgs{
font-family:Arial;
clear:both;}
#ygrp-vitnav{
padding-top:10px;font-family:Verdana;font-size:77%;margin:0;}
#ygrp-vitnav a{
padding:0 1px;}
#ygrp-actbar{
clear:both;margin:25px 0;white-space:nowrap;color:#666;text-align:right;}
#ygrp-actbar .left{
float:left;white-space:nowrap;}
.bld{font-weight:bold;}
#ygrp-grft{
font-family:Verdana;font-size:77%;padding:15px 0;}
#ygrp-ft{
font-family:verdana;font-size:77%;border-top:1px solid #666;
padding:5px 0;
}
#ygrp-mlmsg #logo{
padding-bottom:10px;}

#ygrp-vital{
background-color:#e0ecee;margin-bottom:20px;padding:2px 0 8px 8px;}
#ygrp-vital #vithd{
font-size:77%;font-family:Verdana;font-weight:bold;color:#333;text-transform:uppercase;}
#ygrp-vital ul{
padding:0;margin:2px 0;}
#ygrp-vital ul li{
list-style-type:none;clear:both;border:1px solid #e0ecee;
}
#ygrp-vital ul li .ct{
font-weight:bold;color:#ff7900;float:right;width:2em;text-align:right;padding-right:.5em;}
#ygrp-vital ul li .cat{
font-weight:bold;}
#ygrp-vital a {
text-decoration:none;}

#ygrp-vital a:hover{
text-decoration:underline;}

#ygrp-sponsor #hd{
color:#999;font-size:77%;}
#ygrp-sponsor #ov{
padding:6px 13px;background-color:#e0ecee;margin-bottom:20px;}
#ygrp-sponsor #ov ul{
padding:0 0 0 8px;margin:0;}
#ygrp-sponsor #ov li{
list-style-type:square;padding:6px 0;font-size:77%;}
#ygrp-sponsor #ov li a{
text-decoration:none;font-size:130%;}
#ygrp-sponsor #nc {
background-color:#eee;margin-bottom:20px;padding:0 8px;}
#ygrp-sponsor .ad{
padding:8px 0;}
#ygrp-sponsor .ad #hd1{
font-family:Arial;font-weight:bold;color:#628c2a;font-size:100%;line-height:122%;}
#ygrp-sponsor .ad a{
text-decoration:none;}
#ygrp-sponsor .ad a:hover{
text-decoration:underline;}
#ygrp-sponsor .ad p{
margin:0;}
o {font-size:0;}
.MsoNormal {
margin:0 0 0 0;}
#ygrp-text tt{
font-size:120%;}
blockquote{margin:0 0 0 4px;}
.replbq {margin:4;}
-->







__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

[Non-text portions of this message have been removed]