How can I change HSQLDB by Oracle DB?

[Normandes Jr]

I would like to use oracle instead HSQLDB, is it possible?

Are there some configuration file?

Thanks.

Normandes Junior.

[Alexandre Mendonça]

Sure. Check the documentation:

http://docs.jboss.org/jbossas/docs/Administration_And_Configuration_Guide/5/html_single/index.html#alternative_DBs

Regards,

--

Alexandre Mendonça // JBoss R&D
http://ammendonca.blogspot.com/

[Normandes Jr]

I know how to change HSQLDB from JBoss 5. I would like to change
Mobicents databases.

I am not the owner of database, so I would like the DDL to create
mobicents table at my Oracle database.

How can I configure mobicents to know that the database changed?

On Aug 26, 9:44 pm, Alexandre Mendonça <brains...@gmail.com> wrote:
> Sure. Check the documentation:
>

> http://docs.jboss.org/jbossas/docs/Administration_And_Configuration_G...
>
> Regards,
>
> --
> *Alexandre Mendonça* // JBoss R&Dhttp://ammendonca.blogspot.com/

[Alexandre Mendonça]

Mobicents uses the same database as JBoss AS.

--

Alexandre Mendonça // JBoss R&D
http://ammendonca.blogspot.com/

[Normandes Jr]

When I start mobicents at HSQLDB I can see the tables:
- SLEE_PEAAV_ADDRESSPROFILECMP_167136107ADDRESS
- SLEE_PE_ADDRESSPROFILE11CMP_152456043
- SLEE_PE_ADDRESSPROFILECMP_167136107
- SLEE_PE_RESOURCEINFOPROFILECMP_1974387886

Where are these ddl? Here at my company we have a DBA that create
tables for us and then grant its to another user. For example, he will
create the table with the user: jbossowner and grant select, insert,
delete and update to the user: jboss. So I will use the user jboss to
connect.

Will I have to create the ddl by mysef? At JBoss, as you suggest me,
there are some xml files that exists DDL, what about mobicents?

On Aug 29, 9:28 am, Alexandre Mendonça <brains...@gmail.com> wrote:
> Mobicents uses the same database as JBoss AS.
>
> --

[Alexandre Mendonça]

Those tables are automatically created by Mobicents on deploy of those Profile Specifications, you do not need to worry about creating anything, just change the JBoss Datasource to use Oracle DB and grant it the rights to create and drop tables as well, because mobicents will need to do so when deploying/undeploying profile specifications.

--

Alexandre Mendonça // JBoss R&D

http://ammendonca.blogspot.com/

[Normandes Jr]

Thanks Alexandre.

I would like to do a final question. Does the name of these tables
change?

I am asking because I can not start JBoss with a user that has
permission to create or delete table here at company.

So I will use a test database, generate the ddl and then use it to
create at my production database.

On Aug 29, 11:02 am, Alexandre Mendonça <brains...@gmail.com> wrote:
> Those tables are automatically created by Mobicents on deploy of those
> Profile Specifications, you do not need to worry about creating anything,
> just change the JBoss Datasource to use Oracle DB and grant it the rights to
> create and drop tables as well, because mobicents will need to do so when
> deploying/undeploying profile specifications.
>
> --

[Normandes Jr]

I am trying to use a development database with permission to create -
drop table and I am getting this error, do you know something about?

Bound factory to JNDI name: persistence.unit:unitName=#mobicents-
profile-persistence-pu
11:42:16,434 WARN [SessionFactoryObjectFactory] InitialContext did
not implement EventContext
11:42:16,447 INFO [SchemaUpdate] Running hbm2ddl schema update
11:42:16,447 INFO [SchemaUpdate] fetching database metadata
11:42:16,451 ERROR [SchemaUpdate] could not get database metadata
java.sql.SQLException: ORA-00942: table or view does not exist

at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:
112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
...
...

[Alexandre Mendonça]

The name should be the same, but since it uses JPA, I'm not sure that will work.

--

Alexandre Mendonça // JBoss R&D

http://ammendonca.blogspot.com/

[Normandes Jr]

