2017年11月23日 星期四

SQL 2000 的分頁

在 Laravel 中,雖然有方便的分頁功能,但必須是 SQL 2005 以上。使用 SQL 2000,就只好自行想辦法了。

下面是參考網路找來的作法,參見 Efficient Paging (Limit) Query in SQLServer 2000?

-----------------
CREATE PROCEDURE [dbo].[GetPage]
    @pTableName VARCHAR(30),
    @pColumns VARCHAR(200) = '*',
    @pFilter VARCHAR(200) = '',
    @pSort VARCHAR(200) = '',
    @pPage INT = 1,
    @pPageRows INT = 10
    AS

    SET NOCOUNT ON
    DECLARE @vSQL VARCHAR(4000)
    DECLARE @vTempTable VARCHAR(30)
    DECLARE @vRowStart INT
    DECLARE @vTotalRows INT

    SET @vTempTable = '##Tmp' + CAST(DATEPART(YYYY, GETDATE()) AS VARCHAR(4)) +
    CAST(DATEPART(MM, GETDATE()) AS VARCHAR(2)) +
    CAST(DATEPART(DD, GETDATE()) AS VARCHAR(2)) +
    CAST(DATEPART(HH, GETDATE()) AS VARCHAR(2)) +
    CAST(DATEPART(MI, GETDATE()) AS VARCHAR(2)) +
    CAST(DATEPART(SS, GETDATE()) AS VARCHAR(2)) +
    CAST(DATEPART(MS, GETDATE()) AS VARCHAR(3))

    SET @vSQL = 'SELECT ' + @pColumns + ', IDENTITY(INT, 1, 1) AS ROWID INTO ' + @vTempTable + ' FROM ' + @pTableName

    IF @pFilter != '' AND @pFilter IS NOT NULL
    SET @vSQL = @vSQL + ' WHERE ' + @pFilter

    IF @pSort != '' AND @pSort IS NOT NULL
    SET @vSQL = @vSQL + ' ORDER BY ' + @pSort

    EXECUTE (@vSQL)

    -- Get the total number of rows selected
    SET @vTotalRows = @@ROWCOUNT

    -- If page number = 0, set it to the first page
    IF @pPage = 0
    SET @pPage = 1

    -- If page number is beyond the last page, set page to the last page
    IF (@pPage * @pPageRows) > @vTotalRows
    BEGIN
    SET @pPage = @vTotalRows / @pPageRows
    IF (@vTotalRows % @pPageRows) != 0
    SET @pPage = @pPage + 1
    END

    SET @vRowStart = ((@pPage - 1) * @pPageRows) + 1
    SET @vSQL = 'SELECT * FROM ' + @vTempTable + ' WHERE ROWID BETWEEN ' + CAST(@vRowStart AS VARCHAR(10)) +
    ' AND ' + CAST((@vRowStart + @pPageRows - 1) AS VARCHAR(10)) + ' ORDER BY ROWID'
    EXECUTE (@vSQL)

    SET @vSQL = 'DROP TABLE ' + @vTempTable
    EXECUTE (@vSQL)

GO
------------------

使用
exec getpage 'ifcrfct', '*', 'ct_cono like ''9%''', 'CT_YYSE desc', 100, 10

一些變數的值
@vTempTable: ##Tmp20171124141845213
@vSQL: SELECT *, IDENTITY(INT, 1, 1) AS ROWID INTO ##Tmp20171124142153370 FROM ifcrfct WHERE ct_cono like '9%' ORDER BY CT_YYSE desc
@vSQL: SELECT * FROM ##Tmp20171124142424353 WHERE ROWID BETWEEN 1741 AND 1750 ORDER BY ROWID


方便就好,先頂著用

一事不煩二主,再加上幾行,會在第二個 result set 傳回總筆數和總頁數
----------------------
DECLARE @vTotalPages INT
SET @vTotalPages = @vTotalRows / @pPageRows
IF (@vTotalRows % @pPageRows) != 0
    SET @vTotalPages = @vTotalPages + 1
SELECT @vTotalPages AS TotalPages
-----------------------

主要的花費,是在建立 temp table。
---------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[tmp_test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   drop table [tmp_test]
GO

select *, identity(int, 1, 1) as rowid into tmp_test from caucgi.tsel
--------------------------
44 萬筆,第一次 10 秒,第二次以後,約 5 秒。

2017年11月16日 星期四

Google Static Maps 的使用

Google Static Maps API 可用來將「Google 地圖」影像內嵌在網頁中,而不需要使用 JavaScript 或任何動態頁面載入功能。

原本,這個是可以不用加 app key,但於 2017 年底, Google 更改策略,必須申請 app_key,但每天使用量不超過2萬5千,是免費的,超過則必須付費。

範例如下
http://maps.googleapis.com/maps/api/staticmap?center=25.015547,121.538156&zoom=16&size=400x300&sensor=false&markers=color:red|label:C|25.015547,121.538156&key=App_key


要使用 Google map 的服務,必須先 [取得金鑰]。在啟用之後,可以 [查詢現有金鑰],點選其中的 [資訊主頁} 可以看到使用的情形。



2017年11月10日 星期五

使用 json 記錄SQL操作


參考 1: Example SQL Server TSQL Stored Procedure to produce JSON
建立 stored procedure SerializeJSON,使用方式
EXEC [dbo].[SerializeJSON] "SELECT * FROM myTable;";


參考 2: StackOverflow 的討論 Log record changes in SQL server in an audit table
 其中一則回答,提到 Pop Rivett 所提的作法

一個 Audit TABLE 應包括下列欄位
               Type CHAR(1),   // SQL 操作: U, I, D
               TableName VARCHAR(128),
               PK VARCHAR(1000),  // 不用了
               FieldName VARCHAR(128),  // 不用了
               OldValue VARCHAR(1000),  // 以 JSON 表示
               NewValue VARCHAR(1000), // 以 JSON 表示
               UpdateDate datetime,
               UserName VARCHAR(128))


Maintaining a Log of Database Changes,比較設定 trigger 或程式完成的優缺點。仔細考慮,設定 trigger 是比較省事。

網誌存檔