[GeoNetwork-users] Using SQL Server as a backend of the GeoNetwork

Ishtiaq_Ahmed · July 18, 2017, 1:59pm

All,

We are planning on using GeoNetwork as the interface of the GIS project for our organization and SQL Server as the backend. I'm new to the GeoNetwork and I'm wondering if it is even possible to connect SQL Server to GeoNetwork or not. I have read the official GeoNetwork documentation and it talks about using the default database, which is H2 database and it also mentions about supporting other databases, like Oracle, MySQL and other databases, but not SQL. I've not found any good documentation on the internet that gives some specific instructions on how to connect or configure SQL database to GeoNetwork.

Does anyone have any experience connecting SQL and GeoNetwork? If so, could you please post the instructions on how to do that? Thank you.

Ahmed

________________________________

This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager.

This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail.

Jose_Garcia1 · July 18, 2017, 2:52pm

Hi

Check in
http://geonetwork-opensource.org/manuals/trunk/eng/users/maintainer-guide/installing/configuring-database.html?highlight=database#configuring-the-database,
you need to enable SQLServer in WEB-INF/config-node/srv.xml, set the db
connection info in WEB-INF/config-db/jdbc.properties and add to WEB-INF/lib the
sqlserver jdbc libs.

GeoNetwork uses Hibernate/JPA for the database layer, so should be quite
agnostic about the database, but we had issues with Oracle and MySql so
can't guarantee that SQLServer works, you'll need to do some testing. H2
and Postgres are quite tested and work fine.

Regards,
Jose García

On Tue, Jul 18, 2017 at 3:59 PM, Ishtiaq Ahmed <Ishtiaq.Ahmed@anonymised.com> wrote:

All,

We are planning on using GeoNetwork as the interface of the GIS project
for our organization and SQL Server as the backend. I'm new to the
GeoNetwork and I'm wondering if it is even possible to connect SQL Server
to GeoNetwork or not. I have read the official GeoNetwork documentation and
it talks about using the default database, which is H2 database and it
also mentions about supporting other databases, like Oracle, MySQL and
other databases, but not SQL. I've not found any good documentation on the
internet that gives some specific instructions on how to connect or
configure SQL database to GeoNetwork.

Does anyone have any experience connecting SQL and GeoNetwork? If so,
could you please post the instructions on how to do that? Thank you.

Ahmed

________________________________

This email and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed.
If you have received this email in error please notify the system manager.

This message contains confidential information and is intended only for
the individual named. If you are not the named addressee you should not
disseminate, distribute or copy this e-mail.

------------------------------------------------------------
------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
GeoNetwork-users mailing list
GeoNetwork-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geonetwork-users
GeoNetwork OpenSource is maintained at http://sourceforge.net/
projects/geonetwork

--

*Vriendelijke groeten / Kind regards,Jose García
<http://www.geocat.net/>Veenderweg 136721 WD BennekomThe NetherlandsT: +31
(0)318 416664 <+31318416664> <https://www.facebook.com/geocatbv>
<https://twitter.com/geocat_bv>
<https://plus.google.com/u/1/+GeocatNetbv/posts>Please consider the
environment before printing this email.*

Oscar_Diago_Alonso · July 18, 2017, 2:55pm

Hi, Ishtiaq

It's possible to connect SQL Server with GeoNetwork. It's the database we use.

I'll try to explain the steps:

First of all, stop the Tomcat where GeoNetwork has been installed.

1 - Download the SQL Server Driver: https://www.microsoft.com/en-us/download/details.aspx?id=54671 (sqljdbc_4.2.8112.100_enu.tar.gz)
2 - Unpack the file and copy " sqljdbc42.jar". Paste that jar in the lib directory of your Apache Tomcat.
3 - Delete all H2 related files from the bin folder of your Apache Tomcat (if there is any): geonetwork.h2.db, geonetwork.lock.db, geonetwork.trace.db
4 - Modify the file "srv.xml": C:\apache-tomcat-8.5.9\webapps\geonetwork\WEB-INF\config-node
Comment the line: “<import resource="../config-db/h2.xml"/>” and uncomment the line with the SQL Server reference
5 - Modify the file "jdbc.properties": C:\apache-tomcat-8.5.9\webapps\geonetwork\WEB-INF\config-db\jdbc.properties
You must change username, password, database, port and host
6 - Start Tomcat.

Stop Tomcat again.

7 - Fix the SQL: C:\apache-tomcat-8.5.9\webapps\geonetwork\WEB-INF\classes\setup\sql\
Modify " data-db-default.sql": C:\apache-tomcat-8.5.9\webapps\geonetwork\WEB-INF\classes\setup\sql\data --> the word true needs quotes: 'true'

Star Tomcat again

8 - Table "Services" is not created . Open your SQL Server Management and run the following script:

