Query Macros

Delford Chaffin asked on February 22, 2017 16:35

In a SQL query for a report, I am using:

WHERE myField LIKE '%{%CurrentUser.UserName#%}%'

But I'm trying to use that same syntax (and I've tried a few others) in a Document Type Query and it is not working. What is going on here? How can I inject the current username into my query?

Thanks!

Recent Answers


Delford Chaffin answered on February 22, 2017 16:43

Ugh ... It seems I can put that kind of macro in a Where Condition field on a web part and it replaces the ##WHERE##, but it seems like I should be able to just put it in the query as well.

0 votesVote for this answer Mark as a Correct answer

Zachary Perry answered on February 22, 2017 16:57

In Web Part queries you have to use ##WHERE## and in the where condition field on the web part you can put your macro there. Not sure what version of Kentico you are on, but in 9 on Hotfix 44 (or around there) there was a change that will cause using macros in queries to not work in some scenarios. Typically if you tried to use the ##WHERE## somewhere other then after the actual WHERE in the query.

0 votesVote for this answer Mark as a Correct answer

Peter Mogilnitski answered on February 22, 2017 20:48 (last edited on February 22, 2017 21:14)

Without custom code you can do it ONLY using sql code expressions: ##ORDERBY##, ##COLUMNS##, ##TOPN##, ##WHERE##.
Take a look Loading data using custom queries. The standard approach is: you put your macros in web parts properties and they get resolved in the context of the current document.

If you really have such a need - it is possible, but it will require custom coding. You have to modify back-end code of your web part and run macroresolver on the text of your sql query, for instance:

string SQlQueryTextWithMacros= "select * from CMS_User where userName = '{%CurrentUser.UserName@%}'"
SQlQueryTextWithMacros = MacroContext.CurrentResolver.ResolveMacros(SQlQueryTextWithMacros) 
DataSet ds = ConnectionHelper.ExecuteQuery(SQlQueryTextWithMacros, null, QueryTypeEnum.SQLQuery);
0 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.