Posted on 2009-02-17 18:50
大大毛 阅读(457)
评论(0) 编辑 收藏 所属分类:
VB
使用ADO時經常都會遇到要利用事務批次提交SQL的情況，如果使用不當的提交方式會出現預料外的問題，現在先看下面的示例代碼：
1 Private Sub cmdRun_Click()
2 On Error GoTo ErrHandle
3 Dim con As Connection
4 Dim strSQL As String
5 strSQL = ""
6 '第一筆SQL1
7 strSQL = strSQL & "Insert Into tb1 Values (...)" & vbNewLine
8 '第二筆SQL2
9 strSQL = strSQL & "Update tb1 Set C=1 Where ..." & vbNewLine
10 '...
11 '第N筆SQLn
12 strSQL = strSQL & "Delete tb1 Where ..." & vbNewLine
13 Set con = getCon() '獲取/打開DB連線
14 '準備事務
15 con.BeginTrans
16 '一批一起執行
17 con.Execute strSQL
18 '提交事務
19 con.CommitTrans
20 ExitHandle:
21 If con.State = 1 Then con.Close
22 Set con = Nothing
23 Exit Function
24 ErrHandle:
25 con.RollbackTrans
26 GoTo ExitHandle
27 MsgBox Err.Description
28 End Sub
上面這段程式利用換行符(分號也可)來將多條SQL合成一個字符串，最後在事務中執行一次就OK，既簡潔又漂亮的想法。
看似OK，但實際卻是埋下了禍端。從事務的機制來看，在事務中提交的SQL應該是一起成功/失敗的，而且這裏也有利用異常捕獲機制來實現RollBack，那麽在使用時到底是會出現什麽情況呢?
(0) SQL1,SQL2,...SQLn其中有語句含有非執行時的語法錯誤，比如說嵌套語句少掉一個右括號等；
OK。這種情況下，可以發現執行結果如預期，SQL1 -> SQLn都不會被執行，且會提示錯誤訊息。
(1) SQL1,SQL2,...SQLn都執行成功；
OK。這種情況下，可以發現執行結果如預期，SQL1 -> SQLn都會被執行，可以正確提交事務。
(2) SQL1,SQL2,...SQLn其中有語句會執行失敗；
(2.1) SQL1可以執行，而SQL2會發生執行時期錯誤(比如UPDATE上去欄位超過寬度)；
NO。Execute執行成功，查詢結果會發現SQL1的結果有提交，而SQL2以及之後的SQL都沒有執行。
(2.2) SQL1會發生執行時期錯誤(比如UPDATE上去欄位超過寬度)，而SQL2及其後的SQL可以被執行；
OK。這種情況下，可以發現有提示錯誤訊息，事務會被RollBack，SQL1 -> SQLn都不會被執行。
通過上面的幾種情況可以得到結論，如果是以一整個字串(中間用換行或分號分隔)來提交多筆SQL命令時，在所有SQL語法檢查OK後，執行的成功與否取決於第1筆SQL的執行狀態，因此就是說如果第一筆SQL執行OK，那麽該事務就一定會被Commit。
所以說，在使用事務提交多筆SQL時不能使用上面的提交方式，而必須逐筆的提交才行。