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.

unique index violation and stored proc behavior

4 posts in General Discussion Last posting was on 2007-05-05 01:08:02.0Z
ericr Posted on 2007-05-01 19:40:03.0Z
From: ericr@trans-soft.net
User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: unique index violation and stored proc behavior
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: ip68-2-42-77.ph.ph.cox.net
X-Original-NNTP-Posting-Host: ip68-2-42-77.ph.ph.cox.net
Message-ID: <46379793$1@forums-1-dub>
Date: 1 May 2007 12:40:03 -0700
X-Trace: forums-1-dub 1178048403 68.2.42.77 (1 May 2007 12:40:03 -0700)
X-Original-Trace: 1 May 2007 12:40:03 -0700, ip68-2-42-77.ph.ph.cox.net
Lines: 39
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5965
Article PK: 1935

Hi,
I'm calling a stored proc I have that performs an insert into a table.
I then return the @@identity value and everything is fine.

If the unique index on my table is violated, the record is NOT inserted
but I get an @@identity value. If I use it to query the table, it's not
found.

If the insert fails I still get the @@identity.

Any and all insight greatly appreciated.

Eric

Details:

ASA 9.0.2.3228
Windows 2000


create table table1
( ID integer not null default autoincrement,
c2 varchar(10) not null
constraint PK_TBL_TABLE1 primary key (ID) )

create unique index indx1 on table1 (c2)

stored proc:

create proc proc1
( @c2 varchar(10) )
as
begin
insert table (c2 ) values ( @c2 )

select @id = @@identity

return ( @id )
end


Greg Fenton Posted on 2007-05-02 01:57:45.0Z
From: Greg Fenton <greg.fenton@googles-mail-site>
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: unique index violation and stored proc behavior
References: <46379793$1@forums-1-dub>
In-Reply-To: <46379793$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: cpe00096b10fe8a-cm000f212f9e50.cpe.net.cable.rogers.com
X-Original-NNTP-Posting-Host: cpe00096b10fe8a-cm000f212f9e50.cpe.net.cable.rogers.com
Message-ID: <4637f019$1@forums-1-dub>
Date: 1 May 2007 18:57:45 -0700
X-Trace: forums-1-dub 1178071065 74.122.201.248 (1 May 2007 18:57:45 -0700)
X-Original-Trace: 1 May 2007 18:57:45 -0700, cpe00096b10fe8a-cm000f212f9e50.cpe.net.cable.rogers.com
Lines: 28
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5966
Article PK: 1937


ericr@trans-soft.net wrote:
>
> If the unique index on my table is violated, the record is NOT inserted
> but I get an @@identity value. If I use it to query the table, it's not
> found.

You always need to check the SQLCODE at the end of any SQL statement to
see if a warning or error occured, but this is *especially* true for
statements that alter the data or schema (insert, update, delete,
create, alter, ...).

According to the SQL Anywhere 10.x docs, a failure on insert should not
affect the @@IDENTITY value:


http://www.ianywhere.com/developer/product_manuals/sqlanywhere/1000/en/html/dbrfen10/rf-identity-variables-wsqlref.html

But recognize that what you might be getting is the @@IDENTITY value
from the *previous* successful insert, and that would be to any table
that had an IDENTITY (i.e. autoincrement) column.

But the main issue with the code you posted is that you have to check
for errors.

g.f
--
Greg Fenton
Some Random Dude


ericr Posted on 2007-05-02 18:38:45.0Z
From: ericr@trans-soft.net
User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: unique index violation and stored proc behavior
References: <46379793$1@forums-1-dub> <4637f019$1@forums-1-dub>
In-Reply-To: <4637f019$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: ip68-2-42-77.ph.ph.cox.net
X-Original-NNTP-Posting-Host: ip68-2-42-77.ph.ph.cox.net
Message-ID: <4638dab5$1@forums-1-dub>
Date: 2 May 2007 11:38:45 -0700
X-Trace: forums-1-dub 1178131125 68.2.42.77 (2 May 2007 11:38:45 -0700)
X-Original-Trace: 2 May 2007 11:38:45 -0700, ip68-2-42-77.ph.ph.cox.net
Lines: 90
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5969
Article PK: 1939

Greg,

Thanks for the response. I'm still seeing something odd with this so
I'm sending what I'm using.

