At 05:32 AM 22/06/2004 -0700, you wrote:

>Greetings,

Greetings, Kevin

>I have an order entry system that requires an absolutely unique order number
>to be issued when sales people enter an order.
>
>In remembering all the posts over the years, using a generator is the way to
>go as it's my understanding it "operates" outside of the transaction thus
>assuring a unique value.

True.

>I am getting reports of duplicate order numbers being issued (a very bad
>thing).

Yup. One thing you can be certain of, the error is happening in your
application, somehow. There's simply no way you could get duplicate
numbers from a generator.

>Here's how I get the next generator value via a function in a data module:
>
>function TdmSalesInq.GetPermOrderNo: String;
>begin
>{
>qryGetID is a TIB_Cursor with the SQL of : select * from
>sp_get_nextid(:idtype)
> this cursor is hooked up to a TIB_Transaction set to autocommit = true
>which should not matter (I think)
>}

It does matter. You have a "hit-and-run" operation that is holding
resources. Use an explicit transaction for hit-and-run.

> with qryGetID do
> begin
> If Active then Close;
> If NOT Prepared then Prepare;
> ParamByName('IDType').AsString := 'ORDERNO';
> Open;
> Result := FieldByName('NextID').AsString;
> Close;
> end;

Bad. TIB_Cursor doesn't have the Active property or Open or Close. Use
First to read a singleton from a TIB_Cursor that is a SELECT.
But don't use a SELECT statement for a SP that returns asingleton - use an
EXECUTE PROCEDURE statement, call Execute, and read the field value
immediately following the Execute. Commit immediately after you have the
value. Even better, use a TIB_DSQL to do the same thing with about a
quarter of
the overhead.

[..]

>//~~~~~~~~~~~~~~~~~~~~~~
>sp_get_nextid looks like this:
>CREATE PROCEDURE SP_GET_NEXTID(
> IDTYPE VARCHAR(15))
>RETURNS (
> NEXTID INTEGER)
>AS
>BEGIN
> if (IDType = 'QUOTE') then
> NextID = GEN_ID(GEN_QUOTES, 1);
>
> if (IDType = 'ORDERNO') then
> NextID = GEN_ID(GEN_ORDERNO, 1);
>
>/* many more of these */

>Suspend; <----------NO!!! <----------because you want to EXECUTE this
>end
>//~~~~~~~~~~~~~~~~~~~~~~
>
>Any ideas of my problem would be GREATLY appreciated.

The cause of the duplicate numbers is somewhere in code you haven't shown
us - possibly some flaw in the way you are converting the integer to
string, which you don't show here.

But I'd like to recommend a more elegant way to do this task.

function TdmSalesInq.GetPermNo(idtype: string): integer;
begin
{
qryGetID is a TIB_Cursor with the SQL of :

EXECUTE PROCEDURE SP_GET_NEXTID(:idtype)

and it has its own ReadCommitted transaction !!

}

with qryGetID do
begin
ib_transaction.StartTransaction;
If NOT Prepared then Prepare;
ParamByName('IDType').AsString := idtype;
Execute;
Result := FieldByName('NextID').AsString;
Commit;
end;

Now, your various methods that want their particular generator number all
call the same function:
e.g. for the ORDERNO:

function TdmSalesInq.GetPermOrderNo: String;
begin
If NOT (qryOH.State in [dssEdit, dssInsert]) then
qryOH.Edit;
qryOH.FieldByName('OrderNo').AsString := IntToStr(GetPermNo('ORDERNO'));
PostChanges(qryOH);
Global.RefreshQry(qryOD, False);
Global.RefreshQry(qryODC, False);
end;

Helen