Manual for the i-net FOSITEX JDBC driver

Version 1.19
Last Modified: 26. Oct. 2011

Table of Content

1. Description

This is a documentation of the i-net Fositex JDBC 4 driver for simple text.

2. Installation

2.1. Using the Driver in a Java Application

Add the file Fositex.jar to your classpath or extract the jar file in the directory of the application.

3. Getting Started

3.1. Java and JDBC Versions

Java Version: 1.1.x or higher
JDBC Version: 1.22

3.2. Driver Class Name

The class name of the driver is: com.inet.csv.CsvDriver

3.3. JDBC URL Syntax of the Driver

jdbc:csv:<path>

<path> - an absolute or relative to the application pathname of a local directory contained table files

Example:

jdbc:csv:C:CsvExampleDB jdbc:csv:ExampleDB

3.4. Implemented Driver Properties

Name Default Description
delimiter ; A character used to separate the values in the table.
e.g.: delimiter = (,)
or delimiter = (t) for tab character
or delimiter = fixedlength for table in the fixed length format
colnameheader true TRUE - the table has a header
FALSE - no header
locale default on your system This parameter could be used to specify the locale for date/time formatting. It consists of two underline separated parts:

- ISO Language Code (the lower-case two-letter code as defined by ISO-639)
- ISO Country Code (the upper-case two-letter codes as defined by ISO-3166)
e.g.: en_US or de_DE
style SHORT This parameter is used to specify the date/time formatting style.
The styles include SHORT, MEDIUM, LONG, FULL. The exact result depends on the locale, but generally:

- SHORT is completely numeric, such as 12.13.52 or 3:30pm
- MEDIUM is longer, such as Jan 12, 1952
- LONG is longer, such as January 12, 1952 or 3:30:32pm
- FULL is pretty completely specified, such as Tuesday, April 12, 1952 AD or 3:30:42pm PST.
extension .csv This extension can be left out in sql statements for such table files. (e.g. if extension set to .txt then both myTable.txt and myTable are valid)
Note: This property can be put only in the url and it applies to whole connection.
casesensitive false TRUE - the column names and data are case sensitive
FALSE - not case sensitive
Note: This property can be put only in the url and it applies to whole connection.
charset - Set the default codepage of the database. It is equals to CharacterSet in schema.ini.
file - This property can be added to the table description in “schema.ini” if the table name used in SQL statements differ from the table file name. The file can be a file name if the table file is in the database directory or a full path name if the table file is anywhere else.For example:
[myTable]
File = myTableFile.dat
:
[myTable]
File = c:DatamyTableFile.dat
:Note: This property can not be put in the url. This property can be set in schema.ini for each table.
comment - If this property was set then the lines with “comment” string at start will be ignored by the driver.
E.g. comment=# or comment=a word
quotechar The quotation character added by the driver to character values when written they to the table file (INSERT or UPDATE statements). E.g. ..&quotechar=%%&..
..&quotechar=&.. (none)
logging false If set to “true” the driver will write log to the “standard” output.
logfile ”“ if logging is set, the driver writes the log to 'filename'

To put the properties to the driver, append the properties to the URL like this:

jdbc:csv:pathname?delimiter=<your delimiter>&locale=<your locale>&style=<your style>&colnameheader=<true|false>

or use the object of type java.util.Properties in the corresponding method of DriverManager.

You can also add one or more properties in the beginning of the database schema file “schema.ini” (see also 5.) as shown below:

Delimiter = (,)
ColNameHeader = false
Locale = en_US
Style = MEDIUM
....

Note: The properties putting to a url or adding in the beginning of “schema.ini” apply to all tables in the database. The properties names putting to the the driver in this way are case sensitive. How to set properties for a single table (see 5.).

3.5. Connection Example

  import java.sql.*; // JDBC package
  ...
  String url = "jdbc:csv:pathname"; // use your pathname here
  ...
  try {
  	DriverManager.setLogStream(System.out); // to create more info for technical support
 
  	//load the class with the driver
  	Class.forName("com.inet.csv.CsvDriver");
 
  	//open a connection to the database
  	Connection connection = DriverManager.getConnection(url);
 
  	//to get the driver version
  	DatabaseMetaData conMD = connection.getMetaData();
  	System.out.println("Driver Name:\t" + conMD.getDriverName());
  	System.out.println("Driver Version:\t" + conMD.getDriverVersion());
 
  	//create a statement
  	Statement st = connection.createStatement();
 
  	//execute a query
  	ResultSet rs = st.executeQuery("SELECT * FROM tblExample");
 
  	// read the data and put it to the console
  	while (rs.next()){
  		for(int j=1; j<=rs.getMetaData().getColumnCount(); j++){
  			System.out.print( rs.getObject(j)+"\t");
  		}
  		System.out.println();
  	}
 
  	//close the objects
  	st.close();
  	connection.close();
  } catch(Exception e) {
  	e.printStackTrace();
  }
  ...

