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: <CA+o1LfDaLkpFfCk5+_9t7W4=4OGL=AGFm-v9CbvMc2o0fPD7og@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>
	<CA+o1LfDaLkpFfCk5+_9t7W4=4OGL=AGFm-v9CbvMc2o0fPD7og@mail.gmail.com>
Date: Thu, 26 May 2016 15:25:53 +0600
Message-ID: <CAJmbs8hgW9DP9GbnmKWH8L9ec1N_Qqfb6ZrBhHoscAk43B6omw@mail.gmail.com>
Subject: Re: Connect to Oracle DB with Service name instead of SID
From: Maxim Solodovnik <solomax666@gmail.com>
To: dev <dev@openmeetings.apache.org>
Content-Type: multipart/alternative; boundary=001a1133143e77e4d70533bb6016
archived-at: Thu, 26 May 2016 09:26:01 -0000

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

I moved discussion to user list, let's continue there

On Thu, May 26, 2016 at 2:57 PM, Ioannis Vlachos <ioannis.vlachos@gmail.com>
wrote:

> 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
> >
>


-- 
WBR
Maxim aka solomax

--001a1133143e77e4d70533bb6016--