Mailing-List: contact dev-help@openmeetings.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@openmeetings.apache.org
MIME-Version: 1.0
In-Reply-To: <CAJmbs8iM-ttvU4OGMaCQu0F5DwGpdoSdkNjEPjGU-D_2-LLCBw@mail.gmail.com>
References: <CA+o1LfA5hVK=+p8xX9sH2TejqBE=sNu2KTRpdXMv76vEygg2Tw@mail.gmail.com>
	<CAJmbs8guNL_e4eUMcFeTUosBz_xXXkS215cZW=Mpxu-pXoeouQ@mail.gmail.com>
	<CA+o1LfBo1nQ5Ojq5NFibMwcOqgFrO4GgSHM+1mnkauyGWi7X8w@mail.gmail.com>
	<CAJmbs8hn9sO6WB76GoND2J_AHT3bh330+v58QSj235xq-TpOMA@mail.gmail.com>
	<CA+o1LfBZaQFovaW-A4tNwrcFMwigwORDBwdcAYP50hGfXFHMEg@mail.gmail.com>
	<CAJmbs8jy2kVihp=hxvdduPbBD=BiFJSrLygKO45TRzZMywG=sw@mail.gmail.com>
	<CA+o1LfDihxjtA_qh9MzrOKtoQynZXiSJFJ4N_OaPBpX2vMBKUw@mail.gmail.com>
	<CAJmbs8iM-ttvU4OGMaCQu0F5DwGpdoSdkNjEPjGU-D_2-LLCBw@mail.gmail.com>
Date: Thu, 26 May 2016 11:57:41 +0300
Message-ID: <CA+o1LfDaLkpFfCk5+_9t7W4=4OGL=AGFm-v9CbvMc2o0fPD7og@mail.gmail.com>
Subject: Re: Connect to Oracle DB with Service name instead of SID
From: Ioannis Vlachos <ioannis.vlachos@gmail.com>
To: dev@openmeetings.apache.org
Content-Type: multipart/alternative; boundary=001a11452cec997a5a0533bafbf5
archived-at: Thu, 26 May 2016 08:57:49 -0000

--001a11452cec997a5a0533bafbf5
Content-Type: text/plain; charset=UTF-8

This is a big email, sorry:
I was able to connect to the DB through the command line admin. I just had
to remove all parameters that had to do with the database (e.g. --db-type)
and then the script uses the persistence.xml as it is, so it can read the
/SERVICE_NAME at the end. I used the command: admin -i -v -tz Europe/Athens
-email ioannis.vlachos@gmail.com -group gspr -user admin --password admin
--system-email-address noreply@gsis.gr

Then I had this exception:
Caused by: org.apache.openjpa.persistence.PersistenceException: ORA-00904:
: invalid identifier
 {stmnt 251155815 CREATE TABLE appointment (id NUMBER NOT NULL,
is_connected_event NUMBER, deleted NUMBER, description CLOB, end TIMESTAMP,
icalId VARCHAR2(255), inserted TIMESTAMP, isdaily NUMBER, ismonthly NUMBER,
isweekly NUMBER, isyearly NUMBER, language_id NUMBER, location
VARCHAR2(255), password VARCHAR2(255), is_password_protected NUMBER,
reminder VARCHAR2(20), is_reminder_email_send NUMBER, start TIMESTAMP,
appointmentname VARCHAR2(255), updated TIMESTAMP, user_id NUMBER, room_id
NUMBER, PRIMARY KEY (id))} [code=904, state=42000]
...

