Strings containing the command separator character

[Former Member]

Hi,

If a string contains the command separator MaxDB doesn't parse SQL statements correctly. I have SQL several Files containing the ; character as termination of the commands (as used in most other DBs). When I import the file using

sqlcli.exe -n myhost -d DB1 -u USER,PASS -m -c ; \i c:\test.sql

the execution of the file fails when a statement contains the ; in a string. The error can be reproduced with a file containing following statements:

CREATE TABLE TEST_TABLE

(

TEST VARCHAR(128)

);

INSERT INTO TEST_TABLE (TEST) values ('test');

INSERT INTO TEST_TABLE (TEST) values (';
:');

DROP TABLE TEST_TABLE;

The first insert statement works, the second doesnu2019t.... Is there any other solution than turning of multi-line mode or trying to find a line separator which is not contained in any string? I am using SQLCLI version 7.6.3.

Regards,

Tarik

[lbreddemann]

Hi Tarik,

check the documentation...

When used in Batchmode you don't need to switch to multiline mode as it is already used.

The standard delimiter between two statements is then '//'.

If you want to change that, use the -c ; command line option.

Anyhow - the parsing issue unfortunately still remains.

Therefore, the best workaround is: replace the ";" delimiters with "//".

regards,

Lars

[Former Member]

Hello,

the output of sqlcli is

sqlcli -d db -u mona,red \i test.sql

0 rows affected (1579 usec)

1 row affected (594 usec)

1 row affected (433 usec)

0 rows affected (4670 usec)

What error do you get?

Please omit the options -m -c ;

Best Regards

Wolfgang

[Former Member]

Hi Wolfgang

That's strange... I get the output:

sqlcli.exe -n myhost -d DB1 -u USER,PASS \i C:\test.sql

0 rows affected (4 msec)

1 row affected (3 msec)

• -3014: POS(40) Invalid end of SQL statement SQLSTATE: 42000

• -3014: POS(40) Invalid end of SQL statement SQLSTATE: 42000

0 rows affected (3 msec)

It works fine as long as the inserted string doesn't contain a //

The only difference I see is that i specify the host...

Can you post the output printed with \s?

sqlcli=> \s

host : myhost

database : DB1

xuserkey :

user : USER

kernel version: KERNEL 7.6.03 BUILD 012-123-169-237

sapdbc version: libSQLDBC 7.6.3 BUILD 012-123-169-237

unicode : YES

default code : ASCII

sql mode : INTERNAL

autocommit : ON

Thanks & Regards,

Tarik

[Former Member]

Hello,

is it possible you post the content of the test.sql again?

Best Regards

Wolfgang

[Former Member]

CREATE TABLE TEST_TABLE

(

TEST VARCHAR(128)

)

//

INSERT INTO TEST_TABLE (TEST) values ('test')

//

INSERT INTO TEST_TABLE (TEST) values (';//:')

//

DROP TABLE TEST_TABLE

[Former Member]

Hello,

you have to use a command separator that is not inside your values.

e.g.:

sqlcli -d db -u mona,red -c * \i test.sql

and

INSERT INTO TEST_TABLE (TEST) values (';//:')

*

INSERT INTO TEST_TABLE (TEST) values (';//:')

Best Regards

Wolfgang

[Former Member]

Hi Wolfgang

I actually wanted to prevent having to restrict the values inserted in any way (depending on the separator I use)... But right now that is the only solution I see.

Best regards,

Tarik

[Former Member]

Hello,

please use // instead of ; to separate commands.

CREATE TABLE TEST_TABLE

(

TEST VARCHAR(128)

)

//

INSERT INTO TEST_TABLE (TEST) values ('test')

//

INSERT INTO TEST_TABLE (TEST) values (';
:')

//

DROP TABLE TEST_TABLE

The script above works without problems.

Best Regards

Wolfgang

[Former Member]

Hi Wolfgang

At least on my server it doesn't work any more as soon as you change the string to ';//:'

CREATE TABLE TEST_TABLE

(

TEST VARCHAR(128)

)

//

INSERT INTO TEST_TABLE (TEST) values ('test')

//

INSERT INTO TEST_TABLE (TEST) values (';//:')

//

DROP TABLE TEST_TABLE

//

Regards,

Tarik