Essential on SQL 2005 Repository - Document

Post Reply
manikvijay
Posts: 20
Joined: 04 Apr 2011, 18:19

Hi,

Do you have a document that takes through step by step configuration for SQL 2005 repository. I tried to configure but i am facing issues like JDBC sql errors. Wondering i might be missing something,let me know if there is one in the site.
User avatar
jonathan.carter
Posts: 1087
Joined: 04 Feb 2009, 15:44

Some members of the community have reported successful use of MS SQL. However, I'm not sure which version of MS SQLthey were using.

From a Protege point of view, there are no specific things to do to use any specific database. For any database, create an empty database with a user name and password for Protege to use. Protege will then connect to the database and create the table that it needs.

I think it would be really helpful to know which JDBC errors that you're receiving. Perhaps you could post the relevant exceptions?
I have seen errors creating indices with MS SQL but that was with Protege 3.3.1 and the database connectors have be re-implemented since then.

Interestingly, the Protege team just posted yesterday about database support and it seems that whereas they used to test with a very wide range of database servers, they are now focussing on MySQL and PostgresSQL, which are the databases that they recommend.

Finally, if possible, it might be worth trying with a later version of the Microsoft SQL server (e.g. 2008).
Hope this helps - but do post your JDBC exceptions.

Jonathan
Essential Project Team
manikvijay
Posts: 20
Joined: 04 Apr 2011, 18:19

Thanks Jonathan.

I made little progress. Got the right driver and configured right in the protege file. Now when I try to connect using protege gui i am getting the below exception.

"The driver was found but it is not possible to create a connection with the driver URL. Perhaps the URL syntax is wrong or the username/password is invalid".

"No suitable driver found for jdbc:microsoft.sqlserver://locahost:1433/essentialdb"

Below are my settings

JDBC Driver Class Name:
com.microsoft.sqlserver.jdbc.SQLServerDriver
JDBC URL:
jdbc:microsoft:sqlserver://localhost:1433/essentialdb


Please let me know if anybody has faced this problem before.

Thanks
User avatar
jonathan.carter
Posts: 1087
Joined: 04 Feb 2009, 15:44

When you say that you configured the driver in the Protege file, you mean that you added the mssql JDBC driver JAR file name to the classpath in the Protege startup?

Alternatively, what I've often found simplest is to rename the MS SQL driver to 'driver1.jar' and drop it into the Protege application folder, e.g. C:\Program Files\Protege_3.4.4

That simplifies the business of Protege finding the driver.

Check that you're using the latest version of the driver, I found a v3 of it (eventually).

The Driver class Name field should be as specified by Microsoft's JDBC driver documentation. The download page describes a Help system that is included with the driver.

One thing to note is that before you attempt to have Protege connect to the database, you need to create that database in SQL Server - 'essentialdb' and allocate a user name and password to connect to that database with write permissions. Protege will create a table(s) in there - 1 per Protege project but cannot create the database itself.

Hope this helps to move things on a bit for you

Jonathan
Essential Project Team
manikvijay
Posts: 20
Joined: 04 Apr 2011, 18:19

No worries. I got it working.
For some reason you have to specify something like this

JDBC URL: jdbc:sqlserver:\\localhost

no need to specify the database or the port name.
But need to configure the SQL username to default database that you have created for essential repository.

I will do a detailed post sooner.
User avatar
jonathan.carter
Posts: 1087
Joined: 04 Feb 2009, 15:44

Great to hear that you got it working - and thanks very much for posting back what the solution is.

This is bound to be of use to other members of the community.

It's a shame that there doesn't seem to be strict consistency between the database vendors in terms of how to use their JDBC drivers.

Thanks again - and it would be great if you could post a more detailed solution to this one!

Jonathan
Essential Project Team
manikvijay
Posts: 20
Joined: 04 Apr 2011, 18:19

Hi,

Now i am running to a different problem.
Before I state my problem, i have couple of questions.

1) I installed the Essential Architecture Manager for server based installation. As per the installation it says an EA Repository .pprj project will be created. But instead metaproject.pprj is created with two projects in it "ESsential AM Repository" and "Essential AM Annotations". What happened to EA Repository.pprj? the installation should have created that as well.

2) Installation says use "Project convert to Format" to host the project you need in database. So i am hosting the EA Repository.pprj or essential_baseline_v1.2? it is so confusing going through the documentation.


So i assumed my metaproject.pprj as my EA Repository.pprj and convert that to protege database. Now i used a protege client to connect to the database and i am getting the following error "java.net.connectionexception:Connection refused:connect -- Serverpanel.log()"

Please let me know if i am missing anything here.

Thanks
-Mani
User avatar
jonathan.carter
Posts: 1087
Joined: 04 Feb 2009, 15:44

Hi Mani,

Question 1)

It is important to understand that the entries in the metaproject.pprj are logical definitions of the repositories that the server will be providing.
'Essential AM Repository' is just a label in the Meta Project for your Essential repository. Note that this will be used within the meta project and also on the server connection dialogs to choose which repository you want to connect to. You can rename this as required.
An actual repository with this filename is NOT created.

Within the metaproject and selecting the Essential AM Repository instance in the list of Projects, the 'Location' field specifies the actual filename for your Essential repository. This should be whatever you have chosen for the filename of your repository (including the full path on the server). By default, this will be 'essential_baseline_v1.2.pprj'

