Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Error in SQL Script Ver 7 and Not in 8

2 posts in General Discussion Last posting was on 2003-08-20 16:51:05.0Z
Richard Posted on 2003-08-20 14:54:01.0Z
From: "Richard" <Richardr@ldsinc.com>
Newsgroups: ianywhere.public.general
Subject: Error in SQL Script Ver 7 and Not in 8
Lines: 70
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Original-NNTP-Posting-Host: lds253.ldsinc.com
Message-ID: <3f438ba1$1@forums-2-dub>
X-Original-Trace: 20 Aug 2003 07:54:25 -0700, lds253.ldsinc.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 20 Aug 2003 07:53:13 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 20 Aug 2003 07:54:01 -0700
X-Trace: forums-1-dub 1061391241 10.22.108.75 (20 Aug 2003 07:54:01 -0700)
X-Original-Trace: 20 Aug 2003 07:54:01 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:1638
Article PK: 3863

Can anyone see why I am getting an error in the following script. It works
in Version 8 but not in Version 7

Error message: "Could not execute statement. ASA-Error-131:Syntax error near
'c'"


/***********************************************/
/** Create supporting Stored Procedures **/
/***********************************************/
IF Object_ID('LDS.sp_Get_Configuration') IS NOT NULL THEN
DROP Procedure LDS.sp_Get_Configuration
END IF;
CREATE PROCEDURE LDS.sp_Get_Configuration ( @KeyID integer )
AS
set NOCOUNT on
SELECT Billing_Rates_CategoryType
FROM LDS.Billing_Rates_Category brc
WHERE brc.Billing_Rates_CategoryKeyID = @KeyID
if @@Error <> 0 return-1
else return 0;

IF Object_ID('LDS.sp_Replace_Configuration') IS NOT NULL THEN
DROP Procedure LDS.sp_Replace_Configuration
END IF;
CREATE PROCEDURE LDS.sp_Replace_Configuration (
@TokenValue varchar(8000),
@TokenSize integer,
@KeyID integer )
AS
SET NOCOUNT ON
--Create Temp Array/results table to process string
Create TABLE #array(k1 int identity, arraycol varchar(8000))
Create TABLE #results(arrayVal varchar(8000))
--Insert Token string into working array
Insert #array (arraycol) values (@TokenValue)
--Declare working variables
Declare @arrayvar varchar(8000)
Declare @i integer
Declare @l integer
Declare c cursor for select arraycol from #array
Open c
FETCH c INTO @arrayvar
--Test SQLState and Main Loop
While SQLSTATE <> '02000' Begin
Select @i=0
Select @l=Datalength(@arrayvar)/@TokenSize
While (@i<@l) Begin
--parse through arrayvar and Insert token values into result table
Insert #results Select Substring(@arrayvar,(@i*@TokenSize)+1,@TokenSize)
Select @i=@i+1
End
FETCH c INTO @arrayvar
End
--Clean up cursor variables
Close c
Deallocate c
--Remove old values from table and insert new values
Delete From Billing_Rates_Category WHERE Billing_Rates_CategoryKeyID =@KeyID
Insert INTO Billing_Rates_Category
(Billing_Rates_CategoryKeyID,Billing_Rates_CategoryType)
Select @KeyID, RTRIM(arrayVal) From #results
--Delete temp tables
DROP TABLE #results
DROP TABLE #array
--Check to see if any errors
if @@Error <> 0 return-1
else return 0


"Bruce Hay" Posted on 2003-08-20 16:51:05.0Z
From: "Bruce Hay" <hay at sybase dot com>
Newsgroups: ianywhere.public.general
References: <3f438ba1$1@forums-2-dub>
Subject: Re: Error in SQL Script Ver 7 and Not in 8
Lines: 84
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
X-Original-NNTP-Posting-Host: hay-xp.sybase.com
Message-ID: <3f43a713@forums-2-dub>
X-Original-Trace: 20 Aug 2003 09:51:31 -0700, hay-xp.sybase.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 20 Aug 2003 09:50:18 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 20 Aug 2003 09:51:05 -0700
X-Trace: forums-1-dub 1061398265 10.22.108.75 (20 Aug 2003 09:51:05 -0700)
X-Original-Trace: 20 Aug 2003 09:51:05 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:1641
Article PK: 3866

In version 7, the DEALLOCATE statement requires the CURSOR keyword, as in
DEALLOCATE CURSOR c
In 8.0.2, the CURSOR keyword is optional.

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

"Richard" <Richardr@ldsinc.com> wrote in message
news:3f438ba1$1@forums-2-dub...
> Can anyone see why I am getting an error in the following script. It
works
> in Version 8 but not in Version 7
>
> Error message: "Could not execute statement. ASA-Error-131:Syntax error
near
> 'c'"
>
>
> /***********************************************/
> /** Create supporting Stored Procedures **/
> /***********************************************/
> IF Object_ID('LDS.sp_Get_Configuration') IS NOT NULL THEN
> DROP Procedure LDS.sp_Get_Configuration
> END IF;
> CREATE PROCEDURE LDS.sp_Get_Configuration ( @KeyID integer )
> AS
> set NOCOUNT on
> SELECT Billing_Rates_CategoryType
> FROM LDS.Billing_Rates_Category brc
> WHERE brc.Billing_Rates_CategoryKeyID = @KeyID
> if @@Error <> 0 return-1
> else return 0;
>
> IF Object_ID('LDS.sp_Replace_Configuration') IS NOT NULL THEN
> DROP Procedure LDS.sp_Replace_Configuration
> END IF;
> CREATE PROCEDURE LDS.sp_Replace_Configuration (
> @TokenValue varchar(8000),
> @TokenSize integer,
> @KeyID integer )
> AS
> SET NOCOUNT ON
> --Create Temp Array/results table to process string
> Create TABLE #array(k1 int identity, arraycol varchar(8000))
> Create TABLE #results(arrayVal varchar(8000))
> --Insert Token string into working array
> Insert #array (arraycol) values (@TokenValue)
> --Declare working variables
> Declare @arrayvar varchar(8000)
> Declare @i integer
> Declare @l integer
> Declare c cursor for select arraycol from #array
> Open c
> FETCH c INTO @arrayvar
> --Test SQLState and Main Loop
> While SQLSTATE <> '02000' Begin
> Select @i=0
> Select @l=Datalength(@arrayvar)/@TokenSize
> While (@i<@l) Begin
> --parse through arrayvar and Insert token values into result table
> Insert #results Select Substring(@arrayvar,(@i*@TokenSize)+1,@TokenSize)
> Select @i=@i+1
> End
> FETCH c INTO @arrayvar
> End
> --Clean up cursor variables
> Close c
> Deallocate c
> --Remove old values from table and insert new values
> Delete From Billing_Rates_Category WHERE Billing_Rates_CategoryKeyID
=@KeyID
> Insert INTO Billing_Rates_Category
> (Billing_Rates_CategoryKeyID,Billing_Rates_CategoryType)
> Select @KeyID, RTRIM(arrayVal) From #results
> --Delete temp tables
> DROP TABLE #results
> DROP TABLE #array
> --Check to see if any errors
> if @@Error <> 0 return-1
> else return 0
>
>