access2010 SQL Azureいってみようか -11-

データ転送に掛るコストってどれぐらいになるのだろうと考えるとモヤモヤする。

テーブルの種類用途によってはリンクテーブルではなくてもよいのではないか。多分ローカルテーブルを使ってやりくりは転送料にかかわらずでてくるだろうから、任意のタイミングでSQL Azureとローカルのテーブル同期ができればよいということもあるだろう。

 この二つのテーブルを同期させたい。といっても一方通行だけど。

まずはSQL Azureの作業

CREATE TABLE dbo.table01
(
    ID int IDENTITY, 
    F01 nvarchar(20) COLLATE Japanese_CI_AS NOT NULL, 
    TS timestamp, 
    CONSTRAINT PK_table01 PRIMARY KEY CLUSTERED (ID ASC)
)
GO
INSERT INTO dbo.table01 (F01) VALUES (N'テスト01')
GO
INSERT INTO dbo.table01 (F01) VALUES (N'テスト02')
GO
INSERT INTO dbo.table01 (F01) VALUES (N'テスト03')
GO
INSERT INTO dbo.table01 (F01) VALUES (N'テスト04')
GO
INSERT INTO dbo.table01 (F01) VALUES (N'テスト05')
GO

rowversion (Transact-SQL) timestampで指定しているけれどもrowversionのこと。

Access上のローカルテーブルを作る。バイナリ型はテーブルデザインで選択できないからDDLで。

ALTER TABLE Local_table01 ADD COLUMN TS binary(8)

リンクテーブルを開いたところ。特に問題なし。TSはバイナリ型(8)になるから表示されてないだけ。

で、同期させるコード。

Option Compare Database
Option Explicit

Sub SQLAzureToLocal()
    Dim dbs As DAO.Database, qdf As DAO.QueryDef
    Dim strTS As String, rsTo As DAO.Recordset, rsFrom As DAO.Recordset
    
    strTS = TimestampToString(DMax("TS", "Local_table01"))
    Set dbs = CurrentDb
    Set qdf = dbs.CreateQueryDef("")
    qdf.Connect = strCn
    qdf.SQL = "select ID, F01, TS from dbo.table01 where TS > " & strTS
    qdf.ReturnsRecords = True
    Set rsFrom = qdf.OpenRecordset
    Set rsTo = dbs.OpenRecordset("Local_table01", dbOpenTable)
    rsTo.Index = "PrimaryKey"
    Do Until rsFrom.EOF
        rsTo.Seek "=", rsFrom("ID")
        If rsTo.NoMatch Then
            rsTo.AddNew
                rsTo("ID") = rsFrom("ID")
                rsTo("F01") = rsFrom("F01")
                rsTo("TS") = rsFrom("TS")
            rsTo.Update
        Else
            rsTo.Edit
                rsTo("F01") = rsFrom("F01")
                rsTo("TS") = rsFrom("TS")
            rsTo.Update
        End If
        rsFrom.MoveNext
    Loop
End Sub

Function TimestampToString(RsCol) As String
    If IsNull(RsCol) Then TimestampToString = "0x0000000000000000": Exit Function
    Dim Buffer As String, i As Integer
    Buffer = "0x"
    For i = 1 To 8
        Buffer = Buffer & Right("00" & Hex(AscB(MidB(RsCol, i, 1))), 2)
    Next i
    TimestampToString = Buffer
End Function

timestamp列はInsert/Update時カウントアップで更新されていくから、ローカルテーブル上の最大値より大きいtimestampを持つレコードだけパススルークエリで取得し、ローカルにあるレコードは更新、なければ追加てな感じに。