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

From: BP Margolin <bpmargo_at_attglobal.net>
Date: Fri, 20 Jul 2001 18:10:44 -0400
Message-ID: <3b58aa8c_2@news3.prserv.net>

Douglas,

I understand that you are looking to have a single code base, but I thought that it still might be useful if you had a Microsoft guide to porting from Oracle to SQL Server:

http://mspress.microsoft.com/prod/books/4939.htm

CHAPTER 7 Migrating Oracle Databases to SQL Server 2000

---

BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which can be cut and pasted into Query Analyzer is appreciated.

"Douglas Reay" <douglasr_at_chiark.greenend.org.uk> wrote in message news:X4h*9tH1o_at_news.chiark.greenend.org.uk...
> 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
Received on Fri Jul 20 2001 - 17:10:44 CDT