[thelist] using mySQL with JSP/Tomcat

[Hassan Schroeder]

Tom Dell'Aringa wrote:

> Well I found and downloaded some GUI which doesn't seem to do
> anything...asks me to login and says my login is wrong (using the
> login I set for mySQL)..

I don't use the MySQL GUI for anything, anyway, so no matter. :-)

> I just am not sure how to get started mainly - my SQL seems to be
> running...
/
> Also, in the instructions there are all these directions to do shell
> commands like:
>
> shell> scripts/mysql_install_db
>
> I assumed that meant go into the CMD and do shell> etc, but thats not
> it. Just curious then what a shell command is. Appears I don't need
> it since mySQL seems to be running...

The 'shell>' above just represents the prompt of whatever shell
command interpreter) you're using; I'll use "prompt%" below for
the same purpose.

Assuming MySQL/bin is in your PATH, type:

  prompt% mysql  /* will prompt for your password */

Now you'll get something like:

  Welcome to the MySQL monitor.  Commands end with ; or \g.
  ... more stuff ...

  mysql>

>From this prompt ("mysql>" you can create DBs and tables, run
queries, etc. You probably want to start by using the 'test'
DB and creating a small table for your JSP testing.

Now, your JSP:

Make sure you're importing java.sql classes:
  <%@ page
	import = "javax.servlet.http.*,
		java.sql.*"
  %>

And here's the skeleton of a lookup:

  <%
	String thisStatement = null;

	try
	{
		Class.forName("org.gjt.mm.mysql.Driver").newInstance();
	}
	catch (Exception E)
	{
		out.println("Unable to load driver.");
		E.printStackTrace();
	}
	try
	{
		Connection Conn = DriverManager.getConnection(
			   "jdbc:mysql://localhost/test?user=me&password=myPassword";
		try
		{
			Statement Stmt = Conn.createStatement();
			thisStatement = "SELECT foo, bar FROM myTestTable";
			while (RS.next())
			{
				String myFoo = RS.getString(1);
				String myBar = RS.getString(2);
  %>

    <%= myFoo %> <%= myBar %> <br />

  <%
			}

			RS.close();
			Stmt.close();
			Conn.close();
		}
		catch (SQLException E)
		{
			out.println("<b style=\"color: red;\">SQLException: </b>" + E.getMessage());
			out.println("SQLState:     " + E.getSQLState());
			out.println("VendorError:  " + E.getErrorCode());
		}

	}
	catch (SQLException E)
	{
	    out.println("<b style=\"color: green;\">SQLException: </b>" + E.getMessage());
	    out.println("SQLState:     " + E.getSQLState());
	    out.println("VendorError:  " + E.getErrorCode());
	}
  %>

HTH!
--
H*
Hassan Schroeder ----------------------------- hassan at webtuitive.com
Webtuitive Design ===  (+1) 408-938-0567   === http://webtuitive.com

    -- creating dynamic Web sites and applications since 1994 --