|
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: 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 I rather expected the output of that result set to be two rows containing one and two. The actual output, however, is two rows containing NULL and two. I find it bizarre that the variable @A is defined but does not get initialized to the specified value. Interestingly, the following change to the DECLARE statement provides the expected output: DECLARE @A INTEGER = 1, @B INTEGER = 2 What I don't understand is why repeating the DECLARE keyword fails as it does. Can somebody help me understand? Thanks in advance! |
|
This behaviour has confirmed to be an engine bug, fixed in CR #726396, 12.0.1.3845. Thank you for the bug report. FWIW, according 12.0.1 EBFs for Mac OS and Windows are available now.
(24 Feb '13, 07:29)
Volker Barth
|
|
The difference is the SQL dialect again (as in the other question). Derly uses Watcom-SQL, which does work correct w.r.t. the separate DECLARE statements, i.e. it enters 1 and 2. Your original sample is T-SQL, and it seems to be a bug/feature of the T-SQL DECLARE statement to (not) work that way... I can't tell whether this is expected or not, the docs don't treat the features of DECLARE to be dialect-dependent... If you are using SQL Anywhere and not also using MS SQL or Sybase ASE (which use T-SQL), I'd hightly recommend to use the Watcom-SQL dialect. Sure looks like a bug to me too. Do SQL Anywhere folks monitor this forum, or should I look into opening a case with them?
(06 Dec '12, 09:50)
JWilliston
Replies hidden
Volker is correct - the original statement you typed is interpreted by SQL Anywhere as Transact-SQL. If you'd like to see the reference for the T-SQL ("ASE-style") The Watcom-SQL compatible dialect supports assignment and multiple-variable declarations, and is indicated to the SQL parser by the semi-colons. To declare a local variable’s name and datatype use: declare @variable_name datatype [, @variable_name datatype].. Yes, this forum is monitored by staff from Sybase, however, if you are ever encountering an urgent issue that you need an immediate response on, then no, this forum is not an appropriate venue to raise this type of issue with us. For these important cases/questions, you should open an official technical support case with technical support so that we can ensure that your issue/question is examined with the appropriate priority.
(06 Dec '12, 11:05)
Jeff Albion
Well, I had liked to say that the T-SQL DECLARE statement in v12 does not support an initialization at all, but according to these samples, it partially does... which sounds buggy IMHO.
(06 Dec '12, 11:08)
Volker Barth
Following the documentation on DCX DECLARE Statement it looks like a default value is valid in T-SQL too. Only DECLARE ... EXCEPTION is explicitly excluded from T-SQL. I've tried this one (12.0.1.3769): BEGIN DECLARE @myvar INTEGER DEFAULT 17 SELECT @myvar END and it works, also if used as a procedure.
(06 Dec '12, 11:24)
Reimer Pods
The DEFAULT clause added as below doesn't fix the original post (with 12.0.1.3484). BEGIN DECLARE @A INTEGER DEFAULT 1 DECLARE @B INTEGER DEFAULT 2 CREATE LOCAL TEMPORARY TABLE MyIntegers ( MyInteger INTEGER ) INSERT INTO MyIntegers ( MyInteger ) VALUES (@A) INSERT INTO MyIntegers ( MyInteger ) VALUES (@B) SELECT * FROM MyIntegers END MyInteger (Null) 2
(06 Dec '12, 12:01)
Derli Marcochi
Yep, therefore we do discuss that this is a bug and not a "T-SQL has no DECLARE-with-init feature at all":)
(06 Dec '12, 12:07)
Volker Barth
1
Internally checking the SQL Anywhere engine grammar currently suggests that this is documentation error (as there are two separate language tokens for DECLARE), so we will have to further investigate what our intended behaviour is here. (Specifically, if DECLARE needs a specific "T-SQL" version in the documentation or if something else is supposed to happen here with the engine). I have opened this investigation as CR #726396. Thank you to everyone in the thread who gave their impressions for the report.
(06 Dec '12, 15:55)
Jeff Albion
|
|
There is a nice example of the SET statement in the documentation. Hope it helps. 1
That does illustrate certain uses of the DECLARE statement, but that's not the question. The question is why the particular use of the DECLARE statement I've described does what it does. Thanks for trying.
(05 Dec '12, 12:55)
JWilliston
Yes indeed, that does work. In fact, I'd already found it, and it led me to a problem I submitted in a separate question (http://sqlanywhere-forum.sap.com/questions/14659/declare-statement-syntax-error-in-a-beginend-block-but-only-with-semi-colons). But it still doesn't answer the question at hand: why does repeating the DECLARE keyword fail as it does? If it's a syntax error, then why doesn't it produce a syntax error message? And if it's valid syntax, then why does it declare the variable without initializing it?
(05 Dec '12, 13:26)
JWilliston
|
|
How about? 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 |
What version (and build) of SA are you using?
Sybase Central shows SQL Anywhere 12 12.0.1.3797.