Dear IvyTeam

How can I print sql query to log file when there are some get ivy task by query?

I followed this link https://answers.axonivy.com/questions/1585/hibernate-show_sql but it seem for project that using hibernate?

If I print query by method TaskQuery query.toString() I got some kind of query in log but it's not friendly with normal sql like below

SELECT *  
  FROM IWA_Task  
  LEFT JOIN IWA_TaskAdditionalProperty TaskAddPrp_isDeleted ON Column(IWA_TaskQuery.TaskId) = Column(IWA_TaskAdditionalProperty.TaskId)
  LEFT JOIN IWA_AdditionalProperty AddPrpTask_isDeleted ON Column(TaskAddPrp_isDeleted.AdditionalPropertyId) = Column(IWA_AdditionalProperty.AdditionalPropertyId)
  INNER JOIN IWA_CaseQuery ON (Column(IWA_CaseQuery.CaseId) = Column(IWA_TaskQuery.CaseId) || Column(IWA_CaseQuery.CaseId) = Column(IWA_TaskQuery.BusinessCaseId))
  LEFT JOIN IWA_CaseAdditionalProperty CaseAddPrp_taskType ON Column(IWA_Case.CaseId) = Column(IWA_CaseAdditionalProperty.CaseId)
  LEFT JOIN IWA_AdditionalProperty AddPrpCase_taskType ON Column(CaseAddPrp_taskType.AdditionalPropertyId) = Column(IWA_AdditionalProperty.AdditionalPropertyId)
  WHERE (Column(IWA_TaskQuery.CurrentActivatorName) NOT LIKE #SYSTEM && Column(IWA_TaskQuery.State) <> 3 && (((Column(AddPrpTask_isDeleted.Name) = isDeleted || Column(AddPrpTask_isDeleted.Name) IS NULL) && Column(AddPrpTask_isDeleted.Value) IS NULL) || ((Column(AddPrpTask_isDeleted.Name) = isDeleted || Column(AddPrpTask_isDeleted.Name) IS NULL) && Column(AddPrpTask_isDeleted.Value) NOT LIKE true)) && (Column(IWA_TaskQuery.CurrentActivatorUserId) = 64104 || (! Column(IWA_Task.State) = 3 && (Column(IWA_Task.ActivatorRoleId) = 204 || (Column(IWA_Task.IsExpired) = 1 && Column(IWA_Task.ExpiryActivatorRoleId) = 204))) || (! Column(IWA_Task.State) = 3 && (Column(IWA_Task.ActivatorRoleId) = 205 || (Column(IWA_Task.IsExpired) = 1 && Column(IWA_Task.ExpiryActivatorRoleId) = 205))) || (! Column(IWA_Task.State) = 3 && (Column(IWA_Task.ActivatorRoleId) = 902 || (Column(IWA_Task.IsExpired) = 1 && Column(IWA_Task.ExpiryActivatorRoleId) = 902)))) && (Column(IWA_TaskQuery.State) = 4 || Column(IWA_TaskQuery.State) = 5 || Column(IWA_TaskQuery.State) = 8) && ((Column(AddPrpCase_taskType.Name) = taskType || Column(AddPrpCase_taskType.Name) IS NULL) && Column(AddPrpCase_taskType.Value) LIKE CONTINUE_CREATE_KLARAHOME_COMPANY))

Do you have any idea about it? How can I print sql?

asked 27.07 at 04:55

maidanh's gravatar image

maidanh
364
accept rate: 0%


Hi

Basically you can enable a logger that prints out all statements that are executed by Axon.ivy engine against the System Database. It will grow big, so I suggest that you put into an extra file:

Open [engineDir]/configuration/log4jconfig.xml and route all logs of the persistence to a custom appender called sqlLog:

<category name="ch.ivyteam.ivy.persistence.db" class="ch.ivyteam.log.Logger">
    <appender-ref ref="sqlLog"/>
    <priority value="DEBUG"/>
  </category>

Declare the appender as FileLog (copy the normal file logger and adjust):

<appender name="sqlLog" class="org.apache.log4j.DailyRollingFileAppender">
    <param name="Threshold" value="DEBUG"/>
    <param name="File" value="${user.dir}/logs/sql.log"/>
    <param name="DatePattern" value="'.'yyyy-MM-dd"/>
    <layout class="ch.ivyteam.log.layout.IvyLog4jLayout">
      <param name="DateFormat" value="HH:mm:ss.SSS"/>
    </layout>
  </appender>

The result will be a file under /logs/sql.log


full sample:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">

<!--
  This is the logging configuration file for Axon.ivy Engine.
  It defines which log messages are logged (category/priority) and where the logs are written to.

  Logging in Axon.ivy Engine is based on a 3rd party library called Log4J 
  (see http://logging.apache.org/log4j for more information).

-->
<log4j:configuration debug="false" xmlns:log4j="http://jakarta.apache.org/log4j/">

  <!--
   Defines an appender that will write log messages with at least priority INFO to a file
   that is located in the logs directory and is called ch.ivyteam.ivy.log with
   a prefix of the current date.
   Every day a new file will be created.  
  --> 
  <appender name="FileLog" class="org.apache.log4j.DailyRollingFileAppender">
    <param name="Threshold" value="INFO"/>
    <param name="File" value="${user.dir}/logs/ch.ivyteam.ivy.log"/>
    <param name="DatePattern" value="'.'yyyy-MM-dd"/>
    <layout class="ch.ivyteam.log.layout.IvyLog4jLayout">
      <param name="DateFormat" value="HH:mm:ss.SSS"/>
    </layout>
  </appender>

  <appender name="sqlLog" class="org.apache.log4j.DailyRollingFileAppender">
    <param name="Threshold" value="DEBUG"/>
    <param name="File" value="${user.dir}/logs/sql.log"/>
    <param name="DatePattern" value="'.'yyyy-MM-dd"/>
    <layout class="ch.ivyteam.log.layout.IvyLog4jLayout">
      <param name="DateFormat" value="HH:mm:ss.SSS"/>
    </layout>
  </appender>

  <!--
   Defines an appender that will write log messages with at least a priority WARN to the console  
  -->  
  <appender name="ConsoleAppender" class="org.apache.log4j.ConsoleAppender">
    <param name="Threshold" value="WARN"/>
    <layout class="ch.ivyteam.log.layout.IvyLog4jLayout">
      <param name="DateFormat" value="HH:mm:ss.SSS"/>
      <param name="ContextPrinting" value="false"/>
      <param name="FixedCategoryLength" value="20"/>
      <param name="ThreadPrinting" value="false"/>
    </layout>
  </appender>

  <!-- Prevent "ClientAbortException: java.io.IOException: Broken pipe" from filling the log -->
  <category name="org.apache.myfaces.application.ResourceHandlerImpl" class="ch.ivyteam.log.Logger">
    <priority value="FATAL"/>
  </category>

  <!-- Disable deprecated integer API warnings -->
  <!--
  <category name="ch.ivyteam.ivy.persistence.restricted.TableKeyCompatibilityConvertor" class="ch.ivyteam.log.Logger">
    <priority value="ERROR"/>
  </category>
  -->

  <!-- 
    Enables WebService SOAP Message logging for a certain application and process model. 
    Replace {application} and {process_model} in the logger name below with the name of the application and process model you want to enable the logging. 
  -->
  <!-- 
  <category name="runtimelog.{application}.{process_model}.web_service" class="ch.ivyteam.log.Logger">
    <priority value="DEBUG"/>
  </category>
  -->

<category name="ch.ivyteam.ivy.persistence.db" class="ch.ivyteam.log.Logger">
    <appender-ref ref="sqlLog"/>
    <priority value="DEBUG"/>
  </category>


  <!--
   Defines that every log message is written that has at least a priority of INFO. 
   Defines that log messages are written to the file and console appender defined above.
  -->
  <root> 
    <level value ="INFO" />
    <appender-ref ref="FileLog"/>
    <appender-ref ref="ConsoleAppender"/>
  </root>

</log4j:configuration>
link

answered 30.07 at 06:53

Reguel%20Wermelinger's gravatar image

Reguel Werme... ♦♦
7.1k11342
accept rate: 70%

edited 30.07 at 07:51

It works like a charm, I'm very appreciate you

(31.07 at 00:14) maidanh maidanh's gravatar image

I have another question that. With this configuration we only can see sql query with question mark. How can we show (sql query + param value instead of question mark)?

(31.07 at 23:35) maidanh maidanh's gravatar image

Today it is not possible to see the values for SystemDB queries. The exception is the cryptic toString() functionality of the TaskQuery instance itself. External Database queries have a mature logging and performance measuring stack included which exposes the real SQL query that was fired.

(07.08 at 07:48) Reguel Werme... ♦♦ Reguel%20Wermelinger's gravatar image
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×10

Asked: 27.07 at 04:55

Seen: 56 times

Last updated: 07.08 at 07:48