I'm a relative newcomer to SQL Anywhere, and I keep finding myself beating my head against the DECLARE statement, hard as that might be to believe. Consider the following code which works just fine:

BEGIN
    DECLARE @A INTEGER = 1;
    DECLARE @B INTEGER = 2;
    CREATE LOCAL TEMPORARY TABLE MyIntegers ( MyInteger INTEGER );
    INSERT INTO MyIntegers ( MyInteger ) VALUES (@A);
    INSERT INTO MyIntegers ( MyInteger ) VALUES (@B);
    SELECT * FROM MyIntegers;
END

Now consider a minor variant which moves the declaration of the second variable down a few lines:

BEGIN
    DECLARE @A INTEGER = 1;
    DROP TABLE IF EXISTS MyIntegers;
    CREATE LOCAL TEMPORARY TABLE MyIntegers ( MyInteger INTEGER );
    DECLARE @B INTEGER = 2;
    INSERT INTO MyIntegers ( MyInteger ) VALUES (@A);
    INSERT INTO MyIntegers ( MyInteger ) VALUES (@B);
    SELECT * FROM MyIntegers;
END

When I execute this variant I get an ISQL Error that says Could not execute statement. Syntax error near 'DECLARE' on line 5 SQLCODE=-131, ODBC 3 State="42000". Study of this gives rise to two questions:

  1. How is that a syntax error when only statement order has changed, and in what seems a valid way?
  2. Even more bizarre is this: why does it work as expected if I remove the semi-colons?!

Thanks in advance for any light y'all can shed on this issue.

asked 05 Dec '12, 12:15

JWilliston's gravatar image

JWilliston
1064410
accept rate: 0%


The order of the SQL can be important depending on which dialect of SQL you use.

In your first example you are using a WATCOM SQL block BEGIN ... END statement, and within a SQL block all local declarations (i.e. DECLARE statements) must be prior to any executable (non-declarative) statement. In your case you have a DROP TABLE and a CREATE ... TABLE before the second DECLARE @B statement and hence this violates the WATCOM SQL rules.

Please refer to the documentation on BEGIN statement for more information.

If you remove the semicolons then the statement is TRANSACT SQL syntax and the rules are slightly different - a statement block is composed of a sequence of statements in any order. See the TSQL BEGIN statement documentation.

permanent link

answered 05 Dec '12, 13:41

Mark%20Culp's gravatar image

Mark Culp
24.9k10141297
accept rate: 41%

Excellent! Thanks for clarifying that. The amazing thing is that I've been using SQL databases since the early 1990s and have never run into that before.

(05 Dec '12, 15:11) JWilliston

I guess I have to add another comment after reviewing that documentation more closely. I looked at that yesterday and saw the "variable-declaration", but the subsequent body of the BEGIN/END block is defined only as a "statement-list". And given that DECLARE is a valid statement, I guess I falsely assumed it was a valid statement within a "statement-list". Are there other such caveats; i.e., statements that can't be used in a statement-list in such a block?

(06 Dec '12, 09:56) JWilliston
Replies hidden
1

Note that in WATCOM SQL statement-list does not include declarations. In TSQL statement-list does allow declarations. Yes, this is confusing :-(

There are many differences between WATCOM SQL and TSQL and they are hard to enumerate because some are very subtle. A simple way to see the high level differences is to take note of the [TSQL] annotations in the documentation - if a statement is annotated with [TSQL] then the statement is part of the TSQL dialect and should not be mixed with WATCOM SQL dialect. Statements that do not contain a [TSQL] annotation are either WATCOM SQL dialect or are consisten with/ok to be used with WATCOM SQL procedures.

(06 Dec '12, 10:30) Mark Culp
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:

×3

question asked: 05 Dec '12, 12:15

question was seen: 4,861 times

last updated: 06 Dec '12, 10:30