I tried to run the create command on sql developer (directly on oracle) and
I had the same error. It turns out that the error was caused by the  *start
TIMESTAMP*  column. The word "start" is reserved by oracle and needs
special treatment. So I ran the command through sql developer using *"START"
TIMESTAMP* (with the ") and the table was created. I ran again the admin
command line and it seemed to have created the DB.

Unfortunately, it crashes later on with the stacktrace shown at the end of
the mail. It seems to have something to do with the Naviglobal entity. Any
ideas would be appreciated. I will send a mail to the users mailing list
also, in case someone has come across the same errors .

2016-05-26 10:56:04,134 [main] ERROR org.apache.openmeetings.cli.Admin -
Install failed
org.springframework.orm.jpa.JpaSystemException: 10; nested exception is
<openjpa-2.4.1-r422266:1730418 nonfatal general error>
org.apache.openjpa.persistence.PersistenceException: 10
at
org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:418)
at
org.springframework.orm.jpa.DefaultJpaDialect.translateExceptionIfPossible(DefaultJpaDialect.java:122)
at
org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:521)
at
org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:761)
at
org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:730)
at
org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:485)
at
org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:291)
at
org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at
org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:655)
at
org.apache.openmeetings.db.dao.basic.NavigationDao$$EnhancerBySpringCGLIB$$b65241b9.addGlobalStructure(<generated>)
at
org.apache.openmeetings.installation.ImportInitvalues.loadMainMenu(ImportInitvalues.java:118)
at
org.apache.openmeetings.installation.ImportInitvalues.loadSystem(ImportInitvalues.java:517)
at
org.apache.openmeetings.installation.ImportInitvalues.loadAll(ImportInitvalues.java:533)
at org.apache.openmeetings.cli.Admin.process(Admin.java:278)
at org.apache.openmeetings.cli.Admin.main(Admin.java:528)
Caused by: org.apache.openjpa.persistence.PersistenceException: 10
at
org.apache.openjpa.kernel.BrokerImpl.beforeCompletion(BrokerImpl.java:2029)
at
org.apache.openjpa.kernel.LocalManagedRuntime.commit(LocalManagedRuntime.java:81)
at org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1526)
at
org.apache.openjpa.kernel.DelegatingBroker.commit(DelegatingBroker.java:932)
at
org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:571)
at
org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:517)
... 13 common frames omitted
Caused by: java.lang.ArrayIndexOutOfBoundsException: 10
at oracle.jdbc.driver.OracleSql.computeBasicInfo(OracleSql.java:950)
at oracle.jdbc.driver.OracleSql.getSqlKind(OracleSql.java:623)
at
oracle.jdbc.driver.OraclePreparedStatement.<init>(OraclePreparedStatement.java:1212)
at
oracle.jdbc.driver.T4CPreparedStatement.<init>(T4CPreparedStatement.java:28)
at
oracle.jdbc.driver.T4CDriverExtension.allocatePreparedStatement(T4CDriverExtension.java:68)
at
oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:3140)
at
oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:3042)
at
oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:6022)
at
org.apache.commons.dbcp2.DelegatingConnection.prepareStatement(DelegatingConnection.java:807)
at
org.apache.commons.dbcp2.DelegatingConnection.prepareStatement(DelegatingConnection.java:807)
at
org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:450)
at
org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection.prepareStatement(LoggingConnectionDecorator.java:517)
at
org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:448)
at
org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:448)
at
org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:441)
at
org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.prepareStatement(PreparedStatementManagerImpl.java:288)
at
org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushAndUpdate(PreparedStatementManagerImpl.java:113)
at
org.apache.openjpa.jdbc.kernel.BatchingPreparedStatementManagerImpl.flushAndUpdate(BatchingPreparedStatementManagerImpl.java:79)
at
org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushInternal(PreparedStatementManagerImpl.java:100)
at
org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flush(PreparedStatementManagerImpl.java:88)
at
org.apache.openjpa.jdbc.kernel.ConstraintUpdateManager.flush(ConstraintUpdateManager.java:550)
at
org.apache.openjpa.jdbc.kernel.ConstraintUpdateManager.flush(ConstraintUpdateManager.java:106)
at
org.apache.openjpa.jdbc.kernel.BatchingConstraintUpdateManager.flush(BatchingConstraintUpdateManager.java:59)
at
org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:104)
at
org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:77)
at
org.apache.openjpa.jdbc.kernel.JDBCStoreManager.flush(JDBCStoreManager.java:731)
at
org.apache.openjpa.kernel.DelegatingStoreManager.flush(DelegatingStoreManager.java:131)
at
org.apache.openjpa.datacache.DataCacheStoreManager.flush(DataCacheStoreManager.java:668)
at
org.apache.openjpa.kernel.DelegatingStoreManager.flush(DelegatingStoreManager.java:131)
at org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:2205)
at org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:2103)
at
org.apache.openjpa.kernel.BrokerImpl.beforeCompletion(BrokerImpl.java:2021)
... 18 common frames omitted

2016-05-25 12:08 GMT+03:00 Maxim Solodovnik <solomax666@gmail.com>:

