cancel
Showing results for 
Search instead for 
Did you mean: 

Why is the semicolon wrong?

MCMartin
Participant
2,861

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?

chris_keating
Product and Topic Expert
Product and Topic Expert

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.

VolkerBarth
Contributor

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'';')
MCMartin
Participant

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

Breck_Carter
Participant
0 Kudos

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.

VolkerBarth
Contributor
0 Kudos

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" 🙂

Accepted Solutions (1)

Accepted Solutions (1)

chris_keating
Product and Topic Expert
Product and Topic Expert

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
chris_keating
Product and Topic Expert
Product and Topic Expert

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.

Answers (1)

Answers (1)

MarkCulp
Participant

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.