I saw at the class:
org.mobicents.slee.container.deployment.profile.jpa.Configuration that
there is a property - persistProfiles - that I can set to choose
update or create-drop.

How can I set this class?

On Aug 29, 11:52 am, Alexandre Mendonça <brains...@gmail.com> wrote:
> The name should be the same, but since it uses JPA, I'm not sure that will
> work.
>
> --

[Alexandre Mendonça]

It looks like it needs more than those permissions. JPA probably needs some admin permissions in order to access the DB metadata and verify the existence of tables and it's conformity to the java objects. And probably that's not just only for the first time, so you'll need to sort the problem with not allowing to have users with more than insert/update/delete permissions.

--

Alexandre Mendonça // JBoss R&D

http://ammendonca.blogspot.com/

[Normandes Jr]

It works.

I found how to configure at mobicents-slee/META-INF/jboss-beans.xml

I changed the property: hibernateDialect to use OracleDialect

<bean name="Mobicents.JAINSLEE.Profiles.JPA.HSQLDBConfig"
class="org.mobicents.slee.container.deployment.profile.jpa.Configuration">
<property name="persistProfiles">true</property>
<property name="clusteredProfiles">false</property>
<property name="hibernateDatasource">java:/DefaultDS</property>
<property
name="hibernateDialect">org.hibernate.dialect.OracleDialect</property>
<depends>jboss.jca:service=DataSourceBinding,name=DefaultDS</
depends>
</bean>

On Aug 29, 11:58 am, Alexandre Mendonça <brains...@gmail.com> wrote:
> It looks like it needs more than those permissions. JPA probably needs some
> admin permissions in order to access the DB metadata and verify
> the existence of tables and it's conformity to the java objects. And
> probably that's not just only for the first time, so you'll need to sort the
> problem with not allowing to have users with more than insert/update/delete
> permissions.
>
> --

[Alexandre Mendonça]

Great to hear! Let us know if you get it working completely (ie, once you move to the more restricted account), other users may benefit from this as well.

Regards,

--

Alexandre Mendonça // JBoss R&D
http://ammendonca.blogspot.com/

[Normandes Jr]

Unfortunatly I was wrong, it isn't working. Look the messages that I
didn't see it:

16:23:06,801 ERROR [SchemaUpdate] Unsuccessful: create table
SLEE_PEAAV_AddressProfileCMP_167136107addresses (id number(19,0) not
null, serializable raw(255), string varchar2(255), owner_profileName
varchar2(255) not null, owner_tableName varchar2(255) not null,
primary key (id), unique (owner_tableName, string))
16:23:06,801 ERROR [SchemaUpdate] ORA-00972: identifier is too long

16:23:06,803 ERROR [SchemaUpdate] Unsuccessful: create table
SLEE_PE_AddressProfileCMP_167136107 (profileName varchar2(255) not
null, tableName varchar2(255) not null, primary key (profileName,
tableName))
16:23:06,803 ERROR [SchemaUpdate] ORA-00972: identifier is too long

16:23:06,804 ERROR [SchemaUpdate] Unsuccessful: alter table
SLEE_PEAAV_AddressProfileCMP_167136107addresses add constraint
FKC2CF5BDED03DFEFA foreign key (owner_profileName, owner_tableName)
references SLEE_PE_AddressProfileCMP_167136107
16:23:06,804 ERROR [SchemaUpdate] ORA-00972: identifier is too long

I will have to continue using HSQLDB.

Sorry.

On Aug 29, 12:18 pm, Alexandre Mendonça <brains...@gmail.com> wrote:
> Great to hear! Let us know if you get it working completely (ie, once you
> move to the more restricted account), other users may benefit from this as
> well.
>
> Regards,
>
> --

[Alexandre Mendonça]

Hmmm.. it seems to be a limitation with Oracle [table] names.. we can try to improve it to make it work with Oracle DB. If you can, please open an issue requesting such fix in our issue tracker.

Anyway, there's plenty of other options, such as MySQL or PostgreSQL, I think they deserve a try if you want a true database.

Regards,

--

Alexandre Mendonça // JBoss R&D
http://ammendonca.blogspot.com/