Use MSSQL "EXECUTE" command with AdvancedQuery

Can the Advanced query be used to execute a query usin the "EXECUTE()" command? I have not been able to get it to work. I have even stripped it down to a very bare case. I have an Advanced Query where the code is only EXECUTE(@query).
When I provide @query with a test value of a very simple query, I cannot get it to work. I have tried the following combinations (and tested both with "Display inline" set to true and false for @query). Each one gives a different error...

- "SELECT last_name FROM core_person" 
- "SELECT last_name FROM {core_person}'

Can AdvancedQuery work with the EXECUTE statement? I desperately need to run some dynamic SQL, and this issue is causing me headaches.

NOTE: core_person is an external DB table that I have integrated through an extension.

Guy -

When you say "I cannot get it to work", what *precisely* do you mean by that?

What kind of error message or symptoms of a problem are you experiencing?

My experience has been to NOT use "EXECUTE", by the way, but to just pass in the query as a parameter, with "Display Inline" to False, and have that be the query all by itself. IE: have the Advanced SQL query just be: "@query".

J.Ja

Yes Guy,

Just be aware of the following:

1. By default, the queries run on the OutSystems Platform DB. Learn more about it, and how to change it;
2. You need to ensure the Runtime User (configured in the Configuration Tool) has permissions to execute this stored procedure;
3. The OutSystems Platform does not read the output parameters of stored procedures, so you might execute it, but will not be able to use its return values, so you should consider using a connector instead.

I think your problem is related with the first item.

Also, you won't be able to write physical table names of platform entities inside strings, so executes will not work for that.

You can use the advanced query directly as an execute like Justin suggested. Just keep in mind that tablenames inside strings won't work.

Regards,
João Rosado

Good suggestions, but this was the base part I needed to get to work for a Dynamic SQL dataset I needed to generate. In the end, I pulled it off, though it took alot more work than I feel it should have. The biggest killer was the fact that OS forces us to use a pre-defined Entity structure to recieve the results of an Advanced Query.

I believe the original problem I posted about was due to the fact that I had built the Output Entity Structure using some record type attributes as well as what joao F mentioned. It appears that since the query was being executed from a string, rather than an OS query builder, it did not recognize the record type datatypes that were being returned. I reduced them all to Integer and Text types. That, along with fully qualifying my table call (i.e. externalDB.schema.core_person) and that appeared to fix the problem I was having.

As for the "Why".... I needed to use a complex query with a dynmaic pivot table to generate the dataset for a table. To do this, I used several logic pieces to dynamically build the sql query as a string, then finally fed it to the simple "EXECUTE(@query)" AdvancedQuery. It got tricky to limit it to a finite number of columns, so I ended up picking a number (20). I created an Entity with 3 or 4 fixed attributes, then added 20 generic attributes after it. I built the query to always pull back this many columns (whether the pivot date pulled in that many or not). The "extra" columns in the dataset were set to "unused1, unused2, ...". I then used the string "unused" to hide any columns of the resulting table through an exetended "style" property on the cells. Like I said, it took A LOT more work than I feel it should have, but in the end, I pulled off a table with "dynamic" columns thant only show if their is data for them.

Guy,
Thanks for sharing. I'm glad you were able to overcome this issue.