rowland
Insert with returning clause no longer works in Firebird 3+
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
