db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Sylvain Leroux (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4557) Make it possible to parameterize ij scripts.
Date Sat, 20 Feb 2010 16:32:27 GMT

    [ https://issues.apache.org/jira/browse/DERBY-4557?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12836200#action_12836200

Sylvain Leroux commented on DERBY-4557:

Hi Denis,

This is a very interesting issue - since I think it is a first step toward extending the "scripting"
capabilities of ij. And you're welcome to work on it.

Here are some random thoughts concerning this issue.


I think there is two use cases suggested here:
1) Having a way to propagate values from the calling context to an ij script. Using positional
parameters $1, $2,  etc.
2) Having a way to store values (constants?) in ij for re-use. That's the ${dbname}, ${user},
and so on.

The second point implies there should be a way in ij to define those variables. Something
ij> SET user AS 'fred';

Or are we only considering accessing environment variables and/or java properties?


Substituting parameters before parsing seems to be the easiest way of doing. Especially since
substitution must occur both in ij statements and in DBMS statements. Moreover, since ij could
interact with any JDBC data source, it has no knowledge of the syntax used by a particular

However, what about the compatibility with existing ij scripts, if those already embed parameter-like
ij> INSERT INTO T(user, password) VALUES('john', '$1');
In the (rather simplistic) example above, the statement will set the password of "fred" to
"$1" with some versions of ij, but will set it to the empty string - or whatever the parameter
$1 is defined - with other versions.


Considering the following variable definition:
ij> SET UserName AS 'O''Hara';
ij> INSERT INTO T(UserName) VALUES('${UserName}');
When will the double-simple-quote ('') be substituted by a simple-quote (')? If we stick with
the aproach of using simple text substitution, we could state that a variable stores the 'raw
value' (that is with double-simple-quote). 
But, in that case, positional parameters must follow the same rule. However, I think this
could be error prone:
  # This will work
  sh$ export username="Sylvain Leroux"
  sh$ ij SetupUserEnvironment.sql "${username}"
  # But that might require "doubling" the quote
  sh$ export username="Peter O'Hara"
  sh$ ij SetupUserEnvironment.sql "${username}"

And I don't think it is really user friendly to impose SQL escape sequences outside ij.


Couldn't we seize the opportunity here to introduce "true" variables? With some kind of string
substitution operator we could achieve mostly the same goal, but leaving room for later improvements.
Here's a thing I have in mind:
  ij> connect 'jdbc:derby:?;user=?;password=?' SUBSTITUTING $1, $2, $3; 
  ij> EXECUTE 'insert into ?( a ) values ( ? )' SUBSTITUTING $4, $5;
  ij> connect 'jdbc:derby:?;user=?;password=?' SUBSTITUTING dbname, user, password; 
  ij> EXECUTE 'insert into ?( a ) values ( ? )' SUBSTITUTING tableName, intValue;

This is quite verbose, so I think we could find a better alternative, but that's the spirit.
Something more like that could be better:
  ij> connect SUBSTITUTING('jdbc:derby:$1;user=$2;password=$3');
  ij> SUBSTITUTING('insert into $4( a ) values ( $5 )');

Maybe this is "too much", but one reason I push toward this direction is that, at some point
in the future, we might wish ij to be able to handle "complex" maintenance scripts:
  ij> SET dblist AS 'select dbname, user, password from maintenance';
  ij> FOREACH dblist 
  >       connect 'jdbc:derby:?;user=?;password=?' SUBSTITUTING dblist[0], dblist[1], dblist[2];

  >       alter table .....
  >       disconnect


Feel free to comment!

> Make it possible to parameterize ij scripts.
> --------------------------------------------
>                 Key: DERBY-4557
>                 URL: https://issues.apache.org/jira/browse/DERBY-4557
>             Project: Derby
>          Issue Type: Improvement
>          Components: Tools
>            Reporter: Rick Hillegas
> It would be nice to be able to parameterize ij scripts. At a high level, this would be
akin to the $N parameters which you pass on the command line to Unix shell scripts. I think
it would be easiest to understand if parameter substitution happened before the script was
handed to the ij parser.
> It would be nice to write something like the following:
> connect 'jdbc:derby:$1;user=$2;password=$3';
> insert into $4( a ) values ( $5 );
> or something like this:
> connect 'jdbc:derby:${dbname};user=${user};password=${password}';
> insert into ${tableName} values ( ${intValue} );

This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message