External stored procedure to run SP. Strange BUG!

General
Enthusiast

External stored procedure to run SP. Strange BUG!

Hi guys!

I want to create XSP to dynamicaly call existing SP by name. I've read manuals and came to the following solution:

/*REPLACE PROCEDURE TestProc(ProcName Varchar(30))
LANGUAGE C
NO SQL
EXTERNAL NAME 'CS!TestProc!c:/TestProc.c!F!TestProc'
PARAMETER STYLE SQL;*/
#define SQL_TEXT Latin_Text
#include <sqltypes_td.h>
#include <string.h>
#define MAX_RESULT_SIZE 200;
void TestProc (VARCHAR_LATIN *ProcName,
int *ProcName_isnull,
char sqlstate[6],
SQL_TEXT extname[129],
SQL_TEXT specific_name[129],
SQL_TEXT error_message[257])
{
FNC_CallSP((SQL_TEXT *)ProcName, 0, NULL, NULL, NULL, sqlstate);
return;
}

This XSP takes only one parameter - the name of the SP which has to be called. SP doesn't take any parameters.

Then I've created SP's which will be called:

replace procedure Short()
begin
end;
------------------------------------
replace procedure LongFFFFFFFFFFFFFFFFFFFFFFFFFF()
begin
end;

After this I try to call created procedures using XSP:

call testproc('Short');

- this one executes.

call testproc('LongFFFFFFFFFFFFFFFFFFFFFFFFFF');

and this one ends with an error: 7504: in UDF/XSP/UDM Samples.TestProc: SQLSTATE 39000:.

Is there logical explanation of this? Why does the error occurs?

And the second question:

It is said in the manual that it's better to use CLI calls rather then FNC_CallSP. Who knows why? In case of using CLI to solve this task is it possible to call SP which takes OUT parameter?

Thank you in advance,

Dmytro Boiko

5 REPLIES
Enthusiast

Re: External stored procedure to run SP. Strange BUG!

RELEASE    13.00.00.12

VERSION    13.00.00.12

LANGUAGE SUPPORT MODE    Standard

Enthusiast

Re: External stored procedure to run SP. Strange BUG!

And want to add that if the length of SP's name is 29 (not 30) execution completes.

Enthusiast

Re: External stored procedure to run SP. Strange BUG!

I solved this task using CLI procedures (instead of using FNC_CallSP).

Re: External stored procedure to run SP. Strange BUG!

I stumbled on this issue recently. I found that the length of stored procedure name is limited by FNC_MAXNAMELEN value in header file.

You should check your definition in sqltypes_td.h header file.  #define FNC_MAXNAMELEN x.   x is the stored procedure name length. I think by default it is limited to 30.

Re: External stored procedure to run SP. Strange BUG!

Apologize the above solution wont work. This will be fixed in future releases of Teradata DBS. Right now there is restriction of 29 characters for procedure names which are invoked through FNC_CallSP.