curly brackets: which side are YOU on?

[Winston Gutkowski]

Martin Vajsar wrote:Sorry for playing the devil's advocate here, but have you ever heard about SQLJ?

Not at all, and no I hadn't. I notice that the very first sentence in the link says "outdated" though, so, as you say, the demand probably wasn't there; maybe because it was just yet another bolt-on to SQL.

I'm still not sure whether your issue with SQL is the relational algebra in itself, or the way SQL encapsulates it?

A bit of both. Like I said earlier, the simple act of finding the "top 10" of a particular query is ridiculously difficult/unstandardized, when it's the sort of thing you're likely to want to do a LOT. Basing everything around Boolean logic may have seemed like a great idea back when databases were still young; but it seems to me like we could have moved on a bit. And for external entities like Java it leads to the "fetch, then process" paradigm that I dislike intensely. How often do we tell people, when they start to get advanced, to design object contracts on the "tell, don't ask" premise? Well, you simply can't do that with JDBC (or if you can, it's darn difficult), because the only way to interact with the database is via SQL and ResultSets.

Progress has lots of very nifty imperative logic and functions, like 'for-each' and 'can-find' and 'exists' and 'of' that makes database tables feel far more like Java collections than SQL tables; and it seems to me like there ought to be some way to translate logic like that via middleware.

But maybe I'm just barking at the moon.

After all, it is just GRANT (rights) ON (objects) TO (dudes). All but the three keywords just name the objects.

Yeah, over and over and over. Like I say: GRANT fatigue.

Which better way of doing it would you imagine?

Well, the best system I ever saw was on a little visual db system called Speed 2, written for the Wang back in the eighties. Each system component had an optional number (sometimes more than one) that related to a bitmap, and every security "group" was simply a map of "bits" (I think there were 1,000 by default) that was either on or off, with commands to turn whole ranges of them on or off, or create "GRANT ALL" security groups for entire apps or sets of tables, which you could then copy and/or change.
It took a bit of thought, but you could create incredibly sophisticated security, right down to the field level of forms. And blisteringly fast (as it needed to be in those days). Definitely NOT very relational though; it was definitely a programmatic solution. It did have some very nice reporting tools though; mainly, I suspect, because it was so simple.

Winston

[chris webster]

Well, it's true that more complicated stuff may require vendor-specific extensions to SQL, but even here there is a lot of standardisation going on e.g. analytical functions are becoming more standard, as well as offering very powerful functionality. As for "top N" queries, they're pretty easy in Oracle and PostrgreSQL, the RDBMS I'm most familiar with.

Many of your other points still seem to relate to taking a procedural stepwise approach to a non-procedural declarative language. I tend to approach hard queries more as a series of transformations, each yielding a new set of records that looks more like what I'm looking for. Interestingly, the resolutely NoSQL folk who wrote MongoDB's nifty Aggregates query API have taken a similar approach.

As for SQLJ and JDBC, they're both kludges to bridge this awkward gap. But objects aren't everything, and the more "processing" I can do in the database, the less I need to do in clunky old objects. ;-)

[Martin Vashko]

Winston Gutkowski wrote:... because it was just yet another bolt-on to SQL.

Yes, it was. It's the Pro*C of the Java world. So it doesn't do away with the "fetch, then process" paradigm, but completely encapsulates JDBC. You don't have to deal with the monstrosities (the ResultSet and PreparedStatement), but write static SQL instead which references Java variables directly. And get compile-time validation of static SQL queries, for-each and so on. Much, much better than JDBC - I don't understand why it didn't take off. Perhaps the idea of a pre-processor was just too gross when Java was still young?

... to design object contracts on the "tell, don't ask" premise ...

I'm finally starting to see what are you talking about. Hibernate seems to be a way of addressing it. I'm not well versed in Hibernate and similar frameworks, though, so I don't understand their shortcomings, but I believe there are some.

[Winston Gutkowski]

chris webster wrote:Well, it's true that more complicated stuff may require vendor-specific extensions to SQL

