Firebird

Hi all,

Le 27 mars 06 =E0 19:37, Greg Strauss a =E9crit :

> When I make a call to isc_dsql_describe_bind(), the XSQLVAR sqltype
> values that are returned do not have the null indicator flag set. Is
> this by design? To me, it looks like an oversight.

I would add two other possible oversights to this wagon.

1) The same thing happens for ordinary EXECUTE PROCEDURE statements. =20
The XSQLVAR sqltype values do not reflect that the null indicator =20
might be set on the input parameters.

2) In case of the EXECUTE BLOCK statement, there is another small =20
problem, the isc_info_sql_stmt_type returned by a call to =20
isc_dsql_sql_info declares such an EXECUTE BLOCK statement as being =20
an isc_info_sql_stmt_select. Is this really by design or oversight?  =20
Wouldn't it be better to return at least =20
isc_info_sql_stmt_exec_procedure if not a whole new distinct code?

--=20
Olivier Mascia

"Olivier Mascia" <om-lists@...> wrote:
>
> 2) In case of the EXECUTE BLOCK statement, there is another small problem, 
> the isc_info_sql_stmt_type returned by a call to
> isc_dsql_sql_info declares such an EXECUTE BLOCK statement as being
> an isc_info_sql_stmt_select.

If your block contains a SUSPEND statement, it's described as 
isc_info_sql_stmt_select, otherwise as isc_info_sql_stmt_exec_procedure. I 
think it's documented in the release notes. If not, it should be.

> Wouldn't it be better to return
> a whole new distinct code?

It would prevent all existing software from using the feature.


Dmitry

""Dmitry Yemanov"" ...
> "Olivier Mascia" wrote:
> >
> > 2) In case of the EXECUTE BLOCK statement, there is another small problem, 
> > the isc_info_sql_stmt_type returned by a call to
> > isc_dsql_sql_info declares such an EXECUTE BLOCK statement as being
> > an isc_info_sql_stmt_select.

    What kind of problem do you have with it ?

> If your block contains a SUSPEND statement, it's described as 
> isc_info_sql_stmt_select, otherwise as isc_info_sql_stmt_exec_procedure. I 
> think it's documented in the release notes. If not, it should be.

    Not a SUSPEND but an output parameters. Of course this is described in 
release notes and in doc\sql.extensions\README.execute_block

Regards,
Vlad

Vlad and Dmitry,

Thanks for your answers, but then what about this part of the  
original question:

> When I make a call to isc_dsql_describe_bind(), the XSQLVAR sqltype
> values that are returned do not have the null indicator flag set. Is
> this by design? To me, it looks like an oversight.

-- 
Olivier Mascia

> Vlad and Dmitry,
> 
> Thanks for your answers, but then what about this part of the  
> original question:
> 
> > When I make a call to isc_dsql_describe_bind(), the XSQLVAR sqltype
> > values that are returned do not have the null indicator flag set. Is
> > this by design? To me, it looks like an oversight.

    As i understand this is not an regression in FB2 code so i'm not sure
it must be fixed now (at RC stage). If there are strong needs it can be 
fixed (by myself or Dmitry) of course.

Regards,
Vlad

Le 27 mars 06 =E0 23:20, Vlad Horsun a =E9crit :

>> Vlad and Dmitry,
>>
>> Thanks for your answers, but then what about this part of the
>> original question:
>>
>>> When I make a call to isc_dsql_describe_bind(), the XSQLVAR sqltype
>>> values that are returned do not have the null indicator flag set. Is
>>> this by design? To me, it looks like an oversight.
>
>     As i understand this is not an regression in FB2 code so i'm =20
> not sure
> it must be fixed now (at RC stage). If there are strong needs it =20
> can be
> fixed (by myself or Dmitry) of course.

