/dev/weblog

SQLSTATE[HY000]: General error: 1007 Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier.

I hate you so much, Microsoft SQL Server.

Today's exchange originated from a short-lived discussion on Twitter.  Everyone familiar with the acronym VARCHAR? It's short for variable character, which is a SQL data type.

Since it's short for variable character, one would think the correct pronunciation is /ˈvær.ˈkʰæɹ/.  (For those of you who don't read IPA, that's var—rhymes with bare—and char—also rhymes with bare).

It drives me up a wall when I hear someone pronounce it like it rhymes with "Far Car", or worse still, soften the "Ch" like in Charbroiled.

Anyway, these are my favorite tweets in regards to the matter:

• @RealBigDannyT: VARCHAR STARE!!!
• @rizza: I, too, char varry much about how people pronounce this.

Now, don't even get me started on how you should pronounce "SQL".

Last night I tried to install Oracle 10g XE on my Debian-based database server for testing purposes. Tried is the key word here.

Interestingly enough, Oracle offers a deb repository for this purpose. Add the following line to your /etc/apt/sources.list:

deb http://oss.oracle.com/debian unstable main non-free

Then execute the following commands as root (or with sudo) to add Oracle's public signing key and then install Oracle XE:

# wget http://oss.oracle.com/el4/RPM-GPG-KEY-oracle  -O- | sudo apt-key add - 
# apt-get update
# apt-get install oracle-xe

Here's the catch: my database server is an OpenVZ VPS. The Oracle installer checks for a sufficiently large swap partition and will bomb out if one of a sufficient size is not found. OpenVZ VPSs do not have a swap partition.

OpenVZ does support swap space, and that swap space can be set with barriers and limits, but the swap space is as a whole meaning that there is one swap space for the entire system, not a user-configurable swap space per-VPS.

So now comes the task of tricking the installer to think I the VPS has a swap partition, or trying to get it on there some other way.  Stay tuned for the nuttiness.

Oracle is stupid.

If you want foreign key support in MySQL, you need to use the InnoDB storage engine. If you want fulltext index support, you need to use the MyISAM storage engine. Ain't that a bitch?

Just for the record: PostgreSQL supports them both well.

To be fair, the syntax for fulltext searching in MySQL is more simple than that of fulltext searching in PostgreSQL. Here's an example:

MySQL:

SELECT title
FROM example
WHERE MATCH (title, body) AGAINST ('foobar')

PostgreSQL:

SELECT title
FROM example
WHERE to_tsvector(title || body) @@ to_tsquery('foobar');

So while the syntax is easier, you can't use fulltext and foreign keys simultaneously in MySQL. PostgreSQL wins this battle.