Top n is complex? Or first n; or last n? What they aren't is Boolean (or "relational", if you prefer); and, by extension, that means that they aren't (or certainly weren't) standardized in SQL; so every darn vendor provides their own way of doing it. They're certainly data-directed though, and I'll bet there's any number of ways that databases could optimize queries for exactly that sort of request.

Many of your other points still seem to relate to taking a procedural stepwise approach to a non-procedural declarative language. I tend to approach hard queries more as a series of transformations, each yielding a new set of records that looks more like what I'm looking for.

I've done enough data modelling (which I really enjoy, especially the ERD side) to know how I want to structure my data, and how BPs and transforms work; but lots of apps require process modelling as well - which is where OO came from.
My question is simply: why, after 30 years, haven't we come up with a better way of translating data (or perhaps, more specifically, databases) to procedure? Neither exists in a vacuum, and I want to tell my database what to do in my terms, and let it do it; not ask it what it's got and then plough through all the crap it gives me to work out HOW I'm going to do it. Now that's procedural.

Like I say: probably tilting at windmills.

Winston

[Winston Gutkowski]

Martin Vajsar wrote:You don't have to deal with the monstrosities (the ResultSet and PreparedStatement), but write static SQL instead which references Java variables directly. And get compile-time validation of static SQL queries, for-each and so on. Much, much better than JDBC - I don't understand why it didn't take off. Perhaps the idea of a pre-processor was just too gross when Java was still young?

Sounds interesting. I'll have a further look into it. Thanks Martin.

I'm finally starting to see what are you talking about. Hibernate seems to be a way of addressing it. I'm not well versed in Hibernate and similar frameworks, though, so I don't understand their shortcomings, but I believe there are some.

I have to admit, I love Hibernate. It just seems like a logical extension of JPA to me. But you're right, there are some shortcomings; and I hate to say, but most of them relate back to this seeming brick wall of declarative vs procedural. OO has taken us a long way on the programming/"procedural" side; I just don't see any similar progress (no pun intended) with databases - or at least "database connectivity". The general philosophy seems to be "our way (SQL) or the highway".

Winston

[chris webster]

Winston Gutkowski wrote:Top n is complex?

No, and you're absolutely right that SQL could use more standardisation. But I wonder if the dominance of Java has perhaps reduced the pressure to standardise this stuff. So many Java developers are so terrified/ignorant of SQL that they often stick to over-simplistic queries and do everything else (often far less efficiently) in Java. Which means nobody really pushes to standardise or simplify the hard stuff, because only the database folk are using it and they're OK with it anyway. Just a theory.

...lots of apps require process modelling as well - which is where OO came from.

Really? I thought it came from GUI development. Personally, I find OO is a mixed blessing, and I'm quite happy to drop out of OO-land if I can do what I want more easily/efficiently some other way.

My question is simply: why, after 30 years, haven't we come up with a better way of translating data (or perhaps, more specifically, databases) to procedure? Neither exists in a vacuum, and I want to tell my database what to do in my terms, and let it do it; not ask it what it's got and then plough through all the crap it gives me to work out HOW I'm going to do it. Now that's procedural.

Control-freak! :-)
You can always use PL/SQL etc ;-) which is procedural and has support for objects (although this is clunky as hell). Or check out Clojure creator Rich Hickey's Datomic database, which is an interesting attempt to introduce some new thinking to databases.

I'm OK with the fact that SQL isn't a full Turing-complete programming language - Scott Davis (Groovy advocate) describes it as "a DSL for databases" which sounds about right to me. As for the interfaces to Java etc, well, they're not intrinsically a SQL problem really.

Like I say: probably tilting at windmills.

SELECT pie FROM picture_library WHERE pie_quality = 'lovely';

Chris

[Pat Farrell]

chris webster wrote:No, and you're absolutely right that SQL could use more standardisation.

Wow, I've always thought that the problem with SQL was too much standardization.

By definition, any industry wide agreement/standardization has to be a minimal common subset. Each vendor fights to get their special extensions in standard, and each of the others fight to keep it out.

