转换不同数据库的SQL查询

Is there a tool to convert from one SQL query of one database to another?

是否有工具可以从一个数据库的SQL查询转换到另一个数据库?

For SQLite

为SQLite

> CREATE TABLE ConstantValues(    Id int
> AUTOINCREMENT primary key   ,
> VariableName varchar(50)    , Values
> varchar(150) )

For SQL Server

对SQL Server

> CREATE TABLE ConstantValues(    Id
> INTEGER identity(1,1) primary key   ,
> VariableName varchar(50)   , Values
> varchar(150) )

Similarly it is different for Oracle and SQL Server. Also in the foreign key constraints declaration, if there is a tool so that we can get SQL from any database to any database, it would be really helpful for me.

类似地，Oracle和SQL Server也不同。另外，在外键约束声明中，如果有一个工具可以让我们从任何数据库获取SQL到任何数据库，这对我来说将非常有帮助。

I have created a function like this, but it doesn't seem to be a good solution:

我创建了一个这样的函数，但它似乎不是一个好的解决方案:

private string changeSQL(string sql)
{
    switch (dbtype)
    {
        case dbType.SQLite:
            sql = sql.Replace(" int ", " INTEGER ");
            sql = sql.Replace(" identity(1,1) ", " AUTOINCREMENT ");
            break;
        case dbType.MsAscess:
            sql = sql.Replace(" int ", " ");
            sql = sql.Replace(" identity(1,1) ", "");
            sql = sql.Replace("AUTOINCREMENT", "AUTOINCREMENT");

            break;
    }
    return (sql);
}

Similarly for SQLite, concatenation is done using || while in SQL Server it is done using +.

类似地，SQLite使用||进行连接，而在SQL Server中使用+。

5 个解决方案

#1

SwisSQL Console 5.0

5.0 SwisSQL控制台

Console offers an instant solution providing quick and reliable SQL query conversion utility that accelerates migration. Console supports migration across Oracle, SQL Server, IBM DB2, MySQL, Sybase, PostgreSQL, Informix and Netezza databases. This software also has features to test the converted SQLs in target databases.

控制台提供了一个即时解决方案，提供了快速和可靠的SQL查询转换实用程序，可以加速迁移。控制台支持跨Oracle、SQL服务器、IBM DB2、MySQL、Sybase、PostgreSQL、Informix和Netezza数据库进行迁移。该软件还具有测试目标数据库中转换后的sql的特性。

#2

first you need to know and understand that every SQL engine works with a different SQL grammar. Despite the SQL ANSI standard, no language on earth respects it 100%. Moreover, every large and known SQL engine adds own methods and stuff to the original grammar.

首先，您需要了解和理解每个SQL引擎都使用不同的SQL语法。尽管有SQL ANSI标准，但没有一种语言能100%地尊重它。此外，每个大型和已知的SQL引擎都在原始语法中添加自己的方法和内容。

So, if you want to do a conversion, the easiest way is to achieve a middle SQL layer. That means, to create an agnostic SQL grammar out of the very common features in every well known SQL engine (it would result in something like SQL ansi plus every feature present in every engine, like TOP). Once you have this, you have to make the conversion to this middle layer, and from this middle layer for each SQL variation you need.

因此，如果要进行转换，最简单的方法是实现中间的SQL层。这意味着，要在每个众所周知的SQL引擎中创建一个不可知的SQL语法(它将导致像SQL ansi这样的东西，加上每个引擎中出现的每个特性，比如TOP)。一旦有了这个，您就必须对中间层进行转换，并从中间层对您需要的每个SQL变体进行转换。

I told you this because I needed this exact thing at my work, and that was the only way to actually achieve it, and made it reusable. Having a tool gives you the job to actually manually convert every single query, and make huge SWITCHs just to choose the query, or to have an inherited class for every engine.

我告诉你这个是因为我在工作中需要这个东西，这是实现它的唯一方法，并使它可重用。有了工具，您就可以手动地转换每个查询，并进行巨大的切换来选择查询，或者为每个引擎创建一个继承类。

