I am connecting to my SQL Server 2008 database server using a default catalog and default schema in my cfg.xml. As such, the table names don't have to be fully qualified with the schema name.
I do call one stored procedure to load a queue table that I then use Hibernate mapping to manage. The weird thing is I have to fully qualify the stored proc in my hbm.xml named query in order for it to be found (i.e. DatabaseName.SchemaName.StoredProcName). It's working but it feels wrong and I wanted some advice. Here is my cfg.xml:
Code:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory name="sessionFactory">
<property name="hibernate.connection.driver_class">com.microsoft.sqlserver.jdbc.SQLServerDriver</property>
<property name="hibernate.connection.password">password</property>
<property name="hibernate.connection.url">jdbc:sqlserver://localhost:1433</property>
<property name="hibernate.connection.username">jdbc</property>
<property name="hibernate.default_catalog">DatabaseName</property>
<property name="hibernate.default_schema">SchemaName</property>
<property name="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</property>
<property name="hibernate.c3p0.min_size">5</property>
<property name="hibernate.c3p0.max_size">20</property>
<property name="hibernate.c3p0.timeout">300</property>
<property name="hibernate.c3p0.max_statements">50</property>
<property name="hibernate.c3p0.idle_test_period">3000</property>
<mapping resource="com/example/dao/EntityOne.hbm.xml"/>
<mapping resource="com/example/dao/EntityTwo.hbm.xml"/>
</session-factory>
</hibernate-configuration>
And here is how I am calling the stored proc:
Code:
<sql-query name="spLoadQueue" callable="true">
<return-scalar column="NumberRows" type="java.lang.Integer" />
{ call DatabaseName.SchemaName.StoredProcName() }
</sql-query>
Again, if I just put { call StoredProcName() } I get a SQLException "could not find the stored procedure"
UPDATE: This is not just a stored procedure issue. Anytime I execute a SQLServer-specific statement in code using createSQLQuery I have to fully qualify the object name no matter if my cfg.xml file has a default database and schema indicated or not. HQL statements are fine without qualifying the object name.