I agree, we need a completely new approach to binding our big wads of data to our programming languages. And I really don't want it to be restricted to OO languages because I'm not convinced that OO will be important in a few decades. Binding to big wads of data will always be important.

Interesting how the definition of "big" for a database has changed over time. Up into the early 1970s, a mag tape was THE definition of a big database, they held at most about 160 MB of data. Many of the early disk-based DBMS had problems accessing millions of records or a gigabyte of data. These days, $200 hold terabytes. I don't even know the word for the amount of data that Amazon or Google keep.

[chris webster]

Pat Farrell wrote:I don't even know the word for the amount of data that Amazon or Google keep.

Try asking the NSA?

[Winston Gutkowski]

Pat Farrell wrote:I agree, we need a completely new approach to binding our big wads of data to our programming languages. And I really don't want it to be restricted to OO languages because I'm not convinced that OO will be important in a few decades.

Sorry to resurrect this, but I've only been on sporadically for a few days and felt I had to challenge that statement. If you don't think OO will be important in [some indeterminate future], I wonder what you think will replace it?

There are very few "pure" OO languages around, and Java certainly isn't one of them. About the only thing it requires you to do is create classes; but how you use them is entirely up to you - and if you spend a bit of time in the Beginners section, you'll soon find out that most of them (and several more advanced people) spend a great deal of their time writing C code in Java.

To me, OO is just a technique, like geodesic construction. It was always there; it just took us a bit of time (and maybe the right technology and materials) to find it - and a few bright sparks to formalise it for us. It's not applicable to everything, and it doesn't replace procedural programming; but it is a pretty comprehensive improvement that solves many of the problems inherent in older procedural languages. In fact, I'd argue that the explosion of reactive and interactive software and components is almost entirely down to Object Orientation (and probably the JVM architecture too).

The fact is that we puny humans like procedure. 'Do this...then do this' is simple. I have no doubt that at some point we'll be dealing with reactive "widgets" on a much grander scale, but I doubt very much that OO principles will be unimportant; we'll simply be dealing with them at a few more "layers of indirection".

My two-penn'orth, for what it's worth.

Glad to see you agree with me about binding data and procedure, though.

Winston

[Jelle Klap]

chris webster wrote:

SELECT pie FROM picture_library WHERE pie_quality = 'lovely';

[Pat Farrell]

Winston Gutkowski wrote:you'll soon find out that most of them (and several more advanced people) spend a great deal of their time writing C code in Java

This is one of the fundamental problems with OO. I will grant that OO was a breakthrough in the late 70s, it enabled the whole WIMP (windows, icons, mice, pointers) change to how we interact with computers. It works wonderfully for that space. When you build OO models that you glue with a little procedural code, you can make a great UI.

Even though I have been writing OO code for 33+ years, I don't find it a good match for a lot of the business systems that I work on. Not only is there a huge impedance mismatch between the DBMS and OO models, but you quickly find that your Customer class doesn't really handle the types of customers you really have, and that 50 subclasses to avoid the procedural hacks is as much of a hack itself.

At a high level, OO is just procedural code with some syntactical sugar on the data structures. Structs with methods and all that. IMHO, procedural code will be dead fairly soon as it doesn't handle multi-core systems. Modern cell phones have octa-core processors, so multi-core systems is not the future, its today's technology. For that, we need something different. Functional programming has been proposed, but the jury is still out on that one.

[Winston Gutkowski]

Pat Farrell wrote:This is one of the fundamental problems with OO.

Erm...sorry, but no, it's not. It's perfectly possible to write a purely objective language, in the same way that you can write a purely functional one; but the problems are the same: it's not the way we think. People can certainly be trained to write purely objective code, but I doubt very much if they can be trained as easily to prove that it works.

At a high level, OO is just procedural code with some syntactical sugar on the data structures. Structs with methods and all that...

