At 02:20 AM 5/12/2010, Michael Ludwig wrote:

>Hi Helen,
>
>thanks for this post. I haven't encountered too much guidance on
>writing Firebird-PSQL yet, like best practices and so on. I'd be
>thankful for any recommendations anyone might have in that regard,
>like pointers to documentation.

I think that would be hard! Any "best practices" are driven by your own requirements and test results. (My own are driven by complete paranoia about data consistency! From years of experiences as a support person, I am only too well aware that others have different priorities...the *overriding* one being to save typing!!!)

>The chapters on "Server Programming"
>in your book are good, but there's room for more specialized advice.

<sunday_sermon>
There's always room for more specialized advice in any technical field - even those where people are actually employed to write documentation and receive money for it. That's reality and it's one that causes Firebirdies a lot of pain.

When people are getting the software for nothing, often getting free technical advice just for the asking, being offered constant opportunities to get the software improved just by asking reasonably, there's quite an onus to "give back" if you can. Not all users of Firebird are smart and articulate, alas. However, since you are clearly a person who is both, I have often noted your potential value to Firebird as a documenter! ;-)
</sunday_sermon>

>I also have a couple of questions with respect to your explanations.
>Please see my comments inline.

Sorry I'll have to be vague, brief, or both, as I have deadlines screaming...

>> b) you want to return values from a sub-procedure back to local
>> variables in a calling procedure
>
>What's the rationale for preferring an executable SP in this
>scenario?

Less work for the engine to do. Does it matter for a trivial little thing like the OP's example? not at all. But if you have a sub-proc that is plodding its way through a loop, it matters.

>> Understand what SUSPEND actually *does*. It sends a result row
>> out to the client buffer and waits for the client app to fetch the
>> row. Execution of the SP is *suspended* until the waiting row has
>> been fetched.
>
>This sounds like some IPC, possibly even a network transaction, is
>needed for each row. But is that so?

I don't know. You could ask on firebird-devel if it is important for you.

>In your book on pp.617, 618
>(schema) and 620, you mention the "row cache". My understanding was
>that rows are buffered there and fetched in batches, not unlike the
>way an IO buffer works. But I may well have misunderstood.

There are ways that client apps can "batch" fetches. The engine delivers output rows from a selectable SP one by one and keeps the execution flow of the SP suspended until it hears that the client is no longer waiting for the current contents of the output row structure.

>> Furthermore, there are some good reasons why you shouldn't write
>> SPs that both change data and return sets.
>
>What are those reasons? I can think of user expectation. Are there
>other reasons?

The rows output from a SP are not real data. They are a contrived set of values that reflect the state of the underlying data within the context of the user's (currently unfinished) transaction. Thus, if you hand SP output to the client app during the course of a data-changing operation and the client makes decisions according to those values, you run the risk of writing bad data if the SP excepts.

>> >usage as (SELECTABLE) STORED PROCEDURE:
>> >execute procedure MYPROC( PARAM) returning_values :RESULT
>>
>> This is not usage as a selectable SP. The SUSPEND in this
>> procedure, when invoked with EXECUTE PROCEDURE, is a no-op
>> (actually, it just behaves as EXIT).
>
>Except for (at least) one aspect: Omitting SUSPEND will deny you the
>possibility to SELECT from the procedure.

I don't get that point. The OP described this call as "usage as (SELECTABLE) STORED PROCEDURE". EXECUTE PROCEDURE is usage as EXECUTABLE stored procedure, not selectable.

If there is no SUSPEND, a procedure won't return a result from a SELECT call. If it has output arguments, it will return a result of zero or one rows from an EXECUTE PROCEDURE call. Note that the XSQLVAR structure for EXECUTE commands is *different* to that for SELECTs. There have been some API drivers around that don't support that, so it seemed to me there has been a culture of making all SPs selectable ones on principle. ;-)

>Which makes me think that
>a *selectable* procedure (one you can SELECT from) is not necessarily
>one that contains a SELECT itself, but rather one that contains a
>SUSPEND statement.

Wrong thinking, a bit. A selectable SP is one that returns a result set from a SELECT call. It doesn't have to be one that contains a SELECT call inside it.

To be a selectable SP it must
a) have output parameters declared (and loaded, of course!!)
and
b) be written with a SUSPEND after every block that should return a row.

>So a selectable procedure is a SUSPEND-procedure.

That's half of it. ;-) See a) above.

> With the proviso
>that in cases such as above, as you point out, using SUSPEND is not
>recommended; the recommended way in this case, IIUC, is to just call
>EXECUTE PROCEDURE and not to think SELECT would be nicer. Correct?

Yes - of course with the proviso that, if you want a result set back (a set of zero or one rows) from EXECUTE PROCEDURE, it will need to be prepared correctly at the client side.

>> >usage as SELECTABLE STORED PROCEDURE:
>> >select * from MYPROC( 19.0 ) into :RESULT;
>>
>> Hmm, not a great idea. Sometimes it will work (as this one would,
>> since it is scalar) but sometimes you will get unpleasant surprises.
>
>A scalar, i.e. its return value is a single row

A scalar is a return value that is singular at both row and column.

>, not a table

SELECTs do not return tables. They return sets. Always.

>; its cardinality is 1;

The cardinality of a scalar is irrelevant.

>a fact which is not known at compile-time, so it is prone to runtime errors.

Untrue.

>In contrast to the cardinality, the degree (number of columns)

"Degree number" is the left-to-right position of a column in a set. A scalar has only one possible value: 1.

>of the SP *is* known to the caller, so you'll get a useful error
>message straight away:
>
> -SQL error code = -313
> -count of column list and variable list do not match

That error is the response to a specific *coding* error in PSQL. It means that, in a [FOR] SELECT <list-of-columns>...INTO <list-of-variables>... construct, there is a mismatch between the column counts in the <list-of-columns> and the <list-of-variables>.

At compile time, the engine knows everything about the structure of the output set. In fact, if you try to create a SP in which anything about the output structure is indeterminate, it won't compile.

>> SSPs are for returning multi-row sets to a client or (if you must)
>> returning a multi-row set to a cursor (fairly inefficiently).
>
>Ah, right, because there are no table-valued variables as of FB 2.5,
>so other than cursors and tables (including global temporary tables)
>there is no facility to receive the result of a SSP. Which is why
>you're saying that they're really meant for the client, correct?

Well, sort of, in a way, hmmmmm. Your statement doesn't hold totally true...think about derived tables and named cursors (which have been with us since v.2.0).

I consider it a mistake to be talking about "table variables": it's muddles the thinking. In SQL, the only thing that is a "table" is the set that is physically stored on the disk according to the specs laid out by its CREATE TABLE definition. Cursors, GTT instances, views, derived tables, stored procedure outputs all are sets. Variables are supported for cursors and derived tables, probably for not much better reason than that the SQL standard defines syntaxes that require (or optionally allow) them.

>> >usage of a UDF
>> >:RESULT = udf_myFunc( 19.0 );
>>
>> That is not a valid syntax. You can do something a bit like this
>> in DSQL with a scalar SSP, viz.,
>>
>> select udf_myFunc( 19.0 ) RESULT from rdb$database;
>
>This syntax doesn't work with a scalar SSP. I think you meant a
>function here?

Yes, that's why I used udf_myFunc instead of udf_MyProc that the OP used.