Menu
▾
▴
[Firebird-devel] Updatable cursors are in need of some touchup
|
From: Jason L. W. <jwh...@jw...> - 2007-07-25 04:10:31
|
First I want to say, I absolute love Firebird 2.x! As I have worked to get
IBO totally compliant to it there is so much I have long wanted that I now
have. Kudos to all of you for your hard work and dedication!
Also, my apologies for my absence among you for a couple years. I have not
been able to attend the conferences and I'm not sure if I will get to this
year either. There's a lot of people I would love to meet and rekindle
friendships with. I know many are growing concerned about whether or not
IBO is going to "keep up". Based on my (inexcusable) performance to get IBO
ready they are well justified concerns.
For the record, IBO 4.7 had most everything 6 months ago and 4.8 has
everything supported except for built in UTF8 transliteration for BLOB MEMO
columns. Otherwise, to my knowledge, everything is fully operational. 4.8
is on the cusp of being released official too.
Now that I have been back deep into the gizzards of IBO I have some "off the
beaten path" issues that have arisen out of the progress of Firebird's
development that I would like to raise. These are likely issues purely to
do with support of legacy features that indicate some of the wonderful new
features just need a bit more completion in this obscure area I am exposing.
I have a fairly simple sample that should be easy to test with any client
that works directly with the handles and can work with a "live" server
cursor. If any here would like to work on this issue and use IBO I will
provide free access to the source code of IBO to facilitate testing. Please
send me a private email requesting access to the full sources.
Here's the sample.
Please create this table with one record:
CREATE TABLE SALES_HREF (
ORDERID INTEGER NOT NULL
, ORDERDETAILID INTEGER NOT NULL
, DDATE TIMESTAMP NOT NULL
, LITORDERSUBTOTAL DECIMAL ( 9, 2 )
, LITORDERTAX DECIMAL ( 9, 2 )
, LITORDERSH DECIMAL ( 9, 2 )
, LITORDERTOTAL DECIMAL ( 9, 2 )
, REP VARCHAR( 3 )
, LINE SMALLINT
, QTY SMALLINT
, ITEMNO VARCHAR( 12 )
, ITEMDESC VARCHAR( 50 )
, ITEMRETAIL DECIMAL ( 9, 2 )
, ITEMTAX DECIMAL ( 9, 2 )
, SHIPPING DECIMAL ( 9, 2 )
, EXTPRICE DECIMAL ( 9, 2 )
, IV_ID INTEGER NOT NULL
, ADD_DATE TIMESTAMP NOT NULL
, CU_ID INTEGER
, IMPORTED VARCHAR( 1 )
);
commit;
insert into sales_href( ORDERID, ORDERDETAILID, DDATE, LITORDERSUBTOTAL,
LITORDERTAX, LITORDERSH, LITORDERTOTAL, REP, LINE, QTY, ITEMNO, ITEMDESC,
ITEMRETAIL, ITEMTAX, SHIPPING, EXTPRICE, IV_ID, ADD_DATE, CU_ID, IMPORTED )
values (
1283,1,'1/14/1998',415,31.13,30.75,476.88,'Non',1,1,'0612','Interbase
Objects, with CD',415,NULL,0,415,102342,'8/14/2001',101597,'T' );
commit;
Then, using the client tool of your choice, open a live cursor using the
following statement:
SELECT sh.ORDERID
, sh.DDATE
, sh.QTY
, sh.CU_ID
FROM SALES_HREF sh
FOR UPDATE OF sh.QTY
Now, for the first problem. I cannot use the sh.QTY in the FOR UPDATE list
/*---
START TRANSACTION
DB HANDLE COUNT 1
TR_HANDLE = 87
----*/
/*---
PREPARE STATEMENT
TR_HANDLE = 87
STMT_HANDLE = 86
SELECT sh.ORDERID
, sh.DDATE
, sh.QTY
, sh.CU_ID
FROM SALES_HREF sh
FOR UPDATE OF sh.QTY
FIELDS = [ Version 1 SQLd 0 SQLn 30 ]
ERRCODE = 335544569
----*/
/*---
INTERPRET BUFFER =
ERRCODE = 17
----*/
/*---
INTERPRET BUFFER = Dynamic SQL Error
ERRCODE = 21
----*/
/*---
INTERPRET BUFFER = SQL error code = -104
ERRCODE = 33
----*/
/*---
INTERPRET BUFFER = Token unknown - line 6, column 17
ERRCODE = 1
----*/
/*---
INTERPRET BUFFER = .
ERRCODE = -1
----*/
I can't even use the fully qualified name of the table as a prefix.
/*---
PREPARE STATEMENT
TR_HANDLE = 87
STMT_HANDLE = 86
SELECT sh.ORDERID
, sh.DDATE
, sh.QTY
, sh.CU_ID
FROM SALES_HREF sh
FOR UPDATE OF sales_href.QTY
FIELDS = [ Version 1 SQLd 0 SQLn 30 ]
ERRCODE = 335544569
----*/
/*---
INTERPRET BUFFER =
ERRCODE = 17
----*/
/*---
INTERPRET BUFFER = Dynamic SQL Error
ERRCODE = 21
----*/
/*---
INTERPRET BUFFER = SQL error code = -104
ERRCODE = 33
----*/
/*---
INTERPRET BUFFER = Token unknown - line 6, column 25
ERRCODE = 1
----*/
/*---
INTERPRET BUFFER = .
ERRCODE = -1
----*/
I have to actually use the field name without any relation name or alias at
all:
/*---
ALLOCATE STATEMENT
DB_HANDLE = 1
STMT_HANDLE = 89
----*/
/*---
PREPARE STATEMENT
TR_HANDLE = 88
STMT_HANDLE = 89
SELECT sh.ORDERID
, sh.DDATE
, sh.QTY
, sh.CU_ID
FROM SALES_HREF sh
FOR UPDATE OF QTY
PLAN (SH NATURAL)
FIELDS = [ Version 1 SQLd 4 SQLn 30
SALES_HREF.ORDERID = <NIL>
SALES_HREF.DDATE = <NIL>
SALES_HREF.QTY = <NIL>
SALES_HREF.CU_ID = <NIL> ]
----*/
/*---
STATEMENT INFO
STMT_HANDLE = 89
SECONDS = 0.015
----*/
It works!
The next problem has to do with the update failing due to a conflict of
relation alias names between the live cursor and the update statement
against the live cursor.
/*---
ALLOCATE STATEMENT
DB_HANDLE = 1
STMT_HANDLE = 89
----*/
/*---
PREPARE STATEMENT
TR_HANDLE = 88
STMT_HANDLE = 89
SELECT sh.ORDERID
, sh.DDATE
, sh.QTY
, sh.CU_ID
FROM SALES_HREF sh
FOR UPDATE OF QTY
PLAN (SH NATURAL)
FIELDS = [ Version 1 SQLd 4 SQLn 30
SALES_HREF.ORDERID = <NIL>
SALES_HREF.DDATE = <NIL>
SALES_HREF.QTY = <NIL>
SALES_HREF.CU_ID = <NIL> ]
----*/
/*---
STATEMENT INFO
STMT_HANDLE = 89
SECONDS = 0.015
----*/
/*---
EXECUTE STATEMENT
TR_HANDLE = 88
STMT_HANDLE = 89
PARAMS = [ ]
----*/
/*---
OPEN CURSOR
STMT_HANDLE = 89
NAME = C12357620765374753
----*/
/*---
FETCH
STMT_HANDLE = 89
FIELDS = [ Version 1 SQLd 4 SQLn 4
SALES_HREF.ORDERID = <n> 1283
SALES_HREF.DDATE = <n> '14 Jan 1998'
SALES_HREF.QTY = 1
SALES_HREF.CU_ID = 101597 ]
----*/
/*---
EXECUTE IMMEDIATE
DB_HANDLE = 1
TR_HANDLE = 88
UPDATE SALES_HREF SH
SET QTY = ?
WHERE CURRENT OF C12357620765374753
PARAMS = [ Version 1 SQLd 1 SQLn 1
SALES_HREF.QTY = 2 ]
ERRCODE = 335544569
----*/
/*---
INTERPRET BUFFER =
ERRCODE = 17
----*/
/*---
INTERPRET BUFFER = Dynamic SQL Error
ERRCODE = 21
----*/
/*---
INTERPRET BUFFER = SQL error code = -204
ERRCODE = 54
----*/
/*---
INTERPRET BUFFER = alias SH conflicts with an alias in the same statement
ERRCODE = -1
----*/
I think this is a rather unexpected error message that indicates a problem
of some kind.
Rather than have it conflict, it should actually facilitate accurate mapping
of columns to what should be updated and what shouldn't. In the case of a
join, which should be made to work like it used to, this would be ideal to
make sure the exact column is updated that should be. Especially in the
case of a self-referencing table that is joined with itself. My testing
shows that updatable joins are no longer supported and I'll send a sample of
that if anyone so desires.
If I may, I'd like to explain why these issues are important to IBO.
The type of usage I am requiring here is a very server-centric model. In
short, it is a way to ensure that certain columns can be updated without any
other context than it is the current record (or records) fetched into the
cursor on the server and sent to the client (one at a time).
This is necessary for applications that are not trying to figure out primary
keys or DB_KEY's that would otherwise be required to submit "searched"
updates back to the server.
I realize it is a bit expensive in terms of sparse packets on the network
and server resources (open cursors) being held up while a dataset is
processed one record at a time. This drawback is known but traditionally
required based on very thin dumb clients.
I'm quite certain this will be viewed as a thorn in the flesh of those who
have worked very hard to tidy up and restructure both the handling of cursor
resources on the server, avoid inefficient network packets and simplify the
DSQL interface.
But, I really do need the UPDATE statements using WHERE CURRENT OF <cursor
name> processing of records. This feature worked quite well with datasets
involving joins. If someone would take my hand a bit there's a chance I
could get my feet wet in the Firebird code to see if I can spot how and
where things need to be restored. My guess is, and I certainly could be
wrong, that there are some places in code that are tagged for deprecation
but I'd like to stimulate interest in preserving this legacy feature. One
of my most favorite and long-standing customers is getting bit by this and
I'd like to rectify this so they don't have to make radical changes to work
around things.
All the best,
Jason L Wharton
www.ibobjects.com
|
