Configuring SQL Server datasource

[Bruce Wexler <bw...@massconnections.com>]

OK, can you tell I'm struggling? Can't seem to get a break here. Wouldn't have anything to do with SQL Server being a Microsoft product would it!? ;)

My latest error:
Caused by: java.lang.reflect.InvocationTargetException
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:324)
	at org.apache.struts.beanaction.ActionInvoker.invoke(ActionInvoker.java:16)
	... 39 more
Caused by: com.ibatis.dao.client.DaoException: Failed to queryForPaginatedList - id [getProductListByCategory], parameterObject [FISH], pageSize [4].  Cause: java.sql.SQLException: [DataDirect][SQLServer JDBC Driver]Unable to connect.  Invalid URL.
Caused by: java.sql.SQLException: [DataDirect][SQLServer JDBC Driver]Unable to connect.  Invalid URL.
	at com.ibatis.dao.client.template.SqlMapDaoTemplate.queryForPaginatedList(SqlMapDaoTemplate.java:274)
	at com.ibatis.jpetstore.persistence.sqlmapdao.ProductSqlMapDao.getProductListByCategory(ProductSqlMapDao.java:19)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:324)
	at com.ibatis.dao.engine.impl.DaoProxy.invoke(DaoProxy.java:72)
	at $Proxy4.getProductListByCategory(Unknown Source)
	at com.ibatis.jpetstore.service.CatalogService.getProductListByCategory(CatalogService.java:47)
	at com.ibatis.jpetstore.presentation.CatalogBean.viewCategory(CatalogBean.java:126)
	... 44 more


database.properties:
driver=com.ddtek.jdbc.sqlserver.SQLServerDriver
url=jdbc:datadirect:sqlserver://TEST:1433:jpetstore
username=sa
password=


sql-map-config.xml:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMapConfig PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
    "http://ibatis.apache.org/dtd/sql-map-config-2.dtd">
<sqlMapConfig>
  <properties resource="properties/database.properties"/>
  <transactionManager type="JDBC">
    <dataSource type="SIMPLE">
      <property value="${driver}" name="JDBC.Driver"/>
      <property value="${url}" name="JDBC.ConnectionURL"/>
      <property value="${username}" name="JDBC.Username"/>
      <property value="${password}" name="JDBC.Password"/>
    </dataSource>
  </transactionManager>
  <sqlMap resource="com/ibatis/jpetstore/persistence/sqlmapdao/sql/Account.xml"/>
  <sqlMap resource="com/ibatis/jpetstore/persistence/sqlmapdao/sql/Category.xml"/>
  <sqlMap resource="com/ibatis/jpetstore/persistence/sqlmapdao/sql/Product.xml"/>
  <sqlMap resource="com/ibatis/jpetstore/persistence/sqlmapdao/sql/Sequence.xml"/>
  <sqlMap resource="com/ibatis/jpetstore/persistence/sqlmapdao/sql/LineItem.xml"/>
  <sqlMap resource="com/ibatis/jpetstore/persistence/sqlmapdao/sql/Order.xml"/>
  <sqlMap resource="com/ibatis/jpetstore/persistence/sqlmapdao/sql/Item.xml"/>
</sqlMapConfig>


DaoConfig.java:
package com.ibatis.jpetstore.persistence;

import com.ibatis.common.jdbc.ScriptRunner;
import com.ibatis.common.jdbc.SimpleDataSource;
import com.ibatis.common.resources.Resources;
import com.ibatis.dao.client.DaoManager;
import com.ibatis.dao.client.DaoManagerBuilder;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;
import javax.sql.DataSource;

public class DaoConfig {
  private static final String resource = "com/ibatis/jpetstore/persistence/dao.xml";
  private static final DaoManager daoManager;

  static {
    try {
      daoManager = newDaoManager(null);
      Properties props = Resources.getResourceAsProperties("properties/database.properties");
      String url = props.getProperty("url");
      String driver = props.getProperty("driver");
      String username = props.getProperty("username");
      String password = props.getProperty("password");

      if (url.equals("jdbc:datadirect:sqlserver://DB1:1433")) {
        Class.forName(driver).newInstance();
        Connection conn = DriverManager.getConnection(url, username, password);
        try {
        	DataSource dataSource = new SimpleDataSource(props); //properties usually loaded from a file
        	conn = dataSource.getConnection();
//        	.....database queries and updates???
        	conn.commit();
//          ScriptRunner runner = new ScriptRunner(conn, false, false);
//          runner.setErrorLogWriter(null);
//          runner.setLogWriter(null);
//          runner.runScript(Resources.getResourceAsReader("ddl/mssql/jpetstore-mssql-schema.sql"));
//          runner.runScript(Resources.getResourceAsReader("ddl/mssql/jpetstore-mssql-dataload.sql"));
        } finally {
          conn.close();
        }
      }
    } catch (Exception e) {
      throw new RuntimeException("Description.  Cause: " + e, e);
    }
  }

  public static DaoManager getDaoManager() {
    return daoManager;
  }

  public static DaoManager newDaoManager(Properties props) {
    try {
      Reader reader = Resources.getResourceAsReader(resource);
      return DaoManagerBuilder.buildDaoManager(reader, props);
    } catch (Exception e) {
      throw new RuntimeException("Could not initialize DaoConfig.  Cause: " + e, e);
    }
  }
}

Any help would be so greatly appreciated.

Thanks,
Bruce

-----Original Message-----
From: Bob [mailto:gnomeking@gmail.com]
Sent: Wednesday, March 22, 2006 5:20 PM
To: user-java@ibatis.apache.org
Subject: sql server 2k parameterized queries


I'm trying to use ibatis with sql server 2k, and I'm having problems
with parameterized queries.  I have many parameterized queries working
with mysql, and I'm really confused as to why this is causing a
problem.  The query is set up very similarly to all the mysql ones
(except it is trying to put the results in a list of hashmaps rather
than of beans)

I am recieving the following exception when trying to use the query.

com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred while applying a parameter map.
--- Check the getPayoutOrgData-InlineParameterMap.
--- Check the statement (query failed).
--- Cause: java.sql.SQLException: Incorrect syntax near the keyword 'GROUP'.
Caused by: java.sql.SQLException: Incorrect syntax near the keyword 'GROUP'.
        at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:185)
        at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList(GeneralStatement.java:123)
        at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:610)
        at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:584)
        at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:101)
        at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForList(SqlMapClientImpl.java:78)

SQL Profiler suggests that the following is being executed by Ibatis
create proc #jtds000002 @P0 nvarchar(4000) as SELECT <blah> WHERE @P0
GROUP BY <blah>

Running that query in Query Analyser indeed gives the same error that
there is an incorrect syntax near "GROUP".
However, if I run the query stripping out the proc and @P0 and
replacing it with the actual parameter, it works flawlessly.

Does Ibatis support parameterized queries with SQL Server 2000?
Can anyone think of anything I might be doing wrong to get this error?
 Would more information on any part of the problem be helpful?

I would grately appreciate any help or suggestions - I am prepared to
consider anything! I've been really struggling with this and am
stumped at the moment.

Thanks a lot,

Bob Ball