I want to create a dynamic query. Can I use the sysQueryForm?

(0) Share

Report

Posted on by Community Member

Can anyone guide me to use the sysQueryForm. I want to create a dynamic query.

I have read that this can be used to create and manipulate and save queries.

*This post is locked for comments

I have the same question (0)

All responses (11)

Answers (1)

Suggested answer

Brandon Wiese 17,788 on at

Like (0)

Report

It depends what you mean by dynamic. If you're using the Query, QueryBuildDataSource, QueryBuildRange, etc., objects in X++ to construct a Query and ultimately a QueryRun object in code, then SysQueryForm won't be much use to you. You can still cause that form to appear and allow the user to further alter your constructed query, for which SysQueryForm will be used.

Many queries in AX that are available on dialog forms and reports can be altered by the user using the Select button. However, it is not a generic tool for creating any query from scratch nor does it guarantee that user changes will not break the query and keep it from doing its intended job. It is not a tool like what you would find in SQL Server Management Studio or a report writer, for example.

Hope that answers your question.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Brandon, thanks for the reply.

I want to make a dynamic query. The user can give me any complex query can I make it by using the sysQuery ??

As I came across a link on this community community.dynamics.com/.../215472

And from here I want to know that how to do this. I am unable to get this to work.

Was this reply helpful? Yes No
Martin Dráb 237,880 Most Valuable Professional on at

Like (1)

Report

The linked thread is about using DEV_QueryBrowser for previewing results of a query. Is it what you mean by "to create a dynamic query" and "make it by using the sysQuery"?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you Martin for the reply.

Yes I need the results as well !! I need to show the results to the grid or export it to excel.

But first I need to know how to make a dynamic query. I want to make my own Select query .... And I can make any select query...on run time... and then when I click ok it should return the results in excel or on the From Grid control...

Please help!

Thanks

Was this reply helpful? Yes No
Martin Dráb 237,880 Most Valuable Professional on at

Like (1)

Report

What you mean by "dynamic query" is crucial, yet it's not clear from your description. Please trey to explain your business requirement from beginning?

You can use multiple sentences, concrete examples, use cases, pictures or anything else that explains it the best.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Martin basically I want the front end user to access the DB.

As you know that DB is not accessible from front end.

I want that the user can construct his own SQL query from the front end and the results returned by it are then exported in a excel file.

For Ex:

Select Table1.Field2 , Table2.Field1

from Table1, Table2

join someTable

where criteria

orderby ASC somefield

OR

Select * from Table3

I hope you get the idea, The user can create/construct any SQL query he wants and we should return the results in an excel file.

I read somewhere that this framework of sysOperation and SysQuery can be used to make query and apply joins etc.

Now please tell me that is this framework any help to me or not? Can I use this to make a query on the go and then get the results?

So I have to think of some other logic from scratch? And make up my own form.

Was this reply helpful? Yes No
Martin Dráb 237,880 Most Valuable Professional on at

Like (1)

Report
Accessing DB directly is no way to go, for many reasons.

You surely could design your own SQL-like language, write a lexer and parser and then create a Query class instance from it, but I don't think anybody would seriously consider it. You could also allow users to write X++ code and run it, if your users were X++ developers with knowledge of AX data model and you were willing to open an enormous security hole in your system. I assume it's not the case.

Let's start from a different end - why would you do it? Have you consider existing features, such as:

Opening a form, modifying query using Advanced filter/sort and exporting to Excel.

Data Import Export Framework (supports Excel)

Excel Add-in

What exact business case can't you achieve with them?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

There is no security breach. Just tell me a few things,

1)

Is there a way to have a string in which is my actual query

like "Select * from CustTable where CustTable .AccountNum=="Acc-001" " (hope this is clear that i am receiving my query in a string).

Now is there any way that I can execute this query in X++. Where i can just pass this string that is actually my query to some object/variable/function which executes it and returns me a buffer or a query object which I can iterate and get all the records returned as result.

2)

Opening a form, modifying query using Advanced filter/sort and exporting to Excel.

This is something I also tried, but for this dont we need to always have an existing AOT query to work with and the query object will always have a root data-source set previously , which i dont want to be set beforehand.

So any solutions?

Was this reply helpful? Yes No
Martin Dráb 237,880 Most Valuable Professional on at

Like (0)

Report

Do you claim that executing arbitrary X++ code that you get from users isn't a security risk? Well, I can't agree with that. What if I write delete_from custTable instead of select * from custTable, to use the most obvious example? Users would likely learn very quickly they can bypass security, validations, approvals etc. with your "feature". Even if everybody had the best intentions, don't forget that most users aren't experienced X++ developers (not mentioning the fact that developers do many mistakes too), users wouldn't have a proper code editor with Intellisense and other features reducing errors.

Your couldn't upgrade your solution to AX 7, so you would have to throw it away sooner or later - fortunately.

Sorry, this is so bad idea that I can't help you to destroy your system. I'm willing to help you design a better solution, but I would first need to understand the business case. "I want users to execute aby code" isn't a business requirement; it's a description of how you decided to solve some business problem.

For example, you could allow users to select a root table, create a Query instance, open the dialog and let users to add related tables, ranges, sorting etc., without having to teach users how to write X++ code and without huge risks of your original design.

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report

This is what solves the problem:

void Sample_1(void)

{

Connection Con = new Connection();

Statement Stmt = Con.createStatement();

ResultSet R =Stmt.executeQuery(‘SELECT VALUE FROM SQLSYSTEMVARIABLES’);

while ( R.next() )

{

print R.getString(1);

}

}

Was this reply helpful? Yes No