質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

.NET Framework

.NET Framework は、Microsoft Windowsのオペレーティングシステムのために開発されたソフトウェア開発環境/実行環境です。多くのプログラミング言語をサポートしています。

Q&A

解決済

1回答

4641閲覧

複数レコードをストアドのパラメータにセットしたい

OKARA

総合スコア63

PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

.NET Framework

.NET Framework は、Microsoft Windowsのオペレーティングシステムのために開発されたソフトウェア開発環境/実行環境です。多くのプログラミング言語をサポートしています。

0グッド

0クリップ

投稿2018/06/18 02:30

編集2018/06/18 06:18

前提・実現したいこと

NPGSQLからpostgresqlに存在するストアドでデータ更新したいと考えています

データ更新の内容はヘッダテーブルと明細テーブルでレコードは1:Nとなり、ヘッダ用のパラメータ(データ件数1件)明細用のパラメータ(データ件数N件)です

ロック発生の可能性を下げるため、トランザクションはC#側で制御したくないためストアドを使いたいと思ってます

発生している問題・エラーメッセージ

上記の実装の仕方がわからず困ってます
・ストアド側のパラメータの型は?
・NPGSQLでパラメータをセットするときのデータ型は?

該当のソースコード

簡易的に下記の内容で試行錯誤してみましたがうまくいきません

lang

1■ストアド定義(定義部のみ) 2CREATE OR REPLACE FUNCTION public.pkg_hoge(ary hoge[]) 3 4■HOGE[]型 5CREATE TYPE hoge AS 6( 7 id bigint, 8 name character varying, 9 trx_date date 10);

lang

