- PR -

OPENQUERYの文字列

1|2 次のページへ»
投稿者投稿内容
ともこ
大ベテラン
会議室デビュー日: 2007/09/14
投稿数: 111
投稿日時: 2008-01-21 19:50
いつもお世話になります。

SQLサーバー2000のストアドプロシージャに下記のように記述しています。

記述内容-------------------------
DECLARE @SQL NVARCHAR(100)
DECLARE @P1 NVARCHAR(10)
DECLARE @P2 NVARCHAR(10)
SET @P1 = '87'
SET @P2 = '12'

SET @SQL = 'SELECT *
FROM OPENQUERY(AS400,''SELECT COUNT(*) FROM HDATALIB.HSE030P '
+ 'WHERE '
+ 'SH31 = '''
+ '''' + @P1 + ''''
+ 'AND '
+ 'SH32 = '''
+ '''' + @P2 + ''''
+ ''''''
+ ''')'

EXECUTE sp_executesql @SQL
----------------------------
説明させて頂くと、OPENQUERYにて
「SELECT COUNT(*) FROM テーブル WHERE SH31 = '87' AND SH32 = '12'」
を実行したいと考えています。

しかし、どうしても「'」の扱い方がおかしく、SQL実行時にエラーとなります。
どなたかアドバイスを頂けないでしょうか？
よろしくお願いします。
こあら
大ベテラン
会議室デビュー日: 2007/06/26
投稿数: 157
投稿日時: 2008-01-21 21:45
文字列の引用符には「”（ダブルクォート）」
文字列中の引用符には「’（シングルクォート）」として使い分ければ、
文字列中のシングルクォートの入れ子（と言うのかな？）が一つ少なくて済むので、
間違いを探しやすくなると思います。

単純に置換すると、たぶん、こんな感じになって、
OPENQUERY内のSELECT文字列が @P1 の後のシングルクォートで閉じているのがわかります。
コード:
SET @SQL = "SELECT * FROM OPENQUERY(AS400,'SELECT COUNT(*) FROM HDATALIB.HSE030P " 
+ "WHERE " 
+ "SH31 = '" 
+ "'" + @P1 + "'" 
+ "AND " 
+ "SH32 = '" 
+ "'" + @P2 + "'" 
+ "''" 
+ "')"

ともこ
大ベテラン
会議室デビュー日: 2007/09/14
投稿数: 111
投稿日時: 2008-01-23 18:43
こあらさん、いつもアドバイスありがとうございます。
せっかくアドバイス頂いたのに返事が遅くなってすみませんでした。

＞文字列の引用符には「”（ダブルクォート）」
＞文字列中の引用符には「’（シングルクォート）」として使い分ければ
この方法で冷静に見直しを行なったところうまく行くようになりました！！

あと、こういった方法もアリかもしれません。
変数に文字列をセットし、中身を確認するには「print」が便利です！
実際にprintしてみるとどこに「'」が足りないかが一目で分かりました。

SQL文
　SET @SQL = 'SELECT *
　FROM OPENQUERY(AS400,''SELECT COUNT(*) FROM HDATALIB.HSE030P'
　 + ' WHERE '
　 + 'SH31 = '
　 + @P1
　 + ' AND '
　 + 'SH32 = '
　 + @P2
　 + ''')'
　print @sql
表示結果
　SELECT * FROM OPENQUERY(AS400,'SELECT COUNT(*) FROM HDATALIB.HSE030P WHERE SH31 = 87 AND SH32 = 12')

OPENQUERYで文字列を渡す際にパラメータ値の前後に「'」は不要というのはうまくいったものの不思議な感じです。

こあらさん、ありがとうございました。
こあら
大ベテラン
会議室デビュー日: 2007/06/26
投稿数: 157
投稿日時: 2008-01-23 19:08
> OPENQUERYで文字列を渡す際にパラメータ値の前後に「'」は不要というのはうまくいったものの不思議な感じです。

WHEREリテラルのデータ型と、AS400側のSH31,SH32の項目定義のデータ型が一致していない場合、
AS400側で暗黙的に型変換されていると思います。



> DECLARE @P1 NVARCHAR(10)

例えば「A」など数字以外の値をセットするとSELECTで例外が発生しそうです。
対応は、リンクの「sp_executesql ストアド プロシージャを使用する」が良さそうかも？

リンク サーバー クエリに変数を渡す方法
http://support.microsoft.com/kb/314520/ja
ともこ
大ベテラン
会議室デビュー日: 2007/09/14
投稿数: 111
投稿日時: 2008-01-24 19:46
こあらさんの教えて下さったサイトより
＞『sp_executesql ストアド プロシージャを使用する』
＞DECLARE @VAR char(2)
＞SELECT @VAR = 'CA'
＞EXEC MyLinkedServer.master.dbo.sp_executesql
＞ N'SELECT * FROM pubs.dbo.authors WHERE state = @state',
＞ N'@state char(2)',
＞ @VAR
この「CA」や「N」は何を表すのでしょうか？調べてみたのですがいまいちよく分かりません。