> according to the stackowerflow connection string should be
> "*jdbc:oracle:thin:@//oracle.hostserver2.mydomain.ca:1522/ABCD
> <http://oracle.hostserver2.mydomain.ca:1522/ABCD>*"
> OM requires
> host
> port
> db
>
> could you please try the following:
> host = "*//oracle.hostserver2.mydomain.ca
> <http://oracle.hostserver2.mydomain.ca>*"   (don't forget "//" in the
> beginning)
> port = 1521  (or other valid port)
> db = "Service Name"
>
> same options are available for command line admin
>
>
>
> On Wed, May 25, 2016 at 1:15 PM, Ioannis Vlachos <
> ioannis.vlachos@gmail.com>
> wrote:
>
> > I've done that and the web installer gives the error I've sent you.
> > Command line admin doesn't work either. There is no way (?) to specify
> > whether you want to use SID or Service name and the persistence.xml
> created
> > uses the SID syntax.
> >
> > 2016-05-25 9:34 GMT+03:00 Maxim Solodovnik <solomax666@gmail.com>:
> >
> > > Can you try to manually copy and modify oracle specific persistense.xml
> > > And run installer?
> > > In this case db properties should not be patched
> > >
> > > WBR, Maxim
> > > (from mobile, sorry for the typos)
> > > On May 25, 2016 12:27, "Ioannis Vlachos" <ioannis.vlachos@gmail.com>
> > > wrote:
> > >
> > > > screenshot:
> https://www.dropbox.com/s/k0ocnkwitak7dq3/install.PNG?dl=0
> > > >
> > > > log: https://www.dropbox.com/s/xe3jf9791j8h4bb/openmeetings.log?dl=0
> > > >
> > > >
> > > >
> > > > 2016-05-25 9:14 GMT+03:00 Maxim Solodovnik <solomax666@gmail.com>:
> > > >
> > > > > Mailing list seems to drop your attachment
> > > > > Can you share the image via link?
> > > > >
> > > > > WBR, Maxim
> > > > > (from mobile, sorry for the typos)
> > > > > On May 25, 2016 12:12, "Ioannis Vlachos" <
> ioannis.vlachos@gmail.com>
> > > > > wrote:
> > > > >
> > > > > > I've already tried that (I've searched before I asked).
> > > > > > Unfortunately, openmeetings' web installer does not recognise
> that
> > > > syntax
> > > > > > and shows an error when you try to set the db (see attached
> image).
> > > If
> > > > > you
> > > > > > try to fill out the form, then the connection is made with SID.
> > > > > >
> > > > > > I haven't tried the command line admin though. I'll try it and
> come
> > > > back
> > > > > > with the results.
> > > > > >
> > > > > >
> > > > > >
> > > > > > 2016-05-24 14:41 GMT+03:00 Maxim Solodovnik <
> solomax666@gmail.com
> > >:
> > > > > >
> > > > > >> I believe this is the answer to your question:
> > > > > >>
> > > > > >>
> > > > >
> > > >
> > >
> >
> http://stackoverflow.com/questions/4832056/java-jdbc-how-to-connect-to-oracle-using-service-name-instead-of-sid
> > > > > >>
> > > > > >> On Tue, May 24, 2016 at 3:34 PM, Ioannis Vlachos <
> > > > > >> ioannis.vlachos@gmail.com>
> > > > > >> wrote:
> > > > > >>
> > > > > >> > Hi.
> > > > > >> > In my organization we use Oracle, but we can only connect
> > through
> > > > > >> Service
> > > > > >> > Name and not SID.
> > > > > >> > Is there any way to specify Service Name instead of SID in the
> > > > > >> connection
> > > > > >> > configuration?
> > > > > >> > All the efforts I have made result in an ora-12505 error: tns
> > > > listener
> > > > > >> does
> > > > > >> > not currently know of sid given in connect descriptor.
> > > > > >> >
> > > > > >> > Thanks
> > > > > >> >
> > > > > >> > Ioannis Vlachos
> > > > > >> >
> > > > > >>
> > > > > >>
> > > > > >>
> > > > > >> --
> > > > > >> WBR
> > > > > >> Maxim aka solomax
> > > > > >>
> > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>
>
>
> --
> WBR
> Maxim aka solomax
>

--001a11452cec997a5a0533bafbf5--