Re: Oracle / MS SQLServer - string concatenation & other migration issues

Douglas Reay wrote:

> I have recently been tasked with taking a java application that runs
> against a database running on Oracle ( 8.1.5i ) and making it capable
> of running against both Oracle and Microsoft SQL Server ( 7.x or 2k ).
>
> I was hoping that I could use SQLServer's user defined functions feature
> to define equivalents of some of Oracle's functions, rather than change
> the majority of the java code to have to be aware of the db type.
>
> For example:
>
> In Oracle, if I want to concatenate two strings, I can do either:
> SELECT 'foo' || 'bar'
> or
> SELECT CONCAT( 'foo', 'bar' )
> while in SQLServer one uses:
> SELECT 'foo' + 'bar'
>
> Now, using SQLServer's UDFs I can do:
> CREATE FUNCTION concat ( @one varchar(1024), @two varchar(1024) )
> RETURNS varchar(2048) AS
> BEGIN
> RETURN @one + @two
> END
>
> But unfortunatly, that still leaves me having to call it as:
> SELECT douglas.CONCAT( 'foo', 'bar' )
>
> Is there any way around this, or am I stuck with forking the java?
>
> ===================================================================
>
> Second Question.
>
> What other gotchas am I likely to come across, and are there any good
> resources listing the differences between the SQL from different
> databases?
>
> Other things I've noticed differences in so far:
>
> * casting
> * maximum sizes of numbers
> * dates (how do you specify the exact format you want a data object
> to be displayed as in SQLServer? eg yyyy-mm-dd hh:mm:ss)
> * reserved words (eg "full" and "metadata")
>
> Douglas
> --
> By the way, getting sufficient compatability between Oracle & Postgres
> was no problem

Every attempt I have ever seen to do this has ended in either failure or with bad code unless the coding was done in the back-end as procedures.

Daniel A. Morgan Received on Fri Jul 20 2001 - 12:02:28 CDT