前回なんとかいったので引き続き別のSQL文を作成していたらエラーが出てしまいました。
これはこあらさんの言っておられた「例外」と関係するのでしょうか？

SQL文----------------------------------
DECLARE @SQL NVARCHAR(300)
DECLARE @PRODUCT NVARCHAR(300)
DECLARE @PR1 NVARCHAR(10)
DECLARE @PR2 NVARCHAR(10)

SET @PR1 = '43'
SET @PR2 = '01'

SET @SQL = 'SELECT *
FROM OPENQUERY(AS400,''SELECT COUNT(*) FROM HDATALIB.HSE030P'
+ ' WHERE '

SET @PRODUCT = '(SH02 = '
+ @PR1
+ ' AND '
+ 'SH03 = '
+ @PR2
+ ')'
+ ''')'

SET @SQL = @SQL + @PRODUCT
EXECUTE sp_executesql @SQL
-----------------------------------
エラー-----------------------------
OLE DB プロバイダ 'IBMDA400' はエラーをレポートしました。
[OLE/DB provider returned message: SQL0802: データ変換またはデータ・マッピング・エラー。
原因−−エラー・タイプ6が起こりました。
エラー・タイプとその意味は次の通りです。
6 --数値データが無効です。
OLE DB エラー トレース [OLE/DB Provider 'IBMDA400' IRowset::GetNextRows returned 0x80004005: ]。
------------------------------------

こあら
大ベテラン
会議室デビュー日: 2007/06/26
投稿数: 157
投稿日時: 2008-01-24 23:03
> この「CA」や「N」は何を表すのでしょうか？調べてみたのですがいまいちよく分かりません。

「CA」は「CALIFORNIA」だと思います。ってそうじゃ無いですよね。

コード:
DECLARE @VAR char(2) 
SELECT @VAR = 'CA' 
EXEC MyLinkedServer.master.dbo.sp_executesql 
N'SELECT * FROM pubs.dbo.authors WHERE state = @state', 
N'@state char(2)', 
@VAR


素直に読めば、
・項目stateで抽出条件を指定します。プレースフォルダは @state です。
・@state は char(2)です。
・@stateにセットする値は @VAR です。
ということで、
リンクサーバーの著者テーブルから出身州がカリフォルニアの人を検索するクエリ、じゃないですか？

詳しくはMSDNを読んでみて下さい。「N」についても説明があります。
http://msdn2.microsoft.com/ja-jp/library/ms188001.aspx



> エラーが出てしまいました。

これがエラーということは、AS400側は厳密な型指定が必要（暗黙的な型変換をしない）のですね。
おそらく、SH02 か SH03 の項目定義が数値型以外なのではないでしょうか？
この場合は、WHERE句中のパラメータをシングルクォートで括るように編集するか、
サンプルのようにプレースフォルダを使用する必要があります。
ともこ
大ベテラン
会議室デビュー日: 2007/09/14
投稿数: 111
投稿日時: 2008-01-28 09:37
こあらさん、お世話になります。

「N」についてはだいたい理解できました！「CA」も最初SQL独自のものだと思っててカリフォルニアとは思いませんでした（stateも州ではなくてStatement的な意味かと^^;）
AS400にはシングルコーテーション付きのSQLを渡すように試してみます！

私のSQL文では、
　EXECUTE sp_executesql @SQL
とし、@SQLにセットされたSQL文（OPENQUERY）を実行していますが、
例では、
＞EXEC MyLinkedServer.master.dbo.sp_executesql
＞ N'SELECT * FROM pubs.dbo.authors WHERE state = @state',
と、リンクサーバー内のストアド（sp_executesql）を実行しているようです。
リンク元でOPENQUERYを実行するのと、リンク先でsp_executesqlを実行するのとでは何か違いはありますでしょか？（例えば処理速度やサーバーの処理負荷など）

こあら
大ベテラン
会議室デビュー日: 2007/06/26
投稿数: 157
投稿日時: 2008-01-28 12:34
> リンク元でOPENQUERYを実行するのと、リンク先でsp_executesqlを実行するのとでは何か違いはありますでしょか？

上記そのままの違いがあります。


> リンク元でOPENQUERY

OPENQUERYはパススルークエリですからリンク先で処理されます。
リンク元では、リンク先から結果セットを受け取って、そこから結果セットを作ります。


> リンク先でsp_executesql

SQL処理をすべてリンク先へ委託します。
リンク元では、リンク先から結果セットを受け取るだけです。


上記を踏まえると、例えばこんなクエリはよろしくないと言う事が分かります。
コード:

SELECT COUNT(*) FROM OPENQUERY(AS400,'SELECT * FROM HDATALIB.HSE030P')

WHERE SH02 = '43' AND SH03 = '01'


[編集]WHERETをWHEREに修正[/編集]

[ メッセージ編集済み 編集者: こあら 編集日時 2008-01-28 13:55 ]
1|2 次のページへ»

スキルアップ／キャリアアップ（JOB@IT）