I recently noticed that many of the comments and trackbacks on this website were composed entirely of question marks. At first I thought it might be plain old spam, but it turned out to be a character encoding problem. Here’s how I fixed it. Continue reading “MySQL character encodings” →
Posts Tagged: SQL
Oracle testing with the DUAL table
This feature is a hack intended for testing, but it’s built in to every Oracle installation.
DUAL is a table automatically created by Oracle and accessible to all users. It has one column, DUMMY, containing one row. It’s useful for selecting a constant or expression, because the table always exists (so the query will succeed) and the result will only be returned once (since there’s exactly one row in the table). Continue reading “Oracle testing with the DUAL table” →
Oracle silently truncates dates
According to the documentation, the Oracle DATE type does not store fractions of a second. So if you store a date/time value including fractions of a second and read it back, you’ll get back a slightly different date/time. This will cause exact date matching code in your appplication to fail.
Oracle empty string = null
In Oracle 8, there is no such thing as a zero-length string. Any zero-length string, either from a function call or the literal '', is treated as null.
Some of the consequences of this are counterintuitive. Here are some examples. Continue reading “Oracle empty string = null” →
Don’t use automatically generated unique IDs
Think carefully before using an automatically-generated unique ID as a primary key in a SQL table. Using such automatically-generated IDs introduces extra implementation detail. This is a pain to manage, especially if they are used as primary keys or worse, foreign keys in tables. Continue reading “Don’t use automatically generated unique IDs” →