I tell you what I've done: I created the BNF of my SQL middle grammar, then created a tree parser with GoldParser for C#. Then I created individual rules for each rule in the grammar to be converted to each SQL dialect. It's a huge and tedious job, I know. But they'd paid me to do it...

我告诉您我做了什么:我创建了SQL中间语法的BNF，然后为c#创建了一个带有GoldParser的树解析器。然后，我为语法中的每个规则创建了单独的规则，并将其转换为每个SQL方言。我知道这是一项庞大而乏味的工作。但他们付钱让我做……

If you don't have the time to accomplish this, you could use ODBC. Every SQL engine has an ODBC connector, and the ODBC itself will act as a middle abstract layer. But, it's not as happy as it sounds, because only simple queries will maintain this illusion... hard stuff like UNION, JOINs, and metadata creation won't be the same.

如果您没有时间完成此任务，可以使用ODBC。每个SQL引擎都有一个ODBC连接器，ODBC本身将充当中间抽象层。但是，它并不像听起来那么快乐，因为只有简单的查询才能保持这种错觉……像UNION、join和元数据创建这样的硬东西是不一样的。

I hope it helped,

我希望它帮助,

good luck

祝你好运

#3

If I were to support multiple database management systems, I'd do it thoroughly, with a Data Access Layer for each system. It'd require some amount of work, of course, but the modularity would be quite beneficial.

如果我要支持多个数据库管理系统，我将彻底地使用每个系统的数据访问层。当然，这需要一定的工作量，但是模块化将是非常有益的。

One alternative I'm quite happy with, is DevExpress' XPO. It's an Object Relational Mapping system that supports multiple databases. You design your classes, define a proper connection string, and the database schema will be created for you, and you can apply crud to your classes easily in code. In order to use a different database system, only change the connection string!

我很满意的另一个选择是DevExpress的XPO。它是一个支持多个数据库的对象关系映射系统。您可以设计您的类，定义适当的连接字符串，并为您创建数据库模式，并且您可以在代码中轻松地将crud应用到您的类。为了使用不同的数据库系统，只更改连接字符串!

And no, I'm not affiliated with DevExpress other than as a very pleased customer.

不，我不是DevExpress的员工，而是一个非常满意的客户。

http://www.devexpress.com/Products/NET/ORM/info.xml

#4

This isn't an automated tool, but the best resource I've found for understanding the differences between the different SQL implementations is O'Reilley's SQL Cookbook by Anthony Molinaro. http://oreilly.com/catalog/9780596009762/

这不是一个自动化的工具，但是我找到的理解不同SQL实现之间差异的最好的资源是Anthony Molinaro的SQL Cookbook。http://oreilly.com/catalog/9780596009762/

He shows how to solve many different problems with clever SQL queries, including presenting side by side solutions for Oracle, SQL Server, DB2, MySQL and Postgres when they use different syntax or proprietary features. Changing your code to work with a different database is alot easier given descriptions of how they systems differ.

他展示了如何用聪明的SQL查询解决许多不同的问题，包括在使用不同的语法或专有特性时，为Oracle、SQL Server、DB2、MySQL和Postgres提供并行解决方案。更改代码与不同的数据库一起工作并不容易，因为它描述了它们的系统是如何不同的。

Molinaro also does a nice job of explaining windowing queries (or what Oracle calls analytic queries) which are well worth the time to learn, as you can accomplish things very efficiently with a query that previously required custom client code that wasted bandwidth and time.

Molinaro还很好地解释了窗口查询(或者Oracle所称的分析查询)，这些查询非常值得花时间去学习，因为您可以通过查询高效地完成任务，而查询之前需要的定制客户端代码浪费了带宽和时间。

#5

-1

give me details about query converter for diff databases

请提供有关diff数据库查询转换器的详细信息

thats your artical " Convert SQL query for a different database "

这就是你的文章“为不同的数据库转换SQL查询”

#1