Chapter 2. Installation
Index:
HXTT Text (CSV) packages include a Type 4 JDBC driver. Type 4 indicates that the driver is written in Pure Java, and communicates in the database system's own network protocol. Because of this, the driver is platform independent; once compiled, the driver can be used on any system. HXTT Text (CSV) can run on any platforms with Java VM, which includes Microsoft Windows, Novell Netware, OS2, UNIX, and LINUX. HXTT Text (CSV) supports Personal Java, JDK1.0.X, JDK1.1.X, JDK1.2.X, JDK1.3.X, JDK1.4.X, JDK1.5.X, JDK1.6.X, JDK1.7.X, JDK1.8.X, and JDK1.9.X. HXTT Text (CSV) includes a database engine which can support multi-user access. It supports { UNION | INTERSECT | EXCEPT | MINUS } [ ALL ] query , INNER JOIN, FULL JOIN, LEFT JOIN, RIGHT JOIN, NATURAL JOIN, CROSS JOIN, and subquery which includes single-row subquery, multirow subquery, multiple-column subquery, inline views, and correlated subquery.
When java loads any class, it searches a list known as the classpath. This is a list of directories where classes are placed, or a list of jar files (archives containing classes and other resources) or both. HXTT Text (CSV) driver is a Type 4 driver. You can do this in many different methods, but the most command are:
You can know detailed information about "Setting the Classpath" from your JDK Tools and Utilities. Let's use JDBC4.0 package as a simple sample. To put Text_JDBC40.jar into your class path, you should use "export CLASSPATH=/usr/share/lib/Text_JDBC40.jar:$CLASSPATH" on Solaris and Linux, and "SET CLASSPATH=\javalib\Text_JDBC40.jar;%classpath%" on Windows.
Any source that uses JDBC needs to import the java.sql package by using "
import java.sql.*;".
HXTT Text (CSV) driver' name is com.hxtt.sql.text.TextDriver, and you can uses it without
involving hard coding the driver into your code. You do this by setting the
jdbc.drivers system property. For example, for command line apps you can use:
java -Djdbc.drivers=com.hxtt.sql.text.TextDriver yourApp
Then, the JVM upon startup will load the drivers automatically. Some applications
(JBoss, Tomcat etc) support a .properties file which they use to save putting
this on the command line.
The second method is the most common and involves you loading the driver yourself.
It's simple:
Class.forName("com.hxtt.sql.text.TextDriver");
From then on you can get connections from DriverManager.
Note: If Class.forName() throws ClassNotFoundException, you should check your classpath.
After the driver has been registered with the DriverManager, you can obtain a Connection instance that is connected to a particular database by calling DriverManager.getConnection(). With JDBC, a database is represented by a URL (Uniform Resource Locator).
Embedded: jdbc:text:[//]/[DatabasePath][?prop1=value1[;prop2=value2]] (You can omit that "//" characters sometimes) or jdbc:%csv:[//]/[DatabasePath][?prop1=value1[;prop2=value2]] (You can omit that "//" characters sometimes) The only difference for two kinds of url format is: with jdbc:csv prefix, the default table type for CREATE TABLE is CSV. For example: "jdbc:text:/." "jdbc:text:/c:/data" for Windows driver "jdbc:text:///c:/data" for Windows driver "jdbc:text:////usr/data" for unix or linux "jdbc:text://///192.168.10.2/sharedir" for UNC path "jdbc:text:/./data" "jdbc:csv:/." "jdbc:csv:/c:/data" "jdbc:csv:////usr/data" Remote Access (client/server mode): jdbc:text://host:port/[DatabasePath] or jdbc:csv://host:port/[DatabasePath] The only difference for two kinds of url format is: with jdbc:csv prefix, the default table type for CREATE TABLE is CSV. For example: "jdbc:text://domain.com:3099/c:/data" if one TextServer is run on the 3099 port of domain.com Compressed Database:(.ZIP, .JAR, .GZ, .TAR, .BZ2, .TGZ, .TAR.GZ, .TAR.BZ2, .7z) jdbc url format is the same as embedded url and remote url. For example: "jdbc:text:/c:/test/testtext.zip Memory-only Database: jdbc:text:/_memory_/ URL Database:(http protocol, https protocol, ftp protocol, sftp protocol) jdbc:text:http://httpURL jdbc:text:https://httpsURL jdbc:text:ftp://ftpURL jdbc:text:ftps://ftpURL jdbc:text:ftpes://ftpURL jdbc:text:sftp://sftpURL For example: "jdbc:text:http://www.hxtt.com/test" //Note: FTP site's user/password should be set in ftpURL, and cannot be set in JDBC connection property because user/password JDBC connection property belongs to server/client connection. //FTP protocol supports explicit SSL/TLS encryption (FTPES) and implicit SSL/TLS (FTPS). FTPES can be detected according to FTP Server reply, and FTPS can be used if you use port 990 in Ftp url. "jdbc:text:sftp://testa:123456@localhost" SAMBA Database:(smb protocol) jdbc:text:smb://[[[domain;]username[:password]@]server[:port]/[[share/[dir/]file]]][?[param=value]] For example: "jdbc:text:smb://test1:123@100.100.13.94/textfiles/zone" //Note: SAMBA user/password should be set in SMB url, and cannot be set in JDBC connection property because user/password JDBC connection property belongs to server/client connection. UNC path JDBC url: jdbc:text:/uncpath jdbc:text:///uncpath For example: "jdbc:text:/\\PC17\c$\values" "jdbc:text:/\\PC17\val" Free JDBC url:(Warning: only use it for special project) jdbc:text:/" or "jdbc:text:///". Then you can use some full UNC path names in SQL to visit anywhere where your Java VM has right to access. For instance: select * from \\amd2500\e$\textfiles\test; select * from "\\amd2500\d$\textfiles".test; select * from ".".test; HXTT Text (CSV) supports seamlessly data mining on memory-only table, physical table, url table, compressed table, SAMBA table in a sql. More details is in Advanced Programming chapter.
To connect, you need to get a Connection instance from JDBC. To do this, you use the DriverManager.getConnection() method:
Connection con = DriverManager.getConnection(url, properties);
There are a few different signatures for the getConnection() method. You should see the API documentation that comes with your JDK for more specific information on how to use them. You can specify additional properties to the JDBC driver by placing them in a java.util.Properties instance and passing that instance to the DriverManager when you connect.
Property Name
|
Definition
|
Default Value
|
lockType | To specify a compatible lock for other applications. You needn't set that property now since there is no lock from other applications. If you're using multi Java VM to access the same file, you can use lockType=MultiJVM. | null |
schemaFile | Indicates whether loads some SQL statements for table definition from a text file on database directory. '#' is used to start a remark line. schemaFile and odbcSchemaFile can be load from any (absolute or relative) path, even you can load it from http url or compressed file. Default: null. | null |
odbcSchemaFile | Indicates whether loads ODBC schema file for table definition. Default: null. | null |
createMissingFile4odbcSchemaFile | Indicates whether creates an empty CSV file which is defined in ODBC schema file, but is missing. | true |
fileExtension | To specify other suffix as default extension of raw data or flat file. | BIN |
csvfileExtension | To specify other suffix as default csv file extension. | CSV |
_CSV_Separator | To specify a character sequence used to separate the values of the fields. It supports escape processing so that you can use \t, \r, \u001A, \x32, and so on. It will detect automatically CST(TSV, PSV) if _CSV_Separator defines more than one separator. For instance, _CSV_Separator=,__or__\t__or__\x32 . If you wish to know what's the detected _CSV_Separator character, you can use DatabaseMetaData.getTables(...) or sql(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_type='TABLE' and table_name='dogstab.csv';) to get a ResultSet object, then use Resulset.getString("_CSV_Separator") to get the detected separator. |
, |
_CSV_EOL | To specify 1~2 character sequence to terminate one line. An end-of-line line sequence is any one of a line feed ('\n', 0x0A), a carriage return ('\r', 0x0D), or a carriage return followed immediately by a linefeed. In most occasions, you needn't to care that connection property, since HXTT CSV supports even to mix three styles in one file. For data update, HXTT CSV can detect automatically OS version and choose the suitable EOL style in UNIX-style, DOS-style, and Mac-style. If you wish to create unix-style file on Windows, then you need to assign that connection porperty. It supports escape processing so that you can use \t, \r, \u001A, \x32, and so on. For instance, your data file is using $ as EOL, HXTT CSV can support that file. | null |
_CSV_Quoter | To specify a character used to quote the string value. It supports escape processing so that you can use \t, \r, \u001A, \x32, and so on. | " |
_CSV_Header | Indicates whether the first record of text file to consist of the names
of the fields in the data following. The first CSV line information is used to verify predefined CREATE TABLE sql when _CSV_Header=true. You can use the following code to know the error in your predefined CREATE TABLE sql. If you make sure that predefined CREATE TABLE sql is correct, you can ignore those warnings.
SQLWarning warnings=con.getWarnings(); if(warnings!=null){ do{//SQState: 2A000 Direct SQL syntax error or access rule violation System.out.println(warnings.getMessage()); System.out.println("Error Code:"+warnings.getErrorCode()); System.out.println("SQL State:"+warnings.getSQLState()); }while((warnings=warnings.getNextWarning())!=null); con.clearWarnings(); } For CSV/TSV file, the preferable solution is using maxScanRows to detect automatically table information, not using CREATE TABLE sql. |
false |
_CSV_Comment | To specify whether there're some comment lines before csv header and data rows. If you use n (integer values), the first n lines will be ingored as comment. If you use some paragraphs, these paragraphs will be used for csv file creation, the total line number of comment will be used for existent file. | null |
_CSV_ColumnCount4EmptyFile | To specify the default column count for an empty text file without header information and data rows. | 25 |
soloMode | Indicates whether uses solo mode for speed optimization. Solo mode means single user read/write mode or multi-user read mode. | true |
readOnlyMode | Indicates whether uses readOnly mode for speed optimization. ReadOnly mode means multi-user read mode and need more memory. | false |
maxScanRows | Indicates how many rows should be scanned when determining the column types. If you set maxScanRows to 0, the entire file is scanned. If you set maxScanRows to a negative value, the file won't be scanned. For those tables with predefined table structure, that option will be ignored. | -1 |
maxScanRows4ColumnCount | Indicates how many rows should be scanned when determining the possible maximum column count. If you set maxScanRows4ColumnCount to 0, the entire file is scanned. If you set maxScanRows4ColumnCount to a negative value, the file won't be scanned, and will use the absolute value of that negative value as column count. For those tables with predefined table structure, that option will be ignored. | 1 |
ignoreDirtyData | Indicates whether ignores all dirty data and return null value when failed to parse number value or date value. You can use the following code to know where's dirty data in your file.
warnings=rs.getWarnings(); if(warnings!=null){ do{//SQState: C0106 Convert dirty data into null value System.out.println(warnings.getMessage()); System.out.println("Error Code:"+warnings.getErrorCode()); System.out.println("SQL State:"+warnings.getSQLState()); }while((warnings=warnings.getNextWarning())!=null); rs.clearWarnings(); } |
false |
ignoreDirtyXML | Indicates whether ignores all dirty data and return null value when found invalid XML characters in string value. You can use the following code to know where's dirty xml data in your file.
warnings=rs.getWarnings(); if(warnings!=null){ do{//SQState: C0106 Convert dirty data into null value System.out.println(warnings.getMessage()); System.out.println("Error Code:"+warnings.getErrorCode()); System.out.println("SQL State:"+warnings.getSQLState()); }while((warnings=warnings.getNextWarning())!=null); rs.clearWarnings(); } |
false |
tmpdir | Indicates whether set a temp directory, Default: the value of JVM's "java.io.tmpdir" property. If that value is incorrect, using the directory of JDBC url. _memory_ means large data in memory. | null |
delayedClose | Indicates the delayed seconds for close transaction. That option is used to avoid frequent close/open table operations for following sqls. Automatic temporary index is disabled when delayedClose<=60s. You can use 0~120 seconds. Default: 3. | null |
refreshInterval | To specify a a refresh interval setting in seconds for FTP/SFTP/HTTP/HTTS database file which determines how long it to discard the content cache. | 60 |
lockTimeout | To specify HXTT Text (CSV) driver's timeout in milliseconds to wait until other processes or applications released record lock or table lock. 0 means a default value, and <0 means no wait. For server/client mode, remote client connection uses also that parameter(Default value: 30000ms) to wait response from server side. | 1000 |
maxIdleTime | Indicates the max idle time in minute for remote connection. That option is mainly used to avoid closing automatically idle remote connection for connection pool. Embedded idle connectoin won't be closed automatically except for garbage collection. You can use 1~1440 minutes. Default: 30. | null |
soTimeout | To specify Enable/disable Socket read timeout with the specified timeout, in milliseconds. With this option set * to a non-zero timeout, a read() call on the InputStream associated with * this Socket will block for only this amount of time. If the timeout * expires, a java.net.SocketTimeoutException is raised, though the * Socket is still valid. The option must be enabled * prior to entering the blocking operation to have effect. The * timeout must be {@code > 0}. * A timeout of zero is interpreted as an infinite timeout. | 1000 |
charSet | To specify a Character Encoding Scheme other than the client default. You can find a Supported Encodings list of file:///c|/jdk1.2/docs/guide/internat/encoding.doc.html. Cp895(Czech MS - DOS 895), Cp620(Polish MS - DOS 620) and Mazovia are extra supported although JVM doesn't support those. | null |
ignoreCompressionFile | Indicates whether can list alll tables in compression file. You can use null, true, false | true |
ODBCTrimBehavior | Indicates whether works like MS Access ODBC driver to ignore tail space characters in condition expression. You can use null, true, false | false |
caseInsensitive | Indicates whether works like MS Access ODBC driver to be case insensitve for string comparison. You can use null, true, false | false |
emptyDecimalAsZero | Indicates whether returns empty decimal as zero value. You can use null, true, false | false |
emptyStringAsNull | Indicates whether returns empty string as null value. You can use null, true, false | true |
locale | locale is used to specify a default local for parse. You can use CANADA, CANADA_FRENCH, CHINA, CHINESE, ENGLISH, FRANCE, FRENCH, GERMAN, GERMANY, ITALIAN, ITALY, JAPAN, JAPANESE, KOREA, KOREAN, PRC, ROOT, SIMPLIFIED_CHINESE, TAIWAN, TRADITIONAL_CHINESE, UK, or US. | null |
dateFormat | dateFormat is used to specify a default parse sequence of date(Default: 'yyyy-MM-dd') format. Date and Time patterns follow the Java java.text.SimpleDateFormat Format (https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html) standard. You can use __or__ to define more than one format for ETL data type detection, but only the first format will be used for output format. | yyyy-MM-dd |
timeFormat | timeFormat is used to specify a default parse sequence of time(Default: 'hh:mm:ss') format. Date and Time patterns follow the Java java.text.SimpleDateFormat Format (https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html) standard. You can use __or__ to define more than one format for ETL data type detection, but only the first format will be used for output format. | hh:mm:ss |
timestampFormat | timestampFormat is used to specify a default parse sequence of timestamp(Default: 'yyyy-MM-dd hh:mm:ss') format. Date and Time patterns follow the Java java.text.SimpleDateFormat Format (https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html) standard. You can use __or__ to define more than one format for ETL data type detection, but only the first format will be used for output format. | yyyy-MM-dd hh:mm:ss |
decimalFormat | decimalFormat is used to specify a default parse sequence of decimal number format. You can use __or__ to define more than one format for ETL data type detection, but only the first format will be used for output format. | null |
decimalSeparator | decimalSeparator is used to specify a default character for decimal sign. Different for French, etc. | null |
groupingSeparator | groupingSeparator is used to specify a default character for thousands separator. Different for French, etc. | null |
timezone | local is used to specify a default time zone for calendar. "local" means use the local calendar. | null |
focusValue | For extracting XML/JSON data into a relational table, if set to true, it will fetch parsed SQL value, not XML element, or JSON object. | true |
stuffAutomatically | For extracting XML/JSON data into a relational table, if it set to true, it can split one complicated element/object into multi data rows. If it set to false, it can still flat XML/JSON into SQL table, but won't increase data rows by stuff automatically. | true |
maxStuffColumnCount | When using stuffAutomatically=true, maxStuffColumnCount value limits the max expanded child columns. Please remember, one stuffed column can own many child columns. | 2 |
maxStuffLevel | maxStuffLevel value limits the max level of expanded child columns. For instance, `SubImageList/SubImageInfoObject/ImageID` column will show for maxStuffLevel=3. `SubImageList/SubImageInfoObject/` column will show for maxStuffLevel=2, and you can use `SubImageList/SubImageInfoObject/`->'ImageID' to quote the value of ImageID. | 2 |
hyphen4name | For Extracting XML/JSON data into a relational table, it can convert automatically sub element/object to SQL column. The default column name is ParentName_ChildName, if you use '_' as separator. For instance, ""name": {"first": "John", "last": "Doe" } will become two columns, name_first and name_last. If you use null or empty string, then the columm name will become ParentNameChildName. Then you can use select namefirst,namelast from yourJsonTable. | _ |
hyphenInColumName | '_', '-', and ' '(space) can occur in column name, and you can choose hyphenInColumName=_(or other strings) to format those special characters(_,-, space) so that database tools can read it. | null |
maxCacheSize | Indicates the max memory utilization for per table on automatic temporary index or matched result cache. You can use 16~65536 kilo bytes. Default: 1024. | null |
host | The remote host on which one TextServer is running | null |
port | The port on which one TextServer is listening | null |
serverType | The type of TextServer on the remote host | null |
user | The user to connect as | null |
password | The password to use when connecting | null |
cryptType | To specify a crypt type for Table Encryption and Column Level Encryption. All new created table in this connection will become crypted table. You can use DES, TRIDES, BLOWFISH, and AES now. | null |
cryptKey | To specify a crypt key. Without encrypt key, CREATE TABLE won't create crypted table. | null |
storeCryptKey | Indicates whether crypt key is stored in crypted table. If stored, crypted table can be opened automatically in any connection without predefined crypt properites. If not stored, cryptd table can only be opened with correct key, and none include us can help you in cracking your data without the correct key. | false |
When your code then tries to open a Connection, and you get a No driver available SQLException being thrown, this is probably caused by the driver not being in the class path, or the JDBC url not being correct.
To close the database connection, simply call the close() method to the Connection:
con.close();