Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Creating a stored procedure with an "update or insert" statement with MATCHING fails [CORE3966] #4299

Closed
firebird-automations opened this issue Oct 29, 2012 · 6 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Frank Schlottmann-Goedde (fsg)

Is duplicated by CORE3968

Using the following script in isql against the employee database demonstrates the error

/*
Creating a stored procedure with an "update or insert" statement with returning value
fails with:

Invalid token.
invalid request BLR at offset 364.
context not defined (BLR error).
Error while parsing procedure P_BETEILIGUNG_IU's BLR.
*/
SET TERM ^ ;
create or alter procedure P_BETEILIGUNG_IU (
GID char(36) character set ISO8859_1 collate ISO8859_1,
GID_BETEILIGUNGS_VERHAELTNIS char(36) character set ISO8859_1 collate ISO8859_1,
GID_VRINFO char(36) character set ISO8859_1 collate ISO8859_1,
GID_AGNR char(36) character set ISO8859_1 collate ISO8859_1,
ANTEIL numeric(18,3),
ORDERNR integer,
GUELTIG_AB date,
GUELTIG_BIS date,
FUEHRENDE_PRAEMIE char(1) character set ISO8859_1 collate ISO8859_1,
FUEHRENDE_COURTAGE char(1) character set ISO8859_1 collate ISO8859_1,
GID_ZAHLART char(36) character set ISO8859_1 collate ISO8859_1,
GID_POLICEN_BETEILIGTE char(36) character set ISO8859_1 collate ISO8859_1,
VSNRVR varchar(36) character set ISO8859_1 collate ISO8859_1,
VSNR varchar(36) character set ISO8859_1 collate ISO8859_1)
as
declare variable VON date;
declare variable BIS date;
declare variable GID_POLICEN char(36);
declare variable GID_POLICEN_OR_DETAIL char(36);
declare variable ALLE_SOLLST_NEUMACHEN char(1);
declare variable GID_BETEILIGUNG char(36);
declare variable D char(1);
declare variable po_number char(8);
declare variable cust_no integer;
declare variable sales_rep smallint;
declare variable order_status varchar(7);
declare variable order_date timestamp;
declare variable ship_date timestamp;
declare variable date_needed timestamp;
declare variable paid char(1);
declare variable qty_ordered integer;
declare variable total_value decimal(9,2);
declare variable discount float;
declare variable item_type varchar(12);
declare variable aged numeric(18,9);
begin
if (ORDERNR is null) then
begin
select max(B.QTY_ORDERED) + 1
from SALES B
where B.item_type = :GID_BETEILIGUNGS_VERHAELTNIS
into :ORDERNR;
end
update or insert into SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID,
QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE)
values (:PO_NUMBER, :CUST_NO, :SALES_REP, :ORDER_STATUS, :ORDER_DATE, :SHIP_DATE, :DATE_NEEDED, :PAID, :QTY_ORDERED,
:TOTAL_VALUE, :DISCOUNT, :ITEM_TYPE)
matching (PO_NUMBER)
returning (PO_NUMBER)
into :PO_NUMBER;

end^
SET TERM ; ^

Commits: 62ac6e3

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

summary: Creating a stored procedure with an "update or insert" statement with returning value fails => Creating a stored procedure with an "update or insert" statement with MATCHING fails

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Link: This issue is duplicated by CORE3968 [ CORE3968 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Closed [ 6 ] => Closed [ 6 ]

QA Status: Done successfully

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants