with SQL Anywhere Network Server Version 17.0.10.6160 I experience the following effect:

SELECT MyCount = count() FROM Table WHERE TableID = '58693';

leads to the error "ERROR [42000] [Sybase][ODBC Driver][SQL Anywhere]Syntax error near ';' on line 1 " But

SELECT count() as MyCount FROM Table WHERE TableID = '58693';

works fine. Can anyone explain?

asked 22 Oct, 09:31

Martin's gravatar image

Martin
8.7k121157241
accept rate: 14%

3

I do not get a syntax error with the statement. Note that the first statement is TSQL dialect and the use of semicolons as a statement separator in TSQL was not supported until 17.0 and requires a database upgraded or created using 17. You can query the table syshistory to confirm the version of software used to create the database and whether the database has been upgraded and which version was used. That said, I also do not see an error with this statement using a v16 running on the same version of SA17 as you reported.

(22 Oct, 10:00) Chris Keating
Replies hidden
1

Interestingly enough, the first statement (used with MyTable instead of TABLE as table name for obvious reasons) returns 'Transact-SQL' both with 17.0.10.6175 and 16.0.0.2798:

select sqldialect('SELECT MyCount = count() FROM MyTable WHERE TableID = ''58693'';')
(22 Oct, 10:35) Volker Barth
2

You are right database was created using SA 16.0.0.1691, is that the culprit?

(22 Oct, 10:41) Martin

This is a message for future lurkers...

Sorry to be so late to the party, but...

SELECT count() as MyCount FROM Table WHERE TableID = '58693';

absolutely positively DOES NOT WORK on SQL Anywhere 16...

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

...or SQL Anywhere 17...

Could not execute statement.
Syntax error near 'WHERE' on line 1
SQLCODE=-131, ODBC 3 State="42000"
Line 1
SELECT count() as MyCount FROM Table WHERE TableID = '58693'

...with or without the semicolon.

Details matter, especially when exploring the absurd differences between Transact SQL and Watcom SQL.

Details like copy-and-pasting actual code when asking questions on this forum.

(27 Oct, 11:52) Breck Carter
Replies hidden

used with MyTable instead of TABLE as table name for obvious reasons...:

select sqldialect('SELECT MyCount = count() FROM MyTable WHERE TableID = ''58693'';')

"Obvious" was an exaggeration here, it took me a while to understand the "Error at character 30" when using "...FROM TABLE" :)

(27 Oct, 11:58) Volker Barth

Based on information provided in the support incident, I was able to reproduce this error. A database created pre-v17 and not upgraded will report a syntax error due to the trailing semicolon in some interfaces such as ODBC:

SELECT MyCount = count() FROM Table WHERE TableID = '58693';

To reproduce, I modified the sample odbcselect to execute:

SELECT mycount = count() FROM syshistory;

which reported the syntax error in a database created with 16.0 Build 1691 but not after upgrading the database using 17.0 build 6160 or with a database created with that same 17.0 release.

Several options to resolve this are available

  • remove the trailing semicolon which is not valid syntax for pre-v17 databases
  • rewrite the statement as Watcom-SQL (as in the 'working case' reported initially)
  • Upgrade or create the database using v17 software
permanent link

answered 27 Oct, 23:39

Chris%20Keating's gravatar image

Chris Keating
5.2k2988
accept rate: 33%

edited 27 Oct, 23:43

2

I have also reproduced this using the SQL Anywhere .NET driver with the same findings. There is a need to upgrade or create the database in v17 to get support for the semi-colon terminator for Transact-SQL based statements.

(28 Oct, 00:01) Chris Keating

My SQL is a bit rusty... but I believe the first syntax (which generates the error) is TSQL which does not use semicolons to separate statements, whereas the second is WATCOM SQL which does use semicolons.

permanent link

answered 22 Oct, 09:50

Mark%20Culp's gravatar image

Mark Culp
24.4k9137289
accept rate: 40%

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:

×174
×35
×18

question asked: 22 Oct, 09:31

question was seen: 162 times

last updated: 28 Oct, 00:01