CREATE TABLE Services
  (
    id int,
    name varchar(64) not null,
    class varchar(1048) not null,
    description varchar(1048),

    primary key(id)
  );
ALTER TABLE ServiceParameters ADD FOREIGN KEY (service) REFERENCES services (id);

Now you should be able to use GeoNetwork with SQL Server.

Best regards.

Óscar Diago Alonso
Desarrollador GIS / GIS Developer

e-mail: mailto:oscar.diago@…1602…
Calle Pintor Clemente Arraiz, 3 Bajo 01008 Vitoria-Gasteiz
Geograma. Expertos en GeoInformación

www.geograma.com

De acuerdo con la LO 15/1999, de Protección de Datos, y la Ley 34/2002 (LSSICE), se informa que su dirección de correo electrónico figura en nuestros archivos, al objeto de mantener el contacto y remitirle publicidad e información empresarial sobre las actividades de Geograma, S.L. Si no desea recibir publicidad e información empresarial sobre las actividades de Geograma SL le rogamos que nos lo comunique a la siguiente dirección de correo electrónico: geograma@...1602... Podrá ejercer los derechos de acceso, cancelación, rectificación y oposición reconocidos por la LOPD mediante solicitud escrita y firmada a Geograma, S.L. a la siguiente dirección C/ Castillo de Eskibel, 2 Bajo de Vitoria (C.P. 01007), adjuntando en todo caso copia de su DNI. En caso de modificación de sus datos deberá notificarlo en la misma dirección, declinando toda responsabilidad para la empresa en caso de no hacerlo. Si no es Ud. el destinatario y ha recibido este mensaje por error, agradecemos que lo comunique inmediatamente al remitente sin difundir, almacenar o copiar su contenido.

-----Mensaje original-----
De: Ishtiaq Ahmed [mailto:Ishtiaq.Ahmed@…1624…]
Enviado el: martes, 18 de julio de 2017 16:00
Para: geonetwork-users@lists.sourceforge.net
Asunto: Re: [GeoNetwork-users] Using SQL Server as a backend of the GeoNetwork

All,

We are planning on using GeoNetwork as the interface of the GIS project for our organization and SQL Server as the backend. I'm new to the GeoNetwork and I'm wondering if it is even possible to connect SQL Server to GeoNetwork or not. I have read the official GeoNetwork documentation and it talks about using the default database, which is H2 database and it also mentions about supporting other databases, like Oracle, MySQL and other databases, but not SQL. I've not found any good documentation on the internet that gives some specific instructions on how to connect or configure SQL database to GeoNetwork.

Does anyone have any experience connecting SQL and GeoNetwork? If so, could you please post the instructions on how to do that? Thank you.

Ahmed

________________________________

This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager.

This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail.

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot _______________________________________________
GeoNetwork-users mailing list
GeoNetwork-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geonetwork-users
GeoNetwork OpenSource is maintained at http://sourceforge.net/projects/geonetwork

Dr_Marco_Lechner · July 19, 2017, 6:16am

Hi Oscar,

if you those necessary workarounds (steps 7 and 8), would you have a
heart and file a bug including your workaround that this can be fixed in
future revisions? Or this this already fixed in recent master code?

Marco

Am 18.07.2017 um 16:55 schrieb Oscar Diago Alonso:

Hi, Ishtiaq

It's possible to connect SQL Server with GeoNetwork. It's the database we use.

I'll try to explain the steps:

First of all, stop the Tomcat where GeoNetwork has been installed.

1 - Download the SQL Server Driver: https://www.microsoft.com/en-us/download/details.aspx?id=54671 (sqljdbc_4.2.8112.100_enu.tar.gz)
2 - Unpack the file and copy " sqljdbc42.jar". Paste that jar in the lib directory of your Apache Tomcat.
3 - Delete all H2 related files from the bin folder of your Apache Tomcat (if there is any): geonetwork.h2.db, geonetwork.lock.db, geonetwork.trace.db
4 - Modify the file "srv.xml": C:\apache-tomcat-8.5.9\webapps\geonetwork\WEB-INF\config-node
     Comment the line: “<import resource="../config-db/h2.xml"/>” and uncomment the line with the SQL Server reference
5 - Modify the file "jdbc.properties": C:\apache-tomcat-8.5.9\webapps\geonetwork\WEB-INF\config-db\jdbc.properties
     You must change username, password, database, port and host
6 - Start Tomcat.

Stop Tomcat again.

7 - Fix the SQL: C:\apache-tomcat-8.5.9\webapps\geonetwork\WEB-INF\classes\setup\sql\
     Modify " data-db-default.sql": C:\apache-tomcat-8.5.9\webapps\geonetwork\WEB-INF\classes\setup\sql\data --> the word true needs quotes: 'true'

Star Tomcat again

