Bulan kemaren kan posting ttg SQL Dump, terus sekarang mau nyoba manfaatin sql dump tsb untuk export import atau sinkronisasi data. enak sih, tinggal panggil SP, terus hasilnya di save ke file text. nanti di db yg akan di sinkronisasi, tinggal execute perintah2x yang ada di file text tadi.
masalahnya, sql dump hanya support untuk insert statement aja. untuk update statement tidak bisa. masalah lain adalah, SP Sql Dump ini hanya bisa dibuat dan di execute di SQL Server, tidak bisa di MS Access. akhirnya bikin dumper sendiri deh..8D
Dumper ini dibuat di visual basic 6.0. udah ditest dengan db SQL Server dan MS Access. hasilnya, procedure ini bisa handle 2 jenis database tersebut.
Procedure dumper ini memang tidak menghasilkan sql statement. yang dihasilkan adalah table definition dan data dalam table tersebut dalam bentuk yg terpisahkan dengan karakter tertentu. Katakter yang dipake adalah |.
Procedure dumpernya sebagai berikut:
Public Sub GenInsert(tField As String, tTable As String, tKondisi As String, Optional tForce As Boolean = False)
Dim RsGen As New ADODB.Recordset, I As Integer
RsGen.Open “select ” & IIf(tField <> “”, tField, “*”) & ” from ” & tTable & IIf(tKondisi <> “”, ” where ” & tKondisi, “”), ConnAPP, adOpenForwardOnly, adLockPessimistic, adCmdText
Open NamaFile For Append As #1
Print #1, “Table:” & tTable
Print #1, “Field:”;
For I = 0 To RsGen.Fields.Count – 1
If I <> RsGen.Fields.Count – 1 Then
Print #1, IIf(RsGen.Fields(I).Properties(4), “PK:”, “”) & RsGen.Fields(I).Name & “;” & RsGen.Fields(I).Type & “|”;
Else
Print #1, IIf(RsGen.Fields(I).Properties(4), “PK:”, “”) & RsGen.Fields(I).Name & “;” & RsGen.Fields(I).Type & “|”
End If
Next
If RsGen.RecordCount <> 0 Then
Do While Not RsGen.EOF
For I = 0 To RsGen.Fields.Count – 1
If I <> RsGen.Fields.Count – 1 Then
Print #1, IIf(IsNull(RsGen(I)), “NULL”, RsGen(I)) & “|”;
Else
Print #1, IIf(IsNull(RsGen(I)), “NULL”, RsGen(I)) & “|”
End If
Next
RsGen.MoveNext
Loop
End If
Close #1
RsGen.Close
End Sub
Hasil file Dumper, di baca dengan menggunakan procedure yang lain. dalam procedure yg berfungsi membaca, terdapat pengecheckan data. kalau data sudah ada, data diupdate, klo belum ada, maka data akan di insert.
Procedure bacanya sebagai berikut:
Public Sub ReadText(StrTXT As String)
On Error Resume Next
Dim I As Integer
If Left(StrTXT, 6) = “Table:” Then
tTable = Mid(StrTXT, 7)
ElseIf Left(StrTXT, 6) = “Field:” Then
ReDim tVField(30)
Sep Mid(StrTXT, 7)
TIndex = IndArray
ReDim tField(TIndex)
For I = 0 To TIndex – 1
tField(I) = tVField(I)
Next
Else
ReDim tVField(TIndex)
Sep StrTXT
tWhere = ” where “
For I = 0 To TIndex – 1
If Left(tField(I), 3) = “PK:” Then
tWhere = tWhere + Mid(Left(tField(I), InStr(1, tField(I), “;”, vbTextCompare) – 1), 4) & ” = ” & CheckType(Mid(tField(I), InStr(1, tField(I), “;”, vbTextCompare) + 1), tVField(I)) & ” and “
End If
Next
tWhere = Left(tWhere, Len(tWhere) – 4)
Set RsCari = ConnAPP.Execute(“select * from ” & tTable & tWhere)
InStr(1, tField(0), “;”, vbTextCompare) + 1), tVField(0)))
If RsCari.RecordCount <> 0 Then
StrSQL = “update ” & tTable & ” set “
For I = 0 To TIndex – 1
StrSQL = StrSQL + IIf(Left(tField(I), 3) <> “PK:”, Left(tField(I), InStr(1, tField(I), “;”, vbTextCompare) – 1), Mid(Left(tField(I), InStr(1, tField(I), “;”, vbTextCompare) – 1), 4)) & ” = ” & CheckType(Mid(tField(I), InStr(1, tField(I), “;”, vbTextCompare) + 1), tVField(I)) & “, “
Next
StrSQL = Left(StrSQL, Len(StrSQL) – 2)
StrSQL = StrSQL + tWhere ‘” where ” & Left(tField(0), InStr(1, tField(0), “;”, vbTextCompare) – 1) & ” = ” & CheckType(Mid(tField(0), InStr(1, tField(0), “;”, vbTextCompare) + 1), tVField(0))
ConnAPP.Execute StrSQL
Else
StrSQL = “insert into ” & tTable & ” (“
For I = 0 To TIndex – 1
StrSQL = StrSQL + IIf(Left(tField(I), 3) <> “PK:”, Left(tField(I), InStr(1, tField(I), “;”, vbTextCompare) – 1), Mid(Left(tField(I), InStr(1, tField(I), “;”, vbTextCompare) – 1), 4)) & “, “
Next
StrSQL = Left(StrSQL, Len(StrSQL) – 2)
StrSQL = StrSQL + “) values (“
For I = 0 To TIndex – 1
StrSQL = StrSQL + CheckType(Mid(tField(I), InStr(1, tField(I), “;”, vbTextCompare) + 1), tVField(I)) & “, “
Next
StrSQL = Left(StrSQL, Len(StrSQL) – 2)
StrSQL = StrSQL + “)”
ConnAPP.Execute StrSQL
End If
End If
End Sub
Private Function CheckType(Tipe As String, tValue As String) As String
Select Case Tipe
Case “200″, “202″
CheckType = BP(tValue)
Case “11″
If tValue = “0″ Or LCase(tValue) = “false” Then
CheckType = SQLBool(False)
Else
CheckType = SQLBool(True)
End If
Case “7″, “135″
CheckType = BP(Format(tValue, FMasking))
Case Else
CheckType = TS(tValue)
End Select
If LCase(tValue) = “null” Then CheckType = “NULL”
End Function
Private Sub Sep(kata As String, Optional Krit As String = “|”)
Dim tPos As Integer, tempPos As Integer
IndArray = 0
tPos = 1
tempPos = 1
tPos = InStr(tempPos, kata, Krit, vbTextCompare)
If tPos > 1 Then
tPos = 2
While tPos <> 1
tPos = InStr(tempPos, kata, Krit, vbTextCompare)
If tPos <> 0 Then
tVField(IndArray) = Mid(kata, tempPos, tPos – tempPos)
IndArray = IndArray + 1
Else
tPos = 1
tVField(IndArray) = Mid(kata, tempPos)
End If
tempPos = tPos + 1
Wend
ElseIf tPos = 0 Then
tVField(IndArray) = kata
End If
End Sub
Semoga bermanfaat…8D
powered by performancing firefox