Quantcast

Integrating Orbeon with SQL Server 2014

classic Classic list List threaded Threaded
14 messages Options
SM7
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Integrating Orbeon with SQL Server 2014

SM7

I'm unable to connect/integrate Orbeon Froms with MS SQL Server.

 

Does anyone have a step-by-step guide to configuring Orbeon Forms with SQL Server?

 

My current environment:

-    Orbeon 2016.2.1

-    Tomcat 8.5.6

-    jdk 1.8.0_112

    The above applications are installed on a server running Windows Server 2012 R2 Standard.

 

I have MS SQL Server 2014 installed on a separate server running Windows Server 2012 R2 Standard

 

I have extracted the orbeon war file into D:\apache-tomcat-8.5.6\webapps\orbeon

 

Tomcat is running successfully. I can access the apache Tomcat/8.5.6 home page.

 

Orbeon is running successfully. I can access the orbeon (Forms builder) home page. I am able to build a form (which does not integrate with SQL Server).

 

I'm attempting to use: sqljdbc42.jar This has been copied into "D:\apache-tomcat-8.5.6\lib" and also "D:\apache-tomcat-8.5.6\webapps\orbeon\WEB-INF\lib"

 

I've updated "D:\apache-tomcat-8.5.6\conf\server.xml" with the following:

 

<--Resource

     name="jdbc/sqlserver"

     driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"

     auth="Container"

     type="javax.sql.DataSource"

     initialSize="3"

     maxIdle="20"

     poolPreparedStatements="true"

     validationQuery="select 1"

     testOnBorrow="true"

     username="xxxxxxxx"

     password="xxxxxxxx"

     url="jdbc:sqlserver://<IP Address>:1433;database=OrbeonForms"/-->

 

I've run the ddl to create the Orbeon Forms tables, etc in my database: https://github.com/orbeon/orbeon-forms/blob/master/form-runner/src/main/resources/apps/fr/persistence/relational/ddl/sqlserver-2016_2.sql

 

I've attempted to implement the following example, but cannot get it to work. https://doc.orbeon.com/form-builder/database-services.html

 

The drop down box appears in the form, but does not list any of the content from the database.

 

Have I missed some configuration steps in the above setup?

 

Thanks

 

 


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].
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Integrating Orbeon with SQL Server 2014

Alessandro  Vernet
Administrator
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
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
SM7
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Integrating Orbeon with SQL Server 2014

SM7
Alex,

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

Thanks
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Integrating Orbeon with SQL Server 2014

Alessandro  Vernet
Administrator
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
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
SM7
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Integrating Orbeon with SQL Server 2014

SM7
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Integrating Orbeon with SQL Server 2014

Eusebio
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
SM7
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Integrating Orbeon with SQL Server 2014

SM7
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Integrating Orbeon with SQL Server 2014

Eusebio
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


SM7
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Integrating Orbeon with SQL Server 2014

SM7
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




Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Integrating Orbeon with SQL Server 2014

Eusebio
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
SM7
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Integrating Orbeon with SQL Server 2014

SM7
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Integrating Orbeon with SQL Server 2014

Alessandro  Vernet
Administrator
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
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
SM7
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Integrating Orbeon with SQL Server 2014

SM7
Hi Alex,

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

Thanks
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Integrating Orbeon with SQL Server 2014

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

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