What is the best way to track 'database' modifications?
-
Suppose, I have an application which talks to a database in SQL/Oracle, and presents some set of data to the user. If, for any reason, someone modifies the table structure in the original database, and the application tries to work on the database, there will be an error or incorrect data being displayed. Is there any way to prompt the user that there was a modification happened on this database ,.. with these tables modified,.. and so on? Or can we write a windows service to do this kind of tracking a database?
-
Suppose, I have an application which talks to a database in SQL/Oracle, and presents some set of data to the user. If, for any reason, someone modifies the table structure in the original database, and the application tries to work on the database, there will be an error or incorrect data being displayed. Is there any way to prompt the user that there was a modification happened on this database ,.. with these tables modified,.. and so on? Or can we write a windows service to do this kind of tracking a database?
How about this ? Create a "version" table where you could store a database schema version number and have your application check that value when it starts up. However, you would have to have an agreement between the folks that modify your database schema and the people that write your application that any changes to the database schema would cause the version number to increment. Another way of doing this would be to have a procedure in your application startup that would verify each table for it's structure; if the structure doesn't match, then stop the application. Something like: Open Table A, Iterate through the fields, check their name, datatype, length, Allow Null or Not ... What you describe is actually an indication of a bigger problem. No change control procedures. If there is a change to your database in a production enviornment, there has to be a checklist, including signoff that all parties involved are aware of the change. You can't have people making ad-hoc changes to a production system without proper procedures; it will always end up biting you in the end. Good Luck. :thumbsup:
-
Suppose, I have an application which talks to a database in SQL/Oracle, and presents some set of data to the user. If, for any reason, someone modifies the table structure in the original database, and the application tries to work on the database, there will be an error or incorrect data being displayed. Is there any way to prompt the user that there was a modification happened on this database ,.. with these tables modified,.. and so on? Or can we write a windows service to do this kind of tracking a database?
You could write a startup procedure in the application the user runs that verifies the known database structure against one hardcoded in the application. This can be done using the ODBC / JDBC, but you'll need to contact the application developer. For as far as I know most programming languages offer the capability to request the existing table structure of a database. Another option is that all applications access the data through views. And after every schema change an automated check on database level is performed to see if all the views still work. If not you alter the views slightly, making sure they return the expected data. This way you will only have to change the applications when they need additional data from your database scheme. However the true question becomes rather different, why are you expecting the schema of the database to change without an update to the programming of the various applications using it. As usually a change in the database is triggered by a change in one of the programs using the data in it?
-
Suppose, I have an application which talks to a database in SQL/Oracle, and presents some set of data to the user. If, for any reason, someone modifies the table structure in the original database, and the application tries to work on the database, there will be an error or incorrect data being displayed. Is there any way to prompt the user that there was a modification happened on this database ,.. with these tables modified,.. and so on? Or can we write a windows service to do this kind of tracking a database?
As has been pointed out, you have a fundamental problem with change control! Both SQL Server and Oracle have system tables/views which will list all the objects (table and column names) in the database. I would use these and create a checksum or something based on the existing structure, create a proc that checks this and use it in your apps launch!
Never underestimate the power of human stupidity RAH
-
As has been pointed out, you have a fundamental problem with change control! Both SQL Server and Oracle have system tables/views which will list all the objects (table and column names) in the database. I would use these and create a checksum or something based on the existing structure, create a proc that checks this and use it in your apps launch!
Never underestimate the power of human stupidity RAH
won't you get a lot of service calls any time you add a table or a field, making it all fail suddenly? :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.
-
Suppose, I have an application which talks to a database in SQL/Oracle, and presents some set of data to the user. If, for any reason, someone modifies the table structure in the original database, and the application tries to work on the database, there will be an error or incorrect data being displayed. Is there any way to prompt the user that there was a modification happened on this database ,.. with these tables modified,.. and so on? Or can we write a windows service to do this kind of tracking a database?
If you are using Sql Server 2005 or above, you should look at DDL Triggers for capturing changes to the database structure. http://www.simple-talk.com/sql/t-sql-programming/sql-server-2005-ddl-trigger-workbench/[^]
-
won't you get a lot of service calls any time you add a table or a field, making it all fail suddenly? :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.
As there is a fundamental problem with change management I think that would be the least of his problems. It sounds like he does not own the database and he is not in the change management loop. We recently had something like this where the IT team changed a view that was feeding us and did not inform the clients of the view. A rocket went around the IT team and we are now in the CM loop.
Never underestimate the power of human stupidity RAH