'
'Date: 2012/05/04
'Author: xi wei cheng
'
'Option Explicit
Public Sub SqlInsert()
Dim flg As Boolean
flg = Worksheets("SQL-Tool").CheckBox1.value
Dim template, t, t1, t2 As String
template = "insert into {tScame}.{tName} ({colNameArr}) VALUES ({colValArr})"
t = "String sqlInsert{index} = " & Chr(34) & "{template}" & Chr(34) & ";"
Dim tScame, tName, colNameArr, colValArr As String
tScame = Range("D5").value
tName = Range("D3").value
colNameArr = ""
colValArr = ""
If tScame = "" Then
MsgBox "[ƒXƒL[ƒ}] can't be empty!"
Exit Sub
End If
If tName = "" Then
MsgBox "[ƒe[ƒuƒ‹–¼i•¨—–¼j] can't be empty!"
Exit Sub
End If
'11
Dim i, j, lastCol As Integer
lastCol = Rows(11).Worksheet.Range("IV11").End(xlToLeft).Column
Dim colName, colVal, colType As String
colName = ""
colVal = ""
For i = 2 To lastCol
colName = Cells(11, i).value
colNameArr = colNameArr & colName & ", "
Next i
colNameArr = Strings.Left(colNameArr, Len(colNameArr) - 2)
t1 = Strings.Replace(template, "{tScame}", tScame)
t1 = Strings.Replace(t1, "{tName}", tName)
t1 = Strings.Replace(t1, "{colNameArr}", colNameArr)
'MsgBox t1
Dim lastRow As Integer
lastRow = MaxRowIndex(ActiveSheet)
If lastRow <= 16 Then
MsgBox "You haven't fill insert datas!"
Exit Sub
End If
Dim rowData, sqlArr, sqlIdArr As String
rowData = ""
sqlArr = ""
sqlIdArr = ""
'VARCHAR2
'VARCHAR
'DATE
'NUMBER
'TIMESTAMP
'NUMERIC
For i = 17 To lastRow
rowData = ""
For j = 2 To lastCol
colType = Cells(12, j).value
Select Case colType
Case "VARCHAR"
rowData = rowData & "'" & Cells(i, j).value & "'" & ", "
Case "VARCHAR2"
rowData = rowData & "'" & Cells(i, j).value & "'" & ", "
Case "DATE"
'to_date('2007-08-03 22:24:29','yyyy-mm-dd HH24-mi-ss')
'rowData = rowData & "'" & Cells(i, j).value & "'" & ", "
rowData = rowData & "to_date('" & Cells(i, j).value & "','yyyy-mm-dd HH24-mi-ss')" & ", "
Case "NUMBER"
rowData = rowData & Cells(i, j).value & ", "
Case "NUMERIC"
rowData = rowData & Cells(i, j).value & ", "
Case "TIMESTAMP"
'SYSTIMESTAMP
'rowData = rowData & "'" & Cells(i, j).value & "'" & ", "
rowData = rowData & "SYSTIMESTAMP" & ", "
Case Else
MsgBox "Can't parse Column type-> [" & colType & "]."
End Select
Next j
colValArr = Strings.Left(rowData, Len(rowData) - 2)
t2 = Strings.Replace(t1, "{colValArr}", colValArr)
If flg Then
t2 = Strings.Replace(t, "{template}", t2)
t2 = Strings.Replace(t2, "{index}", i - 16)
sqlIdArr = sqlIdArr & "sqlInsert" & (i - 16) & ", "
End If
sqlArr = sqlArr & t2 & vbCrLf
Next i
If flg Then
sqlArr = sqlArr & vbCrLf _
& "TestUtil tu = new TestUtil();" & vbCrLf _
& "String[] sqlArr = {" & Strings.Left(sqlIdArr, Len(sqlIdArr) - 2) & "};" & vbCrLf _
& "for(String sql : sqlArr){" & vbCrLf _
& " tu.runBySql(sql);" & vbCrLf _
& "}"
End If
Dim dataObj As DataObject
Set dataObj = New DataObject
dataObj.SetText sqlArr
dataObj.PutInClipboard
MsgBox "The insert Sql has been put into the clipboard."
End Sub
Function MaxRowIndex(ws As Worksheet)
Dim i, index, tempIndex As Integer
index = 0
For i = 1 To 100
tempIndex = ws.Cells(65536, i).End(xlUp).row
If tempIndex > index Then index = tempIndex
Next
MaxRowIndex = index
End Function
Public Sub SqlDelete()
Dim flg As Boolean
flg = Worksheets("SQL-Tool").CheckBox1.value
Dim template, t, t1, t2 As String
template = "delete from {tScame}.{tName} where {condition}"
t = "String sqlDelete{index} = " & Chr(34) & "{template}" & Chr(34) & ";"
Dim tScame, tName, condition As String
tScame = Range("D5").value
tName = Range("D3").value
condition = ""
If tScame = "" Then
MsgBox "[ƒXƒL[ƒ}] can't be empty!"
Exit Sub
End If
If tName = "" Then
MsgBox "[ƒe[ƒuƒ‹–¼i•¨—–¼j] can't be empty!"
Exit Sub
End If
Dim i, j, lastCol As Integer
lastCol = Rows(11).Worksheet.Range("IV11").End(xlToLeft).Column
Dim colName, colVal, colType, colKey As String
colName = ""
colVal = ""
colKey = ""
t1 = Strings.Replace(template, "{tScame}", tScame)
t1 = Strings.Replace(t1, "{tName}", tName)
'MsgBox t1
Dim lastRow As Integer
lastRow = MaxRowIndex(ActiveSheet)
If lastRow <= 16 Then
MsgBox "You haven't fill delete datas!"
Exit Sub
End If
Dim rowData, sqlArr, sqlIdArr As String
rowData = ""
sqlArr = ""
sqlIdArr = ""
'VARCHAR2
'VARCHAR
'DATE
'NUMBER
'TIMESTAMP
'NUMERIC
For i = 17 To lastRow
rowData = ""
For j = 2 To lastCol
colKey = Cells(9, j).value
If colKey <> "" Then
colType = Cells(12, j).value
colName = Cells(11, j).value
colVal = Cells(i, j).value
' where c1 = '1' and c2 = '2'
Select Case colType
Case "VARCHAR"
rowData = rowData & colName & " = " & "'" & colVal & "'" & " and "
Case "VARCHAR2"
rowData = rowData & colName & " = " & "'" & colVal & "'" & " and "
Case "DATE"
'to_date('2007-08-03 22:24:29','yyyy-mm-dd HH24-mi-ss')
'rowData = rowData & "'" & Cells(i, j).value & "'" & ", "
rowData = rowData & colName & " = " & "to_date('" & colVal & "','yyyy-mm-dd HH24-mi-ss')" & " and "
Case "NUMBER"
rowData = rowData & colName & " = " & colVal & " and "
Case "NUMERIC"
rowData = rowData & colName & " = " & colVal & " and "
Case "TIMESTAMP"
'SYSTIMESTAMP
'rowData = rowData & "'" & Cells(i, j).value & "'" & ", "
'rowData = rowData & "SYSTIMESTAMP" & ", "
MsgBox "Can't parse Column key type-> [" & colType & "]."
Case Else
MsgBox "Can't parse Column type-> [" & colType & "]."
End Select
End If
Next j
condition = Strings.Left(rowData, Len(rowData) - 5)
t2 = Strings.Replace(t1, "{condition}", condition)
If flg Then
t2 = Strings.Replace(t, "{template}", t2)
t2 = Strings.Replace(t2, "{index}", i - 16)
sqlIdArr = sqlIdArr & "sqlDelete" & (i - 16) & ", "
End If
sqlArr = sqlArr & t2 & vbCrLf
Next i
If flg Then
sqlArr = sqlArr & vbCrLf _
& "TestUtil tu = new TestUtil();" & vbCrLf _
& "String[] sqlArr = {" & Strings.Left(sqlIdArr, Len(sqlIdArr) - 2) & "};" & vbCrLf _
& "for(String sql : sqlArr){" & vbCrLf _
& " tu.runBySql(sql);" & vbCrLf _
& "}"
End If
Dim dataObj As DataObject
Set dataObj = New DataObject
dataObj.SetText sqlArr
dataObj.PutInClipboard
MsgBox "The delete Sql has been put into the clipboard."
End Sub
