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

Hi Douglas,

This is not an answer to your question but perhaps a suggestion for future. I learned my lesson about a year ago when I had to do a similar thing. The suggestion is not to mix your database code with the business logic or front-end. There are tools in the market, like Cocobase from Though Inc. that would generate the Java code for you. If you are looking for cheaper solutions that visit http://www.imranweb.com and read the technical article about MVC written by this fellow. He has an application that comes with source code that generates Java objects that map to your database.

I suggest that if you end up changing a lot of code, consider using any of these products. So that if you have to change your queries, they all are in one place and can be done easily.

Thanks.

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
Received on Fri Jul 20 2001 - 17:36:52 CDT