Grails / Hibernate + Sql server 2008 = compliant?

[Moe-25]

I am thinking about changing my database from mysql to sql server 2008 (don't ask why! :O (mostly because of a good integrated full text search) )

But not sure if grails / hibernate will work. Anyone know? I just got hold of the JDBC driver (only beta, but should work) : 

http://www.microsoft.com/downloads/details.aspx?familyid=f914793a-6fb4-475f-9537-b8fcb776befd&displaylang=en#Overview

How does the settings look like in datasource? 

Other tips of databases with good integrated fulltext search ? 

[Dean Del Ponte-2]

Don't do it.  Have you looked at the Searchable Plugin (http://www.grails.org/Searchable+Plugin)?

- Dean

On Jan 1, 2009, at 3:02 PM, Mohamed wrote:

I am thinking about changing my database from mysql to sql server 2008 (don't ask why! :O (mostly because of a good integrated full text search) )

But not sure if grails / hibernate will work. Anyone know? I just got hold of the JDBC driver (only beta, but should work) : 

http://www.microsoft.com/downloads/details.aspx?familyid=f914793a-6fb4-475f-9537-b8fcb776befd&displaylang=en#Overview

How does the settings look like in datasource? 

Other tips of databases with good integrated fulltext search ? 

[SvenOA]

I have the same question about grails and sql server 2008.
While connecting to the database with groovy works, I am not able to connect with grails / hibernate. I use this connectionstring and the 1.2 JDBC driver:

url = "jdbc:sqlserver://[SERVER]:[Port];databaseName=...;instance=SQLEXPRESS;user=...;password=...;"
dialect = org.hibernate.dialect.SQLServerDialect

Has anybody expierence on this?

[elvanor]

MySQL has a integrated full text search, dont know if it's good or not. The real way to get full text search is to use a good library to do that anyway, I would stick to the Searchable plugin if I were you. Hibernate Search may be interesting too.

Jean-Noel

PS: and Hibernate should work fine with SQL Server 2008 as long as you have a stable JDBC driver. Of course, you would still be crazy to switch :)

On Thu, Jan 1, 2009 at 10:02 PM, Mohamed <[hidden email]> wrote:

I am thinking about changing my database from mysql to sql server 2008 (don't ask why! :O (mostly because of a good integrated full text search) )

But not sure if grails / hibernate will work. Anyone know? I just got hold of the JDBC driver (only beta, but should work) : 

http://www.microsoft.com/downloads/details.aspx?familyid=f914793a-6fb4-475f-9537-b8fcb776befd&displaylang=en#Overview

How does the settings look like in datasource? 

Other tips of databases with good integrated fulltext search ?

[Moe-25]

Sven, I finally went for PostgreSQL 8.3 with built in search support. 

It has great support for various things such as dictionaries, highlighting, ranking etc + it's easy to set up + and fast.

There might be some reading that you might have to do but everything is available on 

http://www.postgresql.org/docs/8.3/interactive/textsearch.html

Everything is working now for me so I happy. 

However, I choose this out of simplicity and I will some day in the future make the switch to Compass/Searchable or Hibernate Search so if you want it correct from the beginning perhaps you should start there instead.

Also be aware that SQL would have to be used instead. You can like I do fetch the ids and reload the instance and I think Searchable does the same thing if you want the whole domain with accosiations.

Good luck / Moe

On Sat, Feb 7, 2009 at 1:32 AM, Jean-Noël Rivasseau <[hidden email]> wrote:

MySQL has a integrated full text search, dont know if it's good or not. The real way to get full text search is to use a good library to do that anyway, I would stick to the Searchable plugin if I were you. Hibernate Search may be interesting too.

Jean-Noel

PS: and Hibernate should work fine with SQL Server 2008 as long as you have a stable JDBC driver. Of course, you would still be crazy to switch :)

On Thu, Jan 1, 2009 at 10:02 PM, Mohamed <[hidden email]> wrote:

I am thinking about changing my database from mysql to sql server 2008 (don't ask why! :O (mostly because of a good integrated full text search) )

But not sure if grails / hibernate will work. Anyone know? I just got hold of the JDBC driver (only beta, but should work) : 

http://www.microsoft.com/downloads/details.aspx?familyid=f914793a-6fb4-475f-9537-b8fcb776befd&displaylang=en#Overview

How does the settings look like in datasource? 

Other tips of databases with good integrated fulltext search ?

[SvenOA]

Jean-Noël Rivasseau-3 wrote

PS: and Hibernate should work fine with SQL Server 2008 as long as you have
a stable JDBC driver. Of course, you would still be crazy to switch :)

Grails / Hibernate work very well with SQL Server 2000 and 2005 and the given url string. And it works well with SQL Server 2008 and groovy and the 1.2 JDBC-Driver.

url = "jdbc:sqlserver://[SERVER]:[Port];databaseName=...;instance=SQLEXPRESS;user=...;password=...;"
dialect = org.hibernate.dialect.SQLServerDialect

But it stopped working with the combination SQL Server 2008 and Grails. Neither the old 1.2 JDBC Driver nor the new 2.0 beta driver works.

I am using the connection url wich worked fine with SQL Server 2000/2005 and tried several variations.
In the stack trace the SQL Server returns, that the user 'sa' has an Authentification Error, though I tried to log as another user. So I guess I am using a false connection url or it might be something hibernate dialect specifc.

I am a bit helpless in this and do not know where to dig further. Perhaps anybody has some experience in this?



P.S.: Switching to SQL Server 2008 might be crazy, but on the other hand its alluring for me because of its spatial capabilities.

[elvanor]

I would tend to agree with you, the spatial capabilities of SQL server is probably the only valid reason for using this DB compared to others.

P.S.: Switching to SQL Server 2008 might be crazy, but on the other hand its
alluring for me because of its spatial capabilities.

[Brian Schlining]

I would tend to agree with you, the spatial capabilities of SQL server is probably the only valid reason for using this DB compared to others.

or if it's the DB that your organization officially supports.

--
~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
Brian Schlining
[hidden email]

[SvenOA]

You are right. And if I would be free in choosing a database, I would follow Mohamed, who suggested to use Postgres - with PostGIS and its superior spatial capabilities.

But the problem still remains: How to get Grails working with SQL Server 2008?
I would be great, if anybody could share his experience in this.

hohonuuli wrote

> I would tend to agree with you, the spatial capabilities of SQL server is
> probably the only valid reason for using this DB compared to others.


or if it's the DB that your organization officially supports.

--
~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
Brian Schlining
[e-mail]

[SvenOA]

Sven Axt wrote

But the problem still remains: How to get Grails working with SQL Server 2008?
I would be great, if anybody could share his experience in this.

Fixed it - a gotcha. When changing config from SQL Server 2005 to 2008 I have overseen, that I defined different (old) password and username as explicit variables at the top of the config.
I had to learn, that Grails doest care about the user and password in the url, when they are defined additional as variables. (And perhaps to read the code better before changing it.)

For all those who like to use SQL Server 2008 Express here is the way I did it with the new JDBC 2.0 beta:

dataSource {
        pooled = true
        driverClassName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
}

[...]
 
// environment specific settings
environments {
        development {
        dataSource {
            dbCreate = 'create-drop' // one of 'create', 'create-drop','update'
            url = "jdbc:sqlserver://[Server];database=[database];port=[Portnumber];instanceName=SQLEXPRESS"
            databaseName = "..."
            username = "..."
            password = "..."
            dialect = org.hibernate.dialect.SQLServerDialect
            //logSql = true
                }
        }
[...]
}