4. Column Data Types

The driver supports the following SQL data types:

CHAR
VARCHAR
LONGVARCHAR
INTEGER
TINYINT
SMALLINT
BIGINT
DOUBLE
REAL
FLOAT
DECIMAL
NUMERIC
BIT
DATE
TIME
TIMESTAMP

If the table or the column description can not be found in the “schema.ini” then the column data type is defined on the basis of the first non-null value in the column. In this manner defined data types are restricted to INTEGER, DOUBLE, CHAR, TIME, DATE and TIMESTAMP.
For more exact data types definition should be used the special text file “schema.ini” that describes data type of each table column (see 5.).

5. Database Schema

The database schema file is called “schema.ini”. This is a text file that contains the information about the database tables. This file will be create automatically while executing CREATE statement or can be written by user and put in the database directory. The driver can also work with “schema.ini” that was generated by ODBC text driver.
If this file not found in the database directory or some information in the file is missing then the driver will try to define missing parameters. How to set properties without using “schema.ini” (see 3.3.).
In the case if the table doesn't have a header the columns names will be defined as F1, F2, F3 …
The file “schema.ini” has the following format:

The table description in “schema.ini” starts with the table name(the fully file name including extension) in square bracket. In the next row follow the table properties and after that the column descriptions.
After column type you can also place the column default value or nullability.
:
[tablename]
Format = Delimited(;)
or Format = Delimted(t) (also valid: Format = TabDelimited)
or Format = FixedLength
or Format = Delimted(,) (also valid: Format = CsvDelimited)
ColNameHeader = true
:
Col1 = ColName1 VARCHAR(20)
Col2 = ColName2 DECIMAL(10,3) DEFAULT 10.0
Col2 = ColName3 INTEGER NOT NULL
:
[table2]
: Note: You can also put your own properties in schema.ini. They will be ignored by the driver. ===== 6. Date and time ===== You can use for the date and time in your table files any unquoted string values that can be parsed as date/time for the current formatting locale and style(e.g. 10/10/1999 or 10.10.1999) or date/time in the JDBC escape format(e.g. 1999-10-10).
How to set locale or style see 3.3. Example: | Time | Date | Timestamp | | 12:00 | 04.07.01 | 04.07.01 12:00 | | 12:00:00 | 2001-07-04 | 2001-07-04 12:00:00 | | 12:00:00 | 07/04/01 | 07/04/01 12:00 | Format of the first row depends on the current locale and style which can be set in schema.ini or as connection properties. Format of the second row is locale and style independent. In WHERE and SET clauses should be used date and time in JDBC escape format (see 7.).
It is also possible to use date or time in the arithmetic operations. In this case the numbers are considered as milliseconds. For instance, in order to subtract one minute to a date/time use <your time> + 60*1000, one hour - 60*60*1000, one day - 24*60*60*1000 etc.

Example: select * from myTable where myTimestamp >= {ts '2000-10-10 12:13:14'} + 24*60*60*1000 ===== 7. Escape Clauses ===== The driver implements date and time escape clauses as follows: Date: {d 'yyyy-mm-dd'}

Time: {t 'hh:mm:ss[.fff]'}

Timestamp: {ts 'yyyy-mm-dd hh:mm:ss[.fff]'}

where yyyy-mm-dd provides the year, month, and date, e.g. 1999-06-13,
hh:mm:ss[.fff] - hours:minutes:seconds[.milliseconds], e.g. 10:15:45.234. ===== 8. Unicode and Codepages ===== The driver supports ASCII and unicode. The driver detecting the codepage in the following order: - If the file is save in the Microsoft unicode format or Microsoft UTF8 format. - The values of “CharacterSet”. - The value of “charset” in the JDBC URL. ===== 9. Supported SQL Syntax ===== ==== 9.1. Creating tables ==== You can create a table file using the 'create table' command. This command creates a new text file in the directory specified in the url.

Syntax: create table table_name(column_name datatype [default constant][{null|not null}], …) Example: create table customers.txt(custID int not null, Name char(50), `Date of birth` date, Address char default “unknown”) ==== 9.2. Dropping tables ==== Use the 'drop table' command to delete the table file. This command removes also the table description from schema.ini

