Writing SQL Maps that support both SQL Server and Oracle

["Barnett, Brian W." <bb...@scholarinc.com>]

We have a web app that runs against SQL Server. All of our SQL maps are SQL
Server compliant. We now have to be able to support Oracle as well. (We
never thought it would happen... a mistake.)

Anyway, we are wondering if anyone has some general guidelines for writing
SQL Maps so that they run against both databases. Before you get scared and
close this email, let me say we're not looking for a list of differences
between the two databases, and how to resolve those differences. We already
have a doc that explains those things.

The first issue we have run into is the CONVERT and CAST functions of SQL
Server. We make use of them in our SQL maps. We are debating on whether we
should take them out and do the conversion or casting in java code or pass a
parameter to the SQL map indicating SQL Server or Oracle and then have a
<dynamic> element that generates the appropriate CONVERT or CAST syntax.

All input is welcome.

Thank you,
Brian Barnett

Re: Writing SQL Maps that support both SQL Server and Oracle

[Mike Fagan <mf...@tde.com>]

Brian,

I think you would be best served by creating separate maps for each 
database system. I went from Oracle to SQL Server and found it hard to 
impossible to make the sql identical. Using separate maps also gives you 
the ability to tune sql statements for the targeted RDBMS. Hopefully you 
can write DB specific Type Handlers to handle all your data conversion 
issues you might have so your data access code is identical.

Regards,
Mike Fagan

Barnett, Brian W. wrote:

>We have a web app that runs against SQL Server. All of our SQL maps are SQL
>Server compliant. We now have to be able to support Oracle as well. (We
>never thought it would happen... a mistake.)
>
>Anyway, we are wondering if anyone has some general guidelines for writing
>SQL Maps so that they run against both databases. Before you get scared and
>close this email, let me say we're not looking for a list of differences
>between the two databases, and how to resolve those differences. We already
>have a doc that explains those things.
>
>The first issue we have run into is the CONVERT and CAST functions of SQL
>Server. We make use of them in our SQL maps. We are debating on whether we
>should take them out and do the conversion or casting in java code or pass a
>parameter to the SQL map indicating SQL Server or Oracle and then have a
><dynamic> element that generates the appropriate CONVERT or CAST syntax.
>
>All input is welcome.
>
>Thank you,
>Brian Barnett
>
>  
>

Re: Writing SQL Maps that support both SQL Server and Oracle

[Ron Grabowski <ro...@yahoo.com>]

You could use stored procedures. 

I'm starting to port statements originally written for SQL Server over
to Access (with the .Net version of iBatis) and I think I can get away
with defining database specific functions in the properties file that I
define my database information:

<settings>
	<add key="userid" value="xxxxx" />
	<add key="password" value="xxxxx" />
	<add key="database" value="xxxxx" />
	<add key="datasource" value="xxxxx" />
	<add key="getDate" value="NOW()" />
	<add key="selectKey" value="SELECT @@IDENTITY" />
</settings>

Then in my sql maps:

<insert id="AddressInsert" parameterClass="Address">
	INSERT INTO Address
	(
		Street, 
		City, 
		Zip,
		DateAdded
	)
	VALUES
	(
		#Street#,
		#City#,
		#Zip#,
		${getDate}
	)
	<selectKey property="AddressId" type="post" resultClass="int">
		${selectKey}
	</selectKey>
</insert>

Another option would be to maintain database specific copies of your
sql maps :(

- Ron

--- "Barnett, Brian W." <bb...@scholarinc.com> wrote:

> We have a web app that runs against SQL Server. All of our SQL maps
> are SQL
> Server compliant. We now have to be able to support Oracle as well.
> (We
> never thought it would happen... a mistake.)
> 
> Anyway, we are wondering if anyone has some general guidelines for
> writing
> SQL Maps so that they run against both databases. Before you get
> scared and
> close this email, let me say we're not looking for a list of
> differences
> between the two databases, and how to resolve those differences. We
> already
> have a doc that explains those things.
> 
> The first issue we have run into is the CONVERT and CAST functions of
> SQL
> Server. We make use of them in our SQL maps. We are debating on
> whether we
> should take them out and do the conversion or casting in java code or
> pass a
> parameter to the SQL map indicating SQL Server or Oracle and then
> have a
> <dynamic> element that generates the appropriate CONVERT or CAST
> syntax.
> 
> All input is welcome.
> 
> Thank you,
> Brian Barnett
>