Hi, --- I send this again because it doesn't seems to be appear in the Sourceforge list. Some problems in the Sourceforge? I'm sorry if this appears twice ---
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