Really? Have you ever studied Smalltalk (by many accounts, the original OO language)? In it, code is an object, which is (I suspect) what made it seem so amazing for a while. Then reality hit, and most companies realised that anyone who wasn't totally immersed in CS as a theoretical exercise wasn't likely to be able to mine all that wonderful recursion.

Java (and I must admit to being biased) strikes me as a simple base for allowing people to write OO programs, without forcing them to. And my argument with SQL is not that it doesn't do what it was designed to do (data retrieval and update) very well; it's that it doesn't translate well to procedural thinking, which (yes still, in 2013; and I suspect for a lot longer than you think) is the foundation of most current computer-based applications.

On the OO (or programming) side, we have new paradigms: messages and 'Tell, don't ask'. Why hasn't SQL (or more likely, the databases that support it) caught up with the times? I don't care if an Oracle database ends up executing SQL behind the scenes, but don't force me to use it. It's also the ONLY correct place for it to be used, since it's the only place that needs it.

I don't think OO has a short shelf-life. I think SQL - and databases that support it, and only it, as the basis for "connectivity" - do.

Winston

[Pat Farrell]

Yes, I started doing OO with professional Smalltalk. That everything is an object does not really change much. All IMHO, of course.

[Paul Anilprem]

Winston Gutkowski wrote:Why hasn't SQL (or more likely, the databases that support it) caught up with the times? I don't care if an Oracle database ends up executing SQL behind the scenes, but don't force me to use it. It's also the ONLY correct place for it to be used, since it's the only place that needs it.

Well, the name is Structured query language. It is meant to query. It is meant to be "asked" not to be "told". Would you ask a coffee machine, why it doesn't wash your clothes??
If you want something like Java that allows you to write OO as well as procedural code, you can use any other language that allows you to "query" as well as allows to "tell" and if that language doesn't do that well then you can fault it. I don't see any fault with SQL just because it is not amenable to doing something that it was not meant to do in the first place!

[Winston Gutkowski]

Paul Anilprem wrote:Well, the name is Structured query language. It is meant to query. It is meant to be "asked" not to be "told". Would you ask a coffee machine, why it doesn't wash your clothes??

So, why are we forced to use it? Are you saying that something that has been designed by experts, and has (presumably) cost billions of dollars to implement over the years, has only ONE mode of access - SQL? And, furthermore, one that doesn't dovetail well with the things that are likely to want to access it? Sounds like an exercise in Machiavellianism if you ask me.

If you want something like Java that allows you to write OO as well as procedural code...

Erm...perhaps you missed the preamble, but Java is a procedural language. I don't think anybody (certainly not me) has argued otherwise.

you can use any other language that allows you to "query" as well as allows to "tell" and if that language doesn't do that well then you can fault it. I don't see any fault with SQL just because it is not amenable to doing something that it was not meant to do in the first place!

Now that is simply NOT true (at least not in the context of this argument).

Can I "tell" a database to add an new account for a client? Empirically. I assume you think I can.
Tell me: How would you do it with SQL?

What happens if there's a collision? Or if the client doesn't exist? Or the request is fraudulent? Or any number of other possible outcomes other than success? SQL isn't worried about any of that. But this is what databases actually DO for a living, not in a lab....or rather, they DON'T (at least, not just with SQL).

SQL protects (albeit in a horrible Boolean style) the structure. Procedure does everything else.

I want a protocol that allows me, as an applications programmer, to deal with a request like the above, at the database level, in terms that I understand, not dictated by some spotty geek with a BSc in database theory and a gold star in SQL. My "accounts" are actually people's accounts; not some figurative "row" in a database or value in a Turing engine.

Winston

[Winston Gutkowski]

Paul Anilprem wrote:Well, the name is Structured query language. It is meant to query.

Again: really? Then why does it have an UPDATE and INSERT command? To my mind, as a Java programmer, those are the ones that interest me most, because they might allow me to actually do something imperative. Sadly (as always) real life intervenes, and I'm pretty much forced to query before I update.

Winston

[Paul Anilprem]

Winston Gutkowski wrote:

