How to connect to Open Office Database using JDBC connection in Java

[Foni kumar]

Hi Experts,

I am having some difficulty while trying to connect to my Open Office Database using JDBC connections. I dont know where i am committing mistake,it will be great if you could provide me suggestions or modify my code which is pasted below. I want to access the database to perform some further operations:-

**********************************************************************************************

**********************************************************************************************

public static void main (String[] args) {
try{
String db_file_name_prefix = "c:\\mydbdir\\test";
Connection connection = null;
connection = DriverManager.getConnection("jdbc:hsqldb:file:" + db_file_name_prefix, "","");
Statement st=connection.createStatement();
ResultSet rs=st.executeQuery("Select * from student");

***********************************************************************************************

***********************************************************************************************

I havent got much exposure about the JDBC connections before so it will be nice if you can take a look if i have committed some errors in any of the statements in the above code snippets. Please provide me any refernce code snippets also if you have any for Open office JDBC connectivity...


I will be looking for your reply on this

P.S : The database is Open Office database with the table named student created in it.

Kind Regards,
deb

[sri ramvaithiyanathan]

Hi debashis,

Welcome to Code Ranch.
You missed to load the driver class.
Means you need to use

 Class.forName("driverDetails");

In future use code tag to post your code, it will help others to read your code.
Do Google search with keyword jdbc connection example, you will get more examples.

Regards,
Sriram.V

[Foni kumar]

Hi Sri ram.

First of all thank you for all your reply and suggestions in this regard. I tried to implement the driver class as shown below in my code but it doesn't seem to work. My code still doesn't respond and also doesn't connect to the Open office Database. I will brief out a bit about my requirement .

I want to connect to the Open office database and fetch the datafrom the Openoffice database table named employee and populate it in my excel sheet. But in this process i am failing in the first step itself ,that is connection to the Open Office database.Could you please take a look on the code which i have pasted below because i have made many attempts to figure out the exact issue but I am not able to get it resolved.


**********
Code
**********

import java.io.*;
import java.sql.*;
import org.hsqldb.jdbcDriver;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFRow;


public class CreateExcel{
public static void main (String[] args) {
try{
String db_file_name_prefix = "c:\\mydbdir\\test";
Connection connection = null;
Class.forName("org.hsqldb.jdbcDriver");	
connection = DriverManager.getConnection("jdbc:hsqldb:file:" + db_file_name_prefix, "","");
Statement st=connection.createStatement();
ResultSet rs=st.executeQuery("Select * from employee");
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("Excel Sheet");
HSSFRow rowhead = sheet.createRow((short)0);
rowhead.createCell((int) 0).setCellValue("Emp Id");
rowhead.createCell((char) 1).setCellValue("Name");
rowhead.createCell((char) 2).setCellValue("Department");
rowhead.createCell((int) 3).setCellValue("Salary");
rowhead.createCell((char) 4).setCellValue("Grade");

int index=1;
while(rs.next()){

HSSFRow row = sheet.createRow((short)index);
row.createCell((int)0).setCellValue(rs.getInt(1));
row.createCell((char)1).setCellValue(rs.getString(2));
row.createCell((char)2).setCellValue(rs.getString(3));
row.createCell((int)3).setCellValue(rs.getInt(4));
row.createCell((char)4).setCellValue(rs.getString(5));
index++;
}
FileOutputStream fileOut = new FileOutputStream("c:\\temp\\excelFile.xls");
wb.write(fileOut);
fileOut.close();
System.out.println("Data is saved");
rs.close();
connection.close();
}
catch(Exception e){}
}
}

Kind Regards,
Debashis

[Paul Clapham]

Your main mistake is here:

catch(Exception e){}  

It's quite likely (and in your case almost certain) that the code will throw some kind of exception. And you have written your program to say "If the code throws an exception, don't do anything".

This is a bad idea. If an exception is thrown, you want to know all about it. So print its stack trace when it happens:

catch(Exception e){
  e.printStackTrace();
}  

[Foni kumar]

Hi Paul,

Thank you!for the suggestions.I tried implementing what you have suggested and got the below errors in my java console.I am unable to make out the very reasons behind these errors ,the first one says File input/output error but my file path for the database file is correct. it is the same location c:\mydbdir\test.properties .It will be great if you could provide me your suggestions on the below errors I am getting with reference to execution of my code which i have pasted before in my previous post reply.

****************************
ERROR I GOT IN CONSOLE

****************************

java.sql.SQLException: File input/output error: file:c:\mydbdir\test.properties
at org.hsqldb.Trace.getError(Trace.java:180)
at org.hsqldb.Trace.getError(Trace.java:144)
at org.hsqldb.Trace.error(Trace.java:205)
at org.hsqldb.Log.saveProperties(Log.java:630)
at org.hsqldb.Log.create(Log.java:521)
at org.hsqldb.Log.open(Log.java:159)
at org.hsqldb.Database.<init>(Database.java:96)
at org.hsqldb.jdbcConnection.openStandalone(jdbcConnection.java:926)
at org.hsqldb.jdbcConnection.<init>(jdbcConnection.java:682)
at org.hsqldb.jdbcDriver.connect(jdbcDriver.java:116)
at java.sql.DriverManager.getConnection(DriverManager.java:322)
at java.sql.DriverManager.getConnection(DriverManager.java:358)
at com.database.CreateExcelFile.main(CreateExcelFile.java:21)

[sri ramvaithiyanathan]

You are facing problem in creating excel file.
Check line number 21

at com.database.CreateExcelFile.main(CreateExcelFile.java:21)

To check status of the connection use

connection.isClosed();

Regards,
Sriram.V