Executing script via hdbsql

[Former Member]

The following stuff runs fine from HANA Studio, but when executed as script via hdbsql:

hdbsql -c ";" -m -i 00 -n 0.0.0.0:30015 -u SYSTEM -p secret -I ./create.sql

fails with:

* 257: sql syntax error: line 1 col 110 (at pos 110) SQLSTATE: HY000

Wtf? Feeling stupid, but how do I run batch scripts via hdbsql?

Thanks,

Tobias

DROP TABLE test1.t2;

CREATE TABLE test1.t2 (f1 integer);

DROP TABLE test1.t3;

CREATE TABLE test1.t3 (f1 integer);

DROP PROCEDURE test1.add3;

CREATE PROCEDURE test1.add3 (IN x INTEGER, IN y INTEGER, OUT r INTEGER) LANGUAGE SQLSCRIPT AS BEGIN r := x + y; END;

[richard_bremer · Accepted solution]

Hi Tobias,

you're mixing the same character here with two different semantics - and there is no way hdbsql could figure out the correct semantics in all cases:

1. The semicolon is used in HANA procedures to separate the lines of code within the procedure (all lines between BEGIN and END should be delimited by a semicolon).
2. In your hdbsql session, you declared with the -c option, that the line separator for multi-line input is the semicolon.

If you change the line separator for hdbsql from ; to something else, e.g. a hash (#) your script runs without problem:

DROP TABLE test1.t2#

CREATE TABLE test1.t2 (f1 integer)#

DROP TABLE test1.t3#

CREATE TABLE test1.t3 (f1 integer)#

DROP PROCEDURE test1.add3#

CREATE PROCEDURE test1.add3 (IN x INTEGER, IN y INTEGER, OUT r INTEGER) LANGUAGE
SQLSCRIPT AS BEGIN r := x + y; END#

(You should of course always use a character that is not needed anywhere within your regular SQL code. The hash is probably not always the best delimiter).

Best regards,

Richard

--

Dr. Richard Bremer

Customer Solution Adoption (CSA), SAP AG

[Former Member · Accepted solution]

Hi Richard,

this works for hdbsql, but then not any longer for HANA Studio.

My workflow is:

1. develop a DDL script in HANA Studio (I want to run it there via F8)

2. when done, checkin the _unmodified_ file into version control

3. run the script against production using hdbsql

I think this workflow is quite common. Modifying the script between step 1 and 3 is a no go (error prone, tedious). How is that supported?

Remark: HANA Studio is able to run a script where both statements and lines in procedures are delimited by ";". Why isn't hdbsql able to do so?

Sorry, but I am coming from an Oracle background where I have been using TOAD/sqlplus with above workflow for years ..

Thanks,

Tobias

[richard_bremer · Accepted solution]

Hi Tobias,

in this case the only option I can think of is to also change the end-of-line-character in HANA Studio's SQL editor:

SAP HANA Studio -> Menu "Window" -> Preferences -> Administration Console -> SQL -> Change the "command separator" from semicolon to the same character that you'll be using in hdbsql.

It would be easier if you could just change the statement separator used within stored procedures - however, I'm not aware of an option to do this.

Best regards,

Richard

[Former Member · Accepted solution]

great! that works! now I can have the workflow as above ..

[tom_slee]

As a warning to other readers: I faced the same problem as the original poster. I tried setting the command delimiter in hdbsql and in HANA Studio to "GO", but found that I could no longer execute a statement like this:

CREATE TABLE CATEGORY ( ...

because the GO in "CATEGORY" was interpreted as a command delimiter.