insert not working inside SQLScript procedure| error: identifier must be declared:

[Former Member]

hi all,

I create a script that creates and executes a pal procedure. Now I am try to encapsulate this script

within a SQLScript procedure to be able to call it from within the xsjs script.

the SQLScript procedure right now is the following:

create procedure OutlierDetection(in  schemaName varchar(100),

                                                                                  in  currentDimension varchar(100))

          language SQLSCRIPT as

begin

    exec 'set schema '||:schemaName;

    exec 'DROP TYPE DATA_T';

          exec 'create type DATA_T as table('||:currentDimension||' VARCHAR(100), SUM_MEASURE DOUBLE)';

          exec 'DROP TYPE OUTLIER_T';

          exec 'create TYPE OUTLIER_T AS TABLE('||:currentDimension||' VARCHAR(100),"OUTLIERS" INT)';

          exec 'DROP TYPE RESULT_T';

    exec 'CREATE TYPE RESULT_T AS TABLE("MEAN" DOUBLE,"SD" DOUBLE)';

    exec 'DROP TYPE CONTROL_T';

           exec 'CREATE TYPE CONTROL_T AS TABLE("Name" VARCHAR(100), "intArgs" INT, "doubleArgs" DOUBLE,"strArgs" VARCHAR(100))';

           exec 'DROP table SIGNATURE_TAB';

          exec 'create column table SIGNATURE_TAB("ID" INT,"TYPENAME" VARCHAR(100),"DIRECTION" VARCHAR(100))';

exec "insert into SIGNATURE_TAB values (2,'"||:schemaName||".CONTROL_T','in')";

end;

call OutlierDetection('I301605', 'CITY');

everything works except that when i try to insert into the signature table,

in this line:

exec "insert into SIGNATURE_TAB values (2,'"||:schemaName||".CONTROL_T','in')";

i get an error saying:

SAP DBTech JDBC: [1287]: identifier must be declared: insert into SIGNATURE_TAB values

could you please tell me what's wrong?

Thanks & Regards

Mohamed Ali

[Former Member · Accepted solution]

ok , I found the solution. It's working but i don't know if it is the best solution or  not:

I had to use an intermediate variable which has to be declared before the begin statement

here the code for the procedure:

drop procedure OutlierDetection;

create procedure OutlierDetection(in  schemaName varchar(100),

                                                                                  in  currentDimension varchar(100))

          language SQLSCRIPT as

          insertInto varchar(100);

begin

    exec 'set schema '||:schemaName;

    exec 'drop type DATA_T';

    exec 'create type DATA_T as table("'||:currentDimension||'" VARCHAR(100), SUM_MEASURE DOUBLE)';

          exec 'DROP TYPE OUTLIER_T';

          exec 'create TYPE OUTLIER_T AS TABLE("'||:currentDimension||'" VARCHAR(100),"OUTLIERS" INT)';

          exec 'DROP TYPE RESULT_T';

    exec 'CREATE TYPE RESULT_T AS TABLE("MEAN" DOUBLE,"SD" DOUBLE)';

    exec 'DROP TYPE CONTROL_T';

           exec 'CREATE TYPE CONTROL_T AS TABLE("Name" VARCHAR(100), "intArgs" INT, "doubleArgs" DOUBLE,"strArgs" VARCHAR(100))';

           DROP table SIGNATURE_TAB;

          create column table SIGNATURE_TAB("ID" INT,"TYPENAME" VARCHAR(100),"DIRECTION" VARCHAR(100));

          insertInto := :schemaName||'.DATA_T';

          insert into SIGNATURE_TAB values (1,:insertInto,'in');

          --insert into SIGNATURE_TAB values (1,'I301605.DATA_T','in');

          insertInto := :schemaName||'.CONTROL_T';

          insert into SIGNATURE_TAB values (2,:insertInto,'in');

          insertInto := :schemaName||'.RESULT_T';

          insert into SIGNATURE_TAB values (3,:insertInto,'out');

          insertInto := :schemaName||'.OUTLIER_T';

          insert into SIGNATURE_TAB values (3,:insertInto,'out');

end;

call OutlierDetection('I301605', 'CITY');

which is thankfully working fine.

Thanks & regards

Mohamed Ali

[rindia]

Hi Mohamed,

You are not passing the correct value for currentDimension to the column "DIRECTION".

In Insert statement replace the last parameter in with '||:currentDimension||' with appropriate quote.

Use single quote for schema name and current dimension. If syntax error then use double quote.

Regards

Raj

[Former Member]

hi Raj,

thanks for replying,

Why would I change the

in with '||:currentDimension||'  in the insert statement.

I mean, in the signature table declaration I have to put the direction of the parameter.

and by the way, outside the SQLScript procedure in the pal script

I do the insert this way:

insert into PDATA values (1,'I301605.DATA_T','in');

notice the single quote.

If i use double quotes this way:

insert into PDATA values (1,"I301605.DATA_T","in");

it doesn't work.

thanks & regards

Mohamed Ali

[rindia]

Hi,

I thought that as there is a parameter in a procedure for dimension, you need to use that in insert.

Sorry for that.

Could you please try having schema name for table type, table declarations, Insert staement etc.

Regards

Raj

[Former Member]

hi again,

for example if i execute the following insert query (not dynamically) within the SQLScript procedure in this way:

insert into SIGNATURE_TAB values (1,'I301605.DATA_T','in');

it works just fine.

but if i try to execute the same query using exec, in this way:

exec "insert into SIGNATURE_TAB values (1,'I301605.DATA_T','in')";

it throws an error:

SAP DBTech JDBC: [1287]: identifier must be declared: insert into SIGNATURE_TAB values (1,'I301605.DATA_T','in')

so, the error is thrown without even creating the script dynamically.

[Former Member]

hi, thanks for replying it works now.

By the way has any body tried this pal script encapsulation in a sqlScript  procedure.

If yes, could you please point me to some examples, links?

thanks & regards

Mohamed Ali