We have a PowerBuilder Client Server Application using SA11. We have a number of cases where there are user reports where the User can select a large number of items which need to form part of query join in the report. In some cases, literally hundreds of products might be included.
In order to facilitate this type of reporting we want to be able to take these individually selected items and insert them into a temporary table to be used as part of the join for the report.
The SA documentation seems a bit thin on use of temporary tables and our various experiences do not seem to correspond to the documentation as to behavior.
Ideally we would like a tablename (like #prodselect) that can be shared by multiple queries but each user would only see their own records. Some of the documentation alludes to this behavior being available but when we try each use see''s all the entries.
We realize that we could dynamically create uniquely named tables for each user report instance, but this creates various problems with the datawindows and queries.
Is there a worktable that each user can create and destroy (with the same name) that does not interfere with other users who might need to use a work table of the same name?
Any best practices and references to more detail on using temporary tables for this kind of requirement would be appreciated.
asked 29 Jun '11, 21:15
I would usually tend to base complex queries on stored procedures and would then use local temporary tables within in the procedure to "collect data". That works with the DECLARE LOCAL TEMPORARY TABLE statement - recommended with the NOT TRANSACTIONAL clause.
These temporary tables are connection-specific. When declared in stored procedures, they do only exist during the block/compound statement they're declared in. In such cases, these temporary table are not visible for users at all.
When you are looking for permanently accessibe temporary tables, use the CREATE GLOBAL TEMPORARY TABLE statement. Unless you use the SHARE BY ALL clause, each connection can only access its own data. You would create such a table once (just like a base table), and each user can make use of it. Note when using them, it's important to use the appropriate setting of the ON COMMIT clause - just make sure the data you report logic adds are still there when they are selected: By default, the rows of a temporary table are deleted on COMMIT, so an intermediate COMMIT might empty the temporary table for that connection. (Yep, been there, too.)
answered 30 Jun '11, 04:11
From the SA Docs
It seams that you did something wrong with your tests. I don't know Powerbuilder so a quick test with 2 iSql Connections will prove the documentation.
In Connection 1
create table #Test( t int ); insert into #test values (1); select * from #Test;
In Connection 2
create table #Test( t int ); insert into #test values (2); select * from #Test;
The result is that each select only shows one row.
answered 30 Jun '11, 07:51
Volker said it, but he did not SHOUT it: CREATE GLOBAL TEMPORARY TABLE T ( columns ) NOT TRANSACTIONAL
You CREATE it once, and the schema is global and permanent, and it behaves exactly like a regular permanent base table EXCEPT the data is not global or persistent. To each connection, the table is empty when the connection starts. The connection can only SELECT FROM T and UPDATE T and DELETE T what it INSERTs INTO t itself. Other connections cannot see that connection's data. When the connection ends, its data goes away.
It is a wonderful invention, exactly what you are asking for: "can be shared by multiple queries but each user would only see their own records".
If you come from the Transact SQL world, think of it as a CREATE TABLE with @@SPID as part of the primary key, and all the necessary code to purge rows matching on @@SPID when the connection starts and end, and an extra predicate in every WHERE clause to select rows matching on @@SPID... which is exactly what folks have to do when they use ASE ("those poor people!" - Galaxy Quest).
answered 30 Jun '11, 14:51
Thanks everyone for your responses:
We tried to follow the documentation which was pretty much as described above. We appreciate the pointers.
We tried to use it much as described in Thomas Duemesnil's 2 connection example above - but both queries saw the same data.
I will have to back track to see how we got off the rails.
answered 30 Jun '11, 14:15