Which relational databases exist with a public API for a high level language?

Question

We typically interface with a RDBMS through SQL. I.e. we create a sql string and send it to the server through JDBC or ODBC or something similar.

Are there any RDBMS that allow direct interfacing with the database engine through some API in Java, C#, C or similar? I would expect an API that allows constructs like this (in some arbitrary pseudo code):

Iterator iter = engine.getIndex("myIndex").getReferencesForValue("23");
for (Reference ref: iter){
    Row row = engine.getTable("mytable").getRow(ref);
}

I guess something like this is hidden somewhere in (and available from) open source databases, but I am looking for something that is officially supported as a public API, so one finds at least a note in the release notes, when it changes.

In order to make this a question that actually has a 'best' answer: I prefer languages in the order given above and I will prefer mature APIs over prototypes and research work, although these are welcome as well.

------------------ Update ----------------

Looks like I haven't been clear enough.

What I am looking at is a lower level API, sort of what the RDBMS probably use internally. RDBMS have the concept of an execution plan, and the API I am looking for would allow us to actually execute an execution plan without specifying the intended result, using SQL or similar.

The very vague idea behind this is to implement a DSL which translates directly to RDBMS system calls, without going through SQL or similar.

Trying to explain it in yet a different way: When e.g. Oracle gets fed with a SQL statement, it parses that statement, creates an execution plan out of it and finally executes the execution plan using some internal API, which probably allows things like: retrieving a specific row from a table, retrieving a range or rowids from an index, joining to sets of rows using a hash join and so on. I am looking for that API (or something similar for an RDBMS where this is available)

---------- Another update after comment by Neil ----------------

I think it would be appropriate to consider the API I am looking for the 'ISAM' level as in the second bullet point on this article: http://en.wikipedia.org/wiki/ISAM

Answer 1

You may want to check out the following Wikipedia article for a list of interfacing alternatives to SQL for relational databases:

• Wikipedia: SQL: Alternatives to SQL

The list includes (in alphabetical order):

• .QL - object-oriented Datalog
• 4D Query Language (4D QL)
• Datalog
• Hibernate Query Language (HQL) - A Java-based tool that uses modified SQL
• IBM Business System 12 (IBM BS12)
• ISBL
• Java Persistence Query Language (JPQL)
• LINQ
• Object Query Language
• QBE (Query By Example)
• Quel
• Tutorial D
• XQuery

Answer 2

There is LINQ for .NET/C#. An example of what that would look like is:

var results = from row in db.SomeTable
              where row.Key == 23
              select row;

Which you can write in "natual" C# like so (that is, the above is syntactic sugar for):

var results = db.SomeTable.Where(row => row.Key == 23);

Answer 3

JDBC and ODBC already have what you need. Try looking at cursors, for example this.

The syntax will not be as compact as in LINQ for example, but you'll get your rows through which you'll be able to iterate.

You might also want to check for object relational mapping, because I assume that's what you are ultimately looking for.

Furthermore, you will probably find that LINQ and other approaches work well with single row updates and with simple retrievals, but that for general database work (multiple row updates, complicated retrievals, aggregates, etc) there is nothing like SQL. SQL is and will be the most natural way to communicate to RDBMS, since it is the native language for RDBMS.

If you don't want to use SQL you will have to either use a) one of approaches described at here or here. Keep in mind that these are full blown API's which map to SQL and that at the end they are essential no simpler and where they differ in pardigm, usually not as effective (unnatural fro RDBMS). b) Write your own data abstraction layer (possibly using one of the frameworks mentioned under a) providing natural methods for your objects to talk to the database. This way you'll get the best of both worlds and exactly what you need. Although this really shines in larger projects.

Answer 4

The stuff I was looking for is called ISAM as correctly pointed out by Neil Butterworth.

So the wikipedia article http://en.wikipedia.org/wiki/Isam gives some points to start further research.

If you happen to find this useful please upvote Neils comment above.