CALL ins_msg_proc - syntax error

[Former Member]

Hi Team

I am trying to write logs after every step of the sscript.

But I get a syntax error at this

"SAP DBTech JDBC: [328] (at 583): invalid name of function or procedure: INS_MSG_PROC: line 22 col 6 (at pos 583)"

CALL ins_msg_proc("WRONG: cursor contains no valid data");

Here is the script

CREATE PROCEDURE jmj_cursor_proc LANGUAGE SQLSCRIPT AS

v_id INTEGER;

v_name varchar(30);

v_lname varchar(30);

v_role_id integer;

CURSOR c_cursor1(v_id INTEGER) FOR SELECT id,name,LAST_NAME,role_id FROM ZEMPLOYEE WHERE id = :v_id ORDER BY id;

BEGIN

OPEN c_cursor1('1');

IF c_cursor1::ISCLOSED THEN

    CALL ins_msg_proc('WRONG: cursor not open');

ELSE

    CALL ins_msg_proc('OK: cursor open');

END IF;

FETCH c_cursor1 INTO v_id,v_name,v_lname,v_role_id;

IF c_cursor1::NOTFOUND THEN

CALL ins_msg_proc("WRONG: cursor contains no valid data");

ELSE

CALL ins_msg_proc('OK: cursor contains valid data');

END IF;

CLOSE c_cursor1;

END;

Please help

[former_member1150167]

Make Sure that You have added your current HANA Schema name before the procedure name with Quotation Mark

"SchemaName"."ProcedureName" (?,?,?) 

To send Quotation Mark in Procedure call use back slash (\)

" CALL \"SchemaName\".\"ProcedureName\" (?,?,?) "

[lbreddemann]

It's in fact the wrong use of double quotes here.

By replacing

     CALL ins_msg_proc("WRONG: cursor contains no valid data");

with

     CALL ins_msg_proc('WRONG: cursor contains no valid data');

The function call will work (at least it does for me ;-)).

Cheers, Lars

[former_member198127]

Not working for me somehow.

I am working in a different scehma (PRACTICE_TEST) and not th default sys one. Don't i need to put any information about that somewhere?

Currently, i used the syntax :

CALL ins_msg_proc('WRONG: cursor contains no valid data');

This gave me the following error:

Could not execute 'create procedure PRACTICE_TEST.PROC_PUZZLE_OUTPUT LANGUAGE SQLSCRIPT as ELEPHANT_COUNT integer; ...'

SAP DBTech JDBC: [328] (at 559): invalid name of function or procedure: INS_MSG_PROC: line 26 col 11 (at pos 559)

[former_member184768]

Hi Vishal,

I don't think ins_msg_proc is a built in procedure / function. You may have to first define it and then use it.

Regards,

Ravi

[former_member198127]

Hey Ravindra,

