FAQ for HXTT Text (CSV) Packages of type 4 JDBC Driver for raw data, flat text, CSV file, TSV file, fixed-length, and variable-length binary file
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 Text (CSV) 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 Text (CSV) Package, Embedded Package,
and Remote Access Package? Can I get some sample code to use the HXTT Text (CSV)?
The HXTT Text (CSV) supports Embedded and Remote Access. HXTT Text (CSV) Package includes a Database GUI manager. If you're accessing the local data, you can use the HXTT Text (CSV) Package or Embedded Package. If
you're accessing the remote data, you can use the HXTT Text (CSV) 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 Text (CSV) driver's name is com.hxtt.sql.text.TextDriver, and its JDBC URL:
Embedded: jdbc:text:[//]/[DatabasePath][?prop1=value1[;prop2=value2]] (You can omit that "//" characters sometimes) For example: "jdbc:text:/." "jdbc:text:/c:/data" "jdbc:text:////usr/data" for unix or linux: "jdbc:text:/./data" Access by Text Server: Skip it if you don't use TCP, RMI or JINI. jdbc:text://host:port/[DatabasePath] For example: "jdbc:text://domain.com:3099/c:/data" if one TextServer 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:text:/" or "jdbc:text:///".
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 Text url on the Novell Server?
Text driver can run on Novell server. You can use directly access
or TextServer to visit your data on Novell server. If your Text
files is at sys:/java/yourdata, the direct URL should be:
jdbc:text:///sys:/java/yourdata
or
jdbc:text:////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:text:/<DatabasePath>, the connection's
schema was empty. "create catalog if not exists textfiles".
What is Catalog?
Text's schema is always empty. You can use catalog to query subdirectory.
Catalog means a directory, which contains some Text files.
8. Can HXTT Text (CSV) 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 Text (CSV) 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.
9. What kind of text files are you able to read now?
It can read any flat files with a fixed length, variable length or CSV file.
The only key is how to use "CREATE TABLE" to define a table structure. If you're using CSV file, you can access your data without any predefined CREATE TABLE sql.
If you have many prepared table definition sqls, you can set "schemaFile"(standard sql)
and "odbcSchemaFile"(odbc compatible ini file)
properity for connection, then Text driver will load automatically those table
definitions. If you don't know too much about your data file, we can write special
demo code to show how to fetch data from your text file and csv file.
10. In my text file, there is some variable-length field which ends with a special string.
For example,a filed ends with "aaa"
CREATE TABLE IF NOT EXISTS INTRANSIT (office char(6) default 'OFFICE',_StuffedColumn char(1) default ' ',description longvarchar default 'aaa');
or a filed ends with "aaa",another field ends with "\r\n"
CREATE TABLE IF NOT EXISTS INTRANSIT (office char(6) default 'OFFICE',_StuffedColumn char(1) default ' ',description longvarchar default 'aaa',
information longvarchar default '\r\n');
This variable-length file function should support any regular format data.
You must assign the end string to the LONGVARCHAR(LONGVARBINARY) fileds in the table.
If you need more inforatmion on how to utilize that function, please email us.
11. How to use "CREATE TABLE" to define a table structure for
flat file with a fixed length?
For instance, CREATE TABLE if not exists chk (businessmode CHAR(14),"first
name" char(16), "last name" char(18), company char(50), trade
CHAR(35), address char(30), address2 char(30), city char(19), flag char(2),ID
char(7), serialNO numeric(6,0),_StuffedColumn char(2) default '\r\n') will
define a chk table structure, which the total row length is 229 byte. _StuffedColumn
is a special column, which can be used to ignore all noise columns. For instance,
you can use "_StuffedColumn char(8) default 'abceed\r\n'", or '_StuffedColumn
char(8)" to ignore 8 byte information of every row. Default value can be
omitted, or be a expression. The stuffed column of inserted new row will be
set with the default value. BTW, for variable-length file, you shoud use longvarchar
or longvarbinary with default value to indicate its terminator signature.
12. How to use "CREATE TABLE" to define a table structure for
csv file?
For insatnce, CREATE TABLE chk (businessmode CHAR(14),"first name"
char(16),"last name" char(18), company char(50),trade CHAR(35), address
char(30), address2 char(30), city char(19), flag char(2),ID char(7),serialNO
numeric(6,0),_CSV_Separator char(1) default ',', _CSV_Quoter char(1) default
'"',_CSV_Header boolean default false) will define a csv table called
chk.
_CSV_Separator, _CSV_Quoter, _CSV_Header, and _CSV_Comment, are special columns, which can
be used to define a different value from connection properites. _CSV_Separator
specifies a character used to separate the values of the fields. _CSV_Quoter
specifies a character used to quote the string value. _CSV_Header indicates
whether the first record of CSV file to consist of the names of the fields in
the data following. _CSV_Comment specify whether ther'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.
Text supportes to access CSV table without CREATE TABLE too. For instance:
Properties props=new Properties(); props.put("_CSV_Header","true"); Connection con = DriverManager.getConnection(url,props); Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY); ResultSet rs = stmt.executeQuery("select * from pcfull2");
13. Do you have one table definition sql for opening existing tables?
You can use "CREATE TABLE" although your table exists. Then Text driver
will use those table definition to access your data. If you have many prepared
table definition sqls, you can set "schemaFile" properity for connection,
then Text driver will load automatically those table definitions.
14. Is there any way to have the HXTT Text (CSV) driver use the first row of the
text file for setting the column names of the resulted recordset for a SELECT
query?
Text supports to use the first row of CSV file as column name. For instance:
Properties props=new Properties();
props.put("_CSV_Header","true");
Connection con
= DriverManager.getConnection(url,props);
15. How can we skip some header lines from a text file, say with some report header (My Project Budget)?
Text supports to skip 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. For instance:
Properties props=new Properties();
props.put("_CSV_Comment","3");
//Or props.put("_CSV_Comment","Product report for FY 1996\r\n- - - - - - - - - - - -
\r\n\r\n");
Connection con DriverManager.getConnection(url,props);
create table FY1996 (_CSV_Separator char(1) default ',', _CSV_Quoter char(1) default null,_CSV_Header boolean default true,_CSV_Comment longvarchar default 'Product report for FY 1996\r\n- - - - - - - - - - - - \r\n\r\n');
create table FY1996 (_CSV_Separator char(1) default ',', _CSV_Quoter char(1) default null,_CSV_Header boolean default true,_CSV_Comment longvarchar default 3);
16. When I create new tables or opening existing tables,should I use the
whole file name or just the file name without the extensions?
For .bin and .csv, you needn't to quote extentsion. For your CHK.TXT, you have
to use "select * from \"chk.txt\"". Another way is to use
"select * from chk", after using "fileExtension" properity:
Properties properties = new Properties();
properties.setProperty("fileExtension", "txt");//If your
CHK.TXT is a text table
//properties.setProperty("csvfileExtension", "txt");//If
your CHK.TXT is a csv table
Connection con = DriverManager.getConnection(url, properties);
sql="CREATE TABLE if not exists chk (businessmode CHAR(14),...";
Please note that "CREATE TABLE" should use "chk", not "\"chk.txt\"", since you have set the default file extension.
17. When I created a new table, I used "create table \"chk_presort.txt\"
....", the resulting file is actually chk_presort.txt.bin.
It's resulted by the default BIN suffix. You should use:
Properties properties = new Properties();
properties.setProperty("fileExtension", "txt");
Connection con = DriverManager.getConnection(url, properties);
sql="create table chk_presort ...";
18. In my text file, the first and second records have such a huge seperation.
It's resulted by deleted row, since there's not a standard for empty text row.
If you don't delete/update your row frequently, there won't be too much empty
row.
19. I have three csv files which have the same file name and different suffix in the same directory. How to access those files?
For instance, you can use create table "a.txt" (_CSV_Header boolean default true);create table "a.lst" (_CSV_Header boolean default false,
_CSV_Separator char(1) default '\t' );create table "a.csv" (_CSV_Comment longvarchar default 3 );
select * from "a.txt" as atxt,"a.lst" as alst, "a.csv" as acsv
1. I already configured the .java.policy for my applet, but I continue with
problems of "acess denied".
For instance, you're using "jdbc:text:/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.text.TextDriver error in the gui list.
Please add a codebase tag. For instance, "<applet code="jdbcapplet.class"
codebase="Text_Remote_Access_JDBC20.jar"></applet>".
The Text_Remote_Access_JDBC20.jar should be at the same directory of
jdbcapplet.html.
1. The data directory is not in the IBM machine where the Java program should
run, but instead those Text files are in another machine with Windows
operating system.
com.hxtt.sql.admin.Admin provides a GUI manger for Text server. For instance, you wish to provide JDBC3.0 remote data access. Please use "java -cp yourdirectrory/Text_JDBC30.jar
com.hxtt.sql.admin.Admin" to start GUI manager, and add a url setting of
'"jdbc:text://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:text://10.32.90.48:" + 8029 +"/"+databaseDirectory;'
to visit your Text 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:text://yourNT.com:8029/c:/database"
on your web application. jdbc:text://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 Text server
for some sensitive information, embedded encrypt/decrypt functions can help
you.
2. How to remote access Text data without TextServer?
You can share your remote directory which contains your data files, then map
it to a local driver.
For Windows: You can connect remote Text 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 Text 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 Text supports port mapping and NAT route. Let HXTT Text 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:text://localhost:8029/d:/dbffiles,
and click Start button to start that server. Then on your internet client side, you can use jdbc:text://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 Text 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:text://localhost:1099/d:/dbffiles?serverType=RMIServer to let HXTT Text 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) from our application, instead of DBAdmin. I need to be able to programmatically tell the application which profile to start.
Please read Start/Stop Server Programmatically.
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.
Text driver can work with mapped driver, and you should use "jdbc:text:/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 Text 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(Solaris) Daemon?
Please read Run HXTT Text (CSV)Server as Windows Service or Linux(Solaris) Daemon.
8. How to start remote control when TextServer is running as Windows service or Linux(Solaris) 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: [catalog.]tableName
For instance, you have many Text files on c:\data. You can use "jdbc:text:/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" 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 Text (CSV) 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 Text 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 Text (CSV) 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.
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. Text 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'".
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.
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 Text (CSV)?
No limitation. The HXTT Text (CSV) supports to join query big databases with DISTINCT,
GROUP BY, and ORDER BY.
1. Does HXTT Text (CSV) support multi-user access?
The HXTT Text (CSV) 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 Text 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 know it from Set Record Lock Manually.
Internationalization Questions
1. Can the HXTT Text (CSV) support Czech MS - DOS 895?
The HXTT Text (CSV) 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 Text (CSV) 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 Text (CSV) driver. //Czech MS - DOS 895 // Cp620 is supported by HXTT Text (CSV) driver. //Polish MS - DOS 620 // Mazovia is supported by HXTT Text (CSV) driver. //Polish Properties properties=new Properties(); properties.setProperty("charSet","sv_SE"); Connection con = DriverManager.getConnection(url,properties);
3. While reading encrypted data in a Text file using u'r parser
in java. The data retreived is different from the data in the Text
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.
1. How to set up HXTT Text (CSV) 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/testtextPool1" auth="Container" type="javax.sql.DataSource"/> <ResourceParams name="jdbc/testTextPool1"> <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.text.TextDriver</value> </parameter> <parameter> <name>url</name> <value>jdbc:text:///d:/textfiles</value> </parameter> </ResourceParams> <Resource name="jdbc/testTextPool2" auth="Container" type="com.hxtt.sql.HxttConnectionPoolDataSource"/> <ResourceParams name="jdbc/testTextPool2"> <parameter> <name>factory</name> <value>org.apache.naming.factory.BeanFactory</value> </parameter> <parameter> <name>url</name> <value>jdbc:text:///d:/textfiles</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> </ResourceParams> <Resource name="jdbc/testTextPool3" auth="Container" type="com.hxtt.sql.HxttConnectionPoolDataSource"/> <ResourceParams name="jdbc/testTextPool3"> <parameter> <name>factory</name> <value>com.hxtt.sql.HxttObjectFactory</value> </parameter> <parameter> <name>url</name> <value>jdbc:text:///d:/textfiles</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> </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/testTextPool1"); Connection conn1 = ds1.getConnection(); out.println("testTextPool1 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/testTextPool2"); Connection conn2 = ds2.getConnection(); out.println("testTextPool2 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/testTextPool3"); Connection conn3 = ds3.getConnection(); out.println("testTextPool3 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 Text (CSV) 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\Text_JDBC20.jar as location value, Text
Driver as Description value, then click OK button.
5. Please copy Text_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 Text (CSV) with Coldfusion MX 6.1 Application Server?
For instance,your Coldfusion MX is installed at C:\CFusionMX\, and wish to use
Text_JDBC30.jar.
1. Please copy Text_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/Text_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 "TextTest",
and select "Other" for the driver. Then Click "Add".
6. Enter the datasource information. JDBC URL is always in the format jdbc:text:[//[host:port]]/[DatabasePath],
for instance jdbc:text:/c:/data. Driver class is always com.hxtt.sql.text.TextDriver.
Driver name is used to identify the driver in the datasources view, and you
can use Text. 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). Note: Connection String seems abnormal now. You should have to put Connection String into JDBC URL, for instance: jdbc:text:/c:data?delayedClose=15;maxCacheSize=6144;lockTimeout=2000;
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 Text (CSV) 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); //...