Say that you define a table like this:
create table 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.
firm_id integer default null references companies
);
Now, if you instead defined your table like this:
create table companies (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.
firm_id integer default null
);
Is this insane? Is it crazy? Am I thoroughly disgusted by now?
Yes. And also, AR-JDBC finally runs all ActiveRecord tests with Oracle.
8 kommentarer:
This is criminal! Do you mean to tell me that people pay an arm and a leg for this software?
Ack! That reminds me of a (thankfully) long-ago and far away time when I was stuck programming with the Windows API in C.
As a newbie, I wasted a lot of time before figuring out that the reason a while(blah){...} code segment was producing weird results was that some Windows functions prototyped as returning BOOL can actually return 0, 1, or -1. Nice design, guys!
What's Ola Bini's database of choice?
I've been using JRuby at work lately and the database I'm tied to is of course Oracle. Luckily Rails has nifty features to let me utilize legacy database tables and ActiveRecord-JDBC worked well for me right off the bat (I'm not doing anything ground breaking). But it looks like your book will end up on my desk soon...
I like Oracle a lot, but the JDBC driver is just criminally bad. The C API is nice, though. =(
Welcome to the wonderful world of oracle. Afraid oracle alway have there own way of doing things. For >1Tb databases and using the database as application server with pl/sql its great. For implement JDBC driver like other vender they not so hot.
The JDBC driver metadata calls dont work well. Most likely will have to do same as native ActiveRecord drive and go direct for column information with a oracle specifc meta data select (via ALL_TABLES,ALL_TAB_COLUMNS, ALL_CONSTRIANTS etc.) eg.
select column_name as name, data_type as sql_type, data_default, nullable,
decode(data_type, 'NUMBER', data_precision,
'FLOAT', data_precision,
'VARCHAR2', data_length,
null) as limit,
decode(data_type, 'NUMBER', data_scale, null) as scale
from all_tab_columns
where owner = '#{owner}'
and table_name = '#{table_name}'
order by column_id
The Next with Default is can be a sql function call like sys_guid() so may need to filter out these.
After that most likily will find the 4kb blob problems on insert and then fact oracle number() is fixed 22 place decimal and not expected float (BINARY_DOUBLE in oracle speak).
Ever since I had to work with Oracle in a publishing environment at a former job, I've hated it. I think it's utter piece of crap. Why anyone in their right mind would pay anything for it is beyond me. The issues you've discovered here are just scratching the enamel of the surface of an ice berg the size of Antares.
Anyway; good progress, great job!
http://in.relation.to/2104.lace
you do realize that you are really complaining about the getString... why are you not trying to get the int value out of it? i would think that the string value is probably driver dependent. and based on your description it is. oh well just a thought.
Skicka en kommentar