Where I am mainly using Sequences, not tied to a specific field is where many entities in our application<div>all use the same number sequence to be attached a unique reference, There is a single point of entry to type in that ref# which will open anything relevant, ie an invoice, a statement a client, a debtor etc. when talking to customers on the phone, it lets the client access relevant info very quickly. I don't tend to use a database to its full capacity as a database as oracle and MSSql etc would let you.. its a hybrid between the middletier OO layer, and the database. </div>
<div><br></div><div>The actual stored proc I've come up with is as follows... I'm just waiting to find the flaws.. :D i think 99.999% it will work great.. all my primary keys are GUIDs.</div><div><div><br></div><div>
CREATE PROCEDURE [GETGEN] @TableName VarChar(256), @ACount Int</div><div><br></div><div>AS</div><div><br></div><div>SET NOCOUNT ON</div><div>DECLARE @AVALUE Int</div><div>BEGIN TRAN</div><div><br></div><div>DECLARE @Res TABLE ( ID int )</div>
<div><br></div><div>BEGIN</div><div> UPDATE GEN_IDTABLE with (XLOCK,ROWLOCK)</div><div> SET KEYVALUE = @AVALUE+@ACOUNT</div><div> OUTPUT deleted.KEYVALUE INTO @RES(ID)</div><div> WHERE KEYNAME = @TableName</div><div>
if @@ROWCOUNT = 0</div><div> begin</div><div> SET @AVALUE = 1</div><div> INSERT INTO GEN_IDTABLE (KEYNAME,KEYVALUE) VALUES (@TableName,@ACount+1);</div><div> end else</div><div> begin</div><div> SELECT @AVALUE=ID from @Res</div>
<div> end</div><div>END</div><div><br></div><div>COMMIT TRAN</div><div>RETURN @AVALUE</div><div><br></div></div><div><br><br><div class="gmail_quote">On Mon, Dec 1, 2008 at 9:40 PM, Neven MacEwan <span dir="ltr"><<a href="mailto:neven@mwk.co.nz">neven@mwk.co.nz</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">Kyley<br>
<br>
my experience is with postgreqsl, it has sequences (I assume from its<br>
oracleness), which get tied to a column via the serial type.<br>
<br>
I'm puzzled as to where you would use a Sequence/Generator in a non-pk<br>
situation, unless you<br>
are talking about your 'documentno' column, which as shown can be done<br>
via a stored proc<br>
<br>
I wonder is mssql eventually will allow functions to update tables (or<br>
code a primative sequence generation)<br>
which would allow this<br>
<br>
Neven<br>
<div class="Ih2E3d"><br>
> The main difference is that a Generator is more like an Oracle Sequence,<br>
> It is table independant, which allows you to use the same sequence<br>
> accross more than one table,<br>
> and you can also have more than one per table. I find the identity is<br>
> only useful if it is to be a primary key, not useful as a data element.<br>
><br>
> On Mon, Dec 1, 2008 at 5:12 PM, Neven MacEwan <<a href="mailto:neven@mwk.co.nz">neven@mwk.co.nz</a><br>
</div><div><div></div><div class="Wj3C7c">> <mailto:<a href="mailto:neven@mwk.co.nz">neven@mwk.co.nz</a>>> wrote:<br>
><br>
> John<br>
><br>
> Really interbase generators are more akin to the mssql identity column<br>
> type, the problem with mssql identity columns WAS<br>
> with the @@Identity variable which was per connection (and hence<br>
> triggers that inserted other identity columns not return the identity<br>
> you would expect), this has been 'fixed' with the<br>
> SCOPE_IDENTITY() function<br>
><br>
> Stangely identity columns and generators behave the same way, ie they<br>
> are both not rolled back by a trans<br>
><br>
> PostgeSQL, uses a combo of both a column type of 'serial', which auto<br>
> generates a 'sequence'<br>
><br>
> Of course I could be spouting BS.<br>
><br>
> Neven<br>
> //<br>
> > Whats others opinion on the merits of trggers etc as discussed<br>
> here vs the<br>
> > Firebird/Interbase method of using generators?<br>
> ><br>
> > I have often wondered if this is an area where<br>
> Firebird/Interbase has a less<br>
> > convenient but more transparent mechanism - where you have to fire a<br>
> > generator to get a new key value before you start putting data<br>
> in a new<br>
> > record.<br>
> ><br>
> > And the generator never rolls back even if the transaction does<br>
> - avoiding<br>
> > the lock problem. Mostly in<br>
> > my experience this is fine, except some times when the ID<br>
> numbers (eg batch<br>
> > numbers) are supposed to be strictly sequential and then you<br>
> have to get the<br>
> > next value from a SQL query, and make sure no-one else is<br>
> running another of<br>
> > the same batch at the same time. (In those situations the main<br>
> thing that<br>
> > protects the database is that only one staff member ever runs<br>
> this batch<br>
> > operation - I bet thats how a lot of databases run in practice)<br>
> ><br>
> ><br>
> > John<br>
> ><br>
> > _______________________________________________<br>
> > NZ Borland Developers Group - Delphi mailing list<br>
</div></div>> > Post: <a href="mailto:delphi@delphi.org.nz">delphi@delphi.org.nz</a> <mailto:<a href="mailto:delphi@delphi.org.nz">delphi@delphi.org.nz</a>><br>
<div class="Ih2E3d">> > Admin: <a href="http://delphi.org.nz/mailman/listinfo/delphi" target="_blank">http://delphi.org.nz/mailman/listinfo/delphi</a><br>
> > Unsubscribe: send an email to <a href="mailto:delphi-request@delphi.org.nz">delphi-request@delphi.org.nz</a><br>
</div>> <mailto:<a href="mailto:delphi-request@delphi.org.nz">delphi-request@delphi.org.nz</a>> with Subject: unsubscribe<br>
<div class="Ih2E3d">> ><br>
> ><br>
> ><br>
><br>
> _______________________________________________<br>
> NZ Borland Developers Group - Delphi mailing list<br>
</div>> Post: <a href="mailto:delphi@delphi.org.nz">delphi@delphi.org.nz</a> <mailto:<a href="mailto:delphi@delphi.org.nz">delphi@delphi.org.nz</a>><br>
<div class="Ih2E3d">> Admin: <a href="http://delphi.org.nz/mailman/listinfo/delphi" target="_blank">http://delphi.org.nz/mailman/listinfo/delphi</a><br>
> Unsubscribe: send an email to <a href="mailto:delphi-request@delphi.org.nz">delphi-request@delphi.org.nz</a><br>
</div>> <mailto:<a href="mailto:delphi-request@delphi.org.nz">delphi-request@delphi.org.nz</a>> with Subject: unsubscribe<br>
<div class="Ih2E3d">><br>
><br>
><br>
><br>
> --<br>
> Kyley Harris<br>
> Harris Software<br>
> +64-21-671-821<br>
</div>> ------------------------------------------------------------------------<br>
<div><div></div><div class="Wj3C7c">><br>
> _______________________________________________<br>
> NZ Borland Developers Group - Delphi mailing list<br>
> Post: <a href="mailto:delphi@delphi.org.nz">delphi@delphi.org.nz</a><br>
> Admin: <a href="http://delphi.org.nz/mailman/listinfo/delphi" target="_blank">http://delphi.org.nz/mailman/listinfo/delphi</a><br>
> Unsubscribe: send an email to <a href="mailto:delphi-request@delphi.org.nz">delphi-request@delphi.org.nz</a> with Subject: unsubscribe<br>
<br>
_______________________________________________<br>
NZ Borland Developers Group - Delphi mailing list<br>
Post: <a href="mailto:delphi@delphi.org.nz">delphi@delphi.org.nz</a><br>
Admin: <a href="http://delphi.org.nz/mailman/listinfo/delphi" target="_blank">http://delphi.org.nz/mailman/listinfo/delphi</a><br>
Unsubscribe: send an email to <a href="mailto:delphi-request@delphi.org.nz">delphi-request@delphi.org.nz</a> with Subject: unsubscribe<br>
</div></div></blockquote></div><br><br clear="all"><br>-- <br>Kyley Harris<br>Harris Software<br>+64-21-671-821<br>
</div>