Use variables in SQL script?

["Bailey, David" <Da...@pearson.com>]

I'm considering Derby as one of several possible replacement databases
for a product of ours currently running on Microsoft's SQL Server
Express.  As part of the evaluation process, I am trying to build a
Derby database using existing SQL scripts we have which use T-SQL
dialect.  Obviously, I need to make changes here or there to get the
scripts to run in the Derby dialect.
 
One thing I am having a great deal of difficulty with is the following.
In T-SQL, it is possible to define variables within an SQL script as
follows:
 
    DECLARE @myvar int;
    SET @myvar = 100;
 
And for the rest of my script, T-SQL will make the appropriate
substitution whenever it encounters the string '@myvar'.  Derby appears
not to like the '@' symbol.
 
I'm assuming the Derby dialect supports this sort of variable
declaration, but I'm having a hard time hitting on the proper syntax,
and I'm having just as much difficulty finding any useful information in
the documentation or online.  Can someone explain to me how this works?
 

David Bailey

Software Developer
Assessment & Information 
Pearson

D: (952) 681-3890


Pearson 

Always Learning
Learn more at www.pearsonassessments.com


 

Re: Use variables in SQL script?

[Rick Hillegas <ri...@oracle.com>]

Thanks for raising this topic, David and Thomas. Feel free to vote for 
this new enhancement request: 
https://issues.apache.org/jira/browse/DERBY-5298 I have worked around 
this problem by using the helper functions attached to 
https://issues.apache.org/jira/browse/DERBY-5242

Thanks,
-Rick

On 6/27/11 2:53 AM, Thomas wrote:
> David,
>
> I am afraid Derby doesn't implement variables - I had been looking for this
> myself also some time back when writing Derby scripts for test data
> creation/manipulation. I finally ended up writing small Java programs where
> needed instead of or on top of just scripts. Since a couple of days I am using
> Squirel as front-end rather than ij and this tool has a script plug-in which
> sounds interesting, but I have not really looked into this plug-in yet whether
> it would provide any help when it comes to variables. When I asked basically the
> same question you just did to the derby community there was a mention that it
> should not be too hard to enhance ij to offer this support - however I have not
> raised a feature request and to the best of my knowledge this didn't go any
> further than the communication on the user mailing list itself. I would be
> willing to help document/test such feature, but can't program myself as I am not
> a programmer.
>
>
>
>

Re: Use variables in SQL script?

[Thomas <Th...@t-online.de>]

David,

I am afraid Derby doesn't implement variables - I had been looking for this
myself also some time back when writing Derby scripts for test data
creation/manipulation. I finally ended up writing small Java programs where
needed instead of or on top of just scripts. Since a couple of days I am using
Squirel as front-end rather than ij and this tool has a script plug-in which
sounds interesting, but I have not really looked into this plug-in yet whether
it would provide any help when it comes to variables. When I asked basically the
same question you just did to the derby community there was a mention that it
should not be too hard to enhance ij to offer this support - however I have not
raised a feature request and to the best of my knowledge this didn't go any
further than the communication on the user mailing list itself. I would be
willing to help document/test such feature, but can't program myself as I am not
a programmer.