on 2012 Dec 05 12:15 PM
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:
Thanks in advance for any light y'all can shed on this issue.
Request clarification before answering.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
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.
User | Count |
---|---|
60 | |
10 | |
8 | |
8 | |
7 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.