2

I am facing weird issue in my grail project and after trying a lot i am posting this question here.I have tried all the URL related combination form http://jtds.sourceforge.net/faq.html#noSuitableDriver and other stack over flow answers like Help me create a jTDS connection string.

I am working on grails 2.4.3 project with groovy 2.3 and trying to connect with SQL Server database using jtds 1.3.1 but always getting "No suitable driver found for jdbc:jtds:sqlserver:" But to test this scenario i have written stand alone program as given below for same data base using same jar jtds 1.3.1 and its working fine.

try{
                Class.forName("net.sourceforge.jtds.jdbc.Driver");
                String url = "jdbc:jtds:sqlserver://<hostname>:<port>/<database>";
                Connection con = DriverManager.getConnection(url,"user","password");

                Statement stmt = con.createStatement();
                ResultSet rs = stmt.executeQuery(query);}

Below I have provided the grails project code snippet for connection class 

class SQLServerConnection implements DBConnection {

@Override
public Connection getConnection(String serverName, String databaseName) {
    // TODO Auto-generated method stub
    Class.forName("net.sourceforge.jtds.jdbc.Driver");
    String url = "jdbc:jtds:sqlserver://<host>:<port>/<database>";
    Connection con = DriverManager.getConnection(url,"user","password"); 
    return con

}

Action form where i am calling this method 

def dataFaucetColumn (){
    def currentApp = RawDataApp.get(params.int('id'))
    String query = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='"+currentApp?.tableName +"'"
    Connection con = new SQLServerConnection().getConnection(currentApp?.servers,currentApp?.databaseName)
    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery(query); }

waiting for your answers.

Community
  • 1
  • 1
Vishwas Upadhyay
  • 182
  • 1
  • 12
  • connected or not, in context groovy console: http://stackoverflow.com/questions/32494835/what-is-classpath-for-groovy-console-jdbc-driver-prblem/32507453#32507453 – Jacek Cz Sep 12 '15 at 06:26
  • I have googled a hour. Groovy has sometimes `"No suitable driver found for ... "` in selected context of use I use Java almost 14yers and see such exception very rarely – Jacek Cz Sep 12 '15 at 06:34

1 Answers1

1

Here's how you can use SQL Server with Grails, while taking advantage of Hibernate:

  1. Open grails-app/conf/BuildConfig.groovy
  2. In the dependencies section add the JTDS dependency. Then save the file.

Example:

dependencies {
    runtime 'net.sourceforge.jtds:jtds:1.3.1'       

}
  1. Open grails-app/conf/DataSource.groovy
  2. In the environments section, set up the data source and save the file. In the following example I'll set up SQL Server for the production environment

Example:

environments {
    production {
         dataSource {
            dbCreate = "update"
            url = "jdbc:jtds:sqlserver://<hostname>:<port>/<database>"
            username = "X"
            password = "X"
            driverClassName = "net.sourceforge.jtds.jdbc.Driver"
            dialect = org.hibernate.dialect.SQLServerDialect
            properties {
                maxActive = 8 
                minEvictableIdleTimeMillis = 1800000
                timeBetweenEvictionRunsMillis = 1800000
                numTestsPerEvictionRun = 3
                testOnBorrow = true
                testWhileIdle = true
                testOnReturn = true
                validationQuery = "SELECT 1"
                validationQueryTimeout = 3
                validationInterval = 15000
               jdbcInterceptors = "ConnectionState"
               defaultTransactionIsolation = java.sql.Connection.TRANSACTION_READ_COMMITTED
             }
         }
     }
}

That's it. Now you can use GORM to query the database. Plus, Hibernate will manage the database connection(s) :)

Emmanuel Rosa
  • 9,697
  • 2
  • 14
  • 20
  • Thanks for information, But my problem is to take data base parameter dynamically from front end and connect to database,putting data base entry in DataSource.groovy file wouldn't help me much. – Vishwas Upadhyay Sep 14 '15 at 10:59