Introduction:
The following document will go through the functionality found within JetTrac DB2XML. The purpose of this module is to run an SQL query on a database and output the resulting data into an XML file.
Technical Support:
If you need assistance in installing and configuring JetTrac DB2XML™, 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 DB2XML:
To run the program, you will need to have the following files located in the same directory:
- JTDB2XML.exe
- ExitHandler.dll
Executable files and dll files should not be edited for any reason.
Default Functionality:
JetTrac DB2XML takes an SQL query and runs it on a database, then outputs the resulting data in XML format.
Job Step Configuration Window in JobConfig for JetTrac DB2XML:
When setting up a job step using JTDB2XML in JobConfig the only two lines of the config you need to worry about are the first and the last, the Config file 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 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 DB2XML Command Line:
The command line to run JetTrac DB2XML contains the location of the .exe file, 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:\JTDB2XML.exe” “C:\JTDB2XML.ini” “C:\output.xml” “C:\JTDB2XML.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 DB2XML 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
SqlStatement=select * from table;
RowTag=row
JobName=job
FieldMap=FieldName1>NewFieldName1|FieldName2>NewFieldName2
- 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.
- SqlStatement: The SQL statement you wish to run. This will determine what data is actually pulled from the database. This value can be fairly short or extremely long. You are also able to add a single SQL statement here or multiple.
- RowTag: This value will be the parent node that each row of the output data will be found in.
- 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.
- FieldMap (optional): This optional value will change the name of the field you are exporting to a new name. Multiple field name changes can be added here by separating each one by a pipe.
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 batch version of this module named JetTrac DB2XMLBatch which can run multiple SQL queries in a single module.