[
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 like:
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 server.
However, what about the compatibility with existing ij scripts, if those
already embed parameter-like strings?
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;
or
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!
Sylvain.
> 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.
