HomeActive Query Builderneed to convert a query from one schema to another - is active query builder the best tool to use

need to convert a query from one schema to another - is active query builder the best tool to use


I am upgrading a table full of queries from one database to a whole new schema. I realize that many of these queries will need to be upgraded by hand

but i would like to know if anyone has ever done anything like this using active query builder?

how easy is it to find table names that are sued and do a repalce to the new schema's table names

how about all the fields used?

some of the queries used are fairly complicated and so that is why we want to try using active query builder first. when i say that they are complicated i mean there are many unions and sub queries as well as calculated fields.

any thoughts on this? are there any samples of someone doing this?
Wendy Mathis | email
May 7 2010, 08:03 PM
Dear Wendy,

You may analyze your queries with Active Query Builder and change table names. An example of the query analysis can be found in the Non-visual demo.

Unfortunately, it is not possible to determine all fields in the query, especially if they used without table alias prefix.
Sergey Smagin | email
3 days, 11 hours since original post
OK, so I see how to analyze the query and have been able to get alist of tables in the query using the crieteria list.

I then looked at the ExpressionDataSource object as I assume that is where a table name can be found and changed however every property where I see the table name listed seems to be read only so I can't change the table name to my new schema table name.

any ideas?
Wendy Mathis | email
5 days, 18 hours since original post
Dear Wendy,

it seems the only way you can involve Active Query Builder to perform your conversion is following:
1) load the query into AQB
2) collect used objects using AQB's query stats
3) perform search-and-replace old names to new names in the source SQL
Sergey Smagin | email
6 days, 5 hours since original post
Is there a way to create a criteria item with the new table name and then copy over features from the old criteria item and rebuild the query that way?

or maybe even create brand new instance of the Query builder and try and duplicate the query with the same objects but using different tables and fields?
Wendy Mathis | email
6 days, 16 hours since original post
Dear Wendy,

Copying a query is not a good idea because there are some query properties that aren't mapped to high-level query representation objects.
Copying criteria items is possible, but please note that you still have to deal with string expressions in the Expression and Criteria columns and perform search and replace operations over them. Actually, you'd better not copy criteria items, but deal with existing criteria item strings: CriteriaItem.ExpressionString, CriteriaItem.ConditionStrings[].
Sergey Smagin | email
2 weeks, 3 days since original post
This topic is closed