Hi,

I just got my hibernate working with my java object, but I couldn't specify which the database with in the sybase server,

I am able to connect to the sybase database, and now I am having problem in pointing to a different database with in the sybase server.
Does any one know how to look for a table in a particular data base?

say my table is in prod_db data base (table name: facility) how/where can I specify the prod_db? in the hibernate?

Do I have to do this with in the hibernate mapping?

Please advice

Thanks

is there a good sample code?

I am still looking for an answer

Well how would you do it in JDBC?

Yes, Here is what I do it with the jdbc:

I establish the connection using the following parameters

DRIVER = com.sybase.jdbc2.jdbc.SybDriver
URL = jdbc:sybase:Tds:myhost.com:3029
USER = username
PASSWORD = password

Then on the sql query I specify the db name for example:

select * from ql_prod.myTable this way it points to the right data base (in this case it is ql_prod)

This says unknown alias of "ql_prod"

Then I tried to assign the database in the url as
jdbc:sybase:Tds:myhost.com:3029?database=ql_prod
here it couldn't find the table and it is trying to create one!! on the default data base!.

I have also tried to specify the db on the hibernate mapping
<hibernate-mapping>
<class name="test.ShoppingCart" table="ql_prod.myTable">

In the above case hibernate couldn't find the table first!, then it tries to create one!

I suspect that you are mixing up the terms schema and database.

Check out the schema tag in the mapping files.

Thanks, but what I want is hibernate to point to specific sybase database, say I have multiple databases in my sybase server, ql_prod, test_dev, test_prod, how do I set the hibernate to access these datra bases?

Did you try setting the property

... in your hibernate.cfg.xml file?

Hi,
Thanks!,

Here is my hibernate.cfg.xml, where do I set the database (ql_prod)? in this?
<hibernate-configuration>

<session-factory>
<property name="hibernate.connection.driver_class">com.sybase.jdbc2.jdbc.SybDriver</property>
<property name="hibernate.connection.url">jdbc:sybase:Tds:myhost.com:3029</property>
<property name="hibernate.connection.username">userName</property>
<property name="hibernate.connection.password">password</property>
<property name="dialect">org.hibernate.dialect.SybaseDialect</property>
<property name="show_sql">true</property>
<property name="transaction.factory_class">
org.hibernate.transaction.JDBCTransactionFactory
</property>
<property name="hibernate.cache.provider_class">
org.hibernate.cache.HashtableCacheProvider
</property>
<property name="hibernate.hbm2ddl.auto">update</property>

<mapping resource="adbtest/shoppingcart.hbm.xml"/>

</session-factory>

</hibernate-configuration>

Hi, Thanks, but no luck yet, here is the error message I am getting

first of all the hibernate is not finding the table and next it is trying to create the table (which is already there in the db), and finally it is trying to execute the select query, but throwed some error

Here is my java code:
Session session = sessionFactory.openSession();

Transaction tx = session.beginTransaction();

Query query = session.createQuery("from tshopping_cart");
tx.commit();
return query.list();

Here is the hbm.xml
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="adbtest.ShoppingCart" table="tshopping_cart">
<id name="session_id" column="session_id" type="int">
<generator class="increment"/>
</id>
<property name="site" column = "site"/>
</class>
</hibernate-mapping>

here is the hibernate.cfg.xml
<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-2.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="hibernate.connection.driver_class">com.sybase.jdbc2.jdbc.SybDriver</property>
<property name="hibernate.connection.url">jdbc:sybase:Tds:myhost.com:3029</property>
<property name="default_schema">ql_prod</property>
<property name="hibernate.connection.username">userName</property>
<property name="hibernate.connection.password">password</property>
<property name="dialect">org.hibernate.dialect.SybaseDialect</property>
<property name="show_sql">true</property>
<property name="transaction.factory_class">
org.hibernate.transaction.JDBCTransactionFactory
</property>
<property name="hibernate.cache.provider_class">
org.hibernate.cache.HashtableCacheProvider
</property>
<property name="hibernate.hbm2ddl.auto">update</property>
<mapping resource="adbtest/shoppingcart.hbm.xml"/>
</session-factory>
</hibernate-configuration>

Here is the hibernate output:

