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

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

ただいまの
回答率

89.99%

C#.NETでPostgreSQLのストアドOUTパラメータを受け取りたい

解決済

回答 4

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 3,581

dotnet

score 18

前提・実現したいこと

WindowsフォームアプリケーションよりNpgsql経由でDBへアクセスする際、あらかじめ作ったストアドのOUTパラメータを受け取ることができません(コマンドプロンプトで実行するとうまく結果を受け取れます)。

フォーム側でSQL直打ちすれば結果は受け取れるのですが、使えるのであればストアドにしたいと思っています。C#.NET、PostgreSQLどちらも日が浅く、根本的な勘違い(無知)かもしれませんが、ご教示ください。

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

Field not found in row:username

該当のソースコード

作成したストアド
create or replace function db_login(in varchar, in varchar, out varchar, out varchar) as
'select username, userstatus from 表名 where userid = $1 and password = $2' language 'sql';

上記を実行
select db_login('hoge','fuga');
db_login
-----------
("ほげさん", active)
(1行)

フォーム側
Using (NpgsqlConnection con = new NpgsqlConnection(接続文字列))
{
 using (NpgsqlCommand cmd = new NpgsqlCommand("",con))
 {
   useridとpasswordをNpgsqlParameterへ格納

  // これだとうまくいきます
  cmd.commandText="select username, userstatus from 表名 where userid = :userid and password = :password";

  // これがダメ
  cmd.commandText="select db_login(:userid, :password)";

  以下、NpgsqlDataReaderで値の受け取り部分で「Field not found in row:username」

 }
}

試したこと

NpgsqlParameterにOUTパラメーターを作ろうとした
→ParameterDirectionがNpgsqlではなくSystem.Dataにしか表示されない

CommandTypeをStoredProcedureにした
→cmd.ExecuteNonQuery()で{42601:syntax error at or near \"select\""}

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

PostgreSQL(Windows 32bit 9.6.3)

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

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • hihijiji

    2017/09/02 19:22

    Npgsqlは使ってないのでヒントだけです。 useridとpasswordは接続文字列に、NpgsqlParameterはストアドに渡すパラメータ、CommandTypeをStoredProcedureにした→正解、new NpgsqlCommand("",con)→new NpgsqlCommand("[ストアド名]",con)

    キャンセル

  • dotnet

    2017/09/02 19:51

    hihijiji さま お世話になります。useridとpasswordはDBユーザーではなく、ユーザー表を作って管理しているuseridとpasswordで、ストアドのパラメーター(input)にあたります。 CommandType=StoredProcedureにしてCommandText="db_login()"; ですと 42601: syntax error at or near "(" となり、CommandText="db_login(:p_userid,:p_password)"; ですと 42601: syntaxerror at or near ":" となりました。

    キャンセル

回答 4

checkベストアンサー

+1

 cmd.commandText="select db_login(:userid, :password)";


上記だと戻りは'(xxx,xxx)'のように値の列挙で返却されるだけです。
db_login()のOUTパラメータには名称が無いので、名称で参照されるなら名称を付ける必要があります。
※Npgsqlで$1,$2のように順序で取得する方法があるかは分かりません。
ストアドを変更しない場合は以下のようになります。

 cmd.commandText="select * from db_login(:userid, :password) as w(username, userstatus)";


※*で取得できないようであれば、カラム名でselectしてみて下さい。

ただ、今後の拡張があれば別ですが、それが無いならこんなに単純な処理をわざわざストアドにしなくても良い気がしますが。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/09/04 00:26 編集

    sazi さま
    お世話になります。休日で実機が手元になく、自宅の適当なPCで環境を作っていたので返信が遅くなり申し訳ありませんでした。

    cmd.commandText="select * from db_login(:userid, :password) as w(username, userstatus)";

    有難うございます。これで解決しました!

    psqlで実行するときは select db_login(:p_userid,:p_password); で欲しいものが返ってきていたので、そこで思考停止してしまっていました。少しだけOracleを触っていたので、fromの無いselectとか、今回のストアドとか、色々と衝撃を受けているところです。

    なんとかC#からストアドを呼ぶ方法を習得したいのですが、元々のコードでsyntax errorになったのはなぜかご教示頂けると幸いです。宜しくお願い致します。

    キャンセル

  • 2017/09/04 00:54

    Npgsqlには詳しくないし、エラーになった元のコードが無いとなんとも。
    ネットで調べてみましたが、色々な呼び出し方ができそうですし、新たな質問として有識者にお願いした方が良さそうです。

    キャンセル

  • 2017/09/04 17:02

    sazi さま
    コメント有難うございました。ご指摘の通りこれ自体はストアドである必要無いのですが、今後の取っかかりとして質問してみました。大変勉強になりました!

    キャンセル

+1

PostgreSQL / Npgsql には触ったこともないのでハズレかもしれませんが、SQL Server / SqlClient と似たようなものだと想像してレスします。

あまり適当な例ではないかもしれませんが、以下の記事の「ストアドプロシージャ 」のコードと「aspx ファイル」の CreateDataSource メソッドを見てください。 

カスタムページャー
http://surferonwww.info/BlogEngine/post/2010/08/25/Custom-pager.aspx

ストアドは引数で pageNum と pageSize を入力とし、pageCount を OUTPUT に設定しています。

