JetTrac DBExtract

Introduction:

Executes SQL statement to pull data from database and create XML

Technical Support:

If you need assistance in installing and configuring JetTrac DBExtract™, call Pro Technology Automation, Inc. at 805-527-1248 or email us at support@protechinc.com. Please note that the JetTrac DBExtract™ 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 DBExtract:

To run JTDBExtract, you will need these files in the same folder:

  • JTDBExtract.jar
  • JTDBExtract.ini
  • ExitHandler.dll
  • MySql.Data.dll
  • SecurityExtensions.dll

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

Default Functionality:

Automatically gets data from a database to use anywhere in the JetTrac Field Service system as needed

Job Step Configuration Window if JobConfig for JetTrac DBExtract:

When setting up a job step using JTDBExtract in JobConfig the only two lines of the config you need to worry about are the first and the last, the Config file and the Output XML. The data entered into these fields 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 DBExtract Command Line:

java -jar -DJETTRACLF=”C:\JetTrac\JobController\License\jettrac.lic” “C:\JTDBExtract.jar” “C:\JTDBExtract.ini” “C:\JTDBExtract.fnf” “C:\JTDBExtract.log”

This command line begins with the java license file followed by the fully quallified path to module’s executable file, the config.ini file, then the output fnf file and the module’s log file.

JetTrac DBExtract Configuration.ini:

#======================================

#ServerType=”MYSQL”|”MSSQL”|”ACCESS”

#ServerName=”localhost”|”Server_IP_Address”

#Database=”DatabaseName”

#Select=Pipe delimeted list of column names, or “*”

#Table=”TableName”

#Where=”ColumnName”=ValueFromDatafile

#RowName=”Name of repeating xml node denoting a row”

#FieldNamePrefix=”string to prepend to field names from SQL to ensure data tags are unique”

#UserID=”Username”

#Password=”EncryptedPassword”|”!UnencryptedPassword”

#Password may be entered unencrypted or encrypted. To encrypt password run DBExtract in a batch file with just the password as an argument. You will receive the encrypted version in the console. Don’t forget to use a “Pause” in the batch file. To use an unencrypted password put an exclamation point before the password. Example: password is “foobar”. password ini entry is: Password=”!foobar”.

#If encryption is used password must be encrypted using the local machine. No password encryption is bulletproof. Please take extra precaution to only allow the bare minimum of permissions for the database user that you use. NEVER EVER EVER USE THE ROOT or SA LOGIN. NEVER! NEVER EVER!
#Output looks like:

#

#<SQLRow>

  1. <SQLcolumn1>value1</SQLcolumn1>

 

 

  1. <SQLcolumnN></SQLcolumnN>

#</SQLRow>

#<SQLRow>

  1. <SQLcolumn1>value1</SQLcolumn1>

 

 

  1. <SQLcolumnN></SQLcolumnN>

#</SQLRow>

#…

#<SQLRow>

  1. <SQLcolumn1>value1</SQLcolumn1>

 

 

  1. <SQLcolumnN></SQLcolumnN>

#</SQLRow>