パソコン情報

【Sqlserver】ストアド実行し戻り値をAccessで取得する(データベース複製実験1)

このブログ記事は、SqlServerにてデータベースをODBC接続してAccessを端末として利用している環境で、

注意ポイント

データベースをコピー(複製)した時に、Accessからストアドを実行しても動かないんじゃないか!?

という疑問から始まった実験です。

今回は、その実験の最初の段階である実験データベースを準備する時に、ストアドからの戻り値をどうやってAccess側で受け取るのか?を備忘録にしてみました。

実験用データベースの準備

SqlServerの方に実験用データベース「DB1」というのを作成して、テーブル「dbo.data」というのを作成しました。

テーブル内は、適当なサンプルデータを入力しておきました。

これから何の実験をするのか?は下記の項目で説明します。




ODBC接続設定の準備

ODBCの接続設定は、MSSMSから新規ログインユーザーを追加しました。

データベースを指定するために、ユーザーマッピングにて、DB1を指定しました。

次にAccessを使うパソコン(Windows10)にて、ODBCの設定をしました。

ODBCにより、データベースを選択して切り分けができるんじゃないかと考えています。
データベースによって接続できるユーザーを分けたいので今回もDB1には、ODBC1での接続、DB2には、ODBC2での接続を準備して実験を進めていく予定です。

ストアドの準備

この記事を書く前に、散々実験をして悩んだ末のストアドコードになります。

本当にこのコードでいいのか?は全く自信がありませんが、動いたので良しとしています。

ストアド名は、「test_calc」にしました。

