String literal in repository procedure

[Former Member]

Hi,

I am attempting to store a procedure in the repository that has a string literal, but the validation fails every time.  For example:

select 'cat''s' from dummy

This syntax works, but it does not compile into a repository procedure.

I tried this too:

select 'cat'|| char(39) || 's' from dummy

but the function CHAR is not accepted 😞    Any possible solution?

thx.

Steven.

[lbreddemann]

Hi Steven,

not sure exactly, what you're doing, but this works on my system:

/********* Begin Procedure Script ************/

v_text varchar(20) := 'HELLO WORLD!';

BEGIN

execute immediate 'select '''||:v_text||''' from dummy';

END;

/********* End Procedure Script ************/

- Lars

[Former Member]

So I created a basic procedure in the repository like this:

CREATE PROCEDURE TEST ( )

          LANGUAGE SQLSCRIPT

          SQL SECURITY INVOKER

          READS SQL DATA AS

          v_text varchar := 'hello';

BEGIN

/*****************************

          Write your procedure logic

*****************************/

execute immediate 'select '''  || :v_text || ''' from dummy';

END;

and I get this error:

END;; sql syntax error: incorrect syntax near "TEST": line 1 col 140 (at pos 140) at qp_gram.y:34124

Now in the editor the text is highlighted in red with the error "Syntax Error; ''' is incorrect or missplaced"

Basically putting in place 3 quote marks will not commit to the repository.

[lbreddemann]

For execute immediate you've to set the procedure to READ/WRITE (remove the READS SQL DATA).

- Lars

[former_member184768]

Hi Steven,

I am on revision 49 and do not have repository configured yet (still on old 32 bit architecture computer ).

When I tried your code in plain SQL editor, I got the error as follows:

Could not execute 'CREATE PROCEDURE TEST ( ) LANGUAGE SQLSCRIPT SQL SECURITY INVOKER READS SQL DATA AS v_text varchar ...' in 21 ms 447 µs . SAP DBTech JDBC: [7]: feature not supported: Dynamic SQL is not supported in the READ ONLY procedure/function

So I removed READS SQL DATA and then I could compile the procedure. I encountered the error when I tried to run the procedure, but I think the code posted by you is not the complete procedure, so the usage must be different.

Not sure if this is the issue, but you can try removing the READ SQL DATA and see if it works.

Regards,

Ravi

P.S. Unfortunately my screen refresh was slower than Lars' reply. I guess again the fault of old 32 bit architecture..

[Former Member]

Hi,

I tried that but am afraid the result is the same.  The syntax is simply not accepted.

END;; sql syntax error: incorrect syntax near "TEST2": line 1 col 126 (at pos 126) at qp_gram.y:34124

Also, why does the CHAR function not work?  I could of used that as a workaround.

Steven.

CREATE PROCEDURE TEST ( )

          LANGUAGE SQLSCRIPT

          SQL SECURITY INVOKER

          AS

v_text varchar(10) := 'hello';

BEGIN

/*****************************

          Write your procedure logic

*****************************/

execute immediate 'select '''  || :v_text || ''' from dummy';

--select 1 from dummy;

END;

[Former Member]

This changed nothing.  The literals are not accepted.  Even something as simple as this:

v_text varchar := '''hello''';

fails to compile.  Even the editor rejects it with little wiggly red lines. 

Now as I see it there are 2 possibilities

1. I just do it wrong and there is a different way to specify the quote mark.

2. It is broken.

Maybe someone can test this on their system so I know either way, because right now it is not working and I do not see why. 

I'm using Hana SP05 with Hana Studio revision 48,

[lbreddemann]

HI Steven,

Strings in SQLScript are always enclosed into single quotation marks '.

Double quotation marks indicate identifiers like table, view or column names.

- Lars

[Former Member]

OK, that's what I am doing here:

v_text varchar := '''hello''';

But the repository refuses to accept it.

[Former Member]

This is what you should have:

v_text varchar := 'hello';