[GeoNetwork-users] Geonetwork on MS-SQL

Jason_Pickering1 · March 23, 2007, 4:50pm

Hi Everyone,
My apologies for not responding sooner to your mail.

Your procedure for populating the MS-SQL tables is more or less the same as mine, except I used DataDruid to get the table setups from the HSQL setup. It should be the same, and the tables can be created by hand as well, as they are quite simple.

First of all, let me describe our setup. We are using MS-SQL 2000 Enterprise and jakarta-tomcat-5.5.20. Mileage may vary with other versions. I do not have any experience with MSSQL 2005. It should work, but really do not know.

The following additions were made to apache-tomcat-5.5.20\conf\server.xml

This is not the preferred way, but the only way I could get Geonetwork and Intermap to work. Theoretically, these contexts should be added to Tomcats context.xml file, but this configuration would now work if you also need Intermap. I was able to get geonetwork running by changing the context.xml file, but not Intermap. (See an earlier email thread for a bit of discussion on this).

Moving right along...

The following changes were made to geonetwork\web\WEB-INF\config.xml

<resource enabled="true">
      <name>main-db</name>
      <provider>jeeves.resources.dbms.DbmsPool</provider>
      <config>
        <user>YOUR USER GOES HERE</user>
        <password>YOUR PASSWORD GOES HERE</password>
        <driver>net.sourceforge.jtds.jdbc.Driver</driver>
        <url>jdbc:jtds:sqlserver://YOUR SERVER GOES HERE:YOUR PORT GOES HERE/geonetwork</url>
        <poolSize>4</poolSize>
      </config>
    </resource>
  </resources>

As you can see, I am using username/passwords in clear text. This was the only authentication method I could get to work. Windows based authentication (although theoretically supported by JTDS) did not work, and I could not be bothered to get it to work (nor could our sysadmins).

As you can see, I chose to use the JTDS driver from

http://sourceforge.net/project/showfiles.php?group_id=33291

Mileage may vary with other drivers. This file (jtds-1.2.jar) was placed in the geonetwork\web\WEB-INF\lib directory.

This is more or less an extract of the instructions that I provided to our admins to get the app up and running, without how to get Tomcat installed. I assume you have Tomcat up and running though already!

I hope this is of help. We have version 1.4 of geonetwork up and running on www.who.int/geonetwork and it uses older versions of the JDBC connector and an older version of Tomcat, so it would seem that the versions are not so important.

Please let me know if I can be of any more help.

Best regards,
Jason

-----Original Message-----
From: Ivan Renteria Toledo [mailto:ivanrt@anonymised.com]
Sent: 23 March 2007 16:46
To: guillermorl@anonymised.com; Pickering, Jason
Cc: karinavbg@anonymised.com; ponchog@anonymised.com; khrizart@anonymised.com
Subject: RE: Fwd: [GeoNetwork-users] Geonetwork on MS-SQL

Hi Jason

I'm working with Guillermo in the installation of GeoNetwork with SQL
Server,
It s going to help us a lot because this year we have an inventory project,
the problem is that we have to follow some local rules that let us only the
Oracle or the SQL Server options =?
And we had problems with the lenght of metadata using Oracle Express, if we
want to use Geonetwork the last coice is running on SQL Server

- We have installed SQL Server 2005 using the Windows security,
- have runned the sql procedure for MySQL in order to create the tables, the
script that gave me Guillermo have created the tables tables but without
keys
- populated them using an odbc 5 from MySQL
- downloaded the jdbc sqljdbc.jar 1.1 and copied into
geonetwork\web\WEB-INF\lib directory, even added in the classpath variables
- have changed the resources pool config in config.xml

<resource enabled="true">
  <name>main-db</name>
  <provider>jeeves.resources.dbms.DbmsPool</provider>
  <config>
    <driver>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver>
    <url>jdbc:sqlserver://localhost;databaseName=geonetwork;integratedSecurity=true;</url>
  </config>
</resource>

- and restarted Tomcat
but still cannot made the connection

Are we missing something else?
Could you give us an advice?

Thanks in advance
Iván Israel

2007-03-23 09:32:05 | === Starting system

2007-03-23 09:32:05 | Path : C:\Archivos de programa\geonetwork\web\/
2007-03-23 09:32:05 | BaseURL : geonetwork
2007-03-23 09:32:05 | Loading : C:\Archivos de
programa\geonetwork\web\/WEB-INF/config.xml
2007-03-23 09:32:05 | Initializing general configuration...
2007-03-23 09:32:05 | Initializing defaults...
2007-03-23 09:32:05 | Default local is :true
2007-03-23 09:32:05 | Initializing resources...
2007-03-23 09:32:05 | Adding resource : main-db
2007-03-23 09:32:07 | Raised exception while initializing resource. Skipped.
2007-03-23 09:32:07 | Resource : main-db
2007-03-23 09:32:07 | Provider : jeeves.resources.dbms.DbmsPool
2007-03-23 09:32:07 | Exception :
com.microsoft.sqlserver.jdbc.SQLServerException: La conexión TCP/IP al host
ha fallado. java.net.ConnectException: Connection refused: connect
2007-03-23 09:32:07 | Message : La conexión TCP/IP al host ha fallado.
java.net.ConnectException: Connection refused: connect
2007-03-23 09:32:07 | Stack :
com.microsoft.sqlserver.jdbc.SQLServerException: La conexión TCP/IP al host
ha fallado. java.net.ConnectException: Connection refused: connect
  at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown
Source)
  at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(Unknown
Source)
  at
com.microsoft.sqlserver.jdbc.SQLServerConnection.loginWithoutFailover(Unknown
Source)
  at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Source)
  at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)
  at java.sql.DriverManager.getConnection(Unknown Source)
  at java.sql.DriverManager.getConnection(Unknown Source)
  at jeeves.resources.dbms.Dbms.connect(Dbms.java:78)
  at jeeves.resources.dbms.DbmsPool.init(DbmsPool.java:78)
  at
jeeves.server.resources.ProviderManager.register(ProviderManager.java:54)
  at jeeves.server.JeevesEngine.initResources(JeevesEngine.java:366)
  at jeeves.server.JeevesEngine.loadConfigFile(JeevesEngine.java:210)
  at jeeves.server.JeevesEngine.init(JeevesEngine.java:127)
  at jeeves.server.sources.http.JeevesServlet.init(JeevesServlet.java:68)
  at javax.servlet.GenericServlet.init(GenericServlet.java:211)
  at
org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1105)
  at org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:932)
  at
org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:3951)
  at
org.apache.catalina.core.StandardContext.start(StandardContext.java:4225)
  at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1013)
  at org.apache.catalina.core.StandardHost.start(StandardHost.java:718)
  at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1013)
  at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:442)
  at org.apache.catalina.core.StandardService.start(StandardService.java:450)
  at org.apache.catalina.core.StandardServer.start(StandardServer.java:709)
  at org.apache.catalina.startup.Catalina.start(Catalina.java:551)
  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
  at java.lang.reflect.Method.invoke(Unknown Source)
  at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:294)
  at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:432)

2007-03-23 09:32:07 | Initializing services...

From: "Guillermo Ramos" <guillermorl@anonymised.com>
To: "Ivan Renteria Toledo" <ivanrt@anonymised.com>, "Karina Barrionuevo
Gaggiotti" <karinavbg@anonymised.com>, ponchog@anonymised.com, Khrizart
<khrizart@anonymised.com>
Subject: Fwd: [GeoNetwork-users] Geonetwork on MS-SQL
Date: Tue, 20 Mar:17:05 -0600

Aqui les mando el script para la creacion de tablas que me mando Jason,
Ivan
las podrías probar?, dice que va a subir los otros archivos en cuanto pueda

Saludos!!

---------- Forwarded message ----------
From: Pickering, Jason <pickeringj@anonymised.com>
Date: Mar 20,:24 AM
Subject: RE: [GeoNetwork-users] Geonetwork on MS-SQL
To: Guillermo Ramos <guillermorl@anonymised.com>

Hi Guillermo,
I have not tested this yet on another server. Can you try and instantiate
the following?