Syntax: drop table table_name ==== 9.3. Adding new data ==== You can use the insert command to add rows to a table. Insert syntax: insert into table_name [(column_list)] values (value_list) Examples: insert into customers.txt(custID, Name) values (10, 'Johnson') insert into customers.txt values (10, 'Johnson', 10/11/1900, 'somewhere') ==== 9.4. Changing existing data ==== Use the update command to change single rows, groups of rows, or all rows in a table.

Update syntax: update table_name
set column_name1 = {expression1 | null | constant} [, column_name2 = {expression2 | null | constant}]…
[where search_conditions] If you do not have a where clause, the specified columns of all the rows are updated. Example: update customers.txt set Name = 'Ivanow' where custID = 1231 and Address <> 'New York' Note: Any new line characters in the strings will be replaced by the driver with an another character while writing data in the file. They will be replaced back while reading the data. ==== 9.5. Deleting data ==== Syntax: delete from table_name [where search_conditions] If you do not have a where clause, all rows will be deleted.

Example: delete from customers.txt where custID = 1231 ==== 9.6. Selecting data ==== Syntax: select { select_list | * } from table_name [as][table_alias] [,table_name2…] [where search_conditions] order by column_name[{asc | desc}] In a multi-table query a column can be accesed using either the table alias or the table name without file extension. E.g. table_name.column_name. Example: select * from customers.txt order by custID select cust.address as Adresse from customers.txt cust, cust_data.txt where cust.custId = cust_data.custId and cust.name <> 'Smith' Note: The string, numeric, date and aggregate function are not supported.
Note: Both single and double quotation marks can be used to terminate a string. To mask a quotation mark within a string in sql statement use the same character twice.

