Clinton,
Thanks for the quick reply. This is definitely good information. I was only using Access for evaluation since it allowed my to import an existing ODBC datasource for testing. Other than MySql, can you recommend another small-midsize db that works with iBatis? Something along the lines of Derby or SQLite.
Clinton Begin wrote:
Eric,
Please email all questions to the mailing list.
This is a severe limitation of both the JDBC/ODBC bridge driver, ODBC in general and MS Access in general. All three suck big time.
My first recommendation is to get a real database. There are plenty of high quality, free databases to choose from. There's no reason to use MS Access, unless there's a business driver constraining you.
If you are constrained by some hard requirement to use MS Access, then I'm afraid you're out of luck. There are 2 key limitations that will keep you from being successful with your intention:
1) MS Access can only have a single result set open at a time. Therefore, your nested SELECT statement will fail. This is also why
the join mapping (N+1) works -- it's a single result set.
2) Columns must be read in the order that they're specified in the statement. For this reason you must avoid SELECT * and also avoid interrupting the serial reading of all columns (including running an intermediate select statement).
Other than that, you have all of the usual threading problems associated with MS Access and the JDBC/ODBC bridge driver.
In any case, best of luck in finding a solution.
Clinton
On 4/24/05, Eric T. Blue <[EMAIL PROTECTED]> wrote:
Hi Clinton,
First of all I wanted to say that you guys have done a fantastic job on Ibatis. I'm emailing a little late on this thread, but I've actually run into a similar issue to this that I've been trying to track down for about 3 days! I'm using MS Access and am trying to populate a list for a 1:n relationship with a select statement.
<resultMap id="categoryResult" class="Category"> <result property="id" column="CategoryID"/> <result property="name" column="Name"/> <result property="parentId" column="Parent"/> <result property="articles" column="CategoryID" select="getArticlesbyCategory"/> </resultMap>
...
Error:
--- Check the result mapping for the 'articles' property. --- Cause: java.sql.SQLException: No data found Caused by: java.sql.SQLException: No data found
Sure enough, after finding this thread I swapped the order of "articles" and "id" and I get an exception on the "id" column. If I comment out id, the getArticlesbyCategory will run just fine. What is the resolution to this, and is it something specific with MS Access and the JdbcOdbc driver?
I've experimented and can populate this by doing the n+1 workaround solution. But I would still prefer to use the select method in some circumstances. Thanks!
Re: Populating 2 lists within a class
Clinton Begin Wed, 02 Mar 2005 15:35:29 -0800
Uh oh. Would you by chance be using Microsoft Access or perhaps MS SQL Server?
Cheers, Clinton
On Wed, 2 Mar 2005 15:02:50 -0800, Warren Bell
<[EMAIL PROTECTED]> wrote:
I have a class that has two Lists as properties. I can populate one or the other List, but I can not seem to get both of them populated. I have switched the order of the List mappings and it always has a problem with the last one listed. The two lists are itemMovement and itemPromoPrices1 in the result map "get-Item-Result1". I am getting a java.sql.SQLException: No data found Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:. Any help will be appreciated.
<resultMap id="get-Item-Result1" class="item"> <result property="upc" column="INV_ScanCode"/> <result property="desc" column="INV_Name"/> <result property="brand" column="BRD_Name"/> <result property="size" column="INV_Size"/> <result property="unit" column="PI1_Description"/> <result property="department" column="DPT_Name"/> <result property="category" column="PI4_Description"/> <result property="vendorSKU" column="ORD_SupplierStockNumber"/> <result property="cost" column="INV_LastCost"/> <result property="price" column="SIB_BasePrice"/> <result property="itemMovement" column="INV_ScanCode" select="getItemMovement1"/> <result property="itemPromoPrices1" column="INV_ScanCode" select="getItemPromoPrice1"/> </resultMap>
<resultMap id="get-Item-Promo-Price-Result1" class="promoPrice1"> <result property="upc" column="INV_ScanCode"/> <result property="description" column="PCD_WRKName"/> <result property="salePrice" column="PCD_Price_pl1"/> <result property="promoStartDate" column="PCD_PSWStartDate"/> <result property="promoEndDate" column="PCD_PSWEndDate"/> <result property="priorityLevel" column="PCD_PriorityLevel"/> <result property="discountKey" column="PCD_DIS_FK_pl1" nullValue="-999"/> </resultMap>
<resultMap id="get-Item-Movement-Result1" class="movement"> <result property="upc" column="Scancode"/> <result property="movementDate" column="ISG_StartTime"/> <result property="quantity" column="SIT_Quantity"/> </resultMap>
<select id="getItem1" parameterClass="java.lang.String" resultMap="get-Item-Result1"> SELECT INV_ScanCode, INV_Name, BRD_Name, INV_Size, PI1_Description, DPT_Name, PI4_Description, ORD_SupplierStockNumber, INV_LastCost, SIB_BasePrice FROM v_InventoryMaster WHERE INV_ScanCode = #value# </select>
<select id="getItemPromoPrice1" parameterClass="java.lang.String" resultMap="get-Item-Promo-Price-Result1"> SELECT INV_ScanCode, PCD_WRKName, PCD_Price_pl1, PCD_PSWStartDate, PCD_PSWEndDate, PCD_PriorityLevel, PCD_DIS_FK_pl1 FROM v_InventoryMaster, PriceChangeData WHERE INV_PK = PCD_INV_FK AND INV_CPK = PCD_INV_CFK AND INV_ScanCode = #value# </select>
<select id="getItemMovement1" parameterClass="java.lang.String" resultMap="get-Item-Movement-Result1"> SELECT Scancode, ISG_StartTime, SIT_Quantity FROM v_SummaryItems WHERE Scancode = #value# </select>
Thanks,
Warren Bell Systems Administrator Clark's Nutritional Centers 4225 Market St. Riverside, CA 92501 951-321-1960 ext. 142 909-645-8864 mobile
