Hibernate Dialect for MS Access

[Prabhat Ranjan]

Hi ,

I am using this setting for MS Access Dialect for Hibernate.

But getting Error while parsing of hibernate.cfg.xml

<property name="hibernate.dialect">net.sf.hibernate.dialect.MSAccessDialect</property>
<property name="hibernate.conn.driver_class">sun.jdbc.odbc.JdbcOdbcDriver</property>
<property name="hibernate.conn.url">jdbcdbc:;DRIVER=Microsoft Access Driver (*.mdb);DBQ=C:\health\hibernateData.mdb
</property>

Error is : Value must be declared for element type "property".

Please help on this.

Regards,
Prabhat

[Prabhat Ranjan]

This is my hibernate.cfg.xml

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
<session-factory>
            <property name="hibernate.connection.username"></property>
      <property name="hibernate.connection.password"></property>
      <property name="hibernate.connection.pool_size">10</property>
      <property name="show_sql">true</property>
      <property name="hibernate.dialect">net.sf.hibernate.dialect.MSAccessDialect</property>
<property name="hibernate.connection.driver_class">sun.jdbc.odbc.JdbcOdbcDriver</property>
<property name="hibernate.conn.url">jdbc:odbc:;DRIVER=Microsoft Access Driver (*.mdb);DBQ=C:\health\hibernateData.mdb
</property>
<property name="hibernate.hbm2ddl.auto">insert</property>
      <!-- Mapping files -->
      <mapping resource="student.hbm.xml"/>
</session-factory>
</hibernate-configuration>

This problem solved, but now i am getting this error:

Dialect class not found: net.sf.hibernate.dialect.MSAccessDialect

could anyone tell me where i am wrong ?

[Prabhat Ranjan]

I think no dialect made for MS Access so its not working

[Lester Burnham]

You're right that Hibernate does not ship with an Access dialect; that hasn't stopped various people from writing one, though. You'll find their efforts by searching for "access hibernate dialect" or some such phrase.

But Access as a DB is limited, as is the JDBC/ODBC bridge driver you're trying to use, so be prepared for problems. If this is for a commercial project I advise to use a real JDBC driver (like http://www.hxtt.com/access.html) which incidentally comes with a Hibernate dialect.

[Prabhat Ranjan]

thanks i have downloaded the mysql and now working fine for the same.

[Prabhat Ranjan]

I am getting this error:
Exception in thread "main" org.hibernate.exception.GenericJDBCException: Cannot open connection


My java code:

import org.hibernate.Session;
import org.hibernate.Transaction;

import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

public class FirstExample {
  public static void main(String[] args) {
    Session session = null;

    SessionFactory sessionFactory = new 

    Configuration().configure().buildSessionFactory();
    session =sessionFactory.openSession();
    // The below transaction element is not initialized and commited
    Transaction trans = session.beginTransaction();
    //Create new instance of Contact and set 

    System.out.println("Inserting Record");
    Student contact = new Student();
    contact.setId(3);
    contact.setName("Aman");
    contact.setRoll(45);
    contact.setAge(12);
    session.save(contact);
    trans.commit();
    session.close();
    System.out.println("Done");
  }
}

my cfg file:

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
<session-factory>
      <property name="hibernate.connection.driver_class">

com.mysql.jdbc.Driver</property>
      <property name="hibernate.connection.url">

jdbc:mysql://localhost:3036/hibernatetutorial</property>
      <property name="hibernate.connection.username">root</property>
      <property name="hibernate.connection.password"></property>
      <property name="hibernate.connection.pool_size">10</property>
      <property name="show_sql">true</property>
      <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
      <property name="hibernate.hbm2ddl.auto">insert</property>
      
      <property name="connection.autocommit">true</property>
      <!-- Mapping files -->
      <mapping resource="student.hbm.xml"/>
</session-factory>
</hibernate-configuration>

this is the error while executing the file:


Exception in thread "main" org.hibernate.exception.GenericJDBCException: Cannot open connection
at org.hibernate.exception.ErrorCodeConverter.handledNonSpecificException(ErrorCodeConverter.java:92)


what is the problem with the connection.

hOW CAN I GO AHEAD TO SOLVE THIS

[Lester Burnham]

Is MySql up and running on port 3036? Can you connect to it with some other JDBC client?

[Prabhat Ranjan]

yes mysql was up and i am not sure it was running up on 3036.

How can i make sure that it is running on 3036.

[Lester Burnham]

You would have had to configure it especially to use that port, because the default port is 3306.

[Prabhat Ranjan]

yes i checked it is using 3036 port only .

any another clue to resolve it, i saw in google there is lot of people has faced this issue...the solution is also quite different for each one.

I am sorry for typing mistake

it is running only in 3036 port

[Lester Burnham]

