Save Orbeon Forms in SQL server

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.

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

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>

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

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

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

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?

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

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

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

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

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

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

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

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

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