You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by "Kezerashvili, Denis" <De...@gs.com> on 2007/12/13 19:41:31 UTC

iBATIS with DB2 stored procedure call problem

> We are using iBATIS as a persistence layer running on top of DB2.
> We've ran into a problem executing stored procedure.
> 
> The following works:
> <procedure id="user.deleteById" parameterClass="DaoUserId">		
> 		{call PERMIT.PERMIT_DELETE_USER_BY_ID_SP(#id#,
> #updateId#)} 
> </procedure>
> 
> But this does not:
> <procedure id="user.deleteById" parameterClass="DaoUserId">		
> 		{call PERMIT_DELETE_USER_BY_ID_SP(#id#, #updateId#)} 
> </procedure>
> 
> The error reported is that stored procedure named
> PERMIT_DELETE_USER_BY_ID_SP cannot be found.
> 
> Basically we are forced to specify the schema name for the stored
> procedure. The schema name is set up in the config file and we do not
> have to specify it for any regular <insert>, <update>, <delete>
> elements.
> 
> Have anyone seen this problem and knows how to solve it? We have
> multiple schemas used by different developers, so hard coding it is
> not an option.
> 
> Thank you in advance.

RE: iBATIS with DB2 stored procedure call problem

Posted by "Kezerashvili, Denis" <De...@gs.com>.
Thank you! That solves the problem. I really appreciate your help.


________________________________

	From: Stephen Boyd [mailto:swboyd@gmail.com] 
	Sent: Saturday, December 15, 2007 9:00 AM
	To: user-java@ibatis.apache.org
	Subject: Re: iBATIS with DB2 stored procedure call problem
	
	
	You need to set currentFunctionPath=PERMIT on the datasource.
It is a custom property that can be set on the jdbc url.  For example,
	
	
jdbc:db2://host:port/dbname:currentFunctionPath=PERMIT;currentSchema=PER
MIT;
	
	If you are running in websphere, setting this can be done in the
custom properties of the datasource.
	
	
	
	On Dec 14, 2007 2:00 AM, Abhigyan Agrawal1 <
abhigyan_agrawal@in.ibm.com <ma...@in.ibm.com> >
wrote:
	


		Are you sure that the user name you are providing in
config file has access to the schema? I tried simulating the problem but
it is working without specifying in the schema name. May be you can send
what config you are using. 
		
		Cheers,
		Abhigyan Agrawal
		
		
		
		
		
"Kezerashvili, Denis" <De...@gs.com> 

14/12/2007 00:11 

Please respond to
user-java@ibatis.apache.org

To
<us...@ibatis.apache.org> 	
cc
	
Subject
iBATIS with DB2 stored procedure call problem	

		





		We are using iBATIS as a persistence layer running on
top of DB2. 
		We've ran into a problem executing stored procedure. 

		The following works: 
		<procedure id="user.deleteById"
parameterClass="DaoUserId" >             
		               {call
PERMIT.PERMIT_DELETE_USER_BY_ID_SP(#id#, #updateId#)} 
		</procedure> 

		But this does not: 
		<procedure id="user.deleteById"
parameterClass="DaoUserId" >             
		               {call PERMIT_DELETE_USER_BY_ID_SP(#id#,
#updateId#)} 
		</procedure> 

		The error reported is that stored procedure named
PERMIT_DELETE_USER_BY_ID_SP cannot be found. 

		Basically we are forced to specify the schema name for
the stored procedure. The schema name is set up in the config file and
we do not have to specify it for any regular <insert>, <update>,
<delete> elements. 

		Have anyone seen this problem and knows how to solve it?
We have multiple schemas used by different developers, so hard coding it
is not an option. 

		Thank you in advance.

Re: iBATIS with DB2 stored procedure call problem

Posted by Stephen Boyd <sw...@gmail.com>.
You need to set currentFunctionPath=PERMIT on the datasource.  It is a
custom property that can be set on the jdbc url.  For example,

jdbc:db2://host:port/dbname:currentFunctionPath=PERMIT;currentSchema=PERMIT;

If you are running in websphere, setting this can be done in the custom
properties of the datasource.


On Dec 14, 2007 2:00 AM, Abhigyan Agrawal1 <ab...@in.ibm.com>
wrote:

>
> Are you sure that the user name you are providing in config file has
> access to the schema? I tried simulating the problem but it is working
> without specifying in the schema name. May be you can send what config you
> are using.
>
> Cheers,
> Abhigyan Agrawal
>
>
>
>
>  *"Kezerashvili, Denis" <De...@gs.com>*
>
> 14/12/2007 00:11
>  Please respond to
> user-java@ibatis.apache.org
>
>   To
> <us...@ibatis.apache.org>  cc
>   Subject
> iBATIS with DB2 stored procedure call problem
>
>
>
>
> We are using iBATIS as a persistence layer running on top of DB2.
> We've ran into a problem executing stored procedure.
>
> The following works:
> <procedure id="user.deleteById" parameterClass="DaoUserId">
>                {call* PERMIT.*PERMIT_DELETE_USER_BY_ID_SP(#id#,
> #updateId#)}
> </procedure>
>
> But this does not:
> <procedure id="user.deleteById" parameterClass="DaoUserId">
>                {call PERMIT_DELETE_USER_BY_ID_SP(#id#, #updateId#)}
> </procedure>
>
> The error reported is that stored procedure named PERMIT_DELETE_USER_BY_ID_SP
> cannot be found.
>
> Basically we are forced to specify the schema name for the stored
> procedure. The schema name is set up in the config file and we do not have
> to specify it for any regular <insert>, <update>, <delete> elements.
>
> Have anyone seen this problem and knows how to solve it? We have multiple
> schemas used by different developers, so hard coding it is not an option.
>
> Thank you in advance.
>
>

RE: iBATIS with DB2 stored procedure call problem

Posted by "Kezerashvili, Denis" <De...@gs.com>.
Well, it seems to work for regular SQL. I do not need to specify schema
name for the tables I am hitting. Here are the config file as well as
properties file that it uses.
 
TestApplicationContext.xml:
<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xmlns:context="http://www.springframework.org/schema/context"

xmlns:tx="http://www.springframework.org/schema/tx"

xsi:schemaLocation="

http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-2.0.xsd

http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-2.5.xsd

http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-2.5.xsd">

<!-- Load up two properties file for DB settings,

and make the properties available in this file.

-->

<bean id="properties-config"

class="org.springframework.beans.factory.config.PropertyPlaceholderConfi
gurer">

<property name="ignoreResourceNotFound" value="true" />

<property name="locations">

<list>

<!-- shared values, and values used by the

integration build -->

<value>classpath:standard/database.properties</value>

</list>

</property>

</bean>

<context:annotation-config />

<!-- not needed if test cases extend
AbstractTransactionalJUnit4SpringContextTests

<tx:annotation-driven />

-->

<bean id="managementService"

class="com.gs.security.permit.admin.service.impl.PermitManagementService
">

<property name="applicationDao">

<ref local="applicationDao" />

</property>

<property name="attributeTypeDao">

<ref local="attributeTypeDao" />

</property>

<property name="applToPolicyDomainMappingDao">

<ref local="applToPolicyDomainMappingDao" />

</property>

<property name="policyDomainDao">

<ref local="policyDomainDao" />

</property>

<property name="policyDomainInclusionDao">

<ref local="policyDomainInclusionDao" />

</property>

</bean>

<bean id="dataSource"

class="org.springframework.jdbc.datasource.SingleConnectionDataSource">

<property name="driverClassName">

<value>com.ibm.db2.jcc.DB2Driver</value>

</property>

<property name="url">

<value>

jdbc:db2://${database.host}:${database.port}/${database.dbname}:currentS
chema=${database.schema};

</value>

</property>

<property name="username">

<value>${username}</value>

</property>

<property name="password">

<value>${password}</value>

</property>

</bean>

<bean id="jdbcExceptionTranslator"

class="org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslat
or">

<property name="dataSource">

<ref bean="dataSource" />

</property>

</bean>

<bean id="sqlMapClient"

class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">

<property name="configLocation">

<value>

classpath:com/gs/security/permit/admin/service/internal/impl/sqlmap/maps
/sqlmap-config.xml

</value>

</property>

<property name="dataSource">

<ref local="dataSource" />

</property>

</bean>

<bean id="actionDao"

class="com.gs.security.permit.admin.service.internal.impl.sqlmap.SqlMapA
ctionDao">

<property name="sqlMapClient">

<ref local="sqlMapClient" />

</property>

</bean>

<bean id="applicationDao"

class="com.gs.security.permit.admin.service.internal.impl.sqlmap.SqlMapA
pplicationDao">

<property name="sqlMapClient">

<ref local="sqlMapClient" />

</property>

</bean>

<bean id="applToPolicyDomainMappingDao"

class="com.gs.security.permit.admin.service.internal.impl.sqlmap.SqlMapA
pplToPolicyDomainMappingDao">

<property name="sqlMapClient">

<ref local="sqlMapClient" />

</property>

</bean>

<bean id="attributeTypeDao"

class="com.gs.security.permit.admin.service.internal.impl.sqlmap.SqlMapA
ttributeTypeDao">

<property name="sqlMapClient">

<ref local="sqlMapClient" />

</property>

</bean>

<bean id="policyDomainDao"

class="com.gs.security.permit.admin.service.internal.impl.sqlmap.SqlMapP
olicyDomainDao">

<property name="sqlMapClient">

<ref local="sqlMapClient" />

</property>

</bean>

<bean id="policyDomainInclusionDao"

class="com.gs.security.permit.admin.service.internal.impl.sqlmap.SqlMapP
olicyDomainInclusionDao">

<property name="sqlMapClient">

<ref local="sqlMapClient" />

</property>

</bean>

<bean id="userDao"

class="com.gs.security.permit.admin.service.internal.impl.sqlmap.SqlMapU
serDao">

<property name="sqlMapClient">

<ref local="sqlMapClient" />

</property>

</bean>

<bean id="userService"

class="com.gs.security.permit.admin.service.impl.PermitUserService">

<property name="userDao">

<ref local="userDao" />

</property>

</bean>

<bean id="transactionManager"

class="org.springframework.jdbc.datasource.DataSourceTransactionManager"
>

<property name="dataSource">

<ref local="dataSource" />

</property>

</bean>

</beans>

 

database.properties:

# Database settings. This file is used by the main code (via Spring
Context.xml),

# as well as by the build.xml

database.host=cdigdss05

database.port=61700

database.dbname=ncrm01

database.schema=PERMIT

database.username=*****

database.password=*****

# pending update of the context.xml:

host=${database.host}

port=${database.port}

server=${database.dbname}

schema=${database.schema}

username=${database.username}

password=${database.password}


________________________________

	From: Abhigyan Agrawal1 [mailto:abhigyan_agrawal@in.ibm.com] 
	Sent: Friday, December 14, 2007 2:00 AM
	To: user-java@ibatis.apache.org
	Subject: Re: iBATIS with DB2 stored procedure call problem
	
	

	Are you sure that the user name you are providing in config file
has access to the schema? I tried simulating the problem but it is
working without specifying in the schema name. May be you can send what
config you are using. 
	
	Cheers,
	Abhigyan Agrawal
	
	
	
	
	
"Kezerashvili, Denis" <De...@gs.com> 

14/12/2007 00:11 
Please respond to
user-java@ibatis.apache.org


To
<us...@ibatis.apache.org> 
cc
Subject
iBATIS with DB2 stored procedure call problem

	





	We are using iBATIS as a persistence layer running on top of
DB2. 
	We've ran into a problem executing stored procedure. 

	The following works: 
	<procedure id="user.deleteById" parameterClass="DaoUserId">

	               {call PERMIT.PERMIT_DELETE_USER_BY_ID_SP(#id#,
#updateId#)} 
	</procedure> 

	But this does not: 
	<procedure id="user.deleteById" parameterClass="DaoUserId">

	               {call PERMIT_DELETE_USER_BY_ID_SP(#id#,
#updateId#)} 
	</procedure> 

	The error reported is that stored procedure named
PERMIT_DELETE_USER_BY_ID_SP cannot be found. 

	Basically we are forced to specify the schema name for the
stored procedure. The schema name is set up in the config file and we do
not have to specify it for any regular <insert>, <update>, <delete>
elements. 

	Have anyone seen this problem and knows how to solve it? We have
multiple schemas used by different developers, so hard coding it is not
an option. 

	Thank you in advance.

Re: iBATIS with DB2 stored procedure call problem

Posted by Abhigyan Agrawal1 <ab...@in.ibm.com>.
Are you sure that the user name you are providing in config file has 
access to the schema? I tried simulating the problem but it is working 
without specifying in the schema name. May be you can send what config you 
are using.

Cheers,
Abhigyan Agrawal





"Kezerashvili, Denis" <De...@gs.com> 
14/12/2007 00:11
Please respond to
user-java@ibatis.apache.org


To
<us...@ibatis.apache.org>
cc

Subject
iBATIS with DB2 stored procedure call problem







We are using iBATIS as a persistence layer running on top of DB2. 
We've ran into a problem executing stored procedure. 
The following works: 
<procedure id="user.deleteById" parameterClass="DaoUserId"> 
                {call PERMIT.PERMIT_DELETE_USER_BY_ID_SP(#id#, 
#updateId#)} 
</procedure> 
But this does not: 
<procedure id="user.deleteById" parameterClass="DaoUserId"> 
                {call PERMIT_DELETE_USER_BY_ID_SP(#id#, #updateId#)} 
</procedure> 
The error reported is that stored procedure named 
PERMIT_DELETE_USER_BY_ID_SP cannot be found. 
Basically we are forced to specify the schema name for the stored 
procedure. The schema name is set up in the config file and we do not have 
to specify it for any regular <insert>, <update>, <delete> elements.
Have anyone seen this problem and knows how to solve it? We have multiple 
schemas used by different developers, so hard coding it is not an option.
Thank you in advance.