[depolitic]

Hi

I am trying to execute SQL using JSTL. I added:

<context-param>
<param-name>
javax.servlet.jsp.jstl.sql.dataSource
</param-name>
<param-value> jdbc:mysql:depolitic,com.mysql.jdbc.Driver,twobent grapes,password
</param-value>
</context-param>

to the web.xml file, then I am executing the following:

<sql : query var="empDbInfo">
SELECT * FROM Employee
WHERE UserName = ?
<sql : param value="${param.userName}" />
</sql:query>

Only to get the following error:

org.apache.jasper.JasperException: Unable to get connection, DataSource invalid: "No suitable driver"

The driver I downloaded from the MySQL web-site. I placed the mysql-connector-java-3.0.8-stable-bin.jar in the WEB-INF/lib directory.

I am trying to get an example chapter 11 working from O'Reilly Java Server Pages. The problem is the MS and BILL LOVER author uses MS Access as his database, and I am having problems getting his example chapter to work.

--RANT--
I find it strange and even disturbing, that so many Java books, are written almost exclusively from a Windows perspective. Many books give the impression that Java itself was invented by Microsoft under the gentle hand of the all wise leader his Billness. It is strange that a language with its roots in UNIX, and the ideal of cross platform development at its source. Has become to be so Windows centric. It posses also in my mind the question of Java's future development with the advent of .NET since so much Java development is done on Windows, why would you bother with developing Java if you are a Windows developer now that his Billness has officially sanctioned .NET as the successor to Java.

[asmodeus]

Not sure about the other stuff, but I was messing around with the MySQL JDBC driver the other day. I've got the .jar for it in /Library/Java/Extensions (any .jar you put there is automatically in the classpath) and it was working fine.

[depolitic]

DELETE

[Arkham_c]

You're confusing servlets with jsp. JSP compiles to servlets internally inside tomcat, but the compile is done automatically by tomcat.

What you want to do is ensure that the jar is in tomcat's classpath. To do that, edit the file tomcat/bin/setclasspath.sh to include the jar file.

[DaGuy]

Another alternative is to copy the JAR file to:

$CATALINA_HOME/common/lib

Tomcat manages it's own classpath and the latter directory is one of the locations that it looks for JAR files on startup.

I believe that this is the Jarkarta recommendation but you may want to double check this.

[MadBrowser]

Yeah, it's a classpath thing... The above suggestions should get you rolling.

JSTL rules BTW. Saved me HOURS on my last JSP project.

[Arkham_c]

Originally posted by MadBrowser:
Yeah, it's a classpath thing... The above suggestions should get you rolling.

JSTL rules BTW. Saved me HOURS on my last JSP project.

Personally, I'm not a big fan of JSP. If you are going to use jsp though, be sure to use it for presenation only. Adding something like struts to your app can help keep the presentation and the business logic separate.

[depolitic]

THE ANSWER

After much searching and reading, and not much understanding I managed to get it to work. As usual it was the little things that makes life hard.

I added in the mysql-connector-java-3.0.8-stable-bin.jar WEB-INF/lib/ this automatically added the jar to the class path for the container (Tomcat). My reading tell me that it is better to place the jar in WEB-INF/lib/ because it is more J2EE compliant to do so. Because it your applications portable across various containers.


NOTE:
Adding the jar into Tomcat/common/lib makes the jar available to all users of Tomcat container. However adding the jar to WEB-INF/lib/ does the same thing but it is only available to that WEB-INF directory. In both cases Tomcat should add the jar to the classpath automatically on restart.

Now to the context parameter in the deployment descriptor web.xml. The reason this exists is so that al JSTL actions can crate an DataSource instance themselves rather then for you to manually initialise a connection yourself every-time you wish to access the database.

<!-- makes MySQL driver accessible -->
<context-param>
<param-name>
javax.servlet.jsp.jstl.sql.dataSource
</param-name>
<param-value>
jdbc:mysql://localhost/depolitic,com.mysql.jdbc.Driver,username,password
</param-value>
</context-param>

The core point to get right is the mysql://localhost/depolitic this is the exact path were your application resides, depolitic is root directory of the site. The remaining parts of the context parameters point the correct driver and the username and password for the database you wish to access.

Hope this helps others, maybe some wiser developer can explain it better then me but it works, so I am happy.

[depolitic]

I am aware of the MVC idea. I am however just starting with a pure JSP application. Breaking up the JSP pages to perform each aspect of the Model View Control architecture. I hope to use beans and servlets later date.

[depolitic]

Hopes this helps someone: Read THE ANSWER above. On how to get JSTL, JDBC, and MySQL playing happy.

[DaGuy]

Originally posted by depolitic:


My reading tell me that it is better to place the jar in WEB-INF/lib/ because it is more J2EE compliant to do so. Because it your applications portable across various containers.

That's reasonable but allow me to make some additional comments.

If only one web app uses the database, then I completely agree with you. However, more often than not, you find yourself making database calls from different web apps. Under those cases it's best to use the $CATALINA_HOME/common/lib
directory. It's meant to be use for common web app infrastructure. According to J2EE your webapps should not care what database they use -loose coupling.

Portability comes from not marrying your app to any third party libs or application servers. In your case, if you were to ship your app with the MySQL drivers than you are married to MySQL (this is the case if you put the drivers on WEB-INF/lib/). It's best to avoid such commitments (despite the fact that MySQL is really cool). It doesn't foster portability and to certain extent bloats the webapp with unnecessary classes.

But you're on right track by using a datasource. This allows for compatibility with any databse that supports JDBC.