Generate SQL From a Query
Sebastian Widz
I have the following question:
How to generate in the code based on the Query Object an SQL statement that
is used by the applicatoin to retrieve data from the database.
I am askeing about the exact statement that will be send via ODBC
I have already tried the following but it is not exactlly what I need:
1. For a given Query object
str getSQL(Query _query)
{
return _query.dataSourceNo(1).toString();
}
But the following code is some semi SQL, not the one that is produced by
Axapta
2. I have tried to use the SQLBuilderSelectExpression class and write my
own Query object parser that generates the SQL, so far I have managed to
produce SQL with inner joins and simple where clauses but this way of doing
it would require manny afforts as the some queries are very complax
Beside I can not get off the impression that it is already done in the
kernel so, it not smart to do it again...
3. I was wondering if there is a way of using the SysTrace Class, the code
should
do somenthing like this:
init and turn on the SysTrace for SQLStatement
turn on forceliterals for a query
execute the query
get the SQL statement from the trace output
turn off the systrace
return sqlStatement
Does SysTrace class could be used that way?
--
Best Regards,
Sebastian Widz
Michal Kupczyk
news:70B7F8F2-58D7-4D5A...@microsoft.com...
> Hi,
>
> I have the following question:
> How to generate in the code based on the Query Object an SQL statement
> that
> is used by the applicatoin to retrieve data from the database.
I have tried to use the SQLBuilderSelectExpression class and write my
> own Query object parser that generates the SQL, so far I have managed to
> produce SQL with inner joins and simple where clauses but this way of
> doing
> it would require manny afforts as the some queries are very complax
> Beside I can not get off the impression that it is already done in the
> kernel so, it not smart to do it again...
I havent found the way of getting the Final SQL out of AX's Query Object
without major hassle.
> 3. I was wondering if there is a way of using the SysTrace Class, the code
Yes, the sysTrace seems to be the only way to get the final SQL.
In AX3 there was SystemMonitor::SQLDump() which returned all queries, and
you might find a way to associate which is your, but AX4 returns always
empty container.
Regards
--
Michal Kupczyk
http://staticsax.blogspot.com/
Sebastian Widz
with the following code you can catch SQL send to the database engine (works
in AX v3, I did not checked this with DAX4 or DAX2009):
static void Query2SQL(Args _args)
{
//create query, here we use LedgerTable
Query query = new Query(QueryStr(LedgerTable));
QueryRun queryRun = new QueryRun(query);
UserInfo userInfo;
UserInfo userInfoOrig;
#LOCALMACRO.FLAG_SQLTrace (1 << 8) #ENDMACRO
#LOCALMACRO.FLAG_TraceInfoStateInfo (1 << 1) #ENDMACRO
str sql;
int line,pos1,pos2;
;
query.literals(true);
//disable the record cache
for(i=1; i<=query.dataSourceCount(); i++)
{
dictTable = new SysDictTable(query.dataSourceNo(i).table());
if(dictTable)
{
common = dictTable.makeRecord();
common.disableCache(true);
queryRun.setCursor(common);
}
}
select firstonly forupdate userInfo
where userInfo.Id == curUserId();
userInfoOrig.data(userInfo);
userInfo.traceInfo = userInfo.traceInfo | #FLAG_TraceInfoStateInfo;
userInfo.debugInfo = userInfo.debugInfo | #FLAG_SQLTrace;
infolog.getUserSetup().setUserSetup(userInfo);
queryRun.next();
infolog.getUserSetup().setUserSetup(userInfoOrig);
line = infolog.line();
sql = infolog.text(line);
pos1 = strscan(sql, "SELECT A.", 1, strlen(sql));
sql = substr(sql, pos1, strlen(sql)-pos1);
pos2 = strscan(sql, " [", 1, strlen(sql));
sql = substr(sql, 1, pos2-1);
info(sql);
}
Sebastian Widz
Michal Kupczyk
> with the following code you can catch SQL send to the database engine
> (works
> in AX v3, I did not checked this with DAX4 or DAX2009):
>
A) infolog.getUserSetup().setUserSetup(userInfo);
queryRun.next();
B) infolog.getUserSetup().setUserSetup(userInfoOrig);
You may find more than one statements exexuted between A) and B).
1) because of link types in query
2) other code can interrupt you method using the timeOut call, and execute
its own DB statements, which will get caught here also.
Amar Nalla [MSFT]
Have you tried using the SQL Profiler? You will get the exact statement that
is sent to the database. Is there any reason you are looking at X++ based
options.
Thanks you,
Amar Nalla [MSFT]
"Michal Kupczyk" <michal@@kupczyki..eclipse..co..uk> wrote in message
news:%2308meBb...@TK2MSFTNGP02.phx.gbl...