Paul Anilprem wrote:Well, the name is Structured query language. It is meant to query. It is meant to be "asked" not to be "told". Would you ask a coffee machine, why it doesn't wash your clothes??

So, why are we forced to use it? Are you saying that something that has been designed by experts, and has (presumably) cost billions of dollars to implement over the years, has only ONE mode of access - SQL? And, furthermore, one that doesn't dovetail well with the things that are likely to want to access it? Sounds like an exercise in Machiavellianism if you ask me.

That most databases support only SQL, is not SQL's fault. What has SQL got to do with it? Fault the database makers for not providing a language that you would like.

Winston Gutkowski wrote:

If you want something like Java that allows you to write OO as well as procedural code...

Erm...perhaps you missed the preamble, but Java is a procedural language. I don't think anybody (certainly not me) has argued otherwise.

What it is or what you call it, is immaterial. What it allows you to do is what I believe is important. Are you claiming that Java does not allow you to write OO code at all?

you can use any other language that allows you to "query" as well as allows to "tell" and if that language doesn't do that well then you can fault it. I don't see any fault with SQL just because it is not amenable to doing something that it was not meant to do in the first place!

Now that is simply NOT true (at least not in the context of this argument).

Can I "tell" a database to add an new account for a client? Empirically. I assume you think I can.
Tell me: How would you do it with SQL?

Why in the world would I ask my coffee machine to wash my clothes? SQL is meant to deal with rows in a relational database. Why should it care about Accounts? If you want to deal with Accounts, try Object databases. Or something else, I don't know.

Overall, what you are arguing is either way over my head or makes no sense at all. Probably the former

[Paul Anilprem]

Winston Gutkowski wrote:

Paul Anilprem wrote:Well, the name is Structured query language. It is meant to query.

Again: really? Then why does it have an UPDATE and INSERT command? To my mind, as a Java programmer, those are the ones that interest me most, because they might allow me to actually do something imperative. Sadly (as always) real life intervenes, and I'm pretty much forced to query before I update.

Winston

OK, so it lets you update and insert. Seems pretty intuitive to me. I don't understand what is the big deal. I really don't.

[Martin Vashko]

Winston Gutkowski wrote:Sadly (as always) real life intervenes, and I'm pretty much forced to query before I update.

... MERGE?

In any case, if you don't mind mixing OO and procedural code, what's so wrong in mixing SQL and procedural code? Both these cases mean that you need to master a more abstract system (object oriented / relational) on top of the (natural) procedural thinking. Both of them allow you to elegantly do things for which procedural code is not well suited.

I'm far from being fluent in relational algebra, but on a few occasions I was able to arrive at an elegant SQL solution that displaced much more bloated procedural code. If the hypothetical new language offered good procedural code, but didn't provide all options supported by SQL, I wouldn't be happy with it.

[Winston Gutkowski]

Paul Anilprem wrote:That most databases support only SQL, is not SQL's fault. What has SQL got to do with it? Fault the database makers for not providing a language that you would like.

There is another? That is externally accessible? SQL is it AFAIK, so what else should I blame? It's the "public face" of the database....and, in procedural terms, it's crap.

Why in the world would I ask my coffee machine to wash my clothes?

Don't be absurd. I asked a perfectly valid question, and you come back with a non-sequitur?

SQL is meant to deal with rows in a relational database. Why should it care about Accounts? If you want to deal with Accounts, try Object databases. Or something else, I don't know.

Which, I hate to say, speaks volumes. Databases were (and ARE) presumably successful because they're very good at managing (note that verb) large volumes of data, structured in some way that makes sense to me, not just to database theorists.

So: My database better be darn sure it DOES care about Accounts if I tell it to.

Winston

[Pat Farrell]

Paul Anilprem wrote:Well, the name is Structured query language. It is meant to query. It is meant to be "asked" not to be "told".

Others have already blown this argument up. Its always had update commands. More importantly, SQL was designed to implement relational calculus, which was the key difference between relational DBMS and all other competitors. Relational calculus was a "basis set" of operations that you could do on data. It was complete. They proved you do could do anything you would want against data using relational calculus.