I also originally thought this as an externally defined procedure. But then i found it mentioned in reference page for creating procedures (http://help.sap.com/hana/html/sql_create_procedure.html ), i realized this might be an easy way to display user-defined messages while running procedures.

And from the above conversation with Lars, it seems this is a pre-defined procedure, we just need to make proper reference to it.

Still not able to figure it out clearly.

[lbreddemann]

Hi Vishal,

Sorry, but I did not imply anywhere that this is a pre-defined procedure.

What I did was to write a little mock procedure that mimics the interface of ins_msg_proc to figure out where the problem was.

The procedure I used looks like this:

create procedure ins_msg_proc (IN message VARCHAR) as 
begin 
         exec 'INSERT INTO message_log values (''' || :message || ''')';
end;

As you see, you need to have a table called message_log for this statement to run as well.

And of course to call the procedure, it must be in the the searchable namespace.

That means, there's either a public synonym for it or it's part of the current schema.

OR: you reference it fully qualified with the schema name in front.

Cheers, Lars

[former_member184768]

Hi Vishal,

You can also check which all procedures are available for you to execute.

Please SELECT procedure_name, schema_name, procedure_type from  PROCEDURES;

to check if the procedure exists and it is a built in procedure or user defined SQL script procedure.

Regards,

Ravi

[former_member198127]

So if i want to display some message during the execution, what is the easiest way for me to do that? Someone suggested me to do a Select <variable name> from dummy table when i want the output. That one worked, but when running in loops, it gives separate result sets in separate windows each time it runs.

now i am trying with your suggestion of using session context variables (http://scn.sap.com/message/13754597#13754597).

I have added the desired variables, and with this, i am able to create the stored procedure successfully. But when i run it, it is giving strage error:

Could not execute 'call PRACTICE_TEST.PROC_PUZZLE_OUTPUT'

SAP DBTech JDBC: [257]: sql syntax error:  [257] PRACTICE_TEST.PROC_PUZZLE_OUTPUT: line 16 col 1 (at pos 267): SQLException257 exception: sql syntax error: unterminated quoted string literal: line 1 col 13 (at pos 13)

this is my procedure:

create procedure PRACTICE_TEST.PROC_PUZZLE_OUTPUT

LANGUAGE SQLSCRIPT

as

ELEPHANT_COUNT  integer;

COW_COUNT  integer;

GOAT_COUNT  integer;

Elephant  integer;

Cow  integer;

goats  integer;

CNTR  INTEGER;

Begin

ELEPHANT_COUNT := 1;

COW_COUNT := 1;

GOAT_COUNT := 1;

CNTR := 0;

EXEC  'set session ''Starting with the procedure now ';

While ELEPHANT_COUNT<10

DO

while COW_COUNT < 100

DO

while GOAT_COUNT < 100

Do

          CNTR := CNTR + 1;

if MOD(:cntr, 1000) = 0 then

-- EXEC  'set session ''PUZZLE_STORED_PROC PROGRESS''= '''||:CNTR || ||''' ';

              EXEC   'set session ''PUZZLE_STORED_PROC''= '''||:CNTR || ''' ';

end if;

if (:ELEPHANT_COUNT + :COW_COUNT + :GOAT_COUNT = 100)  AND (:ELEPHANT_COUNT * 10 + :COW_COUNT * 3  + :GOAT_COUNT * 0.5 = 100)

THEN

                  Elephant := :ELEPHANT_COUNT;

                  Cow := :COW_COUNT;

                  Goats := :GOAT_COUNT;

--CALL ins_msg_proc('WRONG: cursor contains no valid data');

--select elephant, cow, goats from dummy;

select :ELEPHANT_COUNT, :COW_COUNT,:GOAT_COUNT from dummy;                     

end if;

              GOAT_COUNT := :GOAT_COUNT + 1;

END While;

         COW_COUNT := :COW_COUNT +1;

--     select :ELEPHANT_COUNT, :COW_COUNT,:GOAT_COUNT from dummy;

End While;

--select :ELEPHANT_COUNT, :COW_COUNT,:GOAT_COUNT from dummy;

end while;

End;

[lbreddemann]

Hi Vishal,

the line

EXEC  'set session ''Starting with the procedure now ';

is just plain wrong - it's not how SET SESSION works.

In general there is no "System.out" for HANA

If you want to deliver additional output from your SQLScript code, then the session context variables and the logging procedure approach are pretty much what you can do currently.

- Lars

[former_member184768]

Hi Vishal,

I liked the concept of counting Elephants, Cows and Goats.. , so decided to enhance your procedure. Please find the complete code below:

create table MY_LOG (LOG_ENTRY varchar(100));

CREATE PROCEDURE PROC_MY_LOG (IN  v_in_msg    VARCHAR  (100)       -- Debug Message

)

LANGUAGE SQLSCRIPT

AS

BEGIN

  INSERT INTO MY_LOG ( LOG_ENTRY ) VALUES (:v_in_msg);

END;

drop procedure  PROC_PUZZLE_OUTPUT;

create procedure PROC_PUZZLE_OUTPUT

LANGUAGE SQLSCRIPT

as

  ELEPHANT_COUNT  integer;

  COW_COUNT  integer;

  GOAT_COUNT  integer;

  ELEPHANT  integer;

  COW  integer;

  GOATS  integer;

  CNTR  integer;

  v_msg varchar(100);

Begin

  ELEPHANT_COUNT := 1;

  COW_COUNT := 1;

  GOAT_COUNT := 1;

  CNTR := 0;

  v_msg := '';

  While ELEPHANT_COUNT < 10

    DO

      While COW_COUNT < 100

        DO

          while GOAT_COUNT < 100

            Do

              CNTR := CNTR + 1;

              if MOD(:cntr, 1000) = 0 then

                v_msg := 'PUZZLE_STORED_PROC = '||:CNTR;

                call PROC_MY_LOG(:v_msg);

              end if;

              if (:ELEPHANT_COUNT + :COW_COUNT + :GOAT_COUNT = 100)  AND (:ELEPHANT_COUNT * 10 + :COW_COUNT * 3  + :GOAT_COUNT * 0.5 = 100) THEN

                  ELEPHANT := :ELEPHANT_COUNT;

                  COW := :COW_COUNT;

                  GOATS := :GOAT_COUNT;

                  v_msg := 'Goat Loop : Elephant count = '||:ELEPHANT_COUNT||' Cow count = '||:COW_COUNT||'Goat count = '||:GOAT_COUNT;

                  call PROC_MY_LOG(:v_msg);

              end if;

              GOAT_COUNT := :GOAT_COUNT + 1;

         END While;

         COW_COUNT := :COW_COUNT + 1;

         v_msg := 'Cow Loop : Elephant count = '||:ELEPHANT_COUNT||' Cow count = '||:COW_COUNT||'Goat count = '||:GOAT_COUNT;

         call PROC_MY_LOG(:v_msg);

      End While;

      v_msg := 'Elephant Loop : Elephant count = '||:ELEPHANT_COUNT||' Cow count = '||:COW_COUNT||'Goat count = '||:GOAT_COUNT;

      call PROC_MY_LOG(:v_msg);

  end while;

End;

call PROC_PUZZLE_OUTPUT;

SELECT * from MY_LOG;

Regards,

Ravi

[former_member198127]

Hey Lars,

Actually I was looking for some kind of "System.out"
thing in HANA, for which I was trying all the different options as suggested on
different articles. And yes, EXEC was an incorrect choice for doing this.

I got somewhat working option for doing this from the code
provided by Ravindra in below comment.

Though i have still not achieved what I am exactly looking for,
but yes, now there is a good way and approach for reaching to the desired
target.

Thanks for your guidance about EXEC.

[former_member198127]

Hey Ravindra,

Thanks a lot for taking interest in this. Actually with this code, i am trying to solve some simple puzzle that is usually solved using some programming language having loops and "System.Out" kind of things.

Just wanted to see if SAP HANA can perform minor business logic processing at database level at faster rate than any application programming layer. If that happens, and with a good performance difference, we may prefer writing business logic using SQL Script and just avoid application layer for small applications.

The puzzle is as follows:

You have $100, with which you have to buy exact 100 toys.

There are three different choices for toys:   Elephant Toy = $10, Cow Toy = $3, Goat Toy=$0.5.

So what are the possible options for buying 100 toys with just $100 ?

[former_member198127]

So far, I am able to run the procedure successfully, and also able to see the output now. But this output is not the desired output.

My code is:

create

procedure PRACTICE_TEST.PROC_PUZZLE_OUTPUT

LANGUAGE

SQLSCRIPT

as

  ELEPHANT_COUNT 

integer;

  COW_COUNT 

integer;

  GOAT_COUNT 

integer;

  ELEPHANT 

integer;

  COW 

integer;

  GOATS 

integer;

  CNTR 

integer;

  v_msg

varchar(100);

Begin

  ELEPHANT_COUNT := 1; 

  CNTR := 0;

  v_msg := '';

While ELEPHANT_COUNT < 10

DO

    COW_COUNT := 1;

While COW_COUNT < 100

DO

          GOAT_COUNT := 1;

while GOAT_COUNT < 100

Do

              CNTR := CNTR + 1;

if MOD(:cntr, 1000) = 0 then

                     v_msg := 'CNTR RUN COUNT = '||:CNTR;

call PRACTICE_TEST.PROC_MY_LOG(:v_msg);

end if;

if (:ELEPHANT_COUNT + :COW_COUNT + :GOAT_COUNT = 100) AND ((:ELEPHANT_COUNT * 10) + (:COW_COUNT * 3)  + (:GOAT_COUNT * 0.5) = 100)

THEN

                       ELEPHANT := :ELEPHANT_COUNT;

                       COW := :COW_COUNT;

                       GOATS := :GOAT_COUNT;

                       v_msg := 'CNTR RUN COUNT = '||:CNTR || 'Goat Loop : Elephant count = '||:ELEPHANT_COUNT||' Cow count = '||:COW_COUNT||'Goat count = '||:GOAT_COUNT;

call PRACTICE_TEST.PROC_MY_LOG(:v_msg);

end if;

                   GOAT_COUNT := :GOAT_COUNT + 1

;

END While;

         COW_COUNT := :COW_COUNT + 1;

         v_msg := 'CNTR RUN COUNT = '||:CNTR || 'Cow Loop : Elephant count = '||:ELEPHANT_COUNT||' Cow count = '||:COW_COUNT||'Goat count = '||:GOAT_COUNT;

call PRACTICE_TEST.PROC_MY_LOG(:v_msg);

End While;

   ELEPHANT_COUNT := :ELEPHANT_COUNT + 1;

      v_msg := 'CNTR RUN COUNT = '||:CNTR || 'Elephant Loop : Elephant count = '||:ELEPHANT_COUNT||' Cow count = '||:COW_COUNT||'Goat count = '||:GOAT_COUNT;

call PRACTICE_TEST.PROC_MY_LOG(:v_msg);

end while;

End;

[former_member184768]

Hi Vishal,

In that case you can remove the calls near end while loop. I added them just for the debugging purpose. You needto keep the Call only in the if loop.

Nice example,  🙂

Please post your findings on this forum comparing HANA with other programming option for performance.

Regards,

Ravi

[former_member198127]

Yep,

Now i have done that, and i have found the correct answer.

Thanks for your help and suggestions.

I would try to run it now with Time Tracings to identify the total time it took. Then I would try it for other programming options.

But i am not sure that running this query on SAP HANA One (cloud) instance, and running others locally on my laptop would be a fair comparision enough. 

[former_member198127]

Hey Lars,

I finally got what i was looking for. 

Actually with this code, i am trying to solve some simple puzzle that is usually solved using some programming language having loops and "System.Out" kind of things.

Just wanted to see if SAP HANA can perform minor business logic processing at database level at faster rate than any application programming layer. If that happens, and with a good performance difference, we may prefer writing business logic using SQL Script and just avoid application layer for small applications.

The puzzle is as follows:

You have $100, with which you have to buy exact 100 toys.

There are three different choices for toys:   Elephant Toy = $10, Cow Toy = $3, Goat Toy=$0.5.

So what are the possible options for buying 100 toys with just $100 ?

[lbreddemann]

Hi Vishal

glad to hear that your initial question was answered finally

For the puzzle problem:

HANA provides options to automatically split queries on highly optimized data structures so that they can be processes by many CPU cores in parallel.

To make this happen, a lot of conditions must be met, so this "automatic speedup" is not generic in any sense.

It works for query and calculation scenario processing on column store tables.

To leverage the processing capabilities of a HANA box you'll have to change your program design, so that the divide and conquer approach can be employed.

So for this permutation problem, maybe a recursion approach can be used ?!

SQLScript on its own doesn't parallelize - it's the operators in it (CE_...) that do.

So, I wouldn't expect too much performance benefit here.

well, that's what I think, at least.

- Lars

[former_member198127]

I get your point Lars.

So this means that when I run this logic using SQLScript, it would just consume the normal processing power of HANA. By using CE functions, parallelization related performance improvements can be seen. And for doing that, I need to split my business logic in such way that it uses the parallelism offered by HANA (instead of simply running the loops in SQL Script).

If I get time, I would explore more on how can I redesign my logic to gain parallelization related improvements in SAP HANA.

Thanks again for enlightening me .

[former_member198127]

i am also facing similar problem.

I tried both with single qoutes ('') and double quotes (" ") inside the proc, but both are not working.

Any resolution for this issue yet?

[former_member202087]

Joseph,

I think the issue may be that you have double quotes on the line that is failing...

CALL ins_msg_proc("WRONG: cursor contains no valid data");

I see single quotes on the other calls.

Thanks,

Ken

[Former Member]

i tried <schema>, _SYS_BIC, SYS etc , but doesnt seem to like it.

[Former Member]

Hi Joseph

  Every thing appears to be correct ,but syntactically some thing is wrong , can you try  giving space between call and the message as depicted below.May be it seems to be foolish ,but some times it counts.I am not very aware of the syntax but trying my part .

CALL ins_msg_proc ('OK: cursor contains valid data');

Please let us know the feedback

Thanks

Santosh

[Former Member]

I tried , this is a standard proc to ouput messages to the screen, but doesnt seem to work.

[Former Member]

Hi Joseph

  Can you try giving Schema name before the procedure name "SchemaName.ProcedureName" .

Thanks

Santosh

[Former Member]

any ideas

[former_member184768]

BTW Joseph, did you get your answer ?

Regards,

Ravi