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 prabho <pr...@gmail.com> on 2008/06/26 01:25:02 UTC
Ibatis queryForList in Sybase Database
Hi all,
When I am trying to execute a stored procedure in Sybase Database, it is
failing, if the entry is not present in the DB table. If the entry is
present, it works fine. We have the same code in the Weblogic and is working
fine. But seems like in tomcat, some more configuration needs to be made.
I have provided the code details.
Java Code
========
Map<String, String> parmMap = new HashMap<String, String>();
parmMap.put("configParmName", configParmName);
try
{
startTxn(sqlMap); // Just a wrapper for startTransaction
sqlMap.flushDataCache();
List resultList = sqlMap.queryForList("getConfigParmData",
parmMap);
String status = (String) parmMap.get("statusString");
if (status.indexOf("Error:") > 0)
if (status.indexOf("Return code 906") <= 0)
throw new SQLException(status);
commitTxn(sqlMap); // Just a wrapper for commitTransaction
return resultList;
} catch (SQLException sqlE) {
throw wrapSqlException(sqlE);
} finally {
endTxn(sqlMap); // Just a wrapper for endTransaction
}
Stored Proc (Sybase)
=========
CREATE PROCEDURE get_ConfigParm
@config_parm_name udt_short_name,
@statusString varchar( 1024 ) = NULL
OUTPUT
AS
DECLARE
@error int,
@rowcount int,
@statusInfo varchar( 1024 ),
@ret_code int,
@create_date udt_datetime,
@valid char(1),
@procname varchar(35),
@error_code varchar(5),
@num_rows varchar(5)
SELECT @procname = OBJECT_NAME( @@procid )
SELECT
@statusInfo = ISNULL( @@servername, 'UNKNOWN' ) + '.'
+ DB_NAME() + '.' + @procname
,
@statusString = ' Successful'
SELECT
config_parm_name,
parm_datatype,
config_parm_desc,
create_date,
create_user_id,
modify_date,
modify_user_id
FROM
ConfigParm
WHERE
config_parm_name = @config_parm_name
-- Status check
SELECT
@error = @@error,
@rowcount = @@rowcount
-- Check for error
If( @error != 0 )
BEGIN
select @ret_code = 904,
@error_code = CONVERT( varchar(5), @error )
SELECT @statusString = @statusInfo + ' Error: ['
+ convert( varchar(5), @error ) + '] selecting config_parm_name [' +
+ @config_parm_name + '] from ConfigParm table- Return code '
+ CONVERT( varchar(5), @ret_code)
RAISERROR 25012, 'GET', 'ConfigParm' , @error_code
RETURN 904
END
-- Check for no rows
IF( @rowcount = 0 )
BEGIN
select @ret_code = 906
SELECT @statusString = @statusInfo + ' Error: config_parm_name [' +
+ @config_parm_name + '] does not exist in ConfigParm table- Return
code '
+ CONVERT( varchar(5), @ret_code)
RAISERROR 25014, "config_parm_name",
@config_parm_name,"ConfigParm",'GET'
RETURN 906
END
-- Check for multiple rows
IF( @rowcount > 1 )
BEGIN
SELECT @statusString = @statusInfo + ' Warning: Select affected ['
+ convert( varchar(5), @rowcount ) + '] ConfigParms'
END
RETURN 0
XML Data
=======
<parameterMap id="getConfigParmDataMap" class="map" >
<parameter property="returnCd"
jdbcType="INTEGER" javaType="java.lang.Integer" mode="OUT"/>
<parameter property="configParmName"
jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="statusString"
jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT"/>
</parameterMap>
<procedure id="getConfigParmData"
parameterMap="getConfigParmDataMap"
resultClass="java.util.HashMap"
cacheModel="siteConfigCache" >
{? = call get_ConfigParm(?, ?) }
</procedure>
Error
====
Caused by: com.sybase.jdbc3.jdbc.SybSQLException: ** config_parm_name
DEV_WRPSYSCONFIG_1 does not exist in ConfigParm table- GET operation can not
be completed ***
at com.sybase.jdbc3.tds.Tds.a(Unknown Source)
at com.sybase.jdbc3.tds.Tds.nextResult(Unknown Source)
at com.sybase.jdbc3.jdbc.ResultGetter.nextResult(Unknown Source)
Can anyone, let me know how to fix this issue??
Regards
Prabhu
--
View this message in context: http://www.nabble.com/Ibatis-queryForList-in-Sybase-Database-tp18123663p18123663.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
Re: Ibatis queryForList in Sybase Database
Posted by Gwyn Evans <gw...@gmail.com>.
Could it be using different JDBC drivers (or different versions of the same
driver) in the different appservers, and they don't behave the same way? If
that doesn't get you anywhere, I'd suggest that you minimise things -
ideally to a simple static test case using just JDBC and investigate using
that.
/Gwyn
On Thu, Jun 26, 2008 at 12:25 AM, prabho <pr...@gmail.com> wrote:
>
> Hi all,
>
> When I am trying to execute a stored procedure in Sybase Database, it is
> failing, if the entry is not present in the DB table. If the entry is
> present, it works fine. We have the same code in the Weblogic and is
> working
> fine. But seems like in tomcat, some more configuration needs to be made.
>
> I have provided the code details.
>
> Java Code
> ========
> Map<String, String> parmMap = new HashMap<String, String>();
>
> parmMap.put("configParmName", configParmName);
>
> try
> {
> startTxn(sqlMap); // Just a wrapper for startTransaction
>
> sqlMap.flushDataCache();
>
> List resultList = sqlMap.queryForList("getConfigParmData",
> parmMap);
>
> String status = (String) parmMap.get("statusString");
>
> if (status.indexOf("Error:") > 0)
> if (status.indexOf("Return code 906") <= 0)
> throw new SQLException(status);
>
> commitTxn(sqlMap); // Just a wrapper for commitTransaction
>
> return resultList;
> } catch (SQLException sqlE) {
> throw wrapSqlException(sqlE);
> } finally {
> endTxn(sqlMap); // Just a wrapper for endTransaction
> }
>
> Stored Proc (Sybase)
> =========
>
> CREATE PROCEDURE get_ConfigParm
> @config_parm_name udt_short_name,
> @statusString varchar( 1024 ) = NULL
> OUTPUT
> AS
>
> DECLARE
> @error int,
> @rowcount int,
> @statusInfo varchar( 1024 ),
> @ret_code int,
> @create_date udt_datetime,
> @valid char(1),
> @procname varchar(35),
> @error_code varchar(5),
> @num_rows varchar(5)
>
>
> SELECT @procname = OBJECT_NAME( @@procid )
>
> SELECT
> @statusInfo = ISNULL( @@servername, 'UNKNOWN' ) + '.'
> + DB_NAME() + '.' + @procname
> ,
> @statusString = ' Successful'
>
>
> SELECT
> config_parm_name,
> parm_datatype,
> config_parm_desc,
> create_date,
> create_user_id,
> modify_date,
> modify_user_id
> FROM
> ConfigParm
> WHERE
> config_parm_name = @config_parm_name
>
> -- Status check
> SELECT
> @error = @@error,
> @rowcount = @@rowcount
>
> -- Check for error
> If( @error != 0 )
> BEGIN
> select @ret_code = 904,
> @error_code = CONVERT( varchar(5), @error )
> SELECT @statusString = @statusInfo + ' Error: ['
> + convert( varchar(5), @error ) + '] selecting config_parm_name [' +
> + @config_parm_name + '] from ConfigParm table- Return code '
> + CONVERT( varchar(5), @ret_code)
> RAISERROR 25012, 'GET', 'ConfigParm' , @error_code
> RETURN 904
> END
> -- Check for no rows
> IF( @rowcount = 0 )
> BEGIN
> select @ret_code = 906
> SELECT @statusString = @statusInfo + ' Error: config_parm_name [' +
> + @config_parm_name + '] does not exist in ConfigParm table- Return
> code '
> + CONVERT( varchar(5), @ret_code)
> RAISERROR 25014, "config_parm_name",
> @config_parm_name,"ConfigParm",'GET'
> RETURN 906
> END
>
> -- Check for multiple rows
> IF( @rowcount > 1 )
> BEGIN
> SELECT @statusString = @statusInfo + ' Warning: Select affected ['
> + convert( varchar(5), @rowcount ) + '] ConfigParms'
> END
>
> RETURN 0
>
> XML Data
> =======
> <parameterMap id="getConfigParmDataMap" class="map" >
> <parameter property="returnCd"
> jdbcType="INTEGER" javaType="java.lang.Integer" mode="OUT"/>
> <parameter property="configParmName"
> jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
> <parameter property="statusString"
> jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT"/>
> </parameterMap>
>
> <procedure id="getConfigParmData"
> parameterMap="getConfigParmDataMap"
> resultClass="java.util.HashMap"
> cacheModel="siteConfigCache" >
> {? = call get_ConfigParm(?, ?) }
> </procedure>
>
> Error
> ====
> Caused by: com.sybase.jdbc3.jdbc.SybSQLException: ** config_parm_name
> DEV_WRPSYSCONFIG_1 does not exist in ConfigParm table- GET operation can
> not
> be completed ***
> at com.sybase.jdbc3.tds.Tds.a(Unknown Source)
> at com.sybase.jdbc3.tds.Tds.nextResult(Unknown Source)
> at com.sybase.jdbc3.jdbc.ResultGetter.nextResult(Unknown Source)
>
> Can anyone, let me know how to fix this issue??
>
> Regards
> Prabhu
> --
> View this message in context:
> http://www.nabble.com/Ibatis-queryForList-in-Sybase-Database-tp18123663p18123663.html
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>
Re: Ibatis queryForList in Sybase Database
Posted by prabho <pr...@gmail.com>.
There is a difference between the sybase versions used in Weblogic and
Tomcat. So, it is behaving strangely. I got a workaround of implementing my
logic within the catch block (though it is not a good practice).
cmathrusse wrote:
>
> Could it be that your datasource in Tomcat is pointing to a different
> database than your datasource in WebLogic? If you look at the exception
> message it is coming from the database itself and not your stored proc.
> (It doesn't contain your formatting)
>
> Chris
>
>
>
>
> prabho <pr...@gmail.com>
> 06/25/2008 04:25 PM
> Please respond to
> user-java@ibatis.apache.org
>
>
> To
> user-java@ibatis.apache.org
> cc
>
> Subject
> Ibatis queryForList in Sybase Database
>
>
>
>
>
>
>
> Hi all,
>
> When I am trying to execute a stored procedure in Sybase Database, it is
> failing, if the entry is not present in the DB table. If the entry is
> present, it works fine. We have the same code in the Weblogic and is
> working
> fine. But seems like in tomcat, some more configuration needs to be made.
>
> I have provided the code details.
>
> Java Code
> ========
> Map<String, String> parmMap = new HashMap<String, String>();
>
> parmMap.put("configParmName", configParmName);
>
> try
> {
> startTxn(sqlMap); // Just a wrapper for startTransaction
>
> sqlMap.flushDataCache();
>
> List resultList = sqlMap.queryForList("getConfigParmData",
> parmMap);
>
> String status = (String) parmMap.get("statusString");
>
> if (status.indexOf("Error:") > 0)
> if (status.indexOf("Return code 906") <= 0)
> throw new SQLException(status);
>
> commitTxn(sqlMap); // Just a wrapper for commitTransaction
>
> return resultList;
> } catch (SQLException sqlE) {
> throw wrapSqlException(sqlE);
> } finally {
> endTxn(sqlMap); // Just a wrapper for endTransaction
> }
>
> Stored Proc (Sybase)
> =========
>
> CREATE PROCEDURE get_ConfigParm
> @config_parm_name udt_short_name,
> @statusString varchar( 1024 ) = NULL
> OUTPUT
> AS
>
> DECLARE
> @error int,
> @rowcount int,
> @statusInfo varchar( 1024 ),
> @ret_code int,
> @create_date udt_datetime,
> @valid char(1),
> @procname varchar(35),
> @error_code varchar(5),
> @num_rows varchar(5)
>
>
> SELECT @procname = OBJECT_NAME( @@procid )
>
> SELECT
> @statusInfo = ISNULL( @@servername, 'UNKNOWN' ) + '.'
> + DB_NAME() + '.' + @procname
> ,
> @statusString = ' Successful'
>
>
> SELECT
> config_parm_name,
> parm_datatype,
> config_parm_desc,
> create_date,
> create_user_id,
> modify_date,
> modify_user_id
> FROM
> ConfigParm
> WHERE
> config_parm_name = @config_parm_name
>
> -- Status check
> SELECT
> @error = @@error,
> @rowcount = @@rowcount
>
> -- Check for error
> If( @error != 0 )
> BEGIN
> select @ret_code = 904,
> @error_code = CONVERT( varchar(5), @error )
> SELECT @statusString = @statusInfo + ' Error: ['
> + convert( varchar(5), @error ) + '] selecting config_parm_name ['
> +
> + @config_parm_name + '] from ConfigParm table- Return code '
> + CONVERT( varchar(5), @ret_code)
> RAISERROR 25012, 'GET', 'ConfigParm' , @error_code
> RETURN 904
> END
> -- Check for no rows
> IF( @rowcount = 0 )
> BEGIN
> select @ret_code = 906
> SELECT @statusString = @statusInfo + ' Error: config_parm_name [' +
> + @config_parm_name + '] does not exist in ConfigParm table-
> Return
> code '
> + CONVERT( varchar(5), @ret_code)
> RAISERROR 25014, "config_parm_name",
> @config_parm_name,"ConfigParm",'GET'
> RETURN 906
> END
>
> -- Check for multiple rows
> IF( @rowcount > 1 )
> BEGIN
> SELECT @statusString = @statusInfo + ' Warning: Select affected ['
> + convert( varchar(5), @rowcount ) + '] ConfigParms'
> END
>
> RETURN 0
>
> XML Data
> =======
> <parameterMap id="getConfigParmDataMap" class="map" >
> <parameter property="returnCd"
> jdbcType="INTEGER" javaType="java.lang.Integer" mode="OUT"/>
> <parameter property="configParmName"
> jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
> <parameter property="statusString"
> jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT"/>
> </parameterMap>
>
> <procedure id="getConfigParmData"
> parameterMap="getConfigParmDataMap"
> resultClass="java.util.HashMap"
> cacheModel="siteConfigCache" >
> {? = call get_ConfigParm(?, ?) }
> </procedure>
>
> Error
> ====
> Caused by: com.sybase.jdbc3.jdbc.SybSQLException: ** config_parm_name
> DEV_WRPSYSCONFIG_1 does not exist in ConfigParm table- GET operation can
> not
> be completed ***
> at com.sybase.jdbc3.tds.Tds.a(Unknown Source)
> at com.sybase.jdbc3.tds.Tds.nextResult(Unknown Source)
> at com.sybase.jdbc3.jdbc.ResultGetter.nextResult(Unknown
> Source)
>
> Can anyone, let me know how to fix this issue??
>
> Regards
> Prabhu
> --
> View this message in context:
> http://www.nabble.com/Ibatis-queryForList-in-Sybase-Database-tp18123663p18123663.html
>
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>
>
>
>
--
View this message in context: http://www.nabble.com/Ibatis-queryForList-in-Sybase-Database-tp18123663p18141440.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
Re: Ibatis queryForList in Sybase Database
Posted by Ch...@sybase.com.
Could it be that your datasource in Tomcat is pointing to a different
database than your datasource in WebLogic? If you look at the exception
message it is coming from the database itself and not your stored proc.
(It doesn't contain your formatting)
Chris
prabho <pr...@gmail.com>
06/25/2008 04:25 PM
Please respond to
user-java@ibatis.apache.org
To
user-java@ibatis.apache.org
cc
Subject
Ibatis queryForList in Sybase Database
Hi all,
When I am trying to execute a stored procedure in Sybase Database, it is
failing, if the entry is not present in the DB table. If the entry is
present, it works fine. We have the same code in the Weblogic and is
working
fine. But seems like in tomcat, some more configuration needs to be made.
I have provided the code details.
Java Code
========
Map<String, String> parmMap = new HashMap<String, String>();
parmMap.put("configParmName", configParmName);
try
{
startTxn(sqlMap); // Just a wrapper for startTransaction
sqlMap.flushDataCache();
List resultList = sqlMap.queryForList("getConfigParmData",
parmMap);
String status = (String) parmMap.get("statusString");
if (status.indexOf("Error:") > 0)
if (status.indexOf("Return code 906") <= 0)
throw new SQLException(status);
commitTxn(sqlMap); // Just a wrapper for commitTransaction
return resultList;
} catch (SQLException sqlE) {
throw wrapSqlException(sqlE);
} finally {
endTxn(sqlMap); // Just a wrapper for endTransaction
}
Stored Proc (Sybase)
=========
CREATE PROCEDURE get_ConfigParm
@config_parm_name udt_short_name,
@statusString varchar( 1024 ) = NULL
OUTPUT
AS
DECLARE
@error int,
@rowcount int,
@statusInfo varchar( 1024 ),
@ret_code int,
@create_date udt_datetime,
@valid char(1),
@procname varchar(35),
@error_code varchar(5),
@num_rows varchar(5)
SELECT @procname = OBJECT_NAME( @@procid )
SELECT
@statusInfo = ISNULL( @@servername, 'UNKNOWN' ) + '.'
+ DB_NAME() + '.' + @procname
,
@statusString = ' Successful'
SELECT
config_parm_name,
parm_datatype,
config_parm_desc,
create_date,
create_user_id,
modify_date,
modify_user_id
FROM
ConfigParm
WHERE
config_parm_name = @config_parm_name
-- Status check
SELECT
@error = @@error,
@rowcount = @@rowcount
-- Check for error
If( @error != 0 )
BEGIN
select @ret_code = 904,
@error_code = CONVERT( varchar(5), @error )
SELECT @statusString = @statusInfo + ' Error: ['
+ convert( varchar(5), @error ) + '] selecting config_parm_name ['
+
+ @config_parm_name + '] from ConfigParm table- Return code '
+ CONVERT( varchar(5), @ret_code)
RAISERROR 25012, 'GET', 'ConfigParm' , @error_code
RETURN 904
END
-- Check for no rows
IF( @rowcount = 0 )
BEGIN
select @ret_code = 906
SELECT @statusString = @statusInfo + ' Error: config_parm_name [' +
+ @config_parm_name + '] does not exist in ConfigParm table-
Return
code '
+ CONVERT( varchar(5), @ret_code)
RAISERROR 25014, "config_parm_name",
@config_parm_name,"ConfigParm",'GET'
RETURN 906
END
-- Check for multiple rows
IF( @rowcount > 1 )
BEGIN
SELECT @statusString = @statusInfo + ' Warning: Select affected ['
+ convert( varchar(5), @rowcount ) + '] ConfigParms'
END
RETURN 0
XML Data
=======
<parameterMap id="getConfigParmDataMap" class="map" >
<parameter property="returnCd"
jdbcType="INTEGER" javaType="java.lang.Integer" mode="OUT"/>
<parameter property="configParmName"
jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="statusString"
jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT"/>
</parameterMap>
<procedure id="getConfigParmData"
parameterMap="getConfigParmDataMap"
resultClass="java.util.HashMap"
cacheModel="siteConfigCache" >
{? = call get_ConfigParm(?, ?) }
</procedure>
Error
====
Caused by: com.sybase.jdbc3.jdbc.SybSQLException: ** config_parm_name
DEV_WRPSYSCONFIG_1 does not exist in ConfigParm table- GET operation can
not
be completed ***
at com.sybase.jdbc3.tds.Tds.a(Unknown Source)
at com.sybase.jdbc3.tds.Tds.nextResult(Unknown Source)
at com.sybase.jdbc3.jdbc.ResultGetter.nextResult(Unknown
Source)
Can anyone, let me know how to fix this issue??
Regards
Prabhu
--
View this message in context:
http://www.nabble.com/Ibatis-queryForList-in-Sybase-Database-tp18123663p18123663.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.