I want to be able to do something like I can do in the Oracle 'C' preprocessor:

EXEC SQL FOR :count

INSERT INTO FRED (FRED_ID)

VALUES (:a_fred_id);

where count says how many and the a_fred_id variable is an array. I know this is pre-processed code but it is available in the non-pre-processed environments as well. Obviously there is syntax for bulk fetching as well.

The current JDBC junk, where you fetch each record one at a time, has always seemed to me to have too much Microsoft/whoever ODBC heritage; everything is talking to Access on the same DOS machine.

Last time I looked into this they did allow you to send multiple statements, which was OK but so what? Array binding is genuinely useful and if too hard to implement for a given SQL engine then throw an exception saying so.

Many years ago a colleague did the array binding in C++ and then linked these C++ objects into Java. This was a factor of 10 (or maybe more) times faster than JDBC. If you are retrieving a list of, say, order lines, why on earth would you want them one at a time anyway? If you are retrieving a list of big XML documents again, you want them in the most network-efficient manner.

My opinion - and someone will doubtless tell me there is a way to do this, probably using Business Delegates which pass arrays back - fine, but why can't I have the whole array straight from the database in the first place?

JDK 6.0 includes support for JDBC 4.0, which has some new features:

1. DataSet. A DataSet is a parametrized type interface for the data returned by an SQL query.

2.Support for new data types. Support for database data types NCHAR, NVARCHAR, LONGNVARCHAR, and NCLOB has been added.

3.SQL 2003 XML Data Type Support.The SQL 2003 supports a new data type, ‘XML’, for storing XML documents. SQLXML is the Java mapping for the database type ‘XML’.

4. Automatic driver loading.

None of the databases provide a JDBC driver for the JDBC 4.0 specification yet. And few of the databases support the 'XML' data type. SQL Server 2005 and DB2 9 support the 'XML' data type.

Deepak:

Do XML datatypes thoroughly compromise the relational model? And, if not, why not?

The 'XML' data type is a new data type in SQL:2003 standard.

The 'XML' data type is just like any other SQL data type such as VARCHAR. A complete XML document is stored in the 'XML' type column. The database is still of a relational database type. Just like SQL data type VARCHAR maps to the Java data type java.lang.String, the 'XML' SQL data type maps to Java type java.sql.SQLXML.

A relational model is based on relvar, attribute, and tuple.

The corresponding structures in a relational database are table, column, and row. Adding a column of type 'XML' does not alter the relational model of the database.

However, at the column storage level the XML Infoset may be stored in a format other than the relational model.

As discussed by Philip Howard in a tutorial,On IBM and native XML(http://www.channelregister.co.uk/2005/04/06/ibm_db2_xml_native_storage/):"A datatype definition is, in essence, the definition of a mapping from the physical storage mechanisms used by the database to the logical representation of that data, so that it can be recognised and used automatically by standard database facilities such as the optimiser. When you define a new datatype you only create a new mapping, you do not impact the underlying storage mechanisms used by the database. Thus the fact that you have an XML datatype does not mean that the data is stored in XML format – it is still stored using whatever physical mechanisms are employed by that database."

However, the 'XML' data type does not specify the physical storage model for XML. As discussed in the tutorial: XML and Relational Database Management Systems: the Inside Story(http://portal.acm.org/citation.cfm?id=1066157.1066298): "The logical data model on which the XML datatype is based does not specify any particular organization for physical storage. In addition, access aids such as indexes may be created to improve query performance. Index creation and maintenance are more complex in a native XML system than in a pure relational system, since the XML data model is less constrained than the relational data model. XML indexes may support access to data at various levels of the element hierarchy, and the objects indexed may vary in cardinality and datatype."

Conclusion: At the database level the 'XML' data type does not alter the relational model of the database. At the column storage level the XML Infoset may be stored in a format other than the relational model.

I'm fully aware (even if some database vendors seem not to be) that the relational model as proposed by Codd relates purely to the logical view of the data - and says NOTHING about how it is physically stored on disk.

But I also thought that it said that a column must contain atomic values. Surely, if you have an XML datatype, a column can contain whole documents? Can a table containing such XML datatype columns be said to be normalised?

I realise that there is no real problem with mapping XML data into a fully normalised relational table, but this doesn't seem to be what XML datatypes are all about...

Data held in a relational database is supposed to be atomic. Codd’s rule 2, the guaranteed access rule says:

"Each and every datum (atomic value) in a relational data base is guaranteed to be logically accessible by resorting to a combination of table name, primary key value and column name."

I take atomic to mean, in general usage, that the data has no internal structure. The value ‘14’, it can be argued, has no internal structure, so it is atomic.

But it turns out to be rather difficult to define atomic.

In his recent book “Database in Depth - relational theory for practitioners” (ISBN 0-59610012-4, O’Reilly, 2005; Chris Date argues that the data types that we know and love, (integers, text, dates) are not really atomic at all. For a start, strings can be decomposed into characters. Dates, even more obviously, have an internal structure – day, month, year. Another argument says that, if these data types really are atomic, then why do we have so many functions that manipulate them and their internal structure – DateDiff, Date, Day, Hour, Chr, LCase, Left, InStr etc.?

Chris goes on to say that “The real point I’m getting at here is that the notion of atomicity has no absolute meaning; it depends on what we want to do with the data.”

So, is XML atomic data or not? My own personal belief (for what its worth) is that XML is non-atomic. It can have an arbitrarily complex internal structure and, for me, by the definitions I have used over the past 20 years, is clearly non-atomic.

But then, it we consider the arguments above, I think it is clear that the relational model is already handling non-atomic data so whether it really matters if XML is atomic or non-atomic is open to question.

Chris goes on to suggest that we can argue that relations can contain any type whatsoever. However he also says that an exception is that:

“…… no relation in the database can have an attribute of any pointer type. As you probably know, prerelational databases were full of pointers and access to such databases involved a lot of pointer-chasing; a fact that made application programming error-prone and direct end-user access impossible. Codd wanted to get away from such problems in his relational model, and of course he succeeded.”

A column of type XML is atomic from the SQL point of view in that you can select it as an entity. However it provides the ability to query and update into its structure, so from the programming point of view it is not atomic.

As JDBC 4.0 has not yet been implemented, the XML to database mapping provided with Hibernate 3.x may be used to store XML documents in a relational database.