Re: [bug #27823] SQLClient drops connections without sending notifications

[Nicola Pero]

> > At work, one part of our application is used to 'scan' data  
> > gathered from various devices, normalizes it, and inserts it into  
> > the database as measurements.  This is very performance-critical,  
> > and for a very large percentage of the cases, we are only ever  
> > inserting rows (new measurements).  However, on occasion we need to  
> > 'reprocess' the data that was originally gathered (for instance, if  
> > the model used to normalize the data has been modified to correct  
> > an error).  In this case, the same measurement rows will be  
> > inserted, albeit they may contain slightly different information.   
> > This component of the application has no knowledge of whether it is  
> > processing data for the first time or reprocessing it (and no need  
> > to know, either).  It first attempts to insert a row and if that  
> > fails due to a duplicate key constraint violation, it will do an  
> > update instead.  The overhead of querying the database first to see  
> > if the record is already there would in most cases be completely  
> > wasted, and in the rarer case would not save anything over simply  
> > attempting the insert in the first place.  This of course uses  
> > straight C and ODBC, but the principle is the same…if the ODBC  
> > drivers forced a disconnect on every constraint violation, we would  
> > have significantly worse performance, and would have to opt for the  
> > generally slower approach of querying first, since we can only  
> > commit a group of measurements for an interval on success of the  
> > entire scan (it either all goes in or none of it does).
>
> As I said I don't mind accepting a patch to allow things to not  
> disconnect on error, but your example really just re-enforces my  
> assertion that it should not be an issue.

What about simply having an option to choose between disconnect (or  
not) on error ?

I agree that disconnect on error is safer and should be the default,  
on the other hand it should be
trivial to have an option not to disconnect on error (and just do a  
quick cleanup of the connection). :-)

SQL does support errors, and sometimes they could be useful - for  
example, you wouldn't be able to
implement an SQL terminal using SQLClient if you can't have an option  
to not disconnect on error - if you
mistype a command in an SQL terminal, you'll get an error back, but  
the connection stays open.

It's just part of the standard features of SQL - if we make it an  
option, it would be really easy and non-intrusive.
If you need to use it, you turn it on, if not, you don't. ;-)

Thanks