[IQUG] Unique Constraint Failure Not Trappable

William Kennedy kennb53 at icloud.com
Fri Dec 16 14:59:02 MST 2016


Thanks Mark, makes sense.

> On 17 Dec. 2016, at 08:06, Mumy, Mark <mark.mumy at sap.com> wrote:
> 
> The bug had something to do with the parenthesis if I remember.  That’s treated as a sub-process or something to that effect, so the messages weren’t properly returned to the calling client.
>  
> 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: William Kennedy <kennb53 at icloud.com>
> Date: Friday, December 16, 2016 at 13:35
> To: Mark Mumy <mark.mumy at sap.com>
> Cc: "iqug at iqug.org" <iqug at iqug.org>
> Subject: Re: [IQUG] Unique Constraint Failure Not Trappable
>  
> Hi Mark,
> yes I agree - upgrading is the obvious thing to do and I am cracking that whip.
>  
> In the mean time I have been asking developers to work around it by not using “select @v = ( select max (a1) from unq )” but asking them to use “select @v = max (a1) from unq” instead (which works).
>  
> As mentioned at the start of the thread below this is not a "big issue” item because "We easily work around the problem…”.  However it is an interesting one and I had not been able to get my hands on IQ 16 to test if it was resolved now.
>  
> Since you have not been able to duplicate it on IQ 16 I guess it’s resolved so thank you very much for doing the IQ 16 testing for me - sincerely appreciated.
>  
> Cheers and thanks
> Bill
>  
>  
>  
>> On 17 Dec. 2016, at 04:10, Mumy, Mark <mark.mumy at sap.com <mailto:mark.mumy at sap.com>> wrote:
>>  
>> Might I suggest you upgrade?  You’re running IQ 15.4 GA.  It’s at least 5 years old.  It is also now unsupported as of 30 Nov 2016.  That means you can’t even get service packs for it any more.
>> https://wiki.scn.sap.com/wiki/display/SYBIQ/SAP+IQ+15+End+of+Maintenance+Report <https://wiki.scn.sap.com/wiki/display/SYBIQ/SAP+IQ+15+End+of+Maintenance+Report>
>>  
>> I’ve done my testing on IQ 16 SP11 so clearly we’ve seen and fixed this issue since IQ 15.4 was released.  I’m not sure how to work around it other than an upgrade to get the fix integrated.
>>  
>> 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: William Kennedy <kennb53 at icloud.com <mailto:kennb53 at icloud.com>>
>> Date: Friday, December 16, 2016 at 06:50
>> To: Mark Mumy <mark.mumy at sap.com <mailto:mark.mumy at sap.com>>
>> Cc: "iqug at iqug.org <mailto:iqug at iqug.org>" <iqug at iqug.org <mailto:iqug at iqug.org>>
>> Subject: Re: [IQUG] Unique Constraint Failure Not Trappable
>>  
>> 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 <mailto: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 <mailto:iqug-bounces at iqug.org>" <iqug-bounces at iqug.org <mailto:iqug-bounces at iqug.org>> on behalf of William Kennedy <kennb53 at icloud.com <mailto:kennb53 at icloud.com>>
>>> Date: Friday, December 9, 2016 at 03:06
>>> To: "iqug at iqug.org <mailto:iqug at iqug.org>" <iqug at iqug.org <mailto: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/20161217/0bedc492/attachment-0001.html>


More information about the IQUG mailing list