ansaurus

Question

Problems with escaping table and field names in Derby and Hsqldb

Answer 1

A:

I thought it would be a good practice to escape all entity names.

What gave you the idea that this was a best practice?

A better one would be to avoid reserved words in table and column names. They aren't descriptive enough. 'date' would satisfy your 'best practice', but it wouldn't tell me whether this was a birth or death or hired or effective date.

duffymo 2010-06-25 20:15:57

I didn't say "best" practice -- I said "good" practice. I figured it was good for the SQL generated to protect the user from the various SQL reserved words. Isn't the point of a good ORM to shield the user from the intracacies of the SQL flavor that they are plugged into -- to hide the fact that 'OLD' is reserved in postgres but not other databases? Oh, and date is a fine field name IMO in the Birth object.

Gray 2010-06-25 20:28:57

Not even "good". I'd agree that SQL date column keyword limitations don't apply to a Birth object (do you really have one?), but in that case I'd have my ORM map the "date" attribute to the "birth_date" column. Your argument still doesn't hold water.

duffymo 2010-06-25 20:35:34

Dude, I'm writing the ORM package itself. I'm trying to protect stupid users from using field names like 'order' and 'group' and other reserved SQL names that they may know nothing about. Oh and I would argue that if a date field is in the birth table then it shouldn't be birth_date yes. select birth_date from birth? Bleah.

Gray 2010-06-25 20:46:29

You're writing an ORM package when there are so many available? No one should use SQL in complete ignorance of it. Good luck to you and your users.

duffymo 2010-06-26 01:12:24

I needed one that was lightweight, used java annotations, and had good Spring support. Thanks for the luck duffymo!

Gray 2010-06-26 17:43:11

Spring supports both Hibernate and TopLink. I can't see why you'd want to write one of your own. What's your value proposition? What are you gaining by taking on this development, test, and maintenance burden? How will you develop a user base to compare to either Hibernate or TopLink? Unless you're trying to learn how to make wheels, I see no reason to do this. Especially if you're going to foist this on other members of your team or clients.

duffymo 2010-06-26 19:04:47

Hibernate is _far_ from lightweight of course and TopLink only support the Oracle db, right? What am I gaining? Same thing that I've gained through my dmalloc and mailnull projects. I think it's a better wheel that fills a niche.

Gray 2010-06-28 01:29:30

I agree that Hibernate isn't lightweight, but if you end up implementing the same features I'd be surprised if you were able to do better. You might be "lighter" because you don't implement all the features. As for TopLink, I believe Oracle open sourced it. I'll check to see if it can be used for databases besides Oracle. If your wheel fits your needs and fills your niche, then that's enough justification.

duffymo 2010-06-28 01:47:07

Answer 2

+1 A:

You just have to make sure that the case matches.

So if it's:

create table "Footable" ("Stuff" varchar (25))

Then it has to be:

insert into "Footable" ("Stuff") values 'hi mom'

If the table/column name is in double quotes, the case is preserved as is.

If the table/column name is not in double quotes, then Derby handles it insensitive to case.

Bryan Pendleton 2010-06-25 20:33:49

Thanks for this Bryan. I was using the same case in both the CREATE and SELECT statements so your answer didn't initially seem appropriate. However I see now (per my answer below) that it was a case issue just that Derby and Hsqldb were promoting the unquoted names to be all uppercase.

Gray 2010-06-25 20:56:46

Answer 3

+1 A:

So kudos to Bryan for leading me down the path although his answer wasn't quite right.

Turns out that because I am creating the database as "footable" then, as Bryan states, it will be create case sensitively. However, when I did the select on footable (without quotes) Derby and Hsqldb are promoting it to be all uppercase so I'm in effect doing:

SELECT * FROM FOOTABLE

It's not about being case insensitive without the quotes (which would have worked) but about promoting the entity names to be all capitals when there are no quotes and then matching by case. I'd argue there was a bug here...

In any case, I've changed my Derby and Hsqldb to capitalize all entity names and things are working. Ugly IMO but working.

Gray 2010-06-25 20:54:43

ansaurus

tags:

views:

answers:

Problems with escaping table and field names in Derby and Hsqldb

related questions