DECLARE statement defines a variable but does not set its value

[Former Member]

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!

[MarkCulp]

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

[Former Member]

Sybase Central shows SQL Anywhere 12 12.0.1.3797.

[jeff_albion]

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

Thank you for the bug report.

[VolkerBarth]

FWIW, according 12.0.1 EBFs for Mac OS and Windows are available now.

[VolkerBarth]

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.

[Former Member]

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?

[jeff_albion]

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.

[VolkerBarth]

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.

[reimer_pods]

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.

[former_SQLA_member1694870]

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

[VolkerBarth]

Yep, therefore we do discuss that this is a bug and not a "T-SQL has no DECLARE-with-init feature at all":)

[jeff_albion]

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.

[former_SQLA_member1694870]

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

[former_SQLA_member1694870]

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

Hope it helps.

[Former Member]

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.

[Former Member]

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 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?