1■.NET 2HOGE[] dtPARA = new HOGE[3]; 3 4HOGE DR = new HOGE(); 5DR.ID = 1; 6DR.NAME = "HOGE"; 7DR.TRX_DATE = DateTime.Parse("2018/01/01"); 8dtPARA[0] = DR; 9 10DR = new HOGE(); 11DR.ID = 2; 12DR.NAME = "ORA"; 13DR.TRX_DATE = DateTime.Parse("2018/02/01"); 14dtPARA[1] = DR; 15 16using (NpgsqlConnection CN = new NpgsqlConnection(strCONNECT_INFO)) 17{ 18 CN.Open(); 19 20 using (NpgsqlCommand INST_CMD = new NpgsqlCommand()) 21 { 22 INST_CMD.CommandType = CommandType.StoredProcedure; 23 INST_CMD.CommandText = "PKG_HOGE"; 24 INST_CMD.Connection = CN; 25 26 INST_CMD.Parameters.Add(new NpgsqlParameter("@ARY", NpgsqlDbType.Arry | NpgsqlDbType.Box)); 27 28         /*記入漏れ:追記しました*/ 29         /*再記入漏れ:「Value」への代入を直しました*/ 30 INST_CMD.Parameters["@ARY"].Direction = ParameterDirection.Input; 31 INST_CMD.Parameters["@ARY"].Value = dtPARA; 32         /***********************/ 33 34 var RESULT = INST_CMD.ExecuteScalar();

※上記の「ExecuteScalar」で「Unable to cast data to Rectangle type」のエラーが発生します

補足情報(FW/ツールのバージョンなど)

VS2017 C# .net 4.7
Npgsql 2.2.3
postgresql 10.3, compiled by Visual C++ build 1800, 64-bit

よろしくお願いします

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

OKARA

2018/06/18 02:52

ご指摘ありがとうございました
guest

回答1

0

ベストアンサー

.CommandTextはそもそもパラメータを受け取る記述になっていませんよね
それから.Parameters.Addも型にあったものでは無いと思います。

先ず、ストアド単独で動作させて、確認されて下さい。

SQL単独では以下のような呼び出しになるかと思います。

SQL

1select pkg_hoge(array[(1,'hoge1','2018/06/18'),(2,'hoge2','2018/06/18')]::hoge[]) 2 3--↓unknownでエラーになるかも 4select pkg_hoge('{(1,"hoge1","2018/06/18"),(2,"hoge2","2018/06/18")}')

追記

ロック発生の可能性を下げるため、トランザクションはC#側で制御したくないためストアドを使いたいと思ってます

そもそも、(ネストなどは行なえますが)COMMITやROLLBCAKのトランザクションの制御はストアドではできません。
トランザクションの開始と終了は呼び出し側で行う必要があります。

追記2

Npgsqlでサポートされる型とそのマッピング((Supported Types and their Mappings)

投稿2018/06/18 03:15

編集2018/06/18 07:16
sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

OKARA

2018/06/18 04:34

回答ありがとうございました C#のパラメータセットは記述が漏れてたので追記しました すみません ご教授いただきましたSQLは1つ目はOKでしたが2つはエラーでした >そもそも、(ネストなどは行なえますが)COMMITやROLLBCAKのトランザクションの制御はストアド>ではできません。 >トランザクションの開始と終了は呼び出し側で行う必要があります。 SQLERVERのようなストアド中の「BEGIN TRANSACTION」「COMMIT TRANSACTION」「ROLLBACK TRANSACTION」による制御はPOSTGRESQLではなさそうですが、代わりに「BEGIN~EXCEPTION~END」のブロックで自動的に制御されるとの認識なのですが、違うということでしょうか?
sazi

2018/06/18 04:47 編集

「BEGIN~EXCEPTION~END」はプロシージャの制御文で、トランザクションの制御ではないですね。 伝えたい事は、例えば、ストアド内で1000件ごとにCOMMITするような事はできません。
sazi

2018/06/18 05:17 編集

>C#のパラメータセットは記述が漏れてたので追記しました CUR.VALUEの中身はどのようなものですか? そもそも、NpgsqlDbType.Boxは矩形型で用途には合致していないと思われますが。
OKARA

2018/06/18 06:32

>1000件ごとにCOMMITするような事はできません。 おっしゃる通りですね 今回行いたい処理は下記のような感じで、明細が失敗してもヘッダの処理がコミットされなければOKかなと BEGIN   ヘッダ処理   明細処理 EXCEPTION END >CUR.VALUEの中身はどのようなものですか? すみません。 記載ミスでした .net側で同じレイアウトで設定した配列をValueにセットしてます >NpgsqlDbType.Boxは矩形型で用途には合致していないと思われますが。 最初は「NpgsqlDbType.Array」だけを指定していたのですが、下記のエラーが発生したのでBoxを追加した次第です 「Cannot set NpgsqlDbType to just Array, Binary-Or with the element type (e.g. Array of Box is NpgsqlDbType.Array | NpgsqlDbType.Box).パラメーター名:value」 SQLSERVERはそこそこ使った経験があるのですが、Postgresqlは今回初めてで。。。 お手数かけてすみません
sazi

2018/06/18 07:42 編集

>今回行いたい処理は下記のような感じで、明細が失敗してもヘッダの処理がコミットされなければOKかなと ストアドから結果を返し、C#側でCOMMITまたはROLLBACKの判断が必要です。 AutoCommitに制御を任せる状態だと、オペレーションしているユーザには伝わりません。 >下記のエラーが発生したのでBoxを追加 e.g.は「例えば」という意味です。 「配列を指定するなら、配列内の型を示しなさい」というエラーです。 今回はtype定義してるのでその型ということになりますが、ユーザー定義のtypeの指定ができるのかどうか分かりません。 多分、recordでの指定(.net側ではobject[])ではないかと思います。 回答にマッピングのリファレンスへのリンクを追記しておきます。
sazi

2018/06/18 07:23

手っ取り早くは、パラメータを独立した配列にして、 pkg_hoge(id bigint[], name character varying[], trx_date date[]) とすれば、それぞれでNpgsqlDbType.が使えるとは思います。
OKARA

2018/06/25 09:07

返事が遅くなりすみませんでした 本文に補足を入れましたが、結論として、配列やデータ型をパラメータに指定するのはあきらめて、XML型をパラメータにしました ストアドでZZ_HOGE_LへのINSERTが失敗した場合は、ZZ_HOGE_HへのINSERT結果は破棄されることも確認できました この方法で実装していこうと思います 回答いただきありがとうございました
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問