OSX, mySQL,PHP5.x & java, connection-pool quick start in 5mi

Quick start for mySQL,PHP5.x on OS X + java drivers:

1) Download the MySQL server package from here http://dev.mysql.com/downloads/mysql/5.0.html#macosx-dmg

As root:
2) Install the server - just follow wizard and install the SystemPreferences package (easy UI start/stop)

3) update your /etc/bashrc by adding these lines (so that all users have SQL in their path):
PATH=${PATH}:/usr/local/mysql/bin

4) Start SQL from preference panel.

5) Default mySQL comes with root and anonymous users w/o password - change these! To set
pwd to mysql:
mysql -u root
set password for 'root'@'localhost' = password('mysql');
set password for 'root'@'' = password('mysql');


(if you dont know your hostname do "select Host, User, password from mysql.user;")

As normal user:
5) go to bsh shell and enter the below to quickly learn some simple things about your DB

mysql - u root -p
enter password
Show databases;
use test;
show tables;
show user(); //current logged in users


Install PHP5.x integrated with built-in apache:

1) get entropy from here:
http://www.entropy.ch/software/macosx/php/

2) Update your /etc/httpd/httpd.conf with an alias - for example:

Options Indexes FollowSymlinks MultiViews

AllowOverride None

Order allow,deny

Allow from all 

Alias /dir/dummytst/ "/Users/foobar/eclipse_workspace/dummytst/"

and you can now access PHP files from http://localhost/dir/dummytest/helloworld.php

Install JDBC:

1) Download the JDBC driver here http://dev.mysql.com/downloads/connector/j/5.0.html

2) Best option is to install the driver in the extension for the jvm - this way all programs (tomcat, eclipse,etc) will have access. On OS X this is in /Library/Java/Extensions/
PS: Apache/tomcat says its recommended to put in CATALINA_HOME/common/lib - might be true for other OS but for OSX the above works fine.


3) Code snippet for a java/jsp/etc program:


try {
Statement stmt;

Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/test";
Connection con =DriverManager.getConnection(url,"root", "mysql");
stmt = con.createStatement();

ResultSet rs = stmt.executeQuery("select Host, User, password from mysql.user;");


while(rs.next()){
    Object c1 =  rs.getObject(1);
    Object c2 =  rs.getObject(2);
    Object c3 =  rs.getObject(3);
  System.out.print("Column1="+c1.toString()+"|");
  System.out.print("Column2="+c2.toString()+"|");
  System.out.print("Column3="+c3.toString()+"\n");
    }
con.close();
}catch( Exception e ) {
    e.printStackTrace();
}



Connection pooling in tomcat 5.5:

These work only on 5.5.x
a) CREATE the file WEB-INF/context.xml for your webapp with similar to the following:






Then in your servlet use this code snippet


Context initContext = new InitialContext();
Context envContext  = (Context)initContext.lookup("java:/comp/env");
DataSource ds = (DataSource)envContext.lookup("jdbc/TestDB");
Connection con = ds.getConnection();
Statement stmt= con.createStatement();
ResultSet rs = stmt.executeQuery("select Host, User, password from mysql.user;");
while(rs.next()){.....}
con.close();


OR use JSP SQL tags:



select Host, User, password from mysql.user;


Results

Host ${row.Host}

User ${row.User}

Pwd ${row.password}