With prior DBMS technology, there were things you could NOT do. But with relational calculus, you could theoretically do anything. Sadly, being able to "theoretically do anything" does not mean that anyone can do it, or that when you specify it, that its efficient for some definition of efficient.

[Winston Gutkowski]

Martin Vajsar wrote:I'm far from being fluent in relational algebra, but on a few occasions I was able to arrive at an elegant SQL solution that displaced much more bloated procedural code. If the hypothetical new language offered good procedural code, but didn't provide all options supported by SQL, I wouldn't be happy with it.

Now there, I can't disagree. There are, absolutely, things for which SQL is definitely the best tool for the job - even in conjunction with a procedural "requestor". The problem for me is that they tend to be very specific - and, unfortunately, constrained by SQL - which makes it the "brick wall" that no external module can get past.

I wouldn't expect to have to write GUI code in terms of a screen painter. Why should I be forced to access database data in terms that only the database itself stipulates?

Winston

[Paul Anilprem]

Winston Gutkowski wrote:

Paul Anilprem wrote:That most databases support only SQL, is not SQL's fault. What has SQL got to do with it? Fault the database makers for not providing a language that you would like.

There is another? That is externally accessible? SQL is it AFAIK, so what else should I blame? It's the "public face" of the database....and, in procedural terms, it's crap.

Why in the world would I ask my coffee machine to wash my clothes?

Don't be absurd. I asked a perfectly valid question, and you come back with a non-sequitur?

Really sorry to waste everyones' bandwidth here but I really want to understand your point. So far, what I have understood is that you want to "tell" your db to add "account" using SQL and you are not able to do that. Your point is that that is why SQL is no good. Did I understand it correctly? If so, my point is that why should expect something that was not meant to be procedural to be procedural? In that sense, why would I ask coffee maker to wash my clothes? Why is that analogy absurd?

SQL is meant to deal with rows in a relational database. Why should it care about Accounts? If you want to deal with Accounts, try Object databases. Or something else, I don't know.

Which, I hate to say, speaks volumes. Databases were (and ARE) presumably successful because they're very good at managing (note that verb) large volumes of data, structured in some way that makes sense to me, not just to database theorists.

So: My database better be darn sure it DOES care about Accounts if I tell it to.

Winston

Again, why is it important for you that SQL and RDBMS care about Accounts? RDBMS is there to manage relational data. SQL is tool meant to manipulate that data. Now, I understand that you are not happy that it is not procedural enough. But then was meant to be procedural in the first place? Did it promise something that it failed to deliver?

[Winston Gutkowski]

Paul Anilprem wrote:Really sorry to waste everyones' bandwidth here but I really want to understand your point. So far, what I have understood is that you want to "tell" your db to add "account" using SQL and you are not able to do that. Your point is that that is why SQL is no good. Did I understand it correctly?

Basically, yes. My point is also that the paradigm hasn't changed in 30 years. On the procedural (programming) side, there have been many advances in technique and structure that allow us to make use of something more than just "do this...then do this" logic. Unfortunately, we're stuck with a declarative language (the same one we used 30 years ago) being the only way to communicate meaningfully with a database.

Some middleware, like Hibernate, has tried to reduce some of the more onerous boilerplate involved in writing programs that can actually do stuff in terms that make sense to a Java programmer, but in order to go further, it's likely to need the participation of database providers themselves, since that is where the work actually needs to be done if we want to harness the power that databases provide.

If so, my point is that why should expect something that was not meant to be procedural to be procedural? In that sense, why would I ask coffee maker to wash my clothes? Why is that analogy absurd?

OK, maybe 'absurd' was a bit OTT, but the fact is that coffee machines were never designed for washing clothes. Databases were designed to make access and update of complex data structures easier, faster, and safe. And furthermore, they were designed to make management of that data relevant to me, not some boffin in a back room.

