[ic] UTF-8 in MySQL

Jon Jensen jon at endpoint.com
Tue Mar 7 12:07:39 EST 2006


On Tue, 7 Mar 2006, Elver Loho wrote:

> To get utf8 from MySQL's utf8 fields, *you have to tell MySQL that you
> want it to 'convert' things to UTF-8 for you* This can be done using:
>
> 1) set names utf8
> 2) set character set utf8
>
> Interchange will have to send either of those above commands before
> executing 'select * from locale' in order to get proper utf8 strings
> rather than the question marks it is getting now.
>
> What I need documentation on is how to tell Interchange to send 'set
> names utf8' (or the other string) to MySQL before selecting anything
> from there. And then it will have to either internally convert some
> fields to latin1 OR it can use 'select cast' which can take care of
> that as well.

I don't necessarily think doing this is the best solution for you, but 
present it as something to consider:

Once upon a time I needed a certain session variable set in PostgreSQL 
before a query was run (maybe timezone? I don't recall now), and added a 
PREQUERY option to Interchange's databases, allowing something like this 
in catalog.cfg:

Database  products  PREQUERY  "SET SESSION TIMEZONE TO 'EST5EDT'"

In the end, I decided to change the user's default settings so that the 
timezone was like that automatically, and I never committed the change to 
Interchange CVS. I've attached the patch -- it was written a long time 
ago, so I hope that's everything you need to try it. If you find it 
useful, let me know and we'll see about getting it in the core.

Jon
-------------- next part --------------
Index: lib/Vend/Config.pm
===================================================================
RCS file: /var/cvs/interchange/lib/Vend/Config.pm,v
retrieving revision 2.201
diff -u -r2.201 Config.pm
--- lib/Vend/Config.pm	2 Mar 2006 16:08:28 -0000	2.201
+++ lib/Vend/Config.pm	7 Mar 2006 16:57:47 -0000
@@ -4024,6 +4024,7 @@
 						BINARY              BINARY 
 						PRECREATE           PRECREATE 
 						POSTCREATE          POSTCREATE 
+						PREQUERY			PREQUERY
 						INDEX               INDEX 
 						ALTERNATE_DSN       ALTERNATE_DSN
 						ALTERNATE_USER      ALTERNATE_USER
Index: lib/Vend/Table/DBI.pm
===================================================================
RCS file: /var/cvs/interchange/lib/Vend/Table/DBI.pm,v
retrieving revision 2.70
diff -u -r2.70 DBI.pm
--- lib/Vend/Table/DBI.pm	14 Feb 2006 08:30:51 -0000	2.70
+++ lib/Vend/Table/DBI.pm	7 Mar 2006 16:57:48 -0000
@@ -621,6 +621,17 @@
 	$tablename = $config->{REAL_NAME}
 		if $config->{REAL_NAME};
 
+	if (ref $config->{PREQUERY} eq 'ARRAY') {
+		for (@{$config->{PREQUERY}}) {
+			$db->do($_)
+				or ::logError(
+						"DBI: Pre-use query '%s' failed: %s" ,
+						$_,
+						$DBI::errstr,
+				);
+		}
+	}
+
 	# Used so you can do query() and nothing else
 	if($config->{HANDLE_ONLY}) {
 		return bless [$config, $tablename, undef, undef, undef, $db], $class;


More information about the interchange-users mailing list