Insert with returning clause no longer works in Firebird 3+ #86
Description
The returning clause is not working with Firebird 3+ (server and client library). It has previously worked. I opened a ticket years back about it ... but that turned out to be a library misconfiguration issue.
The issue may be related to changes in how the the returning clause is handles. Previously, it returned a singleton row. Now it allows for multiple rows.
The following code works on with Firebird 2.5 (server and client library) but fails on Firebird 3/4/5 with:
#<Fb::Error:"Unknown cursor\nSQL error code = -504\nInvalid cursor reference\nCursor is not open\n">
**Edit: I have now tested 3/4/5 with linux server and 5 on a windows server. All fail with the invalid cursor error.
#testing insert/returning functionality with fb and Firebird 5
require 'fb'
include Fb
db = Database.new(
:database => "localhost:/opt/firebird/dbs/test1.fdb",
:username => 'sysdba',
:password => 'masterkey')
conn = db.connect rescue db.create.connect
create_gen=%|CREATE SEQUENCE GEN_TEST_ID;|
set_gen=%|SET GENERATOR GEN_TEST_ID TO 0;|
create_bi_trigger = %|CREATE TRIGGER BI_TEST_ID FOR TEST BEFORE insert POSITION 0 AS BEGIN IF(NEW.ID IS NULL)THEN NEW.ID=GEN_ID(GEN_TEST_ID, 1);END|
conn.transaction do
if conn.table_names.include?("TEST")
conn.execute('delete from TEST;')
else
conn.execute("CREATE TABLE TEST(ID INT NOT NULL PRIMARY KEY, NAME VARCHAR(20));")
end
unless conn.generator_names.include?('GEN_TEST_ID')
conn.execute(create_gen)
conn.execute(set_gen)
end
conn.execute(create_bi_trigger) unless conn.trigger_names.include?('BI_TEST_ID')
conn.commit
cursor=conn.execute('INSERT INTO TEST(NAME) VALUES (?) RETURNING ID;','test-value')
begin
val = cursor.fetch
puts "Success!"
puts val.inspect
rescue Exception=>e
puts "Exception"
puts e.inspect
puts "-" * 20
end
end
puts conn.query("select * from test;")
conn.close