Again, why is it important for you that SQL and RDBMS care about Accounts? RDBMS is there to manage relational data.

And what is an Account if not a piece of relational data? Data isn't some amorphous mass of relations and tuples and keys - except possibly to the Codd's and Date's of this world - it's information. And in order for a database to be useful at all, that information needs to be relevant to me. Or at least to the business I work for.

SQL is tool meant to manipulate that data. Now, I understand that you are not happy that it is not procedural enough. But then was meant to be procedural in the first place? Did it promise something that it failed to deliver?

Probably not, but why should it be the only way to access or update data?
INSERT CUSTOMER
may allow me to insert a row into a Customer table, but it doesn't achieve the business aim of adding a Customer, because it doesn't allow me to know what happened when I tried to do it. Surely, at the very least, it could hand me back a row-id if it worked, or null if it failed? That might then allow designers to create protocols for business processes. Databases have very sophisticated tools for modelling data for businesses; why not a language that allows you to process business requests on that data?

All you need to do is look at the tortuous code required to get something as basic as a sequence ID back from an INSERT request to know that there's something very wrong with the current set-up.

Winston

[Martin Vashko]

I believe the issue is a lock-in. Especially in Java world, there is a lot of striving for database independence. If a single database comes up with a new, revolutionary interface, for years that interface will be a database specific code. Who'll write that code, if he can write a portable (though inelegant) SQL code instead?

And if you ditch database independence, existing, database specific features already solve at least some of your grievances. In Oracle, for example, getting back a generated sequence ID in PL/SQL is easy. In Java is is a bit cumbersome, but still straightforward and efficient through the (Oracle specific) RETURNING clause. And most importantly - if you use Oracle's NEXTVAL and CURRVAL sequence attributes correctly, you may not need to read the generated IDs at all.

Database agnostic code makes it tortuous indeed, not least because not all databases use sequences at all.

[Winston Gutkowski]

Martin Vajsar wrote:I believe the issue is a lock-in. Especially in Java world, there is a lot of striving for database independence. If a single database comes up with a new, revolutionary interface, for years that interface will be a database specific code. Who'll write that code, if he can write a portable (though inelegant) SQL code instead?

Yeah, you may be right. Like I said: barking at the moon .

I'm quite surprised that, in 30 years, there seems to have been no major move on this stuff though; especially since many languages (not just Java) have become serious players in the business of mining information that pretty much only databases can manage.

And most importantly - if you use Oracle's NEXTVAL and CURRVAL sequence attributes correctly, you may not need to read the generated IDs at all.

But that gets back to the "fetch, then process" paradigm that I hate so much. In order to do that properly, I have to wrap the whole darn "request...process" routine in a transaction in order to make sure I don't get stale data. At least INSERT...RETURNING means that the database takes care of all that for me. Better still, what about a PUT statement, akin to Map.put()? I suspect what we actually need is a JDBPLC (although that'd probably get misinterpreted in the UK) .

Winston

[Jelle Klap]

Martin Vajsar wrote:Especially in Java world, there is a lot of striving for database independence.

I never really understood that. It's seems great in theory, but in practice it can be severely limiting. An RDBMS isn't something you choose willy-nilly, it's a fundamental part of the system as a whole, you just don't arbitrarily swap it out for something else. It feels so over-engineered, just like wrappers, around wrappers, around wrappers for - wait for it - logging.

[Bear Bibeault]

Not willy-nilly.

But for me it is useful to be able to develop and test locally on a PostgreSQL DB prior to deploying to the testing and production environments using (gag) SQL Server.

[chris webster]

Jelle Klap wrote:

Martin Vajsar wrote:Especially in Java world, there is a lot of striving for database independence.

I never really understood that. It's seems great in theory, but in practice it can be severely limiting. An RDBMS isn't something you choose willy-nilly, it's a fundamental part of the system as a whole, you just don't arbitrarily swap it out for something else. It feels so over-engineered, just like wrappers, around wrappers, around wrappers for - wait for it - logging.

