PowerBuilder.NET 无法正确读取 Datawindow SQL 语法
希望以前有人遇到过这个问题,因为它已经困扰了我和同事几天。
我们成功地将项目从 PowerBuilder 12 Classic 转换为 PowerBuilder.NET,并且运行正常,但我们遇到了 PowerBuilder.NET 如何从数据窗口读取 SQL 语法的问题。
打开数据窗口,我复制语法并将其粘贴到 SQL Server 2008 R2 中,它完成了所有检索。 SQL 如下(请注意,删除了一些字段以节省空间)
SELECT hl7_in.intf_app_parm_id,
hl7_in.sending_app,
hl7_in.msg_typ,
hl7_in.process_ind,
hl7_in.hl7_in_seq_no,
hl7_in.msg_evnt_typ,
hl7_in.wrng_msg_cnt,
pt.pt_middle_name,
hl7_in.resolved_ind,
hl7_in.pt_id,
hl7_in.hl7_msg_cntl_id,
hl7_in.msg_txt,
intf_app_parm.app_parm_desc,
intf_engine.engine_name
FROM {oj hl7_in LEFT OUTER JOIN pt ON hl7_in.pt_id = pt.pt_id}, {oj intf_app_parm LEFT OUTER JOIN intf_engine ON intf_app_parm.intf_engine_id = intf_engine.intf_engine_id}
WHERE ( hl7_in.intf_app_parm_id = intf_app_parm.intf_app_parm_id ) and
( ( intf_app_parm.direction_ind = 'I' ) )
但是,当在运行时执行代码时,我收到 SQL 错误(即使 SQL 有效)
SQLSTATE = 42000
Microsoft SQL Native Client
The multi-part identifier "intf_app_parm.intf_engine_id" could not be found
在尝试找出导致错误的原因后(因为上面的 SQL 上的两个连接都工作正常),我决定我们应该通过调试器检查数据窗口。这就是我们所做的,并且我们找到了原因(请参阅下面的 SQL):
SELECT hl7_in.intf_app_parm_id,
hl7_in.sending_app,
hl7_in.msg_typ,
hl7_in.process_ind,
hl7_in.hl7_in_seq_no,
hl7_in.msg_evnt_typ,
hl7_in.wrng_msg_cnt,
pt.pt_middle_name,
hl7_in.resolved_ind,
hl7_in.pt_id,
hl7_in.hl7_msg_cntl_id,
hl7_in.msg_txt,
intf_app_parm.app_parm_desc,
intf_engine.engine_name
FROM hl7_in LEFT OUTER JOIN pt ON hl7_in.pt_id = pt.pt_id
LEFT OUTER JOIN intf_engine ON intf_app_parm.intf_engine_id = intf_engine.intf_engine_id
WHERE ( hl7_in.intf_app_parm_id = intf_app_parm.intf_app_parm_id ) and
( ( intf_app_parm.direction_ind = 'I' ) )
如果您查看 FROM 语句,您将看到 PowerBuilder 决定通过删除 pt.pt_id 之后的逗号 (,) 来修改该语句以及第二个连接的开始部分 (intf_app_parm)。我们决定检查原始的 PB12 Classic 代码,看看是否也存在问题,但这种奇怪的事情并没有发生(检索语句被正确读取)。
在应用程序期间的任何时候,数据窗口本身都不会被修改,SQL 语句也不会被修改。有过使用 PowerBuilder.NET 经验的人曾经见过这种情况吗?如果是这样,您采取了什么措施来解决该问题(这种情况发生在多个数据窗口上)。
谢谢您的宝贵时间!
Hopefully, someone has come across this issue before, as it has stumped a co-worker and I for a few days.
We successfully converted our project from PowerBuilder 12 Classic to PowerBuilder.NET and things are running decently, but we are experiencing an issue with how PowerBuilder.NET is reading the SQL syntax from a datawindow.
Opening up the datawindow, I copied the Syntax and pasted it into SQL Server 2008 R2, and it did all of it's retrieval. The SQL is below (note that some of the fields are removed to save space)
SELECT hl7_in.intf_app_parm_id,
hl7_in.sending_app,
hl7_in.msg_typ,
hl7_in.process_ind,
hl7_in.hl7_in_seq_no,
hl7_in.msg_evnt_typ,
hl7_in.wrng_msg_cnt,
pt.pt_middle_name,
hl7_in.resolved_ind,
hl7_in.pt_id,
hl7_in.hl7_msg_cntl_id,
hl7_in.msg_txt,
intf_app_parm.app_parm_desc,
intf_engine.engine_name
FROM {oj hl7_in LEFT OUTER JOIN pt ON hl7_in.pt_id = pt.pt_id}, {oj intf_app_parm LEFT OUTER JOIN intf_engine ON intf_app_parm.intf_engine_id = intf_engine.intf_engine_id}
WHERE ( hl7_in.intf_app_parm_id = intf_app_parm.intf_app_parm_id ) and
( ( intf_app_parm.direction_ind = 'I' ) )
But, when the code is executed during runtime, I get an SQL error (even though the SQL works)
SQLSTATE = 42000
Microsoft SQL Native Client
The multi-part identifier "intf_app_parm.intf_engine_id" could not be found
After trying to figure out what was causing the error (since both the joins on the above SQL work fine), I decided that we should check out the datawindow through the debugger. Well that's what we did, and we figured out why (Please see the below SQL):
SELECT hl7_in.intf_app_parm_id,
hl7_in.sending_app,
hl7_in.msg_typ,
hl7_in.process_ind,
hl7_in.hl7_in_seq_no,
hl7_in.msg_evnt_typ,
hl7_in.wrng_msg_cnt,
pt.pt_middle_name,
hl7_in.resolved_ind,
hl7_in.pt_id,
hl7_in.hl7_msg_cntl_id,
hl7_in.msg_txt,
intf_app_parm.app_parm_desc,
intf_engine.engine_name
FROM hl7_in LEFT OUTER JOIN pt ON hl7_in.pt_id = pt.pt_id
LEFT OUTER JOIN intf_engine ON intf_app_parm.intf_engine_id = intf_engine.intf_engine_id
WHERE ( hl7_in.intf_app_parm_id = intf_app_parm.intf_app_parm_id ) and
( ( intf_app_parm.direction_ind = 'I' ) )
If you take a look at the FROM statement, you will see that PowerBuilder decided to modify that statement by removing the comma (,) after pt.pt_id as well as the beginning part of the second join (intf_app_parm). We decided to check the original PB12 Classic code to see maybe it was a problem there too, but this strange thing doesn't happen there (the retrieve statement is read correctly).
The datawindow itself is not modified at any point during the application, nor is the SQL statement modified either. Anyone that has had experience with PowerBuilder.NET ever see this happen? If so, what did you do to fix the issue (this is happening on more than one data window).
Thank you for your time!
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
您可能看到的是为符合 ODBC 规范而构建的语法。大括号是 ODBC 转义序列。您是否有可能在开发过程中使用 ODBC 连接(数据库配置文件)并使用应用程序中的本机驱动程序连接到数据库,反之亦然?
如果 SQL Server 数据库引擎不直接支持,SQL Server ODBC 驱动程序会将任何 ODBC 语法即时转换为 T-SQL。
另请注意,SQL 中的区别不是缺少逗号,而是其中一个而不是另一个中的 where 子句部分周围的大括号。 :)
*多年后编辑此内容,我刚刚了解到 PB12.5 不支持 SQL Server 2008 及更高版本,但支持 2000 和 2000 及更高版本。 2005,但我的 PowerBuilder 2017 支持直接从帮助文件中连接到 SQL Server 2008、2002、2014、2016。只要您使用较新的提供程序,PB 11.5 就支持 SQL Server 2008。我们遇到了一个问题,您只能放置一个检索参数,尝试添加更多参数会导致第 2 列上的语法无效,这是我在职业生涯中从未见过的,并且使用了 PB 3 及以上的所有版本。
您可以在此处获取更多信息:
Microsoft ODBC 程序员参考:
http://msdn.microsoft.com/en- us/library/....
特别针对 ODBC SQL 语法:
http://msdn.microsoft.com/en-我们/图书馆/....
What you are probably seeing is syntax that was constructed to conform to ODBC specification. The braces are an ODBC escape sequence. Is it possible you are using an ODBC connection during development (DB Profile) and connecting to the database using native driver in the application, or vice-versa?
The SQL Server ODBC driver will translate any ODBC syntax into T-SQL on the fly, if it is not directly supported by the SQL Server database engine.
Also note, that the difference in SQL isn't the comma being missing it is the curly braces surrounding parts of your where clause in one and not the other. :)
*Editing this many years later I just learned that PB12.5 does not support SQL Server 2008 and up but supports 2000 & 2005 but my PowerBuilder 2017 supports connection to SQL Server 2008, 2002, 2014, 2016 taken directly from the help files. PB 11.5 supports SQL Server 2008 as long as you use the newer provider. We had a problem where you could only put one retrieval argument, trying to add more gives you invalid syntax on column 2 which I have not seen in my career and have used all versions from PB 3 on up.
You can get more information here:
Microsoft ODBC Programmer's Reference:
http://msdn.microsoft.com/en-us/library/....
And specifically for ODBC SQL Grammar:
http://msdn.microsoft.com/en-us/library/....