Version 1.18
Last Modified: 6. Mar. 2008
This is a documentation of the i-net Fositex JDBC 4 driver for simple text.
Add the file Fositex.jar to your classpath or extract the jar file in the directory of the application.
Java Version: 1.1.x or higher
JDBC Version: 1.22
The class name of the driver is: com.inet.csv.CsvDriver
jdbc:csv:<path>
<path> - an absolute or relative to the application pathname of a local
directory contained table files
Example:
jdbc:csv:C:\Csv\ExampleDB\ jdbc:csv:ExampleDB
| 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: 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. .."echar=%%&.. |
| 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.).
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();
}
...
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.).
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.
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
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.
The driver supports ASCII and unicode. The driver detecting the codepage in the following order:
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")
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
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')
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.
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
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.
To enable the logging of the driver you can use the static method of DriverManager class with PrintStream as argument:
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" ) ) );
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 |
Currently no known problems.
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 occured or happened as possible to Fositex@inetsoftware.de
You can find more info about our support at: http://www.inetsoftware.de/support/
Copyright by i-net software
More info and updates can be found at
http://www.inetsoftware.de
™ 2000-2009 i-net software