8 - Table "Services" is not created . Open your SQL Server Management and run the following script:

CREATE TABLE Services
  (
    id int,
    name varchar(64) not null,
    class varchar(1048) not null,
    description varchar(1048),

    primary key(id)
  );
ALTER TABLE ServiceParameters ADD FOREIGN KEY (service) REFERENCES services (id);

Now you should be able to use GeoNetwork with SQL Server.

Best regards.

Óscar Diago Alonso
Desarrollador GIS / GIS Developer

e-mail: mailto:oscar.diago@anonymised.com
Calle Pintor Clemente Arraiz, 3 Bajo 01008 Vitoria-Gasteiz
Geograma. Expertos en GeoInformación

www.geograma.com

De acuerdo con la LO 15/1999, de Protección de Datos, y la Ley 34/2002 (LSSICE), se informa que su dirección de correo electrónico figura en nuestros archivos, al objeto de mantener el contacto y remitirle publicidad e información empresarial sobre las actividades de Geograma, S.L. Si no desea recibir publicidad e información empresarial sobre las actividades de Geograma SL le rogamos que nos lo comunique a la siguiente dirección de correo electrónico: geograma@anonymised.com Podrá ejercer los derechos de acceso, cancelación, rectificación y oposición reconocidos por la LOPD mediante solicitud escrita y firmada a Geograma, S.L. a la siguiente dirección C/ Castillo de Eskibel, 2 Bajo de Vitoria (C.P. 01007), adjuntando en todo caso copia de su DNI. En caso de modificación de sus datos deberá notificarlo en la misma dirección, declinando toda responsabilidad para la empresa en caso de no hacerlo. Si no es Ud. el destinatario y ha recibido este mensaje por error, agradecemos que lo comunique inmediatamente al remitente sin difundir, almacenar o copiar su contenido.

-----Mensaje original-----
De: Ishtiaq Ahmed [mailto:Ishtiaq.Ahmed@anonymised.com]
Enviado el: martes, 18 de julio de 2017 16:00
Para: geonetwork-users@lists.sourceforge.net
Asunto: Re: [GeoNetwork-users] Using SQL Server as a backend of the GeoNetwork

All,

We are planning on using GeoNetwork as the interface of the GIS project for our organization and SQL Server as the backend. I'm new to the GeoNetwork and I'm wondering if it is even possible to connect SQL Server to GeoNetwork or not. I have read the official GeoNetwork documentation and it talks about using the default database, which is H2 database and it also mentions about supporting other databases, like Oracle, MySQL and other databases, but not SQL. I've not found any good documentation on the internet that gives some specific instructions on how to connect or configure SQL database to GeoNetwork.

Does anyone have any experience connecting SQL and GeoNetwork? If so, could you please post the instructions on how to do that? Thank you.

Ahmed

________________________________

This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager.

This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail.

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot _______________________________________________
GeoNetwork-users mailing list
GeoNetwork-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geonetwork-users
GeoNetwork OpenSource is maintained at http://sourceforge.net/projects/geonetwork
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
GeoNetwork-users mailing list
GeoNetwork-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geonetwork-users
GeoNetwork OpenSource is maintained at http://sourceforge.net/projects/geonetwork

Luke · July 19, 2017, 6:56am

I can confirm that Oscar's listed steps are required for a MSSQL db
implementation. I implement the same steps.

On Wed, Jul 19, 2017 at 4:16 PM, Dr. Marco Lechner <mlechner@anonymised.com> wrote:

Hi Oscar,

if you those necessary workarounds (steps 7 and 8), would you have a
heart and file a bug including your workaround that this can be fixed in
future revisions? Or this this already fixed in recent master code?

Marco