The job of the metaproject is to manage the configuration of the server in terms of the repositories that it is serving and the users who can access those repositories.
The repositories are defined by the 'Project' instances in the metaproject but these definitions only describe the repositories (projects), they do not create them. Hence the name of this repository is 'meta'project.

Question 2)
The persistence approach that you take for your repository (database, Protege files, XML, RDF, etc.) is a choice that can be taken independently of whether the repository will be used in multi-user (server) mode or standalone mode.
This means that this operation to convert the project from one format to another is performed from within that project - NOT the metaproject.
We recommend that if you wish to use your Essential repository in multi-user mode, that you use a database 'backend' - that is the contents of the repository is persisted and managed by a relational database system. Although not strictly necessary, we recommend that you convert your Essential repository project, e.g. by default 'essential_baseline_v.1.2' BEFORE using it via the server.

Some notes on this:
  • I would suggest that you rename your repository file to something that means something to you, e.g. myCompanyEARepository or the like, rather than the 'essential_baseline_v.1.2'
  • You can use the repository in multi-user mode without the use of a relational database 'backend' but you must configure the server to automatically save the repositories that it is serving periodically. This can have a detrimental effect on the performance of the server, hence we do not recommend continuing with file-based repositories in multi-user mode.
  • Changing the project format for your repository has no effect on the server configuration in the metaproject. This means that you can convert from file-based to database backend without changing the metaproject. You will need to restart the server, however.
  • If you change the location or the filename of your Essential Project repository for any reason, e.g. archiving a snapshot version (although there are tools included in Protege to manage this particular scenario), need to move the file for physical infrastructure reasons, you must update the entry for this repository (Project) in the meta project.
  • By default, the Protege server uses the metaproject that resides within the Protege installation. Although you can change this by editing the Protege Server startup configuration, we felt that it was simpler to continue using the default metaproject location and is why the installer drops our starter-for-10 version of the metaproject, which attempts to simplify and guide the setup of your server for hosting the Essential Architecture Manager repository
  • To start with, it's probably worth ignoring the Annotations project (this supports all the change history and additional collaborative working capabilities), which means that you can leave the Annotations Project field of the Essential AM Repository instance in the metaproject empty.
I think it will help if we get your repository working with the database in standalone mode and then work on adding this to the server to use in multiuser mode.

In your last paragraph, I think that's where you're having trouble.
The metaproject should use the file-based format and should NOT be converted to the database.
Rather, it is your Essential Repository, e.g. essential_baseline_v.1.2.pprj that should be converted to the Database format, as described in Section 13 of the multi-user-install-guide or in the article specifically covering how to use a database backend.

I had a look at the documentation but couldn't find a reference to where it says
an EA Repository .pprj project will be created.

as you mention in question 1, so I hope this helps to clarify what is expected in the setup of the server and that I haven't missed something.


Jonathan
Essential Project Team
manikvijay
Posts: 20
Joined: 04 Apr 2011, 18:19

Hi Jonathan,

Thanks for the reply. I did tried the Stand alone it is working fine.

After that i changed to multiuser mode,installation went through well and i am able to create table in SQL server using protege and uploaded my repository.

Now the challenge is when it try to connect using the client.

I ran the run_protege_server script and throws me a warning as :Error at loading project: Essential AM RespositoryError message: class not found: com.microsoft.sqlserver.jdbc.sqlserverdriver - server. initializeProjects()"

I did set the classpath for the driver as well still not working.

Any thoughts..?

Thanks
-Mani
User avatar
jonathan.carter
Posts: 1087
Joined: 04 Feb 2009, 15:44

This certainly seems like a classpath / loader issue.

If you haven't already, I would suggest copying the SQLServer Driver into the Protege installation root folder, e.g. C:/Program Files/Protege_3.4.4 as the file 'driver.jar'

Otherwise, note that the classpath settings for the server are defined in 'run_protege_server.bat', so if you've set the classpath for the stand-alone version, those settings will not be picked up by the server.

You can edit the classpath in 'run_protege_server.bat' but you may find the first approach (copying the driver into the Protege root) simpler.

Let me know how you get on

Jonathan
Essential Project Team
manikvijay
Posts: 20
Joined: 04 Apr 2011, 18:19

Hi Jonathan,

I got it working. Thanks for the help.

However when i try to connect to the Repository from my client (my desktop) the project is taking for ever to load.
The status say "preloading frame values.." and it just stays there for ever.

if i try to open the project using the client in the server itself it takes around 5 mins and its loading up.

well, just a quick background on what i am using and the configuration is

VM Lab manager Virtual machine running windows 2008,SQL 2005, Protege 3.4.6, JDBC driver 3.0.

Please let me know what's causing this delay in loading the project.
User avatar
jonathan.carter
Posts: 1087
Joined: 04 Feb 2009, 15:44

It's worth checking the server logs for any Java exceptions - out of memory in particular.

Thanks for the technical platform details.

Some more questions:

- How large is the repository (you can get a reading on the number of instances in the repository from the Project->Metrics... menu option)?

- How long does it take to open this repository in stand-alone mode? That is, not using the Protege Server, just opening the project file directly.

- Is the database hosted on a remote server? Is this responding as it should be?

I think the server logs should provide the most help with this.
You can find it in <Protege Install>/logs
If you are not getting enough information, the Protege logging properties file in <Protege Install> can be tweaked to provide more detail in selected areas

Hope this helps

Jonathan
Essential Project Team
Post Reply