使用当前目标连接的动态 SQL 语句返回值
我目前正在 Pervasive 中创建我的第一个现实生活项目。任务是将包含订单（如商店和产品）的特定 XML 结构映射到我自己创建的 3 个表。这些表位于 MS-SQL-Server 实例内。
所有表都有一个名为“id”的唯一键，这是一个自动递增的列。我已从所有映射中删除此列，以便 Pervasive 不会尝试自行填充它。
对于某些计算，对于其中一个表中的拆分键以及对其他表中创建的记录的引用，我将需要数据库刚刚创建的 ID。为此，我用谷歌搜索了答案。我可以使用“选择@@identity;”作为一条语句，这将返回最近为当前连接创建的 id。这意味着在 Pervasive 中，我必须使用已经存在的目标连接对象来执行此语句。
但如何做到这一点呢？我很确定我需要一个 JDImport 或 DJExport 对象，但如何获取与 Pervasive 插入记录的当前连接关联的对象？
或者当我需要引用其他表中的 id 时，是否有其他方法来处理此自动增量？
I'm currently creating my first real life project in Pervasive. The task is to map a certain XML structure containing orders (as in shops and products) to 3 tables I created myself. These tables rest inside a MS-SQL-Server instance.
All of the tables have a unique key called "id", an automatically incremented column. I've dropped this column from all mappings so that Pervasive will not try to fill it itself.
For certain calculations, for a split key in one of the tables and for references to the created records in other tables, I will need the id that the database has just created. For that, I have googled the answer. I can use "select @@identity;" as a statement, and this returns the id that has most recently been created for the current connection. This means that in Pervasive, I will have to execute this statement using the already existing target connection object.
But how to do that? I am quite sure that I will need a JDImport or DJExport object, but how to get one associated with the current connection that Pervasive inserts the records by?
Or is there any other way to handle this auto increment when I need to reference the id in other tables?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
不确定 Pervasive 中的情况如何，但您可能会遇到 @@identity 的问题。 Scope_identity() 可能会更安全，但在 Pervasive 中可能仍然不起作用。
希望您的表除了生成的 id 之外还有一个自然键，在这种情况下您可以根据自然键选择您的 id。这将避免您在不同的会话和范围中可能遇到的任何问题。
Not sure how things work in Pervasive, but you may run into issues with @@identity,. Scope_identity() would probably be safer but may still not work in Pervasive.
Hopefully your tables have a natural key in addition to the generated id, in which case you can select your id based on the natural key. This will avoid any issues you may have with disparate sessions and scope.
如果有人查看这篇文章并想知道答案，那就是“你不能”。 Pervasive 不允许访问他们自己的连接对象，即他们用来查询数据库的连接对象。如果无法访问它，您就无法保证获取正确的 ID。我们的解决方案是这样的：我们使用了一个在 Before-Transformation 事件中调用的存储过程，该过程创建了标头记录并以表的形式返回 id 和可选的错误消息。我们执行它，它返回我们保存并在整个映射过程中使用的 id。
If there is anyone looking this post up and wonders about the answer, it's "You can't". Pervasive does not allow access to their very own connection object, the one they use to query the database. Without access to it, you cannot guaranteed fetch the right id. The solution for us was this: We used a stored procedure which we called in the Before-Transformation event that created the header record and returned the id and an optional error message as a table. We executed it and it returns the id we then save and use throughout our mapping.