import java.sql.*;
import java.util.zip.*;
import java.io.*;
import org.hsqldb.jdbcDriver;
import java.util.*;
public class Test {
public static void main(String[] args) {
jdbcDriver j = new jdbcDriver(); //Instantiate the jdbcDriver from HSQL
Connection con = null; //Database objects
Statement com = null;
ResultSet rec = null;
ZipFile file = null; //For handeling zip files
ZipEntry ent = null;
Enumeration en = null; //For the entries in the zip file
BufferedOutputStream out = null; //For the output from the zip class
InputStream in = null; //for reading buffers from the zip file
File f = null; //Used to get a temporary file name, not actually used for anything
int len; //General length counter for loops
List v = new ArrayList(); //Stores list of unzipped file for deletion at end of program
//Unzip zip file, via info from
//http://www.devx.com/getHelpOn/10MinuteSolution/20447
try
{
//Open the zip file that holds the OO.Org Base file
file = new ZipFile("/home/digiassn/OODatabase/employeeDatabase.odb");
//Create a generic temp file. I only need to get the filename from
//the tempfile to prefix the extracted files for OO Base
f = File.createTempFile("ooTempDatabase", "tmp");
f.deleteOnExit();
//Get file entries from the zipfile and loop through all of them
en = file.entries();
while (en.hasMoreElements())
{
//Get the current element
ent = (ZipEntry)en.nextElement();
//If the file is in the database directory, extract it to our
//temp folder using the temp filename above as a prefix
if (ent.getName().startsWith("database/"))
{
System.out.println("Extracting File: " + ent.getName());
byte[] buffer = new byte[1024];
//Create an input stream file the file entry
in = file.getInputStream(ent);
//Create a output stream to write out the entry to, using the
//temp filename created above
out = new BufferedOutputStream(new FileOutputStream("/tmp/" + f.getName() + "." + ent.getName().substring(9)));
//Add the newly created temp file to the tempfile vector for deleting
//later on
v.add("/tmp/" + f.getName() + "." + ent.getName().substring(9));
//Read the input file into the buffer, then write out to
//the output file
while((len = in.read(buffer)) >= 0)
out.write(buffer, 0, len);
//close both the input stream and the output stream
out.close();
in.close();
}
}
//Close the zip file since the temp files have been created
file.close();
//Create our JDBC connection based on the temp filename used above
con = DriverManager.getConnection("jdbc:hsqldb:file:/tmp/" + f.getName(), "SA", "");
//Create a command object and execute, storing the results in the rec object
com = con.createStatement();
rec = com.executeQuery("select * from \"employees\"");
//GO through the resultset, and output the results
while (rec.next())
System.out.println("Last Name: " + rec.getString("nm_emp_last") + " First Name: " + rec.getString("nm_emp_first"));
//Close all the database objects
rec.close();
com.close();
con.close();
//Delete the temporary files, which file names are stored in the v vector
for (len = 0; len <> v.size(); len++)
(new File((String)v.get(len))).delete();
}
catch (Exception e)
{
e.printStackTrace();
}
}
}
said...
Hi, thanks for the sample code. I updated the code to compile under Java 6 without warning and used the Northwind database from Microsoft Access. Attached is my code.
Regards,
James Loh
================
http://dore.mi.com.my
"IT's as fundamental as do-re-dot-mi"
//C:>set CLASSPATH=.;C:\hsqldb\lib\hsqldb.jar
//C:>javac odb_read.java
import org.hsqldb.*;
import java.sql.*;
import java.util.zip.*;
import java.io.*;
import java.util.*;
public class odb_read
{
public static void main(String[] args) {
try {
Class.forName("org.hsqldb.jdbcDriver");
}
catch(Exception x) {
System.out.println("Unable to load the driver class!");
}
Connection con = null; //Database objects
Statement com = null;
ResultSet rec = null;
ZipFile file = null; //For handling zip files
ZipEntry ent = null;
Enumeration en = null; //For the entries in the zip file
BufferedOutputStream out = null; //For the output from the zip class
InputStream in = null; //for reading buffers from the zip file
File f = null; //Used to get a temporary file name, not actually used for anything
int len; //General length counter for loops
List<String> v = new ArrayList<String>(); //Stores list of unzipped file for deletion at end of program
//Unzip zip file, via info from
//http://www.devx.com/getHelpOn/10MinuteSolution/20447
try {
//Open the zip file that holds the OO.Org Base file
file = new ZipFile("/Northwind.odb");
//Create a generic temp file. I only need to get the filename from
//the tempfile to prefix the extracted files for OO Base
f = File.createTempFile("ooTempDatabase", "tmp");
f.deleteOnExit();
//Get file entries from the zipfile and loop through all of them
en = file.entries();
while (en.hasMoreElements()) {
//Get the current element
ent = (ZipEntry)en.nextElement();
//If the file is in the database directory, extract it to our
//temp folder using the temp filename above as a prefix
if (ent.getName().startsWith("database/")) {
System.out.println("Extracting File: " + ent.getName());
byte[] buffer = new byte[1024];
//Create an input stream file the file entry
in = file.getInputStream(ent);
//Create a output stream to write out the entry to, using the
//temp filename created above
out = new BufferedOutputStream(new FileOutputStream("/tmp/" + f.getName() + "." + ent.getName().substring(9)));
//Add the newly created temp file to the tempfile vector for deleting
//later on
v.add("/tmp/" + f.getName() + "." + ent.getName().substring(9));
//Read the input file into the buffer, then write out to
//the output file
while((len = in.read(buffer)) >= 0)
out.write(buffer, 0, len);
//close both the input stream and the output stream
out.close();
in.close();
}
}
//Close the zip file since the temp files have been created
file.close();
//Create our JDBC connection based on the temp filename used above
try {
con = DriverManager.getConnection("jdbc:hsqldb:file:/tmp/" + f.getName(), "SA", "");
}
catch (SQLException x) {
System.out.println("Couldn't get connection!");
}
//Create a command object and execute, storing the results in the rec object
com = con.createStatement();
rec = com.executeQuery("select * from \"customers\"");
//GO through the resultset, and output the results
while (rec.next())
System.out.println("Company Name: " + rec.getString("CompanyName") + " Contact Name: " + rec.getString("ContactName"));
//Close all the database objects
rec.close();
com.close();
con.close();
//Delete the temporary files, which file names are stored in the v vector
for (len = 0; len < v.size(); len++)
(new File((String)v.get(len))).delete();
}
catch (Exception e) {
e.printStackTrace();
}
}
}
said...
thanks for this code. it was exactly wat i needed.
only one small advice put your file locatios on top for easy rejuse of the code.
Lord Nobady
said...
Thanks for the useful code.
www.ipmango.com
Frans said...
Hi Guys and Girls,
Very nice! About to try this code, does it work with OO 2.4? I'd like to read *.xls files and export them as jdbc connections, just as ms excel odbc driver. Any clue how i can read a file with OO-calc and 'give it' to OO-base? Then I could use the code above.
Thanks Frans
John Ward said...
Not sure. I am not sure if OO2.4 uses the same format for Base files. I think it does. In which case, you can use Calc just fine, take the data, and pump it into Base using the JDBC connection.
edwardsongy said...
request help in 'building' a link or bridge between java & OOo. your sample code fails to find the reference to 'import org.hsqldb.jbdcDriver;' note that java works w/ MS Access, & that the OOo Base lets me create,view,edit.....database & tables. Thanks and regards. Ed Songy
John Ward said...
Ed,
If your not finding the HSQLDB driver, its because the hsqldb.jar file is not in your classpath. Make sure you include the jar files in OO to keep compatible versions, that should solve the problem.
John
said...
I am getting the below error .. Please help out.... I am doing it in WinXp.
C:\Program Files\Java\jdk1.6.0_07\bin>echo %classpath%
.;C:\Program Files\Java\jdk1.6.0_07\lib;C:\hsqldb\lib;C:\Program Files\QuickTime
\QTSystem\QTJava.zip
C:\Program Files\Java\jdk1.6.0_07\bin>javac Test.java
Test.java:4: package org.hsqldb does not exist
import org.hsqldb.jdbcDriver;
^
Test.java:9: cannot find symbol
symbol : class jdbcDriver
location: class Test
jdbcDriver j = new jdbcDriver(); //Instantiate the jdbcDriver from HSQ
L
^
Test.java:9: cannot find symbol
symbol : class jdbcDriver
location: class Test
jdbcDriver j = new jdbcDriver(); //Instantiate the jdbcDriver from HSQ
L
^
Note: Test.java uses unchecked or unsafe operations.
Note: Recompile with -Xlint:unchecked for details.
3 errors
jacks said...
Very Very Thanks Dude ......
I will try this........
Lets see dose that work .,
jacks said...
Tell me how to execute execute insert and update Queries ??
As above example reads data from a temporary file and not the actual database . after inserting/ updating database how to ensure that the original database has been changed??
said...
I have a problem. I copy and paste your code in NetBeans IDE 6.5, and...
Illegal start of expresion ...
for (len=0; len<>v.size();len++)
Why?