vb oracle 연결

1.OracleClient를설치하고,연결할Oracle을에대한설정을한다.

2.DBconnectFunction(여기서XXX부분에OracleClient에서설정한값을입력한다.)
-DB연결이필요한부분에아래function을호출한다.

PrivateSubConnectDB()
SetadoOraCon=NewADODB.Connection

WithadoOraCon
.ConnectionString="Provider=MSDAORA.1;DataSource=XXX;UserID=XXX;Password=XXX;PersistSecurityInfo=True"
.ConnectionTimeout=60
.Open
EndWith
EndSub

3.모듈에다음function을입력한다.
OptionExplicit
PublicadoOraConAsADODB.Connection

'Procedure:GetRecordSet
'Description:인자로넘어오는쿼리를실행하고,결과값을RecordSet으로반환
'Parameter:szSql(쿼리)
'ReturnValue:Recordset
PublicFunctionGetRecordSet(ByValszSqlAsString)AsADODB.Recordset

DimadoRsAsADODB.Recordset

SetadoRs=NewADODB.Recordset

adoRs.OpenszSql,adoOraCon,adOpenKeyset,adLockBatchOptimistic

'Recordset반환
SetGetRecordSet=adoRs

SetadoRs=Nothing

EndFunction

'Procedure:ExecuteQuery
'Description:인자로넘어오는쿼리를실행하고성공여부를반환
'Parameter:szSql(쿼리)
'ReturnValue:True/False
PublicFunctionExecuteQuery(szSqlAsString)AsBoolean

OnErrorGoToErrHandler

adoOraCon.ExecuteszSql

ExecuteQuery=True

ExitFunction

ErrHandler:
IfErr.Number<>0Then
MsgBoxErr.Source&vbCrLf&Err.Description,vbExclamation,"쿼리수행오류"
ExecuteQuery=False
Err.Clear
EndIf
EndFunction

4.원하는쿼리를만들어모듈의function을실행한다.
-일반적으로값을가지고오는SelectQuery이면,GetRecordSet를실행하고,
-insert,delete와같은데이터조작쿼리인경우에는ExecuteQuery를실행한다.

예)SELECTQuery
DimstrSQLAsString
DimadoRsAsADODB.Recordset

'//IMPORTANT:조회쿼리생성
strSQL="SELECT*FROMTEST_TABORDERBYA_COL"

SetadoRs=GetRecordSet(strSQL)

WhileNotadoRs.EOF
'//IMPORTANT:가지고온결과를처리하는부분
adoRs.MoveNext
Wend

adoRs.Close
SetadoRs=Nothing

예)ExecuteQuery
DimstrSQLAsString
strSQL="INSERTINTOTEST_TAB(a_col,b_col,c_col,d_col)VALUES('"&strName&"','"&strKorean&"','"&strMath&"','"&strEnglish&"')"

IfExecuteQuery(strSQL)=FalseThen
Msgbox"실패"
Else
Msgbox"성공"
EndIf

<