Board index » delphi » SQL Window returns different result than Delphi Code - Getting Identity Value

SQL Window returns different result than Delphi Code - Getting Identity Value

Hello,

I am new to Interbase and using Delphi 5 C/S.

I have a function below that should return the last Order_ID from a
table that runs,  but returns the last Order_ID from the table without
considering the Order_Num in the WHERE clause for the Order_ID column
.

In other words my objective is to return the MAX(Order_ID) value from
the table where the Order_Num column matches the parameter coming into
the function.

When the same code is executed in an SQL Window (using Interbase
Workbench ... Great tool) the result of Max(Order_ID) is null which is
correct considering the table has no records currently.

The Order_ID column is of type integer and I am using a Generator when
inserting data into the table to AutoInc that column's value.  So if I
insert records during my testing and then delete or purge out the
records, the Generator value is not re-seeded. Which is OK.

But why the code generated at runtime in Delphi gets a Max value of
let's say 12 when there are no records and I am specifying a WHERE
clause is unclear to me..

This would never happen in SQL server..

Here is my code ..

----------------------------------
function TDM.GetLastDocumentNumber(OrderNum: ShortString): Integer;
var
 SelectSQL : string;
begin
 Result    := 0;
 SelectSQL := '';
 try
  try
   SelectSQL := 'SELECT MAX(Order_ID) FROM Documents WHERE Order_Num =
' + Trim(OrderNum);
    with IBQueryGetLastDocumentNum do
     begin
       Close;
       SQL.Clear;
       SQL.Text := SelectSQL;
       Open;
       Result := FieldByName('MAX').AsInteger;
     end;
  except

  end;
 finally
  IBQueryGetLastDocumentNum.Close
 end;
end;
-----------------------------------

Any help would be greatly appreciated.

Thanks,

--
Reid Roman
Future Generation Software
http://www.futuregenerationsoftware.com

 

Re:SQL Window returns different result than Delphi Code - Getting Identity Value


Quote
Reid Roman wrote in message <3af3416c_2@dnews>...

>The Order_ID column is of type integer and I am using a Generator when
>inserting data into the table to AutoInc that column's value.  So if I
>insert records during my testing and then delete or purge out the
>records, the Generator value is not re-seeded. Which is OK.

>But why the code generated at runtime in Delphi gets a Max value of
>let's say 12 when there are no records and I am specifying a WHERE
>clause is unclear to me..

Almost definitely for the same reason as your other post answered by
Jeff - you need to commit the transaction in WorkBench.

--
Wayne Niddery (WinWright Inc.)
RADBooks - http://members.home.net/wniddery/RADBooks/delphibooks.html
"At the apex of every great tragedy of mankind there stands the figure
of an incorruptible altruist" - Ayn Rand

Re:SQL Window returns different result than Delphi Code - Getting Identity Value


Ok..

Thank You.

--
Reid Roman
Future Generation Software
http://www.futuregenerationsoftware.com
"Wayne Niddery (TeamB)" <winwri...@chaffhome.com> wrote in message
news:3af47166_1@dnews...

Quote

> Almost definitely for the same reason as your other post answered by
> Jeff - you need to commit the transaction in WorkBench.

Other Threads