trapping syntax errors - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

trapping syntax errors

Author

Topic

eddie
Starting Member

45 Posts

Posted - 2002-07-12 : 14:39:57

We are adding a feature that allows a user to add a 'filter'. This filter will basically just be a where clause. I need to take this filter check the syntax and return a nice 'incorrect syntax' error to the user istead of the ugly sql one. How do I do this? This doesn't work as the error fires before I fall into the @@error check.

begin transaction
select * from wrongtablename
if @@error=1
begin
raiserror 30001 'Icorrect Syntax.'
rollback transaction
return
end
commit transaction

Thanks,
Eddie

solart
Posting Yak Master

148 Posts

Posted - 2002-07-12 : 14:48:15

Refer to BOL for SET PARSEONLY. I think this can be solution to your problem.

solart

nizmaylo
Constraint Violating Yak Guru

258 Posts

Posted - 2002-07-12 : 15:01:30

I don't think SET PARSEONLY will help. If you could provide more details, I could probably tell you exactly how to get it done. If not, check MS Excel how they add so called filters. Make sure the user is given all the proper promts so he(she) can only constract a valid SQL Statement.

helena

solart
Posting Yak Master

148 Posts

Posted - 2002-07-12 : 15:24:15

Nizmayo is correct. SET PARSEONLY only tells you if the syntax is correct, not if the objects are known, etc.

solart

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-07-12 : 15:40:27

Maybe you could get rid of errors entirly by only letting users select from tables that exist and fields that exist?

Do some searching on the boards and on the internet for INFORMATION_SCHEMA

Michael

<Yoda>Use the Search page you must. Find the answer you will.

solart
Posting Yak Master

148 Posts

Posted - 2002-07-12 : 16:05:59

Maybe sending the following to sqlwish@microsoft.com would be helpful in the future:

A "connection option" which would suppress any SQL query error messages, but still set @@Error.

The default for this connection option would be to NOT suppress any SQL query error messages. Meaning continue to do what happens today.

i.e. SET QUERYMSGS OFF | ON

Then I could do the following:

begin transaction
SET QUERYMSGS OFF
select * from wrongtablename
if @@error=1
begin
raiserror 50001 'Invalid Query.'
rollback transaction
return
end
commit transaction

solart

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-12 : 16:33:55

Um, then what good would it be? It would suppress your RAISERROR message too.

The proper way to do this is to validate/scrub the user's input for potential syntax problems, and have the application report any, BEFORE it even attempts to parse and execute the SQL statements. You can't capture syntax errors any other way. And in your example, you can't rollback the transaction because:

A) You only have a SELECT statement;
B) A syntax error is fatal and terminates the batch;
C) And because of that, no commands were actually executed, therefore there is nothing to roll back

If you're going to allow users to create their own SQL statements, you're chasing your own tail trying to prevent syntax errors. Either set up some kind of template that they can use but not radically alter, or just let them throw syntax errors all day until they get it right.

solart
Posting Yak Master

148 Posts

Posted - 2002-07-12 : 16:53:19

I am way off base on this topic, but GLAD that sharper minds prevail.

solart

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources