CODEWITHSUNDEEP
sqlarrayspostgresqlh2
Steven Schlansker
Steven Schlansker

Reputation: 38526

SQL Arrays as parameters to prepared statements - portability

I am working with PostgreSQL for my production machines, H2 to make unit tests on the DAO level sane, and Java/JDBC for the application level.

I would like to use SQL Arrays to implement bulk querying of my database. However, the syntax expected by the two databases seems to be different! In Postgres it is

SELECT * FROM mytable WHERE id=ANY(?)

In H2, it is

SELECT * FROM TABLE(id CHARACTER VARYING=?) NATURAL JOIN mytable

I thought that SQL was supposed to be standardized! I decided to go read the actual standard, but apparently you have to pay money for that... Ridiculous!

Am I missing another way that is portable across databases?

Upvotes: 1

Views: 213

Answers (2)

mu is too short
mu is too short

Reputation: 434685

Standardized? Portability across databases? Ha! You made a funny. I've only come across arrays with PostgreSQL but Steven indicates that they're in SQL99; I don't know which syntax is standard (I'd guess PostgreSQL as they're usually pretty good with these things) but an IN query should work the same pretty much anywhere:

select * from mytable where id in (?)

You'd use a comma separated list of id values in the placeholder and you'll have to keep an eye on the size of the list of ids (and that size will, of course, be database specific).

Upvotes: 1

csano
csano

Reputation: 13686

There are certain standards that are consistent across database systems. In many cases, that's a baseline for implementation. Each individual system then builds on those standards in their own way.

It's an absolute nightmare to even think about trying to come up with something that is consistent across all systems. The best way to do this is to make sure you are limiting what you are doing to what is in those standards. Nothing more. If you go beyond that, for example, by using aggregate functions, you're on your own.

A few weeks ago, I thought I'd try implementing a system where I used PostgreSQL in dev/test and SQLite for my unit tests. I thought having an in-memory SQLite database would help me speed up my integration tests. While that part was true, the discrepancy in functionality between PostgreSQL and SQLite caused a number of headaches. After fighting through several different things (I was using NHibernate, too), I gave up and abandoned the thought.

The moral of the story? Portability/standardization don't mean much. Be consistent in what you're using across everything you're doing. You'll save yourself much grief.

Upvotes: 0

Related Questions