Hi,

At September 18, 2004, 17:12, Milan Babuskov wrote:

> Daniel Rail wrote:
>> Looking through the documentation, a SQL Client module is a plugin
>> that contains externally invoked procedures. At least that's what I
>> understood of the documentation after reading it a few times. And,
>> I don't think that I will be quoting it, because of it's length.

> Ok. So, to try to summarize, during a single SQL-session multiple
> SQL-client modules can use temporary tables but modules can't see the
> data of each other.

Correct.

> This said, it really looks like temporary tables should not be bound to
> username, but to a single connection.

Correct.

>>>I guess in every SQL statement?
>>
>> That would be my guess, if SQL Client modules would be implemented and
>> used.

> Which they are currently not (in Firebird)?

The only thing that seems to come close to SQL Client modules, is
UDFs. SQL Client modules are declared via SQL syntax.

> Or, if I understood it
> correctly, SQL client module is actually an instance of client library,
> like GDS.DLL for example?

No, a SQL Client module is not the same thing as a client-connection
library. I find the name SQL Client module misleading.

Just to give you an idea as what I mean by what I just said. Here's
the SQL syntax for the SQL Client module definition:

<SQL-client module definition> ::=
<module name clause> <language clause> <module authorization clause>
[ <module path specification> ]
[ <module transform group specification> ]
[ <module collations> ]
[ <temporary table declaration>... ]
<module contents>...

<module authorization clause> ::=
SCHEMA <schema name>
| AUTHORIZATION <module authorization identifier>
[ FOR STATIC { ONLY | AND DYNAMIC } ]
| SCHEMA <schema name> AUTHORIZATION <module authorization identifier>
[ FOR STATIC { ONLY | AND DYNAMIC } ]

<module authorization identifier> ::= <authorization identifier>

<module path specification> ::= <path specification>

<module transform group specification> ::= <transform group specification>

<module collations> ::= <module collation specification>...

<module collation specification> ::=
COLLATION <collation name> [ FOR <character set specification list> ]

<character set specification list> ::=
<character set specification> [ { <comma> <character set specification> }... ]

<module contents> ::=
<declare cursor>
| <dynamic declare cursor>
| <externally-invoked procedure>

>>>>7) If ON COMMIT is not specified, then ON COMMIT DELETE ROWS is
>>>>implicit.
>>
>>>And if it is? What other could happen to the rows?
>>
>> The other commit action is PRESERVE.

> I expected something like that, but PRESERVE for what or who? Make it
> persistent in database for everyone? or just that user? or?

Persistent to the connection. You have to remember that if you have
ON COMMIT DELETE ROWS, the rows of the temporary table associated with
the transaction being committed will be deleted. If you have ON
COMMIT PRESERVE ROWS, then the rows that were inserted will be
preserved beyond the transaction. But, the data will not persist once
the connection is closed.

> Since the word COMMIT is used, it kind-of applies that this should be
> used in context of transaction? Perhaps they meant:

> - start the transaction
> - create a temporary table (with option to drop it on commit)
> - do something with it
> - commit (temp. table may be dropped or not)
> - if not, the temp table is there for the next transaction
> - when user disconnects from database, drop it

And if I read correctly, a temporary table might also be created by
another connection, and used by everybody. But, the data inserted
during the connection or transaction cannot be viewed by another
connection or transaction, depending on the context of how the user
declared the temporary table. And, the data will be deleted in the
same manner.

>> I try to understand the documentation, but because of how it is
>> written, a person has to read the information a few times to
>> understand(at least start to) what it means.

> It really seems that way.

As you can see, the information can be interpreted in different ways,
yet it should mean the same for everybody.

--
Best regards,
Daniel Rail
Senior System Engineer
ACCRA Group Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.filopto.com)