[VBA] CLOB 형식으로 저장하기

Function map_tab_bigo(strSysID As String)
    Dim DbCon As ADODB.Connection
    Dim DbCmd As ADODB.Command
    Dim DbCmd2 As ADODB.Command
    Dim DbCmd3 As ADODB.Command
    Dim strDir As String
    Dim strFile As String
    Dim msg As String
    Dim sht As Worksheet
    Dim myWorksheet As Worksheet
    Dim myActivesheet As Worksheet
    Dim strDirList(3) As String
    Dim no, Rowcnt As Integer
    Dim idx_1, idx_2, idx_3, idx_4 As Integer
    Dim sys_id, sys_nm, tmpVal As String
   
    on Error Resume Next    ' 오류 처리를 지연합니다.
   
    no = 1  '테이블 key
     
    strDir = "\\192.1.150.129\5000. 공정산출물\5050. 개발\90. Working_데이터이관\"
   
    strDirList(0) = strDir & strSysID & "\"
   
    Set DbCon = New ADODB.Connection
    Set DbCmd2 = New ADODB.Command
    Set DbCmd = New ADODB.Command
    Set DbCmd3 = New ADODB.Command
 
    DbCon.Open "Provider=MSDAORA.1;User ID=CONVDBA;Password=convdba;Data Source=MIGDB;Persist Security Info=False"  ' DB접속 환경
   
    DbCon.IsolationLevel = adXactReadCommitted
 
    DbCmd.ActiveConnection = DbCon
    DbCmd.CommandType = adCmdText

    DbCmd.CommandText = "insert into map_table_bigo ( file_nm, no, sys_id, sys_nm, to_tab_id, to_tab_nm, sheet_nm, bigo) values ( ?,?,?,?,?,?,?,? )" ' DB 컬럼 환경
   
    DbCmd2.ActiveConnection = DbCon
    DbCmd2.CommandType = adCmdText
   
    DbCmd2.CommandText = "delete from map_table_bigo where file_nm = ?"          ' 같은 파일명이 존재하면 기존에 존재한 데이터(파일이름 기준)를 삭제하고 로드된다.

