4.1. ADO에 대한 모든 것 - Command : Command 개체의 속성
ADO의 Command 개체는 질의, SQL 문장 그리고 저장 프로시저(Stored Procedure)를 실행할 수 있는 개체이다.
[그림] Command 개체
Command 개체는 특별히 매개변수를 갖는 질의(Query) 또는 저장 프로시저를 수행할 때 보다 효과적이며, Command 개체를 이용하면, 질의를 통해서 Recordset 개체를 생성하거나, Insert, Update, Delete 같은 SQL 문장을 실행하거나, 데이터베이스의 구조를 변경하는 작업 등을 할 수 있다.
Command 개체는 다음과 같은 방법으로 선언해서 사용할 수 있다.
Dim adoCmd As ADODB.Command
Set adoCmd = New ADODB.Command
Command 개체를 사용하지 않고 Connection 개체의 Execute 메서드 또는 Recordset 개체의 Open 메서드를 사용해서 질의를 실행할 수 있다. 하지만, 질의를 계속해서 재사용하기를 원하거나, 매개변수와 함께 사용하려고 할 때에는 Command 개체를 사용하는 것이 좋다.
그럼 Command 개체의 속성과 메서드 그리고 이벤트에 대해서 자세히 살펴 본다.
Command 개체의 속성
| 속성 | 설명 | 기본 값 | 데이터 형식 |
| ActiveConnection | 상위 Connection 개체를 지정한다. (읽기/쓰기) | Null 개체 | Connection |
| CommandText | 실행할 SQL 문을 지정한다. (읽기/쓰기) | Zero-Length 문자열 | String |
| CommandTimeout | 명령어가 결과를 반환할 때까지 대기하는 시간을 지정한다. (초 단위) (읽기/쓰기) | 30초 | Long |
| CommandType | 실행할 명령의 타입을 지정한다. | adCmdUnknown | CommandTypeEnum |
| Name | Command 개체의 이름을 나타낸다. | 없음 | String |
| Prepared | 명령을 실행하기 전에 컴파일 버전을 저장할지를 결정한다. (읽기/쓰기) | False | Boolean |
| Properties | 데이터 Provider에 대한 프로퍼티 개체들의 컬렉션이다. (읽기 전용) | 없음 | Properties |
| State | Command 개체의 상태를 나타낸다. (읽기 전용) | adStateClosed | Long |
ActiveConnection 속성
ActiveConnection 속성은 현재의 Command 개체가 속해 있는 Connection 개체를 나타낸다. 이 속성은 연결을 위한 정보를 문자열로 지정하거나, Connection 개체를 지정할 수 있으며, 기본값은 Null 개체이다.
ActiveConnection 속성에 Nothing을 지정하면 Command 개체에 지정된 Connection 개체의 정보가 삭제되며, 데이터소스와의 연결을 위해 사용했던 자원(Resource)도 제거된다. Command 개체는 서로 다른 Connection 개체에 연결되어서 사용될 수 있다.
Command 개체의 Parameters 컬렉션이 매개변수를 포함하고 있을 경우라도, Command 개체에 Nothing을 지정하거나, 다른 Connection 개체에 Command 개체가 연결될 경우 Parameters 컬렉션은 초기화된다.
[예제]
다음 예제는 SQL 서버의 Pubs 데이터베이스에 있는 byroyalty라는 저장 프로시저를 수행하는 예이다. 로열티를 입력하라는 메시지 창이 나타나면 100을 입력해 본다.
다음 예제에서는 Connection 개체와 Command 개체의 관계에 대한 부분만을 제대로 이해하면 되며, 코드의 나머지 부분들에 대해서는 이 절의 이어지는 내용에서 자세히 다루고 있다.
Private Sub cmdActiveConnection_Click()
Dim adoCn As New ADODB.Connection
Dim adoCmd As New ADODB.Command
Dim adoPara As New ADODB.Parameter
Dim adoRs As New ADODB.Recordset
Dim iRoyalty As Integer
Dim strAuthorID As String
Dim strConn As String
strConn = "Provider=sqloledb;Data Source=(local);Initial Catalog=Pubs;User ID=sa;Password=;"
adoCn.Open strConn
Set adoCmd.ActiveConnection = adoCn
adoCmd.CommandText = "byroyalty"
adoCmd.CommandType = adCmdStoredProc
adoCmd.CommandTimeout = 15
'저장 프로시저의 매개변수 값을 입력 받는다.
iRoyalty = Trim(InputBox("royalty 입력:"))
adoPara.Type = adInteger
adoPara.Direction = adParamInput
adoPara.Value = iRoyalty
adoCmd.Parameters.Append adoPara
'명령을 실행해서 Recordset 생성
Set adoRs = adoCmd.Execute()
Do While Not adoRs.EOF
Debug.Print , adoRs!au_id & vbNewLine
adoRs.MoveNext
Loop
adoRs.Close
Set adoRs = Nothing
Set adoCmd = Nothing
adoCn.Close
Set adoCn = Nothing
End Sub
CommandText 속성
CommandText 속성은 Provider를 통해서 실행할 명령어(SQL 문장, 테이블 명, 저장 프로시저 명)를 지정하는 속성이며 기본값은 길이가 없는 문자열이다.
ActiveConnection 속성의 예제에서 다음과 같은 코드를 볼 수 있다.
위의 코드는 Pubs 데이터베이스의 byroyalty라는 저장 프로시저를 Command 개체를 사용해서 수행하기 위해 지정하는 예이며, 위의 코드와 같은 방식으로 SQL 문장, 테이블 명, 저장 프로시저 명을 지정해서 사용할 수 있다.
CommandTimeout 속성
CommandTimeout 속성은 명령어가 결과를 반환할 때까지 대기하는 시간을 나타내며, 디폴트 값은 30초 이다. CommandTimeout 속성은 네트워크의 환경을 고려해서 명령어가 충분히 실행될 수 있는 시간을 지정하는 것이 좋으며, 0으로 설정할 경우에는 무한대로 결과를 기다리게 되며, 특별한 경우가 아니라면 디폴트 값을 사용하는 것이 좋다.
Connection 개체에도 CommandTimeout 속성이 있지만 Command 개체의 CommandTimeout 속성과는 아무런 관계가 없다. 이유는 Command 개체의 CommandTimeout 속성은 Connection 개체의 CommandTimeout 속성으로부터 상속받는 값이 아니고 독자적으로 존재하는 것이기 때문이다.
ActiveConnection 속성의 예제에서 다음과 같은 코드를 볼 수 있다.
위의 코드는 Command 개체가 실행한 후에 15초 내에 결과가 반환되지 않으면 오류를 발생시키도록 설정하는 것이다.
특히 대량의 작업을 수행하는 저장 프로시저를 호출하는 경우라면 일반적으로 180초 정도까지는 지정한다. 이런 특별한 경우가 없다면 디폴트 값(30초)을 사용하는 것이 무난하다.
CommandType 속성
CommandType 속성은 Command 개체의 형식을 나타내며, 다음 표의 값을 지정할 수 있다.
| 상수 | 값 | 설명 |
| adCmdUnspecified | -1 | CommandType 인수를 지정하지 않는다. |
| adCmdUnknown | 8 | 디폴트 값, Command 개체의 타입을 알 수 없음으로 지정한다. |
| adCmdText | 1 | 명령어 또는 저장 프로시저를 텍스트로 지정한다. |
| adCmdTable | 2 | 테이블을 지정한다(내부적으로 생성된 SQL 문장에 의해서 필드가 생성된다). |
| adCmdStoredProc | 4 | 저장 프로시저를 지정한다. |
| adCmdFile | 256 | Persisted Recordset 개체의 파일명을 지정한다. |
| adCmdTableDirect | 512 | 테이블을 지정한다(모든 필드가 생성된다). |
CommandType 속성의 값이 디폴트 값인 adCmdUnknown일 경우에는 CommandText 속성이 SQL 문장인지, 저장 프로시저인지, 테이블의 이름인지를 결정하기 위해서 ADO는 Provider를 호출하게 되고, CommandType 속성을 정확하게 지정한 경우 보다 Command 개체가 수행하기 위해 부하가 더 걸리게 된다.
그렇기 때문에 CommandType 속성을 정확하게 지정하고 사용하면 디폴트 값을 사용하는 것보다 빠른 속도로 명령을 실행하게 된다.
CommandType 속성에 지정한 값과 실제 실행에 사용될 명령의 형식이 맞지 않을 경우에는 Execte 메서드를 사용할 때 오류가 발생하게 된다.
Execute 메서드를 사용할 때 함께 사용할 수 있는 옵션으로 adExecuteNoRecords가 있다. 이 옵션은 명령 또는 저장 프로시저가 반환 값을 가지지 않을 경우에 지정한다.
adExecuteNoRecords 상수는 최소한의 내부 프로세스만을 거치기 때문에 속도는 빠른 반면에 항상 adCmdText 또는 adCmdStoredProc 상수와 함께 사용해야 한다.
[예제]
다음 예제는 CommandType 속성을 adCmdText로 지정해서 Command 개체를 수행하는 예이다.
Private Sub cmdCommandType_Click()
Dim adoCn As New ADODB.Connection
Dim adoCmd As New ADODB.Command
Dim strConn As String
strConn = "Provider=sqloledb;Data Source=(local);Initial Catalog=Pubs;User ID=sa;Password=;"
adoCn.Open strConn
Set adoCmd.ActiveConnection = adoCn
adoCmd.CommandText = "Update Authors Set au_lname = 'Chris' Where au_id = '172-32-1176'"
adoCmd.CommandType = adCmdText
adoCmd.Execute
Set adoCmd = Nothing
adoCn.Close
Set adoCn = Nothing
End Sub
Name 속성
Name 속성은 Command 개체의 이름을 나타낸다. 이 속성은 일반적으로 사용되지 않지만, Command 개체들의 컬렉션에서 개체를 식별하기 위해서 사용될 수 있다.
Name 속성의 값은 String이며, 다음의 코드와 같이 사용할 수 있다.
Dim adoCmd As New Command
Dim strCmdName As String
strCmdName = adoCmd.Name
adoCmd.Name = strCmdName
Prepared 속성
Prepared 속성은 실행 전에 명령의 컴파일된 버전을 Provider가 저장할 것인지를 지정하는 속성이다. Prepared 속성을 True로 설정하게 되면, Command 개체가 처음 실행하기 전에 CommandText 속성에 지정된 명령을 컴파일해서 저장하게 된다. 그렇기 때문에 처음 실행 시에는 속도가 좀 떨어지지만, 두 번째 이후의 실행부터는 컴파일 시간이 단축되기 때문에 속도가 빨라지게 된다. 이 속성은 마치 저장 프로시저를 사용하는 것과 동일한 개념이라고 볼 수 있다.
이 프로퍼티가 False 일 경우에 Provider는 명령의 컴파일 버전을 생성하지 않고 직접 실행하게 된다. Provider가 이 기능을 지원하지 않는다면, Prepared 프로퍼티를 True로 지정했을 때에는 오류가 발생한다. 만약 오류가 발생하지 않는다면 Provider가 자동으로 False로 지정한 것이다.
참고
1. 구문 검사(Syntax Check)
2. 구문 분석(Parsing)
3. 컴파일(Complie)
4. 실행(Execution)
저장 프로시저를 사용하면, 컴파일까지 된 상태에서 실행을 대기하고 있기 때문에 SQL 문장을 직접 실행할 때보다 빠르게 수행하는 것이다. 반복해서 SQL 문장을 호출할 때 Prepared 속성을 사용하면, 첫번째 실행에서 저장 프로시저처럼 만든 다음 계속해서 컴파일된 SQL 문장을 사용하는 것이다.
[예제]
다음 예제는 두 개의 Command 개체를 만든 다음, 하나는 Prepared 속성을 False로 다른 하나는 Prepared 속성을 True로 설정해서 동일한 SQL 문장을 20회 수행한 후에 작업에 소요된 시간을 비교해서 Msgbox로 보여주는 예이다.
Private Sub cmdPrepared_Click()
Dim adoCn As New ADODB.Connection
Dim adoCmd1 As New ADODB.Command
Dim adoCmd2 As New ADODB.Command
Dim strConn As String
Dim strCmd As String
Dim sngStart As Single
Dim sngEnd As Single
Dim sngNotPrepared As Single
Dim sngPrepared As Single
Dim i As Integer
'Connection 연결
strConn = "Provider=sqloledb;Data Source=(local);Initial Catalog=Pubs;User Id=sa;Password=;"
adoCn.Open strConn
'두개의 Command 개체 생성
'하나는 Prepared로 다른 하나는 Not Prepared로 지정
strCmd = "SELECT Title, Type FROM Titles ORDER BY Type"
Set adoCmd1.ActiveConnection = adoCn
adoCmd1.CommandText = strCmd
Set adoCmd2.ActiveConnection = adoCn
adoCmd2.CommandText = strCmd
adoCmd2.Prepared = True
'Command를 20회 Execute한 시간을 계산(Not Prepared)
sngStart = Timer
For i = 1 To 20
adoCmd1.Execute
Next i
sngEnd = Timer
sngNotPrepared = sngEnd - sngStart
'Commad를 20회 Execute한 시간을 계산(Prepared)
sngStart = Timer
For i = 1 To 20
adoCmd2.Execute
Next i
sngEnd = Timer
sngPrepared = sngEnd - sngStart
'실행 결과
MsgBox "실행 결과:" & vbCr & _
" Not Prepared: " & Format(sngNotPrepared, "##0.000") & " 초" & vbCrLf & _
" Prepared: " & Format(sngPrepared, "##0.000") & " 초"
adoCn.Close
Set adoCn = Nothing
End Sub
Properties 속성
데이터 Provider에 대한 속성 개체들의 컬렉션으로 읽기 전용이다.
[예제]
다음 예제는 Properties 컬렉션이 가지고 있는 값들을 보여주는 예이다.
Private Sub cmdProperties_Click()
Dim adoCn As New ADODB.Connection
Dim adoCmd As New ADODB.Command
Dim strConn As String
Dim strCmd As String
Dim i As Integer
strConn = "Provider=sqloledb;Data Source=(local);Initial Catalog=Pubs;User Id=sa;Password=;"
adoCn.Open strConn
strCmd = "SELECT Title, Type FROM Titles ORDER BY Type"
Set adoCmd.ActiveConnection = adoCn
adoCmd.CommandText = strCmd
For i = 0 To adoCmd.Properties.Count - 1
Debug.Print adoCmd.Properties(i).Name & " : "; adoCmd.Properties(i).Value
Next i
adoCn.Close
Set adoCn = Nothing
End Sub
위의 예제의 결과는 다음과 같다.
IColumnsInfo : True
IColumnsRowset : True
IConnectionPointContainer : False
IConvertType : True
IMultipleResults : False
IRowset : True
IRowsetChange : False
IRowsetIdentity : True
IRowsetInfo : True
IRowsetLocate : False
IRowsetResynch : False
IRowsetScroll : False
IRowsetUpdate : False
ISupportErrorInfo : True
ISequentialStream : True
Preserve on Abort : False
Access Order : 2
Blocking Storage Objects : True
Bookmark Information : 0
Use Bookmarks : False
Skip Deleted Bookmarks : False
Bookmark Type : 1
Fetch Backwards : False
Hold Rows : False
Scroll Backwards : False
Change Inserted Rows : False
Column Privileges : False
Command Time Out : 30
Preserve on Commit : False
Defer Column : False
Delay Storage Object Updates : False
Immobile Rows : True
Literal Bookmarks : False
Literal Row Identity : True
Lock Mode : 1
Maximum Open Rows : 0
Maximum Pending Rows : 0
Maximum Rows : 0
Notification Granularity : 1
Notification Phases : 31
Column Set Notification : 3
Row Delete Notification : 3
Row First Change Notification : 3
Row Insert Notification : 3
Row Resynchronization Notification : 3
Rowset Release Notification : 3
Rowset Fetch Position Change Notification : 3
Row Undo Change Notification : 3
Row Undo Delete Notification : 3
Row Undo Insert Notification : 3
Row Update Notification : 3
Others' Inserts Visible : False
Others' Changes Visible : False
Own Inserts Visible : False
Own Changes Visible : False
Quick Restart : False
Reentrant Events : True
Remove Deleted Rows : False
Report Multiple Changes : False
Return Pending Inserts : False
Row Privileges : True
Row Threading Model : 1
Server Cursor : False
Server Data on Insert : False
Strong Row Identity : False
Objects Transacted : False
Unique Rows : False
Updatability : 0
Maximum BLOB Length : 0
Fastload Options :
Keep Nulls : False
Keep Identity : False
Bookmarkable : False
State 속성
State 속성은 Command 개체가 데이터 소스에 연결된 상태인지, 연결이 되지 않은 상태인지, 또는 명령을 수행 중인지를 나타내는 속성이다.
| 상수 | 설명 |
| adStateClosed | Command 개체가 데이터 소스에 연결되지 않은 상태는 나타내는 값이다. |
| adStateOpen | Command 개체가 데이터 소스에 연결된 상태를 나타내는 값이다. |
| adStateExecuting | Command 개체가 명령을 실행하고 있는 상태를 나타내는 값이다. |
| adStateFetching | Recordset 개체의 레코드를 가져오고 있는 상태(Fetch)를 나타내는 값이다. |
State 속성은 Command 개체를 사용하고자 하는 특정한 때에 Command 개체의 상태를 알고자 할 때 사용할 수 있다. 이 값은 읽기 전용이다.
이 속성은 Connection 개체와 Recordset 개체에도 있으며, Recordset 개체에서는 두 개 이상의 값이 조합되어 사용되기도 한다. 예를 들어, adStateOpen 값과 adStateExecuting이 조합되어 Recordset 개체의 상태를 나타내기도 한다.
이 속성은 비동기적으로 명령을 수행할 때, 명령이 아직도 수행 중인지를 평가하기 위해서 사용될 수 있다.
[예제]
다음 예제는 비동기적으로 명령을 수행한 다음, 명령이 아직 수행 중인지를 평가하는 예이다.
Private Sub cmdState_Click()
Dim adoCn As New ADODB.Connection
Dim adoCmd As New ADODB.Command
Dim strConn As String
Dim i As Integer
strConn = "Provider=sqloledb;Data Source=(local);Initial Catalog=Pubs;User Id=sa;Password=;"
adoCn.Open strConn
Set adoCmd.ActiveConnection = adoCn
adoCmd.CommandText = "SELECT Title, Type FROM Titles ORDER BY Type"
adoCmd.Execute , , adAsyncExecute
'명령이 아직 수행 중인지를 체크한다.
If adoCmd.State = adStateExecuting Then
Debug.Print "수행 중..."
Else
Debug.Print "수행 완료..."
End If
adoCn.Close
Set adoCn = Nothing
End Sub