USE [DB1]
GO
/****** Object:  StoredProcedure [dbo].[test_calc]    Script Date: 2020/08/14 11:13:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[test_calc]
@input_no int,
@ans VARCHAR(50) OUTPUT
AS
BEGIN TRY

--DECLARE @ans VARCHAR(50) 
 
    BEGIN TRANSACTION
  
UPDATE dbo.[data]
SET dbo.[data].kazu = dbo.[data].kazu+1
WHERE dbo.[data].no = @input_no
 

SELECT @ans=dbo.[data].answer
FROM dbo.data
WHERE dbo.[data].no = @input_no

--RETURN @ans
 
    COMMIT TRANSACTION
  
END TRY
  
BEGIN CATCH
    ROLLBACK TRANSACTION
  
    RETURN ERROR_NUMBER()
  
END CATCH
  
RETURN 0
--RETURN @ans;

上記のコードでは、正常終了したら、0が戻り値になるのと、@ansという変数が返されるようにしました。

RETURN 0,@ans;

とかにするとエラーになるので、他に書き方があるのかは知りません。

(追記)RETURN @ans;は無くてもAccess端末で戻り値として受け取っていることがわかりましたので、コメントアウトしました。

Accessでの端末側の準備

SqlServerのdbo.dataを、Accessのリンクテーブルにして、帳票フォームにて作成しました。

やりたい事メモ

処理」ボタンをクリックすることで、「みかん、パイナップル、リンゴ」の数が1加算されるような仕組みです。

もちろん、AccessのVBAの方では、ストアドを実行させることを目的として、Accessの方でストアドが動いたのかを確認するための物です。

下記にAccessのVBAコードを記載しておきます。

Private Sub job_btn_Click()

On Error GoTo ERR_HANDLER
  Dim strSQL As String
  Dim i As Long
  
  'オブジェクトの作成
  Dim cn As New ADODB.Connection
 
  '接続文字列の設定
  cn.ConnectionString = "DSN=ODBC1;Uid=user1;Pwd=japan2020$$;Database=DB1"
    
  '接続
  cn.Open
 
  'ストアドプロシージャの実行情報を設定
  Dim cmd As New ADODB.Command
  cmd.ActiveConnection = cn
  cmd.CommandType = adCmdStoredProc 'コマンドの種類
  cmd.CommandText = "test_calc" 'ストアドプロシージャ名
  
  'ストアドプロシージャへの引数を設定
  '入力引数
  cmd.Parameters("@input_no").Value = Forms![dbo_data]![input_no]
  
  'ストアドプロシージャの実行
  cmd.Execute
  
  '実行結果の表示
  
  Dim anser As String
  
  anser = cmd.Parameters("@ans").Value
   
   Debug.Print anser
   
   Debug.Print cmd.Parameters(0).Value
   
'
'                       戻り値の判断& 表示用メッセージ生成
                If cmd.Parameters(0).Value = 0 Then
                    MsgBox "出荷予定数の計算は正常終了しました。"
                Else
                    MsgBox "異常終了しました" & vbCrLf
                End If

  
  'オブジェクトのクローズ
  cn.Close
        
  'オブジェクトの開放
  Set prm = Nothing
  Set cmd = Nothing
  Set cn = Nothing
  
Exit Sub

    
ERR_HANDLER:
  'エラーメッセージ
  MsgBox Err.Number & ")" & Err.Description
 

  'オブジェクトの開放
  Set prm = Nothing
  Set cmd = Nothing
  Set cn = Nothing

End Sub

実験用なので、ODBC接続設定もそのままを載せてあります。

ODBC用情報としては、

  • ユーザー名 user1
  • パスワード japan2020$$
  • ODBC接続名 ODBC1
  • データベース名 DB1

以前のストアド実行時には、サーバー名も必要でしたが、Access端末のパソコン(Windows10)のODBC接続設定にサーバー名が入っているので、必要ありませんでした。

まだSqlServerにおいては初心者なので、理解できませんが、もしかしたらVBAにて、サーバー名を指定してストアドを動かす時にはODBCの設定っていらないのかな?その辺はやってみないとわかりません。

実験した事とポイント

この回では、AccessのVBAからストアドを実行して、Access側に戻り値を表示させるというのが目的でした。

参考

Accessの方で、処理ボタンをクリックすることで、(「みかん、パイナップル、リンゴ」のレコードから選択する)ストアドが実行され、そのレコードのkazuというフィールド値に、1が加算させれて、問題なければ、0と@ansという2つの内容が戻り値になるという流れです。

 

@ansの変数に関しては、宣言時に、出力なので、OUTPUTを付けてみました。

へプル等を見ていたら、OUTPUTを付けると、プロシージャの終了時にその値が呼び出し元のプログラムに返されるらしいが、使い方があっているのか?どうかは不明。結果良しとしている。
(追記)OUTPUTが無いとAccess側に戻り値として帰ってこない事がわかりました。

結果がストアドから戻り問題なければ、VBAの方で、メッセージ「"出荷予定数の計算は正常終了しました。"」が出るということで、ちゃんとストアドが動いていることがAccess上でも確認することができました。

@ansの内容に関しては、debug.printにてイミディエイトウインドウで確認しました。メッセージと一緒に表示させてもいいですね。

MsgBox anser & "出荷予定数の計算は正常終了しました。"

 

また、フォームを更新すれば、数が1加算されているかどうかでストアドの計算もされているかどうかも確認できました。

 

それから、

Debug.Print cmd.Parameters(0).Value

のパラメーターを呼び出すコードですが、現状では、どのレコードを選択しても、0が表示されます。

(1)にすると、レコードのno部分、(2)にすると、anser部分(みかん、パイナップル、リンゴ)(3)にするとエラーになります。

今回は、RETURN @ansで戻していますが、パラメーターで戻す方法もありそうです。

 

この辺の解説本も見つからないし、ネットを探し回っても理解できる内容がありませんでした。

自分が時間をかけて実験した内容なので記述的には正解なのか?どうかもわかりません。

この実験は、端末でAccessなどを使うような場合ですが、WEBで作業する段階になると必要ないことかもしれませんね。



https://pctips.jp/server/stored-detabase-2/