cannot create a SQLScript Procedure : invalide column name

[Former Member]

hi all,

I created a pal procedure for outlier detection which requires the creation of

a set of tables and table types to be used within the procedure. I would like to put

all of the preparation and running of the pal procedure in one SQLScript

procedure, so i can call it from xsjs script lateer on. I started simple doing the following procedure

create procedure getOutliers(in  schemaName varchar(100))

          language SQLSCRIPT as

begin

    set schema :schemaName;

end;

i also tried

exec 'set schema '||:schemaName;

but i am always getting an error when i call the procedure this way:

call getOutliers("I301605");

here is the error:

Could not execute 'call getOutliers("I301605")'

SAP DBTech JDBC: [260]: invalid column name: I301605

do you know what is wrong?

Thanks & regards

Mohamed Ali

[Former Member · Accepted solution]

ok, i found the solution, here is the code sample for creating the procedure and calling it

create procedure OutlierDetection(in  schemaName varchar(100))

          language SQLSCRIPT as

begin

    exec 'set schema '||:schemaName;

end;

call OutlierDetection('I301605');

this works fine, except i still have some questions:

1. if i try to create the same procedure and try to the schema in this way:

set schema :schemaName;

I get an error saying:

SAP DBTech JDBC: [257] (at 117): sql syntax error: incorrect syntax near ":schemaName"

why is that?

here is the whole code sample:

create procedure OutlierDetection(in  schemaName varchar(100))

          language SQLSCRIPT as

begin

    exec 'set schema '||:schemaName;

end;

call OutlierDetection('I301605');

2.

if i create the procedure as in the first example but call it this way:

call OutlierDetection("I301605");

notice the double quote in the input parameter!

I get the following error:

SAP DBTech JDBC: [260]: invalid column name: I301605:

why is that?

Thanks & regards

Mohamed Ali

[rindia]

Hi Mohamed,

There is a difference in creating a procedure and PAL procedure.

If it is only a procedure then it should be call getOutliers('I301605');

For calling PAL procedure there are many prerequisites and if you are not aware of this, then go through this link.

General syntax is:

CALL SYSTEM.AFL_WRAPPER_GENERATOR(

'<procedure_name>', '<area_name>',

'<function_name>', <signature_table>);

Regards

Raj

[Former Member]

hi Raj,

thanks for replying!

actually, i didn't the whole creation of PAL procedure with all the required prerequisites, and the script is working fine.

Now I would like to encapsulate this script within an other SQLScript so that i can call it from within xsjs script.

I started by creating the above simple procedure, which does nothing but setting the current data base , i which i am going to store the required table and table type for the PAL procedure to run.

but, as described above i am getting an error that says invalid column name: I301605 and i don't know why!

And the question is why there is an error!

thanks & regards

Mohamed Ali.

[former_member184768]

Any value in double quotes " " is always treated as a column. Hence it will throw the column name error.

Secondly any DDL statement with dynamic value has to be executed with EXEC. It cannot be executed directly.

Regards,

Ravi