Filip Sergeys wrote :
>Hello,
>I have 3 questions about temporary tables:
>(The dbproc they are related to is at the end of this mail.)
>1) updatebility
>In a dbproc I have 3 subsequent queries:
> the first query creates a temp table and populates it with data from
>two named_select_statements create just before
> the second part is a loop that takes data from multiple rows,
>concatenates them into a variable and then updates a column in the temp
>table with that variable
> the third and last query selects everything from the temp table.
>=>RESULT, the returned rows are not updated.
There are no restrictions for updates on temp tables. Please secure
that the loop containing the update statement has really been executed
and that the update statement does not return error 100.
I would recommend to have a look into the vtrace for that.
Besides, there's a problem in the loop. You should check $rc after the
fetch statement :
WHILE $rc<>0 DO BEGIN
FETCH INTO :requestid;
IF $rc = 0
THEN
BEGIN
END;
>I tried to add a `commit;` after the update statement but then the
>dbproc would not compile.
The commit statement is not permitted inside db-procedures. Besides a
commit would not change anything in your example.
>Based on this text (http://www.sapdb.org/sap_db_features.htm)
>`Temporary tables that will be destroyed if the application ends the
>session. These tables can be created within a stored procedure and
>selected from outside this procedure. They are updateable, although
>there is no implicit update of the rows that created the temporary
>table.`
>I thought is was possible to update temp tables, but something needs a
>few words explanation:
>=> `they are updateable, although there is no implicit update ...`
>What does that mean ?
I think this means that you have to code update statements explicitly,
if you want to make any changes in the temp table.
But this is just my interpretation.
>2) For the same dbproc. In any isolation level I run the dbproc, the
>first time it returns a result, the second time I gives an error that
>the temp table already exists.
>I thought the temporary tables where destroyed automatically. So I added
>a `drop table temp.boreqselect` at the end of the dbproc, but that
>crashed my sqlstudio instantly (it compiles without a problem though)
>So what is the correct coding way. Delete explicitly or let the database
>take care, but why is it still there then after the execution?
This is the expected behavior. Temporary tables are dropped automatically
at session end, not at db-procedure end.
If you drop the table at the end of the db-procedure, you drop the data
for the cursor. It's clear that this causes problems.
There are 2 possibilities the solve the problem :
1. drop the temporary table when you enter the db-procedure. You may
need dynamic sql for this job :
VAR
stmt char(100);
stmt = 'DROP TABLE TEMP.BOREQSELECT';
execute stmt;
As a disadvantage of this solution the temporary tables lives longer than
needed.
2. create a 'copied' cursor as the result of your db-procedure, i.e.
DECLARE :$cursor CURSOR FOR SELECT * FROM TEMP.BOREQSELECT FOR REUSE;
After that you can drop the temporary table without affecting the
cursor. The copied cursor of course will cost some space and cpu time,
depending on the size of your result set.
>3) Something which is not entirely clear:
>Are temporary tables unique to a session. In other words, if the dbproc
>is executed multiple times a the same time, will each have its own
>instance? I'm not entirely sure anymore after reading this:
>`These tables can be created within a stored procedure and selected from
>outside this procedure` This would suggest they are not.
>What is the solution then. Create unique tablenames with STAMP?
Yes, names for temporary tables must be unique in a session. If you
execute a db-procedure creating a temporary table multiple times,
this will result in error -6000, if the tables has not been dropped
between two procedure calls.
You may create unique table names, but I think that's not the solution,
because after many calls many temporary tables will litter your database.
Please see above for my suggestion.
HTH,
Thomas
>I use maxdb 7.5 on SuSE linux 9
>And sqlstudio 7.5 on mickeysoft windows2000.
>The relevant part of the dbproc:
>====start=======
>TRY
>CREATE TABLE TEMP.BOREQSELECT AS SELECT A.REQUESTID as REQUESTID,
>A.OFFICEID, A.EMPLOYEEID, A.TIMESTAMP, A.TYPE, A.CLIENTNR, A.TITLE,
>A.FIRSTNAME, A.LASTNAME, A.BIRTHDATE, A.STREET, A.STREETNO, A.ZIP,
>A.CITY, B.RISKOBJECT, '' AS GUARANTEETXT
>FROM DELTA.PHYSDATA A, DELTA.RISKOBJECTDATA B
>WHERE A.REQUESTID=B.REQUESTID;
>CATCH
>IF $rc <> 0 THEN STOP ($rc, 'unexpected error in create
>temp.boreqselect');
>TRY
>SELECT DISTINCT(A.REQUESTID) AS REQUESTID
>ROM DELTA.T_REQUEST A, DELTA.T_REQUEST B, COMMON.T_OFFICE B1,
>COMMON.T_EMPLOYEE B2, DELTA.T_REQUEST C
>WHERE B.REQUESTID=A.REQUESTID
>AND B1.OFFICEID=A.VALUE
>AND B2.EMPLOYEEID=B.VALUE
>AND C.REQUESTID=A.REQUESTID
>AND A.KEY = 'OFFICEID'
>AND A.VALUE = :officeid
>AND B.KEY = 'EMPLOYEEID'
>AND B.VALUE = :employeeid
>AND B1.KEY = 'DESCRIPTION'
>AND B2.KEY = 'NAME'
>AND C.KEY = 'REQUEST_TYPE'
>AND C.VALUE= :reqtype;
>WHILE $rc<>0 DO BEGIN
> FETCH INTO :requestid;
> SELECT C.TXT
> FROM DELTA.L_RQTE3GUARANTEE A, COMMON.T_E3GUARANTEE B,
>COMMON.T_MESSAGELNG C
> WHERE A.IDE3=B.IDE3
> AND B.SEQMSGTEXT = C.SEQMSG
> AND A.REQUESTID = :requestid
> AND C.SEQLNG = :language;
> WHILE $rc<>0 DO BEGIN
> FETCH INTO :guarantee;
> set guaranteetxt = guaranteetxt & guarantee;
> END;
> UPDATE TEMP.BOREQSELECT SET GUARANTEETXT = :guaranteetxt WHERE
> REQUESTID = :requestid;
>//commit;
> END;
>CATCH
>IF $rc <> 100 THEN STOP ($rc, 'unexpected error guaranteetxt
>composition');
>
>TRY
>DECLARE :$cursor CURSOR FOR SELECT * FROM TEMP.BOREQSELECT;
>CATCH
>IF $rc <> 0 THEN STOP ($rc, 'unexpected error in final select from
>boreqselect');
>// drop table temp.boreqselect;
>===stop===
>Regards,
>Filip Sergeys
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