CREATE TABLE [dbo].[Categories] (
[id] [int] NOT NULL ,
[name] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Help] (
[id] [int] NOT NULL ,
[topic] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[data] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Metadata] (
[id] [int] NOT NULL ,
[schemaId] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[isTemplate] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[createDate] [varchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[lastChangeDate] [varchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[data] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[source] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[sourceUri] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[uuid] [varchar] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Operations] (
[id] [int] NOT NULL ,
[name] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Users] (
[id] [int] NOT NULL ,
[username] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[password] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[surname] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[name] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[profile] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Groups] (
[id] [int] NOT NULL ,
[name] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[email] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[referrer] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[MetadataCateg] (
[metadataId] [int] NOT NULL ,
[categoryId] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OperationAllowed] (
[groupId] [int] NOT NULL ,
[metadataId] [int] NOT NULL ,
[operationId] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[UserGroups] (
[userId] [int] NOT NULL ,
[groupId] [int] NOT NULL
) ON [PRIMARY]
GO

------------------------------
*From:* Guillermo Ramos [mailto:guillermorl@anonymised.com]
*Sent:* 16 March:09
*To:* Pickering, Jason
*Subject:* Re: [GeoNetwork-users] Geonetwork on MS-SQL

Hi Jason, where can I get those files?

Regards

On 3/9/07, Pickering, Jason <pickeringj@anonymised.com> wrote:

Hi Everyone,
In regards to Geonetwork with MS-SQL, i have renamed this email thread, as
there appears to be some interest in it.

I am out of the office at the moment, but will be back next week and will
post the install scripts and schemas here, if that is appropriate.

I have a short installation description as well, that I put together for
our sysadmins describing how to get geonetwork running with Tomcat, MS-SQL
and the JDBC connector for those of us that are working in the the
Microsoft world.

Jeroen, shall I post everything here, or is there a better venue for this?

Regards,
Jason

________________________________

From: Guillermo Ramos [mailto: guillermorl@anonymised.com]
Sent: Friday, March 09, 2007 8:58 AM
To: Pickering, Jason
Subject: Re: [GeoNetwork-users] GeoNetwork 2.1 alpha 2 is out

Hi! it is excelent to make Geonetwork compatible with MS SQL, actually I'm
trying to do that but i had a lot of problems, could you tell me how to
get
those files?

Thank you!!

On 3/8/07, Pickering, Jason <pickeringj@anonymised.com> wrote:

        We have GeoNetwork running on MS-SQL using a JDBC connector.
(http://www.who.int/geonetwork).
We are still using an older version on the production site, but I have one
of the alpha versions and 2.0 running on our development servers.

        I did have to create the tables by hand though.

        I could could put together an an install script for MS-SQL if
there is any interest.

        Regards,
        Jason Pickering

        -----Original Message-----
        From: geonetwork-users-bounces@lists.sourceforge.net [mailto:
geonetwork-users-bounces@lists.sourceforge.net
<mailto:geonetwork-users-bounces@lists.sourceforge.net>
] On Behalf Of Andrea Carboni
        Sent: Thursday, March 08, 2007 8:21 PM
        To: geonetwork-users@lists.sourceforge.net
        Subject: Re: [GeoNetwork-users] GeoNetwork 2.1 alpha 2 is out

        Hi André,

        Postgres is supported in GN 2.1 and there is already a schema
file
to
        create the database. Maybe I forgot to add that option to the
installer.

        Anyway, in the new beta you will not have to choose the dbms at
        installation time but using GAST you will be able to change it
any
time.

        As a general information to all, we are finishing the latest
things,
        fixing many small bugs and doing little improvements all around.
        Maybe we will release the first beta soon.

        Cheers,
        Andrea

        > Hello Andrea Carboni
        >
        > I think it is a great idea to add the possibiltity to use a
JDBC
for
        > PostgreSQL, but I struggle a bit to get it runnin. The Problems
seems to be,
        > that PostgreSQL doesn't know the Charaktertype LONGVARCHAR,
what
during
        > installation stops the build-up of the tables. The problem
seems
to be that
        > PostgreSQL databases don't know the JaceType LONGVARCHAR. Is it
a bug or
        > have I done somethin wrong?
        >
        > Best reagrds
        >
        > André
        >
        >
        >
        > Andrea Carboni-2 wrote:
        > >
        > > Hi all,
        > >
        > > I have just uploaded a new alpha. This release is starting to
resemble to
        > > the
        > > final one. For a full description of what have been
added/changed please
        > > see
        > > the usual docs/changes.txt file. Here are some notes:
        > >
        > > - The harvesting code is finished for the geonetwork node
type. Due to the
        > > new
        > > protocol, you can harvest only from a GN 2.1 node. The web
folder
        > > harvesting
        > > type is not implemented yet even the web interface is fully
working.
        > >
        > > - Many options have been moved to a web form. Once installed,
use
        > > username='admin'
        > > and password='admin' to login and select the admin form.
There you can
        > > find the
        > > link to the web form for the options.
        > >
        > > - The metadata root element for the 19139 has been changed to
MD_Metadata
        > > and now the 'gmd' namespace prefix must be explicit in
order
to have the
        > > editor
        > > working.
        > >
        > > - The editor works, more or less. You can update information
but you
        > > cannot add new
        > > elements. Other work must be done here.
        > >
        > > - The CSW code is in its final form. Please notice that now
the HOST and
        > > PORT values
        > > into the capabilities output are taken from the system
config so, go the
        > > web form
        > > and supply proper values for the 'server' part.
        > >
        > >
        > > We have planned to provide a first beta within first 2 weeks
of February
        > > with all
        > > mandatory parts implemented. This release will have a fully
working editor
        > > with
        > > subtemplates, a localization web form, a new simplified
installer and a
        > > new stand
        > > alone application to execute tasks that could not be handled
by a web
        > > form.
        > >
        > > Happy testing!
        > >
        > > Cheers,
        > > Andrea
        > >
        > >
        > >
        > >
        > >
        > >
        > >
        >

-------------------------------------------------------------------------
        Take Surveys. Earn Cash. Influence the Future of IT
        Join SourceForge.net's Techsay panel and you'll get the chance to
share your
        opinions on IT & business topics through brief surveys-and earn
cash

http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV

        _______________________________________________
        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

-------------------------------------------------------------------------
        Take Surveys. Earn Cash. Influence the Future of IT
        Join SourceForge.net 's Techsay panel and you'll get the chance
to
share your
        opinions on IT & business topics through brief surveys-and earn
cash

http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
        _______________________________________________
        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

--
Guillermo Ramos Leal
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share
your
opinions on IT & business topics through brief surveys-and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
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

--
Guillermo Ramos Leal

--
Guillermo Ramos Leal

_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/