Re: iBatis DAO - How to turn off data connection pool
What are you trying to achieve by doing that? You can't really
turn off pooling with SimpleDataSource, but you could try one of the
other implementations or write your own to achieve that.
Cheers,
Clinton
On 5/16/05, Folashade Adeyosoye [EMAIL PROTECTED] wrote:
How can I turn off the Database connection pool in the dao.xml
transactionManager type=JDBC
property name=DataSource
value=SIMPLE/
property name=JDBC.Driver
value=${driver}/
property name=JDBC.ConnectionURL
value=${url}/
property name=JDBC.Username
value=${username}/
property name=JDBC.Password
value=${password}/
property name=JDBC.DefaultAutoCommit
value=true/
property
name=Pool.MaximumActiveConnections value=10/
property
name=Pool.MaximumIdleConnections value=5/
property name=Pool.MaximumCheckoutTime
value=12/
/transactionManager
I tried changing the property
name=Pool.MaximumActiveConnections value=0/ and property
name=Pool.MaximumIdleConnections value=0/, but I got
an exception
exception is thrown - java.lang.IndexOutOfBoundsException:
Index: 0, Size: 0
Thanks
SqlMap More sql Funcion
Hello!! Is possible use Trim(),UpperCase and another funcion in this mode: . dynamic prepend=WHERE isNotNull prepend=AND property=name param=trim() param=uppercase() (etc ) TNODE = #name# /isNotNull /dynamic ... Thanks Marco Berri
Re: Custom Type Handler question
Read it too quickly... so you have the problem before the parts I
comment on. One further question: where do you set the return type
as a cursor in your Ibatis version (the
cstmt.registerOutParameter(1,OracleTypes.CURSOR) in the original
version)?
The problem with cursor types is that they are not in the JDBC spec.
Most database vendors support them but using their own db specific
classes.
In general the fetch out of sequence in Oracle you usually get when:
- You fetch from a closed cursor
- You fetch from an open cursor after a commit.
My hunch here would be that Oracle gets confused because you don't
seem to register your return value as cursor type.
Regards,
Sven
On Mon, 16 May 2005 06:49:24 -0500, you wrote:
Sven,
Actually this is a case of a cursor returned within another cursor, so I
haven't performed a fetch until I called result.next() below.
See the straight Java example I included to understand the nesting I'm
trying to accomplish. Or perhaps there is something I'm not understanding
about how getObject() works?
Thanks,
Ken
Ken,
To get back to the original question, the problem you have lies in the
part:
public Object getResult(ResultGetter arg0) throws SQLException {
System.out.println(Object: +
arg0.getObject().getClass().getName());
ResultSet result =3D (ResultSet) arg0.getObject();
System.out.println(Iterating);
while(result.next())
System.out.println(test);
result.close();
return result;
}
1) You already fetch the result in the method above (for debugging
purposes probably), so by the time you want to really extract the
data, the data has already been fetched. This is the explanation of
the Oracle error ORA-01002 fetch out of sequence (have a look at the
documentation for it).
Cursor are for single use... if you used a cursor, you have reexecute
the query to be able to fetch again
2) You close result before returning it???
Regards,
Sven
On Sun, 15 May 2005 20:00:28 -0600, you wrote:
I'm not really sure what the problem could be. It should work fine.
Is there no way to just join the data and return a set of repeating groups
and use the iBATIS N+1 Selects solution?
http://opensource.atlassian.com/confluence/oss/display/IBATIS/How+do+I+get+around+the+N+Plus+1+selects+problem%3F
Cheers,
Clinton
On 5/15/05, Ken Katsma [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
Hi,
My group is currently using stored procedures in Oracle for all our
database access. To get around the n+m problem we are considering
several alternatives. One we know will work is to do a user function
for each column that maps to a m table that gives us the data in
string format (name,value pairs).
Another approach, (which we would prefer) is to return the results for
each m column as a cursor. I can do this in java with code that looks
something like this:
class RefCursor
{
public static void main (String args [])
throws SQLException, ClassNotFoundException
{
String query = begin +
open ? for +
'select dname, CURSOR(select ename +
from emp +
where
emp.deptno=dept.deptno) +
from dept'; +
end;;
DriverManager.registerDriver
(new oracle.jdbc.driver.OracleDrive r());
Connection conn=
DriverManager.getConnection
(jdbc:oracle:thin:@aria-dev:1521:ora817dev,
scott, tiger);
Statement trace = conn.createStatement();
CallableStatement cstmt = conn.prepareCall(query);
cstmt.registerOutParameter(1,OracleTypes.CURSOR);
cstmt.execute();
ResultSet rset= (ResultSet)cstmt.getObject(1);
for(int i = 0; rset.next(); i++ )
{
System.out.println( rset.getString(1) );
ResultSet rset2 = (ResultSet)rset.getObject(2);
for( int j = 0; rset2.next(); j++ )
System.out.println ( + rset2.getString(1) );
rset2.close();
}
rset.close();
cstmt.close();
}
}
To get the same functionality in iBatis we tried writing a custom type
handler for the cursor column that looks like this:
import java.sql.Ref;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.ibatis.sqlmap.client.extensions.ParameterSetter;
import com.ibatis.sqlmap.client.extensions.ResultGetter;
import com.ibatis.sqlmap.client.extensions.TypeHandlerCallback;
import com.ibatis.sqlmap.engine.type.ResultGetterImpl;
/**
* @author S001027
*
* TODO To change the template for this generated type comment go to
* Window - Preferences - Java - Code Style - Code Templates
*/
public class RefTypeHandler implements TypeHandlerCallback {
/* (non-Javadoc)
* @see
com.ibatis.sqlmap.client.extensions.TypeHandlerCallback#setParameter (
com.ibatis.sqlmap.client.extensions.ParameterSetter,
java.lang.Object)
*/
public void setParameter(ParameterSetter arg0, Object arg1)
throws SQLException {
if (arg1 != null)
{
arg0.setObject(arg1);
}
}
/* (non-Javadoc)
* @see
com.ibatis.sqlmap.client.extensions.TypeHandlerCallback#getResult(
com.ibatis.sqlmap.client.extensions.ResultGetter )
*/
public Object
Another custom type handler issue
Now that I have the custom type handler executing correctly for the nested cursor, I have another question :) I'm trying to use the custom type handler to populate an array of objects in my class. For instance, I have a Client, which contains Address[] arrays and Communication[] arrays. I'd like to populate it with a custom type handler. While I can get the type handler to execute for a single Address or communication object, I can't seem to find a way to get iBatis to understand that it needs to fire a type handler for an array of those objects. An even better way to handle this would be if there were some way I could nest the result maps so that I point the nested cursor to a second result map. Any ideas? Either way, I think I feel another hack coming on :( Thanks, Ken
Re: iBATIS for Java 2.1.0 Released
Graham, Oracle doesn't support the getResultSet call in it's JDBC drivers. Though it does appear to be a step in the right direction :) Ken On 5/16/05, Graham Cruickshanks [EMAIL PROTECTED] wrote: Hi Clinton,Great news on the 2.1.0 release. I see in the change log Changed SQLexecutor to always call .execute instead of .executeQuery and.executeUpdate, does this address bug IBATIS-53? CheersGrahamFrom: Clinton Begin [EMAIL PROTECTED]Reply-To: [EMAIL PROTECTED]To: ibatis-dev@incubator.apache.org,ibatis-user-java@incubator.apache.orgibatis-user-java@incubator.apache.org Subject: iBATIS for Java 2.1.0 ReleasedDate: Mon, 16 May 2005 01:58:39 -0600Hi allThe subject says it all. 2.1.0 is now available.Change Log: http://sourceforge.net/project/shownotes.php?release_id=327667Download: http://www.ibatis.com/downloads.htmlCheers,Clinton
Question on dynamic prepend and using multiple properties
Here is a problem that I am facing. My parameter class has two counts, say countA and countB and I need to use these in a HAVING clause in the sql query. Both the values could be empty. For eg. if they both have values, the query would look like: Select . --- from where. HAVING ( countA 5 and count B 10 ) I tried using nested dynamic clause, but looks like it is not supported. dynamic prepend=HAVING ( isNotEmpty property=countA count #countA# /isNotEmpty dynamic prepend=AND isNotEmpty property=countB count #countB# /isNotEmpty /dynamic ) /dynamic Is there any other approach to solve this problem, other than going thru and checking every combination of countA and countB ? Any help/suggestion is appreciated Thanks, Prasad -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.9 - Release Date: 5/12/2005
Another custom type handler question
I posted this last night but I'm posting it again because I didn't see my mail come through on the mailing list. Do I have a gmail problem? Or are users not cc'd on their own posts? Now that I have the custom type handler executing correctly for the nested cursor, I have another question :) I'm trying to use the custom type handler to populate an array of objects in my class. For instance, I have a Client, which contains Address[] arrays and Communication[] arrays. I'd like to populate it with a custom type handler. While I can get the type handler to execute for a single Address or communication object, I can't seem to find a way to get iBatis to understand that it needs to fire a type handler for an array of those objects. An even better way to handle this would be if there were some way I could nest the result maps so that I point the nested cursor to a second result map. Any ideas?
Re: Another custom type handler issue
Do you have to use arrays? How about lists instead? iBATIS (to my knowledge) doesn't support returning arrays. My guess is if you were to have ListAddress addresses and ListCommunication communications (type specification not necessary, just highlighting what its doing) - then iBATIS would do the right thing. Brice On 5/16/05, Ken Katsma [EMAIL PROTECTED] wrote: Now that I have the custom type handler executing correctly for the nested cursor, I have another question :) I'm trying to use the custom type handler to populate an array of objects in my class. For instance, I have a Client, which contains Address[] arrays and Communication[] arrays. I'd like to populate it with a custom type handler. While I can get the type handler to execute for a single Address or communication object, I can't seem to find a way to get iBatis to understand that it needs to fire a type handler for an array of those objects. An even better way to handle this would be if there were some way I could nest the result maps so that I point the nested cursor to a second result map. Any ideas? Either way, I think I feel another hack coming on :( Thanks, Ken -- Brice Ruth Software Engineer, Madison WI
Re: iBATIS for Java 2.1.0 Released
The update for IBATIS to use .execute instead of .executeQuery sadly does
not resolve the Oracle Cursor issue (IBATIS-53)
Bellow is an example of the issue with SqlExecutor problem in straight JDBC.
This shows how the SqlExecutor.java will not work without being adjusted to
loop over the out parameters to see if a out parameter is a resultset for
cursor compatiablity. IThe resultset needs to be retieved via the
getObject(index) method. Oracle Driver problem I know but I predict hell
freezing over before Oracle fix their driver, Weblogic's Oracle Driver and
DataDirects Oracle drivers all map cursors to getResultSet.
Cheers
Graham
import java.sql.*;
import oracle.jdbc.*;
/**
* @author Graham Cruickshanks
*
*/
public class OracleJDBCTest {
/** Store Procedure Definition
*
*CREATE OR REPLACE
* PROCEDURE GetEmpRS (p_deptnoIN emp.deptno%TYPE,
* p_recordset OUT Types.cursor_type) AS
* BEGIN
* OPEN p_recordset FOR
* SELECT ename,
* empno,
* deptno
* FROM emp
* WHERE deptno = p_deptno
* ORDER BY ename;
* END GetEmpRS;
*/
*/
public static void TestCursorViaGetObject() {
try {
DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
Connection conn =
DriverManager.getConnection(jdbc:oracle:thin:@localhost:1521:orcl,
scott, tiger);
CallableStatement stmt = conn.prepareCall(BEGIN GetEmpRS(?, ?);
END;);
stmt.setInt(1, 30); // DEPTNO
stmt.registerOutParameter(2, OracleTypes.CURSOR); //REF CURSOR
stmt.execute();
ResultSet rs = (ResultSet)stmt.getObject(2);
while (rs.next()) {
System.out.println(rs.getString(ename) + : +
rs.getString(empno) + : + rs.getString(deptno));
}
rs.close();
rs = null;
stmt.close();
stmt = null;
conn.close();
conn = null;
}
catch (SQLException e) {
System.out.println(e.getLocalizedMessage());
}
}
public static void TestCursorViaGetResultSet() {
try {
DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
Connection conn =
DriverManager.getConnection(jdbc:oracle:thin:@localhost:1521:orcl,
scott, tiger);
CallableStatement stmt = conn.prepareCall(BEGIN GetEmpRS(?, ?);
END;);
stmt.setInt(1, 30); // DEPTNO
stmt.registerOutParameter(2, OracleTypes.CURSOR); //REF CURSOR
stmt.execute();
ResultSet rs = stmt.getResultSet();
//NULL POINTER HERE, AKA IBATIS SqlExecutor.java
while (rs.next()) {
System.out.println(rs.getString(ename) + : +
rs.getString(empno) + : + rs.getString(deptno));
}
rs.close();
rs = null;
stmt.close();
stmt = null;
conn.close();
conn = null;
}
catch (SQLException e) {
System.out.println(e.getLocalizedMessage());
}
}
public static void main(String[] args){
OracleJDBCTest.TestCursorViaGetObject();
OracleJDBCTest.TestCursorViaGetResultSet();
}
}
From: Ken Katsma [EMAIL PROTECTED]
Reply-To: Ken Katsma [EMAIL PROTECTED]
To: ibatis-user-java@incubator.apache.org
CC: [EMAIL PROTECTED]
Subject: Re: iBATIS for Java 2.1.0 Released
Date: Mon, 16 May 2005 20:11:23 -0500
Graham,
Oracle doesn't support the getResultSet call in it's JDBC drivers. Though
it
does appear to be a step in the right direction :)
Ken
On 5/16/05, Graham Cruickshanks [EMAIL PROTECTED] wrote:
Hi Clinton,
Great news on the 2.1.0 release. I see in the change log Changed SQL
executor to always call .execute instead of .executeQuery and
.executeUpdate, does this address bug IBATIS-53?
Cheers
Graham
From: Clinton Begin [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: ibatis-dev@incubator.apache.org,
ibatis-user-java@incubator.apache.org
ibatis-user-java@incubator.apache.org
Subject: iBATIS for Java 2.1.0 Released
Date: Mon, 16 May 2005 01:58:39 -0600
Hi all
The subject says it all. 2.1.0 is now available.
Change Log:
http://sourceforge.net/project/shownotes.php?release_id=327667
Download: http://www.ibatis.com/downloads.html
Cheers,
Clinton
Very Confused.
Hello, I'm trying to get *anything* to work with the iBatis setup and I'm having very little success... I've tried to follow the tutorial and setup the PetStore example but nothing runs for me. I'm sure it is some sort of configuration issue... I'm very new to Java ... so I'm sure that isn't helping. (Other applets and .java files do successfully access my database) When I try to enter the store (JPetStore) I get : HTTP Status 400 - Invalid path /pet/shop/index was requested I also tried the example from this mailing list found here. It also won't run for me.http://www.mail-archive.com/ibatis-user-java@incubator.apache.org/msg01759.html Any guidance would be greatly appreciated. Dave
Re: Another custom type handler issue
I would like to see an example of how the list approach might work (when using a nested resultset). In the case of the array, I found the solution. You just have to define the array in the custom type handler using the format [Lpackage.ClassName; and it will fire the custom type handler. I then am using the type handler to iterate over the nested resultset. If there is a way to do this using nested resultmaps it would be great, but I haven't seen an example. Thanks, Ken Brice Ruth wrote: Do you have to use arrays? How about lists instead? iBATIS (to my knowledge) doesn't support returning arrays. My guess is if you were to have ListAddress addresses and ListCommunication communications (type specification not necessary, just highlighting what its doing) - then iBATIS would do the right thing. Brice On 5/16/05, Ken Katsma [EMAIL PROTECTED] wrote: Now that I have the custom type handler executing correctly for the nested cursor, I have another question :) I'm trying to use the custom type handler to populate an array of objects in my class. For instance, I have a Client, which contains Address[] arrays and Communication[] arrays. I'd like to populate it with a custom type handler. While I can get the type handler to execute for a single Address or communication object, I can't seem to find a way to get iBatis to understand that it needs to fire a type handler for an array of those objects. An even better way to handle this would be if there were some way I could nest the result maps so that I point the nested cursor to a second result map. Any ideas? Either way, I think I feel another hack coming on :( Thanks, Ken
Re: iBatis DAO - How to turn off data connection pool
There really is no simple answer to what you need. I believe we have
discussed your dilemna in the past haven't we? The answer is the
same... Bite the bullet and swap out your connection manager with a
common connection manager so that both implementations use the same
connection manager. This path would be much simpler than trying all
the gyrations that you are at the moment.
Brandon.
On 5/17/05, Folashade Adeyosoye [EMAIL PROTECTED] wrote:
Thanks for your response, I have actually been battling this for a while,
this is my situation.
I currently have a system that works, but with embedded SQL in my classes,
and the classes and implementation are well separated using interfaces and
implementation classes. ( Action classes à API calls that delegates à
JDBC implementation classes)
I am trying to swap out my written DAO pattern with the iBatis DAO
Add an alternative SQLMAP implementation and also keep around my old
embedded SQL JDBC classes
The JDBC classes do not use JNDI to lookup the database connections, I have
a DatabaseConnection Manager class that creates a pool of up to 20
connections and manages them
Right now I just need the DAO part to work with out using the SQLMAP
portion, this is a future task to do.
And I would like to basically use the dao.xml to switch between both
implementations.
Thanks
From: Clinton Begin [mailto:[EMAIL PROTECTED]
Sent: Monday, May 16, 2005 4:03 PM
To: ibatis-user-java@incubator.apache.org
Subject: Re: iBatis DAO - How to turn off data connection pool
What are you trying to achieve by doing that? You can't really turn off
pooling with SimpleDataSource, but you could try one of the other
implementations or write your own to achieve that.
Cheers,
Clinton
On 5/16/05, Folashade Adeyosoye [EMAIL PROTECTED] wrote:
How can I turn off the Database connection pool in the dao.xml
transactionManager type=JDBC
property name=DataSource value=SIMPLE/
property name=JDBC.Driver value=${driver}/
property name=JDBC.ConnectionURL value=${url}/
property name=JDBC.Username value=${username}/
property name=JDBC.Password value=${password}/
property name=JDBC.DefaultAutoCommit value=true/
property name=Pool.MaximumActiveConnections value=10/
property name=Pool.MaximumIdleConnections value=5/
property name=Pool.MaximumCheckoutTime value=12/
/transactionManager
I tried changing the property name=Pool.MaximumActiveConnections
value=0/ and property name=Pool.MaximumIdleConnections value=0/,
but I got an exception
exception is thrown - java.lang.IndexOutOfBoundsException:
Index: 0, Size: 0
Thanks
Re: Question on dynamic prepend and using multiple properties
Don't forget the new functionality available in 2.1.0 dynamic prepend=HAVING open=( close=) isNotEmpty property=countA count gt; #countA# /isNotEmpty isNotEmpty prepend=AND property=countB count lt; #countB# /isNotEmpty /dynamic Brandon On 5/17/05, Ron Grabowski [EMAIL PROTECTED] wrote: Have you tried this: dynamic prepend=HAVING ( isNotEmpty property=countA count #countA# /isNotEmpty isNotEmpty prepend=AND property=countB count #countB# /isNotEmpty ) /dynamic --- Narasimha Prasad [EMAIL PROTECTED] wrote: Here is a problem that I am facing. My parameter class has two counts, say countA and countB and I need to use these in a 'HAVING' clause in the sql query. Both the values could be empty. For eg. if they both have values, the query would look like: Select . --- from where. HAVING ( countA 5 and count B 10 ) I tried using nested dynamic clause, but looks like it is not supported. dynamic prepend=HAVING ( isNotEmpty property=countA count #countA# /isNotEmpty dynamic prepend=AND isNotEmpty property=countB count #countB# /isNotEmpty /dynamic ) /dynamic Is there any other approach to solve this problem, other than going thru and checking every combination of countA and countB ? Any help/suggestion is appreciated Thanks, Prasad -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.9 - Release Date: 5/12/2005
Re: Very Confused.
I've created a couple of web applications designed to help you get started using frameworks like iBATIS quickly. http://equinox.dev.java.net http://appfuse.dev.java.net Let me know if you'd like me to build one with a particular web framework (Struts, JSF, Spring, WebWork or Tapestry) and I'll upload it for you (or provide a link). Matt On May 17, 2005, at 12:08 PM, Dave Guzda wrote: Hello, I'm trying to get *anything* to work with the iBatis setup and I'm having very little success... I've tried to follow the tutorial and setup the PetStore example but nothing runs for me. I'm sure it is some sort of configuration issue... I'm very new to Java ... so I'm sure that isn't helping. (Other applets and .java files do successfully access my database) When I try to enter the store (JPetStore) I get : HTTP Status 400 - Invalid path /pet/shop/index was requested I also tried the example from this mailing list found here. It also won't run for me. http://www.mail-archive.com/ibatis-user-java@incubator.apache.org/ msg01759.html Any guidance would be greatly appreciated. Dave
Re: Very Confused.
Hi Dave, If you're new to Java, I wouldn't suggest trying to setup jPetstore ... that can be kinda tricky even for folks who've been around Java for a while. I would suggest setting up your SqlMap.xml and sql-map-config.xml files, as provided in the tutorial, and then creating a SqlConfig file that initializes the configuration and provides a static method that returns the SqlMapClient instance. Then, create a very simple application that gets a SqlMapClient instance and calls a query and verifies the results. Let me know if that works for you - if you need more guidance (e.g. code), I can help you with that, too. Brice On 5/17/05, Dave Guzda [EMAIL PROTECTED] wrote: Hello, I'm trying to get *anything* to work with the iBatis setup and I'm having very little success... I've tried to follow the tutorial and setup the PetStore example but nothing runs for me. I'm sure it is some sort of configuration issue... I'm very new to Java ... so I'm sure that isn't helping. (Other applets and .java files do successfully access my database) When I try to enter the store (JPetStore) I get : HTTP Status 400 - Invalid path /pet/shop/index was requested I also tried the example from this mailing list found here. It also won't run for me. http://www.mail-archive.com/ibatis-user-java@incubator.apache.org/msg01759.html Any guidance would be greatly appreciated. Dave -- Brice Ruth Software Engineer, Madison WI
