Re: Using SQL Server as a backend of the GeoNetwork

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Using SQL Server as a backend of the GeoNetwork

iahmed
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geonetwork-users
GeoNetwork OpenSource is maintained at http://sourceforge.net/projects/geonetwork
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Using SQL Server as a backend of the GeoNetwork

Jose Garcia
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 <[hidden email]> 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
> [hidden email]
> 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.*
------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geonetwork-users
GeoNetwork OpenSource is maintained at http://sourceforge.net/projects/geonetwork
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Using SQL Server as a backend of the GeoNetwork

Oscar Diago Alonso
In reply to this post by iahmed
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:[hidden email]
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: [hidden email] 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:[hidden email]]
Enviado el: martes, 18 de julio de 2017 16:00
Para: [hidden email]
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
[hidden email]
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geonetwork-users
GeoNetwork OpenSource is maintained at http://sourceforge.net/projects/geonetwork
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Using SQL Server as a backend of the GeoNetwork

Dr. Marco Lechner
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:[hidden email]
> 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: [hidden email] 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:[hidden email]]
> Enviado el: martes, 18 de julio de 2017 16:00
> Para: [hidden email]
> 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
> [hidden email]
> 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
> [hidden email]
> 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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geonetwork-users
GeoNetwork OpenSource is maintained at http://sourceforge.net/projects/geonetwork

0xD733400E.asc (3K) Download Attachment
smime.p7s (6K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Using SQL Server as a backend of the GeoNetwork

Luke-2
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 <[hidden email]> 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:[hidden email]
> > 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: [hidden email] 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:[hidden email]]
> > Enviado el: martes, 18 de julio de 2017 16:00
> > Para: [hidden email]
> > 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
> > [hidden email]
> > 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
> > [hidden email]
> > 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
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/geonetwork-users
> GeoNetwork OpenSource is maintained at http://sourceforge.net/
> projects/geonetwork
>
>


--

*Luke Bassett*
* <[hidden email]>*Melbourne Australia
------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geonetwork-users
GeoNetwork OpenSource is maintained at http://sourceforge.net/projects/geonetwork
Loading...