Insert of special character with SAP HANA Studio

[MarkusRichter]

Dear Hana Community,

I am having a problem with an insert statement that tries to store a value with a few semicolons into a column of data type NVARCHAR. It looks like the following statement: insert into "SCHEMA"."RS_ORDER" values('014','hostname;schema;port','test1',0,'R')

Within the SAP HANA Studio I am using the SQL Console and I am getting constantly error - no matter what I am trying:

1.Execute the statement SET DEFINE OFF before the INSERT-> Seems to be not supported by HANA

2. Using \ delimiter as ,'hostname\;schema\;port'

3. Using " " for the entire value  ,"hostname;schema;port"

4.Using the escape key word like ,'hostname\;schema\;port','test1',0,'R') {escape '\'}

5. Using the sequence '||';'||' instead of the semicolon

None of the above solutions worked for me. In other databases some of them seem to be working. It would be great if someone can tell me the right statement on how to insert a value with a semicolon using SQL Console of SPA HANA Studio.

Thanks and Best Regards.

Markus

[former_member184768 · Accepted solution]

Hi Markus,

Not sure if I understood your issue correctly, but I can insert the data in a table without any issues or additional settings.

Can you please add the column names from RS_ORDER before values and ensure that the data is mapped to the correct columns.

Regards,

Ravi

[MarkusRichter · Accepted solution]

Hi Ravi,

thanks for the quick response. I tried it with the fully qualified column names. However, it still does not work. My tables is defined as follows:

Name	Key	SQL Data Type	Dim
SID	X	NVARCHAR	3
CI_HOST	X	NVARCHAR	256
TABLENAME	X	NVARCHAR	128
ORDERID		INTEGER
ACTION		NVARCHAR	1

If I insert the same row without any semicolon into the CI_HOST column, it works. Once I entered those additional semicolons, I am getting the error.

Any further hints what might be wrong?

Thanks and Best Regards.

Markus

[former_member184768 · Accepted solution]

Hi Markus,

Can you please post a screenshot of your error.

Regards,

Ravi

[lbreddemann]

Hi Markus,

for me the statement worked without any changes:

insert into "RS_ORDER" values('014','hostname;schema;port','test1',0,'R')

Would you mind to post the complete error message you receive in HANA studio?

- Lars

[MarkusRichter]

Hi All,

by reading that the statements works for all of you, I thought that my problem has to do with the way I am entering the statement. Instead of copy & paste from an example within a Wiki page, I entered it manually into the SQL console and now it works also for me 😉

Hence, the SQL console is not purely in ASCI mode that would eleminate any special formating options of other sources. My problem was that the single quote of my insert statement looks the same, as the it would appear from a keyboard. But it was not the case. Somehow the single quotes contained formating stuff. Anyway entering the entire insert manually worked in my case.

Thanks again for all your help. It helped really to know that it works for you and that HANA does not need any special escape sequence to insert special characters.

Best Regards.

Markus