ADOでParameterを指定するには？
解決環境はVisual Basic6です。
ADOでSQL文を実行したいのですが、
パラメータで値を渡そうとするときは「?」で
渡すと順番が下記ソースのように固定されてしまうので
m_AdoComm(col1) = "1"
といったように項目名と対応させたいのですが
そういったときは、cmd.CreateParameter("col1", adVarChar, adParamInput, 10)
のようにDBの型や長さの指定が必要なのでしょうか、
長さや型を指定しない方法はありませんでしょうか。
よろしくお願い致します。
Dim m_AdoCn As ADODB.Connection
Dim m_AdoComm As ADODB.Command
Set m_AdoCn = New ADODB.Connection
m_AdoCn.Open ("DSN=test;")
Set m_AdoComm = New ADODB.Command
m_AdoComm.ActiveConnection = m_AdoCn
Dim strSQL As String
strSQL = "SELECT * FROM test1 where col1 = ?"
m_AdoComm.CommandText = strSQL
m_AdoComm.CommandType = 1
m_AdoComm(0) = "1"
http://www.accessclub.jp/ado/createparameter.html
以上。参考まで
レスありがとうとざいます。
参考にさせていただいたサンプルで
Set param = cmd.CreateParameter("性別", adVarChar, adParamInput, 10)
で、adVarCharと10を省略したいのですが、
方法がありましたら、よろしくお願い致します。
> m_AdoComm.CommandType = 1
マジックナンバーの使用は避けましょう。
この場合は、adCmdText 定数を代入するべきです。
> 長さや型を指定しない方法はありませんでしょうか。
使用する Provider によって、コマンドにてパラメータ名を定義できるものとできないものがあります。
(そもそも、パラメータをサポートしていないプロバイダも少なくありません)
たとえば、Oracle であれば WHERE col1 = :varName の構文になりますし、
JET であれば PARAMETERS `varName` Text; の構文となりますし、
ODBC では、出現順のみに依存した WHERE col1 = ? の構文を使うことになります。
そこで ADO では、Parameter オブジェクトを通じて、それらに
パラメータ名や型を明示的に与えることができるようになっています。
> Set param = cmd.CreateParameter("性別", adVarChar, adParamInput, 10)
> で、adVarCharと10を省略したいのですが、
コマンド側で変数名と型を指定できるプロバイダの場合においては、
.Parameters.Refresh を呼び出すことで、パラメータ情報を取得できます。
ただし、Refresh メソッドを呼び出す前に、Command オブジェクトの
ActiveConnection/CommandText/CommandType プロパティを、それぞれ
正しく設定しておく必要があります。
たとえば SQL Server の場合、ストアド引数の名前と型を定義できますが、
その場合、この Refresh メソッドを用いることで、CreateParameter せずに
Parameter オブジェクトを利用できるようになっています。
ただし、これは開発時には便利ですが、完成したアプリケーション内で
Refresh を呼び出すのは、下記の 2 点から好ましく無いとの意見もあります。
(1) Refresh 動作を提供しない(もしくは情報提供が不完全な)プロバイダも存在するため、移植性が損なわれる。
(2) 多くのプロバイダでは、Refresh 動作の最適化は行われていないため、
CreateParameter を用いて明示的に情報を与えた方が、動作効率が良い。
ただし、
cmd.CommandText = SQL
Set cmd.ActiveConnection = con
Set rs = cmd.Execute( , "男性") '★
Set cmd.ActiveConnection = Nothing
Do Until rs.EOF
Debug.Print rs.Collect(0)
rs.MoveNext
Loop
のように、Execute メソッドの第二引数にパラメータの値を指定する方法では、
パラメータ情報の提供をプロバイダに求めることにはならないので、
こうしたパフォーマンス上／移植性の問題は生じません。
(複数のパラメータを指定する場合は、配列として渡します)
そのかわり、この方法では Parameter の Direction が adParamInput 方向に
限定されるため、出力パラメータを伴うコマンドの実行には向きませんけれども。
>マジックナンバーの使用は避けましょう。
>この場合は、adCmdText 定数を代入するべきです。
ご指摘ありがとうございます。
相談に載っていただく上でもわかりづらくなってました。
すいません。
>ActiveConnection/CommandText/CommandType プロパティを、それぞれ
>正しく設定しておく必要があります。
動いてる理由がよくわかっていなかったので、勉強になりました。
Refreshメソッドを呼んだ場合は、cmd.Parameters(0)といったように
配列の番号を数字でしか呼べないのでしょうか。
上記のソースを修正してみたのですがm_AdoComm("col1")が
数字でないとエラーになります。
プロバイダがパラメータオブジェクトを作ってくれるので
これで大丈夫だと思ったんですが・・。
strSQL = "SELECT * FROM test1 where col1 = ?"
m_AdoComm.CommandText = strSQL
m_AdoComm.CommandType = adCmdText
m_AdoComm.Parameters.Refresh '追加
m_AdoComm("col1") = "1" '★ここでエラーになります
RefreshについてはこちらのHPを参考にさせて頂きました。
http://www.accessclub.jp/ado/refresh.html
test1 where col1 = ? 構文の場合、名前を指定するには
Parameter オブジェクトを自分で組み立てるしかありません。
名前付きのパラメータ構文がサポートされていれば、
そちらの構文に変更してください。
もし、? の構文しかサポートされていないのだとしたら、
Refresh で使う場合、基本的には番号で指定するか、もしくは
Refresh を使わず、Parameter を自前で組み立てていく事になります。
> プロバイダがパラメータオブジェクトを作ってくれるので
> これで大丈夫だと思ったんですが・・。
プロバイダによっては、名前を自動生成してくれ場合もあります。
その名前がどのようなものになるのかは、
Dim p As ADODB.Parameter
For Each p In cmd.Parameters
Debug.Print p.Name
Next
などの構文で確認できるかと思います。ただし、この名前を使って
パラメータを呼び出せるかどうかは、プロバイダに依存しています。
> m_AdoComm("col1") = "1" '★ここでエラーになります
番号で指定するにしても、名前で指定するにしても、
m_AdoComm(x) = 値
ではなく、
m_AdoComm(x).Value = 値
の構文を利用するようにしてください。
これは Parameter だけではなく、Field の場合も同様で、
rs.Fields("Col1").Value = 値
rs.Collect("Col1") = 値
などの構文は良いですが、
rs.Fields("Col1") = 値
は避けるべきとされています。
まぁ、ADO の場合は Value を忘れても問題は少ないのですが、
Value を指定した方が、実行速度は向上します。
(これが oo4o だと、.Value を忘れて誤動作するケースがしばしば…)
返信ありがとうございます。
何度も読ませて頂きました。
とても勉強になっています。
詳しい返信はもう少し理解、調査をして
また書かせていただきたいと思います。
>もし、? の構文しかサポートされていないのだとしたら、
>Refresh で使う場合、基本的には番号で指定するか、もしくは
>Refresh を使わず、Parameter を自前で組み立てていく事になります。
?以外がサポートされているかについてはDBの影響を受けるという
解釈でしょうか。
同じADOを使ってるAccessでSQLServerにない？？
パラメータの使い方を紹介していただいているので・・。
いまさらですが、環境はSQLServer2005です。
> Dim p As ADODB.Parameter
> For Each p In cmd.Parameters
> Debug.Print p.Name
> Next
使わせていただいたら、パラメータがParam1,Param2・・と
出力されていました。
>m_AdoComm(x).Value = 値
>の構文を利用するようにしてください。
ご指摘ありがとうございます。
早速、修正をかけさせて頂いています。
今更ながら、色々教えていただいてありがとうございます。
自分の書いたソースにコメントを振りながらひとつひとつの意味が
わかるソースに修正していっています。
すいません。
解決・・としてしまったのですが、
下記の疑問が解決しておりません。
よろしくお願い致します。
同じADOを使ってるAccessでSQLServerにない？？
パラメータの使い方を紹介していただいているので・・。
いまさらですが、環境はSQLServer2005です。
| ツイート |  |