Am 18.07.2017 um 16:55 schrieb Oscar Diago Alonso:
> Hi, Ishtiaq
>
> It's possible to connect SQL Server with GeoNetwork. It's the database
we use.
>
> I'll try to explain the steps:
>
> First of all, stop the Tomcat where GeoNetwork has been installed.
>
> 1 - Download the SQL Server Driver: https://www.microsoft.com/en-
us/download/details.aspx?id=54671 (sqljdbc_4.2.8112.100_enu.tar.gz)
> 2 - Unpack the file and copy " sqljdbc42.jar". Paste that jar in the lib
directory of your Apache Tomcat.
> 3 - Delete all H2 related files from the bin folder of your Apache
Tomcat (if there is any): geonetwork.h2.db, geonetwork.lock.db,
geonetwork.trace.db
> 4 - Modify the file "srv.xml": C:\apache-tomcat-8.5.9\
webapps\geonetwork\WEB-INF\config-node
> Comment the line: “<import resource="../config-db/h2.xml"/>” and
uncomment the line with the SQL Server reference
> 5 - Modify the file "jdbc.properties": C:\apache-tomcat-8.5.9\
webapps\geonetwork\WEB-INF\config-db\jdbc.properties
> You must change username, password, database, port and host
> 6 - Start Tomcat.
>
> Stop Tomcat again.
>
> 7 - Fix the SQL: C:\apache-tomcat-8.5.9\webapps\geonetwork\WEB-INF\
classes\setup\sql\
> Modify " data-db-default.sql": C:\apache-tomcat-8.5.9\
webapps\geonetwork\WEB-INF\classes\setup\sql\data --> the word true needs
quotes: 'true'
>
> Star Tomcat again
>
> 8 - Table "Services" is not created . Open your SQL Server Management
and run the following script:
>
> CREATE TABLE Services
> (
> id int,
> name varchar(64) not null,
> class varchar(1048) not null,
> description varchar(1048),
>
> primary key(id)
> );
> ALTER TABLE ServiceParameters ADD FOREIGN KEY (service) REFERENCES
services (id);
>
>
> Now you should be able to use GeoNetwork with SQL Server.
>
> Best regards.
>
> Óscar Diago Alonso
> Desarrollador GIS / GIS Developer
>
> e-mail: mailto:oscar.diago@anonymised.com
> Calle Pintor Clemente Arraiz, 3 Bajo 01008 Vitoria-Gasteiz
> Geograma. Expertos en GeoInformación
>
>
> www.geograma.com
>
> De acuerdo con la LO 15/1999, de Protección de Datos, y la Ley 34/2002
(LSSICE), se informa que su dirección de correo electrónico figura en
nuestros archivos, al objeto de mantener el contacto y remitirle
publicidad e información empresarial sobre las actividades de Geograma,
S.L. Si no desea recibir publicidad e información empresarial sobre las
actividades de Geograma SL le rogamos que nos lo comunique a la siguiente
dirección de correo electrónico: geograma@anonymised.com Podrá ejercer los
derechos de acceso, cancelación, rectificación y oposición reconocidos por
la LOPD mediante solicitud escrita y firmada a Geograma, S.L. a la
siguiente dirección C/ Castillo de Eskibel, 2 Bajo de Vitoria (C.P.
01007), adjuntando en todo caso copia de su DNI. En caso de modificación de
sus datos deberá notificarlo en la misma dirección, declinando toda
responsabilidad para la empresa en caso de no hacerlo. Si no es Ud. el
destinatario y ha recibido este mensaje por error, agradecemos que lo
comunique inmediatamente al remitente sin difundir, almacenar o copiar su
contenido.
>
> -----Mensaje original-----
> De: Ishtiaq Ahmed [mailto:Ishtiaq.Ahmed@anonymised.com]
> Enviado el: martes, 18 de julio de 2017 16:00
> Para: geonetwork-users@lists.sourceforge.net
> Asunto: Re: [GeoNetwork-users] Using SQL Server as a backend of the
GeoNetwork
>
> All,
>
> We are planning on using GeoNetwork as the interface of the GIS project
for our organization and SQL Server as the backend. I'm new to the
GeoNetwork and I'm wondering if it is even possible to connect SQL Server
to GeoNetwork or not. I have read the official GeoNetwork documentation and
it talks about using the default database, which is H2 database and it
also mentions about supporting other databases, like Oracle, MySQL and
other databases, but not SQL. I've not found any good documentation on the
internet that gives some specific instructions on how to connect or
configure SQL database to GeoNetwork.
>
> Does anyone have any experience connecting SQL and GeoNetwork? If so,
could you please post the instructions on how to do that? Thank you.
>
> Ahmed
>
>
>
>
>
> ________________________________
>
> This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they are
addressed. If you have received this email in error please notify the
system manager.
>
> This message contains confidential information and is intended only for
the individual named. If you are not the named addressee you should not
disseminate, distribute or copy this e-mail.
>
> ------------------------------------------------------------
------------------
> Check out the vibrant tech community on one of the world's most engaging
tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
> GeoNetwork-users mailing list
> GeoNetwork-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/geonetwork-users
> GeoNetwork OpenSource is maintained at http://sourceforge.net/
projects/geonetwork
> ------------------------------------------------------------
------------------
> Check out the vibrant tech community on one of the world's most
> engaging tech sites, Slashdot.org! http://sdm.link/slashdot
> _______________________________________________
> GeoNetwork-users mailing list
> GeoNetwork-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/geonetwork-users
> GeoNetwork OpenSource is maintained at http://sourceforge.net/
projects/geonetwork
>

------------------------------------------------------------
------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
GeoNetwork-users mailing list
GeoNetwork-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geonetwork-users
GeoNetwork OpenSource is maintained at http://sourceforge.net/
projects/geonetwork

--

*Luke Bassett*
* <lukembassett@anonymised.com>*Melbourne Australia