Обсуждение: Application validation of data on insert
Hello!
I'm only moderately familiar with postgres and the DBI related modules that I'm using to access it so forgive me if this is a simple question. I've been looking for several hours for information on this and have not found a satisfying answer.
I'm writing a simple CGI application to do simple create, update, delete operations on a few tables and although doing these basic operations is trivial I am having a great deal of trouble figuring out the best way to validate data being inserted and handle related errors.
My question in a nutshell is when performing an INSERT, how do I determine exactly which columns are responsible for the error when one occurs(so that within the application I can say "hey user check how you typed field x, field y). The SQLSTATUS error codes are very non-specific and I cannot find another method for checking what failed without parsing text output(unreasonable for many reasons). Perhaps there is a way to define an INSERT trigger that could place/return the names of the problem columns somewhere? Doing this in a way that does not tie one only to PGSQL(although it's my favorite) is even more of a problem.
It seems to me that all information about acceptable values, constraints and types for columns is already present within the table definitions so duplicating those checks and definitions within the application and user view is folly.
Thanks for your enlightenment!
I'm only moderately familiar with postgres and the DBI related modules that I'm using to access it so forgive me if this is a simple question. I've been looking for several hours for information on this and have not found a satisfying answer.
I'm writing a simple CGI application to do simple create, update, delete operations on a few tables and although doing these basic operations is trivial I am having a great deal of trouble figuring out the best way to validate data being inserted and handle related errors.
My question in a nutshell is when performing an INSERT, how do I determine exactly which columns are responsible for the error when one occurs(so that within the application I can say "hey user check how you typed field x, field y). The SQLSTATUS error codes are very non-specific and I cannot find another method for checking what failed without parsing text output(unreasonable for many reasons). Perhaps there is a way to define an INSERT trigger that could place/return the names of the problem columns somewhere? Doing this in a way that does not tie one only to PGSQL(although it's my favorite) is even more of a problem.
It seems to me that all information about acceptable values, constraints and types for columns is already present within the table definitions so duplicating those checks and definitions within the application and user view is folly.
Thanks for your enlightenment!
John Cohorn wrote: > It seems to me that all information about acceptable values, constraints > and > types for columns is already present within the table definitions so > duplicating those checks and definitions within the application and user > view is folly. Well, there I'd disagree with you. With a web-based application you might typically have three levels of validation: 1. Javascript on the client side 2. Server-side validation 3. Database validation The view of data at the application level might well be different from that at the database level (if you insert data via a view or function for example). Providing test/erros that mean something to the application (and its users) is something that your application needs to handle. Hopefully your application framework provides some help with these tests. But, you still want to extract the column-names etc. and I'm afraid that will require parsing the error messages at present. There has been discussion on returning more structured information in the error packets, but that will mean a change in the communication protocol, so won't ever apply to older versions of the database. HTH -- Richard Huxton Archonet Ltd