[IQUG] Unique Constraint Failure Not Trappable

William Kennedy kennb53 at icloud.com
Fri Dec 16 05:50:03 MST 2016


Hi Mark,
thanks for your reply.

I can repeat this with both interactive and quiet mode.  Yes I have dbisql and isql set to allow multiple result sets.

The following code gets the issue for me.  That is, the expected error “Tried to insert duplicate value…” is not displayed.  However, if I replace the “select @v = ( select max (a1) from unq )" statement with “select @v = max (a1) from unq” then I do get the expected error displayed.


The version I am running is: Sybase IQ/15.4.0.6567/111107/P/GA/Enterprise Linux64 - x86_64 - 2.6.9-67.0.4.ELsmp/64bit/2011-11-07 00:40:24


drop table if exists unq;
 
create table unq ( a1 int, a2 int, primary key( a1 ) );
 
declare @v int
 
insert into unq values ( 1,2)
insert into unq values ( 1,2)
select @v = ( select max (a1) from unq )
 



> On 14 Dec. 2016, at 02:40, Mumy, Mark <mark.mumy at sap.com> wrote:
> 
> Are you using dbisql interactively or in quiet, no gui mode?  Is dbisql set to allow multiple result sets?  If it is not set to return multiple result sets, dbisql only executes the final select statement.
>  
> Are you using TSQL or Watcom-SQL syntax?  If it is TSQL are you setting ON_TSQL_ERROR?
>  
> Can you run the same tests through ‘isql’ and see if you have the same behavior?
>  
> I’ve written a quick sample with TSQL and Watcom SQL but can’t seem to get the same behavior.
>  
> drop table if exists unq;
> go
> create table unq ( a1 int, a2 int, primary key( a1 ) );
> go
>  
> -- TSQL
> message 'TSQL Code =====>' to client;
> go
> begin
> declare @v int
>  
> insert into unq values ( 1,2)
> insert into unq values ( 1,2)
>  
> select @v = ( select max (a1) from unq )
> message 'T val: '||@v to client
> set @v = ( select max (a1) from unq )
> message 'T val: '||@v to client
> end
> go
>  
> -- WATCOM
> message 'WATCOM Code =====>' to client;
> go
> begin
> declare @v int;
>  
> insert into unq values ( 1,2);
> insert into unq values ( 1,2);
>  
> --select @v = ( select max (a1) from unq );
> --message 'W val: '||@v to client;
> set @v = ( select max (a1) from unq );
> message 'W val: '||@v to client;
> end
> go
>  
>  
> Mark
>  
> Mark Mumy
> Customer Innovation and Enterprise Platform |  SAP
> M +1 347-820-2136 | E mark.mumy at sap.com <mailto:mark.mumy at sap.com>
> My Blogs: https://blogs.sap.com/author/markmumy/ <https://blogs.sap.com/author/markmumy/>
>                       
> https://sap.na.pgiconnect.com/I825063 <https://sap.na.pgiconnect.com/I825063>
> Conference tel: 18663127353,,8035340905#
>                      
> <image001.png>
>  
> From: "iqug-bounces at iqug.org" <iqug-bounces at iqug.org> on behalf of William Kennedy <kennb53 at icloud.com>
> Date: Friday, December 9, 2016 at 03:06
> To: "iqug at iqug.org" <iqug at iqug.org>
> Subject: [IQUG] Unique Constraint Failure Not Trappable
>  
>  
> I have an INSERT statement which correctly fails when inserting rows that violate its unique constraint.  This is exactly what it should do of course.
>  
> However...
>  
> If this INSERT statement is immediately followed by a statement like:
> "SELECT @SomeVariable = (SELECT max(Something) FROM MyTable WHERE …)"
> then the client does not get the error message from the INSERT and the error cannot be trapped.
>  
> On the other hand if I express the query like this:
> "SELECT @SomeVariable = max(Something) FROM MyTable WHERE …"
> then the client does get the error message from the INSERT and it can be trapped and dealt with correctly.
>  
> In both cases the error message appears correctly in the server log.  It’s just that in the first case the message does not make it to the client (even if using dbisql on the server host).
>  
> In the request log the first statement gets converted to 
> "set @SomeVariable = (SELECT max(Something) FROM MyTable WHERE …)” 
> whereas the second statement gets converted to 
> “select max(Something) into @SomeVariable…”.
>  
> We easily work around the problem, of course, by making sure developers use statements like "SELECT @SomeVariable = max(Something) FROM MyTable WHERE …” rather than "SELECT @SomeVariable = (SELECT max(Something) FROM MyTable WHERE …)”.
>  
> Has anyone seen this or anything like it?
>  

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://iqug.org/pipermail/iqug/attachments/20161216/cd231d2e/attachment-0001.html>


More information about the IQUG mailing list