Hi,
1. Your sql won't work, you left article_categories and
article_to_categories out of the from clause, so mysql has no idea what you
are talking about,
2. you should be able to do this the easy way
<finder>
<name>foobarquery</name>
<query>, article_categories ac, articles_to_categories atc where
articles.ArticleID=atc.ArticleID and atc.ArticleCategoryID=ac.ArticleCategoryID
and ac.ArticleCategory={0}</query>
<order></order>
</finder>
david jencks
On 2001.06.07 14:35:01 -0400 Ray wrote:
>
> --- David Jencks <[EMAIL PROTECTED]> wrote: > Hi,
> > I misunderstood, I thought you wanted to just define
> > the query in jaws.xml.
>
> Naah, I've decided to do it the hard way .... :-)
>
> > If this won't work,
> > maybe you should show your code and indicate where
> > the exception happens.
>
> OK, here's the method ....
>
>
>-----------------------------------------------------------------------------------------------------
>
> public Collection ejbFindByCategory(String category)
> {
> Connection conn = null;
> Vector articles = new Vector();
>
> try
> {
>
> InitialContext jndiContext = Helper.getInitialContext();
> DataSource home_data = (DataSource)
> jndiContext.lookup("java:/mySQLDS");
>
> conn = home_data.getConnection();
>
> Statement statement = conn.createStatement();
>
> String query = "SELECT ArticleID FROM articles " +
> "WHERE
> articles.ArticleID=articles_to_categories.ArticleID " +
> "AND
> articles_to_categories.ArticleCategoryID=article_categories.ArticleCategoryID
>
> " +
> "AND article_categories.ArticleCategory='"+
> category + "'";
>
>
>
> ResultSet rs = statement.executeQuery(query);
>
>
> ArticleHome home = (ArticleHome)ctx.getEJBHome();
>
>
> while (rs.next())
> {
>
> // Now retrieve the entity
>
> articles.addElement(new Integer(rs.getInt("ArticleID")));
> }
>
>
> }
> catch (NamingException e)
> {
> System.out.println("Naming error --> " + e.getMessage());
> }
> catch (SQLException e)
> {
> System.out.println("SQL error --> " + e.getMessage());
> }
> finally
> {
> try
> {
> conn.close();
> }
> catch (SQLException e) {}
> }
>
> return (Collection)articles;
> }
>
> ---------------------------------------------------------------------------------
>
> The exception I get back is:
>
> ------------------------------------------------------------------------------------
>
> [Article] SQL error --> Error during query: Unexpected Exception:
> java.sql.SQLEx
> ception message given: General error: Unknown table
> 'articles_to_categories' in
> where clause
>
> ------------------------------------------------------------------------------------
>
> And 'articles_to_categories' exists, altough I have not defined an entity
>
> for it (there is an entity for 'articles', which doesn't thrown an
> exception.
>
>
> _________________________________________________________
> Do You Yahoo!?
> Get your free @yahoo.com address at http://mail.yahoo.com
>
>
> _______________________________________________
> JBoss-user mailing list
> [EMAIL PROTECTED]
> http://lists.sourceforge.net/lists/listinfo/jboss-user
>
_______________________________________________
JBoss-user mailing list
[EMAIL PROTECTED]
http://lists.sourceforge.net/lists/listinfo/jboss-user
