Active Query Builder support area

Stack overflow with circular virtual object expression

Avatar
Marc
  • updated
  • Completed

I want to use the virtual object feature to substitute a table name with a query but need help with the syntax of the expression for the MetadataObject.

Say I have a table named TABLE1, and want to have it replaced with:

(SELECT * FROM TABLE1 WHERE COLUMN1 = 'Value')

When it replaces the original TABLE1 with my expression, it seems to then replace the TABLE1 in my expression with my expression again, over and over until it fails.

If I define my table as "TABLE1_AQB" then it works, but I'd rather not have users seeing that _AQB in the ObjectTreeView

Any ideas?

Replies 11
Avatar
0
Andrey Zavyalov, PM

Hello, Marc.

Thank you for your question.

The current version of Active Query Builder implies usage of other virtual objects in SQL expressions of virtual objects. We can let you control this implication in the next version with a new property.

The stack overflow bug will be fixed in the next build.

Avatar
0
Andrey Zavyalov, PM

By the way, what edition of the component you use?

Avatar
0
Marc

If I set the MetadataObject expression as described above with the table named TABLE1_AQB in my metadata xml file, there aren't any fields for that table when I drag it from the tree onto the canvas (only one row with *) . Is this because I defined the Expression with SELECT * ? Is there another property I can set so all the fields appear?

Avatar
0
Marc

Will this feature be included in 2.13?

Avatar
0
Andrey Zavyalov, PM

The version 2.13 that has just been released fixes the stack overflow problem, but for now it just fails to load such objects as it treats "Table1" as a virtual object and aborts the recursion. We will release another version that will treat any object used in the SQL expression of virtual object as real object name. It will be released till th end of this week.

Avatar
0
Marc

It seems as if the virtual objects don't work at all for me now with 2.13.1

Avatar
0
Sergey Kraikov

Hi Marc,

sorry for inconveniences, currently we've only fixed stack overflow in recursive virtual objects.
In the current development version I've implemented new property to disable recursive virtual objects,
this property solves your original question - all names referenced in virtual object expressions treated as
real database objects.
The original virtual objects feature seems work fine. I've just tested it using this code snippet:

using (var syntax = new MSSQLSyntaxProvider())
using (var queryBuilder = new QueryBuilder() {SyntaxProvider = syntax})
{
    var metadataContainer = queryBuilder.MetadataContainer;

    var northwind = metadataContainer.AddDatabase("Northwind");
    northwind.Default = true;

    var dbo = northwind.AddSchema("dbo");
    dbo.Default = true;

    // create virtual table
    var customersVirtual = dbo.AddObject("CustomersV", MetadataType.Table);
    customersVirtual.Expression = "(select * from Customers c where c.CustomerId=10) q";

    // use virtual table
    queryBuilder.SQL = "select * from CustomersV";

    // show expanded SQL
    MessageBox.Show(queryBuilder.SQL);
}
Avatar
0
Sergey Kraikov

Hi Marc,

I forget to say - the version with the new AllowRecursiveVirtualObjects property will be released till the end of this week

Avatar
0
Andrey Zavyalov, PM

Hello, Marc.

The new version introduces the QueryBuilder.MetadataLoadingOptions.AllowRecursiveVirtualObjects. It is set to false by default. This will let you use the same name for a virtual object and in it's SQL expression.

Please let us know if this change has solved your problem or not.

Avatar
0
Andrey Zavyalov, PM

Hello, Marc.

Could you please let us know if the new AllowRecursiveVirtualObjects property was helpful to solve your problem?