Groups > comp.databases.ms-sqlserver > #1207

Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX

From	Gene Wirchenko <genew@ocis.net>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX
Date	2012-08-01 13:30 -0700
Organization	A noiseless patient Spider
Message-ID	<a34j181udim2ml5urm5qcnm7v3c546sbsf@4ax.com> (permalink)
References	(1 earlier) <XnsA0A17141AB34CYazorman@127.0.0.1> <cj0g181jboo2s984amhsd95199t3cs5jes@4ax.com> <XnsA0A1DD643649CYazorman@127.0.0.1> <fflg18908osncqaa9ls5v0im6tcgmo5hv7@4ax.com> <XnsA0A2D578DA97DYazorman@127.0.0.1>

Show all headers | View raw

On Wed, 01 Aug 2012 20:59:06 +0200, Erland Sommarskog
<esquel@sommarskog.se> wrote:

>Gene Wirchenko (genew@ocis.net) writes:
>>      It is not multiple errors.  It is multiple rows of error data
>> being returned.  Preliminarily, it is an error message row followed by
>> zero or more rows of inculpated column names.  e.g.
>>           OrderBy  ErrorResponse
>>              0     Foo must be less than bar.
>>              1     foo
>>              2     bar
>> 0 is the error message to be emitted.  1 and 2 are the involved
>> columns from which the front-end will select the first one in the form
>> tab sequence to set the focus to.
> 
>There are of course error checks that belongs in the database, because
>they work with the data. This does not the least apply to update procedures.

     Huh?  Of course it involves updates.  I do not want bad data
getting into the database.  That is the point of the error checks.

     Did you misstate, or am I missing some context here?

>However, I would recommend that you don't overdo it. T-SQL is not a very
>good language from a general programming perspective. Earlier this day
>I was battling how to formulate an error message that for a while read
>like:
>
>   Column(s) [a] exists only in the left table, but they are not at the end
>
>While the message can apply to multiple columns, I expect that most commonly
>there will be only one, so the bad grammar will hurts the eye. Had I written
>this code in Perl I might have crafted a message that was correct both
>for singular and plural, but in T-SQL the code for that would be just too 
>verbose, and I eventually I found a way to convey the message that did
>not depend on verb inflection.

     I have already written some proof-of-concept code that does
return what I need.  To do so, I created a constraints table that has
the names of the columns associated with each constraint.  That is
where I get the columnnames that are returned to the browser.  I think
it will scale up.

>I think I've written something like you have in mind about once in my 
>career. And that was in SQL 2000 with no TRY-CATCH. With TRY-CATCH it 
>becomes even more difficult to produce multiple error message. Unless
>you return them as result sets, but then you need a clear protocol so
>that your client knows what result set gets back.

     My protocol that I have already described is simple.  I think it
will work.

>Something I've used a little more often is when I have had a table with 
>rows that needs to be processed, and each row is processed independently, or 
>at least there are groups of rows that are independent of each other.
>In such cases, I put a status column and a message column in the table
>where I write error status and message.

     Do you mean that your input table gets modified with the result?
Something like:
          SomeKey     }
          Data1       } Process
          Data2       } this.
          Data3       }
          ProcessingStatus           } What happened
          ProcessingErrorMessage     } goes here.

Sincerely,

Gene Wirchenko

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Gene Wirchenko <genew@ocis.net> - 2012-07-30 19:37 -0700
  Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Gene Wirchenko <genew@ocis.net> - 2012-07-30 19:47 -0700
  Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Erland Sommarskog <esquel@sommarskog.se> - 2012-07-31 09:08 +0000
    Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Gene Wirchenko <genew@ocis.net> - 2012-07-31 09:26 -0700
      Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2012-07-31 14:12 -0400
        Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Gene Wirchenko <genew@ocis.net> - 2012-07-31 15:02 -0700
          Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Erland Sommarskog <esquel@sommarskog.se> - 2012-08-01 21:04 +0200
      Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Erland Sommarskog <esquel@sommarskog.se> - 2012-07-31 21:45 +0200
        Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Gene Wirchenko <genew@ocis.net> - 2012-07-31 15:02 -0700
          Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Erland Sommarskog <esquel@sommarskog.se> - 2012-08-01 20:59 +0200
            Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Gene Wirchenko <genew@ocis.net> - 2012-08-01 13:30 -0700
              Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Erland Sommarskog <esquel@sommarskog.se> - 2012-08-01 23:27 +0200
  Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2012-07-31 14:25 -0400
    Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Gene Wirchenko <genew@ocis.net> - 2012-07-31 15:02 -0700
      Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Jeroen Mostert <jmostert@xs4all.nl> - 2012-08-01 00:26 +0200
        Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Gene Wirchenko <genew@ocis.net> - 2012-07-31 17:43 -0700
          Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2012-08-01 06:29 -0400
            Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Jeroen Mostert <jmostert@xs4all.nl> - 2012-08-01 20:40 +0200
              Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Erland Sommarskog <esquel@sommarskog.se> - 2012-08-01 21:02 +0200
              Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2012-08-01 16:02 -0400
          Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Jeroen Mostert <jmostert@xs4all.nl> - 2012-08-01 20:29 +0200
      Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Erland Sommarskog <esquel@sommarskog.se> - 2012-08-01 09:14 +0200
        Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Jason Keats <jkeats@melbpcDeleteThis.org.au> - 2012-08-04 18:14 +1000
          Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Erland Sommarskog <esquel@sommarskog.se> - 2012-08-04 21:15 +0200
            Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Jason Keats <jkeats@melbpcDeleteThis.org.au> - 2012-08-05 18:10 +1000
              Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Erland Sommarskog <esquel@sommarskog.se> - 2012-08-05 10:50 +0200
                Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Jason Keats <jkeats@melbpcDeleteThis.org.au> - 2012-08-06 00:03 +1000
                Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Erland Sommarskog <esquel@sommarskog.se> - 2012-08-05 20:21 +0200
                Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Jason Keats <jkeats@melbpcDeleteThis.org.au> - 2012-08-06 19:44 +1000
                Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Erland Sommarskog <esquel@sommarskog.se> - 2012-08-06 20:54 +0200

csiph-web