Using Hermes with Microsoft SQL Server

[Ludovic NAUD]

Hello,

I've come across a few threads talking about tricks to be able to use
Hermes with MSSQL Server, but those threads were quite old
(2006-2007), and it seemed that miscellaneous little bugs were still
to be fixed at that time...

Meantime, did any of you managed to use Hermes with MSSQL Server in a
stable and efficient way (production ready) ?

Due to some new IS policy restrictions, we have to migrate Hermes from
Postgres to MSSQL Server and if someone already made some recent /
working patches to achieve such connectivity, I would be very
interested to hear from you :)

Moreover, MSSQL Server is one of the few major DB engines used widely
in enterprise, I'm sure it would be nice to include such DB
connectivity in standard Hermes package...

Thanks in advance :)

Ludovic.

[florent....@gmail.com]

Hello Ludovic,

Your question is 4 years old but we have an answer :-)

Yes, Hermes can work with SQL Server 2012 with minor configuration and database adaptations. At least AS2 module, we didn't try ebMS2 module.

Here is what we did from a fresh, standard Hermes H2O installation:

- Stop your Hermes instance

- Add the Microsoft SQL JDBC driver (http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774) to webapps/corvus/WEB-INF/lib folder
- You can also delete the original MySQL or PostgreSQL JDBC driver

- Create a new database and a dedicated user with, at least, datareader and datawriter permissions on that database.
- Execute the attached SQL script (mssql_as2.sql). The 3 AS2 standard tables (message, repository and partnership) are created with appropriate column types plus an additional stored procedure which have been necessary to handle the order of pagination parameter of request find_messages_by_history. Note that for an SQL Server prior to 2012, little modifications should be done to that stored procedure.

- Edit file hermes2/plugins/hk.hku.cecid.edi.as2/conf/hk/hku/cecid/edi/as2/conf/as2.module.core.xml and update following parameters:
<parameter name="driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
<parameter name="url" value="jdbc:sqlserver://<hostname>:<port, default 1433>;databaseName=<database name>"/>
<parameter name="username" value="<username>"/>
<parameter name="password" value="<password>"/>
<parameter name="validationQuery" value="SELECT 1"/>

- Edit file hermes2/plugins/hk.hku.cecid.edi.as2/conf/hk/hku/cecid/edi/as2/conf/as2.dao.xml and update following parameters:
<parameter type="finder" name="find_messages_by_status" value="select TOP 100 * from message where upper(status)=upper(?) and upper(message_box)=upper(?)" />
<parameter type="finder" name="find_messages_by_history" value="{?, ? = call find_messages_by_history(?, ?, ?, ?, ?, ?, ?, ?)}" />
<parameter type="finder" name="find_no_of_messages_by_history" value="select count(message_id) from message where message_id like ? and message_box like ? and as2_from like ? and as2_to like ? and principal_id like ? and status like ? and ltrim(rtrim(upper(is_receipt))) = 'FALSE'" />
<parameter type="finder" name="key" value="select * from partnership where upper(as2_from)=upper(?) and upper(as2_to)=upper(?) and ltrim(rtrim(upper(is_disabled)))='FALSE' order by partnership_id" />

- Restart your hermes instance.

You're done!

I hope it may help you, Ludovic :)

[Ludovic NAUD]

Well,

Thank you very much Florent, I really appreciate the effort ! :-)

I will ask my team to have a look at your workaround proposal which seems to cover exactly our needs !

I'm sure it will help the community as well !

Best regards :-)

Ludovic.

[Aaron Walker]

Hi Florent,

Thanks for this contribution can I get your approval to add this script to jentrata-msh (https://github.com/jentrata/jentrata-msh) project. It is licensed under the same license (GPL) as Hermes. 

You could also send me a pull request if you would like to.

cheers

Aaron

--
You received this message because you are subscribed to the Google Groups "Hermes 2.0 Discussion List" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cecid-hermes...@googlegroups.com.
To post to this group, send email to cecid-...@googlegroups.com.
Visit this group at http://groups.google.com/group/cecid-hermes2.
For more options, visit https://groups.google.com/d/optout.
<mssql_as2.sql>