|
|
vb ¿¡¼ ¿À¶óŬ ¿¬µ¿ ado ¹æ½Ä |
|
È£¼® ´Ô²²¼ ¾´ ±ÛÀÔ´Ï´Ù - 121.¢½.39.7 |
ÀÐÀ½:1444 |
|
|
1. Oracle Client¸¦ ¼³Ä¡ÇÏ°í, ¿¬°áÇÒ OracleÀ» ¿¡ ´ëÇÑ ¼³Á¤À» ÇÑ´Ù.
2. DB connect Function(¿©±â¼ XXX ºÎºÐ¿¡ Oracle Client¿¡¼ ¼³Á¤ÇÑ °ªÀ» ÀÔ·ÂÇÑ´Ù.)
- DB ¿¬°áÀÌ ÇÊ¿äÇÑ ºÎºÐ¿¡ ¾Æ·¡ functionÀ» È£ÃâÇÑ´Ù.
Private Sub ConnectDB()
Set adoOraCon = New ADODB.Connection
With adoOraCon
.ConnectionString = "Provider=MSDAORA.1;Data Source=XXX;User ID=XXX;Password=XXX;Persist Security Info=True"
.ConnectionTimeout = 60
.Open
End With
End Sub
3. ¸ðµâ¿¡ ´ÙÀ½ functionÀ» ÀÔ·ÂÇÑ´Ù.
Option Explicit
Public adoOraCon As ADODB.Connection
' Procedure : GetRecordSet
' Description : ÀÎÀÚ·Î ³Ñ¾î¿À´Â Äõ¸®¸¦ ½ÇÇàÇÏ°í, °á°ú°ªÀ» RecordSetÀ¸·Î ¹Ýȯ
' Parameter : szSql(Äõ¸®)
'Return Value : Recordset
Public Function GetRecordSet(ByVal szSql As String) As ADODB.Recordset
Dim adoRs As ADODB.Recordset
Set adoRs = New ADODB.Recordset
adoRs.Open szSql, adoOraCon, adOpenKeyset, adLockBatchOptimistic
'Recordset ¹Ýȯ
Set GetRecordSet = adoRs
Set adoRs = Nothing
End Function
' Procedure : ExecuteQuery
' Description : ÀÎÀÚ·Î ³Ñ¾î¿À´Â Äõ¸®¸¦ ½ÇÇàÇÏ°í ¼º°ø¿©ºÎ¸¦ ¹Ýȯ
' Parameter : szSql(Äõ¸®)
'Return Value : True/False
Public Function ExecuteQuery(szSql As String) As Boolean
On Error GoTo ErrHandler
adoOraCon.Execute szSql
ExecuteQuery = True
Exit Function
ErrHandler:
If Err.Number <> 0 Then
MsgBox Err.Source & vbCrLf & Err.Description, vbExclamation, "Äõ¸® ¼öÇà ¿À·ù"
ExecuteQuery = False
Err.Clear
End If
End Function
4. ¿øÇÏ´Â Äõ¸®¸¦ ¸¸µé¾î ¸ðµâÀÇ functionÀ» ½ÇÇàÇÑ´Ù.
- ÀϹÝÀûÀ¸·Î °ªÀ» °¡Áö°í ¿À´Â Select QueryÀ̸é, GetRecordSet¸¦ ½ÇÇàÇÏ°í,
- insert, delete¿Í °°Àº µ¥ÀÌÅÍ Á¶ÀÛ Äõ¸®ÀÎ °æ¿ì¿¡´Â ExecuteQuery¸¦ ½ÇÇàÇÑ´Ù.
¿¹) SELECT Query
Dim strSQL As String
Dim adoRs As ADODB.Recordset
'//IMPORTANT : Á¶È¸ Äõ¸® »ý¼º
strSQL = "SELECT * FROM TEST_TAB ORDER BY A_COL"
Set adoRs = GetRecordSet(strSQL)
While Not adoRs.EOF
'//IMPORTANT : °¡Áö°í ¿Â °á°ú¸¦ ó¸®ÇÏ´Â ºÎºÐ
adoRs.MoveNext
Wend
adoRs.Close
Set adoRs = Nothing
¿¹) Execute Query
Dim strSQL As String
strSQL = "INSERT INTO TEST_TAB(a_col, b_col, c_col, d_col) VALUES ('" & strName & "', '" & strKorean & "', '" & strMath & "', '" & strEnglish & "')"
If ExecuteQuery(strSQL) = False Then
Msgbox "½ÇÆÐ"
Else
Msgbox "¼º°ø"
End If
|
|
|
|
|
|