What is the best way to validate data in MySQ?

[smitty654]

I am storing data in a MySQL Server and want to be able to constrain and validate the data. What is the easiest method to accomplish this in Delphi 2007? Thanks for your time.
P.S. Preferrably if there is a way to do it with MyDAC / MDAC components, feel free to explain.

 

[Glenn9999]

What do you mean by "constrain and validate"? That might be the first question.

If it's what I think it means, though, the only real good way, beyond adding constraints to the table definition (assuming MySQL accomplishes that trick) is to:

1) Write validation checks into all the programs that insert or update data.
2) Write a program that applies those validation checks to the data, deletes all the "bad" records from your "good" table, and puts them somewhere else (another table or a file) for further analysis.

----------
Measurement is not management.

 

[smitty654]

I'm sorry. I implied only to validate the information that goes on a form. This form has a 'Submit' button that will insert a lot of infomation in two tables that I have created. I've already constrained the database fields on the database end (such as shortening the length on the fields to not use more length of characters then it needs). Now, I need to make sure that what that person types on the form gets validated in code or whatever method to make sure, for example that they don't have NULL values where there shouldn't be and no duplicate data. I do like how the TSQLTable component for dbGo has custom constraint errors built in the component. A good reference I found about this is '

http://www.onlinedelphitraining.com/newsletters/dataconstraints.htm'.

Also, another feature I found that I like is you can double click the component to add the fields. I don't believe you can do this with the MyDAC components. I'm still experimenting with the MyDAC components and trying to figure them out.
Thanks again.

 

[Glenn9999]

The best way to do that (that #1 task I posted above) is to grind it out and do all the checks on the data before you touch the database.

I would suggest starting by writing down each field of your data entry program on a sheet of paper and then trying to describe each of the fields (numeric, formatted a specific way, is it to be validated against another table, key field, length, if date is it valid, does data make sense compared to other data -is a ending date before a starting date?, etc). A proper selection of control to force the data into the proper format can be beneficial as well.

Then work from your paper when you have all the fields adequately described and write your checking statements from that. I/O is more costly than processing so do any of the processing you can in checking before you do I/O.

For testing your checking code, make it into a unit and then call it from a program that reads in your data from a data file, runs your checks, and then documents errors (this is truly the easiest way for testing). When you make the data file, put in one of each kind of error you described on your paper along with a few correct records and document that.

Truthfully, data validation is very simple when it comes to code construction. The complexity comes in handling the design and testing side of it.

----------
Measurement is not management.