Hello, Except for vtrace (for which I opened another thread because I can't get it to work) are there any other ways of printing debug information. I 'd like see if the loop gets executed.
Regards, Filip Sergeys On Wed, 2004-02-18 at 08:14, Anhaus, Thomas wrote: > 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] > -- *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* * System Engineer, Verzekeringen NV * * www.verzekeringen.be * * Oostkaai 23 B-2170 Merksem * * 03/6416673 - 0477/340942 * *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