Indeed. And if you're using a proprietary database you might as well get the most value for your money by using the platform-specific tools that your DB provides, where appropriate.

I see Bear's point, of course, and no doubt there are many systems where it is perfectly reasonable to stick to a minimal set of common SQL commands to allow you (or your customers) to swap your Access DB for Exadata at a moment's notice. But if you would otherwise want to use more platform-specific SQL tools, you can run a free (as in beer) "lite" version of most commercial databases - including SQL Server? - these days for development purposes. I've done with this with Oracle in the past, for example.

Of course, it depends on your application as well. In recent years I've done a lot of Oracle work on heavily data-centric applications that simply couldn't have been implemented properly without using a lot of platform-specific code, because they had very particular requirements relating to processing lots of data within the database, and the users were already committed to Oracle for the lifetime of the system anyway. Now I'm working on a PostgreSQL application, where I'm trying to minimise the platform-specific database code, not because we are likely to swap the DB, but simply because the clunky old imperative Java framework stuff is much easier if we stick to the minimal SQL set and don't get any ideas about doing clever stuff in the database instead of in Java!

[Pat Farrell]

Jelle Klap wrote: An RDBMS isn't something you choose willy-nilly, it's a fundamental part of the system as a whole, you just don't arbitrarily swap it out for something else.

Of course you don't swap it out arbitrarily, but sometimes you want to swap it out, say if Oracle buys up Sun which bought up a good open source database (MySql) and then announce that they are abandoning it so you can buy their grossly over-priced Oracle package. It could happen.

Jelle Klap wrote:It feels so over-engineered, just like wrappers, around wrappers, around wrappers for - wait for it - logging.

And the case of logging is special. (sadly, not rare enough). The Java logging API was badly thought out and half baked. So someone quickly rushed a 3/4 baked alternative. Then another 7/8 baked one. This is another case where the initial Java designers did not think it through, if they had started with an interface and factory, we would only have the one wrapper we need.

The JVM is one of the best things that has happened in computer science in the past few decades. Java the language, no so much.

[Ivan Jozsef Balazs]

The JVM is one of the best things that has happened in computer science in the past few decades. Java the language, no so much.

Fortunately other languages can "exist" above the JVM and the class libraries.

[chris webster]

Pat Farrell wrote:

Jelle Klap wrote: An RDBMS isn't something you choose willy-nilly, it's a fundamental part of the system as a whole, you just don't arbitrarily swap it out for something else.

Of course you don't swap it out arbitrarily, but sometimes you want to swap it out, say if Oracle buys up Sun which bought up a good open source database (MySql) and then announce that they are abandoning it so you can buy their grossly over-priced Oracle package. It could happen.

Funny you should mention that...!

[Martin Vashko]

Pat Farrell wrote:Of course you don't swap it out arbitrarily, but sometimes you want to swap it out, say if Oracle buys up Sun which bought up a good open source database (MySql) and then announce that they are abandoning it so you can buy their grossly over-priced Oracle package. It could happen.

Though in case of MySQL, the MariaDB is a drop-in replacement (at least unless I'm grossly mistaken, which is possible, since I don't have first-hand experience).

For open-source DBs, there shouldn't be such a danger at all. If they are successful, and someone tries to buy them out and destroy them, the open source community will surely do a fork and go on as usual.

I'm pretty much in sync with Jelle's view. There might be some rational reasons for swapping a database once in a quarter-centrury, but the database agnostic movement is about developing for a range of databases from the start. The application should work the same on MySQL as on Postgres, which is a true achievement, given that one uses lock-based concurrency and the other multiversioning. The net result is that the database is used just as a little bit more equipped file system.

[Pat Farrell]

Martin Vajsar wrote: The application should work the same on MySQL as on Postgres, which is a true achievement, given that one uses lock-based concurrency and the other multiversioning. The net result is that the database is used just as a little bit more equipped file system.

This can't happen if you use things like stored procedures, triggers, or PL-SQL, which are always specific to the particular package. When you use these, you are no longer really taking SQL