[dbforms] WHERE clause bug

Mon, 21 Jul 2003 06:18:06 -0700 

Hi,

Here is the exmaple for the WHERE caluse bug for bookstore 1.1.4pr1 as I promised 
before...

These are the values in bookstore database:

INSERT INTO author  (author_id, name, organisation)   VALUES (1, '1 Eco, 
Umberto','test2121');
INSERT INTO author  (author_id, name, organisation)   VALUES (2, '2 test','test');
INSERT INTO book    (book_id, isbn, author_id, title) VALUES (5, 
'3-423-12445-4',1,'Die Insel des vorigen Tages');
INSERT INTO book    (book_id, isbn, author_id, title) VALUES (5, 
'3-423-12445-4',2,'Die Insel des vorigen Tages');

Two book_id's are representing that one book has two authors. In this example it is 
not very clever, but it should represent a part of implementation of many-to-many 
relationship.


Then I have in dbforms-config.xml:

<query  name="BOOKLISTPERAUTHOR" 
  distinct="true"
  from="BOOK LEFT JOIN AUTHOR ON BOOK.AUTHOR_ID = AUTHOR.AUTHOR_ID">
  <field name="BOOK_ID" fieldType="int" isKey="true" autoInc="true"/>
  <field name="ISBN" fieldType="varchar"/>
  <field name="TITLE" fieldType="varchar"/>
  <search name="AUTHOR_ID"/>
</query>


Then I have a testBooklistPerAuthor.jsp:

<%@ taglib uri="/WEB-INF/dbforms.tld" prefix="db" %>
<html xmlns:db="http://www.wap-force.com/dbforms";>
        <head>
                <db:base/>
        </head>
        <body>
                <db:dbform 
                        multipart="false" 
                        autoUpdate="false" 
                        followUp="/tests/testBooklistPerAuthor.jsp" 
                        maxRows="*" tableName="BOOKLISTPERAUTHOR"
                        filter="BOOK_ID=5,AUTHOR_ID=1">
                        <db:header>
                                <db:errors/>  
                                <table>
                        </db:header>
                        <db:body allowNew="false">
                                <tr>
                                        <td><db:label fieldName="BOOK_ID"/></a></td>
                                        <td><db:label fieldName="ISBN"/></a></td>
                                        <td><db:label fieldName="TITLE"/></a></td>
                                </tr>
                        </db:body>
                        <db:footer>
                                </table>
                        </db:footer>
                </db:dbform>
</body>
</html>    


I look MySQL log and I would expect a query:

SELECT  DISTINCT BOOK_ID, ISBN, TITLE
FROM BOOK LEFT JOIN AUTHOR
ON BOOK.AUTHOR_ID = AUTHOR.AUTHOR_ID
WHERE   ( ( AUTHOR_ID =  1  ) ) AND (  ( BOOK_ID =  '5'  ) )
ORDER BY BOOK_ID

But instead I got:

SELECT  DISTINCT BOOK_ID, ISBN, TITLE
FROM BOOK LEFT JOIN AUTHOR
ON BOOK.AUTHOR_ID = AUTHOR.AUTHOR_ID
WHERE   ( ( AUTHOR_ID =  5  ) ) (  ( BOOK_ID =  '1'  ) )
ORDER BY BOOK_ID


So, AND is missing from WHERE caluse and number 5 and number 1 are in the wrong 
places. We discussed this missing AND before and I'll put here the explanation of how 
the values go in the wrong order below. It's the same as I sent before.

1. Jsp page calls doStartTag() in DbFormTag.java

2. doStartTag() method calls doConstrainedSelect(..., FieldValue[]
fvEqual, ...) in Table.java fvEqual includes the list of the fields which are coming 
to where and having part of the query

3. doConstrainedSelect() calls getSelectQuery(..., fvEqual, ...) in
Query.java

4. getSelectQuery() separates values from fvEqual to be placed in
where- and having clauses and this usually changes the order of the items. (Items are 
in different order in query string than in fvEqual list.)

5. getSelectQuery() returns query string to doConstrainedSelect(),
and now prepared statements ? (guestion mark) values might be in the different order 
than values in original fvEqual

6. doConstrainedSelect() calls getDoSelectResultSet(..., fvEqual,
...) in Table.java with original fvEqual

7. getDoSelectResultSet() calls populateWhereEqualsClause(fvEquals,
...) in FieldValue.java

8. populateWhereEqualsClause() populates prepared statements values
in that order that the values are in fvField, which is not nesessery the same anymore 
as in the query string

So, this error happens only when query has the values for both where
and having clause. if there is only where caluse, order of
the values is not changed in getSelectQuery().


I must say that I don't know how to fix this...

Regards,
Salient
-- 
__________________________________________________________
Sign-up for your own FREE Personalized E-mail at Mail.com
http://www.mail.com/?sr=signup

CareerBuilder.com has over 400,000 jobs. Be smarter about your job search
http://corp.mail.com/careers



-------------------------------------------------------
This SF.net email is sponsored by: VM Ware
With VMware you can run multiple operating systems on a single machine.
WITHOUT REBOOTING! Mix Linux / Windows / Novell virtual machines at the
same time. Free trial click here: http://www.vmware.com/wl/offer/345/0
_______________________________________________
DbForms Mailing List

http://www.wap-force.net/dbforms

Reply via email to