000033368 - How to use the SQL testing tool for the HXTT CSV Text Pure Java JDBC Driver outside of the Collector UI for Via Lifecycle and Governance

Document created by RSA Customer Support Employee on Jan 11, 2017Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 3Show Document
  • View in full screen mode

Article Content

Article Number000033368
Applies ToRSA Product Set: Via Lifecycle and Governance
 
IssueMany customers provide comma separated value (CSV)) files for their identity, entitlement, account, and role data. Via Lifecycle & Governance leverages a type 4 JDBC driver called HXTT Text (CSV) to be able to collect data from a CSV file(s) as if they were database tables. This has proven to be a very powerful tool for manipulating data coming from a CSV file or even joining data across multiple CSV files. However, there have been issues using this driver to collect data due to some of the SQL issues associated with using a SQL language to access data in a text file vs. a true relational database. Some issues are due to the fact that a text file doesn't have meta-data around the "columns" of data within the file so there is declared datatypes for the data as well as issues around performance; particularly when joining across files or doing complex SQL, due to the fact that this isn't a relational database and isn't going to be as perform as one.
In much the same way we encourage people who are deploying our product to test their SQL queries in their collections against their database using something like Oracles's SQL*Plus or SQL Developer and an LDAP browser to test LDAP filters in their collectors, we want a good way to test our queries against this text-based CSV database driver. One way to do that is to use a tool that supports using any JDBC type 4 driver to query their database. Another option, described in this page, is to use the HXTT Text (CSV) test tool built in the JDBC driver.
In this document we are referring to a trial version of the JDBC driver from the HXTT site. This version has limited functionality but is useful for testing SQL queries from a desktop computer. For supported SQL syntax documentation, license restrictions or information on fully functional licensed versions, please refer to the official HXTT site.
Tasks

Requirements to run the HXTT testing tool


The testing tool is simply a Java program with a UI for typing in SQL queries and viewing the data results of that query obtained using the HXTT driver. To run the the tool you will need:
  • A machine on which you can see a JAVA GUI. This test tool has a graphical interface so you either need to run it on a Windows machine or under XWindows if you running from Linux,
  • Java JRE 1.5 or later, and
  • The HXTT JDBC type 4 driver jar.
    • The limited trial version can be downloaded from the HXTT website.  
    • Refer to the HXTT site for detailed documentation and license restrictions.
  • The CSV text files from which you want to collect data.
    • Be sure that these files have gone through dos2unix if you are going to run the tool on an appliance/sandbox or have gone through unix2dos if you are going to run them on a Windows machine.

Setting up the HXTT testing tool


  1. Make sure your CSV files are in the folder referred to by the JDBC URL. So on Windows, in this example, simply place the CSV file(s) in your temp directory.
  2. Remember to ensure you run unix2dos on the CSV file (if you are on Windows) or dos2unix (if you are on Linux) just to make sure the file is the correct format. In the example here, there are four CSV files that saved under C:\temp which are also on the appliance.  These files are named RACF_Entitlements.csv, RACF_Role_Membership.csv, RACF_RoletoResources.csv, and RACF_Translation.csv.
  3. From a command line run the following command from the same directory where the Text_JDBC30.jar file is located.  Note, this assumes java is in your path: 
    java -jar Text_JDBC30.jar

  4. If the .jar extension is associated with the java executable, you may simply need to double click Text_JDBC30.jar.
  5. A GUI will appear like in the screenshot below:
Initial Screen from HXTT driver

  1. Type in any name in the Name field. In this example, we are on a Windows machine and plan to have the CSV files in the C:\temp directory it is called My Temp Directory.
  2. In the URL info field, type in the appropriate URL for the JDBC driver. This is going to be very much like the JDBC URL used in the collector. If running this tool from a Linux box, use the exact JDBC URL that is used in the collector. For example, the JDBC URL of:

  • jdbc:csv://///home/oracle/database/SampleData/Demo/?_CSV_Header=true;tmpdir=/home/oracle;

    This URL means that the driver will look in the /home/oracle/database/SampleData/Demo directory for any CSV files. On Windows,  set the URL based on a local directory where the CSV files live. For example, if using C:\temp , the JDBC URL would look like: 
    jdbc:csv:////temp?_CSV_Header=true;tmpdir=/temp;

    In either case, be sure to set the _CSV_Header param to true or false depending on whether the CSV files have a header row in them or not.
  1. Press the Add Config button to add this to thr list of server configurations on the left.
Setup HXTT URL

  1. Now, select your configuration (e. g., My Temp Directory) and select View Monitor. The following window displays:
HXTT View Monitor screen

  1. Click on Server Catalogs. You will now see:
Server Catalogs

  1. Click the Build Conn. button. The Build Conn. button makes a quasi connection to the folder. 
  2. Type in any SQL query in the white box under the Build Conn. button and press Execute. For example, run any of the individual EDC queries to see what it returns. The execute button runs the query and displays the result in the window below. Essentially the query in the white box is executed and the result set is shown below. In the example below is a query that would be configured in an EDC:
Example SQL query
NotesIf you are getting a parse error similar to the error below and believe you are using valid SQL syntax according to the HXTT documentation, it is suggested to look at the SQL statement in a HEX editor to look for invalid characters.
Syntax error: Can't continue parse. 
Error Code:172032
SQL State:2A000
java.sql.SQLException: Syntax error: Can't continue parse.
...

There are additional URL parameters available to specify different CSV file properties such as a different column separator other than a comma (_CSV_Separator). Refer to the Advanced Programming documentation from HXTT. 

Attachments

    Outcomes