Executing a multi-statement request

Database
Enthusiast

Executing a multi-statement request

Hi all

I have a newbie question - on TD 13.10, I have a stored procedure which contains 2 dml statements - an update followed by an insert.

I'd like to test this stored procedure in isolation by executing it through Teradata SQL Assistant.

When I try to execute it using ,

CALL FDR.TEST(1);

I am presented with the following error message:

CALL Failed. [5497] CALL cannot be submitted in multi-statement request.

Can anybody suggest an alternative course of action?

Thanks,

Andrew.
8 REPLIES
Senior Apprentice

Re: Executing a multi-statement request

Hi Andrew,
as the error message indicates:
don't run the call in a multi-statement :-)

Do you q
Enthusiast

Re: Executing a multi-statement request

Hi Dieter

thanks - I do appreciate that the problem statement provided by the error message does rather suggest the solution :-)

Sadly, coming from an Oracle background & having scant knowledge of Teradata, I don't know how not to run the call in a multi-statement.

The documentation seems to be quite clear that the way to invoke a stored procedure is via the 'CALL' keyword. However this situation - one in which the stored procedure contains >1 DML statements seems to be an exception to the rule.

Let me rephrase my question:

Is there any way to run a single stored procedure containing >1 DML statements through a client tool such as BTEQ or SQL Assistant?

Thanks,

Andrew.
Senior Apprentice

Re: Executing a multi-statement request

Hi Andrew,
of course, this is how an SP is invoked.
There must be something else going wrong.

Could you post the actual source code?

Oops, i just noticed my previous post was cut off.
How did you submit the CALL in SQLA, using F9?

F9 in SQLA submits a multi-statement request.

A multi-statement request is several SQL statements (seperated by a semicolon) packed into one message and sent to the optimizer:

update tab set .....
;insert into tab ...;

or
CALL FDR.TEST(1)
;something else;

If you press F5 all statements are submitted separatly.
When your session is running in Teradata mode (it probably does) then each request is a single transaction.

In BTEQ this is controlled by the position of the semicolon:
If there's a new statement starting *after* the terminating semicolon, it's part of a multi-statement.

select ....from ... ;--two seperate statements
select ...,from ...;

select ....; select ..... -- a multi-statement consisting of two statements
from ...;

typically it's done by placing the semicolon at the beginning of the line:
select ... from ...
;select .... from ...
;

Dieter

Senior Apprentice

Re: Executing a multi-statement request

Hi Andrew,
a Stored Procedure can only be called in the same session mode it was created, ANSI or Teradata, as there are some differences in transaction handling.
You must switch to Teradata mode, compile it and then CALL it.

I usually don't work in ANSI mode, but i don't think it's related to your source code.

Try to commit before the CALL, but this shouldn't help.

Put the insert/update statements in a BEGIN REQUEST/END REQUEST block, but this shouldn't help, because it submits them as a multi-statement and your error is related to CALLing the SP itself, no the code inside the SP.

Remove both insert/update and see if it's still failing.

Try running it in BTEQ.

Dieter

Enthusiast

Re: Executing a multi-statement request

Hi Dieter,

Whilst in ANSI mode -

- I've committed and run the procedure - it still fails.

- I've added the begin/end request block - it still fails.

- I've removed the update & insert statement - it still fails.

- If I connect via BTEQ, change the session mode to ANSI - *it succeeds*.

- I have recompiled using the 'Teradata' mode and called it through SQLA and it succeeds.

Andrew.
Senior Apprentice

Re: Executing a multi-statement request

Hi Andrew,
i just tried it, any CALL in ANSI mode fails for a .NET connection, when you switch to odbc it succeds.

Checking the query log i found its adding a COMMIT to the CALL as a multi-statement and this is not allowed.

I found this in the .NET help:
"When the Session Mode is ANSI, do not set the CommandText to a CALL statement and CommandType to CommandType.Text. When this request is submitted to the Teradata database, a TdException will be thrown.
The reason this occurs is that the provider is unaware that a stored procedure is to be executed and will append a COMMIT statement to the request. Teradata requires that the CALL statement be the last statement in a multi-statement request."

I don't know if this is a bug or a feature, i'll ask Mike Dempsey (the SQLA-developer) how to fix it.

Dieter
Teradata Employee

Re: Executing a multi-statement request

As usual, Dieter is correct.
I was not aware of this limitation. Now that i am I have devised a workaround that I will implement in the next efixs. (That would be 13.0.0.16 and 13.11.0.3)
Enthusiast

Re: Executing a multi-statement request

That's lovely - thanks a lot for all of your help guys!

Andrew.