Re: postgresql: idle in transaction
Clinton Begin wrote: Try this... transactionManager ... commitRequired="true" By default iBATIS only commits on non-queries (insert/update/delete/proc). I am already using commitRequired="true", which fixed my stored procedures. But it does not fix the "idle in transaction" thing. I do not understand why :-(. It appears as if iBatis starts a new transaction in advantage. Baldur Cheers, Clinton On Tue, 04 Jan 2005 10:35:10 +0100, Baldur Norddahl [EMAIL PROTECTED] wrote: Hi, I am trying to avoid that all my connections ends up in state "idle in transaction" like this: [EMAIL PROTECTED]:~$ ps ax | grep "postres: secure" 11249 ?S 0:00 postgres: secure db 192.168.2.121 idle in transaction With the old version af iBatis and our php projects, that status is only "idle" (unless the connection is currently working on a query). Clinton Begin advised me to try transactionManager type="JDBC" commitRequired="true" in the config file. I am going to use this setting anyway so my stored procedures work correctly, but it does not seem to have any effect on the "idle in transaction" problem. It does not seem to have any bad effects on the system, but from a system admin perspective I would like to avoid it, so I can have my scripts warn me if those starts piling up. They are a sign that somebody is not handling their transactions correctly. Baldur
update inside stored procedure
I thought I had a similiar problem to what the poster of
http://www.mail-archive.com/ibatis-user-java@incubator.apache.org/msg00135.html
had, and I tried the solution that worked for him, but without success. After
doing as suggested I am not getting any exceptions, but there are no apparent
updates being performed on the database either.
Just to verify, my transactionManager tag now looks like:
transactionManager type=JDBC commitRequired=true
... datasource stuff ...
The procedure inside the sqlMap file is:
procedure id=increment parameterMap=incrementMap
{ call incProc(?, ?) }
/procedure
I'm calling it with (update/insert threw exceptions for me):
sqlMap.queryForObject(increment, map);
I'm using postgresql 7.3, the stored procedure works fine when called
from psql. I tried ibatis 2.0.8 w/o luck, and just saw and tried 2.0.9
w/ the same results.
Thanks for any help provided. Love ibatis.
Re: IBatis and Oracle stored functions - strongly typed cursors
Ok,
Got a weakly typed cursor function (sys_refcursor Oracle9i
functionality) working now. Thanks to Jerome.
Instead of using javax.servlet.jsp.jstl.sql.Result I used
*oracle.jdbc.rowset.OracleCachedRowSet* which is available for the 10g
jdbc drivers.
public Object getResult(ResultGetter getter) throws SQLException {
OracleCachedRowSet cachedResult = new OracleCachedRowSet();
ResultSet rs = null;
try {
rs = (ResultSet)getter.getObject();
cachedResult.populate(rs);
} finally {
rs.close();
}
return cachedResult;
}
In this case a stored function is called to insert a record, for example:
function insert_person
(name in varchar2
,lastname in varchar2
,age in number
)
returnsys_refcursor
To get this 'weak' cursor back-into bean mode I do:
Map parameters = new HashMap(4);
parameters.put(name,Jan);
parameters.put(lastname,Vissers);
parameters.put(age,new Integer(34));
sql.insert(insertPersonPck,parameters);
OracleCachedRowSet dataInserted =
(OracleCachedRowSet)parameters.get(result);
Map properties = new HashMap();
ResultSetMetaData md = dataInserted.getMetaData();
int cols = md.getColumnCount();
if (dataInserted.next()) for (int i=1; i=cols ; i++) {
properties.put(md.getColumnName(i),dataInserted.getString(i));
//TODO Change this?
}
Person insertedPerson = new Person();
BeanUtils.populate(insertedPerson,properties);
Is there any other smarter way of doing this? Is it possible to have the
sqlMap procedure like:
procedure id=insertPersonPck parameterClass=my actual class
resultMap=my weak ref cursor containing map
![CDATA[{?= call CLAF_PERSON_PCK.INSERT_PERSON(?,?,?)}]]
/procedure
and
What about 'strongly' typed cursors?
Thx.
Jan.
Jan Vissers wrote:
Hi,
I'm evaluating IBatis sqlMap/DAO wrt Oracle functionality, like:
* RETURNING ... INTO ... Clause
* CLOB (oracle.sql.CLOB) 32K
* BLOB (oracle.sql.BLOB)
* XMLType (oracle.xdb.XMLType)
* CallableStatement...
It looks to me that none of these are really supported. I, for
instance have a packaged function:
function insert_record( p_i_values in pck2.refcursortype)
return pck2.refcursortype;
Is there any way to call these types of objects from IBatis?
Thx.
Jan.
--
Get Firefox! http://www.spreadfirefox.com/?q=affiliatesid=0t=70
And Get Thunderbird http://www.mozilla.org/products/thunderbird/
--
Get Firefox! http://www.spreadfirefox.com/?q=affiliatesid=0t=55
And Get Thunderbird http://www.mozilla.org/products/thunderbird/
Re: postgresql: idle in transaction
I don't know then. iBATIS doesn't do anything special in this regard. Perhaps PGSQL reports idle in transaction for all non-autocommit connections? Cheers, Clinton On Wed, 05 Jan 2005 09:23:03 +0100, Baldur Norddahl [EMAIL PROTECTED] wrote: Clinton Begin wrote: Try this... transactionManager ... commitRequired=true By default iBATIS only commits on non-queries (insert/update/delete/proc). I am already using commitRequired=true, which fixed my stored procedures. But it does not fix the idle in transaction thing. I do not understand why :-(. It appears as if iBatis starts a new transaction in advantage. Baldur Cheers, Clinton On Tue, 04 Jan 2005 10:35:10 +0100, Baldur Norddahl [EMAIL PROTECTED] wrote: Hi, I am trying to avoid that all my connections ends up in state idle in transaction like this: [EMAIL PROTECTED]:~$ ps ax | grep postres: secure 11249 ? S 0:00 postgres: secure db 192.168.2.121 idle in transaction With the old version af iBatis and our php projects, that status is only idle (unless the connection is currently working on a query). Clinton Begin advised me to try transactionManager type=JDBC commitRequired=true in the config file. I am going to use this setting anyway so my stored procedures work correctly, but it does not seem to have any effect on the idle in transaction problem. It does not seem to have any bad effects on the system, but from a system admin perspective I would like to avoid it, so I can have my scripts warn me if those starts piling up. They are a sign that somebody is not handling their transactions correctly. Baldur
Re: How to include code examples in the wiki?
http://wiki.apache.org/ibatis/HelpOnFormatting On Wed, 05 Jan 2005 14:34:21 -0700, Nathan Maves [EMAIL PROTECTED] wrote: I wanted to update/add some things to the wiki but how do you format code/xml in the text?
Re: How to include code examples in the wiki?
I read that but did not see how to place java or xml code inline. On Jan 5, 2005, at 2:38 PM, Larry Meadors wrote: http://wiki.apache.org/ibatis/HelpOnFormatting On Wed, 05 Jan 2005 14:34:21 -0700, Nathan Maves [EMAIL PROTECTED] wrote: I wanted to update/add some things to the wiki but how do you format code/xml in the text?
Re: oracle sequence trouble
This one is really stumping me. Anyone else ever had such a problem?
This seems so trivial but it is hold up a ton of development :)
On Jan 5, 2005, at 12:24 PM, Nathan Maves wrote:
I jest created this sequence today and I works fine from the command
line but it returns null from ibatis. Are there any tricks when using
sequences?
Here are the logs...
DEBUG 01-05 12:21:56 Checked out connection 10110166 from pool.
(SimpleDataSource.java:563)
DEBUG 01-05 12:21:56 {conn-100087} Connection
(ConnectionLogProxy.java:42)
DEBUG 01-05 12:21:56 {pstm-100088} PreparedStatement: select
CurrentId.NextVal from dual (PreparedStatementLogProxy.java:48)
DEBUG 01-05 12:21:56 {pstm-100088} Parameters: []
(PreparedStatementLogProxy.java:49)
DEBUG 01-05 12:21:56 {pstm-100088} Types: []
(PreparedStatementLogProxy.java:50)
DEBUG 01-05 12:21:56 {rset-100089} ResultSet
(ResultSetLogProxy.java:41)
DEBUG 01-05 12:21:56 Returned connection 10110166 to pool.
(SimpleDataSource.java:527)
Here is from the command line...
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.2.0 - Production
With the Partitioning and Oracle Data Mining options
JServer Release 9.2.0.2.0 - Production
SQL select CurrentId.NextVal from dual;
NEXTVAL
--
20025
SQL exit
They are both using the same user/passwd.
Re: oracle sequence trouble
see page 14 of the SQLMaps manual.
On Wed, 05 Jan 2005 14:45:21 -0700, Nathan Maves [EMAIL PROTECTED] wrote:
This one is really stumping me. Anyone else ever had such a problem?
This seems so trivial but it is hold up a ton of development :)
On Jan 5, 2005, at 12:24 PM, Nathan Maves wrote:
I jest created this sequence today and I works fine from the command
line but it returns null from ibatis. Are there any tricks when using
sequences?
Here are the logs...
DEBUG 01-05 12:21:56 Checked out connection 10110166 from pool.
(SimpleDataSource.java:563)
DEBUG 01-05 12:21:56 {conn-100087} Connection
(ConnectionLogProxy.java:42)
DEBUG 01-05 12:21:56 {pstm-100088} PreparedStatement: select
CurrentId.NextVal from dual (PreparedStatementLogProxy.java:48)
DEBUG 01-05 12:21:56 {pstm-100088} Parameters: []
(PreparedStatementLogProxy.java:49)
DEBUG 01-05 12:21:56 {pstm-100088} Types: []
(PreparedStatementLogProxy.java:50)
DEBUG 01-05 12:21:56 {rset-100089} ResultSet
(ResultSetLogProxy.java:41)
DEBUG 01-05 12:21:56 Returned connection 10110166 to pool.
(SimpleDataSource.java:527)
Here is from the command line...
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.2.0 - Production
With the Partitioning and Oracle Data Mining options
JServer Release 9.2.0.2.0 - Production
SQL select CurrentId.NextVal from dual;
NEXTVAL
--
20025
SQL exit
They are both using the same user/passwd.
Re: SOLVED Re: oracle sequence trouble
I am not sure how i feel about that...it would be convenient, but I am not sure of the impact it may have in other areas. On Wed, 05 Jan 2005 15:02:23 -0700, Nathan Maves [EMAIL PROTECTED] wrote: select id=getNextId resultClass=string select CurrentId.NextVal as value from dual /select Is there anyway that sqlmaps could check if there is only one column coming back and assume that this is the value for implicit resultmaps?
Re: How to include code examples in the wiki?
Have a look at the 3rd Party Contributions page:
http://wiki.apache.org/ibatis/3rd_20Party_20Contributions
I just added some stuff that includes XML. Basically, just wrap it like:
{{{
xml-goes-here/
}}}
On Wed, 05 Jan 2005 14:41:32 -0700, Nathan Maves [EMAIL PROTECTED] wrote:
I read that but did not see how to place java or xml code inline.
On Jan 5, 2005, at 2:38 PM, Larry Meadors wrote:
http://wiki.apache.org/ibatis/HelpOnFormatting
On Wed, 05 Jan 2005 14:34:21 -0700, Nathan Maves
[EMAIL PROTECTED] wrote:
I wanted to update/add some things to the wiki but how do you format
code/xml in the text?
--
Kris Schneider mailto:[EMAIL PROTECTED]