Well Vlad, I can't tell if that must be considered a strong needs or =20
not, especially compared to anything else.  Just that indeed, for =20
EXECUTE PROCEDURE, it is not a regression in FB2, it was as wrong in =20
FB 1.5.  But for EXECUTE BLOCK at least it is a nice new feature =20
introduced with a (yes, minor) defect. If that is sufficient to =20
decide to fix it for EXECUTE BLOCK, then it might as well be fixed =20
for EXECUTE PROCEDURE at the same time.

In layers of code between applications and the database API, it is =20
quite easy to correct on the fly, assuming that because the statement =20=

type is an EXECUTE PROCEDURE, the parameters descriptions can be made =20=

nullable even though the API didn't say so.  Though for the EXECUTE =20
BLOCK, the game becomes more tricky because it sometimes declares =20
itself as a SELECT statement and not always as an EXECUTE PROCEDURE.  =20=

There are no nice way to workaround this in layering code, other than =20=

parse the sql statement (which is not the job of such a layer).

I think there is much less risk to break applications by fixing this, =20=

late in the beta, than to release the software as is.  Some =20
application code will never be concerned, but any application =20
following, in confidence, what the engine describe about the =20
parameters (can be made null or not), will erroneously refuse to =20
assign a NULL to parameters.  This is already true in FB 1.5 for =20
EXECUTE PROCEDURE.  I'm not sure this (even very minor) defect should =20=

be promoted to the EXECUTE BLOCK.

--=20
Olivier Mascia

""Vlad Horsun"" <hvlad@...> wrote in message 
news:004b01c651e4$48406190$0201a8c0@......
>
>    As i understand this is not an regression in FB2 code so i'm not sure
> it must be fixed now (at RC stage). If there are strong needs it can be
> fixed (by myself or Dmitry) of course.
>

No, it's not critical per se, but it would be very convenient for sure. We 
have situations like this (over-simplified for the example; syntax may not 
be perfect... I'm doing this without the reference document nearby):

/* MYTABLE USES GENERATOR FOR PK IN TRIGGER; MUST RETURN ITS VALUE IN THIS 
BLOCK */
EXECUTE BLOCK (x VARCHAR(260) = ?) RETURNS (id BIGINT) AS
BEGIN
   INSERT INTO MYTABLE ColX VALUES (:x) RETURNING PK INTO :id;
   SUSPEND;
END

This block returns the new ID generated in MYTABLE as a result. However, due 
to the SUSPEND command, its treated like a SELECT statement, when really 
it's closer to a stored procedure in functionality. However, I have to do 
some hand-coding to the XSQLVAR parameter to allow it to be NULL since the 
dsql_describe_bind() command does not return this correctly.

My requests:

1) EXECUTE BLOCK should always be indicated as a 'stored procedure' since 
it's functionality more closely resembles a stored procedure than a SELECT 
statement. After all, consider:
CREATE PROCEDURE FOO RETURNS (X INTEGER) AS
BEGIN
  FOR SELECT ID FROM MYTABLE INTO :X DO
    SUSPEND;
END
Calling this stored procedure returns 'stored procedure' type in the info 
block, yet it behaves like a SELECT. Similarly, the EXECUTE BLOCK is like an 
'ad hoc' stored procedure.

AND/OR

2) Stored procedure and EXECUTE BLOCK paramters should return the 
XSQLVAR->sqltype with the NULL indicator in place, since NULL in almost all 
cases is a valid parameter value. If the use of this variable inside the 
block / SP is incorrect, then that is more of a programming logic problem.

Either one of these options would allow for a framework to deal with this 
issue in a generic way; otherwise we need to work around this on a query by 
query basis.

Just my $0.02 worth :)

Greg

