SQLServer is also stupid

I can’t understand the problems database drivers have with whitespace where there shouldn’t be any. It’s clearly wrong…

So say that you create a table in SQLServer that is nullable. If you use DatabaseMetaData and do getColumns for that table, and check the nullable column, and fetch the value at index 18 – IS_NULLABLE. What you get back here is “YES” which is all according to the API. But if you by chance do the same thing for a column that is non-nullable – what value would you get then?
“NO “. Notice the space. Lovely. I *heart* databases.

… Someone asked me in response to my last blog post which database I would choose if I could. Hard question. I’d rather do without databases. But if I have to, I’d go with Mimer SQL which is probably the most SQL-compliant database ever, and really doesn’t have WTF moments at all. It’s small, it’s from Sweden, and it’s very nice.

Oracle is stupid

I’ve just spent two days debugging and fixing AR-JDBC issues with Oracle. And let me tell you, those days haven’t been fun. I am really unfond of Oracle at the moment. You probably saw my last post. Now, let me add a new point of insanity to the proceedings…

Say that you define a table like this:

create table companies (
firm_id integer default null references companies

Now, if you try to get the default value of the column firm_id in some manner (for example, you could use JDBC, DatabaseMetaData.getColumns, and getString(13) on the result). You could also use the OCI8 C interface. You would get the same result. Any guesses? What is the default value of the column firm_id? Some might say that it should be the String “null”. Nope. It’s the String “null “. Notice the space.

Now, if you instead defined your table like this:

create table companies (
firm_id integer default null

In this, what is the default value of the column firm_id? it’s “null”. Without a space. Yes, it varies. Yes, it actually varies based on the formatting of the SQL used to create the table. You could potentially use the Whitespace language to embed arbitrary programs in the null default value… Because if there is two blanks between the null word, and the next token, then that’s what you will get from the default value. Notice that we used two totally different interfaces to get this information so it’s obviously something that is saved in the database engine. Wow.

Is this insane? Is it crazy? Am I thoroughly disgusted by now?

Yes. And also, AR-JDBC finally runs all ActiveRecord tests with Oracle.