API for DBC.SysExecSQL

Database
Enthusiast

API for DBC.SysExecSQL

Where do I find documentation on parameters to set before invoking the above routine.

Where do I find documentation on what parameters are available to be retrieved?

I would like  to submit a query that start with 'Select count(*)' , invoke

DBC.SysExecSQL

 and then read a variable that holds the rows counted.

Any suggestions appreciated.

7 REPLIES
Enthusiast

Re: API for DBC.SysExecSQL

You can read about it in SQL Stored Procedures and Embedded SQL in the Teradata documentation set.  However, you cannot execute a select statement via DBC.SysExecSQL.  A dynamic cursor, as illustrated in the example below, is probably the solution for your requirement.

CREATE PROCEDURE GetEmployeeSalary
(IN EmpName VARCHAR(100), OUT Salary DEC(10,2))
BEGIN
DECLARE SqlStr VARCHAR(1000);
DECLARE C1 CURSOR FOR S1;
SET SqlStr = 'SELECT Salary FROM EmployeeTable WHERE EmpName = ?';
PREPARE S1 FROM SqlStr;
OPEN C1 USING EmpName;
FETCH C1 INTO Salary;
CLOSE C1;
END;

Enthusiast

Re: API for DBC.SysExecSQL

There is not any material that covers setting / retrieving parameters in indicated manual.

There is only one example - very minimal documentation.

Teradata Employee

Re: API for DBC.SysExecSQL

All the documentation is in the file mentionned by above by Jim.

You cannot set or retrieve parameters (or result sets) using SysExecSQL(), by definition, this is only invoquing the SQL statement you pass to it. You can use [volatile/temporary] talbles to pass and retrieve data with the embedded code.

That said, if you are just looking to retrieve a count, you are probably looking in the wring direction and won't need dynamic SQL for it...

//Remi

Enthusiast

Re: API for DBC.SysExecSQL

why I can't find sysexecsql???Anyone give me the code?Thanks.

Junior Contributor

Re: API for DBC.SysExecSQL

DBC.SysExecSQL is a kind of dummy/placeholder, you can use it within a SP, but it doesn't exist, it never did :-)

In newer releases you should use EXECUTE IMMEDIATE instead. 

Dieter

Teradata Employee

Re: API for DBC.SysExecSQL

hi gents,

i'm currently having a problem.  not sure if this is a teradata bug or something

I'm running a dynamic insert sql (complete with generating a where clause based on values). The thing is, i'm calling DBC.SysExecSQL and getting ACTIVITY_COUNT to get the number of rows inserted.  It was working fine for some records but for other records, the ACTIVITY COUNT is null ?

So, what i did was to put a table, log the value from the ACTIVITY_COUNT to this and when i executed the procedure and checked the table for logs, it came out around 5 records.  I'm expecting only 1 record with a value of 990, however, the table which i placed some logs is giving me 5 records like 

34

51

2

100

67

when you add them up, it doesn't add up to 990.  I tried using EXECUTE IMMEDIATE, however, the same thing.  What I plan to do, is to put the records into a volatile table and generate the count from those records.  It's going to be a two step approach, I was wondering if there's a way to return only one record with 990 instead of those multiple records having different numbers.  I tried searching the web for answers but couldn't find one, hope you can help me on this matter.

thank you.

Enthusiast

Re: API for DBC.SysExecSQL

Hi All,

I want to use ACTIVITY_COUNT   with  DBC.SysExecSQL to get how many rows updated by query executed in  DBC.SysExecSQL. 

Could you please help me in this ..