> >> Vlad and Dmitry,
> >>
> >> Thanks for your answers, but then what about this part of the
> >> original question:
> >>
> >>> When I make a call to isc_dsql_describe_bind(), the XSQLVAR sqltype
> >>> values that are returned do not have the null indicator flag set. Is
> >>> this by design? To me, it looks like an oversight.
> >
> >     As i understand this is not an regression in FB2 code so i'm 
> > not sure
> > it must be fixed now (at RC stage). If there are strong needs it 
> > can be
> > fixed (by myself or Dmitry) of course.
> 
> Well Vlad, I can't tell if that must be considered a strong needs or 
> not, especially compared to anything else.  Just that indeed, for 
> EXECUTE PROCEDURE, it is not a regression in FB2, it was as wrong in 
> FB 1.5.  But for EXECUTE BLOCK at least it is a nice new feature 
> introduced with a (yes, minor) defect. If that is sufficient to 
> decide to fix it for EXECUTE BLOCK, then it might as well be fixed 
> for EXECUTE PROCEDURE at the same time.

    Ok, understand
 
> In layers of code between applications and the database API, it is 
> quite easy to correct on the fly, assuming that because the statement 
> type is an EXECUTE PROCEDURE, the parameters descriptions can be made 
> nullable even though the API didn't say so.  Though for the EXECUTE 
> BLOCK, the game becomes more tricky because it sometimes declares 
> itself as a SELECT statement and not always as an EXECUTE PROCEDURE.  

    Why the game becomes more tricky for EXECUTE BLOCK ?
describe_bind() always described input parameters despite of kind of statement

> There are no nice way to workaround this in layering code, other than 
> parse the sql statement (which is not the job of such a layer).

    Sorry, i don't understand. Can you show me example, please ?
 
> I think there is much less risk to break applications by fixing this, 
> late in the beta, than to release the software as is.  Some 
> application code will never be concerned, but any application 
> following, in confidence, what the engine describe about the 
> parameters (can be made null or not), will erroneously refuse to 
> assign a NULL to parameters.  This is already true in FB 1.5 for 
> EXECUTE PROCEDURE.  I'm not sure this (even very minor) defect should 
> be promoted to the EXECUTE BLOCK.

    One more question - i'm slightly confused about subject of discussion:
are we talking about nullable flag (least significant bit of XSQLVAR::sqltype)
or about NULL-indicator (XSQLVAR::sqlind) which is proposed to set to -1
by dsql_describe_bind() ?

Regards,
Vlad

PS proposed fix to make all input parameters nullable for EXECUTE BLOCK

cvs -z9 diff -u -wb dsql\pass1.cpp (in directory E:\FB2\fb2.head\src\)
Index: dsql/pass1.cpp
===================================================================
RCS file: /cvsroot/firebird/firebird2/src/dsql/pass1.cpp,v
retrieving revision 1.259
diff -u -w -b -r1.259 pass1.cpp
--- dsql/pass1.cpp 14 Mar 2006 10:57:11 -0000 1.259
+++ dsql/pass1.cpp 28 Mar 2006 21:58:17 -0000
@@ -748,6 +748,8 @@

        DEV_BLKCHK(field, dsql_type_fld);
        DEV_BLKCHK(temp, dsql_type_nod);
+
+      field->fld_flags |= FLD_nullable;
        MAKE_desc_from_field(&(desc_node->nod_desc), field);
        set_parameter_type(request, temp, desc_node.get(), false);
    } // end scope

***** CVS exited normally with code 1 *****

    Objections ?

Le 29 mars 06 =E0 00:11, Vlad Horsun a =E9crit :

>> In layers of code between applications and the database API, it is
>> quite easy to correct on the fly, assuming that because the statement
>> type is an EXECUTE PROCEDURE, the parameters descriptions can be made
>> nullable even though the API didn't say so.  Though for the EXECUTE
>> BLOCK, the game becomes more tricky because it sometimes declares
>> itself as a SELECT statement and not always as an EXECUTE PROCEDURE.
>
>     Why the game becomes more tricky for EXECUTE BLOCK ?
> describe_bind() always described input parameters despite of kind =20
> of statement
>
>> There are no nice way to workaround this in layering code, other than
>> parse the sql statement (which is not the job of such a layer).
>
>     Sorry, i don't understand. Can you show me example, please ?

