« [VB6.0|Oracle] DB接続 | メイン | [VB6.0|Oracle] Excelにグラフを表示 »

2005年07月30日

[VB6.0|Oracle] パラメータを使ったSQL文

Option Explicit


'--- パラメータ入出力

Public Const ORAPARM_INPUT = 1

Public Const ORAPARM_OUTPUT = 2

Public Const ORAPARM_BOTH = 3



'--- パラメータタイプ（NVARCHAR2 は VARCHAR2 でできた。）

Public Const ORATYPE_VARCHAR2 = 1

Public Const ORATYPE_NUMBER = 2

Public Const ORATYPE_SINT = 3

Public Const ORATYPE_FLOAT = 4

Public Const ORATYPE_STRING = 5

Public Const ORATYPE_VARCHAR = 9

Public Const ORATYPE_DATE = 12

Public Const ORATYPE_UINT = 68

Public Const ORATYPE_CHAR = 96

Public Const ORATYPE_CHARZ = 97

Public Const ORATYPE_CURSOR = 102






Public Function Ora_Insert ( _

    ByVal colNo As String, _

    ByVal colName As String, _

    ByVal colBirth As String, _

    ByVal colRole As String, _

    ByVal colMemo As String, _

    ByVal OraSession As Object, _

    ByVal OraDatabase As Object)

    

    '--- パラメータ追加

    OraDatabase.Parameters.Add "pNo", 0, ORAPARM_INPUT

    OraDatabase.Parameters("pNo").serverType = ORATYPE_VARCHAR2

    OraDatabase.Parameters.Add "pName", 0, ORAPARM_INPUT

    OraDatabase.Parameters("pName").serverType = ORATYPE_VARCHAR2

    OraDatabase.Parameters.Add "pBirth", 0, ORAPARM_INPUT

    OraDatabase.Parameters("pBirth").serverType = ORATYPE_NUMBER

    OraDatabase.Parameters.Add "pRole", 0, ORAPARM_INPUT

    OraDatabase.Parameters("pRole").serverType = ORATYPE_VARCHAR2

    OraDatabase.Parameters.Add "pMemo", 0, ORAPARM_INPUT

    OraDatabase.Parameters("pMemo").serverType = ORATYPE_VARCHAR2

    

    If Err <> 0 Or OraDatabase.LastServerErr <> 0 Then

        MsgBox "パラメータ追加に失敗しました。" & Chr(10) & Err & ": " _

                & Error & Chr(10) & "oo4o: " & OraDatabase.LastServerErrText

        End

    End If

    

    OraSession.BeginTrans

        

    '--- パラメータ格納

    OraDatabase.Parameters("pNo").Value = colNo

    OraDatabase.Parameters("pName").Value = colName

    OraDatabase.Parameters("pBirth").Value = colBirth

    OraDatabase.Parameters("pRole").Value = colRole

    OraDatabase.Parameters("pMemo").Value = colMemo

    

    '--- SQL文にパラメータ

    Dim strSql As String

    strSql = "INSERT INTO YAMADA.SAMPLETABLE VALUES _

                  (:pNo, :pName, :pBirth, :pRole, :pMemo)"



    OraDatabase.ExecuteSQL (strSql)

    OraSession.CommitTrans



End Function



'*** 後処理


Private Sub Form_Unload(Cancel As Integer)



    '----- パラメータ解消

    OraDatabase.Parameters.Remove "pNo"

    OraDatabase.Parameters.Remove "pName"

    OraDatabase.Parameters.Remove "pBirth"

    OraDatabase.Parameters.Remove "pRole"

    OraDatabase.Parameters.Remove "pMemo"



    '----- oo4o 接続解除

    Set OraDatabase = Nothing

    Set OraSession = Nothing

    

End Sub

注 ： OraSession と OraDatabase は、DBを開いたところから引数でもってくること！

2005 / 07 / 30