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 Clinton Begin <cl...@gmail.com> on 2005/04/24 21:50:02 UTC
Re: Populating 2 lists within a class
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 <er...@gmail.com> 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
> >
> >
>
>
Re: Populating 2 lists within a class
Posted by Larry Meadors <la...@gmail.com>.
I have yet to run into a database that will not work with iBATIS.
I have heard good things about postgress, or if you want embedded java:
hsqldb.
Larry
On 4/24/05, Eric T. Blue <er...@gmail.com> wrote:
>
> 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 <er...@gmail.com> 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
> >>>
> >>>
> >>>
> >>>
> >>
> >>
> >
> >
> >
>
>
Re: Populating 2 lists within a class
Posted by "Eric T. Blue" <er...@gmail.com>.
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 <er...@gmail.com> 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
>>>
>>>
>>>
>>>
>>
>>
>
>
>