Translate

ebook【Delphi跨平台資料庫程式設計火速上手】電子書出版 (CHT)

Delphi 跨平台資料庫程式設計火速上手,是本關於整合 Delphi 的跨平台技術打造 2-Tier 架構的跨平台 APP 的入門技術書。 全書沒有需要理解的技術知識,只講套路。 力求短時間把製作 APP 的工法熟悉,未來要開發其它的應用程式也能舉一反三。 底下...

2010/11/26

Firebird Select + 流水號 = 非常不實用

先節錄官網上的資料
How to get record number as part of dataset?


Sometimes you need to feed the SQL statement to some 3rd party tool or component and you need it to show the number of each row. While this can easily be done via temporary variable in a stored procedure or using EXECUTE BLOCK, sometimes you need it to be a single SELECT statement.

Firebird context variables can do the job for you here. Here's an interesting way to do it contributed by Fabiano Bonin. This example is showing all the tables and views in the database.

Example for Dialect 3 databases:

SELECT
rdb$get_context('USER_TRANSACTION', 'row#') as row_number,
rdb$set_context('USER_TRANSACTION', 'row#', 
coalesce(cast(rdb$get_context('USER_TRANSACTION', 'row#') as integer), 0) + 1),
a.rdb$relation_name
FROM rdb$relations a
ORDER BY a.rdb$relation_name

Example for Dialect 1 databases:

SELECT
rdb$get_context('USER_TRANSACTION', 'row#') as row_number,
rdb$set_context('USER_TRANSACTION', 'row#',
Coalesce(rdb$get_context('USER_TRANSACTION','row#'),0) + 1),
a.rdb$relation_name
FROM rdb$relations a
ORDER BY a.rdb$relation_name

Dialect 1 example was contributed by Serge Girard of developpez.net.

BCB6的DRIVER只支援Dialect 1,而且實際使用的時候,該欄位會帶出一票小數點
而且,程式必須重開,流水號才會重置,實在是很不實用的技巧……

程式如果要不重開,又要重置,在這邊提供一個小方法:多開一個SQLConnection,如此,再度執行上述的SQL指令,就發現數值被重置啦。

但小數點的問題依舊是無解,如果有人發現解決的方法,請記得分享一下唷。

沒有留言:

張貼留言