We currently have our session factory setup for the MSSQL dialect. I have come across a situation where we want to obtain data from one of our legacy systems, which is using a proprietary sql database. I am able to run query's on it by passing a JDBC connection into the session, and it seems to work OK for the most part under the MSSQL dialect.

The problem I am having is that the database does not support "select top", so when I am doing setMaxResults() on my criteria query, the DB driver is throwing an error. I guess I have a couple of questions:

1. Is there a dialect I can use that will limit the number of rows that are brought back on the list()? For example, the query executes and returns 500 rows, but only have hibernate populate the list with the first 50 of these results.

2. If the answer to question #1 is yes, then is there a way to change the dialect progamatically through the session, or in the mapping file for this class?

Thanks,

Paul Zepernick

Below is the information on my hibernate install:






Hibernate version:
2.1.7

Mapping documents:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>

<class name="com.iws.model.workflow.eci.search.Patient" table="PATIENT">

<composite-id>
<key-property name="punbr" column="PUNBR" type="string" />
<key-property name="grnbr" column="GRNBR" type="string"/>
<key-property name="essn" column="ESSN" type="string"/>
<key-property name="seq" column="SEQ" type="string"/>
</composite-id>

<property name="lname"
column="LNAME"
update="false"
insert="false"
type="string"/>

<property name="ftname"
column="FTNAME"
update="false"
insert="false"
type="string"/>

<property name="addr1"
column="ADDR1"
update="false"
insert="false"
type="string"/>

<property name="city"
column="CITY"
update="false"
insert="false"
type="string"/>

<property name="state"
column="STATE"
update="false"
insert="false"
type="string"/>

<property name="zip"
column="ZIP"
update="false"
insert="false"
type="string"/>
</class>

</hibernate-mapping>


Name and version of the database you are using:
Proprietary database. DBC/FS

Why don't you just use a second SessionFactory with GenericSqlDialect

OK, GenericSqlDialect will work for my situation then?


How would I go about creating a new session factory? Would I just add another <session-factory></session-factory> block to the hibernate.cfg.xml?

If so, do I have to build the session factory differently? Here is how I am currently building it:




Once the session factory is built, how do I tell hibernate to give me a session from the factory I want?

Thanks,

Paul

Just use two config xml files

OK, thanks for the help.....

One more question through. I have the second SessionFactory loading now, but how do I tell the config file to use the GenericDialect. I do not see it in this list:

http://www.hibernate.org/hib_docs/refer ... l-dialects

Thanks,

Paul

Did you try to set it or simply not use a dialect?

I tried to set it.

<property name="dialect">GenericSqlDialect</property>

It did not throw any errors, but I just wanted to make sure that this was the proper format for the Dialect.

Thanks,

Paul

use the full class name of the dialect.