Platform
Learn
Community
Support
Partner Center
Sign up
or
Log in
logout
Account Settings
Profile
messages
Messages
logout
Logout
Platform
Home
Downloads
IPP
Licensing
Project Sizing
Learn
Training
Documentation
Evaluation Guide
What's New
Community
Home
Forums
Forge
Ideas
Members
Support
Partner Guide
Resources Library
Opportunities
Account Management
Sign up
or
Log in
Home
Forums
Forge
Ideas
Members
Dear ,
What would improve your OutSystems Community experience? Let us know by taking this 2-minute survey.
Pick up the survey
Community
›
Forums
›
Technology & Integration
Use MSSQL "EXECUTE" command with AdvancedQuery
Community
›
Forums
›
Technology & Integration
Use MSSQL "EXECUTE" command with AdvancedQuery
New Post
New Post
Lake Pointe Church LPC
Posted on 2013-09-05
Lake Pointe Church LPC
Rank: #5053
Posted on 2013-09-05
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.
Justin James
mvp_badge
mvp_label
Posted on 2013-09-06
Justin James
mvp_badge
mvp_label
Rank: #4
Posted on 2013-09-06
Solution
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
Solution
João Fernandes
Posted on 2013-09-09
João Fernandes
Rank: #242
Posted on 2013-09-09
Solution
Yes Guy,
Just be aware of the following:
By default, the queries run on the OutSystems Platform DB
. Learn
more about it, and how to change it
;
You need to ensure the Runtime User (configured in the Configuration Tool) has permissions to execute this stored procedure;
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.
Solution
João Rosado
Posted on 2013-09-09
João Rosado
Rank: #13
Posted on 2013-09-09
Solution
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
Solution
Lake Pointe Church LPC
Posted on 2013-09-11
Lake Pointe Church LPC
Rank: #5053
Posted on 2013-09-11
Solution
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.
Solution
João Fernandes
Posted on 2013-09-12
João Fernandes
Rank: #242
Posted on 2013-09-12
Solution
Guy,
Thanks for sharing. I'm glad you were able to overcome this issue.
Solution
Quick Reply
attachment
Choose File
No file chosen
Submit
Login to reply
New Post
Available Forums
Technology & Integration
News and Announcements
Forge Discussions
Meta
Community Quick Guides
Usability
Installation Troubleshooting
Personal Environment Troubleshooting
Forum Notifications
Email Digest Settings
Loading...