Initializing Hibernate
INFO - Hibernate 3.0 beta 4
INFO - hibernate.properties not found
INFO - using CGLIB reflection optimizer
INFO - using JDK 1.4 java.sql.Timestamp handling
INFO - configuring from resource: /hibernate.cfg.xml
INFO - Configuration resource: /hibernate.cfg.xml
INFO - Mapping resource: adbtest/shoppingcart.hbm.xml
INFO - Mapping class: adbtest.ShoppingCart -> tshopping_cart
INFO - Configured SessionFactory: null
INFO - processing extends queue
INFO - processing collection mappings
INFO - processing association property references
INFO - processing foreign key constraints
INFO - Using dialect: org.hibernate.dialect.SybaseDialect
INFO - Default schema: ql_prod
INFO - Default batch fetch size: 1
INFO - Generate SQL with comments: disabled
INFO - Order SQL updates by primary key: disabled
INFO - Query translator: org.hibernate.hql.classic.ClassicQueryTranslatorFactor
y
INFO - Query language substitutions: {}
INFO - Using Hibernate built-in connection pool (not for production use!)
INFO - Hibernate connection pool size: 20
INFO - autocommit mode: false
INFO - using driver: com.sybase.jdbc2.jdbc.SybDriver at URL: jdbc:sybase:Tds:myhost.com:3029
INFO - connection properties: {user=userName, password=******}
INFO - Scrollable result sets: enabled
INFO - JDBC3 getGeneratedKeys(): disabled
INFO - Transaction strategy: org.hibernate.transaction.JDBCTransactionFactory
INFO - No TransactionManagerLookup configured (in JTA environment, use of read-
write or transactional second-level cache is not recommended)
INFO - Automatic flush during beforeCompletion(): disabled
INFO - Automatic session close at end of transaction: disabled
INFO - Cache provider: org.hibernate.cache.HashtableCacheProvider
INFO - Second-level cache: enabled
INFO - Optimize cache for minimal puts: disabled
INFO - Structured second-level cache entries: enabled
INFO - Query cache: disabled
INFO - Echoing all SQL to stdout
INFO - Statistics: disabled
INFO - Deleted entity synthetic identifier rollback: disabled
INFO - building session factory
INFO - Not binding factory to JNDI, no JNDI name configured
INFO - Using dialect: org.hibernate.dialect.SybaseDialect
INFO - Using Hibernate built-in connection pool (not for production use!)
INFO - Hibernate connection pool size: 20
INFO - autocommit mode: false
INFO - using driver: com.sybase.jdbc2.jdbc.SybDriver at URL: jdbc:sybase:Tds:myhost.com:3029
INFO - connection properties: {user=userName, password=******}
INFO - Running hbm2ddl schema update
INFO - fetching database metadata
INFO - updating schema
INFO - processing extends queue
INFO - processing collection mappings
INFO - processing association property references
INFO - processing foreign key constraints
INFO - table not found: tshopping_cart
INFO - table not found: tshopping_cart
ERROR - Unsuccessful: create table ql_prod.tshopping_cart (session_id int not nul
l, site varchar(255) null, primary key (session_id))
ERROR - User name ql_prod does not exist in sysusers.

INFO - schema update complete
INFO - cleaning up connection pool: jdbc:sybase:Tds:myhost.com:3029
INFO - Checking 0 named queries
Finished Initializing Hibernate
Hibernate: select from
WARN - SQL Error: 156, SQLState: ZZZZZ
ERROR - Incorrect syntax near the keyword 'from'.

Exception in thread "main" java.lang.RuntimeException: could not execute query
at adbtest.FacilityManager.listEvents(FacilityManager.java:73)
at adbtest.FacilityManager.main(FacilityManager.java:48)
Press any key to continue...

Thanks for the sample code, I tried it and now I am getting the following error

Finished Initializing Hibernate
Hibernate: select from
WARN - SQL Error: 156, SQLState: ZZZZZ
ERROR - Incorrect syntax near the keyword 'from'.

ERROR catched in the exception=null

Any idea? also, the table I am using is read only (select enabled and insert and update disabled), will hibernate always needs to have full privilege (select, insert and update)?

Thanks for your help

I think I know your problem. The database you want to use n the sybase server is not your users default database. So you need to modify your connection url to add '/dbname' after the port. Sybasde doesn't have schemas really, usually everything is done in dbo.

The url might look like partially like this: www.yahho.com:4100/mydb.

Thanks, Thats a great suggestion, yes now the hibernate finds the tables.

But, it is still giving the following error, I would appreciate if you could tell me what is wrong.

Thanks again

INFO - connection properties: {user=userName, password=*****}
INFO - Running hbm2ddl schema update
INFO - fetching database metadata
INFO - updating schema
INFO - processing extends queue
INFO - processing collection mappings
INFO - processing association property references
INFO - processing foreign key constraints
INFO - table found: ql_prod.dbo.shopping_cart
INFO - columns: [site, session_id]
INFO - foreign keys: []
INFO - indexes: [shopping__7805267831]
INFO - schema update complete
INFO - cleaning up connection pool: jdbc:sybase:Tds:muhost.com:3029/ql
_prod
INFO - Checking 0 named queries
Finished Initializing Hibernate
Hibernate: select from
WARN - SQL Error: 156, SQLState: ZZZZZ
ERROR - Incorrect syntax near the keyword 'from'.

ERROR:=null
---------
Here is the code that I am using

private List listEvents() throws Exception{

Session session = sessionFactory.openSession();
Transaction tx = null;
List result = null;
try
{
tx = session.beginTransaction();

Query query = session.createQuery("from shopping_cart");
result = query.list();
tx.commit();
return result;
}
catch (Exception ex)
{
if (tx != null)
{
try
{
tx.rollback();
}
catch (Exception rbe)
{
System.out.println("ERROR:=="+rbe.getMessage());
//log.warn(rbe.getMessage(), rbe);
throw ex; // original exception
}
}
}
finally
{
session.close();
}
return result;
}