Hi all! I have a problem with Delphi (10 Seattle Update1), FireDAC and Firebird (2.1.7).
Hi all! I have a problem with Delphi (10 Seattle Update1), FireDAC and Firebird (2.1.7).
I can't change any version at this moment. Sorry.
The problem is the next:
I have a stored procedure:
SET TERM ^ ;
CREATE PROCEDURE GET_BOOKENTRY_NUMBER
RETURNS (
BOOKENTRY INTEGER )
AS
declare variable HowMany integer;
begin
HowMany=1;
WHILE (HowMany>0) DO
BEGIN
BookEntry = gen_id(BookEntry, 1);
SELECT COUNT(*) FROM Journal WHERE BookEntry =:BookEntry
INTO :HowMany;
END
end
end^
SET TERM ; ^
GRANT EXECUTE
ON PROCEDURE GET_BOOKENTRY_NUMBER TO SYSDBA;
Gen_ID is a standard generator. The problem is, I think, with the returned value of the procedure, because I have other old Program operating with Delphi 7, that works well.
I tried several methods of get the value from inside my program, but all the cases I recover an empty variable.
I've used a TFDStoredProc, calling this procedure.
I've used the same, but created on the fly.
I've used a TFDQuery with the next SQL
EXECUTE PROCEDURE Get_BookEntryNumber
No one of this methods works.
Always return NULL.
Does anyone know another "trick" or technic to recover this value?
Thanks in advance to all.
I've visited a lot os webs, sites and stackoverflow themes. I did a lot of lookups in the web searching a solution. What I need I your experience. If you has been in this state previously, what solution have you told?
Thank's again?
I can't change any version at this moment. Sorry.
The problem is the next:
I have a stored procedure:
SET TERM ^ ;
CREATE PROCEDURE GET_BOOKENTRY_NUMBER
RETURNS (
BOOKENTRY INTEGER )
AS
declare variable HowMany integer;
begin
HowMany=1;
WHILE (HowMany>0) DO
BEGIN
BookEntry = gen_id(BookEntry, 1);
SELECT COUNT(*) FROM Journal WHERE BookEntry =:BookEntry
INTO :HowMany;
END
end
end^
SET TERM ; ^
GRANT EXECUTE
ON PROCEDURE GET_BOOKENTRY_NUMBER TO SYSDBA;
Gen_ID is a standard generator. The problem is, I think, with the returned value of the procedure, because I have other old Program operating with Delphi 7, that works well.
I tried several methods of get the value from inside my program, but all the cases I recover an empty variable.
I've used a TFDStoredProc, calling this procedure.
I've used the same, but created on the fly.
I've used a TFDQuery with the next SQL
EXECUTE PROCEDURE Get_BookEntryNumber
No one of this methods works.
Always return NULL.
Does anyone know another "trick" or technic to recover this value?
Thanks in advance to all.
I've visited a lot os webs, sites and stackoverflow themes. I did a lot of lookups in the web searching a solution. What I need I your experience. If you has been in this state previously, what solution have you told?
Thank's again?
Hi,
ReplyDeleteAnd how about using SUSPEND
Andry
What is SUSPEND? Can you explain this, please?
ReplyDeleteAttila Kovacs
ReplyDeleteSorry. I need to explain again...
The methods and generators are working well for another program. I need not changed it.
The problem is in the new program. How to recover the value from Delphi DX Seattle with FireDac.
Does your Stored Procedure retuns more than one row.
ReplyDeleteIf yes, you need to use a TFDQuery with the SQL like this
SELECT BOOK_ENTRY FROM GET_BOOKENTRY_NUMBER
Attila Kovacs
ReplyDeleteYes. This is the problem, with all the methods enumerated. And I don't know any other form of do this.
RAFILOBERA Andry
ReplyDeleteThis part of the Stored Procedure reveals that it returns only a value:
BookEntry = gen_id(BookEntry, 1);
I'm sure this is a bug in FireDAC. Sure! What I need is a workaround.
ReplyDeleteWhen I call the Stored Procedure from Delphi, The Gen_ID seems not executes. The seed value is not incremented.
May be a problem of the call, not of the execution or the returning. But, I'm looking for a workaround.
Thanks.
Attila Kovacs
ReplyDeleteCREATE PROCEDURE GET_BOOKENTRY_NUMBER
RETURNS (
/* here is book entry =====>>>>>>*/ BOOKENTRY INTEGER )
AS
....
/*======>>>>>>>+/ BookEntry = gen_id(BookEntry, 1);
....
end
Sorry! Translation problem.
But I repeat, The procedure is working in Delphi 7 with DBExpress. I know that the problem is Delphi DX and FireDAC. What I need is a workaround!!!!!
thanks.
Solved!!!!!!
ReplyDeletewith this two methods:
ReplyDeletefunction TOldAccConnectionModel.GetNextBookEntryNumber:Integer;
var Q :TFDQuery;
begin
Q := Accounting.CreateQuery(['SELECT MAX(ASIENTO) CurrentMax FROM DIARIO']);
try Q.Open;
Result := Q.FieldByName('CurrentMax').AsInteger +1;
finally Q.Free;
end;
end;
procedure TOldAccConnectionModel.UpdateGenerator(NewNumber :Integer);
var Q :TFDQuery;
begin
Q := Accounting.CreateQuery(['SET GENERATOR ASIENTO TO '+IntToStr(NewNumber)+' ']);
try Q.ExecSQL;
finally Q.Free;
end;
end;
The first, emulates the Stored Procedure, but in Delphi Code.
The second Set the new value of the Generator.
I think this is a good workaround.
(I have no time to translate again the code. sorry).
Thank you all!!!!
Attila Kovacs
ReplyDeleteI need make Book Entries in an Accounting of a third party software. My intention is left the "house clean" after insert the new Book Entry. No more than one client application is going to made this task and no more than one time in by day.
What I want to say is that concurrency is not my problem. My problem is allow the other software continue running, as if I don't stay inserting thing in his database.
Sorry again Attila Kovacs.
ReplyDeleteThe problem is that I have deleted the messages of Michael, because his relevance was near of ZERO. Because he has not read the problem. Only was explaining how to improve the Stored Procedure.
The others, thank you. You don't gived me the solution, but, as most of the times, you are gived me the inspiration.
Thanks.
Return command "procedure" any value? Or it does only "function"?
ReplyDeleteWhat kind of SQL engine do you use?
In e.g. Oracle this will be the key question.