[Lance Drake]

Hi MyEclipse people,

Well – I was a little quick to call the last hassle as having been vanquished.

The purpose of this note is to ask in which direction should I direct my comments.

There’s a problem, ultimately manifest in a java.sql.SQLException.

[STDOUT] ERROR EmployeeAccess: FinderException for employeeNumber = 12347, ERR = javax.ejb.FinderException: Find failed: java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM employee t0_Employee WHERE' at line 1

After getting the SQL Query Log cranked up in my system I discovered this:

060703 22:56:04         
    30 Prepare     [41] 
    30 Execute     [41] SELECT t0_a.userID FROM users t0_a WHERE (t0_a.userID = '12345')
    30 Prepare     [42] 
    30 Execute     [42] SELECT recnum, userName, userPassword, userPasswordExpires, 
                                    userEmployeeNumber, userRole FROM users WHERE (userID='12345')
    30 Prepare     [43] 
    30 Execute     [43] SELECT t0_a.roleNumber FROM roles t0_a WHERE (t0_a.roleNumber = '1')
    30 Prepare     [44] 
    30 Execute     [44] SELECT recnum, roleName, roleSecurityLevel, roleCreated, roleModified 
                                   FROM roles WHERE (roleNumber='1')
    30 Prepare     [45] 
    30 Execute     [45] SELECT t0_acc.activity FROM access t0_acc 
                                    WHERE (t0_acc.activity = 'RequestEmployeeShiftChangeTickets')
    30 Prepare     [46] 
    30 Execute     [46] SELECT recnum, minLevel, onlyLevel, notLevel FROM access 
                                    WHERE (activity='RequestEmployeeShiftChangeTickets')

    30 Query       SELECT  FROM employee t0_Employee WHERE

    30 Query       commit

What you see above are six properly formed SQL query commands – then, the blurb MySQL is complaing about. BTW – Those first six entries execute without incident.

QUESTION: Where do you imagine this query got clobbered – meaning – “Where is this problem going to get fixed?”

The MyEclipse calls on XDoclet to build the EJB-JAR.xml file that desribes the EJB-QL which is deployed as a JAR into JBoss. The java code makes a call into the ‘EmployeeLocalHome’ interface which requests, “findByEmployeeNumber(employee)”. At that point the call issent of into the jungle.

Here’s the EJB contents of a query that works:

         <query>
            <description><![CDATA[Find by activity]]></description>
            <query-method>
               <method-name>findByActivity</method-name>
               <method-params>
                  <method-param>java.lang.String</method-param>
               </method-params>
            </query-method>
            <ejb-ql><![CDATA[SELECT OBJECT(acc) FROM Access acc WHERE acc.activity = ?1]]></ejb-ql>
         </query>

Here’s the EJB contents of a query that does NOT work:

         <query>
            <description><![CDATA[Find by userID]]></description>
            <query-method>
               <method-name>findByUserID</method-name>
               <method-params>
                  <method-param>java.lang.String</method-param>
               </method-params>
            </query-method>
            <ejb-ql><![CDATA[SELECT OBJECT(a) FROM Employee a WHERE a.employeeUserId = ?1]]></ejb-ql>
         </query>
 

The records are all available via the mysql command line. It’s not a screwed up database. The interesting thing is that, if all but one field are eliminated from the table, the calls work every time. Then you can add fields to the database – and to the EJB refs – and it all works up to maybe five fields total, then it STOPS working and will not resume functionality until you revert – not just one field back – but all the way back down to having just one field.

Yes – I know it’s the birthday of our country – but does anyone have a clue as to how this simple call gets munged?

If you can just point me to the next best place to inquire, that would be wonderful.

Best Wishes [/b]

[Lance Drake]

Oh mann….

After two (long) days – I created another EXACT COPY of the database table using a LOWER-CASE NAME – and the EXACT SAME fields – only ALL LOWER CASE NAMES – copied the EXACT SAME source file, modifed ONLY the TABLE and FIELD NAMES – and it immediately worked.

AllI can say is OUCH! – The original table and field names in the code which did not work matched precisely what was defined for the TABLE and FIELD names in the SQL world… but, evidently, somewhere in there, somebody got confused about it all , and dropped the ball.

Moral of the story: The preventative measures for this problem probably instruct the careful programmer to:

1) name the SQL tables with ALL LOWER CASE NAMES
2) make the field names ALL LOWER CASE.
3) In the JAVADOC EJB headers do the same for ‘schema’ – and all cmp-field definitions.
4) The only place that capitalization should get invoked is for the first letter of the name of the field in the data accessor routines.

DOUBLE-DAY-OUCH!!
…

[Author]

Posts