ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bruce Wexler" <bwex...@massconnections.com>
Subject RE: sql server 2k parameterized queries
Date Thu, 23 Mar 2006 01:33:33 GMT
Hi Bob,

I see you're using sql server 2k. I know you need an answer to your question, but could you
PLEASE help me. I'm trying to get the jpetstore to work with a physical sql server 2k db.
I have the db created with populated tables. I've scoured through docs, wiki, faqs, google
all day and cannot find an answer to how I am supposed to config my DaoConfig.java, or do
I need this? Probably a naive question, but the reason I ask is because I saw some documentation
on configuring for an Access database that didn't even use the DaoConfig. If you could help
I'd greatly appreciate it. I think I'm close, here I have the error I'm getting followed by
the code:

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 [DOGS], pageSize [4].  Cause: com.ibatis.common.jdbc.exception.NestedSQLException:
  
--- The error occurred in com/ibatis/jpetstore/persistence/sqlmapdao/sql/Product.xml.  
--- The error occurred while applying a parameter map.  
--- Check the getProductListByCategory-InlineParameterMap.  
--- Check the statement (query failed).  
--- Cause: java.sql.SQLException: [DataDirect][SQLServer JDBC Driver][SQLServer]Invalid object
name 'PRODUCT'.



database.properties file:

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/jpetstore")) {
        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);
    }
  }
}


dao.xml:

<daoConfig>
  <context>
    <transactionManager type="SQLMAP">
      <property name="SqlMapConfigResource"
        value="com/ibatis/jpetstore/persistence/sqlmapdao/sql/sql-map-config.xml"/>
    </transactionManager>
    <dao interface="com.ibatis.jpetstore.persistence.iface.ItemDao"
      implementation="com.ibatis.jpetstore.persistence.sqlmapdao.ItemSqlMapDao"/>
    <dao interface="com.ibatis.jpetstore.persistence.iface.SequenceDao"
      implementation="com.ibatis.jpetstore.persistence.sqlmapdao.SequenceSqlMapDao"/>
    <dao interface="com.ibatis.jpetstore.persistence.iface.AccountDao"
      implementation="com.ibatis.jpetstore.persistence.sqlmapdao.AccountSqlMapDao"/>
    <dao interface="com.ibatis.jpetstore.persistence.iface.CategoryDao"
      implementation="com.ibatis.jpetstore.persistence.sqlmapdao.CategorySqlMapDao"/>
    <dao interface="com.ibatis.jpetstore.persistence.iface.ProductDao"
      implementation="com.ibatis.jpetstore.persistence.sqlmapdao.ProductSqlMapDao"/>
    <dao interface="com.ibatis.jpetstore.persistence.iface.OrderDao"
      implementation="com.ibatis.jpetstore.persistence.sqlmapdao.OrderSqlMapDao"/>
  </context>
</daoConfig>

-----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

Mime
View raw message