describe_bind describes parameters and application code likes to rely =20=

on that information.
The problem is that the description of input parameters made by =20
describe_bind for an EXECUTE PROCEDURE does not mention that the =20
parameters can be NULL. So the application code (or more exactly the =20
interface code between the application and the API), following =20
blindly the 'truth' returned by describe_bind 'thinks' it is right to =20=

deny storing a NULL in those input parameters.

There is a trick to workaround this, it is to cheat and consider that =20=

because the statement type is EXECUTE PROC then all the n put =20
parameters can be made null. So after the call to describe_bind the =20
interface cheats and updates the types for their +1 values, and the =20
rest of the system then agrees on storing NULLs in there.

Facing the EXECUTE BLOCK which sometimes describes itself as a SELECT =20=

(which it is not anyway, at best it is the equivalent of a SELECT =20
PROCEDURE, not really a SELECT) makes it harder to implement that =20
same trick.

Okay a true SELECT statement wouldn't announce input parameters =20
anyway. So maybe a trickier trick can be found.

But it would so much simpler for everyone if describe_bind took care =20
of declaring all input parameters (if any) as NULLABLE when the =20
statement is an EXECUTE PROCEDURE or an EXECUTE BLOCK. If that is the =20=

case, then the discussion of the kind of statement type of the =20
EXECUTE BLOCK drops all by itself. It is not part of the equation =20
anymore.

Hope this makes it clearer,

--=20
Olivier Mascia

Le 29 mars 06 =E0 00:11, Vlad Horsun a =E9crit :

>     One more question - i'm slightly confused about subject of =20
> discussion:
> are we talking about nullable flag (least significant bit of =20
> XSQLVAR::sqltype)
> or about NULL-indicator (XSQLVAR::sqlind) which is proposed to set =20
> to -1
> by dsql_describe_bind() ?

nullable flag indeed.
(least significant bit of XSQLVAR::sqltype which should be 1 for =20
input parameters of an EXECUTE PROCEDURE *and* EXECUTE BLOCK).

--=20
Olivier Mascia

SGVsbG8sDQoNCkkgbWF5IGJlIHdyb25nIGJ1dCBpcyB0aGVyZSBhbnkgYWN0dWFsIG5lZWQgdG8g
ZGVzY3JpYmUgaWYgaW5wdXQgcGFyYW1hdGVycyB0byBhbnkga2luZCBvZiBzdGF0ZW1lbnQgbWF5
IGNvbnRhaW4gTlVMTHMgb3Igbm90ID8NCg0KVGhlIG1lYW5pbmcgb2YgJ251bGxhYmxlJyBiaXQg
aXMgc29sZWx5IHRvIHRlbGwgd2hldGhlciAnc3FsaW5kJyBmaWVsZCBzaG91bGQgYmUgaW5zcGVj
dGVkIGJ5IHRoZSBjb3VudGVyIHBhcnR5LiBJdCBoYXMgbm8gbWVhbnMgdG8gcHJvdmlkZSBpbmZv
cm1hdGlvbiBhYm91dCB3aGV0aGVyIE5VTEwgaXMgYSBsZWdpdGltYXRlIHZhbHVlIGF0IHRoZSB0
YXJnZXQgY29udGV4dC4gQWx0aG91Z2ggc3VjaCBpbmZvcm1hdGlvbiBfaXNfIHByb3ZpZGVkIGJ5
IEFQSSBmb3Igc2ltcGxlIHN0YXRlbWVudHMsIGl0IGNhbm5vdCBiZSByZWxpYWJsZS4gRm9yIGV4
YW1wbGUsIGNvbnNpZGVyIGFuIElOU0VSVCBzdGF0ZW1lbnQgd2l0aCBhIG5vbi1udWxsYWJsZSBm
aWVsZCB3aXRoIGEgdHJpZ2dlciBkZWZpbmVkIHRoYXQgY2hhbmdlcyBOVUxMIHRvIHNvbWUgcmVh
c29uYWJsZSB2YWx1ZS4gQWZ0ZXIgYWxsLCBhbiBhcHBsaWNhdGlvbiBtYXkgbGVnaXRpbWF0ZWx5
IHBhc3MgTlVMTCB0byBub24tbnVsbGFibGUgcGFyYW1ldGVyIGFuZCBnZXQgY29uc3RyYWludCB2
aWFsb2F0aW9uIG1lc3NhZ2UuDQoNClNvIG15IHZpZXcgaXMgdGhhdCBhbnkgbWlkZGxld2FyZSBj
b2RlIHNob3VsZCBhc3N1bWUgYW55IGlucHV0IHBhcmFtZXRlcnMgYXJlIG51bGxhYmxlIGFuZCBz
ZXQgdGhlICdudWxsYWJsZScgYml0IGZvciB0aGVtLiANCg0KUmVnYXJkcywNCkFsZWtzZXkgS2Fy
eWFraW4NCg0KDQoNCiJPbGl2aWVyIE1hc2NpYSIgPG9tLWxpc3RzQHRpcGdyb3VwLmNvbT4gd3Jv
dGUgaW4gbWVzc2FnZSBuZXdzOjk3QUIwQzczLTk0NkEtNEJDMy05RTcxLTBDNDZCOTdCNkMwOEB0
aXBncm91cC5jb20uLi4=

