Inconsistent results with sql:param

[Will Fleming]

Here is the source of my problems. This code works fine on 3 out of the 4 computers that I have, but when executed on one of them with the same input and the same database, I fail to get a response while the other 3 respond perfectly.

The little check before the actual query returns the same/correct values in all situations.

<c:if test="${empty param.errorMsg }">
Entering SQL: ${param.login } and ${param.passord }<br>
<sql:query var="userInfo" scope="page" dataSource="mySQL">
 SELECT * FROM players WHERE login = ? AND password = ?
 
<sql:param value="${param.login }" />
<sql:param value="${param.password }" />
</sql:query>
</c:if>

I have tried replacing the correct values into the SQL statement and then I get a valid result. i.e.

<c:if test="${empty param.errorMsg }">
Entering SQL: ${param.login } and ${param.passord }<br>
<sql:query var="userInfo" scope="page" dataSource="mySQL">
 SELECT * FROM players WHERE login = 'user' AND password = 'password'
</sql:query>
</c:if>

Returns the correct information about the user. The mysql command line client also returns the correct values.

When I take it a step further and insert the following, I also fail to get a valid result.

<c:if test="${empty param.errorMsg }">
Entering SQL: ${param.login } and ${param.passord }<br>
<sql:query var="userInfo" scope="page" dataSource="mySQL">
 SELECT * FROM players WHERE login = ? AND password = ?
 
<sql:param value="user" />
<sql:param value="password" />
</sql:query>
</c:if>    

This totally baffles me. I am running jBoss 4.0.3SP1, JDK 1.5-6, MySQL 5.0 on all 4 machines. I even copied the entire jBoss directory over to the faulty computer in thoughts that I had a mistake in a config file, but that still results in the above behavior.

Good comps
AMD64X2 with x64smp kernel 2.6.15, 2G RAM,
P-M with 686 kernel 2.6.15, 2G RAM
Celeron-D 686 kernel 2.6.15 768M RAM

Bad comp
P4-HT with 686smp kernel 2.6.15, 1G RAM

Java install identical (Sun Java via repsoitories) on all 4 systems.
[ June 20, 2006: Message edited by: Will Fleming ]

[Stefan Evans]

Just to get this clear.

It works if you hardcode the parameter values into the query.
It fails if you use the <sql:param> tag?

Does it produce any error messages on the page or in the log?

[Will Fleming]

That is correct. If hard coded into the SQL statement, it works. It also works when the same SQL statement is run in the mysql command line client. It works fine on all 3 other computers, but of course the one I want to use as my server is the one that it fails on.

As for the logs, I will have to check them when I get home, but due to further processing on the page, zero results are returned and the user gets the message that the user is not valid.

Here is the code for that section of the page. The error message gets assigned and the page forwards (to itself).

On the second pass, the error message is present, so no SQL is attempted and the forward below is skipped. Later on the page either forwards a valid user to his last page (or index.jsp) or prints the error message if present.

It works like a champ on the other 3 computers.

    
<c:if test="${(userInfo.rowCount == 0 ) && empty param.errorMsg }">
<jsp:forward page="/login.jsp">
<jsp:param name="errorMsg"
value="That login/password combination is not valid" />
<jsp:param name="login" value="${param.login }" />
<jsp:param name="password" value="${param.password }" />
<jsp:param name="origURL" value="${param.origURL }" />
</jsp:forward>
</c:if>

EDIT: No error messages/stack trace from the container and the page processing continues until the error message is assigned and displayed to the user. The user is not sucessfully logged in.
[ June 20, 2006: Message edited by: Will Fleming ]

[Stefan Evans]

Seeing as it is the <c:param> tags, its either the tag itself is not working, or something about prepared statements.
I presume you are using JSTL1.1, and importing it with <%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql"%>

Ok, well lets check a few things.
Is EL enabled on the server? does ${1 + 2} print out as 3?
Is your web.xml declared as 2.4?
Are you using the same JDBC driver in each case?
Are you certain you are connecting to the right database?

Suggestion - try it from scriptlet code with a prepared statement, to see if you can "manually" query the database and get the right answer.
As long as you understand the scriptlet code is for debugging purposes only of course!

[Bear Bibeault]

The fly-on-the-wall also recommends considering moving SQL statements out of the JSP and into model classes where it belongs.

[Will Fleming]

1. JSTL 1.1.2 from apache.org on all systems

2. EL is enabled. ${param.login } prints out the entered login in the code above (if I comment out the future forwards)

3. MySQL connector 3.1.13 and 3.1.12 before. I upgraded jdbc drivers as one of the first steps in troubleshooting. Nothing in the update seemed to apply to me, but I did it anyway since it is easy.

4. I checked jBoss 4.0.4 release notes, but did not upgrade that. Again, nothing in the release notes would help my problem and it is a bit harder of an upgrade. I figrue I will switch to whatever the latest jBoss version is when I go "live", I will do one upgrade then.

5. I only have the one Db setup in MySQL and again, the query works when hardcoded or entered via mysql client. I am confident it works just fine.

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://java.sun.com/xml/ns/j2ee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee  
        <a href="http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"" target="_blank" rel="nofollow">http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"</a>
version="2.4"  >  

That is straight out of one of my textbooks. Probably O'Reiley's JSP 3rd edition, but maybe HF: S&J.

I will try the scripting example to see if I get different results. That is something I did not think of before.

As for SQL on jsp, I agree they should be separate. Please keep in mind that I am just a newbie SCJP type guy and this is my first real java project. All of the other coding has been for book examples and seldom more than one page. I get about 4-6 hours a week to work on this in my spare time and 80% of that is trying to remember where I left off. It is for fun and learning. If I really like Java, I might try it for a career, but right now it is a hobby.

Maybe using jBoss makes it seem like I know more, but I chose that simply because I thought it would be good to learn since I plan to study J2EE/EJB later on. I used tomcat some before, but why not try to learn more? I really like jBoss now that I have it working.

Anyway, I think a bit of SQL in my jsp is OK for now. I have some tables that do the same thing. I just manually enter the data into the database with open office database.

FWIW, my "plan" with the project is to use SQL in jsp for retrieval queries only. I plan to make a SQL utility class (or several) for actually changing data in the database. I want to do some basic sanity checks before entering it. I don't even have my hiking boots for crossing that river yet.

The answer may be that there is just some weird quirk that I will never figure out.

EDIT: Thanks for the good replies. Maybe not the answer I need, but you have me digging for the answer again. That ain't a bad thing.
[ June 21, 2006: Message edited by: Will Fleming ]