JetTrac DB2XMLBatch

Introduction:

The following document will go through the functionality found within JetTrac DB2XMLBatch. The purpose of this module is to run multiple SQL queries on a database and output the resulting data into an XML file.

Technical Support:

If you need assistance in installing and configuring JetTrac DB2XMLBatch™, call Pro Technology Automation, Inc. at 805-527-1248 or email us at support@protechinc.com. Please note that the JetTrac DB2XML™ license fee does not cover configuration services and technical support so there may be an additional charge. Please ensure you read these instructions carefully before calling for technical support.

How to run JetTrac DB2XMLBatch:

To run the program, you will need to have the following files located in the same directory:

  • JTDB2XMLBatch.exe
  • ExitHandler.dll

Executable files and dll files should not be edited for any reason.

Default Functionality:

JetTrac DB2XMLBatch takes multiple SQL queries configured in the configuration INI file and runs them on a database, then outputs the resulting data in XML format.

Job Step Configuration Window in JobConfig for JetTrac DB2XMLBatch:

The Job Step Configuration window for this module is the same as the window for JetTrac DB2XML. The only exception is that the Batch version of this module also allows for an input XML file.

When setting up a job step using JTDB2XMLBatch in JobConfig the three lines of the config you need to worry about are the first and the last two, the Config file, Input XML, and Output XML.

The data entered into these lines should be the fully qualified paths to the config file and the desired location of the input and output. Eg: C:\JetTrac\…\output.xml
In JobConfig, file paths entered should not be in quotes.

As with all modules, there is a dropdown to select whether or not the job should stop completely if this step fails. If you select Yes, any error will terminate the job process and write to the log file what happened, if no, then the job will try to continue anyways. However if any steps further down the line rely on the output of a job set to not stop on error, they may not work properly.

For more specifics go to the JetTrac Field ServiceJobConfig page.

JetTrac DB2XMLBatch Command Line:

The command line to run JetTrac DB2XMLBatch contains the location of the .exe file, the input XML, the configuration .ini file, the location to save the output XML file, and the log file. The following is an example of the command line used to run this module:

“C:\JTDB2XMLBatch.exe” “C:\Input.xml” “C:\JTDB2XMLBatch.ini” “C:\output.xml” “C:\JTDB2XMLBatch.log”

Each part of the line is divided by spaces (the amount doesn’t matter), and contains the fully qualified file path to the file you are referencing surrounded by quotes. In the example all of the file paths reference the root of C:\, however, it is likely that none of these files will be located in the root of C:\ for your system, so this is simply demonstrating that the full file path is required.

JetTrac DB2XMLBatch Configuration .ini:

The configuration file for this module contains two important components, the information needed to connect to the database, and the SQL query statement that you are running. The following is a sample configuration.ini file:

DbType=MYSQL
DbConnection=dbname@localhost
DbUsername=root
DbPassword=password123
JobName=job
JTDB2XMLEXE=C:\JetTrac\Programs\JTDB2XMLBatch\JTDB2XMLBatch.exe
DataPath=C:\JetTrac\JobController\Server\Data
DataName=!!WorkOrder_Num!!_!!Building_ID!!_!!AES_Procedure_Title_Freq!!.xml

SqlStatement_1= SELECT * from table;
SuperParentTag_1=ParentTagName
AddSuffix_1=N
RowTag_1=Data

eg.

# SQL info for Riser info
SqlStatement_2= SELECT DISTINCT c.”Building_ID”, d.”RSR_Location” AS “Riser_Location”, d.”RSR_Riser_ID” AS “Riser_ID”, d.”RSR_Riser_Diameter” AS “Riser_Diameter”, d.”RSR_Main_Drain_Diameter” AS “Riser_Main_Drain_Diameter”, d.”RSR_InitialStaticPressure” AS “Riser_InitialStaticPressure”, d.”RSR_ResidualPressure” AS “Riser_ResidualPressure”, d.”RSR_FinalStaticPressure” AS “Riser_FinalStaticPressure”, d.”RSR_P_F_NA” AS “Riser_P_F_NA” FROM “_Procedures” a INNER JOIN “event” b ON a.”_kp_Procedure_ID” <> b.”_kf_Procedure_IDs” INNER JOIN “_Buildings” c ON b.”_kf_Property_ID” = c.”_kf_Property_ID” INNER JOIN “_Building_System_Info” d ON c.”_kp_Building_ID” = d.”_kf_Building_ID” INNER JOIN “calendar” e ON b.”idCal” = e.”id” WHERE e.”_kat_WorkOrder_Num” = ‘!!WorkOrder_Num!!’ AND c.”Building_ID” = ‘!!Building_ID!!’ AND d.”Item_Type” = ‘Risers & Gauges’
SuperParentTag_2=Risers
RowTag_2=Riser
AddSuffix_2=Y

 

  • DbType: Either SQL or ACCESS depending on what type of database you are accessing.
  • DbConnection: This is the location where you connect to the database.
  • DbUsername: The username used to connect to the database.
  • DbPassword: The password associated with the username.
  • JobName: If the XML data file you are outputting needs to contain a Job_Name within the JobCard, you can specify the Job_Name here. This allows the XML file that is output from DB2XML to immediately be dropped in the JobController watched folder to run another job.
  • JTDB2XMLEXE: This is the fully qualified file path to the JTDB2XML.exe file.
  • DataPath: This is the fully qualified file path to the watched Data folder
  • DataName: This string builds the name for the output XML. Variables used in the name should be surrounded by double exclamation points (!!).

The next lines can have any number of copies within the configuration file. For each SQL statement you are running in the batch have a separate set of these four lines and replace the # with the number of the SQL statement. Ex: The first set will be _1, the second, _2, etc.

  • SqlStatement_#: This value is the SQL query to be run on the database to obtain the data you want.
  • SuperParentTag_#: This is the super parent tag you wish to have surrounding the data from this SQL query in the output XML.
  • AddSuffix_#: If you wish to add a suffix to every data field output from this SQL query, enter it here. For example _1 to append every field name output from this specific SQL query with _1.
  • RowTag_#: This will be the name of the parent node that each row output from this SQL query is wrapped in.

Additional Notes:

Please note that the SQL statement section requires preexisting knowledge of SQL scripting in order to write. This can be anything from a simple query to more complete queries and can be as long as you need.

There is also a single query version of this module named JetTrac DB2XML which is used to run a single SQL query.