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指令，就發現數值被重置啦。

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