At 07:40 AM 07-06-01 +0000, John Vander Reest wrote:

>Hi Helen,
> I'm new to this News-Group and would like to expand and add to the
>questions already posed.

I'm horribly pressed for time right now so others please come in and help. I'll answer briefly.

> >
> > Creating temporary tables is rarely necessary with InterBase
>(unlike
> >e.g. MSSQL, which has no other means to generate a multi-row result
> >set from a stored proc.)
>
>I don't fully understand this. Does this mean that IB/FB have an
>alternate way of chaining queries. What I'm saying is normally I
>would
>perform an SQL and create a result set which I would then save in a
>temporary table to be used as the input to another query. The only
>alternative that I can see is to build HUGE (and unmaintainable) SQL
>statements. The question is then, do I do this differently in IB/FB.

Yes. In a Stored Proc, use FOR...SELECT...INTO <variable list> DO... to create the intermediate result set and form input vars to your other queries. Use the SUSPEND; statement at the end of the loop to return a list of outputs as a dataset. At run-time, pass inputs to the SP and SELECT it.

> >It's also a bad idea in a transactional
> >RDBMS to have users creating and destroying objects.
>
>
>Could you please elaborate? I would really like to know your
>reasoning
>on this one - it is a security concern, a performance concern or
>something else?

All of the above. M$SQL allows it because its architecture requires it.

> > If you don't need to bring the temporary data to the user
>interface,
> >don't have a Temp table at all. In InterBase, you can perform table
> >updates that read data from one or more tables in one or more other
> >tables using a stored procedure, without creating an intermediate
> >on-disk structure at all, using the FOR SELECT...INTO...DO construct.
>
>Is there any way of doing this without creating a stored procedure?

It depends on your requirements. Another approach is to create a view that outputs your intermediate data set and JOIN to it.

I have an additional question which is somewhat related to temporary

>tables. If I create an SQL statement using a "LEFT OUTER JOIN" on
>two
>tables and therefore end up with NULL values in some of my fields in
>my result set, what happens if I use that result set to join to yet
>another table on a field with possible NULL values. Does a join on a
>field with a NULL value always return an emtpy result set, or do I
>get
>all the rows that meet the NULL criteria.

You won't get rows returned as a result of matching NULL to NULL. NULL = NULL is not a valid equivalence.

>I am very new to Interbase and am currently evaluating it as a
>replacement for MS-SQLServer, so you can imagine that I am keen to
>work out all the issues that may confront me before making the switch.

Fair enough. Don't be afraid of SPs in InterBase. They simply don't carry the overhead of complexity comparable with what you have experienced with M$SQL. As InterBase/Firebird stand currently, you will miss COALESCE() if you have relied on it a lot. Many expected functions are not present in the core engine - you need to bring them into your database by declaring UDFs (user-defined functions). A library of standard UDFs comes with the kit; there are several good third-party libraries around the resource sites, too.

The IB/FB engine is lean where M$SQL is bloated; IB/FB is bomb-proofed in places where M$ leaves integrity gaps (e.g. SPs must be compiled before they can be called...); there is no MTS support for IB/FB yet; IB/FB supports stacks of (numbered, positioned) triggers in each trigger event; IB/FB replaces IDENTITY-attributed columns with transaction-independent generators...and more...

Regards,
Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________