ALTER PROCEDURE s_etr_InsertHawb
(@HAWB varchar(25) = null
@HAWBDate date = null,
@Pieces integer = 0,
@Weight TSWeight = 0,
@OriginID integer = 0,
@DestID integer = 0 )
as
begin
declare @rc integer

insert into tbl_hawb( HAWB,HAWBDate,Weight,Pieces,OriginId,DestID )

values( @HAWB,@HAWBDate,@Weight,@Pieces,@OriginID,@DestID)


set @rc=@@error

if(@rc = 0)
return @@identity
else
return @rc

end

If I call the proc:

declare @prc integer

set @prc = s_et_InsertHawb ( 'etr10', '05/02/2007', 0, 0, 0, 0 )

message 'here is prc: ', @prc to client


If I call it with a known unique hawb it runs fine and returns the
@@IDENTITY value. I look and the record is there. When I try to send the
same hawb value, 'etr10', I get a return code of -6.

But, sometimes I get a value that is our range of next ID values (
autoincrement) with a known duplicate. I check the return codes value,
the @@IDENTITY and there is NO record.

Seems like it works most but not all of the times.

It's as if the insert occurs and then is deleted. There is an insert
trigger that has been the same for some time now.

I can actually 'plan' the @@IDENTITY I should get based on how many
times I try to insert a known bad.

Sorry this is such a long email but I wanted to try and explain it in a
little more context.

Thank you
Eric

Greg Fenton wrote:
> ericr@trans-soft.net wrote:
>
>>
>> If the unique index on my table is violated, the record is NOT
>> inserted but I get an @@identity value. If I use it to query the
>> table, it's not found.
>
>
> You always need to check the SQLCODE at the end of any SQL statement to
> see if a warning or error occured, but this is *especially* true for
> statements that alter the data or schema (insert, update, delete,
> create, alter, ...).
>
> According to the SQL Anywhere 10.x docs, a failure on insert should not
> affect the @@IDENTITY value:
>
>
> http://www.ianywhere.com/developer/product_manuals/sqlanywhere/1000/en/html/dbrfen10/rf-identity-variables-wsqlref.html
>
>
> But recognize that what you might be getting is the @@IDENTITY value
> from the *previous* successful insert, and that would be to any table
> that had an IDENTITY (i.e. autoincrement) column.
>
> But the main issue with the code you posted is that you have to check
> for errors.
>
> g.f


Greg Fenton Posted on 2007-05-05 01:08:02.0Z
Message-ID: <463BD8F3.8020605@googles-mail-site>
From: Greg Fenton <greg.fenton@googles-mail-site>
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: unique index violation and stored proc behavior
References: <46379793$1@forums-1-dub> <4637f019$1@forums-1-dub> <4638dab5$1@forums-1-dub>
In-Reply-To: <4638dab5$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: cpe00096b10fe8a-cm000f212f9e50.cpe.net.cable.rogers.com
X-Original-NNTP-Posting-Host: cpe00096b10fe8a-cm000f212f9e50.cpe.net.cable.rogers.com
Date: 4 May 2007 18:08:02 -0700
X-Trace: forums-1-dub 1178327282 74.122.201.248 (4 May 2007 18:08:02 -0700)
X-Original-Trace: 4 May 2007 18:08:02 -0700, cpe00096b10fe8a-cm000f212f9e50.cpe.net.cable.rogers.com
Lines: 32
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5977
Article PK: 1949


ericr@trans-soft.net wrote:
>
> set @rc=@@error
>

I typically use SQLCODE rather than @@error in my SP code. In the
online docs, see the page:

SQL Anywhere® 10 - Error Messages
- Database Error Messages

[Note to docs team: the page linked to from the index for
SQLCODE->special value is not terribly useful and the "see also" links
don't immediately lead to a definition of what SQLCODE actually is...And
I couldn't find any page relating @@error to SQLCODE]

I mostly stick with SQLCODE because I know it is Watcom SQL and I think
that @@error is a TSQL'ism.


I recommend that you put a MESSAGE statement in the IF block when @rc is
not 0 to see exactly what @rc is set to, and also MESSAGE out
ERRORMSG(@rc) [another reason to use SQLCODE...]

If you really want to see what is being inserted (and subsequently
committed or rollbacked] then consider translating the transaction log
(use dbtran) and take a look at what the engine is actually doing.

g.f
--
Greg Fenton
Some Random Dude