MySQL 函数 - 通过 ODBC 返回值
在 MS Access 2007 应用程序中，我需要生成一个文档名称 [Date].[SequenceNumber]，例如 2010-12-07.013，每天 SequenceNumber 都会重置为 1。
为了保持数据完整性，我想创建MySQL 中返回序列号的函数。我设置了一个“序列”表：
CREATE TABLE `doc_sequence_number` (
`SequenceDate` date NOT NULL,
`SequenceNumber` int(11) NOT NULL,
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`SequenceDate`,`SequenceNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
MySQL 函数应该使用服务器的日期和序列号加 1 创建一条新记录，并返回 SequenceDate 和 SequenceNumber 字段。
任何人都可以建议如何编写这样的函数（这是我的第一个 MySQL 函数）。
如何使用 VBA 通过 ODBC 调用此类函数并获取返回值？
由于这对我来说是新的，因此我将不胜感激任何关于如何实现这一目标的建议。 MTIA。
In an MS Access 2007 application, I need to generate a document name of [Date].[SequenceNumber], eg 2010-12-07.013, where each day the SequenceNumber resets to 1.
To maintain data integrity, I'd like to create a function in MySQL that returns the sequence number. I set up a 'sequence' table:
CREATE TABLE `doc_sequence_number` (
`SequenceDate` date NOT NULL,
`SequenceNumber` int(11) NOT NULL,
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`SequenceDate`,`SequenceNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
The MySQL function should create a new record, using the server's Date, and the Sequence Number incremented by 1, and return the SequenceDate and SequenceNumber fields.
Can anyone suggest how such a Function should be written (this is my first MySQL function).
How can I call and get the return value(s) via ODBC of such a function, using VBA?
As this is new to me, I'd appreciate any suggestions on how to accomplish this. MTIA.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我对 MySql 没有太多经验，但如果我在 MSSQL 中进行操作，我会寻找一个具有常规自动递增 ID 的表。然后，该表的记录将被清除，并且序列每天都会重置为 1。然后，我将设置一个存储过程来将记录插入到该临时表中并读回 ID。将其附加到服务器日期上，这就是您的文件名。
至于VBA中的返回值，请查看有关MSSQL的链接，它们应该非常相似
http://bytes.com/topic/access/answers/205120-get-stored-procedure-output-value-back-vba
I don’t have much experience with MySql but if I was doing it in MSSQL I would look to have a table with a regular auto incrementing ID. This table would then have its records cleared and the sequence reset to 1 each day. I would then setup a stored procedure to insert a record into this temp table and read back the ID. Append that onto the servers date and that is your filename.
As for returning values in VBA, have a look at this link about MSSQL, they should be fairly similar
http://bytes.com/topic/access/answers/205120-get-stored-procedure-output-value-back-vba