Question Re: How to query temporary tables and preview data during debugging in SQL Script? in Technology Q&A

How to query temporary tables and preview data during debugging in SQL Script?

former_member192683 — Sun, 25 Nov 2018 14:32:35 GMT

Hi experts,

I am trying to perform a similar use of monitoring internal tables as in ABAP debugger but in SQL Script procedure debugging with WebIDE in XSA.

However, I can only preview the data inside table variables, it looks not logical to create table variables for each row that I want to monitor the internal table. There can be needed to delete or update records, which is not a provided feature of table variables.

HANA doesn't allow dirty reads either, as a result, there is not an option to change the isolation level to "read uncommitted", so I can not read the data before it's committed even if I use global temporary tables instead, to query the table from another SQL console window.

I have written the procedure below to demonstrate the issue. How can I monitor/query the data inside #PEOPLE temporary table during debugging?

CREATE PROCEDURE
TEST_01 (out outTab TABLE 
(
employee nvarchar(10), manager nvarchar(10), manager2 nvarchar(10)) )
LANGUAGE SQLSCRIPT
AS
   vl_cnt integer = 0;
BEGIN
 select count(*) into vl_cnt from
SYS.M_TEMPORARY_TABLES where table_name like '%#PEOPLE%';
 IF :vl_cnt > 0
 THEN
 drop table #PEOPLE;
 END IF;
 CREATE LOCAL TEMPORARY TABLE #PEOPLE (employee
nvarchar(10), manager nvarchar(10)) ;
 insert into #PEOPLE VALUES('person01','person03');
 insert into #PEOPLE VALUES('person02','person03');
 insert into #PEOPLE VALUES('person03','person05');
 insert into #PEOPLE VALUES('person04','person02');
 delete from #PEOPLE WHERE EMPLOYEE ='person04'; 
 --breakpoint
 outTab = select t1.employee, t1.manager,
t2.manager as manager2 from #PEOPLE as t1
left join #PEOPLE as t2 on t1.manager = t2.employee;
END

I have tried the "expressions" feature of the debug perspective, but it can't find the #PEOPLE table and returns as invalid. I guess it uses another session other than the debug session.

Contributions are appreciated,

Thanks in advance.

Re: How to query temporary tables and preview data during debugging in SQL Script?

lbreddemann — Mon, 26 Nov 2018 00:24:05 GMT

You may want to check the SQLScript documentation again. DDL commands like UPDATE, INSERT, DELETE have been supported for years with table variables.

In most cases, you should be able to avoid using session temporary tables and use the more efficient table variables instead.

AFAIK there is no option to inspect the contents of session local temp tables directly. You could however try and assign them to a table variable and inspect that table variable then.

Re: How to query temporary tables and preview data during debugging in SQL Script?

former_member192683 — Tue, 27 Nov 2018 13:25:08 GMT

Thank you for providing awareness @lars.breddemann, so local temporary tables content can't be inspected during debugging.

I didn't know that but when there is a better solution like the ability to use DDL commands for table variables, it's really not a matter. I was trying the same syntax to insert rows to table variables, checked SQLScript documentation again, "Modifying the Content of Table Variables" section has the explanation in there.