Using Microsoft SQL Server with Grails

[kellygreer1]

Can anyone give me some guidance on getting Grails to work with a MS
SQL Server?

grails run-app spits outs tons of output that I haven't been able to
capture the first errors from the Windows DOS box.

I have downloaded the sqljdbc.jar from Microsoft.
I have it referenced in the classpath. Should I do this ... or is
there some "plug-ins" type folder.

Not sure what the DataSource.groovy file should look like.
I have the driver referenced.
driverClassName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"

Any pages/guides that get into this?

Thanks guys,
Kelly

Kelly...@nospam.com
replace nospam with yahoo

[Jesse O'Neill-Oine]

I've never done that, but if I needed to, I'd probably start by searching how well Hibernate works with MSSql.  If you can find an example connection url and then you put the jar file in your lib folder you should be done.

Jesse

--
----------------------------------------------------------
Jesse O'Neill-Oine // je...@refactr.com
Refactr LLC // http://refactr.com
mobile // 612-670-5037
----------------------------------------------------------

[kellygreer1]

Cool. I'll do that.

Any way to use log4j or some other mechanism to log the errors coming
from the:
grails run-app

Kelly

On Jan 4, 2:53 pm, "Jesse O'Neill-Oine" <je...@refactr.com> wrote:
> I've never done that, but if I needed to, I'd probably start by searching
> how well Hibernate works with MSSql. If you can find an example connection
> url and then you put the jar file in your lib folder you should be done.
>
> Jesse
>

> On Jan 4, 2008 1:41 PM, kellygreer1 <kellygre...@yahoo.com> wrote:
>
>
>
>
>
> > Can anyone give me some guidance on getting Grails to work with a MS
> > SQL Server?
>
> > grails run-app spits outs tons of output that I haven't been able to
> > capture the first errors from the Windows DOS box.
>
> > I have downloaded the sqljdbc.jar from Microsoft.
> > I have it referenced in the classpath. Should I do this ... or is
> > there some "plug-ins" type folder.
>
> > Not sure what the DataSource.groovy file should look like.
> > I have the driver referenced.
> > driverClassName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
>
> > Any pages/guides that get into this?
>
> > Thanks guys,
> > Kelly
>

> > KellyGre...@nospam.com

> > replace nospam with yahoo
>
> --
> ----------------------------------------------------------
> Jesse O'Neill-Oine // je...@refactr.com

> Refactr LLC //http://refactr.com
> mobile // 612-670-5037
> ----------------------------------------------------------

[Todd McGrath]

Hi,

You've probably found an answer by now, but I'm running Grails 1 RC1
with MS SQL 2005 on a current projects-

DataSource.groovy:
dataSource {
pooled = false
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
username = "groovymn"
password = "groovymn"
configClass =
org.codehaus.groovy.grails.orm.hibernate.cfg.GrailsAnnotationConfiguration.class
}
....

Skip the configClass variable if you don't use annotations

sqljdbc.jar in lib

So, nothing really out of the ordinary to work with MS SQL

HTH,
Todd McGrath

[kellygreer1]

Tried that. Hmmm. Still getting errors. The first errors (the
important ones) are scrolling off the DOS box.

Any ideas?

I'm not using annotations.

Kelly

[kellygreer1]

Made it a little further. Got the connection URL correct and then I
was able to get to "index" method of my controller.

I have a table setup in the database called Books with 3 fields
id, title, author

My domain class is
class Book {
int id
String title
String author
}

My controller is doing auto-scaffolding.

When I hit the index method I get the error:
Message: could not execute query
Caused by: could not execute query; nested exception is
org.hibernate.exception.SQLGrammarException: could not execute query
Class: Unknown
At Line: [-1]
Code Snippet:

Not very helpful.
How do I troubleshoot this?

Kelly
PS more error

Grails Runtime Exception
Error Details
Message: could not execute query
Caused by: could not execute query; nested exception is
org.hibernate.exception.SQLGrammarException: could not execute query
Class: Unknown
At Line: [-1]
Code Snippet:
Stack Trace

org.springframework.dao.InvalidDataAccessResourceUsageException: could
not execute query; nested exception is
org.hibernate.exception.SQLGrammarException: could not execute query

at
org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:
613)

at
org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:
412)

at
org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:
378)

at
org.springframework.orm.hibernate3.HibernateTemplate.executeFind(HibernateTemplate.java:
342)

at
org.codehaus.groovy.grails.scaffolding.DefaultScaffoldDomain.list(DefaultScaffoldDomain.java:
112)

at
org.codehaus.groovy.grails.scaffolding.DefaultScaffoldRequestHandler.handleList(DefaultScaffoldRequestHandler.java:
91)

at org.codehaus.groovy.grails.scaffolding.DefaultGrailsScaffolder
$ListAction.call(DefaultGrailsScaffolder.java:112)

at groovy.lang.Closure.call(Closure.java:287)

at
org.codehaus.groovy.grails.web.servlet.mvc.SimpleGrailsControllerHelper.handleAction(SimpleGrailsControllerHelper.java:
526)

at
org.codehaus.groovy.grails.web.servlet.mvc.SimpleGrailsControllerHelper.executeAction(SimpleGrailsControllerHelper.java:
399)

at
org.codehaus.groovy.grails.web.servlet.mvc.SimpleGrailsControllerHelper.handleURI(SimpleGrailsControllerHelper.java:
240)

at
org.codehaus.groovy.grails.web.servlet.mvc.SimpleGrailsControllerHelper.handleURI(SimpleGrailsControllerHelper.java:
152)

at
org.codehaus.groovy.grails.web.servlet.mvc.SimpleGrailsController.handleRequest(SimpleGrailsController.java:
88)

at
org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:
48)

at
org.codehaus.groovy.grails.web.servlet.GrailsDispatcherServlet.doDispatch(GrailsDispatcherServlet.java:
238)

at
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:
808)

at
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:
476)

at
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:
431)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)

at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:
487)

at
org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:
367)

at
org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:
216)

at
org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:
181)

at
org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:
712)

at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:
405)

at org.mortbay.jetty.servlet.Dispatcher.forward(Dispatcher.java:268)

at org.mortbay.jetty.servlet.Dispatcher.forward(Dispatcher.java:126)

at
org.codehaus.groovy.grails.web.util.WebUtils.forwardRequestForUrlMappingInfo(WebUtils.java:
194)

at
org.codehaus.groovy.grails.web.util.WebUtils.forwardRequestForUrlMappingInfo(WebUtils.java:
181)

at
org.codehaus.groovy.grails.web.mapping.filter.UrlMappingsFilter.doFilterInternal(UrlMappingsFilter.java:
116)

at
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:
75)

at org.mortbay.jetty.servlet.ServletHandler
$CachedChain.doFilter(ServletHandler.java:1089)

at
com.opensymphony.module.sitemesh.filter.PageFilter.parsePage(PageFilter.java:
119)

at
com.opensymphony.module.sitemesh.filter.PageFilter.doFilter(PageFilter.java:
55)

at org.mortbay.jetty.servlet.ServletHandler
$CachedChain.doFilter(ServletHandler.java:1089)

at
org.codehaus.groovy.grails.web.servlet.filter.GrailsReloadServletFilter.doFilterInternal(GrailsReloadServletFilter.java:
153)

at
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:
75)

at org.mortbay.jetty.servlet.ServletHandler
$CachedChain.doFilter(ServletHandler.java:1089)

at
org.codehaus.groovy.grails.web.servlet.mvc.GrailsWebRequestFilter.doFilterInternal(GrailsWebRequestFilter.java:
58)

at
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:
75)

at org.mortbay.jetty.servlet.ServletHandler
$CachedChain.doFilter(ServletHandler.java:1089)

at
org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:
96)

at
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:
75)

at
org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:
183)

at
org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:
138)

at org.mortbay.jetty.servlet.ServletHandler
$CachedChain.doFilter(ServletHandler.java:1089)

at
org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:
365)

at
org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:
216)

at
org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:
181)

at
org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:
712)

at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:
405)

at
org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:
139)

at org.mortbay.jetty.Server.handle(Server.java:295)

at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:
503)

at org.mortbay.jetty.HttpConnection
$RequestHandler.headerComplete(HttpConnection.java:827)

at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:511)

at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:210)

at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:379)

at
org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:
361)

at org.mortbay.thread.BoundedThreadPool
$PoolThread.run(BoundedThreadPool.java:442)

Caused by: org.hibernate.exception.SQLGrammarException: could not
execute query

at
org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:
67)

at
org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:
43)

at org.hibernate.loader.Loader.doList(Loader.java:2223)

at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)

at org.hibernate.loader.Loader.list(Loader.java:2099)

at
org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:
94)

at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)

at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)

at org.codehaus.groovy.grails.scaffolding.DefaultScaffoldDomain
$1.doInHibernate(DefaultScaffoldDomain.java:135)

at
org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:
373)

... 58 more

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid
object name 'book'.

at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown
Source)

at
com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown
Source)

at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown
Source)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement
$PrepStmtExecCmd.doExecute(Unknown Source)

at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)

at
com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown
Source)

at
com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown
Source)

at
com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown
Source)

at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown
Source)

[Joseph Hoover]

I don't have it in front of me but I recall using an alternate jdbc driver when I connected to SQL Server via grailsabout a year ago. It might be this: http://jtds.sourceforge.net/

I'll try and remember to check, I think I have it on one of my machines at home.

       at groovy.lang.Closure.call (Closure.java:287)

