10

I have a scenario where I get queries on a webservice that need to be executed on a database.

The source for these queries is from a physical device so I cant really change the input to my queries.

I get the queries from the device in MSSQL. Earlier the backend was in SQL Server, so things were pretty straight forward. Queries would come in and get executed as is on the DB.

Now we have migrated to Postgres and we don't have to the option to modify the input data (SQL queries).

What I want to know is. Is there any library that will do this SQL Server/T-SQL translation for me so I can run the SQL Server queries through this and execute the resulting Postgres query on the database. I searched a lot but couldn't find much that would do this. (There are libraries that convert schema from one to another but what I need is to be able to translate SQL Server queries to Postgres on the fly)

I understand there are quite a bit of nuances that will be different between SQL and postgres so a translator will be needed in between. I am open to libraries in any language(that preferably runs on linux : ) ) or if you have any other suggestions on how to go about this would also be welcome.


Thanks!

Improve this question
4
  • 5
    mssql uses transact sql (TSQL) as language.. So you are looking for a way to translate tsql to pl/pgsql on the fly? Quick google found this. tpostgres.org it might be of some help. which points to this bitbucket.org/openscg/pgtsql Commented Mar 4, 2015 at 16:17
  • I believe there are/were some [commercial] attempts at "SQL proxy adapters/converters" between different databases. Commented Mar 4, 2015 at 16:20
  • 2
    Queries aren't data, they are code. Why can't you modify them? If they are spread out in the code, you have a serious design issue that can't be hidden behind a magic translator. If they were stored in the database as strings, convert them one by one. Also consider what made you store them as raw strings instead of views/stored procedures. In general, if you want your code to target multiple databases use an ORM. Otherwise use a database project that you can convert between products, or a code generator that will create the target SQL from a model Commented Mar 4, 2015 at 16:24
  • 1
    Actually these are hand-held devices that I haven't programmed or have access to the code. The web-service I am maintaining also gets called from a few other places that I don't control. So pretty translator seems to be the best bet for me at this point. Commented Mar 4, 2015 at 16:27

3 Answers 3

Reset to default
2

If I were in your position I would have a look on upgrading your SQL Sever to 2019 ASAP (as of today, you can find on Twitter that the officially supported production ready version is available on request). Then have a look on the Polybase feature they (re)introduced in this version. In short words it allows you to connect your MSSQL instance to other data source (like Postgres) and query the data in as they would be "normal" SQL Server DB (via T-SQL) then in the background your queries will be transformed into the native pgsql and consumed from your real source. There is not much resources on this product (as 2019 version) yet, but it seems to be one of the most powerful features coming with this release.

This is what BOL is saying about it (unfortunately, it mostly covers the old 2016 version).

There is an excellent, yet very short presentation by Bob Ward ( Principal Architect @ Microsoft) he did during SQL Bits 2019 on this topic.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

The only thing I can think of that might be worth trying is SQL::Translator. It's a set of Perl modules that have been around for ages but seem to be still maintained. Whether it does what you want will depend on how detailed those queries are.

Improve this answer

Comments

0

The no-brainer solution is to keep a SQL Server Express in place and introduce Triggers that call out to the Postgres database.

If this is too heavy, you can look at creating a Tabular Data Stream (TDS is SQL Server network transport) gateway with limited functionality and map each possible incoming query with any parameters to a static Postgres query. This limits any testing to a finite, small, number of cases.

This way, there is no SQL Server, and you have more control than with the trigger option.

If your terminals have a limited dialect demand then this may be practical. Attempting a general translation is very likely to be worth more than the devices cost to replace (unless you have zillions already deployed).

There is an open implementation FreeTDS that you could use if you are happy with C or Java.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.