Example: ”““some text””“ or '“some text”' equals “some text”
'some text' or “some text” equals some text
'some text' or ”'some text'“ equals 'some text' The driver will automatically mask quotes in strings when they writing to file depending on “quotechar” property. Note: If table or column names contain special characters(e.g. spaces) then they can be quoted with <`>. This character will be returned by the method getIdentifierQuoteString() from DatabaseMetaData. ===== 10. Debugging ===== To enable the logging of the driver you can use the static method of DriverManager class with PrintStream as argument: <code java> DriverManager.setLogStream( System.out );
logging on the standard output stream

DriverManager.setLogStream( System.err ); //logging on the standard error output stream
//logging in a file
DriverManager.setLogStream( new PrintStream( new FileOutputStream( "c:\driver.log" ) ) );

</code>

11. SQL States

The driver can produce errors with following SQL states as listed below:

SQL State Description
37000 Syntax error or access violation
08001 Unable to connect to data source
21S01 Insert value list does not match column list
S0022 Column not found
S0002 Base table not found
S0001 Base table or view already exists
S1004 Sql data type out of range
S1009 Invalid argument value
S1002 Invalid column number
S0022 Column not found
S1000 General error
IM001 Driver does not support this function

12. Known problems

Currently no known problems.

13. Support

Please read this file and our FAQ at http://www.inetsoftware.de/products/jdbc/fositex/faq.htm

If you cannot find your problem in the FAQ then send an email providing as much information of what occurred or happened as possible to Fositex@inetsoftware.de

You can find more info about our support at: http://www.inetsoftware.de/support

14. Changes

14.1. Changes in Version 1.00.

  • The name of the database schema file and its format were modified. The file is called now “schema.ini”
  • Property ColNameHeader was added.
  • Date/time escape clauses were implemented (see 7.).
  • Quoted identifiers are now supported.
  • Column and table aliasing with 'AS' was implemented.
  • PreparedStatement was implemented.
  • “default” and “not null” values are now supported.
  • Tables without header and tables in the fixed length format are now supported.

14.2. Changes in Version 1.01.

  • The syntax TableName.ColumnName is now supported (TableName without extension).
  • The key word NULL is now supported.
  • The property “extension” was added (see 3.4.).
  • The property “casesensitive” was added (see 3.4.).
  • The methods which are not suppoorted throw a SQLWarning instead of a SQLException
  • Connection properties can be supplied now also over the Propeties object in the method getConnection(String url, Properties info) of DriverManager.
  • Methods Connection.setCatalog and Driver.getPropertyInfo are implemented.
  • The bug with time stamp escape clause was fixed.

14.3. Changes in Version 1.02.

  • ORDER BY is now supported.
  • The property “Comment” was added (see 3.4.).
  • The bug with getObject for null number values was fixed.
  • The property “File” was added (see 3.4.).
  • The bug with quoted table names was fixed.

14.4. Changes in Version 1.03.

  • The performance for the join operation was improved.
  • Bug with the read of extended characters (e.g. umlauts) was fixed.
  • Bug when exception “Can't define data type of column …” was throwing was fixed.
  • Bugs in DatabaseMetaData.getColumns() and getSchemas() were fixed.

14.5. Changes in Version 1.04.

  • The problem with writing and reading of strings containing new line characters was fixed.
  • The bug with NullPointerException which was throwing when a getXXX method was called on a empty result set was fixed.
  • The bug with NullPointerException in a getXXX without next() calling was fixed.
  • The property “quotechar” was added.
  • The bug in a multi-table result set which can contain uncompleted data was fixed.

14.6. Changes in Version 1.05.

  • The bug when the quoted values which contain a column delimiter were incorrect read was fixed.
  • The bug from Version 1.04, when “NULL” strings were handled like NULL-values, was fixed.
  • The bug when the string values containing new line characters were incorrect read by query with ORDER BY was fixed.
  • The bug when an exception was thrown when data ending by space on WHERE clause was fixed.
  • The bug when the new line character was not deleted trying to delete the last row of a table was fixed.
  • The bug when an exception was thrown trying to insert the values containing '(' or ')' characters was fixed.
  • The bug with retrieving TIMESTAMP values stored in 'YYYY-MM-DD' format was fixed.

14.7. Changes in Version 1.06.

  • The bug, causing values of type DECIMAL/NUMERIC being rounded off, was fixed.
  • The connection property “logging” was added.
  • An exception is now thrown when trying to request the next data from a closed ResultSet.
  • Fix for the bug: Opening a new connection changed the schema.ini location for an existent connection.
  • Fix for the bug: Some quoted values containing the column delimiter were incorrectly read.
  • The property charset was added and different codepages are supported now.
  • Support for unicode and UTF8 text files was added.

14.8. Changes in Version 1.07.

  • ArrayIndexOutOfBoundException with empty values was fixed.
  • The bug with space as delimiter was fixed.

14.9. Changes in Version 1.08.

  • ArrayIndexOutOfBoundException with FixedLength was fixed.
  • A bug with INSERT INTO and unicode was fixed.
  • A bug in Statement.close() was fixed. It has not closed all result sets. This bug has occurred only if ResultSet.close() was not called.

14.10. Changes in Version 1.09.

  • Support for IS NULL and IS NOT NULL was added.
  • A bug with the order of parentheses in the WHERE clause was fixed.
  • A bug if keywords and parentheses have no spaces between was fixed.

14.11. Changes in Version 1.10.

  • An ArrayindexOutOfBoundException with 2 or more table joins was fixed.
  • A first empty line with tables without colnameheaders with the first INSERT INTO was fixed.
  • The comparing of Strings is caseinsensitive by default now. You can change it with the flag casesensitiv.

14.12. Changes in Version 1.11.

  • A bug with saving newline was fixed.
  • A bug with JDK 1.4.x and absolute file names in SQL expressions was fixed.
  • The scanning of the data types for a table without headings works also with 1 row now.

14.12. Changes in Version 1.12.

  • A bug was fixed that in a table with only one String column and a not terminated last line then the last line was not read.

14.14. Changes in Version 1.13

  • A bug with additional spaces at the end of a SQL expression was fixed.

14.15. Changes in Version 1.14

  • A bug in the keyword parser was fixed.
  • Support for an empty column name was added.

14.16. Changes in Version 1.16 (26. Sep 2006)

  • A memory leak was fixed which occurred if used Statements objects were not closed.
  • DatabaseMetadata.getTables() now filters out all files which are ending with ”.txt“,”.csv“ and defined extension with JDBC URL property “extension”.
  • Support for files larger 2 GB was added.

14.17. Changes in Version 1.17 (14. Mar 2007)

  • For the default extension the configuration data in schema.ini for a table was not found. This resulted in wrong data if the configuration was different than the default configuration.
  • A bug with 2 quotes and a comma in a quoted string was fixed. Before, the string was split on the comma.

14.18. Changes in Version 1.18 (6. Mar 2008)

  • A regression with single quotes produced the follow exception “Table 'XYZ': row x too long > 65535”.

14.19. Changes in Version 1.19 (26. Oct 2011)

  • Changed the order of analyzing date time formats. Now the SQL formats will be parsed first. This improves the precision of time with default settings. Before seconds were not parsed with default settings.

© Copyright by i-net software
More info and updates can be found at http://www.inetsoftware.de

™ 2000-2011 i-net software

 

© Copyright 1996 - 2017, i-net software; All Rights Reserved.