'    DbCon.BeginTrans
   
   
    For idx_1 = 0 To UBound(strDirList)
        strFile = Dir(strDirList(idx_1))
       
        Do While strFile <> ""
            Application.StatusBar = "File Searching... " & strDirList(idx_1) & strFile  ' 처리상태 표시
           
            If Right(strFile, 8) <> "표지).xlsx" Then
           
                If Right(strFile, 4) = "xlsx" And Left(strFile, 7) = "컬럼매핑정의서" Then
                   
                    DbCon.BeginTrans
               
                    ' 기존 파일명에 해당하는 자료 삭제
                    DbCmd2.Parameters.Append DbCmd2.CreateParameter("file_nm", adVarChar, , Len(strFile), strFile)
                    DbCmd2.Execute
                    DbCmd2.Parameters.Delete ("file_nm")
           
                    Workbooks.Open strDirList(idx_1) & strFile
                   
                    Select Case strSysID
                        Case "진료":
                            sys_id = "MED": sys_nm = "진료"
                        Case "진료지원":
                            sys_id = "SUP": sys_nm = "진료지원"
                        Case "원무":
                            sys_id = "ADM": sys_nm = "원무"
                        Case "일반관리":
                            sys_id = "GEN": sys_nm = "일반관리"
                        Case "LIS":
                            sys_id = "LIS": sys_nm = "LIS"
                    End Select

                   
                    For Each myWorksheet In Worksheets
                   
                        If myWorksheet.Range("A1").Value = "컬럼매핑정의서" Then
                       
                            ' 전체 행 구하기
                            With myWorksheet
                           
                                Rowcnt = 0
                                For idx_2 = 0 To 11     '전체 열을 대상으로 가장 큰 행 구하기
                                   If .Range("a65536").Offset(0, idx_2).End(xlUp).Row > Rowcnt Then
                                      Rowcnt = .Range("a65536").Offset(0, idx_2).End(xlUp).Row
                                   End If
                                Next idx_2
                               
                               
                                If Rowcnt > 7 Then
                               
                                        DbCmd.Parameters.Append DbCmd.CreateParameter("file_nm", adVarChar, , Len(strFile), strFile)
                               
                                        DbCmd.Parameters.Append DbCmd.CreateParameter("no", adInteger, , , no)
                               
                                        DbCmd.Parameters.Append DbCmd.CreateParameter("sys_id", adVarChar, , Len(sys_id), sys_id)
                                        DbCmd.Parameters.Append DbCmd.CreateParameter("sys_nm", adVarChar, , Len(sys_nm), sys_nm)
                                       
                                        tmpVal = RTrim(myWorksheet.Range("C2"))
                                        If tmpVal = "" Or IsEmpty(tmpVal) Then
                                           DbCmd.Parameters.Append DbCmd.CreateParameter("to_tab_id", adVarChar, , 1, "")
                                        Else
                                           DbCmd.Parameters.Append DbCmd.CreateParameter("to_tab_id", adVarChar, , Len(tmpVal), tmpVal)
                                        End If
                                       
                                        tmpVal = RTrim(myWorksheet.Range("C3"))
                                       
                                        If tmpVal = "" Or IsEmpty(tmpVal) Then
                                           DbCmd.Parameters.Append DbCmd.CreateParameter("to_tab_nm", adVarChar, , 1, "")
                                        Else
                                           DbCmd.Parameters.Append DbCmd.CreateParameter("to_tab_nm", adVarChar, , Len(tmpVal), tmpVal)
                                        End If
                                       
                                        tmpVal = RTrim(myWorksheet.Name)
                                        If tmpVal = "" Or IsEmpty(tmpVal) Then
                                           DbCmd.Parameters.Append DbCmd.CreateParameter("sheet_nm", adVarChar, , 1, "")
                                        Else
                                           DbCmd.Parameters.Append DbCmd.CreateParameter("sheet_nm", adVarChar, , Len(tmpVal), tmpVal)
                                        End If

                                        tmpVal = RTrim(myWorksheet.Range("C" & Rowcnt - 1))
                                       
                                        If tmpVal = "" Or IsEmpty(tmpVal) Then
                                            DbCmd.Parameters.Append DbCmd.CreateParameter("bigo", adLongVarWChar, adParamInput, 1)
                                            DbCmd.Parameters.Item("bigo").AppendChunk " "
                                        Else
                                           'DbCmd.Parameters.Append DbCmd.CreateParameter("bigo", adVarChar, , Len(tmpVal), tmpVal)
                                            DbCmd.Parameters.Append DbCmd.CreateParameter("bigo", adLongVarWChar, adParamInput, Len(tmpVal))
                                            DbCmd.Parameters.Item("bigo").AppendChunk tmpVal
                                        End If
                                       
                                        DbCmd.Execute
                                       
                                        ' 파라메타 초기화
                                        DbCmd.Parameters.Delete ("file_nm")
                                        DbCmd.Parameters.Delete ("no")
                                        DbCmd.Parameters.Delete ("sys_id")
                                        DbCmd.Parameters.Delete ("sys_nm")
                                        DbCmd.Parameters.Delete ("to_tab_id")
                                        DbCmd.Parameters.Delete ("to_tab_nm")
                                        DbCmd.Parameters.Delete ("sheet_nm")
                                        DbCmd.Parameters.Delete ("bigo")
                                       
                                        no = no + 1 ' 레코드 건수 추가하기
                              

                                End If
                               
                   
                            End With
                        End If
                       
                       
                    Next    '쉬트 루프
                    ActiveWorkbook.Close False
                   
                    DbCon.CommitTrans
                   
                    Application.StatusBar = "File Searching...End... " & strFile
           
                End If
            End If
           
            strFile = Dir()
        Loop
   
    Next idx_1
   

    Application.StatusBar = "전환 조건 로드 완료... "
   
'    DbCon.CommitTrans
   
    DbCmd.ActiveConnection = Nothing
    Set DbCmd = Nothing
   
    DbCmd2.ActiveConnection = Nothing
    Set DbCmd2 = Nothing
 
    DbCon.Close
    Set DbCon = Nothing
End Function

 

참조블로그 : http://blog.naver.com/mrgoguma?Redirect=Log&logNo=140146763181

ADODB.Command 프로시저 Parameter size