At 04:02 AM 12/5/2004, Lester Caine wrote:

> >
> > 1) creating and maintaining connection private data that
> > can be queried and updated like data in the database.
> >
> > 2) creating tables (permanent or temporary) that look like
> > and may contain the data from query result sets.
> >
> > 3) establishing a private name space.
> >
>As I see it the only difference between 1 and 2 is the visibility of the
>results.

Uhmmm, no. Or rather that's not what I meant to say. 1 should
refer to the contents of the tables while 2 and 3 refer to the
definitions of tables.

> ! - data is never visible outside the connection

right.

> ( is that the
>same as a transaction or across transactions from the same connection? I
>never understand the difference - in PHP the connection may be pooled by
>the driver so several people use it ;) ).

To the (very limited) extent that I understand the SQL standards,
they consider a connection (aka SQL-session) to be a monogamous
relationship between one client and one database, a relationship
which may procreate one or more transactions. Connection pooling
is to the SQL standard as polygamy is to Southern Baptist
evangelicals.

> While 2 - data can be used
>elsewhere, or just by the creator? What *IS* the difference between
>permanent and temporary?

The difference between permanent tables and temporary tables, in
the standard is that data in permanent tables becomes a permanent,
shared part of the database when committed. Data in temporary tables
disappears when the transaction that created it ends (in the case of
temporary tables without the PRESERVING ROWS qualification) or when
the connection ends if PRESERVING ROWS is specified.

>If the schema limitation was changed, could both of these in effect be
>'views' created and destroyed in the correct 'space', with just a cache
>of the generated data?

The "schema limitation" can be changed in a number of different
ways. The implementation I'm slightly familiar with is Oracle's,
which implicitly uses the login user name as the schema name.
Let me tell you the number of ways that won't work with connection
pooling or the practice of allowing everyone to login through the
same account.

>My own use of 'temporary tables' is probably of the type 'results AS
>ABCD' where I can the use ABCD in the next query to further process
>things, but at present I have to write 'results' to 'ABCD' which I have
>created as a 'real' table, so I can use them.

I've got an idea for an extra-standard ephemeral "table" thingy
that may meet your needs and Volker's without perturbing the
various standard issues we've been dancing around. Writing it
down so it doesn't conflict with standard SQL syntax given the
size of that language and the tendency of the writers to use
both a descriptive term (e.g. <table definition>) with one set
of words and the actual syntax (e.g. CREATE TABLE ...) with a
different set.



Regards,

Ann
www.ibphoenix.com
We have answers.