strategy for getting friendly backend messages to client side
Keith G Hicks
Unique Index: IX_tblCustomers_CustName_Unique_ERRC5001ERRC_
Check Constraint: CK_tblCustomers_CustName_Size_ERRC5002ERRC_
The constraint is: (len(ltrim(rtrim([CustName]))) >= 5)
Then I have a table of codes as follows:
ErrorCode FriendlyMessage
----------- ------------------------------------
5001 Customer Name must be unique
5002 Customer Name must be at least 5 characters in length.
In my front end I read my error table into an array that can be read from
during program execution. In Delphi, the error message that comes up is via
E.Message and contains the entire message text from the backend. Since my
names contain _ERRC5001ERRC_ I can parse out the error number by looking at
what's between _ERRC and ERRC_ and then match it up with the code in the
array and grab the message text. I'm apply a similar strategy to the error
text in triggers and stored procedures.
I'm just wondering how this sounds to anyone with experience in this area.
Looking for any suggestions that might be helpful.
Thanks,
Keith
Jens Süßmeyer
Make a check constraint which calls the function with the appropiate
parameter and check for the thing you want then raise the error. Hwo can i
raise an error in a UDF ?
HTH, Jens Suessmeyer.
---
http://www.sqlserver2005.de
---
"Keith G Hicks" <k...@comcast.net> schrieb im Newsbeitrag
news:OK40%232YTF...@tk2msftngp13.phx.gbl...
Michael C#
sure you don't use error codes that already exist in SQL Server. According
to BOL, user-defined error message numbers should be "greater than 50,000".
As an example, in SQL Server, the error codes you gave have the following
meaning:
ErrorCode Standard SQL Message
----------- ------------------------------------
5001 User must be in the master database
5002 Database '%.*ls' does not exist. Check sysdatabases.
Personally I prefer to just return the standard SQL Error code and convert
it to a friendlier message (when possible) based on the context of the
current front-end operation; although custom errors can be kicked back to
the front end via RAISERROR as Jens pointed out. When you use RAISERROR,
you can treat your custom error just like any other error/exception in the
front end.
"Keith G Hicks" <k...@comcast.net> wrote in message
news:OK40%232YTF...@tk2msftngp13.phx.gbl...
Keith G Hicks
error number to the front end (But I suppose it would be better to make the
numbers larger to avoid confusion.). It's sort of a mock error number that's
embedded in then constraint name to match up with my own error table. The
front end only sees this (no error numbers):
Database error: '[Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE
statement conflicted with COLUMN CHECK constraint
'CK_tblCustomers_CustName'. The conflict occurred in database 'WidgetsInc',
table 'tblCustomers', column 'CustName'. [Microsoft][ODBC SQL Server
Driveer][SQL Server]The statement has been terminated.'
So by adding my mock error code to the constraint name, the front end sees
this:
Database error: '[Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE
statement conflicted with COLUMN CHECK constraint
'CK_tblCustomers_CustName_ERRC5001ERRC_'. The conflict occurred in database
'WidgetsInc', table 'tblCustomers', column 'CustName'. [Microsoft][ODBC SQL
Server Driveer][SQL Server]The statement has been terminated.'
Since it contains the string "_ERRC5001ERRC_" I can easily strip out "5001"
then match that up with my error table as I mentioned before. 5001 in this
case is not coming from RAISEERROR. The delphi code is like this:
iStartPos := Pos('_ERRC', E.Message) + 5;
iEndPos := Pos('ERRC_', E.Message);
sMsgToFind := MidStr(E.Message, iStartPos, iEndPos - iStartPos);
Windows.MessageBox(Screen.ActiveForm.Handle,
PChar(slBackendErrorMessages[slBackendErrorMessages.IndexOf(sMsgToFind) + 1]
+ CrLf + 'Hit ESC to restore the previous value.'), 'Widgets, Inc.',
MB_ICONEXCLAMATION or MB_OK)
Where E.Message is the error message that the backend passed to Delphi and
"slBackendErrorMessages" is a TStringList (like an array) that stores all
the values from my custom error message table.
2. I guess it's not clear from my posts that I have no idea how to get the
standard sql error code of a constraint (whether or not in SQL or in
Delphi). If I force a constraint to fire in enterprise manager, I still get
the text noted above without any error numbers. Jens response seemed to deal
primarily with UDFs and I found that will definitely be helpful for future
reference (I was in fact wondering about that earlier), but how do you work
wiht Check Constraints and Index Constraints? As far as I can see, you can't
use RAISEERROR in a constraint. So how can I get the friendly message to the
front end? Even if I could get the standard sql error code of a constraint,
wouldn't they be the same for all the Check COnstraints in my program? How
would I match them up with each specific need such as the CustName column
has to have at least 5 characters?
Can someone point me to some samples out there that handle getting friendly
warnings to the client side when dealing with these issues?
Keith
"Michael C#" <x...@abcdef.com> wrote in message
news:pwNce.18250$V02....@fe08.lga...
Michael C#
if I get SQL Server error # 2627 back on the front end after attempting to
insert a new employee, I know that I have violated the unique constraint
(i.e., I have attempted to insert a duplicate), so I can send the message
"That employee already exists in the database." If I receive error #2627
while trying to insert an inventory item I send the message "That item
already exists in the database." That's what I mean by modifying the error
messages based on the context of the operation, and utilizing SQL's
standard error codes.
You can use RAISERROR in Triggers, stored proc's, etc. I believe check
constraints are evaluated first though. Here is an example of going as low
as I know of to try to generate a user friendly error:
CREATE TABLE [dbo].[Table1] (
[x] [int] NOT NULL ,
[y] [varchar] (10)
)
GO
ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[x]
)
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER Table1_OnInsert
ON dbo.Table1
INSTEAD OF INSERT
AS
DECLARE @x INTEGER
DECLARE @y VARCHAR(10)
SELECT @x = t1.x
FROM Table1 t1, Inserted i
WHERE t1.x = i.x
SELECT @y = i.y
FROM Table1 t1, Inserted i
WHERE t1.y = i.y
IF @x IS NOT NULL
BEGIN
RAISERROR ('x value must be unique!', 16, 1)
ROLLBACK TRANSACTION
END
ELSE IF @y IS NOT NULL
BEGIN
RAISERROR ('y value must be unique!', 16, 1)
ROLLBACK TRANSACTION
END
ELSE
INSERT INTO Table1
SELECT x, y FROM Inserted
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--Now try this:
INSERT INTO Table1 VALUES (1, 'Hello') -- Successful
GO
INSERT INTO Table1 VALUES (1, 'Bye') -- Duplicate X, not successful
GO
INSERT INTO Table1 VALUES (2, 'Hello') -- Duplicate Y, not successful
GO
To me that's just way too much work though :) You can change your RAISERROR
statements to return numeric error codes instead of Text messages like I've
done here.
"Keith G Hicks" <k...@comcast.net> wrote in message
news:epN%23l0ZTF...@TK2MSFTNGP14.phx.gbl...
> 1. Just for clarity, I'm not using RAISEERROR to pass 5001 as an actual
> error number to the front end (But I suppose it would be better to make
> the
> numbers larger to avoid confusion.). It's sort of a mock error number
> that's
> embedded in then constraint name to match up with my own error table. The
> front end only sees this (no error numbers):
>
> So by adding my mock error code to the constraint name, the front end sees
> this:
>
Thomas Coleman
5 characters" type of validation in the business layer of your system or at
least in code that fires just prior to calling to the database. There is no
reason to go to the database for this type of checking. That said, I would still
add the check constraint to the database (with no special naming).
For uniqueness checks you can either catch built-in SQL error codes (my
recommendation) or create custom error codes and Raiserror to catch those errors
in your client code.
This type of solution where you embed magic meaning into the constraint names
will become a maintenance headache and worse, is unobvious to other developers.
Thomas
"Keith G Hicks" <k...@comcast.net> wrote in message
news:OK40%232YTF...@tk2msftngp13.phx.gbl...
Keith G Hicks
code from an actual CHECK CONSTRAINT? That there is no such thing? Nobody
has actually answered that question or told me how to do that so I assume
it's not possible. If I am misunderstanding this then please explain. But
if I do understand what you all are saying is that I should be using
TRIGGERS instead of CHECK CONSTRAINTS and use RAISEERROR even for things
like "(len(ltrim(rtrim([CustName]))) >= 5)" (regardless of whether or not
you would put that sort of validatoin in the backend).
Thanks again,
Keith
Keith G Hicks
the same exact message when violating the constraint but there are error
numbers in it (these are *missing* from the error message that is shown in
Delphi). There are 2 numbers. One is 547 and the other is 3621.
Database error: '[Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE
statement conflicted with COLUMN CHECK constraint
'CK_tblCustomers_CustName'. The conflict occurred in database 'WidgetsInc',
table 'tblCustomers', column 'CustName'. (#547) [Microsoft][ODBC SQL Server
Driveer][SQL Server]The statement has been terminated. (#3621)
1. Where can I look to see what those numbers mean?
2. What if a column in a table has 2 constraints? The numbers don't change.
How do I figure out which one was violoated so I can show the user the
appropriate message?
3. Even if the table has 2 columsn each with its own constraint, the numbers
are the same. Same question as with #2.
4. How do I pull out the column and table name so I can post that to a log?
Is it simply a matter of parsing the error message text?
Thanks again,
Keith
Thomas Coleman
> 1. Where can I look to see what those numbers mean?
Look up "errors-SQL Server" in the BOL. Near the bottom of that section are
subsections for Errors 1 to 999, Errors 1000 to 1999 etc.
> 2. What if a column in a table has 2 constraints? The numbers don't change.
The system simply shows the error for the first constraint that failed.
> How do I figure out which one was violoated so I can show the user the
> appropriate message?
> 3. Even if the table has 2 columsn each with its own constraint, the numbers
> are the same. Same question as with #2.
Again, I wouldn't rely on database check constraints for this type of
information. Provide validation in your business and/or data tier that mimics
the types of checks in your check constraints. Remember that the purpose of the
check constraint is as a final check to prevent bad data from getting into the
database. It is not designed as a user-friendly, GUI building tool to give users
detailed information. A separate layer of your architecture should be designed
to handle this problem. Check and unique constraint errors were never designed
to be seen by average users. They were designed to be seen by developers and
DBAs.
> 4. How do I pull out the column and table name so I can post that to a log?
> Is it simply a matter of parsing the error message text?
Database check constraints are not designed for this purpose. Don't try to use
them for this. Instead, use your business or data tier to run these types of
checks before you ever call to the database. From there you can do all kinds of
fancy checks on every column in the dataset. Uniqueness checks can either be
handled by either running initial query checks from your data tier ahead of
time, throwing custom error messages or breaking up statements into a series of
statements where you can check for violations after each statement. In addition,
your business or data tier can log errors as they happen with detailed
information.
When designing a system, if I start to get into situations where I'm trying to
get a product or language to do something that does not appear to be designed to
handle, I generally step back and think about whether my entire approach should
be re-thought. It sounds like this is the case in your situation.
Thomas
Michael C#
> So I guess that the answer to my question is that you cannot get an error
> code from an actual CHECK CONSTRAINT?
You can get Standard SQL Error codes from CHECK CONSTRAINTS, i.e., Code 2627
"Violation of Unique Constraint", as I pointed out. You can set your *own*
custom Error Codes from within TRIGGERS, as I also pointed out, although it
is much more work than I would be prepared to do... How much work you're
prepared to do may be another story, however....
> That there is no such thing?
No there is not. To my knowledge, the *closest* you can get is with a
TRIGGER, as I pointed out.
> Nobody
> has actually answered that question or told me how to do that so I assume
> it's not possible.
I tried to give you examples of what *is* possible, as I thought they might
be more useful to you. AFAIK you cannot intercept and/or change CHECK
CONSTRAINT error codes. CHECK CONSTRAINTS just enforce data integrity, and
if the data violates a CHECK CONSTRAINT you'll receive a standard SQL error.
> If I am misunderstanding this then please explain. But
> if I do understand what you all are saying is that I should be using
> TRIGGERS instead of CHECK CONSTRAINTS and use RAISEERROR even for things
> like "(len(ltrim(rtrim([CustName]))) >= 5)" (regardless of whether or not
> you would put that sort of validatoin in the backend).
Actually that was just an example of what *is* possible, but not all things
that are possible are necessarily the right way to go. By way of example,
when using the TRIGGER example, consider this scenario:
1. Your application opens a connection to your SQL Server and generate a
command to INSERT a value,
2. You send the entire command over your network to the SQL Server,
3. SQL Server begins an 'implicit' transaction for your INSERT statement,
4. SQL Server spends time performing all your string functions (LEN, LTRIM,
RTRIM) and comparing the value in a TRIGGER
5. The SQL Server sees that I have passed "CustName = 'MIKE'", therefore it
does not pass the test,
6. SQL Server raises your custom error,
7. SQL Server rolls back the transaction,
8. SQL Server sends your custom error back over the network to your
application,
9. Your application receives the error code and generates an error message
for the user
That's a lot of overhead, which you could avoid on the business layer with a
simple pre-check:
1. Your application checks if CustName length is >= 5
2. If not, pop up an error message for the user
This can't be done with all errors (i.e., duplicate entries, etc.), but by
handling this one on the business logic layer you avoid a lot of overhead
including network traffic, unnecessary SQL Server connections, transaction
overhead, etc.
Keith G Hicks
some really nice features that make this type of validation pretty easy and
flexible. As you suggested, I think we will abandon using the backend for
this and move it to the client side.
In addition, I did find out how to get the error code numbers that sql sends
to Delphi. They're in the connection object, not the general error message
object.
Keith
"Thomas Coleman" <tho...@newsgroup.nospam> wrote in message
news:%23N8OKgb...@tk2msftngp13.phx.gbl...
Keith G Hicks
has been helpful. I can see that from your scenario below that handling many
validations on the client side makes a lot of sense. I hadn't really thought
about it that way. I still have to figure out how to get some of the error
information for things like duplicate values from the backend into
appropriate Delphi error messages. The code numbers get lost along the way
somehow.
Thanks again,
Ketih
"Michael C#" <x...@abcdef.com> wrote in message
news:6ZSce.2823$o32....@fe09.lga...
Keith G Hicks
a trigger):
RAISEEROR('text to pass', 16, 1)
rather than
RAISERROR 50001, 'text to pass'
Delphi actually does see the wording 'text to pass' without any extraneous
information. And I used your trigger idea below for my unique customer name
and the appropriate message is seen by the front end. All is well with that.
However, I have a question about your setup below. I looked at the
difference between "INSTEAD OF" triggers and regular ones. I'm not entirely
clear on all of it. But I noticed that if I change your code below so that
it's not an INSTEAD OF trigger, then the trigger never actually fires. The
unique index catches the violation first. Does that mean there is no point
in even having the unique index set? I'm not sure what exactly is happening
here.
Thanks,
Keith
Tibor Karaszi
INSTEAD OF triggers
constraints
AFTER triggers
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Keith G Hicks" <k...@comcast.net> wrote in message news:eTB2GXnT...@TK2MSFTNGP15.phx.gbl...
Keith G Hicks
implement the concept on a larger table (more than 2 columns) for updates, I
had problems. I was unable to update any columns at all. How would you use
an INSTEAD OF trigger for updates and inserts? Do you have to do an update
statement in the else section? I tried that and it didnt' work. I'll post
some code if necessary, but just wondering about this in general. I need to
do this for both updates and inserts. I ended up getting rid of the unique
index and just handling it in an AFTER trigger altogether. It works fine but
I posted another question about triggers vs. unique indexes so I'm wondering
about that as well.
Keith
"Michael C#" <x...@abcdef.com> wrote in message
news:2GOce.2755$o32....@fe09.lga...