You are a clever person, uko. I started to put together my demo for you to demonstrate this problem, and indeed, if I remove the join, the booking is visible without closing the application.
If you look at the join, there are 3 tables, i.e. TblBookings, TblFlightBookings, and TblFlightSchedule.
When a booking is inserted, a new record is added in TblBookings and TblFlightBookings,
what I now have to test is if something is happening to the insertion into TblFlightBookings - maybe that's where the transaction is failing...
Thanks for your assistance!
How to work with transactions
I am still investigating further but thought I would answer your questions quickly...
Are both queries running in the same transaction?
--Yes, my application doesn't "handle" transactions, I expected your components to do this for me. When a record is inserted, others must see it, etc. Later, if I want to, I will open and commit transactions in certain procedures.
From the code you posted I would say yes and both using the default transaction. Is this right?
--Yes
When the second query is executed: are you really sure that the first insert query has been executed before?
--Yes, looking at the code, definitely. If I execute the second query in the same client application, I can see the result. If I execute the second query in another client application, it will not see the result UNLESS I terminate/close the first client application.
And how do you know, that the second statement doesn't see the new record?
--As explains, it works in one client, not in another, unless I close the first client. Then it works - my results are returned.
What happens when you execute a simple query only on TblBooking (without joins on other tables and where clauses).
--As mentioned, this seems to solve the problem. We can therefore remove any doubt that the first statement isn't being committed. But there's another statement that inserts into TblFlightBookings, that is obviously not being committed. This I will do my next test
Can you see the record in this case? I'm asking because the second query contains joins on IDs.
--Not with the joins (unless I close the client application)
Are all those IDs valid? (when the first query creates a new record, how and when are the corresponding IDs in TblFlightBookings and TblFlightSchedule created?)
--Yes there are other queries, which I will now investigate.
Are both queries running in the same transaction?
--Yes, my application doesn't "handle" transactions, I expected your components to do this for me. When a record is inserted, others must see it, etc. Later, if I want to, I will open and commit transactions in certain procedures.
From the code you posted I would say yes and both using the default transaction. Is this right?
--Yes
When the second query is executed: are you really sure that the first insert query has been executed before?
--Yes, looking at the code, definitely. If I execute the second query in the same client application, I can see the result. If I execute the second query in another client application, it will not see the result UNLESS I terminate/close the first client application.
And how do you know, that the second statement doesn't see the new record?
--As explains, it works in one client, not in another, unless I close the first client. Then it works - my results are returned.
What happens when you execute a simple query only on TblBooking (without joins on other tables and where clauses).
--As mentioned, this seems to solve the problem. We can therefore remove any doubt that the first statement isn't being committed. But there's another statement that inserts into TblFlightBookings, that is obviously not being committed. This I will do my next test
Can you see the record in this case? I'm asking because the second query contains joins on IDs.
--Not with the joins (unless I close the client application)
Are all those IDs valid? (when the first query creates a new record, how and when are the corresponding IDs in TblFlightBookings and TblFlightSchedule created?)
--Yes there are other queries, which I will now investigate.
Okay the insertion into TblFlightBookings is done via Stored Procedure.
I take it that this is the transaction that is not being committed,
however I think it was with Zeoslib *confused*
Anyway, now I have to find out how to make the stored procedure commit.
Here's the code for executing the stored procedure:
And here is the DDL of the stored procedure itself:
I take it that this is the transaction that is not being committed,
however I think it was with Zeoslib *confused*
Anyway, now I have to find out how to make the stored procedure commit.
Here's the code for executing the stored procedure:
Code: Select all
function TFrmBookingsAddOrEditBarebones.BookASeat(DaFlightID : integer) : string;
var
aQuery : TIBCQuery;
begin
aQuery := TIBCQuery.create(nil);
aQuery.connection := FrmMainMenu.ZConnectRemote;
aQuery.Autocommit := true;
aQuery.Close;
aQuery.SQL.text := 'SELECT "DaFlightBookingID", "DaSeatNumber", "Successful" FROM "reserve_seat"(' +
FrmMainMenu.EdtStaffId.Text + ', ' + //StaffIDBooked
CurrToStr(EdtTotalFees.AsCurrency) + ',' + //PriceDue
CurrToStr(EdtTaxes.ascurrency) + ',' + //Taxes
QuotedStr(EdtDiscountReason.Text) + ',' + //DiscountReason
CurrToStr(EdtDiscount.ascurrency) + ',' + //Discount
CurrToStr(EdtPrice.ascurrency) + ',' + //Price
QuotedStr('First') + ',' + //Class
Quotedstr('Confirmed') + ' ,' + //Status
//KindCode
QuotedStr('N') + ', ' +
EdtBookingID.Text + ', ' + //BookingID
inttostr(DaFlightID) + ')';
aQuery.Open;
if aQuery.fieldbyname('Successful').asstring = 'T' then
begin
result := aQuery.Fieldbyname('DaSeatNumber').AsString;
end;
if aQuery.FieldByName('Successful').Asstring = 'F' then
begin
result := 'fail';
aQuery.free;
exit;
end;
aquery.free;
end;Code: Select all
SET TERM ^ ;
CREATE OR ALTER PROCEDURE "reserve_seat" (
"StaffIDBooked" integer,
"PriceDue" numeric(18,4),
"Taxes" double precision,
"DiscountReason" varchar(15),
"Discount" numeric(18,4),
"Price" numeric(18,4),
"Class" varchar(25),
"Status" varchar(25),
"KindCode" char(1),
"BookingID" integer,
"DaFlightID" integer)
returns (
"DaFlightBookingID" integer,
"DaSeatNumber" varchar(5),
"Successful" char(1))
as
declare variable "DaSeatID" integer;
declare variable "num_free_seats" integer;
begin
"DaSeatNumber" = null;
"DaSeatID" = -1;
"Successful" = 'T';
"num_free_seats" = -1;
SELECT NEXT VALUE FOR "G_TBLFLIGHTBOOKINGSIDGEN4" FROM RDB$DATABASE INTO "DaFlightBookingID";
select count(*)
from "TblFlightScheduleSeats"
where ("PassengerID" is null AND "FlightID" = :"DaFlightID")
into "num_free_seats";
if ("num_free_seats" is null) then
"num_free_seats" = 0;
if ("num_free_seats" = 0) then
begin
"Successful" = 'F';
end
if ("num_free_seats" > 0) then
begin
SELECT FIRST 1 "ID" FROM "TblFlightScheduleSeats" WHERE ("PassengerID" is null AND "FlightID" = :"DaFlightID") INTO :"DaSeatID";
SELECT FIRST 1 "SeatNumber" FROM "TblFlightScheduleSeats" WHERE ("PassengerID" is null AND "FlightID" = :"DaFlightID") INTO :"DaSeatNumber";
UPDATE "TblFlightScheduleSeats" SET "PassengerID" = :"DaFlightBookingID" WHERE "ID" = :"DaSeatID";
INSERT INTO "TblFlightBookings" ("ID", "BookingID", "KindCode", "FlightID", "StaffIDBooked", "Status", "Class", "Price", "Discount", "DiscountReason", "Taxes", "PriceDue", "SeatNumber") VALUES
(:"DaFlightBookingID", :"BookingID", :"KindCode", :"DaFlightID", :"StaffIDBooked", :"Status", :"Class", :"Price", :"Discount", :"DiscountReason", :"Taxes", :"PriceDue", :"DaSeatNumber");
end
SUSPEND;
end^
SET TERM ; ^
GRANT SELECT,UPDATE ON "TblFlightScheduleSeats" TO PROCEDURE "reserve_seat";
GRANT INSERT ON "TblFlightBookings" TO PROCEDURE "reserve_seat";
GRANT EXECUTE ON PROCEDURE "reserve_seat" TO SYSDBA;Okay, I just confirmed that you cannot COMMIT a transaction from inside a Stored Procedure, which means that there is no commit happening when I call my stored procedure from inside my client:
Now here we have AutoCommit set to True, but it doesn't seem to be committing!
I think the bug in your components is that because it is a SELECT statement, it does not see the need to commit a transaction, but if it were an INSERT statement it would.
I will see what kind of manual commits I can call
Code: Select all
aQuery.SQL.text := 'SELECT "DaFlightBookingID", "DaSeatNumber", "Successful" FROM "reserve_seat"I think the bug in your components is that because it is a SELECT statement, it does not see the need to commit a transaction, but if it were an INSERT statement it would.
I will see what kind of manual commits I can call
I think the problem is solved...
Because I execute my stored procedure with a SELECT statement (it used to be called by EXECUTE statement but due to one of your other bugs I could not return the parameters unless I use SELECT) your components do not think it's necessary to commit a transaction.
When I called CommitRetaining the problem is solved.
(NOTE: I did try committing transactions like this earlier and got that error message. This was because I called 'CommitRetaining' twice with two different queries during the procedure - here I am calling CommitRetaining once at the end of the procedure)
To fix this bug you should parse all SQL statements to detect whether a stored procedure is being executed, and if so you should commit the transaction.
Thanks for your assistance.
Please keep my informed when you are releasing the update of IDBAC because this will solve many of the problems in my application - and will solve me hours of work because I have to convert all my INSERT queries.
Because I execute my stored procedure with a SELECT statement (it used to be called by EXECUTE statement but due to one of your other bugs I could not return the parameters unless I use SELECT) your components do not think it's necessary to commit a transaction.
When I called CommitRetaining the problem is solved.
(NOTE: I did try committing transactions like this earlier and got that error message. This was because I called 'CommitRetaining' twice with two different queries during the procedure - here I am calling CommitRetaining once at the end of the procedure)
To fix this bug you should parse all SQL statements to detect whether a stored procedure is being executed, and if so you should commit the transaction.
Thanks for your assistance.
Please keep my informed when you are releasing the update of IDBAC because this will solve many of the problems in my application - and will solve me hours of work because I have to convert all my INSERT queries.
Also, please note that this problem has persisted since Friday last week, in other words I have not been able to work for 5 days thanks to this problem. Your assistance today (Tuesday) was able to help me solve this problem.
While I sincerely appreciate the effort, please try improve your response time in the future. I understand it is hard to run a software business, but also understand my frustration!
While I sincerely appreciate the effort, please try improve your response time in the future. I understand it is hard to run a software business, but also understand my frustration!
You can get values of output parameters when you execute a stored procedure with EXECUTE statement.
Output parameters should be created to get their values. You can do this manually using the Params.Add or Params.AddParam methods.
Output parameters can be created automatically:
1) Use the TIBCStoredProc component. It always creates output parameters.
2) If you use the TIBCSQL component, you can open its editor at design-time, select the Stored Proc Call Generator tab, and generate a SQL statement with output parameters.
3) You can call the CreateProcCall method of TIBCSQL or TIBCQuery at run-time to generate a SQL statement with output parameters.
Output parameters should be created to get their values. You can do this manually using the Params.Add or Params.AddParam methods.
Output parameters can be created automatically:
1) Use the TIBCStoredProc component. It always creates output parameters.
2) If you use the TIBCSQL component, you can open its editor at design-time, select the Stored Proc Call Generator tab, and generate a SQL statement with output parameters.
3) You can call the CreateProcCall method of TIBCSQL or TIBCQuery at run-time to generate a SQL statement with output parameters.