U29ycnkgZm9yIHRoZSBwcm9ibGVtIHdpdGggbWVzc2FnZSBlbmNvZGluZy4NCg0KDQoNCkhlbGxv
LA0KDQpJIG1heSBiZSB3cm9uZyBidXQgaXMgdGhlcmUgYW55IGFjdHVhbCBuZWVkIHRvIGRlc2Ny
aWJlIGlmIGlucHV0IHBhcmFtYXRlcnMgdG8gYW55IGtpbmQgb2Ygc3RhdGVtZW50IG1heSBjb250
YWluIE5VTExzIG9yIG5vdCA/DQoNClRoZSBtZWFuaW5nIG9mICdudWxsYWJsZScgYml0IGlzIHNv
bGVseSB0byB0ZWxsIHdoZXRoZXIgJ3NxbGluZCcgZmllbGQgc2hvdWxkIGJlIGluc3BlY3RlZCBi
eSB0aGUgY291bnRlciBwYXJ0eS4gSXQgaGFzIG5vIG1lYW5zIHRvIHByb3ZpZGUgaW5mb3JtYXRp
b24gYWJvdXQgd2hldGhlciBOVUxMIGlzIGEgbGVnaXRpbWF0ZSB2YWx1ZSBhdCB0aGUgdGFyZ2V0
IGNvbnRleHQuIEFsdGhvdWdoIHN1Y2ggaW5mb3JtYXRpb24gX2lzXyBwcm92aWRlZCBieSBBUEkg
Zm9yIHNpbXBsZSBzdGF0ZW1lbnRzLCBpdCBjYW5ub3QgYmUgcmVsaWFibGUuIEZvciBleGFtcGxl
LCBjb25zaWRlciBhbiBJTlNFUlQgc3RhdGVtZW50IHdpdGggYSBub24tbnVsbGFibGUgZmllbGQg
d2l0aCBhIHRyaWdnZXIgZGVmaW5lZCB0aGF0IGNoYW5nZXMgTlVMTCB0byBzb21lIHJlYXNvbmFi
bGUgdmFsdWUuIEFmdGVyIGFsbCwgYW4gYXBwbGljYXRpb24gbWF5IGxlZ2l0aW1hdGVseSBwYXNz
IE5VTEwgdG8gbm9uLW51bGxhYmxlIHBhcmFtZXRlciBhbmQgZ2V0IGNvbnN0cmFpbnQgdmlhbG9h
dGlvbiBtZXNzYWdlLg0KDQpTbyBteSB2aWV3IGlzIHRoYXQgYW55IG1pZGRsZXdhcmUgY29kZSBz
aG91bGQgYXNzdW1lIGFueSBpbnB1dCBwYXJhbWV0ZXJzIGFyZSBudWxsYWJsZSBhbmQgc2V0IHRo
ZSAnbnVsbGFibGUnIGJpdCBmb3IgdGhlbS4gDQoNClJlZ2FyZHMsDQpBbGVrc2V5IEthcnlha2lu
DQoNCg==