$CachedChain.doFilter( ServletHandler.java:1089)

       at
com.opensymphony.module.sitemesh.filter.PageFilter.parsePage(PageFilter.java:
119)

       at
com.opensymphony.module.sitemesh.filter.PageFilter.doFilter(PageFilter.java :
55)

       at org.mortbay.jetty.servlet.ServletHandler
$CachedChain.doFilter(ServletHandler.java:1089)

       at
org.codehaus.groovy.grails.web.servlet.filter.GrailsReloadServletFilter.doFilterInternal (GrailsReloadServletFilter.java:
153)

       at
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:
75)

       at org.mortbay.jetty.servlet.ServletHandler

$CachedChain.doFilter( ServletHandler.java:1089)

       at
org.codehaus.groovy.grails.web.servlet.mvc.GrailsWebRequestFilter.doFilterInternal(GrailsWebRequestFilter.java:
58)

       at
org.springframework.web.filter.OncePerRequestFilter.doFilter (OncePerRequestFilter.java:

75)

       at org.mortbay.jetty.servlet.ServletHandler
$CachedChain.doFilter(ServletHandler.java:1089)

       at

org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal (CharacterEncodingFilter.java:

96)

       at
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:
75)

       at
org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate (DelegatingFilterProxy.java:
183)

       at
org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:
138)

       at org.mortbay.jetty.servlet.ServletHandler

$CachedChain.doFilter( ServletHandler.java:1089)

[kellygreer1]

Used that driver. Same error.

Is something wrong with my table and Grails conventions.

CREATE TABLE [dbo].[Books](
[id] [int] IDENTITY(1,1) NOT NULL,
[title] [varchar](120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[author] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Issues] PRIMARY KEY CLUSTERED
(
[id] ASC
) ON [PRIMARY]
) ON [PRIMARY]

Wish I knew more about how to reveal the SQL that is being passed to
SQL Server.

Kelly

On Jan 7, 4:39 pm, "Joseph Hoover" <hoover.jos...@gmail.com> wrote:
> I don't have it in front of me but I recall using an alternate jdbc driver
> when I connected to SQL Server via grailsabout a year ago. It might be this:http://jtds.sourceforge.net/
>
> I'll try and remember to check, I think I have it on one of my machines at
> home.
>

> > (SimpleGrailsControllerHelper.java:

> > org.springframework.web.filter.OncePerRequestFilter.doFilter(
> > OncePerRequestFilter.java:
> > 75)
>
> > at org.mortbay.jetty.servlet.ServletHandler

> > $CachedChain.doFilter(ServletHandler.java:1089)
>
> > at
> > com.opensymphony.module.sitemesh.filter.PageFilter.parsePage(
> > PageFilter.java:
> > 119)
>
> > at
> > com.opensymphony.module.sitemesh.filter.PageFilter.doFilter(
> > PageFilter.java:

> > 55)
>
> > at org.mortbay.jetty.servlet.ServletHandler
> > $CachedChain.doFilter(ServletHandler.java:1089)
>
> > at
>
> > org.codehaus.groovy.grails.web.servlet.filter.GrailsReloadServletFilter.doFilterInternal
> > (GrailsReloadServletFilter.java:
> > 153)
>
> > at

> > org.springframework.web.filter.OncePerRequestFilter.doFilter(
> > OncePerRequestFilter.java:
> > 75)
>
> > at org.mortbay.jetty.servlet.ServletHandler

> > $CachedChain.doFilter(ServletHandler.java:1089)
>
> > at
>
> > org.codehaus.groovy.grails.web.servlet.mvc.GrailsWebRequestFilter.doFilterInternal
> > (GrailsWebRequestFilter.java:

> > 58)
>
> > at
> > org.springframework.web.filter.OncePerRequestFilter.doFilter(
> > OncePerRequestFilter.java:
> > 75)
>
> > at org.mortbay.jetty.servlet.ServletHandler
> > $CachedChain.doFilter(ServletHandler.java:1089)
>
> > at
> > org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(
> > CharacterEncodingFilter.java:
> > 96)
>
> > at
> > org.springframework.web.filter.OncePerRequestFilter.doFilter(
> > OncePerRequestFilter.java:
> > 75)
>
> > at
> > org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(
> > DelegatingFilterProxy.java:
> > 183)
>
> > at
> > org.springframework.web.filter.DelegatingFilterProxy.doFilter(
> > DelegatingFilterProxy.java:
> > 138)
>

> > at org.mortbay.jetty.servlet.ServletHandler
> > $CachedChain.doFilter(ServletHandler.java:1089)
>
> > at

> ...
>
> read more »

[kellygreer1]

Added a version field to the table. And made the name of the table
singular.
Works!!!

I'm such a newb.

Time to buy a book. lol

Thanks everyone who helped.

Kelly

> ...
>
> read more »