Save Orbeon Forms in SQL server

classic Classic list List threaded Threaded
16 messages Options
Reply | Threaded
Open this post in threaded view
|

Save Orbeon Forms in SQL server

RameshBhat
Hi,

How do we save orbeon forms in sql server and read data from it?

regards


Note:

I have set up the following:

Modified the properties.xsml template file:
<property as="xs:string" name="oxf.fr.persistence.provider.*.*.*" value="sqlserver"/>
<property as="xs:string" name="oxf.fr.persistence.sqlserver.datasource" value="sqlserver"/>
<property as="xs:boolean" name="oxf.fr.persistence.sqlserver.create-flat-view" value="true"/>

Modified the context.xml :

<Context>
    <WatchedResource>WEB-INF/web.xml</WatchedResource>
    <WatchedResource>${catalina.base}/conf/web.xml</WatchedResource>
    <Resource
                   name="sqljdbc42/sqlserver"
                   driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"

                   auth="Container"
                   type="javax.sql.DataSource"

                   initialSize="3"
                   maxTotal="10"
                   maxIdle="10"
                   maxWaitMillis="30000"

                   poolPreparedStatements="true"

                   validationQuery="select 1"
                   testOnBorrow="true"

                   username="orbeon"
                   password="orbeon"
                   url="sqljdbc42:sqlserver://IpAddress:1433;databaseName=orbeon"/>
 <Resources cachingAllowed="true" cacheMaxSize="100000" />
</Context>

Created a database orbeon in both modes (SQL and Mixed mode authentication)

Created 5 tables (as specified in githug).


When I save forms in orbeon the document is saved successfully on the file system, but no records are saved in orbeon_forms_data table.
Reply | Threaded
Open this post in threaded view
|

Re: Save Orbeon Forms in SQL server

Alessandro  Vernet
Administrator
Hi Ramesh,

The configuration can be tricky. Let's try to figure this out.

- Are you sure you are editing WEB-INF/resources/config/properties-local.xml? Double check the file name, as if you're editing another file, Orbeon Forms won't read your config.
- Remove the oxf.fr.persistence.sqlserver.datasource; you don't need it, since you're not changing its default value, and it is better to keep your config as simple as possible.
- For now, remove or comment out the oxf.fr.persistence.sqlserver.create-flat-view property. You can introduce the flat view later, once things work without it.
- In your context.xml, the name of the resource must be jdbc/sqlserver.

Alex
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
Reply | Threaded
Open this post in threaded view
|

Re: Save Orbeon Forms in SQL server

RameshBhat
Hi,

I have made the changes and tried but no luck. The document still gets saved in the file system. Also I am not able to populate values from a drop down.

Context.xml from the following path :
C:\Program Files (x86)\Apache Software Foundation\Tomcat 6.0\conf\ context.xml

<Context>
    <WatchedResource>WEB-INF/web.xml</WatchedResource>
    <WatchedResource>${catalina.base}/conf/web.xml</WatchedResource>
    <Resource
                   name="jdbc/sqlserver"
                   driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"

                   auth="Container"
                   type="javax.sql.DataSource"

                   initialSize="3"
                   maxTotal="10"
                   maxIdle="10"
                   maxWaitMillis="30000"

                   poolPreparedStatements="true"

                   validationQuery="select 1"
                   testOnBorrow="true"

                   username="orbeon"
                   password="orbeon"
                   url="sqljdbc42:sqlserver://xx.xx.xx.xx:1433;databaseName=orbeon"/>
 <Resources cachingAllowed="true" cacheMaxSize="100000" />
</Context>

properties-local.xml.TEMPLATE file settings:

C:\Program Files (x86)\Apache Software Foundation\Tomcat 6.0\webapps\orbeon\WEB-INF\resources\config

<properties xmlns:xs="http://www.w3.org/2001/XMLSchema"
            xmlns:oxf="http://www.orbeon.com/oxf/processors">
<property as="xs:string" name="oxf.fr.persistence.sqlserver.datasource" value="sqlserver"/>
</properties>
Reply | Threaded
Open this post in threaded view
|

Re: Save Orbeon Forms in SQL server

Alessandro  Vernet
Administrator
Hi Ramesh,

Here is one to try: the file shouldn't be called properties-local.xml.TEMPLATE, but just properties-local.xml. Can you try renaming it, restart Tomcat, and see if this helps?

Alex
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
Reply | Threaded
Open this post in threaded view
|

Re: Save Orbeon Forms in SQL server

RameshBhat
Hi,

I have tried these settings but they do not seem to work.

Is there any other setting that will let it point to a local db rather than the file system. So when a form is created it is saved in sql server
Also what are the settings that orbeon looks at when reading from sql server.

The logs also do not seem to indicate anything.

Regards
Reply | Threaded
Open this post in threaded view
|

Re: Save Orbeon Forms in SQL server

Alessandro  Vernet
Administrator
Hi Ramesh,

I suspect that the properties-local.xml you're changing isn't in the right place. Try adding a "<" as a first character, so the file starts with "<<". Restart the server. You should get a parsing error. If you don't, you're not changing the right file :).

If this is indeed the problem, check you're changing the right file. If you deploy Orbeon Forms as a .war file, then you need to put the file as WEB-INF/resources/config/properties-local.xml inside the war (e.g. with `zip -u` on the command line). If you deploy Orbeon Forms as an uncompressed war (i.e. `orbeon` directory), then just update the WEB-INF/resources/config/properties-local.xml inside that directory.

Alex
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
Reply | Threaded
Open this post in threaded view
|

Re: Save Orbeon Forms in SQL server

RameshBhat
Hi,

I indeed get an parsing error when I add "<" as a first character, my properties-local.xml is in the following path:

\APACHE\apache-tomcat-8.5.11-windows-x64\apache-tomcat-8.5.11\webapps\orbeon\WEB-INF\resources\config
Content of the file:


<properties xmlns:xs="http://www.w3.org/2001/XMLSchema"
            xmlns:oxf="http://www.orbeon.com/oxf/processors">
<property as="xs:string" name="oxf.fr.persistence.sqlserver.datasource" value="sqlserver"/>


</properties>


With all these settings in my log file as well I see that Orbeon is not making an attempt to try connecting to SQL server, Can you let me know all the settings possible?
Reply | Threaded
Open this post in threaded view
|

Re: Save Orbeon Forms in SQL server

Alessandro  Vernet
Administrator
Hi Ramesh,

Interesting. This is with an unmodified copy of Orbeon Forms 2016.3, correct? Assuming it is, could you set the debugging per the development config described on the page linked below, restart the server, reproduce the problem, and send us the orbeon.log you get after this? 


Alex

On Mon, Mar 6, 2017 at 4:26 AM RameshBhat <[hidden email]> wrote:
Hi,

I indeed get an parsing error when I add "<" as a first character, my
properties-local.xml is in the following path:

\APACHE\apache-tomcat-8.5.11-windows-x64\apache-tomcat-8.5.11\webapps\orbeon\WEB-INF\resources\config
Content of the file:


<properties xmlns:xs="http://www.w3.org/2001/XMLSchema"
            xmlns:oxf="http://www.orbeon.com/oxf/processors">
<property as="xs:string" name="oxf.fr.persistence.sqlserver.datasource"
value="sqlserver"/>


</properties>


With all these settings in my log file as well I see that Orbeon is not
making an attempt to try connecting to SQL server, Can you let me know all
the settings possible?

--
View this message in context: http://discuss.orbeon.com/Save-Orbeon-Forms-in-SQL-server-tp4662228p4662254.html
Sent from the Orbeon Forms community mailing list mailing list archive at Nabble.com.

--
You received this message because you are subscribed to the Google Groups "Orbeon Forms" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].

--
You received this message because you are subscribed to the Google Groups "Orbeon Forms" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
Reply | Threaded
Open this post in threaded view
|

Re: Save Orbeon Forms in SQL server

RameshBhat
Hi Alex,

Thanks for replying.

I have enabled the debug (and restarted the services) and kindly find attached the log. When I create a new form in Orbeon and click on save, it still says document saved successfully. Is there any other setting that will tell orbeon to save it in sql server.

localhost_access_log.txtorbeon.log


Regards
Reply | Threaded
Open this post in threaded view
|

Re: Save Orbeon Forms in SQL server

Alessandro  Vernet
Administrator
Hi Ramesh,

Thank you for the log. Orbeon Forms is indeed using the embedded eXist, as if it didn't pick up your <property as="xs:string" name="oxf.fr.persistence.provider.*.*.*" value="sqlserver"/>. Could you also:

- Attach your properties-local.xml.
- Tell me what exact version of Orbeon Forms you are using.
- Give me the list of the files you have in your WEB-INF/resources/config, i.e. where you have the properties-local.xml.

Alex
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
Reply | Threaded
Open this post in threaded view
|

Re: Save Orbeon Forms in SQL server

RameshBhat
Hi ,

Thanks for replying:

Orbeon version - orbeon-2016.3.201612230117-PE

Tomcat version: apache-tomcat-8.5.11

Attached all files (zipped up) webapps\orbeon\WEB-INF\resources\config

config.zip
Reply | Threaded
Open this post in threaded view
|

Re: Save Orbeon Forms in SQL server

Alessandro  Vernet
Administrator
Remesh,

Thanks for the files. So, in your properties-local.xml, you have:

<property as="xs:string" name="oxf.fr.persistence.sqlserver.datasource" value="sqlserver"/>

…which you don't need. And you're missing:

<property as="xs:string"  name="oxf.fr.persistence.provider.*.*.*" value="sqlserver"/>

… which is needed. So try removing the first property above and add the second. Then restart Tomcat, let's see what happens.

Alex
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
Reply | Threaded
Open this post in threaded view
|

Re: Save Orbeon Forms in SQL server

RameshBhat
Hi,
 
In your second mail thread for the same post you had specified that this entry <property as="xs:string"  name="oxf.fr.persistence.provider.*.*.*" value="sqlserver"/>
 is not required and now ur telling that this entry is required.



Can you confirm again?

Regards
Reply | Threaded
Open this post in threaded view
|

Re: Save Orbeon Forms in SQL server

Alessandro  Vernet
Administrator
Ramesh,

Unless I am missing something, I said "Remove the oxf.fr.persistence.sqlserver.datasource", not "oxf.fr.persistence.provider.*.*.*". So again, you need to keep the property `oxf.fr.persistence.provider.*.*.*`.

The property `oxf.fr.persistence.sqlserver.datasource`is there to specify the name of you SQL Server datasource, in case it is different than `sqlserver`. In general, you don't need to set this property, and just use `jdbc/sqlserver` as the name of the resource in your server.xml or context.xml.

Alex
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
Reply | Threaded
Open this post in threaded view
|

Re: Save Orbeon Forms in SQL server

Rakesh.Naidu
Thank you Ramesh and Alex, I was able to successfully save the data to SQLSERVER based on conversation between you both.

I've uploaded the context.xml, properties-local.xml

properties-local.xml
context.xml

Thank you,
Rakesh
Reply | Threaded
Open this post in threaded view
|

Re: Save Orbeon Forms in SQL server

Alessandro  Vernet
Administrator
Hi Rakesh,

Excellent! I'm glad things worked in the end. This configuration can indeed be tricky, as it involves a few moving parts.

Alex
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet