Using Postgresql Bablefish to connect Sybase app to Aurora Postgresql

Question

I have a large Sybase ASE C/C++ app which I would like to migrate to Postgresql. The application is using the Sybase CT library (nothing complex: simple SQL, few bulk uploads, etc.). Application is built and run on Linux using SYBASE OCS-16.

I'm trying to evaluate if the BabelFish extension can help speed up migration.

The first challenge (in moving from Sybsae) is that the application make extensive use of multi-statement queries to return multi result sets, using dynamically generated Transact-SQL. In few cases, the multi-statement queries use placeholders in the subsequent calls. For example: (this is not real code, but it's similar to the pattern)

declare @a int
select @a = ?
select foo,  bar from table1 where key = @a
select x, y from table2 where key = @a and date = ?
...

Quick check has shown that all those constructs are supported by Microsoft T-SQL.

The second challenge is the Sybase CT API. I believe best approach is to use openTDS, which provide the same API as the Sybase, and configure it to use TDS 7.X, which is supported by Babelfish.

Basically: (My application) -> (make CT Calls) -> (freeTDS) -> TDS 7.X -> (Babelfish) -> (Postgresql).

My Questions:

• Has anyone tried this approach for Sybsae->Postgresql.
• Is this approach likely to work ?
• Is freeTDS needed ? Will bablefish talk to a client speaking TDS 5.X (or 4.X) on the babelfish port ?
• Is there a better approach to address this problem ?

In this project, possible to make minor changes to the application (and to freeTDS if needed), but not possible to touch all the code that is issuing CT calls - too many modules - too time consuming - practically not an option.

Answer 1

Coming from Sybase, and having written the book on ASE... it is true that ASE T-SQL and MSSQL T-SQL still have many similarities, so from that perspective things may work. However, there are also many nasty little differences. To name just a random few aspects: the empty string (a single space in ASE, really empty in MSSQL), string concatenation with NULL (MSSQL has moved away from what is still the Sybase default), the T-SQL OJ syntax (no longer supported in MSSQL and neither in Babelfish); different behaviour by some BIFs in some cases (SUBSTRING(), SPACE()), BIFs that SQL Server does not have, different syntax for RAISERROR and PRINT, CREATE TYPE/sp_addtype differences, @@FETCH_STATUS, different syntax for hints, ROLLBACK TRIGGER, extended GROUP-BY syntax. And then there's things like CREATE DATABASE/ALTER DATABASE options, QUIESCE DATABASE, replication, partitioning, that's different. As for connectivity: FreeTDS may work, but from a Babelfish perspective this is not actually supported. In summary: you mileage may vary. Both syntactically and semantically there will be differences that may impact your migration, so while some parts may work, you may need to patch up your ASE SQL code before Babelfish would accept it.