Introduction:
This document is a guide to installing, configuring and using the JetTrac DBUpdate™ module.
Technical Support:
If you need assistance in installing and configuring JetTrac DBUpdate™, call ProTechnology at 805-527-1248 ext. 0 or e-mail us at support@protechinc.com. Please note that the JetTrac DBUpdate™ 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.INSTALLATION AND CONFIGURATION OF JETTRAC DBUPDATE™
Requirements/Prerequisites:
To run the program, you will need to have the following files located in the same folder:
- JTDBUpdateFromXML.exe
- ExitHandler.dll
- MySql.Data.dll
Executable files and dll files should not be edited for any reason.
Installing Microsoft .NET Framework:
You may acquire the .NET Framework from the following Microsoft location: http://www.microsoft.com/en-us/download/details.aspx?id=17851 We recommend only downloading .NET components from Microsoft directly. Contact us for further questions.
Installation and Configuration of JetTrac DBUpdate:
Installing JetTrac DBUpdate:
After the .NET Framework is installed, you are ready to proceed to the JetTrac DBUpdate installation. Extract the delivered .ZIP file to a Program directory under your JetTrac home directory. For example: C:\JetTrac\Programs\JTDBUpdate
The files you should see are:
| Filename | Description |
|---|---|
| DbUpdateFromXml.exe | JetTrac DBUpdate executable program file |
| ExitHandler.dll | JetTrac DBUpdate dynamic link library |
Configuring the JetTrac JTDBUpdate_Config.xml file:
There are two steps to complete the configuration of JetTrac DBUpdate. The first is to set up the configuration file, JTDBUpdate_Config.xml. Note: the name of the file can be modified but you will need to change that name in the Job Controller JMD file if modified.
The “config” file, JTDBUpdate_Config.xml describes the database connection, the database, the table and credentials for the connection. The “Config” file specifies the mapping of information from xml to the database fields.
The second is to set up the Job Management Database (JMD) in JetTrac JobController to call JetTrac DBUpdate™ at the correct time.
Sample JTDBUpdate_Config.xml file:
The sample file shown below indicates a system where the storage database is MSSQL using the default instance “(local)” on a machine named “yourServerName”. The database is named “JetTracTestDatabase” with a table called “JetTracTestTable”.
The table structure has 6 fields of information, FLD1-FLD5 and FormID.
<?xml version=”1.0″ encoding=”UTF-8″?>
<form1>
<Configure_Choices xmlns:xfa=http://www.xfa.org/schema/xfa-data/1.0/ xfa:dataNode=”dataGroup”/>
<Configure_JetTrac_DBUpdate>
<Data_Base_ID>JetTracTestDatabase@youSQLserverName</Data_Base_ID>
<!–
(Note: if your SQL instance name is NOT default “(local)”, you will need to use the following format: <databaseName>@<serverName>\<SQLInstanceName> .
–>
<Data_Base_Table>JetTracTestTable</Data_Base_Table>
<Data_Base_User_Name>JetTracUser</Data_Base_User_Name>
<Data_Base_Pass_Word>JetTracUserPassword</Data_Base_Pass_Word>
<Data_Base_SQL>Y</Data_Base_SQL>
<!–
For Access, Data_Base_SQL = N
For MSSQL, Data_Base_SQL = Y
For MySQL, Data_Base_SQL = MYSQL
–>
<DBConnection>
<TableDBConnection>
<HeaderRow xmlns:xfa=”http://www.xfa.org/schema/xfa-data/1.0/” xfa:dataNode=”dataGroup”/>
<FieldMap>
<XMLPath>FLD1</XMLPath>
<DBField>FLD1</DBField>
</FieldMap>
<FieldMap>
<XMLPath>FLD2</XMLPath>
<DBField>FLD2</DBField>
</FieldMap>
<FieldMap>
<XMLPath>FLD3</XMLPath>
<DBField>FLD3</DBField>
</FieldMap>
<FieldMap>
<XMLPath>FLD4</XMLPath>
<DBField>FLD4</DBField>
</FieldMap>
<FieldMap>
<XMLPath>FLD5</XMLPath>
<DBField>FLD5</DBField>
</FieldMap>
<FieldMap>
<XMLPath>FormID</XMLPath>
<DBField>FormID</DBField>
</FieldMap>
</TableDBConnection>
</DBConnection>
</Configure_JetTrac_DBUpdate>
</form1>
Running JetTrac DBUpdate:
The JetTrac DBUpdate Command Line:
JetTrac DBUpdate is a .NET Windows environment executable program. Refer to section Installing JetTrac DBUpdate for more information on acquiring and installing .NET. Assuming that you already have .NET 4 installed on your system, the format of the command line is:
“C:\JetTrac\Programs\JTDBUpdate\JTDBUpdateFromXML.exe” “”@InFile”” “”@MDFName.”” “”@LogFile.””” “Updates SQL database with data from a PDF form”
The command line begins with the file path to the executable file for the module followed by the path to the input XML file, the path to the config file, and the log file to track the step’s progress. The command line then ends with a short description of what the module does.
Job Step Configuration Window in JobConfig for JetTrac DBUpdate:
When setting up a job step using JTDBUpdate in JobConfig the only two lines of the config you need to worry about are the Config file and the Input XML. If left as an asterisk * the input will be whatever file is in the data folder that triggered the job running. This works well if it’s the first step but if the input is also the output of a previous job step you would need to specify the fully qualified path of the input file. Eg: C:\JetTrac\…\input.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.
Issues to consider when implementing JetTrac DBUpdate:
Here is a list of questions and issues that are important in implementing JetTrac JobController and JetTrac DBUpdate:
- Do you have access to a database where data will be stored?
- Have you verified the connection by using Excel or a similar tool which positively tests the connection to SQL or Access.
- Have you gathered the credentials and server information necessary to connect to the database?
- Have you verified the database table structure, ensuring that it provides a field for each piece of data being stored from your PDF file that gets extracted? We’ve assumed PDF_form > JTPDFExtract > formdata.xml > JTDBUpdate > YourDatabase.
- Your PDF form field names “will” match the field names in the XML_Data_File created by the PDFExtract by definition and the way JTPDFExtract works.
- Your ConfigurationFile must be edited to make a 1 to 1 mapping from your XML_Data_File to the database fieldnames.Ie.
<FieldMap> <!—from the ConfigurationFile.xml –>
<XMLPath>FLD1</XMLPath> <!—The field extracted from PDF –>
<DBField>XYZ1</DBField> <!—The field name in the destination database –>
</FieldMap>
Caution:
It is recommended that no “reserved words” be used as fieldnames in a SQL Table or XML Node names. The XML input file (data to write to SQL tables) cannot be properly formatted to include the SQL “[” and “]” delimiters as that would create illegal XML Node names. The update process will yield an error as DBUpdate attempts to write row(s) into the destination table.
Example: XMLData Node name: LineNo SQL FieldName: [LineNo]
Note that SQL will delineate any reserved words as they are used as field names even though such is considered bad design techniques.
Here is a link to determine if your fields may be reserved words: https://drupal.org/node/141051
Version History & Copyright:
| Date | Version | Notes |
| May 3, 2013 | 3.02 | Initial Specification Release for SQL Capable |
| November 11, 2013 | 3.3 | With Licensing |
Copyright© Pro Technology Automation, Inc., 1996-2013
All rights reserved.
Trademarks
JetTrac and JetTrac DBUpdate are trademarks of Pro Technology Automation, Inc.