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.

Help with stored procedure

5 posts in General Discussion Last posting was on 2007-09-18 17:08:45.0Z
Geoffrey Chambers Posted on 2007-09-17 21:25:00.0Z
Sender: 7ff5.46e83eb2.1804289383@sybase.com
From: Geoffrey Chambers
Newsgroups: ianywhere.public.general
Subject: Help with stored procedure
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <46eef0ac.338b.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 17 Sep 2007 14:25:00 -0700
X-Trace: forums-1-dub 1190064300 10.22.241.41 (17 Sep 2007 14:25:00 -0700)
X-Original-Trace: 17 Sep 2007 14:25:00 -0700, 10.22.241.41
Lines: 49
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6291
Article PK: 2817

Any reason why this does not work, it used to, the only
thing different was I changed description to varchar(25)
from char(25). When I try to execute it it would seem the
xDescription variable is not being filled with a value, so
the insert fails becasue description can't be NULL.


ALTER PROCEDURE "DBA"."AddToJobCost"(in cJob char(5),in
cSubjob char(3),in cCostCode char(6),in cCostType char(1),in
nQuantity decimal(12,2),in nAmount decimal(12,2))
--=======================================================================CPAS=
-- DBA.AddToJobCost - Updates qToDAte and aToDate in job
cost
--============================================================================
begin
declare xJob char(5);
declare xSubJob char(3);
declare xCostCode char(6);
declare xCostType char(1);
declare xDescription varchar(25);
declare cErrNotFound exception for sqlstate value '02000';
declare curCodes dynamic scroll cursor for select
T1.job,T1.subjob,T1.costcode,T1.costtype from
DBA.JobCost as T1 where T1.job = cJob and T1.subjob =
cSubjob and T1.costcode = cCostCode and T1.costtype =
cCostType;
declare curDesc dynamic scroll cursor for select
description from costcodes where costcode = cCostCode;
open curCodes;
open curDesc;
fetch next curCodes into xJob,xSubJob,xCostCode,xCostType;
if sqlstate = cErrNotFound then
fetch next curDesc into xDescription;
insert into DBA.JobCost
(job,subjob,costcode,costtype,description,qToDate,aToDate)
values
(cJob,cSubJob,cCostCode,cCostType,xdescription,nQuantity,nAmount);
insert into DBA.Specification
(job,subjob,costcode,costtype,spec_no,description,owner,rowner
,subs,rsubs) values
(cJob,cSubJob,cCostCode,cCostType,cCostCode,xDescription,0,0
,0,0)
else
update DBA.JobCost set qToDate = nQuantity,aToDate =
nAmount
end if;
close curCodes;
close curDesc
end


Breck Carter [Team iAnywhere] Posted on 2007-09-18 17:08:45.0Z
From: "Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: Help with stored procedure
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <0e10f39no5lhtdglcd43g3umrn7kahijen@4ax.com>
References: <46eef0ac.338b.1681692777@sybase.com>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: bcarter.sentex.ca
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
Date: 18 Sep 2007 10:08:45 -0700
X-Trace: forums-1-dub 1190135325 64.7.134.118 (18 Sep 2007 10:08:45 -0700)
X-Original-Trace: 18 Sep 2007 10:08:45 -0700, bcarter.sentex.ca
Lines: 64
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6295
Article PK: 4691

WAG: Are you sure fetch next curDesc into xDescription returns a row?
If not, then the local variable xDescription will probably retain its
default NULL value.

Breck

On 17 Sep 2007 14:25:00 -0700, Geoffrey Chambers wrote:

