|Applies To||RSA Product Set: Via Lifecycle and Governance|
|Issue||Many 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.
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:
Setting up the HXTT testing tool
|Notes||If 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.
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.