Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Received-SPF: pass (hermes.apache.org: local policy)
Message-ID: <41A27AB0.2070508@golux.com>
Date: Mon, 22 Nov 2004 15:48:00 -0800
From: Army <army@golux.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
 rv:1.7.1) Gecko/20040707
MIME-Version: 1.0
To: Derby Development <derby-dev@db.apache.org>
Subject: Server-side DB creation from DRDA client
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

I have a question regarding the possibility and feasibility of adding new functionality to the Derby Network Server.

BACKGROUND:

With Derby Network Server, communication between server and client happens through use of the DRDA protocol.  That 
protocol doesn't have any explicit way to allow a client to create a database on the server side.  Nonetheless, if one 
is using IBM's Universal JDBC Driver  (hereafter referred to as "JCC"), one can successfully create a DB on the sever 
side by appending ";create=true" to the database name.  Ex.

Class.forName("com.ibm.db2.jcc.DB2Driver");
DriverManager.getConnection("jdbc:derby:net://localhost:1527/myDB;create=true", "someUser", "somePwd");

In this case, the call to connect transmits DRDA data to the server, and as part of that data, the database name is 
transmitted as "myDB;create=true".  Network server passes this name to the Derby driver, which looks at it, sees the 
"create=true" attribute, strips it off, creates a new database "myDB", and then connects to it.

In other words, Derby recognizes the "create=true" attribute as part of the database "name" and does the appropriate thing.

PROBLEM:

With the JCC client, appending ";create=true" to the database name is fine; however, there is no guarantee that such a 
syntax will work for other DRDA clients.  An example?  Well, in addition to working with IBM's DB2 Universal Driver for 
JDBC connections, Derby also has, in "beta", support for IBM's DB2 runtime client for ODBC connections.  See the 
following link for more:

http://www-106.ibm.com/developerworks/db2/library/techarticle/dm-0409kartha/index.html

In the latter case (DB2 runtime), the DRDA client doesn't like the ";create=true" syntax--it parses the semicolon 
internally and then throws an error saying that the URL is invalid.  I admit that I don't know what other DRDA clients 
exist (whether JDBC or ODBC), but it seems reasonable to think that the various clients could very well treat the 
";create=true" attribute in different and potentially problematic ways.

PROPOSAL FOR ADDED FUNCTIONALITY:

If it's possible to modify Derby (Network Server and/or embedded) to allow the creation of a server-side database from a 
DRDA client, and to do so in a robust manner WITHOUT having to modify or diverge from the DRDA protocol, it seems like 
that functionality could prove beneficial--namely, by allowing any (theoretically) DRDA client to create a Derby 
database from a server connection.  But that said, one would of course have to consider the following:

1 -- Desirability: Would users of Derby Network Server find this functionality useful?  Is it a good idea to allow 
creation of server-side databases, or is that a high-risk thing?  Would the potential benefits outweigh the potential 
risks?  Keeping in mind, of course, that with JCC, one can _already_ create server-side databases through Derby Network 
Server--so the risk is already there.

2 -- Feasibility: How to do it?  One thought that comes to mind is to use stored procedures in some way--but of course, 
one has to be connected to a database in order to execute a stored procedure, so I'm not real sure how that would work. 
  Also, Derby has a set of "connection" attributes that apply to database creation (ex. bootPassword)--how would one 
handle those?  What are the options there?

Anyone have any feedback/advice/knee-jerk reactions to these questions?

Thanks for taking the time to read this, and for any responses people might have...
Army