Can't get DECLARE statement to work

[Former Member]

Running ASA 11 version 11.0.1.2878

Hi all. I am trying run a SQL script from MS SQL in ASA11. I can’t figure out what is wrong with the syntax.

In MS SQL it look like this and runs OK:

DECLARE @newItemName as varchar(50)

In ASA 11 I believe it should look like this (my other declarations seem to work):

DECLARE @newItemName varchar(50 char);

But ASA refuses  to execute that statement. Here is the syntax reference I’m using.

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00801.1510/html/iqrefso/X...

I've tried several combinations and it's probably something simple but I'm just not seeing it!

Thanks in advance!

Tony

[jeff_albion · Accepted solution]

Hi Tony,

But ASA refuses  to execute that statement.

Can you clarify this problem? Does this mean that you get an error, or the statement doesn't have the effect you were expecting...?

The line executed on its own inside Interactive SQL is fine:

DECLARE @newItemName varchar(50 char);

Execution time: 0.003 seconds

SELECT @@version;

11.0.1.3069

Back to your original problem:

I am trying run a SQL script from MS SQL in ASA11. I can’t figure out what is wrong with the syntax.

Is the script originally written in Transact-SQL? SQL Anywhere has some native support for Transact-SQL statement syntax - but it looks like you are currently using Watcom SQL (the native SQL Anywhere dialect) for this statement (due to the semi-colon). Are you re-writing the script to target the Watcom SQL or Transact-SQL dialect?

Also be aware that in SQL Anywhere, the choice of SQL dialect will affect the ability to place "DECLARE" in a compound statement:

The body of a Watcom-SQL procedure or trigger is a compound statement, and variables must be declared with other declarations, such as a cursor declaration (DECLARE CURSOR), immediately following the BEGIN keyword. In a Transact-SQL procedure or trigger, there is no such restriction.

Regards,

Jeff Albion

SAP Active Global Support

[Former Member · Accepted solution]

Hi Jeff and thanks for your response. And those are excellent questions...that I wish I could answer. 

This was not my script originally, and I can tell you that I know just enought about SQL to make my head hurt. But, I believe this is the Watcom SQL variety.

And I was mislead by the error dialog, the statement that was really causing the problem was the previous statement. I was trying to initialize the integer variable with value. So no it seems it's my "SET" statement that doesn't run. Here is the entire script in all it's glory.

BEGIN

DECLARE externalId int;

SET externalId = 500000;                                <====== This one not happy

DECLARE newItemName varchar(50);

DECLARE nextkey int;

DECLARE nextUofmKey int;

DECLARE nextXAKey int;

DECLARE gonext int;

SET gonext = 10;                                            <====== assuming this one also not happy

While externalId < 800000

    SET externalId = externalId + gonext

    -- make an item

    exec cbord.cbo_getnextkey  'cbo1000p_item', nextkey out

    SET newItemName = 'NewLinkedItem_' + CAST(externalId AS VARCHAR(10))

    INSERT INTO cbord.cbo1000p_item

               (Item_intid,UnitId,KeyName,LongName,ItemTypeCode,ActivityLevel

               ,ConstructedFlag,TLC,updatecount,creation_date,ItemGuid,Creator

               ,ItemRecipeLockFlag)

         VALUES

               (nextkey,0,externalId,newItemName,1,9,'N',GetDate(),1,GetDate(),NEWID(),1,'N')

    -- Make a uofm

    exec cbord.cbo_getnextkey  'cbo1020p_ItemUofM', nextUofmKey out   

    INSERT INTO cbord.cbo1020p_ItemUofM

        (ItemUofm_Intid,Item_intid,itemuofm_keyname,itemuofm_usage,ItemUofM_Qty,MeasureType,[Description],BasisRatio,TLC,unitid)

        VALUES

        (nextUofMKey,nextkey,'UofM_' + CAST(externalId AS VARCHAR(10)),'P', 1, 'B', 'unit', 1, GetDate(), 0)

    -- Make an ItemXA record

    exec cbord.cbo_getnextkey  'cbo1001p_itemxa', nextXAKey out

    INSERT INTO    cbord.cbo1001p_itemxa

        (Itemxa_intid,Item_intid,srv_ShortName,UnitId,srv_itemuofm_intid,Yield,default_itemxa_intid,TLC,Yield_itemuofm_intid,purtax1flag,purtax2flag,purtax3flag,purtax4flag)

    VALUES

        (nextXAKey,nextkey,'SN_' + CAST(externalId AS VARCHAR(10)),0,nextUofMKey,1,nextXAKey,GetDate(),nextUofMKey,'N','N','N','N')

    if (externalId >=600000 AND externalId < 700000) BEGIN SET gonext = 5 SET externalId = 700000 END

END

GO

Thanks for your help Jeff!

Tony

[jeff_albion · Accepted solution]

Hi Tony,

I believe you're working with a Transact-SQL procedure. I can determine this because of these SQL constructs that appear in the code:

• While externalId < 800000 ( WHILE statement [T-SQL] )
• exec cbord.cbo_getnextkey ( EXECUTE statement [T-SQL] )
• if (externalId >= 600000 AND externalId < 700000) ( IF statement [T-SQL] )

---

The "Could not execute statement. Syntax error near 'DECLARE' on line 4" error is the Watcom-SQL 'CREATE VARIABLE' issue that I had identified in the help - your DECLARE statements in Watcom SQL have to be grouped together at the start of the compound statement:

BEGIN
  DECLARE externalId int;
  DECLARE newItemName varchar(50);
  DECLARE nextkey int;
  DECLARE nextUofmKey int;
  DECLARE nextXAKey int;
  DECLARE gonext int;
  SET externalId = 500000;  -- Happy Watcom SQL parsing!
  SET gonext = 10;
  ...

For your issue, I'm going to assume that you do not want convert this script to Watcom SQL and would like to remain using Transact-SQL. If so, there are two rules you need to use here:

1. Transact-SQL variables need to have "@" in front of their names
2. Do not separate statements via semi-colons - this indicates to SQL Anywhere that you would like to use Watcom SQL instead

So, your variable section for Transact-SQL should look like:

BEGIN
  DECLARE @externalId int
  SET @externalId = 500000  -- Happy T-SQL parsing!
  DECLARE @newItemName varchar(50)
  DECLARE @nextkey int
  DECLARE @nextUofmKey int
  DECLARE @nextXAKey int
  DECLARE @gonext int
  SET @gonext = 10
  ...

Regards,

Jeff Albion

SAP Active Global Support

[Former Member · Accepted solution]

Thanks for your help Jeff - that seems to have done the trick!

Tony