v 16.0.0.2213

If a user name starts with an N we get an error when trying to reload the database structure. Using default choices throughout, the reload.sql file contains lines like:

GRANT INTEGRATED LOGIN TO "group\nigel.farage" AS USER "nigel.farage"
go
COMMENT ON INTEGRATED LOGIN "group\nigel.farage" IS NULL
Go

this generates errors:

Could not execute statement.
Syntax error near 'group
igel.farage' on line 1
SQLCODE=-131, ODBC 3 State="42000"
Line 142228, column 1
(Continuing after error)

Could not execute statement. Syntax error near 'group igel.farage' on line 1 SQLCODE=-131, ODBC 3 State="42000" Line 142231, column 1 (Continuing after error)

because the \n is being treated as a new line character. Manually editing the reload.sql file:
GRANT INTEGRATED LOGIN TO "group\\nigel.farage" AS USER "nigel.farage"
go
COMMENT ON INTEGRATED LOGIN "group\\nigel.farage" IS NULL
Go
fixes the problem.

I encountered the problem when unloading a v11 file to upgrade, but the problem persists when unloading v16 format databases as well. Problem is also present in build 2344 - see below.


Reported to SAP as incident ID:002075129400004752422016


Fixed by Engineering Case 804480 SAP SQL Anywhere Version 16.0.0 build 2417 SAP SQL Anywhere Version 17.0 build 2795

asked 03 Nov '16, 09:41

Justin%20Willey's gravatar image

Justin Willey
6.7k108138208
accept rate: 20%

edited 16 Jan, 09:46

Hm, what is the difference between the generated SQL file and your manual correction?

(I would expect that group members's names starting with t might also lead to problems...)

(03 Nov '16, 09:47) Volker Barth
Replies hidden

Help - we are being escaped again! You should see:

GRANT INTEGRATED LOGIN TO "group\\nigel.farage" AS USER "nigel.farage"

with double \ to make it work

(03 Nov '16, 09:49) Justin Willey
1

Justin, I see that you are using the DB server that is ~10 month old. What does the version "SQL Anywhere 16.0 SP65 Build 2344" generate?

(04 Nov '16, 10:29) Vlad
Replies hidden

Hi Vlad - there's nothing in the release notes for 2344 that applies, but I tried it anyway (unloading and reloading in 2344) and the behaviour is just the same. The \n isn't escaped in the reload.sql file, so causes an error when it's run in the reload.

(04 Nov '16, 16:17) Justin Willey

Reported to SAP as incident ID:002075129400004752422016

Hm, I would have expected that anybody of the helpful SQL Anywhere engineers would have replied here, as well... - or am I too spoilt with their reactions on other FAQs?

(22 Dec '16, 16:14) Volker Barth

@Justin: Personally, I think it's easier to follow this FAQ when information on CRs/fixed issues is posted as an answer instead of an updated question... - just my 2 cents, anyway:)

(17 Jan, 01:04) Volker Barth
showing 4 of 6 show all flat view

It looks like a bug behavior change to me, since "group\nigel.farage" is not a string literal, it is a quoted identifier.

The bug behavior change was introduced in Version 10.

CREATE TABLE t ( "group\nigel.farage" INTEGER );
INSERT t VALUES ( 1 );
COMMIT;
SELECT @@VERSION, * FROM t;

-- SQL Anywhere 9
@@VERSION,group
igel.farage
'9.0.2.3951',1

-- SQL Anywhere 10
Could not execute statement.
Syntax error near 'group
igel.farage' on line 1
SQLCODE=-131, ODBC 3 State="42000"
Line 1, column 1
CREATE TABLE t ( "group\nigel.farage" INTEGER );

I wish there was a global server option that would completely turn off the stupid arcane unnecessary special character processing in string literals. Nobody needs \n to mean new line, but they ABSOLUTELY POSITIVELY FREQUENTLY DO NEED \ CHARACTERS in string literals.

permanent link

answered 05 Nov '16, 08:05

Breck%20Carter's gravatar image

Breck Carter
25.6k427586844
accept rate: 20%

1

Nobody needs \n to mean new line

Hm, I do often need new lines in strings - and probably most folks with a C-based background at least are aware of "\n" as a platform-independent notation for that.

However, an option would be handy, I agree.

(05 Nov '16, 08:59) Volker Barth

Breck, the noticed behaviour change between v9 and v10 seems to have to do with the fact that for v10 and above, identifiers (even quoted ones) are not allowed to contain (among others)

Control characters (characters with an ordinal value of less than 32, or the character value 127).. Backslashes

and therefore '\n', '\t' and '\' are not allowed there.


For v9 and below, the rules for (quoted) identifiers were less stringent:

Identifiers need to be enclosed in double quotes or square brackets if any of the following conditions are true:
[...]
The identifier contains characters other than alphabetic characters and digits.


However, I'n mot sure whether this does apply to Justin's case:

The user profile name used for integrated Windoes login names requires the '\' to separate domain and user name. Nevertheless, the docs leave open whether the <user-profile-name> is an identifier or not... - But apparently it is a bug if you can create such login names but they get messed up during a reload.

(07 Nov '16, 02:19) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×198
×40

question asked: 03 Nov '16, 09:41

question was seen: 6,121 times

last updated: 17 Jan, 01:04