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!

asked 05 Dec '12, 12:04

JWilliston's gravatar image

JWilliston
1064410
accept rate: 0%

What version (and build) of SA are you using?

(05 Dec '12, 13:43) Mark Culp

Sybase Central shows SQL Anywhere 12 12.0.1.3797.

(05 Dec '12, 15:52) JWilliston

This behaviour has confirmed to be an engine bug, fixed in CR #726396, 12.0.1.3845.

Thank you for the bug report.

permanent link

answered 15 Jan '13, 15:47

Jeff%20Albion's gravatar image

Jeff Albion
10.7k171174
accept rate: 24%

edited 15 Jan '13, 15:55

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.

permanent link

answered 06 Dec '12, 05:34

Volker%20Barth's gravatar image

Volker Barth
30.3k300452659
accept rate: 32%

edited 06 Dec '12, 05:36

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") DECLARE, you can see the reference here.

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

@myvar 17

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
showing 1 of 7 show all flat view

There is a nice example of the SET statement in the documentation.

Hope it helps.

permanent link

answered 05 Dec '12, 12:52

Derli%20Marcochi's gravatar image

Derli Marcochi
1.6k323877
accept rate: 33%

edited 05 Dec '12, 12:52

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
permanent link

answered 05 Dec '12, 13:15

Derli%20Marcochi's gravatar image

Derli Marcochi
1.6k323877
accept rate: 33%

edited 05 Dec '12, 18:28

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:04

question was seen: 2,524 times

last updated: 24 Feb '13, 07:29