>Any reason why this does not work, it used to, the only
>thing different was I changed description to varchar(25)
>from char(25). When I try to execute it it would seem the
>xDescription variable is not being filled with a value, so
>the insert fails becasue description can't be NULL.
>
>
>ALTER PROCEDURE "DBA"."AddToJobCost"(in cJob char(5),in
>cSubjob char(3),in cCostCode char(6),in cCostType char(1),in
>nQuantity decimal(12,2),in nAmount decimal(12,2))
>--=======================================================================CPAS=
>-- DBA.AddToJobCost - Updates qToDAte and aToDate in job
>cost
>--============================================================================
>begin
> declare xJob char(5);
> declare xSubJob char(3);
> declare xCostCode char(6);
> declare xCostType char(1);
> declare xDescription varchar(25);
> declare cErrNotFound exception for sqlstate value '02000';
> declare curCodes dynamic scroll cursor for select
>T1.job,T1.subjob,T1.costcode,T1.costtype from
> DBA.JobCost as T1 where T1.job = cJob and T1.subjob =
>cSubjob and T1.costcode = cCostCode and T1.costtype =
>cCostType;
> declare curDesc dynamic scroll cursor for select
>description from costcodes where costcode = cCostCode;
> open curCodes;
> open curDesc;
> fetch next curCodes into xJob,xSubJob,xCostCode,xCostType;
> if sqlstate = cErrNotFound then
> fetch next curDesc into xDescription;
> insert into DBA.JobCost
>(job,subjob,costcode,costtype,description,qToDate,aToDate)
>values
>(cJob,cSubJob,cCostCode,cCostType,xdescription,nQuantity,nAmount);
> insert into DBA.Specification
>(job,subjob,costcode,costtype,spec_no,description,owner,rowner
>,subs,rsubs) values
>(cJob,cSubJob,cCostCode,cCostType,cCostCode,xDescription,0,0
>,0,0)
> else
> update DBA.JobCost set qToDate = nQuantity,aToDate =
>nAmount
> end if;
> close curCodes;
> close curDesc
>end

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
breck.carter@risingroad.com


Arthur Hefti Posted on 2007-09-18 03:20:09.0Z
Reply-To: "Arthur Hefti" <arthur@catsoft.ch>
From: "Arthur Hefti" <arthur@catsoft.ch>
Newsgroups: ianywhere.public.general
References: <46eef0ac.338b.1681692777@sybase.com>
Subject: Re: Help with stored procedure
Lines: 62
Organization: CATsoft Development GmbH
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3138
NNTP-Posting-Host: gprs01.swisscom-mobile.ch
X-Original-NNTP-Posting-Host: gprs01.swisscom-mobile.ch
Message-ID: <46ef43e9@forums-1-dub>
Date: 17 Sep 2007 20:20:09 -0700
X-Trace: forums-1-dub 1190085609 193.247.250.1 (17 Sep 2007 20:20:09 -0700)
X-Original-Trace: 17 Sep 2007 20:20:09 -0700, gprs01.swisscom-mobile.ch
X-Authenticated-User: pb110beta
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6292
Article PK: 4692

Hi

what's the type of the column in the database? Did you try to debug or
output some variable values through "message"? I would be quite interesting
what the value of xDescription is after the select.

Regards
Arthur

<Geoffrey Chambers> wrote in message
news:46eef0ac.338b.1681692777@sybase.com...
> Any reason why this does not work, it used to, the only
> thing different was I changed description to varchar(25)
> from char(25). When I try to execute it it would seem the
> xDescription variable is not being filled with a value, so
> the insert fails becasue description can't be NULL.
>
>
> ALTER PROCEDURE "DBA"."AddToJobCost"(in cJob char(5),in
> cSubjob char(3),in cCostCode char(6),in cCostType char(1),in
> nQuantity decimal(12,2),in nAmount decimal(12,2))
> --=======================================================================CPAS=
> -- DBA.AddToJobCost - Updates qToDAte and aToDate in job
> cost
> --============================================================================
> begin
> declare xJob char(5);
> declare xSubJob char(3);
> declare xCostCode char(6);
> declare xCostType char(1);
> declare xDescription varchar(25);
> declare cErrNotFound exception for sqlstate value '02000';
> declare curCodes dynamic scroll cursor for select
> T1.job,T1.subjob,T1.costcode,T1.costtype from
> DBA.JobCost as T1 where T1.job = cJob and T1.subjob =
> cSubjob and T1.costcode = cCostCode and T1.costtype =
> cCostType;
> declare curDesc dynamic scroll cursor for select
> description from costcodes where costcode = cCostCode;
> open curCodes;
> open curDesc;
> fetch next curCodes into xJob,xSubJob,xCostCode,xCostType;
> if sqlstate = cErrNotFound then
> fetch next curDesc into xDescription;
> insert into DBA.JobCost
> (job,subjob,costcode,costtype,description,qToDate,aToDate)
> values
> (cJob,cSubJob,cCostCode,cCostType,xdescription,nQuantity,nAmount);
> insert into DBA.Specification
> (job,subjob,costcode,costtype,spec_no,description,owner,rowner
> ,subs,rsubs) values
> (cJob,cSubJob,cCostCode,cCostType,cCostCode,xDescription,0,0
> ,0,0)
> else
> update DBA.JobCost set qToDate = nQuantity,aToDate =
> nAmount
> end if;
> close curCodes;
> close curDesc
> end