[pgsql-jp: 29021] VBA+ODBCでトランザクションを行うには

[Hiroshi Saito]

さいとうです。

脱線したのでsubject:を付け替えました。
表題にそってsampleも直してまた載せました。(関係ない人ごめんなさい)

> 井上です。
>
> Hiroshi Saito wrote:
> >
> > さいとうです。
> >
> > 言われる前にテストサンプルです。(^_^;)
> >
> > > VBA+ODBCでもトランザクションの処理はできます。
> > > いろいろやり方はあるかと思いますが、問題なく使えている方法として
> > > ダイレクトSQLで処理できています。
> > > たとえVBAで待たせていてもきちんとトランザクションとして動作します。
> > > (デッドロックもちゃんとします(^_^;))
>
> ODBCを使用する場合の正しい？トランザクションへの移行処理は
>
>   SQLSetConnectOption(.., SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF);
>
> の発行です。この後のqueryはすべてトランザクション内で実行
> されます。トランザクションを終了するには
>
>   SQLTransact(.., .., SQL_COMMIT/SQL_ROLLBACK);
>
> を発行します。その後のqueryはまた新しいトランザクション内で
> 処理されます。トランザクション内処理を回避するには
>
>   SQLSetConnectOption(.., SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_ON);
>
> を発行する必要があります。
>
> ODBCドライバへのBEGIN/ENDの発行はおすすめできません。
> 私にはBEGINが発行された後AUTOCOMMITモードの取り扱いを
> どうすべきなのかわかりません。今の所、BEGINの発行に
> よってAUTOCOMMITモードを変更はしないので、ドライバが
> 思わぬ所でCOMMITを自動発行する可能性があると思います。

御意。m(__)m
素直に訂正。(^_^;)
このサンプルで問題なく(?)動作しています。
※忙しい井上さんにまた負担をかけてしまいました。

<動作確認>
サーバーは、PowerGres1.0β1(PostgreSQL7.3.1)
ODBC は、psqlodbc07-02-0005
Windows2000
Office97(ごめんなさい古くて)

psqlで確認
※VBAでINSERT後(--ここはトランザクションではない。
saito=> select * from saito;
  xxx
-------
 SAITO
(1 row)
※ここはVBAで待たせています。
saito=> update saito set xxx = '更新したよ' where xxx = 'SAITO';
※ここで対象がロックされているので終わるまで帰ってきません。
UPDATE 0
※ここはVBAトランザクションの終了で結局更新できなかったことを
意味しています。
saito=> select * from saito;
  xxx
--------
 したよ
(1 row)


ここからVBA--------------------------------------------------------------
'
' interfaces　odbc32.dll api
'
Public Const SQL_NTS = -3
Public Const SQL_HANDLE_ENV = 1&
Public Const SQL_HANDLE_DBC = 2&
Public Const SQL_HANDLE_STMT = 3&
Public Const SQL_COMMIT = 0&
Public Const SQL_AUTOCOMMIT = 102&
Public Const SQL_AUTOCOMMIT_OFF = 0&
Public Const SQL_AUTOCOMMIT_ON = 1&

Public Declare Function _
SQLAllocHandle Lib "odbc32.dll" _
    (ByVal handletype As Integer, _
     ByVal inputhandle As Long, _
     ByRef outputhandle As Long) As Integer

Public Declare Function _
SQLAllocEnv Lib "odbc32.dll" _
    (ByRef handle As Long) As Integer

Public Declare Function _
SQLFreeHandle Lib "odbc32.dll" _
    (ByVal handletype As Integer, _
    ByVal handle As Long) As Integer

Public Declare Function _
SQLConnect Lib "odbc32.dll" _
    (ByVal hDBc As Long, _
    ByVal servername As String, _
    ByVal serverlen As Integer, _
    ByVal username As String, _
    ByVal userlen As Integer, _
    ByVal pwd As String, _
    ByVal pwdlen As Integer) As Integer

Public Declare Function _
SQLDisconnect Lib "odbc32.dll" _
    (ByVal hDBc As Long) As Integer

Public Declare Function _
SQLExecDirect Lib "odbc32.dll" _
    (ByVal hstmt As Long, _
    ByVal stmt As String, _
    ByVal stmtlen As Long) As Integer

Public Declare Function _
SQLSetConnectOption Lib "odbc32.dll" _
    (ByVal hDBc As Long, ByVal fOption As Integer, _
    ByVal vParam As Long) As Integer

Public Declare Function _
SQLTransact Lib "odbc32.dll" _
    (ByVal hEnv As Long, ByVal hDBc As Long, _
    ByVal fnType As Integer) As Integer


' 最初にこれでテーブルを作ってね
' create table saito (xxx text primary key);

Private Sub PostgreSQL_Update()

    Dim hEnv As Long
    Dim hDBc As Long
    Dim hstmt As Long
    Dim rc As Integer

    rc = SQLAllocEnv(hEnv)
    rc = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, hDBc)
    ' データソースも忘れずに作ってからですよ。
    rc = SQLConnect(hDBc, "PostgreSQL", SQL_NTS, "saito", SQL_NTS, "",
SQL_NTS)

    'INSERT
    rc = SQLAllocHandle(SQL_HANDLE_STMT, hDBc, hstmt)
    ' AUTO_COMMIT DISABLE
    'rc = SQLSetConnectOption(hDBc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_ON)
    rc = SQLExecDirect(hstmt, _
        "insert into saito values('SAITO');", SQL_NTS)
    rc = SQLFreeHandle(SQL_HANDLE_STMT, hstmt)
    MsgBox "INSERT"
    'UPDATE
    ' ****************************************************************
    rc = SQLSetConnectOption(hDBc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF)
    ' ****************************************************************
    rc = SQLAllocHandle(SQL_HANDLE_STMT, hDBc, hstmt)
   ' rc = SQLExecDirect(hstmt, "BEGIN;", SQL_NTS) <- BAD script
    rc = SQLExecDirect(hstmt, _
        "update saito set xxx = 'したよ' where xxx = 'SAITO';", SQL_NTS)
    MsgBox "トランザクション内更新中です。他で覗いてみてね。"
    'rc = SQLExecDirect(hstmt, "END;", SQL_NTS) <- BAD script
    ' ****************************************************************
    rc = SQLTransact(hEnv, hDBc, SQL_COMMIT)
    rc = SQLSetConnectOption(hDBc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_ON)
    ' ****************************************************************
    MsgBox "トランザクションの外にでました。"
    rc = SQLFreeHandle(SQL_HANDLE_STMT, hstmt)
    rc = SQLDisconnect(hDBc)
    rc = SQLFreeHandle(SQL_HANDLE_DBC, hDBc)
    rc = SQLFreeHandle(SQL_HANDLE_ENV, hEnv)

    ' 通常はきちんとエラー対策しないと、とんでもないことになりますよ

End Sub