Dot Matrix

mySql’s time zone tables have a few zones named ‘slightly’ differently than what you might expect. This is mostly a discrepancy between Java’s icu4j time zones and the ones in the tzdata package which is used by mysql to populate its own time zone tables. When you pass a standard Java icu4j time zone which does not correspond to a tzdata one, it causes NULL to be returned from the CONVERT_TZ mySql function.

I’ve made a short list of some of the time zone’s in Java and their corresponding tzdata zones. If I find more (or if you know of any) let me know and I’ll add them to the list.

Java icu4j => mySql (tzdata)

IST => Asia/Calcutta
PST => PST8PDT
US/Pacific-New => US/Pacific
CST => CST6CDT

In postgreSQL you can create insert statements that return the new id by simply appending “returning id_field” after the insert statement (where id_field is the name of your automatic id field). You don’t get that luxury with mySql (why?).
To achieve the same result you’d have to perform two queries inside a transaction: the insert itself and a “select last_insert_id()” query.

However I just found out this little trick: mySql’s connector by default does not allow multiple queries to be executed (using the ; terminator). But if you add this parameter to the JDBC url you will be able to execute multiple queries inside one cfquery tag:

allowMultiQueries=true

You can now perform insert queries that return the new id:

<cfquery name=”insert” datasource=”dsn”>
insert into temp (val) values (‘test’);
select last_insert_id() as new_id;
</cfquery>

This query will have a result set containing a new_id field with the newly inserted id in temp.

You will have to take extra care thought. This option is false by default to reduce the chance of sql injection attack. Make sure you use the cfqueryparam tag and not just embed #vars# in your sql.

See here for a full list of mySql’s Connector J settings.