Well, if it uses port 3026 (that's not a typo, right?), then you can't expect to be able to connect to port 3036.

[Prabhat Ranjan]

i am sorry in typing mistake , it is using 3036 only.

[Lester Burnham]

In that case you can use a JDBC client like SquirrelSQL to make sure you can connect. Or write a small desktop application that uses JDBC to test the connection.

[Prabhat Ranjan]

this is not enough to test , i am using mysql console base client , where i am able to connect it.

[Lester Burnham]

That image shows you using a DB named "prabhat" - that's not what the config file uses.

How are you specifying to use port 3036 when starting the MySQL client?

[Prabhat Ranjan]

I checked the configuration setting it is using 3036

PFA snap screen.

[Lester Burnham]

I checked the configuration setting it is using 3036

Actually, no, just as I thought. Software development is all about paying attention to detail.

[Prabhat Ranjan]

yes you are true, so i say every unknown thing is known by some known thing.

[Prabhat Ranjan]

I have tested a simple jdbc program to test the connection.

But getting error:

import java.sql.*;

public class JDBCSample {
 
public static void main( String args[]) throws SQLException {
 
String connectionURL = "jdbc:mysql://localhost:3036/prabhat;user=root;password=";
// Change the connection string according to your db, ip, username and password
Connection con = null;

try {
 
    // Load the Driver class.
    Class.forName("com.mysql.jdbc.Driver");
    // If you are using any other database then load the right driver here.
 
    //Create the connection using the static getConnection method
  con = DriverManager.getConnection (connectionURL);
 
    //Create a Statement class to execute the SQL statement
    Statement stmt = con.createStatement();
 
    //Execute the SQL statement and get the results in a Resultset
    ResultSet rs = stmt.executeQuery("select moviename, releasedate from movies");
 
    // Iterate through the ResultSet, displaying two values
    // for each row using the getString method
 
    while (rs.next())
        System.out.println("Name= " + rs.getString("moviename") + " Date= " + rs.getString("releasedate"));
}
catch (SQLException e) {
    e.printStackTrace();
}
catch (Exception e) {
    e.printStackTrace();
}
finally {
    // Close the connection
    con.close();
}
}
}

Error is :

com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception: 

** BEGIN NESTED EXCEPTION ** 

java.net.ConnectException
MESSAGE: Connection refused: connect

STACKTRACE:

java.net.ConnectException: Connection refused: connect
	at java.net.PlainSocketImpl.socketConnect(Native Method)
	at java.net.PlainSocketImpl.doConnect(Unknown Source)
	at java.net.PlainSocketImpl.connectToAddress(Unknown Source)
	at java.net.PlainSocketImpl.connect(Unknown Source)
	at java.net.SocksSocketImpl.connect(Unknown Source)
	at java.net.Socket.connect(Unknown Source)
	at java.net.Socket.connect(Unknown Source)
	at java.net.Socket.<init>(Unknown Source)
	at java.net.Socket.<init>(Unknown Source)
	at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:256)
	at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:271)
	at com.mysql.jdbc.Connection.createNewIO(Connection.java:2771)
	at com.mysql.jdbc.Connection.<init>(Connection.java:1555)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
	at java.sql.DriverManager.getConnection(Unknown Source)
	at java.sql.DriverManager.getConnection(Unknown Source)
	at JDBCSample.main(JDBCSample.java:18)


** END NESTED EXCEPTION **



Last packet sent to the server was 0 ms ago.
	at com.mysql.jdbc.Connection.createNewIO(Connection.java:2847)
	at com.mysql.jdbc.Connection.<init>(Connection.java:1555)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
	at java.sql.DriverManager.getConnection(Unknown Source)
	at java.sql.DriverManager.getConnection(Unknown Source)
	at JDBCSample.main(JDBCSample.java:18)
Exception in thread "main" java.lang.NullPointerException
	at JDBCSample.main(JDBCSample.java:40)

I also looked into the google, lot of people faced same issue but no reply.

could any one help me on this. how peoples are using my sql for development environment.
is there any thing missing from my end.

I have already imported the mysql-connection in build path.

[Jimmy Clark]

jdbc:mysql://localhost:3036/

Try using the machine's IP address instead of localhost

[Lester Burnham]

What I meant by my last post is: Your MySQL instance is NOT running on port 3036. The screenshot you posted shows the actual port. That's what I mean by "paying attention to details".

[Prabhat Ranjan]

hey lester, see my other post and mysql is running only on 3306 port.

What else i can do

i checked using command: netstat -a

Proto Local Address Foreign Address State
TCP 0.0.0.0:135 Prabhat-PC:0 LISTENING
TCP 0.0.0.0:445 Prabhat-PC:0 LISTENING
TCP 0.0.0.0:2869 Prabhat-PC:0 LISTENING
[b]

TCP 0.0.0.0:3306 Prabhat-PC:0 LISTENING 

[/b]
TCP 0.0.0.0:5151 Prabhat-PC:0 LISTENING

[Lester Burnham]

What do you mean: "what else can I do"? All the code you posted shows that you are trying to connect to the DB on a port on which -as you now know- it is not listening. The screenshot you posted 2 days ago already told you which port to use, if only you had paid attention to detail. Now there's nothing left to do but fix your code.

[Prabhat Ranjan]

i am sorry , i have fixed my issue..

Now hibernate and jdbc all is working. ALL is WELL.
Thanks

[Paul Clapham]

See also this thread for a parallel but similar process: http://www.coderanch.com/t/298301/JDBC/java/which-port-mySQL-running.