Converting SQL Server Scripts to be compatible with Derby

[Alain Kuchta <al...@etegent.com>]

Hello,

I'm currently evaluating Derby as a potential tool for use at my company. We currently use SQL Server for "stand-alone" deployments of our software. (the database is hosted locally) We are considering implementing Derby for this situation instead.

We would like to reuse the database creation and population scripts that we have written for SQL Server as much as possible for Derby.

Beyond simple differences in syntax there are a two issues which are hindering implementing our scripts with derby.

1) Conditionals. How does one effectively replicate the IF/ELSE statement available in Transact-SQL when using Derby?
2) Local variables. Dow does one achieve a similar effect to the local variable declaration available in Transact-SQL?

Thanks,

AK

RE: Converting SQL Server Scripts to be compatible with Derby

[Alain Kuchta <al...@etegent.com>]

Thank you Rick. Your response was very helpful. I will research this approach.

-----Original Message-----
From: Rick Hillegas [mailto:rick.hillegas@oracle.com] 
Sent: Thursday, August 02, 2012 1:37 PM
To: derby-user@db.apache.org
Subject: Re: Converting SQL Server Scripts to be compatible with Derby

On 8/2/12 9:36 AM, Alain Kuchta wrote:
>
> Hello,
>
> I'm currently evaluating Derby as a potential tool for use at my 
> company. We currently use SQL Server for "stand-alone" deployments of 
> our software. (the database is hosted locally) We are considering 
> implementing Derby for this situation instead.
>
> We would like to reuse the database creation and population scripts 
> that we have written for SQL Server as much as possible for Derby.
>
> Beyond simple differences in syntax there are a two issues which are 
> hindering implementing our scripts with derby.
>
> 1) Conditionals. How does one effectively replicate the IF/ELSE 
> statement available in Transact-SQL when using Derby?
> 2) Local variables. Dow does one achieve a similar effect to the local 
> variable declaration available in Transact-SQL?
>
> Thanks,
>
> AK
>
Hi Alain,

If I understand correctly, you are trying to convert Transact-SQL scripts into code which will run against Derby. Derby's standalone sql script tool (ij) does not support conditionals or variables. The usual solution to this problem is to put that code inside Derby stored procedures (which are simply static Java methods) and invoke those procedures via a CALL statement. More information on this can be found in the Derby Developer's Guide section titled "Programming database-side JDBC routines": http://db.apache.org/derby/docs/10.9/devguide/

Hope this helps,
-Rick

Re: Converting SQL Server Scripts to be compatible with Derby

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

On 8/2/12 9:36 AM, Alain Kuchta wrote:
>
> Hello,
>
> I’m currently evaluating Derby as a potential tool for use at my 
> company. We currently use SQL Server for “stand-alone” deployments of 
> our software. (the database is hosted locally) We are considering 
> implementing Derby for this situation instead.
>
> We would like to reuse the database creation and population scripts 
> that we have written for SQL Server as much as possible for Derby.
>
> Beyond simple differences in syntax there are a two issues which are 
> hindering implementing our scripts with derby.
>
> 1) Conditionals. How does one effectively replicate the IF/ELSE 
> statement available in Transact-SQL when using Derby?
> 2) Local variables. Dow does one achieve a similar effect to the local 
> variable declaration available in Transact-SQL?
>
> Thanks,
>
> AK
>
Hi Alain,

If I understand correctly, you are trying to convert Transact-SQL 
scripts into code which will run against Derby. Derby's standalone sql 
script tool (ij) does not support conditionals or variables. The usual 
solution to this problem is to put that code inside Derby stored 
procedures (which are simply static Java methods) and invoke those 
procedures via a CALL statement. More information on this can be found 
in the Derby Developer's Guide section titled "Programming database-side 
JDBC routines": http://db.apache.org/derby/docs/10.9/devguide/

Hope this helps,
-Rick