Integrating Orbeon with SQL Server 2014

CSC Australia Pty Limited; Registered Office: 26 Talavera Road, Macquarie Park NSW 2113 Australia; Incorporated in Australia ACN: 008 476 944. CSC - This is a PRIVATE message - If you are not the intended recipient, please delete without copying and kindly advise us by e-mail of the mistake in delivery. NOTE: Regardless of content, this e-mail shall not operate to bind the Company to any order or other contract unless pursuant to explicit written agreement or government initiative expressly permitting the use of e-mail for such purpose.

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

I've posted a comment yesterday on this question, which you also crossposted on Stack Overflow, so I suggest we continuhe the discussion there:

http://stackoverflow.com/questions/40457166/integrating-orbeon-forms-2016-2-1-with-microsoft-sql-server-2014

Alex

Alex,

Thanks for the initial reply.
Have you had time to reveiew the supplied code/logs? any suggestions?

Thanks

Hi Scott,

Sorry about that: somehow I haven't received an email notification from Stack Overflow for your comment, which I rely on to see new comments there :(. I've not followed up on hoila's answer on Stack Overflow.

Alex

Alex,

We are still trying to get our Orbeon form to read from SQL Server.  Do you have any documentation/examples of how to make an Orbeon forms write the Form data to SQL Server.  I'll try to implement a SQL Server 'writing' scenario, and maybe also identify the problem with our SQL Server 'reading' scenario.

Thanks

Hi SM7

I recently got Orbeon working with SQL Express with Adv Services and might be able to help.

Are new forms saving at all into the DB?

Cheers

Hi Eusebio,

Thanks for the help.
I have 6 Orbeon SQL Server tables: orbeon_form_data, orbeon_form_data_attach, orbeon_form_definition, etc.
There is no data in any of these tables.

I have configured Orbeon/Tomcat as per the original post.
But have since added the following into "context.xml".
<ResourceLink global="jdbc/sqlserver" name="jdbc/sqlserver" type="javax.sql.DataSource"/>

I'm not sure how to setup a form to write data into SQL server.

Any help would be appreciated.

Thanks

Hi,

So there are a few places to do things. It sounds like you have already done the jar file in the BIN folder so that's fine.

1. \webapps\orbeon\WEB-INF\resources\config\properties-local.xml

Add the following lines:-



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

2. %tomcat%\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="username"
                   password="password"
                   url="jdbc:sqlserver://localhost:1433;databaseName=orbeon"/>
   
   

<Resources cachingAllowed="true" cacheMaxSize="100000" />
</Context>

I had read a few things about not having this in the server context file but I put it here and removed from server.xml and that got things working correctly, rather than doing a resource link. I only have Orbeon running on the server so am not too worried about the Tomcat config.

Change username/password to match what you have setup on your SQL box, I just put those defaults here for example.

After these settings are done for Tomcat/Orbeon the other bits needed to do is to ensure your SQL is allowing access.

IP Addresses and Ports

TCP/1433 enabled on the IP address you are accessing if not on same local server. So assuming it is on 192.168.x.x then you need to open SQL Server Configuration Manager and check the SQL Server Network Configuration then the Protocols and ensure that under TCP/IP under the IP Address tab, the active and enabled boxes are set to yes on the correct NIC and also the TCP port is set to 1433.

Ensure you SQL is accepting remote connections.

Open MSSQL Server Mgmt Studio, login and then right click on the connected server and go to Properties. Once here go to Connections and ensure the 'Allow remote connections to this server' is ticked.

Ensure Windows and SQL authentication is accepted

I'm assuming that you have a specific user with the appropriate permissions setup on the server and with the right roles/permissions on the database to perform the actions you need. As well as this, you need to tell the server to accept connections other than just Windows Auth Mode. Follow the steps above to get to the DB server properties, navigate to the Security option and select 'SQL Server and Windows Authentication Mode'. Test disconnecting from the server with MSSQL Server Mgmt Studio and then reconnecting with your limited OrbeonDB account.

I think that is all of the steps I went through to get it setup.

Currently we are only using it to store submissions and then we query separately but I'll let you know if we get Orbeon to query the DB for any reason.

I hope this helps.

Thanks

Eusebio,

Thanks for taking the time to reply. That was a very comprehensive email....  Unfortunately, when I implement your suggestions, I now get a "http Status 404 - /orbeon/" error, "The requested resource is not available".

The error seems to be caused by the properties-local.xml file.
If I comment the "properties", then Orbeon does not crash, and I can open FormsBuilder.
If I uncomment the "properties", then Orbeon crashes.

Here is my D:\apache-tomcat-8.5.6\webapps\orbeon\WEB-INF\resources\config\properties-local.xml


<properties xmlns:xs="http://www.w3.org/2001/XMLSchema"
            xmlns:oxf="http://www.orbeon.com/oxf/processors">

</properties>


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


Here is my D:\apache-tomcat-8.5.6\conf\context.xml

<?xml version="1.0" encoding="UTF-8"?>

<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="Orbeon123"
         url="jdbc:sqlserver://10.36.22.11:1433;databaseName=OrbeonForms"/>

<Resources cachingAllowed="true" cacheMaxSize="100000" />

</Context>


I checked the SQL Server settings/configuration.
Everything was OK, except for the "Enabled" setting in the Server Network Configuration. I set it to 'Yes' as per your recommendations.


Any further suggestions.


Thanks

Hi,

It looks like your properties tags in properties-local.xml are not covering everything.

Try the below.



<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.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"/>

</properties>

Cheers

That has fixed my problem. My test form is now saving to the SQL Server database. Thanks for all your advice, it is greatly appreciated.

I still can't get the form to read from the SQL Server database.  So I will continue to investigate this issue.

Thanks

Hi Scott,

You're saying that now writing to SQL Server works, but not reading back the data that was previously written? Or maybe I'm not understanding the problem correctly, as typically, once the database setup is working, it works for both reads and writes.

Alex

Hi Alex,

Erik has fixed my problem, I needed to change the 'Label' in the 'Actions Editor' from upper to lower case.

Thanks

Got it Scott; I'm glad it is now working.

Alex