ストアドの中では、入力の pageNum と pageSize を条件に使って、SELECT * ... FROM [Products] ... というクエリで Products テーブルからデータを抽出しています。

さらに、SET @pageCount = CEILING(@rowCount / @pageSize) でページ番号を計算して OUTPUT として渡しています。質問者さんのコードにはこれと同等なコードがないです。

ADO.NET の C# のコードでは pageNum, pageSize, pageCount はパラメータとして設定します。質問者さんのコードにはパラメータの設定がないです。

sqlCom.Parameters.AddWithValue("@pageNum", pageNum);
sqlCom.Parameters.AddWithValue("@pageSize", pageSize);
SqlParameter param = new SqlParameter("@pageCount", SqlDbType.Int);
param.Direction = ParameterDirection.Output;
sqlCom.Parameters.Add(param);

ストアドで OUTPUT として設定した pageCount は、ストアド実行後、以下のようにして取得できます。

pn1.Count = (int)param.Value;
pn2.Count = (int)param.Value;

そのあたりの方法は、たぶん PostgreSQL / Npgsql でも同じだと思います。

#質問者さんは全くの初学者だとお見受けしますが、であれば、サンプルがはるかに多い SQL Server / SqlClient で ADO.NET の勉強を先にして、ある程度基本的な知識をつけるべきと思うのですが。質問にアップされていたストアドの書き方、ADO.NET + Npgsql を使った C# のコードの書き方がメチャクチャなような気がします。(SQL Server であれば問題外です)
 

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/09/05 00:17

    コメントありがとうございます。
    質問者様がコメントで質問されているから、続ける気が無いわけではないと思いますが、そのコメントは解決済みにされた後でした。
    私が回答できればいいのですが、何分力不足で。
    記事としての価値を高めるなら、別途質問してこの質問に結果をフィードバックしてもらう位しか思いつかないですけど。

    キャンセル

  • 2017/09/05 13:19

    SurferOnWww さま、sazi さま
    お世話になります。先ほどまで端末を触れず亀レスで恐縮です。それぞれご回答いただいただけでなく、論議を巻き起こしてしまったようで申し訳ありません。その後の顛末を回答に記載しました。今後ともご指導よろしくお願いします。

    キャンセル

  • 2017/09/06 20:06

    あなたの言う「論議を巻き起こし」というのはどういう意味ですか?

    キャンセル

+1

自分がfunctionの仕様を理解しきれていなかったことによる自爆で(language→sql、plpgsql)、Oracleと同じように書いたらすんなり期待通りの結果が得られました。長くなり恐縮ですが、省略せずに試したことを記載します。

以下、自己検証です。全言語において独習者ですので「おいおい」といった箇所があれば是非教えてください。

--テスト用テーブル
create table test_table(
    userid varchar(20),
    password varchar(20),
    usertext varchar(20),
    status varchar(20),
    constraint pk_test_table primary key(userid));

--ダミーデータ
insert into test_table(userid,password,usertext,status)
values('hoge','fuga','hogeさん','active');

--ストアド
create or replace function testFunction(in p_userid varchar,in p_password varchar,out v_usertext varchar,out v_status varchar) as $$
begin
    select usertext,status into v_usertext,v_status from test_table where userid=p_userid and password=p_password;
end; $$ language plpgsql;

//C#側 ユーザー名:textBox1.text パスワード:textBox2.text 処理開始:button1
private void button1_Click(object sender, EventArgs e)
{
    using (NpgsqlConnection con = new NpgsqlConnection("接続文字列"))
        {
            using (NpgsqlCommand cmd = new NpgsqlCommand("testFunction",con))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                NpgsqlParameter p_userid = 
                cmd.Parameters.Add("p_userid", NpgsqlDbType.Varchar);
                p_userid.Direction = ParameterDirection.Input;
                p_userid.Value = this.textBox1.Text;

                NpgsqlParameter p_passwod = 
                cmd.Parameters.Add("p_password", NpgsqlDbType.Varchar);
                p_passwod.Direction = ParameterDirection.Input;
                p_passwod.Value = this.textBox2.Text;

                NpgsqlParameter v_usertext = 
                cmd.Parameters.Add("v_usertext", NpgsqlDbType.Varchar);
                v_usertext.Direction = ParameterDirection.Output;

                NpgsqlParameter v_status = 
                cmd.Parameters.Add("v_status", NpgsqlDbType.Varchar);
                v_status.Direction = ParameterDirection.Output;

                try
                {
                    con.Open();
                    cmd.ExecuteNonQuery();
                }
                catch(NpgsqlException ex)
                {
                    MessageBox.Show(ex.Message.ToString());
                    return;
                }
                MessageBox.Show(
            "ユーザー名:" + v_usertext.Value.ToString() + "\r\n" +
            "ステータス:" + v_status.Value.ToString());
        }
    }
}

引数 textBox1.text hoge, textBox2.text fuga

実行結果
ユーザー名:hogeさん
ステータス:active

ベストアンサーについては
(1)ベストアンサー選択=解決済みであることを知らずにクリックしてしまった
(2)当初の疑問が解決されたという認識はありませんでしたが、language 'sql' の場合はこう使えばよかったのか!への謝意

このようにご理解いただければ幸いです。ご回答・コメントくださった皆々さま、本当にありがとうございました。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

0

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

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

  • ただいまの回答率 89.99%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる