SV: pager in ibatis
Hi there! Yusuf wrote I think it's better off making the paging logic yourself in the application, and then querying only the records you want to show, for example in oracle you can use the rownum to fetch the data like select * from (select rownum row, a.* from tabel1 as a) where row between 21 and 30 /Yusuf wrote Why not use the ibatis the queryForList with pageNumber and pageSize parameters like this: sqlMap.queryForList(searchCompanies, searchText, ((pageNumber-1) * pageSize), pageSize); ?? Then you only keep track of the pageNumber you are at and don't need to make your query database specific... and you don't worry about the caching, it cache the page you are at and only that as far as I have understood it. You also need the countSearchCompanies, with an exactly the same query but with count in it . to find out how many pages it totally for that search. So you can specifiy page 1, 2, 3, 4 in e.g. your webpage. We use this strategy with success here: http://www.aguiden.no (search for sykkel (bike),gives you two pages) Since queryForPaginatedList don't have a count (??), I don't think it load the totally result set, only the page you are at, and have lazy loading of the next page... smooth. So far as I have understood it ??? Take care, Erlend --- -Opprinnelig melding- Fra: Yusuf [mailto:[EMAIL PROTECTED] Sendt: 13. april 2006 02:44 Til: user-java@ibatis.apache.org Emne: RE: pager in ibatis I think it's better off making the paging logic yourself in the application, and then querying only the records you want to show, for example in oracle you can use the rownum to fetch the data like select * from (select rownum row, a.* from tabel1 as a) where row between 21 and 30 because using the queryForPaginatedList (without caching) will always fetch the entire result and then showing only the selected records, and if the record count is high (like thousands..) then the performance will be bad. cmiiw, Yusuf. -Original Message- From: John Chien [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 12, 2006 11:27 PM To: user-java@ibatis.apache.org Subject: Re: pager in ibatis L:arry: Thank you for the information. The queryForPaginatedList() will return a list, then I can check for the existence of successive records by calling the isNextPageAvailable(). and go to that page by calling gotoPage(); However, I can not keep this list in my server due to concurrecy reason. Does that mean that I have to call the same method every time and give it the page number for it to return me the records ? I am doing the web development. How can I make use of this returned list in JSP with Struct development ? Should I keep the list in the session or request scope ? Thanks, John Chien Larry Meadors wrote: queryForPaginatedList() Larry On 4/12/06, John Chien [EMAIL PROTECTED] wrote: Dear sir: I have a feature that requires me to return query result of a selection statment in certain amount of successive records every time. This is just like a page. For example: SELECT id, name FROM employee; The selection result of this statement is a list. However, I want to only get 30 records every time. For the first call, I want to get the first 30 (1 - 30) records, for thext call, I would like to get the next 30 (31 - 60) records. How can I do this in Ibatis ? Thanks, John Chien
Re: Error in abator generated class
Hi Paul,
The difference you were seeing between windows and AIX must be a difference between JDBC drivers. I believe that DB2 V8 is a little bit more forgiving in this area - so maybe that's the difference?
Regardless, this exposes something that needs to change in Abator - because you should not ever need to use java.sql.Date.
As a test, you could try this...in the generated XML map, look for the sql XML element that includes the generated where clause. In that element, you'll see several places where the database field is specified (probably something like this...#ORDERDUED#). Change it so that the datatype is specified like this...#ORDERDUED:DATE#. Then you should be able to use
java.util.Date in the DAO method.
If you're willing to try, I'd be interested to know if this fixes the problem.
Jeff Butler
On 4/26/06, Paul Carr [EMAIL PROTECTED] wrote:
Thanks Tim.The problem was that I was using a java.util.Date when setting up theExample, (As that is what is specified in the value object) but you
actually need to use a java.sql.Date.Best RegardsPaul.-Original Message-From: Chen, Tim [mailto:[EMAIL PROTECTED]]Sent: 25 April 2006 21:01
To: user-java@ibatis.apache.orgSubject: RE: Error in abator generated class
http://publib.boulder.ibm.com/infocenter/db2v7luw/index.jsp?topic=/com.ibm.db2v7.doc/db2m0/db2cli.htmCLI0114EDatetime field overflow.Explanation: The data sent for a date, time, or timestamp parameter, or
column, was invalid.User Response: Respecify the date, time, or timestamp data.-Original Message-From: Paul Carr [mailto:[EMAIL PROTECTED]
]Sent: Tuesday, April 25, 2006 3:01 PMTo: user-java@ibatis.apache.orgSubject: Error in abator generated classHi Allim running ibatis on WAS
5.0 ...this is an abator generatedselectByExample query using a Date fielddoing a greater or equals.CODE = public static int getNumberOverdue() { Date now = new Date(); DaoManager daoManager =
SQLWriter.getDaoManager(); ManufactureRequestLineDAO mrlDao =(ManufactureRequestLineDAO)daoManager.getDao(ManufactureRequestLineDAO.class); ManufactureRequestLineExample mre = newManufactureRequestLineExample();
mre.setOrderDueD(now); mre.setOrderDueD_Indicator(mre.EXAMPLE_GREATER_THAN_OR_EQUAL); List l = mrlDao.selectByExample(mre); return l.size(); }When I run it standalone on the PC it works ok.run it in WAS on AIX
and I getthe exception below.I think it may be British Summer Times fault but why does it work onmy PC ?Is there anything I can set in ibatis to make this work ok ?Original Exception:Error Message: Failed to queryForList - id
[oper_manufacture_request_line.abatorgenerated_selectByExample],parameterObject [{orderDueD=Tue Apr 25 19:55:13 BST 2006,AND_ORDER_DUE_D_GE=Y}]. Cause:com.ibatis.common.jdbc.exception.NestedSQLException: --- The error
occurred while applying a parameter map. --- Check theoper_manufacture_request_line.abatorgenerated_selectByExample-InlineParameterMap. --- Check the statement (query failed). --- Cause:COM.ibm.db2.jdbc.DB2Exception
: [IBM][CLI Driver] CLI0114E Datetime fieldoverflow. SQLSTATE=22008 Caused by: COM.ibm.db2.jdbc.DB2Exception:[IBM][CLI Driver] CLI0114E Datetime field overflow. SQLSTATE=22008 ErrorCode: 500 Target Servlet: null Error Stack:
com.ibatis.dao.client.DaoException: Failed to queryForList - id[oper_manufacture_request_line.abatorgenerated_selectByExample],parameterObject [{orderDueD=Tue Apr 25 19:55:13 BST 2006,AND_ORDER_DUE_D_GE=Y}]. Cause:
com.ibatis.common.jdbc.exception.NestedSQLException:--- The error occurred while applying a parameter map.--- Check theoper_manufacture_request_line.abatorgenerated_selectByExample-InlineParameterMap.
--- Check the statement (query failed).--- Cause: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver]CLI0114E Datetime field overflow. SQLSTATE=22008Caused by: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver]
CLI0114E Datetime field overflow. SQLSTATE=22008Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:--- The error occurred while applying a parameter map.--- Check theoper_manufacture_request_line.abatorgenerated_selectByExample-InlinePara
meterMap.--- Check the statement (query failed).--- Cause: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver]CLI0114E Datetime field overflow. SQLSTATE=22008Caused by: COM.ibm.db2.jdbc.DB2Exception
: [IBM][CLI Driver]CLI0114E Datetime field overflow. SQLSTATE=22008atcom.ibatis.dao.client.template.SqlMapDaoTemplate.queryForList(SqlMapDaoTemplate.java)ategl.dao.ManufactureRequestLineDAOImpl.selectByExample
(ManufactureRequestLineDAOImpl.java)ategl.dao.ManufactureRequestLineDAOImpl.selectByExample(ManufactureRequestLineDAOImpl.java)at java.lang.reflect.Method.invoke(Native Method)at com.ibatis.dao.engine.impl.DaoProxy.invoke
(DaoProxy.java)at $Proxy35.selectByExample(Unknown Source)at egl.treetable.WebFacade.getNumberOverdue(WebFacade.java)at egl.treetable.WebFacade.getTable(WebFacade.java)at org.apache.jsp._home._jspService
Re: java.net.UnknownHostException: www.ibatis.com
Sorry, I don't know enough about Spring to be able to help you. In my experience with Struts and tomcat, jboss, and weblogic, DTDs are looked up automatically in the JARs that are in the WAR/EAR file as long as the DTD spec in the XML file matches EXACTLY the decl at the top of the DTD file in the jar. -ed On 4/25/06, Simone Chiucchi [EMAIL PROTECTED] wrote: Ed, I modified the host as you suggested, but I got the following exception: org.springframework.beans.factory.BeanDefinitionStoreException: IOException parsing XML document from class path resource [geeg-data-orm-sqlmap.xml]; nested exception is java.net.ConnectException : Connection refused: connect java.net.ConnectException: Connection refused: connect at java.net.PlainSocketImpl.socketConnect(Native Method) at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:329) at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:194) at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:181) at java.net.Socket.connect(Socket.java:459) at java.net.Socket.connect(Socket.java:409) at sun.net.NetworkClient.doConnect(NetworkClient.java:157) at sun.net.www.http.HttpClient.openServer(HttpClient.java:414) at sun.net.www.http.HttpClient.openServer(HttpClient.java:628) at sun.net.www.http.HttpClient.init(HttpClient.java:331) at sun.net.www.http.HttpClient.init(HttpClient.java:292) at sun.net.www.http.HttpClient.New(HttpClient.java:364) at sun.net.www.http.HttpClient.New(HttpClient.java:345) at sun.net.www.http.HttpClient.New(HttpClient.java:340) at sun.net.www.protocol.http.HttpURLConnection.plainConnect(HttpURLConnection.java:506) at sun.net.www.protocol.http.HttpURLConnection.connect(HttpURLConnection.java:497) at sun.net.www.protocol.http.HttpURLConnection.getInputStream(HttpURLConnection.java:599) at java.net.URL.openStream(URL.java(Inlined Compiled Code)) at org.apache.xerces.impl.XMLEntityManager.setupCurrentEntity(Unknown Source) at org.apache.xerces.impl.XMLEntityManager.startEntity(Unknown Source) at org.apache.xerces.impl.XMLEntityManager.startDTDEntity(Unknown Source) at org.apache.xerces.impl.XMLDTDScannerImpl.setInputSource(Unknown Source) at org.apache.xerces.impl.XMLDocumentScannerImpl$DTDDispatcher.dispatch(Unknown Source) at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unknown Source) at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source) at org.apache.xerces.parsers.DTDConfiguration.parse(Unknown Source) at org.apache.xerces.parsers.XMLParser.parse(Unknown Source) at org.apache.xerces.parsers.DOMParser.parse(Unknown Source) at org.apache.xerces.jaxp.DocumentBuilderImpl.parse(Unknown Source) at javax.xml.parsers.DocumentBuilder.parse(Unknown Source) at org.springframework.beans.factory.xml.XmlBeanDefinitionReader.loadBeanDefinitions(XmlBeanDefinitionReader.java:161) at org.springframework.beans.factory.support.AbstractBeanDefinitionReader.loadBeanDefinitions(AbstractBeanDefinitionReader.java:125) at org.springframework.beans.factory.support.AbstractBeanDefinitionReader.loadBeanDefinitions(AbstractBeanDefinitionReader.java:141) at org.springframework.context.support.AbstractXmlApplicationContext.loadBeanDefinitions(AbstractXmlApplicationContext.java:113) at org.springframework.context.support.AbstractXmlApplicationContext.loadBeanDefinitions(AbstractXmlApplicationContext.java:81) at org.springframework.context.support.AbstractRefreshableApplicationContext.refreshBeanFactory(AbstractRefreshableApplicationContext.java:89) at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:268) at org.springframework.context.support.ClassPathXmlApplicationContext.init(ClassPathXmlApplicationContext.java:81) at org.springframework.context.support.ClassPathXmlApplicationContext.init(ClassPathXmlApplicationContext.java:66) at org.springframework.context.access.ContextJndiBeanFactoryLocator.createBeanFactory(ContextJndiBeanFactoryLocator.java:86) at org.springframework.context.access.ContextJndiBeanFactoryLocator.useBeanFactory(ContextJndiBeanFactoryLocator.java:70) at org.springframework.ejb.support.AbstractEnterpriseBean.loadBeanFactory(AbstractEnterpriseBean.java:117) at org.springframework.ejb.support.AbstractMessageDrivenBean.ejbCreate(AbstractMessageDrivenBean.java:76) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:79) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java(Compiled Code)) at java.lang.reflect.Method.invoke(Method.java(Compiled Code)) at com.ibm.ejs.container.MessageDrivenBeanO.init(MessageDrivenBeanO.java:137) at com.ibm.ejs.container.CMMessageDrivenBeanO.init(CMMessageDrivenBeanO.java:67) at
Re: java.net.UnknownHostException: www.ibatis.com
The new DOCTYPES are on the iBATIS home page in the news section. Please make sure you are using them exactly as specified. Also, there was an issue related to this problem some time ago (I can't remember exactly which release fixed the issue). If you still have a problem after upgrading to the new DOCTYPES, then you might also need to upgrade to the latest version of iBATIS. This is all related to the move to Apache. Jeff Butler On 4/26/06, Ed Griebel [EMAIL PROTECTED] wrote: Sorry, I don't know enough about Spring to be able to help you. In myexperience with Struts and tomcat, jboss, and weblogic, DTDs are looked up automatically in the JARs that are in the WAR/EAR file aslong as the DTD spec in the XML file matches EXACTLY the decl at thetop of the DTD file in the jar.-edOn 4/25/06, Simone Chiucchi [EMAIL PROTECTED] wrote: Ed, I modified the host as you suggested, but I got the following exception: org.springframework.beans.factory.BeanDefinitionStoreException : IOException parsing XML document from class path resource [geeg-data-orm-sqlmap.xml]; nested exception is java.net.ConnectException : Connection refused: connect java.net.ConnectException : Connection refused: connect at java.net.PlainSocketImpl.socketConnect(Native Method) at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:329) at java.net.PlainSocketImpl.connectToAddress (PlainSocketImpl.java:194) at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:181) at java.net.Socket.connect(Socket.java:459) at java.net.Socket.connect(Socket.java :409) at sun.net.NetworkClient.doConnect(NetworkClient.java:157) at sun.net.www.http.HttpClient.openServer(HttpClient.java:414) at sun.net.www.http.HttpClient.openServer (HttpClient.java:628) at sun.net.www.http.HttpClient.init(HttpClient.java:331) at sun.net.www.http.HttpClient.init(HttpClient.java:292) at sun.net.www.http.HttpClient.New (HttpClient.java:364) at sun.net.www.http.HttpClient.New(HttpClient.java:345) at sun.net.www.http.HttpClient.New(HttpClient.java:340) at sun.net.www.protocol.http.HttpURLConnection.plainConnect (HttpURLConnection.java:506) at sun.net.www.protocol.http.HttpURLConnection.connect(HttpURLConnection.java:497) at sun.net.www.protocol.http.HttpURLConnection.getInputStream (HttpURLConnection.java:599) at java.net.URL.openStream(URL.java(Inlined Compiled Code)) at org.apache.xerces.impl.XMLEntityManager.setupCurrentEntity(Unknown Source) at org.apache.xerces.impl.XMLEntityManager.startEntity(Unknown Source) at org.apache.xerces.impl.XMLEntityManager.startDTDEntity(Unknown Source) at org.apache.xerces.impl.XMLDTDScannerImpl.setInputSource(Unknown Source) at org.apache.xerces.impl.XMLDocumentScannerImpl$DTDDispatcher.dispatch(Unknown Source) at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unknown Source) at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source) at org.apache.xerces.parsers.DTDConfiguration.parse(Unknown Source) at org.apache.xerces.parsers.XMLParser.parse(Unknown Source) at org.apache.xerces.parsers.DOMParser.parse(Unknown Source) at org.apache.xerces.jaxp.DocumentBuilderImpl.parse(Unknown Source) at javax.xml.parsers.DocumentBuilder.parse(Unknown Source) at org.springframework.beans.factory.xml.XmlBeanDefinitionReader.loadBeanDefinitions (XmlBeanDefinitionReader.java:161) at org.springframework.beans.factory.support.AbstractBeanDefinitionReader.loadBeanDefinitions(AbstractBeanDefinitionReader.java:125) at org.springframework.beans.factory.support.AbstractBeanDefinitionReader.loadBeanDefinitions (AbstractBeanDefinitionReader.java:141) at org.springframework.context.support.AbstractXmlApplicationContext.loadBeanDefinitions(AbstractXmlApplicationContext.java:113) at org.springframework.context.support.AbstractXmlApplicationContext.loadBeanDefinitions (AbstractXmlApplicationContext.java:81) at org.springframework.context.support.AbstractRefreshableApplicationContext.refreshBeanFactory(AbstractRefreshableApplicationContext.java:89) at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:268) at org.springframework.context.support.ClassPathXmlApplicationContext.init( ClassPathXmlApplicationContext.java:81) at org.springframework.context.support.ClassPathXmlApplicationContext.init(ClassPathXmlApplicationContext.java:66) at org.springframework.context.access.ContextJndiBeanFactoryLocator.createBeanFactory (ContextJndiBeanFactoryLocator.java:86) at org.springframework.context.access.ContextJndiBeanFactoryLocator.useBeanFactory(ContextJndiBeanFactoryLocator.java:70) at org.springframework.ejb.support.AbstractEnterpriseBean.loadBeanFactory (AbstractEnterpriseBean.java:117) at org.springframework.ejb.support.AbstractMessageDrivenBean.ejbCreate(AbstractMessageDrivenBean.java:76) at sun.reflect.NativeMethodAccessorImpl.invoke0 (Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:79) at
Update Statement Error ?
Title: Update Statement Error ?
Hi All,
I am getting the following error when calling an Update. I can see UserDTO is passes properly with data. May be declaration is not right ?
Error
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in com/ml/camsweb/resources/User.xml.
--- The error occurred while applying a parameter map.
--- Check the update-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: java.sql.SQLException: JZ006: Caught IOException: java.io.IOException: JZ0SL: Unsupported SQL type 0.
Caused by: java.sql.SQLException: JZ006: Caught IOException: java.io.IOException: JZ0SL: Unsupported SQL type 0.
Update
public void update(UserDTO userDTO) throws SQLException {
try {
sqlMapCams.startTransaction();
sqlMapCams.update(update,userDTO);
sqlMapCams.commitTransaction();
} finally{
sqlMapCams.endTransaction();
}
}
User.XML
typeAlias alias=UserObject type=com.ml.camsweb.dto.UserDTO/
update id=update parameterClass=UserObject
UPDATE users
SET
first_name = #firstName#,
last_name = #lastName#,
phone = #phone#,
fax = #fax#,
e_mail = #email#,
location = #location#,
address1 = #address1#,
address2 = #address2#,
city = #city#,
state = #state#,
zip = #zip#,
password = #password#,
password_last_updated = #password_last_updated#
WHERE user_id = #userId#
/update
Thanks,
Ali
(201) 671-5499
If you are not an intended recipient of this e-mail, please notify the sender, delete it and do not read, act upon, print, disclose, copy, retain or redistribute it. Click here for important additional terms relating to this e-mail. http://www.ml.com/email_terms/
Re: Update Statement Error ?
Title: Update Statement Error ?
Ali,
It could be because of null value(s) in password
date etc.Check the datatype acceptable value for fields in db table
vs the dto. Execute the SQL outside ibatis (winsql, plsql etc)with
the values you are passing when this error is occuring. That should give
you a better error message.
Balaji
- Original Message -
From:
Ali, Mohammed
(Liquidity Risk Technology)
To: user-java@ibatis.apache.org
Sent: Wednesday, April 26, 2006 9:25
AM
Subject: Update Statement Error ?
Hi All, I am
getting the following error when calling an Update. I can see UserDTO is
passes properly with data. May be declaration is not right ?
Error com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in
com/ml/camsweb/resources/User.xml. --- The error occurred while applying a parameter
map. --- Check the
update-InlineParameterMap. --- Check the statement (update failed). --- Cause: java.sql.SQLException: JZ006:
Caught IOException: java.io.IOException: JZ0SL: Unsupported SQL type 0.
Caused by: java.sql.SQLException:
JZ006: Caught IOException: java.io.IOException: JZ0SL: Unsupported SQL type
0.
Update public void update(UserDTO userDTO) throws SQLException {
try
{
sqlMapCams.startTransaction();
sqlMapCams.update("update",userDTO);
sqlMapCams.commitTransaction();
} finally{
sqlMapCams.endTransaction();
}
}
User.XML typeAlias alias="UserObject" type="com.ml.camsweb.dto.UserDTO"/
update id="update"
parameterClass="UserObject" UPDATE users
SET first_name =
#firstName#, last_name =
#lastName#, phone = #phone#,
fax = #fax#,
e_mail = #email#,
location =
#location#, address1 =
#address1#, address2 =
#address2#, city = #city#,
state = #state#,
zip = #zip#,
password =
#password#,
password_last_updated = #password_last_updated#
WHERE user_id =
#userId# /update
Thanks, Ali (201) 671-5499
If you are not an intended recipient of this e-mail, please notify the
sender, delete it and do not read, act upon, print, disclose, copy, retain or
redistribute it. Click here for
important additional terms relating to this e-mail. http://www.ml.com/email_terms/
RE: Update Statement Error ?
Title: RE: Update Statement Error ?
Hi All,
I ran the update sql outside with the same values and no issues.
I have null data values in updates as column allows NULLS for VARCHAR. I think it is giving error on null data.
Is there an issue in IBATIS with null data updating in Varchar field?
Thanks,
Ali
(201) 671-5499
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Larry Meadors
Sent: Wednesday, April 26, 2006 11:08 AM
To: user-java@ibatis.apache.org; Balaji
Subject: Re: Update Statement Error ?
Another quick-n-dirty way to find these (if you have a unit test) is
to remove columns from the update until it works. The last one you
removed is the problem.
Larry
On 4/26/06, Balaji [EMAIL PROTECTED] wrote:
Ali,
It could be because of null value(s) in password date etc. Check the
datatype acceptable value for fields in db table vs the dto. Execute the
SQL outside ibatis (winsql, plsql etc) with the values you are passing when
this error is occuring. That should give you a better error message.
Balaji
- Original Message -
From: Ali, Mohammed (Liquidity Risk Technology)
To: user-java@ibatis.apache.org
Sent: Wednesday, April 26, 2006 9:25 AM
Subject: Update Statement Error ?
Hi All,
I am getting the following error when calling an Update. I can see UserDTO
is passes properly with data. May be declaration is not right ?
Error
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in
com/ml/camsweb/resources/User.xml.
--- The error occurred while applying a parameter map.
--- Check the update-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: java.sql.SQLException: JZ006: Caught IOException:
java.io.IOException: JZ0SL: Unsupported SQL type 0.
Caused by: java.sql.SQLException: JZ006: Caught IOException:
java.io.IOException: JZ0SL: Unsupported SQL type 0.
Update
public void update(UserDTO userDTO) throws SQLException {
try {
sqlMapCams.startTransaction();
sqlMapCams.update(update,userDTO);
sqlMapCams.commitTransaction();
} finally{
sqlMapCams.endTransaction();
}
}
User.XML
typeAlias alias=UserObject type=com.ml.camsweb.dto.UserDTO/
update id=update parameterClass=UserObject
UPDATE users
SET
first_name = #firstName#,
last_name = #lastName#,
phone = #phone#,
fax = #fax#,
e_mail = #email#,
location = #location#,
address1 = #address1#,
address2 = #address2#,
city = #city#,
state = #state#,
zip = #zip#,
password = #password#,
password_last_updated = #password_last_updated#
WHERE user_id = #userId#
/update
Thanks,
Ali
(201) 671-5499
If you are not an intended recipient of this e-mail, please notify the
sender, delete it and do not read, act upon, print, disclose, copy, retain
or redistribute it. Click here for important additional terms relating to
this e-mail. http://www.ml.com/email_terms/
If you are not an intended recipient of this e-mail, please notify the sender, delete it and do not read, act upon, print, disclose, copy, retain or redistribute it. Click here for important additional terms relating to this e-mail. http://www.ml.com/email_terms/
Re: Update Statement Error ?
Title: RE: Update Statement Error ?
If you are using sybase jconnect here
iswhat their error guide says
JZ0SL
Unsupported SQL type _.
Description: The application has declared a parameter to be of a type that
jConnect does not support.
Action: If possible, try declaring the parameter to be of a different type.
Do not use Types.NULL or PreparedStatement.setObject (null).
-
- Original Message -
From:
Ali, Mohammed
(Liquidity Risk Technology)
To: user-java@ibatis.apache.org ; [EMAIL PROTECTED] ; Balaji
Sent: Wednesday, April 26, 2006 10:54
AM
Subject: RE: Update Statement Error
?
Hi All, I ran the update sql outside with the same
values and no issues.
I have "null" data values in
updates as column allows NULLS for VARCHAR. I think it is giving error on
"null" data.
Is there an issue in IBATIS with
null data updating in Varchar field?
Thanks, Ali (201) 671-5499
-Original
Message- From:
[EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]] On Behalf Of Larry Meadors
Sent: Wednesday, April 26, 2006
11:08 AM To: user-java@ibatis.apache.org;
Balaji Subject: Re:
Update Statement Error ?
Another quick-n-dirty way to find
these (if you have a unit test) is to remove columns from the update until it works. The last
one you removed is
the problem.
Larry
On 4/26/06, Balaji
[EMAIL PROTECTED] wrote: Ali, It
could be because of null value(s) in password date etc. Check
the datatype
acceptable value for fields in db table vs the dto. Execute
the SQL
outside ibatis (winsql, plsql etc) with the values you are passing
when this
error is occuring. That should give you a better error
message.
Balaji
- Original Message - From: Ali, Mohammed (Liquidity Risk
Technology)
To: user-java@ibatis.apache.org Sent: Wednesday, April 26, 2006 9:25 AM
Subject: Update Statement
Error ? Hi
All, I am
getting the following error when calling an Update. I can see
UserDTO is
passes properly with data. May be declaration is not right ?
Error
com.ibatis.common.jdbc.exception.NestedSQLException: --- The error occurred
in
com/ml/camsweb/resources/User.xml. --- The error occurred while applying a parameter
map. --- Check
the update-InlineParameterMap. --- Check the statement (update failed).
--- Cause:
java.sql.SQLException: JZ006: Caught IOException: java.io.IOException: JZ0SL:
Unsupported SQL type 0. Caused by: java.sql.SQLException: JZ006: Caught
IOException:
java.io.IOException: JZ0SL: Unsupported SQL type 0. Update public void update(UserDTO userDTO)
throws SQLException {
try {
sqlMapCams.startTransaction();
sqlMapCams.update("update",userDTO);
sqlMapCams.commitTransaction();
} finally{
sqlMapCams.endTransaction();
}
}
User.XML
typeAlias alias="UserObject"
type="com.ml.camsweb.dto.UserDTO"/ update id="update"
parameterClass="UserObject" UPDATE
users SET
first_name =
#firstName#, last_name =
#lastName#, phone =
#phone#, fax =
#fax#, e_mail =
#email#, location =
#location#, address1 =
#address1#, address2 =
#address2#, city =
#city#, state =
#state#, zip =
#zip#, password =
#password#,
password_last_updated = #password_last_updated# WHERE user_id =
#userId# /updateThanks, Ali (201) 671-5499
If you are not an intended recipient of this e-mail, please notify
the sender,
delete it and do not read, act upon, print, disclose, copy,
retain or
redistribute it. Click here for important additional terms relating
to this
e-mail. http://www.ml.com/email_terms/
If you are not an intended recipient of this e-mail, please notify the
sender, delete it and do not read, act upon, print, disclose, copy, retain or
redistribute it. Click here for
important additional terms relating to this e-mail. http://www.ml.com/email_terms/
Re: Update Statement Error ?
There is no iBATIS issue with NULL. But in general with nulls, you need to change your mapping to let iBATIS know about the dataype. For example:
first_name = #firstName:VARCHAR#, last_name = #lastName:VARCHAR#, phone = #phone:VARCHAR#, fax = #fax:VARCHAR#, e_mail = #email:VARCHAR#, location = #location:VARCHAR#,
address1 = #address1:VARCHAR#, address2 = #address2:VARCHAR#, city = #city:VARCHAR#, state = #state:VARCHAR#, zip = #zip:VARCHAR#, password = #password:VARCHAR#,
password_last_updated = #password_last_updated:VARCHAR# WHERE user_id = #userId:INTEGER#
(Note: use your actual datatypes, I'm just guessing)
Jeff Butler
On 4/26/06, Ali, Mohammed (Liquidity Risk Technology) [EMAIL PROTECTED] wrote:
Hi All, I ran the update sql outside with the same values and no issues.
I have null data values in updates as column allows NULLS for VARCHAR. I think it is giving error on null data.
Is there an issue in IBATIS with null data updating in Varchar field?
Thanks, Ali (201) 671-5499
-Original Message- From:
[EMAIL PROTECTED] [
mailto:[EMAIL PROTECTED]] On Behalf Of Larry Meadors Sent: Wednesday, April 26, 2006 11:08 AM
To: user-java@ibatis.apache.org; Balaji
Subject: Re: Update Statement Error ?
Another quick-n-dirty way to find these (if you have a unit test) is to remove columns from the update until it works. The last one you
removed is the problem.
Larry
On 4/26/06, Balaji [EMAIL PROTECTED] wrote:
Ali,
It could be because of null value(s) in password date etc. Check the datatype acceptable value for fields in db table vs the dto. Execute the
SQL outside ibatis (winsql, plsql etc) with the values you are passing when this error is occuring. That should give you a better error message.
Balaji
- Original Message - From: Ali, Mohammed (Liquidity Risk Technology)
To: user-java@ibatis.apache.org
Sent: Wednesday, April 26, 2006 9:25 AM Subject: Update Statement Error ?
Hi All, I am getting the following error when calling an Update. I can see UserDTO
is passes properly with data. May be declaration is not right ?
Error com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in com/ml/camsweb/resources/User.xml. --- The error occurred while applying a parameter map.
--- Check the update-InlineParameterMap. --- Check the statement (update failed).
--- Cause: java.sql.SQLException: JZ006: Caught IOException: java.io.IOException
: JZ0SL: Unsupported SQL type 0. Caused by: java.sql.SQLException: JZ006: Caught IOException:
java.io.IOException: JZ0SL: Unsupported SQL type 0. Update
public void update(UserDTO userDTO) throws SQLException { try {
sqlMapCams.startTransaction();
sqlMapCams.update(update,userDTO); sqlMapCams.commitTransaction();
} finally{ sqlMapCams.endTransaction();
} }
User.XML typeAlias alias=UserObject type=com.ml.camsweb.dto.UserDTO/
update id=update parameterClass=UserObject
UPDATE users SET first_name = #firstName#,
last_name = #lastName#, phone = #phone#,
fax = #fax#, e_mail = #email#, location = #location#,
address1 = #address1#, address2 = #address2#,
city = #city#, state = #state#,
zip = #zip#, password = #password#, password_last_updated = #password_last_updated#
WHERE user_id = #userId# /update
Thanks,
Ali (201) 671-5499
If you are not an intended recipient of this e-mail, please notify the
sender, delete it and do not read, act upon, print, disclose, copy, retain or redistribute it. Click here for important additional terms relating to
this e-mail.
http://www.ml.com/email_terms/
If you are not an intended recipient of this e-mail, please notify the sender, delete it and do not read, act upon, print, disclose, copy, retain or redistribute it.
Click here for important additional terms relating to this e-mail. http://www.ml.com/email_terms/
New code generator for iBATIS - Empusa
Hi, does any one know how to add a new code generator for iBATIS? I am finishing the first beta version now and I would like to make it public. Thanks, Vladimir. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
RE: pager in ibatis
Hi, I don't know if this is true, but i think i've tried that solution before, and when i see the log generated from the ResultSet, it actually query all the data, and then selecting only the required record to show.. Yusuf. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 26, 2006 2:22 PM To: user-java@ibatis.apache.org Subject: SV: pager in ibatis Hi there! Yusuf wrote I think it's better off making the paging logic yourself in the application, and then querying only the records you want to show, for example in oracle you can use the rownum to fetch the data like select * from (select rownum row, a.* from tabel1 as a) where row between 21 and 30 /Yusuf wrote Why not use the ibatis the queryForList with pageNumber and pageSize parameters like this: sqlMap.queryForList(searchCompanies, searchText, ((pageNumber-1) * pageSize), pageSize); ?? Then you only keep track of the pageNumber you are at and don't need to make your query database specific... and you don't worry about the caching, it cache the page you are at and only that as far as I have understood it. You also need the countSearchCompanies, with an exactly the same query but with count in it . to find out how many pages it totally for that search. So you can specifiy page 1, 2, 3, 4 in e.g. your webpage. We use this strategy with success here: http://www.aguiden.no (search for sykkel (bike),gives you two pages) Since queryForPaginatedList don't have a count (??), I don't think it load the totally result set, only the page you are at, and have lazy loading of the next page... smooth. So far as I have understood it ??? Take care, Erlend --- -Opprinnelig melding- Fra: Yusuf [mailto:[EMAIL PROTECTED] Sendt: 13. april 2006 02:44 Til: user-java@ibatis.apache.org Emne: RE: pager in ibatis I think it's better off making the paging logic yourself in the application, and then querying only the records you want to show, for example in oracle you can use the rownum to fetch the data like select * from (select rownum row, a.* from tabel1 as a) where row between 21 and 30 because using the queryForPaginatedList (without caching) will always fetch the entire result and then showing only the selected records, and if the record count is high (like thousands..) then the performance will be bad. cmiiw, Yusuf. -Original Message- From: John Chien [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 12, 2006 11:27 PM To: user-java@ibatis.apache.org Subject: Re: pager in ibatis L:arry: Thank you for the information. The queryForPaginatedList() will return a list, then I can check for the existence of successive records by calling the isNextPageAvailable(). and go to that page by calling gotoPage(); However, I can not keep this list in my server due to concurrecy reason. Does that mean that I have to call the same method every time and give it the page number for it to return me the records ? I am doing the web development. How can I make use of this returned list in JSP with Struct development ? Should I keep the list in the session or request scope ? Thanks, John Chien Larry Meadors wrote: queryForPaginatedList() Larry On 4/12/06, John Chien [EMAIL PROTECTED] wrote: Dear sir: I have a feature that requires me to return query result of a selection statment in certain amount of successive records every time. This is just like a page. For example: SELECT id, name FROM employee; The selection result of this statement is a list. However, I want to only get 30 records every time. For the first call, I want to get the first 30 (1 - 30) records, for thext call, I would like to get the next 30 (31 - 60) records. How can I do this in Ibatis ? Thanks, John Chien
RE: Invalid Column Type issue in confluence
Hi, regarding the 'invalid column type' problem, I've tried to reproduce
it :
i made 2 insert statement, one using map as a parameter, another using
simple pojo class :
insert id=insertEmp parameterClass=java.util.HashMap
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO)
VALUES (
#empno:NUMBER#,
#ename:VARCHAR2#,
#job:VARCHAR2#,
#mgr:NUMBER#,
#hiredate:DATE#,
#sal:NUMBER#,
#comm:NUMBER#,
#deptno:NUMBER#)
/insert
insert id=insertEmp2 parameterClass=sample.Emp
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO)
VALUES (
#empno:NUMBER#,
#ename:VARCHAR2#,
#job:VARCHAR2#,
#mgr:NUMBER#,
#hiredate:DATE#,
#sal:NUMBER#,
#comm:NUMBER#,
#deptno:NUMBER#)
/insert
and then in the java class, i tried the first insert (with map as
parameter), without setting the value of column COMM :
try {
Reader reader =
Resources.getResourceAsReader(sql-map-config.xml);
SqlMapClient sqlMap =
SqlMapClientBuilder.buildSqlMapClient(reader);
Map map = new HashMap();
map.put(empno, new Integer(1234));
map.put(ename, YUSUF);
map.put(job, ANALYST);
map.put(mgr, new Integer(7839));
map.put(hiredate, new Date());
map.put(sal, new Integer(2000));
//map.put(comm, null);
map.put(deptno, new Integer(10));
sqlMap.insert(insertEmp, map);
}catch(Exception e) {
e.printStackTrace();
}
and also tried the second insert (with class as parameter), also without
setting the value of column COMM :
try {
Reader reader =
Resources.getResourceAsReader(sql-map-config.xml);
SqlMapClient sqlMap =
SqlMapClientBuilder.buildSqlMapClient(reader);
Emp emp = new Emp();
emp.setEmpno(new Integer(1234));
emp.setEname(YUSUF);
emp.setJob(ANALYST);
emp.setMgr(new Integer(7839));
emp.setHiredate(new Date());
emp.setSal(new Integer(2000));
//emp.setComm()
emp.setDeptno(new Integer(10));
sqlMap.insert(insertEmp2, emp);
}catch(Exception e) {
e.printStackTrace();
}
and both statements failed to execute with the fist stacktrace like this
:
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in sample/sql/sql-map.xml.
--- The error occurred while applying a parameter map.
--- Check the insertEmp2-InlineParameterMap.
--- Check the parameter mapping for the 'comm' property.
--- Cause: java.sql.SQLException: Invalid column type
Caused by: java.sql.SQLException: Invalid column type
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdat
e(GeneralStatement.java:87)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.insert(SqlMapExecut
orDelegate.java:442)
at
com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.insert(SqlMapSessionImpl
.java:85)
at
com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.insert(SqlMapClientImpl.j
ava:57)
at
com.ekalife.mri.sample.SampleServlet.doGet(SampleServlet.java:53)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Applica
tionFilterChain.java:252)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilt
erChain.java:173)
at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValv
e.java:214)
at
org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveCo
ntext.java:104)
at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:5
20)
at
org.apache.catalina.core.StandardContextValve.invokeInternal(StandardCon
textValve.java:198)
at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValv
e.java:152)
at
org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveCo
ntext.java:104)
at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:5
20)
at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java
:137)
at
org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveCo
ntext.java:104)
at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java
:118)
at
org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveCo
ntext.java:102)
at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:5
20)
at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.
java:109)
at
org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveCo
ntext.java:104)
at
Re: Invalid Column Type issue in confluence
The JDBC type specified in the mapping can only be one of the standard JDBC types that are listed in java.sql.Types. Some of the types you are specifying (VARCHAR2, NUMBER) are invalid - and iBATIS will use Types.OTHER
in those cases. This is likely that cause of the problem you are seeing. Switch to one of the supported datatypes - that will probably solve it.
Jeff Butler
On 4/26/06, Yusuf [EMAIL PROTECTED] wrote:
Hi, regarding the 'invalid column type' problem, I've tried to reproduceit :i made 2 insert statement, one using map as a parameter, another using
simple pojo class :insert id=insertEmp parameterClass=java.util.HashMap INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO) VALUES (
#empno:NUMBER#, #ename:VARCHAR2#, #job:VARCHAR2#, #mgr:NUMBER#, #hiredate:DATE#, #sal:NUMBER#, #comm:NUMBER#, #deptno:NUMBER#)/insert
insert id=insertEmp2 parameterClass=sample.Emp INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO) VALUES ( #empno:NUMBER#, #ename:VARCHAR2#,
#job:VARCHAR2#, #mgr:NUMBER#, #hiredate:DATE#, #sal:NUMBER#, #comm:NUMBER#, #deptno:NUMBER#)/insertand then in the java class, i tried the first insert (with map as
parameter), without setting the value of column COMM :try { Reader reader =Resources.getResourceAsReader(sql-map-config.xml); SqlMapClient sqlMap =SqlMapClientBuilder.buildSqlMapClient
(reader); Map map = new HashMap(); map.put(empno, new Integer(1234)); map.put(ename, YUSUF); map.put(job, ANALYST);
map.put(mgr, new Integer(7839)); map.put(hiredate, new Date()); map.put(sal, new Integer(2000)); //map.put(comm, null); map.put
(deptno, new Integer(10)); sqlMap.insert(insertEmp, map);}catch(Exception e) { e.printStackTrace();}and also tried the second insert (with class as parameter), also without
setting the value of column COMM :try { Reader reader =Resources.getResourceAsReader(sql-map-config.xml); SqlMapClient sqlMap =SqlMapClientBuilder.buildSqlMapClient(reader);
Emp emp = new Emp(); emp.setEmpno(new Integer(1234)); emp.setEname(YUSUF); emp.setJob(ANALYST); emp.setMgr(new Integer(7839)); emp.setHiredate
(new Date()); emp.setSal(new Integer(2000)); //emp.setComm() emp.setDeptno(new Integer(10)); sqlMap.insert(insertEmp2, emp);}catch(Exception e) { e.printStackTrace
();}and both statements failed to execute with the fist stacktrace like this:com.ibatis.common.jdbc.exception.NestedSQLException:--- The error occurred in sample/sql/sql-map.xml.--- The error occurred while applying a parameter map.
--- Check the insertEmp2-InlineParameterMap.--- Check the parameter mapping for the 'comm' property.--- Cause: java.sql.SQLException: Invalid column typeCaused by: java.sql.SQLException: Invalid column type
atcom.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:87) atcom.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.insert(SqlMapExecutorDelegate.java
:442) atcom.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.insert(SqlMapSessionImpl.java:85) atcom.ibatis.sqlmap.engine.impl.SqlMapClientImpl.insert(SqlMapClientImpl.java:57) at
com.ekalife.mri.sample.SampleServlet.doGet(SampleServlet.java:53) at javax.servlet.http.HttpServlet.service(HttpServlet.java:689) at javax.servlet.http.HttpServlet.service(HttpServlet.java:802) at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252) atorg.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173) at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214) atorg.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104) atorg.apache.catalina.core.StandardPipeline.invoke
(StandardPipeline.java:520) atorg.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198) atorg.apache.catalina.core.StandardContextValve.invoke(StandardContextValv
e.java:152) atorg.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104) atorg.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
atorg.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137) atorg.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104) atorg.apache.catalina.valves.ErrorReportValve.invoke
(ErrorReportValve.java:118) atorg.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102) atorg.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java
:520) atorg.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) atorg.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
atorg.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520) atorg.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929) atorg.apache.coyote.tomcat5.CoyoteAdapter.service
(CoyoteAdapter.java:160) atorg.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
Re: New code generator for iBATIS - Empusa
The best thing to do is make the tool available publicly somewhere like Sourceforge. You can make an announcement ofavailability on this list, but please don't use this list to support the tool (Sourceforge would offera mailing list service). We also havea WIKI page for 3rd party contributions - feel free to add a description of the tool (and a link) there. Jeff Butler On 4/26/06, Alarcón Vladimir [EMAIL PROTECTED] wrote: Hi, does any one know how to add a new code generatorfor iBATIS? I am finishing the first beta version now and I would like to make it public.Thanks,Vladimir.__Do You Yahoo!?Tired of spam?Yahoo! Mail has the best spam protection around http://mail.yahoo.com
