I would like to understand if it's possible to mediate queries in MS SQL so that you can update the query before it finally executes on a table. Here is the scenario. I will be supporting some customers whose tables will be translated. Originally the data in these tables are in English but the tables will be replaced by their Spanish, French, etc. versions. I need to be able to take incoming queries from these applications--very similar source code written in Java and using JDBC--, translate the requested parameters into the new language, and retrieve the same results from the new tables. Can this be done by somehow modifying JDBC or perhaps even MS SQL itself?
Here is the example:
1) A number of the clients have MS SQL 2005 and 2008, so we'll start here.
2) Each of the customers has a single table called shirts which has various columns, e.g., color, product_id, size, etc. about their inventory of shirts.
3) They all have, roughly, the same Java application which, through JDBC, connects to their respective shirts table and makes queries such as:
SELECT * from shirts WHERE color = 'red'
4) Some of their suppliers will be giving them a new shirts table in another language (e.g., Spanish). The new table will have an identical scheme and columns but some of the data will be translated into their foreign-language equivalents, using basic translations. The new table will completely replace the original English table (i.e., the original records will be deleted).
5) I need to be able to catch the queries from the application somewhere in MS SQL and rewrite them to reflect the new language. For instance, in the example above, considering a Spanish translation, the query would become:
SELECT * from shirts WHERE color = 'rojo'
6) The same exact results (e.g. rows, etc) should be returned to the user. (Of course the translated columns will now show the translated data.)
Due to a variety of business and technical factors, I cannot change the original application in any way. For example, I cannot turn on multi-language support in the customers' SQL environments, I can't change the schema of the original table, etc. The only thing I know is that their tables will be replaced with translated ones, and the only thing I can change is some component of their MS SQL. Can MS SQL, JDBC, etc. be changed to accomplish such a task, perhaps by rewriting a method, installing a new DLL, etc? I am setting up a small test environment with MS SQL 2008 and would love to hear any thoughts.