供应商中立的 SQL
我目前正在开发一个 Web 应用程序项目,该应用程序可能安装在具有各种软件配置的多个不同服务器上。我希望通过允许用户安装各种 SQL 服务器来使我的应用程序尽可能灵活。问题是任何两个服务器供应商使用的 SQL 语法都不匹配。举个简单的例子,下面是 MS SQL 和 MySQL 的相同 SELECT 语句:
MS SQL - SELECT TOP 1 * FROM MyTable ORDER BY DateCreated DESC
MySQL - SELECT * FROM MyTable ORDER BY DateCreated DESC LIMIT 1
是否有任何标准方法来抽象各个供应商的语句创建?有讨论这个问题的在线资源或书籍吗?有什么我觉得有用的提示或自作聪明的评论吗?
更多信息:我正在用在 Windows 服务器上运行的普通 ASP 编写我的应用程序。
谢谢,斯帕拉
I'm currently working on a project for a web application that may be installed on several different servers with various software configurations. I want to make my application as flexible as possible by allowing the user to have various SQL servers installed. The problem is the SQL syntax used by any two server vendors does not match up. For a simple example, here is the same SELECT statement for MS SQL and MySQL:
MS SQL - SELECT TOP 1 * FROM MyTable ORDER BY DateCreated DESC
MySQL - SELECT * FROM MyTable ORDER BY DateCreated DESC LIMIT 1
Are there any standard way to abstract the statement creation for various vendors? Any online resources or books discussing this problem? Any hints or smart-alec remarks that I'd find useful?
Further information: I'm writing my we application in vanilla ASP running on a Windows server.
Thanks, Spara
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
您可以遵守 ANSI SQL 92。我知道的所有主要 RDBMS 都支持这一点。
然而,各个 RDBMS 制造商添加了大量的东西来增强他们自己的 SQL 风格。这就是你陷入困境的地方。
您可能必须根据要连接的 RDBMS 在代码中进行分支,并在此时生成/选择适当的 SQL 语句。
更好的选择是为每个受支持的 RDBMS 创建一个 DAL。跨 DAL 实现 DAL 接口以使它们统一。这应该比切换代码更容易。
我建议您不要试图取悦所有人,而应该编写代码来支持您希望部署的前一两个系统,并在需要时添加对其他 RDBMS 的支持。
You can conform to ANSI SQL 92. All major RDBMS I know will support that.
However, there are tons of things individual RDBMS makers have added to enhance their own flavor of SQL. That is where you get into a lurch.
You may have to branch out in code depending on the RDBMS you are connecting to and generate / choose the appropriate SQL statement at that point.
A better option would be to create a DAL for each supported RDBMS. Implement a DAL interface across the DALs to make them uniform. This should be easier than switching in code.
I suggest that instead of trying to please everybody, you should write your code such that you support the top one or two systems that you expect to deploy on, and add support for other RDBMS as and when required.
我建议您使用 ORM(linq、nhibernate 等)来抽象 SQL 方言,而不是尝试编写普通的 SQL。
编辑:经典 ASP 有 OR/M 吗?
I suggest you use an ORM (linq, nhibernate etc) to abstract the SQL dialect away rather than trying to write plain vanilla SQL.
Edit: Is there an OR/M for Classic ASP?
你知道,我打赌你可以使用严格的 ansi sql,这只是需要一些努力,可能还需要额外的工作。也就是说,
ansi 中会有解决方法,因为实际上所有数据库特定的构造都是缩短和/或简化现有获取或描述数据的方法的方法。另一种选择可能是将对各种数据库的访问限制为存储过程和用户定义的函数。这样,您可以为一堆您知道将使用的数据库编写脚本,并要求在应用程序运行之前运行您的数据库特定脚本。
只是一个想法。
You know, I bet you could get by with strictly ansi sql, it will just take some effort and probably extra work. i.e.
There will be workarounds in ansi because really all of the db specific constructs are ways to shorten and or shortcut existing ways of getting at or describing data. Another option might be to restrict access to the various databases to stored procs and user-defined functions. That way, you could write scripts for a bunch of the dbs you know will be used with the requirement that your db specific script be run before the app will work.
Just an idea.