I hope this one will be ok, sorry.


Hello,

I may be wrong but is there any actual need to describe if input paramaters 
to any kind of statement may contain NULLs or not ?

The meaning of 'nullable' bit is solely to tell whether 'sqlind' field 
should be inspected by the counter party. It has no means to provide 
information about whether NULL is a legitimate value at the target context. 
Although such information _is_ provided by API for simple statements, it 
cannot be reliable. For example, consider an INSERT statement with a 
non-nullable field with a trigger defined that changes NULL to some 
reasonable value. After all, an application may legitimately pass NULL to 
non-nullable parameter and get constraint vialoation message.

So my view is that any middleware code should assume any input parameters 
are nullable and set the 'nullable' bit for them.

Regards,
Aleksey Karyakin




""Aleksey Karyakin"" <karyakin@...> wrote in message 
news:e0dlhg$lab$1@......
> U29ycnkgZm9yIHRoZSBwcm9ibGVtIHdpdGggbWVzc2FnZSBlbmNvZGluZy4NCg0KDQoNCkhlbGxv

> I may be wrong but is there any actual need to describe if input paramaters 
> to any kind of statement may contain NULLs or not ?

    I have the same wonder ;)
 
...
> So my view is that any middleware code should assume any input parameters 
> are nullable and set the 'nullable' bit for them.

    While i completely agree with you fix for EXECUTE BLOCK 
is commited already :)

Regards,
Vlad

Le 29 mars 06 =E0 11:55, Aleksey Karyakin a =E9crit :

> I hope this one will be ok, sorry.

For me at least, the very first one was perfectly readable as well as =20=

the two others. I don't know what encoding issue you thought you had.

> Hello,
>
> I may be wrong but is there any actual need to describe if input =20
> paramaters
> to any kind of statement may contain NULLs or not ?
>
> The meaning of 'nullable' bit is solely to tell whether 'sqlind' field
> should be inspected by the counter party. It has no means to provide
> information about whether NULL is a legitimate value at the target =20
> context.
> Although such information _is_ provided by API for simple =20
> statements, it
> cannot be reliable. For example, consider an INSERT statement with a
> non-nullable field with a trigger defined that changes NULL to some
> reasonable value. After all, an application may legitimately pass =20
> NULL to
> non-nullable parameter and get constraint vialoation message.
>
> So my view is that any middleware code should assume any input =20
> parameters
> are nullable and set the 'nullable' bit for them.

I must agree that this view of the topic is indeed... interesting and =20=

very logical.


Le 29 mars 06 =E0 12:16, Vlad Horsun a =E9crit :

>> I may be wrong but is there any actual need to describe if input =20
>> paramaters
>> to any kind of statement may contain NULLs or not ?
>>
>
>     I have the same wonder ;)
>
> ...
>
>> So my view is that any middleware code should assume any input =20
>> parameters
>> are nullable and set the 'nullable' bit for them.
>>
>
>     While i completely agree with you fix for EXECUTE BLOCK
> is commited already :)

Thank you very much, anyway.

--=20
Olivier Mascia

> >     While i completely agree with you fix for EXECUTE BLOCK
> > is commited already :)
> 
> Thank you very much, anyway.

    And thank you for patience and explanations ;)

Regards,
Vlad