StelsDBF JDBC Driver v3.0 Documentation

Contents

Installation

Driver Classes

URL Syntax

User-defined SQL functions

Installation

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

Driver Classes

Description	Classes
Driver class (JDBC API v1.0)	jstels.jdbc.dbf.DBFDriver
Data Source class (JDBC API v2.0)	jstels.jdbc.dbf.DBFDataSource
Connection Pool Data Source class (JDBC API v2.0)	jstels.jdbc.dbf.DBFConnectionPoolDataSource

URL Syntax

The connection URL is jdbc:jstels:dbf:dbfdir, where dbfdir may be the following:

absolute or relative path to the local directory containing text files, e.g.:

jdbc:jstels:dbf:c:/mydir/dbffiles

jdbc:jstels:dbf:dbffiles
FTP URL to the FTP-server directory (syntax: ftp://user:password@hostname[:port]/[dirpath/]), e.g.:

jdbc:jstels:dbf:ftp://login:password@somesite.com:21/dbffiles
HTTP URL to the web-server directory, e.g.:

jdbc:jstels:dbf:http://www.somesite.com/dbffiles
HTTP URL to the dynamic server page (JSP, ASP, PHP, CGI, etc) which returns a DBF file as output. For more information please see useWebParam driver property.

jdbc:jstels:dbf:http://www.somesite.com/out.jsp

Driver Properties

The driver supports a number of parameters that change default behavior of the driver.

These properties are:

charset is used to specify a different than default charset encoding of input file (Default is "8859_1" charset)

extension is used to specify a different file extension (Default is ".dbf")

If extension set to ".dbf" then both "myTable.dbf" and myTable are valid.

Note: You should stick to one of these table naming types in the SQL queries, i.e. either "myTable.dbf" or myTable.

logPath is used to set a file path to the log file.

packDBF. If the packDBF parameter is set to "true", the driver packs DBF files by removing deleted records and memo fields. (Default is "false")

Advanced Properties

emptyStringAsNull. If 'emptyStringAsNull' is set to 'true', empty strings are treated as Null values. (By default is true).

trimBlanks. If 'trimBlanks' is set to 'true', the driver trims leading and trailing spaces for string values when reading a DBF file. (By default is true).

useWebParam is used to specify the name of the web parameter that will be used to transfer a DBF file name in the dynamic server page. For instance, if you specify the following value: 'tablename', the driver will access server page using the following HTTP URL:

http://www.site.com/out.jsp?tablename=sometable (where 'sometable' is the table specified in the SQL query)
Note: If you specify driver properties directly in the driver URL and server page also has its own parameters, you should separate them with '??':
jdbc:jstels:dbf:http://www.site.com/out.jsp?param1=value1??useWebParam=tablename&suppressHeaders=true

This following example code shows how these properties are used:

Properties props = new java.util.Properties();

props.put("extension",".db");       	// file extension is .db

props.put("charset","ISO-8859-2");      // file encoding is "ISO-8859-2"

Connection conn = DriverManager.getConnection("jdbc:jstels:dbf:" + args[0],props);

You can also use jstels.jdbc.dbf.DBFDataSource class:

DBFDataSource dbfDS = new DBFDataSource();

dbfDS.setPath("c:/dbffiles");      	// path to the DBF directory

dbfDS.setFileExtension(".db");    	// file extension is .db

dbfDS.setCharset("ISO-8859-2");     	// file encoding is "ISO-8859-2"

Connection conn = dbfDS.getConnection();

Besides, the driver allows to append the properties to the URL like this:

Connection conn =

DriverManager.getConnection(

"jdbc:jstels:dbf:path?charset=ISO-8859-2&caching=false");

Connection Example

This example code shows how the driver is used. You can download it here.

import java.sql.*;

public class DBFDriverTest {

public static void main(String[] args) {

try {

// load the driver into memory

Class.forName("jstels.jdbc.dbf.DBFDriver");

// create a connection. The first command line parameter is assumed to

// be the directory in which the .dbf files are held

Connection conn = DriverManager.getConnection("jdbc:jstels:dbf:" + args[0]);

// create a Statement object to execute the query with

Statement stmt = conn.createStatement();

// execute a query

ResultSet rs = stmt.executeQuery("SELECT * FROM \"test.dbf\"");

// read the data and put it to the console

for (int j = 1; j <= rs.getMetaData().getColumnCount(); j++) {

System.out.print(rs.getMetaData().getColumnName(j) + "\t");

}

System.out.println();

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

rs.close();

stmt.close();

conn.close();

}

catch (Exception e) {

e.printStackTrace();

}

Driver Modes

There are two main modes the driver can run in: the mode with data caching and the mode with data swapping. Running in the first mode (default mode), the driver caches DBF files in the RAM making it possible to achieve maximum performance while processing DBF. Keep in mind that Java Virtual Machine must have enough free memory allotted for storing large tables (use -Xms and -Xmx JVM options). You can also use the DROP TABLE <table name> FROM CACHE command to force tables to be removed from the cache.

The second mode is recommended for processing large DBF files (>100 MB). To use this mode, set the driver property caching to false. There are also some properties for configuring this mode:

nonTransactSubMode is used to set transaction submode. The submode nonTransactSubMode = true is optimized for non-transaction operations and nonTransactSubMode = false completely supports transactions (by default this parameter is true).

tempPath - directory where temporary files will be created (by default it is a OS temporary directory, specified by JVM environment variable "java.io.tmpdir").

maxRecordsInMemoryPerTable is used to define how many records should be saved in the RAM for each table (by default - 5000 records).
maxSwapStringSize - size for MEMO columns in swap files (by default - 50 characters).

Example:

Properties props = new java.util.Properties();

props.setProperty("caching", "false"); // switch to the swapping mode
props.setProperty("tempPath", "c:/temp");
props.setProperty("maxRecordsInMemoryPerTable", "1000");
props.setProperty("maxSwapStringSize", "60");
props.setProperty("nonTransactSubMode", "true");

Connection conn = DriverManager.getConnection("jdbc:jstels:dbf:" + args[0], props);

Data type mapping

The driver supports the following data types: INTEGER, BIGINT, DOUBLE, VARCHAR, DATE, BOOLEAN, MEMO.

The table below demonstrates the mapping scheme between StelsDBF data types and DBF data types:

StelsDBF data type	DBF data type	*JDBC returned type (java.sql.Types.)**	Java class used in StelsDBF
Integer	NUMERIC ( length <= 10, decimal count = 0 )	java.sql.Types.INTEGER	java.lang.Integer
Bigint	NUMERIC ( length > 10, decimal count = 0 )	java.sql.Types.BIGINT	java.lang.Long
Double	FLOATING POINT	java.sql.Types.DOUBLE	java.lang.Double
VARCHAR	CHARACTER	java.sql.Types.VARCHAR	java.lang.String
Date	DATE	java.sql.Types.TIMESTAMP	java.util.Date
BOOLEAN	LOGICAL	java.sql.Types.BOOLEAN	java.lang.Boolean
MEMO	MEMO	java.sql.Types.LONGVARCHAR	jstels.database.types.MemoType

Note: While reading DBF files, NUMERIC data type with the decimal count > 0 is interpreted as DOUBLE data type.

Example of CREATE TABLE statement:

CREATE TABLE test(

int_col INTEGER(5),

long_col BIGINT(12),

float_col DOUBLE(15,2),

str_col VARCHAR(10),

dat_col DATE,

bool_col BOOLEAN

For more information about DBF data types please see the corresponding DBase/XBase documentation.

Supported SQL Syntax

StelsDBF supports the most part of ANSI 92 grammar like SELECT, INSERT, UPDATE, DELETE and CREATE statements.

An SQL query must meet the following conditions:

It must corespond to the SQL syntax supported by the driver. For detailed information about supported SQL syntax please see the specification here.
А column using an SQL reserved word as a name or containing spaces and other delimiters must be quoted in double quotes in a query. For example, SELECT "Date", "My integer-column" FROM "test.dbf"
To use single quotes (') and backslashes (\) in a string constant you should use an escape character (\). For example: 'abcd\'ef\\g'

Query examples:

SELECT SUM(a) AS col1, MAX(b) / MAX(c) AS col2 FROM "test.dbf" GROUP BY a HAVING AVG(a) > 30

SELECT name FROM "salesreps.dbf" WHERE ( rep_office IN ( 22, 11, 12 ) ) OR ( manager IS NULL AND hire_date >= to_date ( '01-05-2002','dd-MM-yyyy' ) OR ( sales > quota AND NOT sales > 600000.0 )

SELECT city, target, sales FROM "offices.dbf" WHERE region = 'Eastern' AND sales > target ORDER BY city

SELECT * FROM "prices.dbf" ps JOIN regions regs ON ps.regionid = regs.id JOIN "products.dbf" prod ON prod.prodid = ps.prodid

INSERT INTO "salesreps.dbf" (name, age, empl_num, sales, title) VALUES ('Henry Smith', 35, 111, NULL, 'Sales Mgr')

DELETE FROM "salesreps.dbf" WHERE NAME LIKE 'Henry%'

UPDATE "customers.dbf" SET credit_limit = 50000.00 WHERE company = 'Acme Mfg.'

User-defined SQL functions

You can use your own SQL functions in the driver. To use this feature, you should do the following:

1) Create a static method that will act as an SQL function
Mind that:

The method must have the public modifier
The number of arguments in the method is arbitrary, but they must belong to one of the java classes specified in the data types table
It is advisable to process the null values for arguments in the method code
The method can return the null value as well

For example:

package my_pack;

public class MyFuncs{

// user-defined SQL function that formats the given argument into a date/time string with specified format

public static String format_date( java.util.Date d, String format ) {
// process the null values

    if (d == null || format == null)
    return null;
    java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat(format);
    // return a function result with java.lang.String type

return sdf.format(d);
}
}

2) Register the function using the static method jstels.jdbc.dbf.DBFDriver.registerFunction (String funcName, String handler). Where funcName argument is the name by which this method will be called in SQL queries and handler argument is the fully-qualified method name.

For example:

jstels.jdbc.dbf.DBFDriver.registerFunction ( "format_date", "my_pack.MyFuncs.format_date" )

Also, you can use the driver property function:<my_func>.

For example:

Properties props = new java.util.Properties();

props.put("function:formate_date","my_pack.MyFuncs.format_date");

...

Connection conn = DriverManager.getConnection("jdbc:jstels:dbf:" + args[0], props);

// or append this property to the URL

Connection conn2 = DriverManager.getConnection("jdbc:jstels:dbf:" + args[0]

 + "?function:formate_date=my_pack.MyFuncs.format_date");

3) Call the function in an SQL query

For example:

Statement st = connection.createStatement();

st.execute( "select format_date( date_column , 'yyyy-MM-dd' ) from \"test.dbf\"" );

StelsDBF JDBC Driver v3.0 Documentation

Installation

Driver Classes

URL Syntax

Driver Properties

Driver Modes

The driver supports the following data types: INTEGER, BIGINT, DOUBLE, VARCHAR, DATE, BOOLEAN, MEMO.

The table below demonstrates the mapping scheme between StelsDBF data types and DBF data types:

Supported SQL Syntax

StelsDBF supports the most part of ANSI 92 grammar like SELECT, INSERT, UPDATE, DELETE and CREATE statements.

Query examples:

SELECT SUM(a) AS col1, MAX(b) / MAX(c) AS col2 FROM "test.dbf" GROUP BY a HAVING AVG(a) > 30

SELECT name FROM "salesreps.dbf" WHERE ( rep_office IN ( 22, 11, 12 ) ) OR ( manager IS NULL AND hire_date >= to_date ( '01-05-2002','dd-MM-yyyy' ) OR ( sales > quota AND NOT sales > 600000.0 )

SELECT city, target, sales FROM "offices.dbf" WHERE region = 'Eastern' AND sales > target ORDER BY city

SELECT * FROM "prices.dbf" ps JOIN regions regs ON ps.regionid = regs.id JOIN "products.dbf" prod ON prod.prodid = ps.prodid

INSERT INTO "salesreps.dbf" (name, age, empl_num, sales, title) VALUES ('Henry Smith', 35, 111, NULL, 'Sales Mgr')

DELETE FROM "salesreps.dbf" WHERE NAME LIKE 'Henry%'

UPDATE "customers.dbf" SET credit_limit = 50000.00 WHERE company = 'Acme Mfg.'

User-defined SQL functions

[HOME] [TOP]