[sclug] URL encoding/decoding question

[pieter claassen]

On Tue, 2006-02-21 at 21:35 +0000, Roland Turner (SCLUG) wrote:

> OK, this is bad. Something somewhere is using 8859-1 interpretation
> instead of UTF-8 interpretation.

> - Raz
> 
> 

Righto, I looked and noticed I was using MySQL 4.0 which might or might
not have sufficient support for utf8 compiled into it.

I promptly upgraded to MySQL 4.1 which should have been straight forward
but there are some issues;

Firstly, I converted the db character set to utf8 by dumping the data
and recreating the table.

I also forced the mysql server to start up with utf8 selected as the
default character set by 

[mysql]
default-character-set = utf8

But suddenly all my PreparedStatement queries failed with the following
info in the mysql.log

SELECT data FROM content WHERE id=? and lang=?

It is clear that connector j (both version 3.1.12 and 5.0.0) failed to
bind/substitute the variables for their place holders.

I noticed in the mysql log that 

 5 Query       SET NAMES latin1
 5 Query       SET character_set_results = NULL

Which I forced through the URL Connection parameters to now state
7 Query       SET NAMES utf8
7 Query       SET character_set_results = utf8

however current results on the test code below are:
7 Prepare     [1] SELECT data FROM content WHERE lang=?
7 Execute     [1] SELECT data FROM content WHERE lang=?

The workaround of using Statement rather than preparedStatement is not
really an option since Tomcat uses preparedStatement internally for
authentication.....

Any ideas appreciated.

Pieter








import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.PreparedStatement;

public class dbtest {

	public static void main(String[] args){


		try {
    			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (Exception ex) {
			 System.out.println("My Exception: " + ex.getMessage());
		}

		try {
			Connection conn =
DriverManager.getConnection("jdbc:mysql://localhost/content?user=aaaa&password=aaaa&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8");
//			Statement stmt = conn.createStatement();
//			ResultSet rs= stmt.executeQuery("SELECT data FROM content where
lang='nl'");
			PreparedStatement stmt = conn.prepareStatement("SELECT data FROM
content WHERE lang=?");
			stmt.setString(1,"nl");
			ResultSet rs = stmt.executeQuery();
			while (rs.next()){
				System.out.println(rs.getString(1));
			}
			rs = null;
			stmt = null;
			conn.close();
			
		} catch (Exception ex){
			 System.out.println("My Exception: " + ex.getMessage());
		}




	}