Chapter 8. Answers to Frequently Asked Questions (FAQ) for the HXTT DBF Packages
The most recent version of this document can be viewed at here.
Table of Contents
1. Can I use it in an iSeries OS/400 IBM machine that has Java 1.4 running
in it?
The HXTT DBF packages can run on any platform with Java VM, which includes Microsoft
Windows, Novell Netware, OS2, UNIX, and LINUX. It supports Personal Java, JDK1.0.X,
JDK1.1.X, JDK1.2.X, JDK1.3.X, JDK1.4.X and JDK1.5.X. It supports JDBC1.2, JDBC2.0, and
JDBC3.0 now.
2. What is difference between the HXTT DBF Package, Embedded Package,
and Remote Access Package? Can I get some sample code to use the HXTT DBF?
The HXTT DBF supports Embedded and Remote Access. HXTT DBF Package includes a Database GUI manager. If you're accessing the local data, you can use the HXTT DBF Package or Embedded Package. If
you're accessing the remote data, you can use the HXTT DBF Package or Remote
Access Package. There is no any difference for your code to use anyone of three
packages. Please download the demo package from here.
3. What causes the 'No suitable driver' SQLException?
This error usually occurs during a call to DriverManager.getConnection(). The
cause can be failing to load the appropriate JDBC driver before calling getConnection(),
or specifying an invalid JDBC URL that isn't recognized by your JDBC driver.
If you're using a trial version, you will get "No suitable driver"
SQLException, and "Evaluation period over" after using about 30 days.
The HXTT DBF driver's name is com.hxtt.sql.dbf.DBFDriver, and its JDBC URL:
Embedded:
jdbc:DBF:[//]/[DatabasePath][?prop1=value1[&prop2=value2]] (You can omit that "//" characters sometimes)
For example:
"jdbc:DBF:/."
"jdbc:DBF:/c:/data"
"jdbc:DBF:////usr/data" for unix or linux:
"jdbc:DBF:/./data"
Access by DBF Server: Skip it if you don't use TCP, RMI or JINI.
jdbc:DBF://host:port/[DatabasePath]
For example: "jdbc:DBF://domain.com:3099/c:/data" if one DBFServer is run on the 3099 port of domain.com
4. Can I setup only one datasource to access four servers for my Cold Fusion?
You need to use a free JDBC url, "jdbc:DBF:/" or "jdbc:DBF:///".
Then you can use some full UNC path names in SQL to visit your four servers.
For instance:
select * from \\amd2500\e$\dbfiles\test; select * from "\\amd2500\d$\dbfiles".test; select * from ".".test;
5. How to setup DBF url on the Novell Server?
DBF driver can run on Novell server. You can use directly access
or DBFServer to visit your data on Novell server. If your DBF
files is at sys:/java/yourdata, the direct URL should be:
jdbc:DBF:///sys:/java/yourdata
or
jdbc:DBF:////java/yourdata
6. I got "java.io.IOException: Permission denied" sometimes for
my SELECT query.
Please figure out what directory Java's java.io.tmpdir system property points
to, and make sure that directory is writable by the user that runs your Java
applications, otherwise you should set tmpdir properity in Connection properity
to a writable directory. tmpdir properity indicates whether set a temp directory,
Default: the value of JVM's "java.io.tmpdir" property. If that value
is incorrect, uing the directory of JDBC url.
7. When I used jdbc:DBF:/<DatabasePath>, the connection's
catalog was empty. "create schema if not exists DBFfiles".
What is Schema?
DBF's catalog is always empty. You can use schema to query subdirectory.
Schema means a directory, which contains some DBF files.
8. Can HXTT DBF support JDK 1.0.2?
Yeah. You need to download JDBC 1.22 from the Sun's JDBC download page and add
JDBC1.22 into JDK 1.0.2. HXTT DBF hasn't be tested on JDK1.0.X since we have
not received such a complement request from our users. If you meet any problem,
please let us know.
1. I already configured the .java.policy for my applet, but I continue with
problems of "acess denied".
For instance, you're using "jdbc:DBF:/C:/test", and grant
codeBase "file:/C:/test" in your policy file, but your applet is running
from "D:\sample\CargaStatApplet.html". You should grant codeBase "file:/D:/sample",
not "file:/c:/test".
2. http://localhost:8080/jdbcapplet.html, the applet started but returns
a Classnotfound com.hxtt.sql.dbf.DBFDriver error in the gui list.
Please add a codebase tag. For instance, "<applet code="jdbcapplet.class"
codebase="DBF_Remote_Access_JDBC20.jar"></applet>".
The DBF_Remote_Access_JDBC20.jar should be at the same directory of
jdbcapplet.html.
3. I put a DBF file in the Tomcat root directory and want to access
it from the client browser.
It seems that you wish to access remotely your database by applet. You need
to use "java com.hxtt.sql.admin.Admin" to run a "jdbc:DBF://yourhostname:yourport/c:\apache~1.0\webapps\root\"
DBFServer on your host. Then your applet should use
"jdbc:DBF://yourhostname:yourport/c:\apache~1.0\webapps\root\".
1. The data directory is not in the IBM machine where the Java program should
run, but instead those DBF files are in another machine with Windows
operating system.
com.hxtt.sql.admin.Admin provides a GUI manger for DBF server. For instance, you wish to provide JDBC3.0 remote data access. Please use "java -cp yourdirectrory/DBF_JDBC30.jar
com.hxtt.sql.admin.Admin" to start GUI manager, and add a url setting of
'"jdbc:DBF://10.32.90.48:" + 8029 +"/"+databaseDirectory'
on your host of 10.32.90.48(just an IP sample), then click Start button. Third,
you can use 'String url =
"jdbc:DBF://10.32.90.48:" + 8029 +"/"+databaseDirectory;'
to visit your DBF database from your IBM machine. If you're running
that GUI manager on "yourNT.com" host to visit "c:/database"
directory, you can use "jdbc:DBF://yourNT.com:8029/c:/database"
on your web application. jdbc:DBF://yourNT.com:8029/c:/database?user=oneuser&password=onepassword can provide a simply user/password verification for remote access. If you wish to write a secure DBF server
for some sensitive information, embedded encrypt/decrypt functions can help
you.
2. How to remote access DBF data without DBFServer?
You can share your remote directory which contains your data files, then map
it to a local driver.
For Windows: You can connect remote DBF database
by sharing the directory and map it to local drive.
For Linux: You can use mounting. One user uses Samba to maped NTFS partitions
in Linux servers, and DBF driver works normally like mapping any mount
point in Linux.
For Novell: You can map NCP directory as driver or mount NCP directory.
3. I can't get the com.hxtt.sql.admin.Admin runnig for internet --> intranet
HXTT DBF supports port mapping and NAT route. Let HXTT DBF listening a port on the database server, and modify your route table or NAT table to map an external port to that internal port. You can use "start java -Djava.security.policy=policy com.hxtt.sql.admin.Admin"
to start GUI manager. You should add a remote url, for instance, jdbc:DBF://localhost:8029/d:/dbffiles,
and click Start button to start that server. Then on your internet client side, you can use jdbc:DBF://externalIP:8029/d:/dbffiles to access your intranet host. externalIP means an external IP or domain name address of your gateway or database server.
BTW, except for TCPServer protocol, HXTT DBF can use also RMIServer protocol. For instance, you have used "start rmiregistry 1099 -J-Djava.security.policy=yourPolicyFile" to startup your rmi service. Then you can use jdbc:DBF://localhost:1099/d:/dbffiles?serverType=RMIServer to let HXTT DBF bind remote service in registry. The key is use "java -Djava.security.policy=policy -Djava.rmi.server.hostname=externalIP com.hxtt.sql.admin.Admin RMISERVER 8029" to start your server. RMIServer protocol is slower much than the default TCPServer protcol.
4. I would like to start a server (TCP) for remote connections from our application, instead of DBAdmin. I need to be able to programmatically tell the application which profile to start.
In com.hxtt.sql.admin.Admin, you can use:
public void startServer(String serverName)throws SQLException
public void stopServer(String serverName)throws SQLException
try {
com.hxtt.sql.admin.Admin admin = new com.hxtt.sql.admin.Admin();
admin.show();
admin.startServer("test1");
admin.stopServer("test1");
admin.stopServer("test4");
}
catch (SQLException e) {
System.out.println(e.getMessage());
}
5. Is there a way to specify a file path in the url that will connect to
a mapped drive in Windows 2000. ie drive \\gomer\pyle\db which is mapped to
f drive on the server.
DBF driver can work with mapped driver, and you should use "jdbc:DBF:/f:"
to access your data.
6. When I click Start button to start a remote service, I get a security
excaption: access denied (java.net.SocketPermission 127.0.0.1:8029 connect,resolve)
You have to enable java.net.SocketPermission right in your policy file if you
run a DBF server. Please read
file:///yourdriver|/jdk1.2/docs/guide/security/PolicyFiles.html for more
information about policy file. It is unnecessary to know the specific content
of a policy file, since you can use policy tool to create and maintain your
policy files. Please read
file:///yourdriver|/jdk1.2/docs/tooldocs/win32/policytool.html for policy tool.
7. How to start remote service as MS Windows service and Linux Daemon?
Please download the latest HXTT DBF Package and save this package to disk. In linux,we assume that you save it to /jdbclib directory.
In windows,we assume it is c:/ .
You should have built the database server configuration by com.hxtt.sql.admin.Admin program. It will create a file named
urlconfig.properties which locate on the user home directory.
For example,in linux,you build the database server configuration in root user,the urlconfig.properties will located
at /root directory if the root's home directory is /root;in windows,it will be the C:\Documents and Settings\Administrator.
You should copy the file to other directory for the service program maybe not access the file.In linux,we assume you copy it
to /jdbclib;in windows,we assume it is c:/.
In windows,you can use JavaService.exe(Here to download) to register a window service to start the servers.
Here is a simple bat file to tell you how to register a service,you should change some options
accord your enviromnent.After you download these two files ,you can run the bat file to register and start the
service at the Control Panel.
In linux,you can use jsvc(Here to download) as a daemon to start the servers for remote connection.
1.You should download the Apache common daemons package(Here to download).
We assume that you save this two files to /commondaemon directory.
2.please run the follows command to enable the exec file property.
chmod +x /commondaemon/jsvc
Attention,the jsvc program has tested at RedHat 9.0 and Sun Open Desktop System 1.0.If it don't work at
your enviroment,please download the jsvc source and make a binary program or tell us your environment.
3.run the follows command to know the default run level of your machine.
cat /etc/inittab | grep :initdefault
it's result will be as follows: id:3:initdefault
or
runlevel
it's result will be as follows:N 3
In common,the default run level should be 3 or 5.
4.Please download the hxttjsvcserv script to save it to /etc/init.d directory and run the follows command to enable the file exec-able bit mask .
chmod +x /etc/init.d/hxttjsvcserv
Attension ,if you don't put HXTT DBF Package to /jdbclib directory or jsvc and commons-daemon.jar to /commondaemon directory,you should modify the
hxttjsvcserv file to fit your configuration.
BTW,the default user run this service is root,maybe you should changed it to another low right user.Please see the dbfjsvcserv for more detail
information.
5.cd /etc/rcx.d (x is the run level,in some os,the rcx.d is not directly located in /etc directory,you can use find . -name rcx.d to find where is it)
At first you should list all the file for find the new service's running sequence number;
run the command
ls
You will see some files which starts with K or S,for example,S99local and K99local.
S99local is the run script file when start this machine.
K99local is the stop script file when shut down this machine.
local is the service name.K represent kill and S represent the start.
This two files all are a file linked to /etc/init.d/local.This is,when starting machine,OS will run local
script with start parameter and when stopping with stop parameter.
99 is the run sequence number when start this machine.
For example,httpd service will start before this local service and stop after the local service for its start
script file name is S15httpd and end script file name is K15httpd.
Find the max running sequence number,in my machine,it is 99,so the new service's running sequence number will be 100.
run the command to build this two file.
ln -s /etc/init.d/hxttjsvcserv S100hxttjsvcserv
ln -s /etc/init.d/hxttjsvcserv K100hxttjsvcserv
now you can run /etc/init.d/hxttjsvcserv start to start the service or reboot your machine to test if this service can auto start.
8. How to start remote control when DBFServer is running as Windows service or Linux Daemon?
You can use "java com.hxtt.sql.admin.Admin TCPCLIENT [host:]port [remoteControlPassword]" to start your remote control.
1. I need to use tables stored in a subdirectory.
table-name: [schemas.] tableName [@catalog]
For instance, you have many DBF files on c:\data. You can use "jdbc:DBF:/c:/data"
as JDBC url. Then you can use "select * from subdirectory1.table1"
to visit table1 file at subdirectory1. For instance, "select tableAlias.*
from "sales/2004/04".sale as tableAlias", "select * from
sale@"sales/2004/04"", and "select * from "2004/04".sale@sales"
can access sale table at "c:\data\sales\2004\04".
2. I can't use "select RIGHT from deldob"
RIGHT is a reserved SQL keyword. "variableName", [variableName] or
{v 'variableName'} is used to quote those columns which use reserved keyword,
so that you should use "RIGHT" or {v 'RIGHT'} to quote the RIGHT field,
for instance, select {v 'RIGHT'},'other' from states where "RIGHT"=32.
HXTT DBF supports using DATE, TIME, TIMESTAMP, GROUP, ORDER, KEY, DESC, UPDATE
directly in SQL, although they're reserved words too.
3. Can I get an example on how to do a query involving a boolean value.
eg. " Select * from tableName where exported = true", where exported
is a boolean column in a DBF file.
Supports. You can use "select * from tableName where exported" too.
All of NOT, AND, and OR operation are supported.
4. How to specify dates?
Please use SQL Escape Syntax, a date is specified in a JDBC SQL statement with
the syntax {d `yyyy-mm-dd'} where yyyy-mm-dd provides the year, month, and date,
e.g. 1996-02-28. There are analogous escape clauses for TIME and TIMESTAMP type:
{t `hh:mm:ss'} and {ts `yyyy-mm-dd hh:mm:ss.f...'}. The fractional seconds (.f...)
portion of the TIMESTAMP can be omitted. For instance,{d '1999-11-01'} and {ts
'3999-03-24 00:59:23.22222'}. You can use PreparedStatement.setDate to set date
columns too.
6. Can {d '2999-11-21'}={ts '2999-11-21 23:22:20.3335'} and {t '23:22:20'}={ts
'1999-01-01 23:22:20.333'}?
Supports.
7. I think this one is for use functions {fn abs(TEST.int1)}
You can use abs(TEST.int1) too. HXTT DBF supports more than 180 functions.
8. Update table_name set (fieldname1=X, fieldname2=X2, ....) where primary_index='blah'
throws a parse exception.
You should use "update table_name set fieldname1=X, fieldname2=X2, ....
where primary_index='blah'".
9. How to delete all deleted records permanently?
"TRUNCATE TABLE [IF EXISTS] table-name" and "delete from tableName
where true" will zap database.
"pack table tableName" will pack database.
"update tableName set _DeletedFlag=false[, column-identifier
= {expression | NULL}]... [WHERE search-condition]" will unmarks records
marked for deletion in the selected table.
10. How to see those deleted records without pack?
You can use DeletesAreVisible property to indicate whether the resultSet include
deleted records. Default:false
properties.setProperty("DeletesAreVisible","true");//Includes
deleted records.
Connection con = DriverManager.getConnection(url,properties);
ResultSet rs=stmt.executeQuery(sql);
Then you can use "ResultSet.rowDeleted()" to see whether a row is
a deleted row.
If you set DeletesAreVisible to true, you can use the sql sample code to query
data:
select deleted(),* from test
select * from test where not deleted()
select deleted(),* from test where deleted()
select * from test where deleted()
select sum(int1),max(dec1),min(double1) from test where deleted()
Now the limitation is that DBF driver will ignore all unindexed deleted rows when it uses index files to quicken its query since DBF driver never index the deleted data into index files.
11. I'm searching for a JAVA tool that can add a field to dbase-table. ALTER
TABLE Add Field ...
For instance, alter table Backup_of_UN1FIN_PreSort drop UN1FIN_PRES;
alter table Backup_of_UN1FIN_PreSort add UN1FIN_DM numeric(12,3),add UN1FIN_CAT
varchar(20), add UN1FIN_DENS varchar(10);.
1. How to rebuilding index in case of corrupted index?
REINDEX {ALL | indexFileName[,indexfileName2,...]} ON table-name
2. I receive 1 record back, however there should be 8 records returned.My
SQL is "SELECT * FROM Schshift@brian WHERE PSCHED='0001092478'"
You should have a UNIQUE index restriction on your PSCHED column in your index
file. You should use "CREATE INDEX PSCHED on Schshift (PSCHED)", not
"CREATE INDEX PSCHED on Schshift (PSCHED UNIQUE)". Then you can get
all ten records. DBF driver will use index to speed up the query which
contains some index expressions.
3. We tried to set a PRIMARY KEY constraint with: create unique index PROVA
on PROVA (COD)
You should try "CREATE INDEX prova ON prova (cod PRIMARY KEY).
4. I have a table that lists an index using: STR(ClassLink,4,0)+STR(StuLink,5,0)
as the column_name. I want to join it to another table that has an index that
uses the same columns... What should the join statement look like in order to
take advantage of the indexes?
For instance, you can use "select * from ACLS3295,AGRD3295 where STR(ACLS3295.ClassLink,4,0)+STR(ACLS3295.StuLink,5,0)='1234abcde'
and STR(AGRD3295.ClassLink,4,0)+STR(AGRD3295.StuLink,5,0)='5678abcde'",
or "select * from ACLS3295 as a,AGRD3295 as b where STR(a.ClassLink,4,0)+STR(a.StuLink,5,0)='1234abcde'
and STR(b.ClassLink,4,0)+STR(b.StuLink,5,0)='5678abcde'".
5. How can I specify the indexes which have to be used for clipper files?
Does
it recognize a cgp files? (as used by codebase, this file just lists the
names of the index files).
Clipper's CGP file has been supported.
6. The ntx can't be updated when I update the DBF file.
DBF driver will use index to speed up the query which contains some index expressions.
Clipper's CGP file has been supported. You can use a text editor to browse and
modify CGP file. If a table has a structural .CDX or .IDX file, the file is
opened automatically when the table is opened. You have to run the DBFIndex
once to supply the necessary index file information for the nonstructural index
file which hasn't the same file name as its table name and is created by other
xbase applications. Then DBF will create a special table.CGP file and maintain
the index files automatically. You can use "java com.hxtt.sql.dbf.DBFIndex"
to find out how to supply existent index files. Useage: java com.hxtt.sql.dbf.DBFIndex
dbfTablePath tableName indexFileFullName1 [indexFileFullName2 ...]. For instance,
you have a legacy 'product.dbf' file with prodname.idx, prodprice.idx and orderdate.idx
on "c:\proudct\data" directory, you can run "java com.hxtt.sql.dbf.DBFIndex
c:\proudct\data product prodname.idx prodprice.idx orderdate.idx" once.
Then DBF will create a special product.CGP file and maintain the index files
automatically. All operations of update, insert and delete will update the .IDX
files that are related to the DBF file. DBF driver doesn't index all deleted
records.
7. Failed to use "create index No, VALUES of 1216.cdx 'product' No
UNIQUE, VALUES)"
You should use "create index No, 'VALUES' of '1216.cdx' on '1216.dbf'(product,
{ v 'VALUES'})" since VALUES is a reserved word and 1216 is a number.
1. What is the most efficient methodto insert records in a table, to use
an updatable RecordSet or to use a PreparedStatement?
PreparedStatement is smally quicker than updatable RecordSet. An updatable RecordSet
is quicker than PreparedStatement if you insert into more than 200 columns with
constant values. It can only cope with constant values. PreparedStatement can
cope with complicated expressions so that you can insert timestamp, function,
ResultSet, and so on.
One user reported that he uses DBF to transfer daily more
than 500MB data between two oracle databases and Clipper's databse.
2. "select count(*) from table" are worked a long time for large
tables.
You should use "select reccount() from table" to get the number of
records. Count(*) sums always up all records except deleted row.
3. Are there any data row count, data volume, memory minimums, maximums
imposed when using the HXTT DBF?
No limitation. The HXTT DBF supports to join query big databases with DISTINCT,
GROUP BY, and ORDER BY.
1. Does HXTT DBF support multi-user access?
The HXTT DBF supports multi-user access, record lock, and table lock.
2. When I execute "delete from BATCH where deleted()", java.sql.SQLException:
Timeout Interrupted Exception: beyond 1000 msecs.
It seems that you're using DBF in a multi-process. Your "where
deleted()" clause requires locking BATCH table for pack, but maybe one
process is using that table, so that it has to give up PACK operation in 1000
msecs tiemout.
3. Is there any way to lock/unlock record programatically.
We have provided a _LockFlag_ virtual column as row lock flag. You can use "select
_LockFlag_,* from yourTable" to get an Updatable ResultSet.
boolean ResultSet.setBoolean("_LockFlag_",true)//Lock the current row.
boolean ResultSet.setBoolean("_LockFlag_",false);//Unlock the current row.
boolean ResultSet.getBoolean("_LockFlag_")//indicates whether the current row
has been locked by other process or application.
If ResultSet.close() is called, all pending record locks will be released automatically.
"update yourTable set _LockFlag_=true where condition", and "update yourTable
set _LockFlag_=false where condition" can lock/unlock records too, but you have
to take care of every record lock. If you need more inforatmion on how to utilize
that function, please email us.
4. Does that Xbase compatible lock work also with Linux ? I mean a DLL under
Linux
Yeah. DBF provides Xbase compatible lock work for Windows, Linux, and OS2.
5. When my program is inserting a record to DataBase using your DBF Driver,
another C program reads the data from the DBF file, Can it read the record data
that my program is inserting? In other words, Does the driver use records level
lock?
DBF driver supports Xbase's record lock. You can download the JNIFile.dll( or
libJNIFile.so) from here,
and add the JNIFile.dll ( or libJNIFile.so) into your library path. For instance,
your JNIFIle.dll ( or libJNIFile.so) is on lib directory, you can use 'java
-classpath lib/DBF_JDBC30.jar:./classes -Djava.library.path=lib testSQL
"jdbc:dbf:/." sql.txt'.
If you wish to use Xbase compatible lock under Tomcat, you should copy the JNIFile library file to one a directory of java.library.path java enviroment parameter. You can get the value of java.library.path java enviroment parameter by System.getProperty("java.library.path") code ,or in windows, which is $PATH, in solaris or linux, which is $LD_LIBRARY_PATH, in hp-unix, which is SHLIB_PATH.
Then please use lockType property to enable Xbase compatible lock:
Properities prop=new Properities();
prop.setProperty( "lockType", "CLIPPER" );//DBASE, DBASE4BDE, DBASEIII, DBASEIV, CLIPPER, CLIPPER5.3, SoftC, FOXBASE, FOXPRO, FOXPRO4BDE, and VFP.
Connection conn=DriverManager.getConnection( url,prop );
DBF driver will lock and unlock automatically when you execute SQL through DBF
driver. ROWLOCKED([nWorkArea | cTableAlias]) function indicates whether the
current row has been locked by other process or application. For instance, "SELECT
rowlocked() from test where recno()=1000", and "SELECT rowlocked('test'),*
from test".
6. There are some errors when I use com.hxtt.sql.dbf.DBFDriver to access
a DBF file which may be modified by other application at the same time.
DBF
driver can see other application's lock, and lock only records when it read/updates
them. Please use lock property to enable Xbase compatible lock:
Properities prop=new Properities();
prop.setProperty( "lockType", "VFP" );//DBASE, DBASE4BDE, DBASEIII, DBASEIV, CLIPPER, CLIPPER5.3, FOXBASE, FOXPRO, FOXPRO4BDE, and VFP.
Connection conn=DriverManager.getConnection( url,prop );
You need download the JNIFile.dll ( or libJNIFile.so) from here, and add the JNIFile.dll ( or libJNIFile.so) into your library path. DBF driver will lock and unlock automatically when you execute SQL through DBF driver.
Internationalization Questions
1. Can the HXTT DBF support Czech MS - DOS 895?
The HXTT DBF supports all codepage, multilingual collation sequence, and unicode character set. Cp895(Czech MS - DOS 895), Cp620(Polish MS - DOS 620) and Mazovia are extra supported although JVM doesn't support those.
2. Do you have a solution for character translation to the right encoding?
The HXTT DBF supports CharacterEncoding. Please use charSet property.
//Default: null
//You can find a Supported Encodings list of files:///yourdriver/jdk1.2/docs/guide/internat/encoding.doc.html
//Extra supports:
// Cp895 is supported by HXTT DBF driver. //Czech MS - DOS 895
// Cp620 is supported by HXTT DBF driver. //Polish MS - DOS 620
// Mazovia is supported by HXTT DBF driver. //Polish
Properties properties=new Properties();
properties.setProperty("charSet","sv_SE");
Connection con = DriverManager.getConnection(url,properties);
3. While reading encrypted data in a DBF file using u'r parser
in java. The data retreived is different from the data in the DBF
file, certain characters are read as ? marks.( the encryption is done using
ASCII values ).
You can use ResultSet.getBytes(int columnIndex), not ResultSet.getString(int
columnIndex) and ResultSet.getObject(int columnIndex), to get your encrypted
data, since your encrypted data is binary stream.
4. When they insert accented characters, it comes out different at the Java
end. There seem to be some character set conversion problems. Is there a way
to solve that?
You can use ResultSet.getBytes() and ResultSet.setBytes() to avoid CharacterEncoding.
5. The charSet's default should be "auto", which means that it
will extract the codepage from the DBF header field?
The default is "null". DBF uses only charSet property when the codepage
flag in DBF header field is empty.
6. My DBF file has Chinese column names.
DBF driver supports Chinese, ant other two bytes languages.
1. How to set up HXTT DBF with Tomcat4.1 as PoolableConnection?
This sample shows three PoolableConnections ways through Database Connection
Pool (DBCP) Configurations and JNDI Resources( You should read JNDI
Datasource HOW-TO and JNDI
Resources HOW-TO also.):
In server.xml:
<Context path="" docBase="ROOT" debug="5" reloadable="true" crossContext="true">
<Resource name="jdbc/testDBFPool1" auth="Container" type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/testDBFPool1">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>50</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>10</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>10000</value>
</parameter>
<parameter>
<name>username</name>
<value></value>
</parameter>
<parameter>
<name>password</name>
<value></value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>com.hxtt.sql.dbf.DBFDriver</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc:DBF:///d:/DBFfiles</value>
</parameter>
</ResourceParams>
<Resource name="jdbc/testDBFPool2" auth="Container" type="com.hxtt.sql.HxttConnectionPoolDataSource"/>
<ResourceParams name="jdbc/testDBFPool2">
<parameter>
<name>factory</name>
<value>org.apache.naming.factory.BeanFactory</value>
</parameter>
<parameter>
<name>subprotocol</name>
<value>DBF</value>
</parameter>
<parameter><name>username</name><value></value></parameter>
<parameter><name>password</name><value></value></parameter>
<parameter><name>host</name><value></value></parameter>
<parameter><name>port</name><value>8029</value></parameter>
<parameter>
<name>database</name>
<value>d:/DBFfiles</value>
</parameter>
</ResourceParams>
<Resource name="jdbc/testDBFPool3" auth="Container" type="com.hxtt.sql.HxttConnectionPoolDataSource"/>
<ResourceParams name="jdbc/testDBFPool3">
<parameter>
<name>factory</name>
<value>com.hxtt.sql.HxttObjectFactory</value>
</parameter>
<parameter>
<name>subprotocol</name>
<value>DBF</value>
</parameter>
<parameter><name>username</name><value></value></parameter>
<parameter><name>password</name><value></value></parameter>
<parameter><name>host</name><value></value></parameter>
<parameter><name>port</name><value>8029</value></parameter>
<parameter>
<name>database</name>
<value>d:/DBFfiles</value>
</parameter>
</ResourceParams>
</Context>
Then you can use the below code to test those PoolableConnections:
Context initContext = new InitialContext();
Context envContext = (Context)initContext.lookup("java:/comp/env");
DataSource ds1 = (DataSource)envContext.lookup("jdbc/testDBFPool1");
Connection conn1 = ds1.getConnection();
out.println("testDBFPool1 OK:)<br/>");
Statement stmt1 = conn1.createStatement();
ResultSet rs1 = stmt1.executeQuery("select * from test");
if(rs1.next())
out.println(rs1.getString(1)+":)<br/>");
rs1.close();
stmt1.close();
conn1.close();
DataSource ds2 = (DataSource)envContext.lookup("jdbc/testDBFPool2");
Connection conn2 = ds2.getConnection();
out.println("testDBFPool2 OK:)<br/>");
Statement stmt2 = conn2.createStatement();
ResultSet rs2 = stmt2.executeQuery("select * from test");
if(rs2.next())
out.println(rs2.getString(1)+":)<br/>");
rs2.close();
stmt2.close();
conn2.close();
DataSource ds3 = (DataSource)envContext.lookup("jdbc/testDBFPool3");
Connection conn3 = ds3.getConnection();
out.println("testDBFPool3 OK:)<br/>");
Statement stmt3 = conn3.createStatement();
ResultSet rs3 = stmt3.executeQuery("select * from test");
if(rs3.next())
out.println(rs3.getString(1)+":)<br/>");
rs3.close();
stmt3.close();
conn3.close();
If you use org.apache.commons.dbcp.BasicDataSource, but get "Cannot create PoolableConnectionFactory" Error, you should check your commons-pool-1.x.jar and commons-dbcp-1.*.jar file in $TOMCAT/common/lib directory to see whether two files have the same version. DBCP v1.2 requires Pool v1.2 so that you should update Pool v1.1 from the tomcat website.
If you wish to add more Connection properity, you should use connectionProperties, for instance:
<parameter>
<name>connectionProperties</name>
<value>charSet=Cp737</value>
</parameter>
2. How to set up HXTT DBF with vqServer 1.9.55 as web server?
The key is to use an absolute path as Java libraries' location, and restart
vqServer after modified Java libraries.
For instance, your vqServer is installed at C:\vqServer\.
1. Please use http://yourhost:9090/ to visit your administration server.
2. Click on Java libraries in the vqServer control centre menu (http://yourhost:9090/admin?action=libraries&serial=14)
3 Click New library (http://yourhost:9090/admin?lib=New_library&action=edit)
4. Enter C:\vqServer\classes\DBF_JDBC20.jar as location value, DBF
Driver as Description value, then click OK button.
5. Please copy DBF_JDBC20.jar into C:\vqServer\classes directory.
6. Please copy ex01.class into C:\vqServer\servlets\servlets
7. Stop and restart vqServer
8. Please use http://yourhost/servlet/yourServlets to get your result.
3. How to set up HXTT DBF with Coldfusion MX 6.1 Application Server?
For instance,your Coldfusion MX is installed at C:\CFusionMX\, and wish to use
DBF_JDBC30.jar.
1. Please copy DBF_JDBC30.jar into C:/CFusionMX/wwwroot/WEB-INF/classes/.
2. Use http://yourhost:8500/CFIDE/administrator/index.cfm to enter the CFMX
Administrator.
3. Go to the "Java and JVM" of Server Settings, http://yourhost:8500/CFIDE/administrator/settings/jvm.cfm
page, and enter the full path, C:/CFusionMX/wwwroot/WEB-INF/classes/DBF_JDBC30.jar,
in the Class Path. Then, click "Submit Changes".
4. Restart the CFMX Service.
5. Please go back to the administrator page, and go to the "Data Sources"
of Data & Services, http://yourhost:8500/CFIDE/administrator/datasources/index.cfm
page, and enter the name for the new datasource, for instance "DBFTest",
and select "Other" for the driver. Then Click "Add".
6. Enter the datasource information. JDBC URL is always in the format jdbc:DBF:[//[host:port]]/[DatabasePath],
for instance jdbc:DBF:/c:/data. Driver class is always com.hxtt.sql.dbf.DBFDriver.
Driver name is used to identify the driver in the datasources view, and you
can use DBF. Username and password are not required. They can also
be specified in the cfquery tag (but datasource verification will fail if you
don't enter them). Description is not required.
7. If you wish to set more connection properties, please click "Show Advanced Setting" button, then in the textbox for "Connection String", you can input "delayedClose=15&maxCacheSize=6144&lockTimeout=2000" (three properites are just a demo, not necessary).
8. Lastly, please press "Submit" to finalize the entered data.
9. You can find edit.cfm and edit_action.cfm sample in demo pacakge.
4. HXTT DBF with alexandria sw and tanuki sw wrapper on Windows 2000 and Windows XP Pro does not work on mapped drives.
The service built by the tanuki sw wrapper can not access the share directory
at other machine by default. But you can do as follows to solve this problem:
1. Right click the service built by the tanuki sw wrapper in service manager,
and click the property menu.
2. On the open window,select the login tab, click this account radio box, and
click the browse button.
3. Select the administrator account(it seems that you should select the administrator
account), input the correct password in the password textbox and confirm password
textbox.
4. Restart this service, you can find this service can access the share directory
at other machine.
5. How to resolve 'DataSet has no unique row identifiers.' issue in JBuilder's
QueryDataSet?
You can use _rowid_, a virtual column to avoid that issue, For instance:
//...
queryDataSet = new QueryDataSet();
//...
queryDataSet.setMetaDataUpdate(MetaDataUpdate.ALL-MetaDataUpdate.ROWID-MetaDataUpdate.TABLENAME);
queryDataSet.setQuery(new QueryDescriptor(database, "select _rowid_,* from test", null, true,
Load.ALL));
queryDataSet.open();
queryDataSet.setTableName("test");
queryDataSet.setRowId("_rowid_", true);
//...
1. Does DBF support Clipper?
HXTT DBF supports dbase, Visual DBASE, SIx Driver, Clipper, Foxbase, Foxpro and VFP. It
supports IDX, CDX, NDX, MDX, NTX, and CGP. It supports DBF, DBC, DCT, DCX, DBT,
FPT, SMT, and user-defined extensions. It supports Multi-user
Access and Xbase applications' compatible lock.
2. VFP's max column number is 255, and max column name length is 10.
DBF driver supports more than 8000 column, and the max length of clolumn name
is 11. The max column size of varchar is 65535. A DB2K table supports at most
32 characters as column name.
3. I can create a dbf file with 3 char column and other programs can read
it; however, I ran into trouble with I add an integer column.
DBF driver supports many Xbase format files, such as Clipper, DbaseIII, Dbase
IV, Visual Dbase 5, Foxbase2.1, and VFP. It's normal that your other program
can't read integer column, since integer column doesn't be supported by some
Xbase programs. You can try decimal or numeric column.
4. How can I connect to a VFP container (e.g. <dbase>.DBC)? Specifically,
what URL
format is needed?
DBF supports VFP container(a kind of database file). For insance, your dbcsample1.DBC
is at c:/dbc". You can use "jdbc:dbf:/c:/dbc" as URL, then you
can use "select yourColumn from dbcsample1.yourTable". If you have
defined a long column name in your dbc, DBF will support that long column name
automatically in your sql. For instance, you have a table1.dbf in dbc1.dbc cotainer.
You can use "select * from table1", or "select * from dbc1.table1".
If a "column1" column of table1.dbf has a long "goodColumnName"
definition in dbc1.dbc container. You can use "select column1 from table1",
or "select goodColumnName from table1".
5. I would like that you help me with a driver for connect databases .dbc
of Visual Fox Pro.
DBF supports DBC, DCT, and DCX files. You can use "select yourColumn from
yourDbc.yourTable" . If you wish to do more for your DBC files, you can
uses "OtherExtensions" property (Default:false) to indicate whether
DBF driver supports other extensions except for '.DBF'. Please use "properties.setProperty("OtherExtensions","true");"
for "DriverManager.getConnection(url,properties);". Then you can use
"select * from 'yourdatabase.dbc'", "update 'your.dbc' set ...",
and so on.
6. I have a program that works with data bases of dBase, but these have
extension .DEC and the index .INC instead of .DBF and .NTX.
Please use "properties.setProperty("DefaultExtension","DEC");properties.setProperty("DefaultNTXExtension","INC");"
for "DriverManager.getConnection(url,properties);".
7. One new problem that I have is how to access dbf files that have a numric
exetention. My files are not a.dbf or b.dbf they are like a.001 b.017.
Please use "properties.setProperty("OtherExtensions","true");"
for "DriverManager.getConnection(url,properties);". Then you can use
"select * from 'a.001'", "update 'b.017' set ...", and so
on.
8. Is there a way to create the table so that FoxPro 2.5 (DOS) can open
the table.
When create connection by using "DriverManager.getConnection(url, properties);",
you can use a "Version Number" property (default value:30) to hint
DBF the format. DBF will try to use that version when it creates new table.
For instance:
properties.setProperty("Version Number", "03");
The Version Number value list:
02 FoxBase
03 dBASE III
04 dBASE IV
05 dBASE V
F5 FoxPro
30 Visual FoxPro
DB2K dbase2k, a variable of 04
DBF won't change the format of dbase 4, 5, or 2k when it edits those tables.
9. How to works simultaneously with BDE's DBASE and FOXFRO driver.
First of all, please use BDE Adminstrator to set LOCAL SHARE =TRUE on the INIT Definitions of System page in the BDE Configuration utility.
Secondly, you need to download the JNIFile.dll( or
libJNIFile.so) from here,
and add the JNIFile.dll ( or libJNIFile.so) into your library path.
Then, plese use lockType property to enable Xbase compatible lock:
Properities prop=new Properities();
prop.setProperty( "lockType", "DBASE4BDE" );//DBASE4BDE or FOXPRO4BDE.
Connection conn=DriverManager.getConnection( url,prop );