Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Enterprise
Expertise: Beginner
Oct 13, 1999

Check if Provider Supports Transactions

A transaction can be defined as a related set of operations that read and modify data in a single batch. Every transaction should follow the ACID rule, where ACID stands for atomic, consistent, isolated, and durable. A transaction provides atomicity to a series of data modifications to one or more recordsets within a connection, allowing all of the changes to take place at once, or not at all.

You can use BeginTrans() and CommitTrans() methods of ADODB.Connection object to begin a new transaction or to commit a pending transaction. Method RollbackTrans() aborts any pending transaction.

But before you use these methods of Connection object, you should ensure that your provider supports transactions. Otherwise calling these methods against a provider that doesn't support transactions will generate an error. You can use "Transaction DDL" property of the connection object to check if your provider supports Transactions. Here is a simple method which can be used to verify if your Provider supports Transactions or not. This method takes in a connection object as parameter and displays a message box about the Transaction support.

 
Private Sub CheckTransactionSupport(ByVal _
	oConnection As ADODB.Connection)

	' Name of the Property
	Const DBPROP_TRANSACTION_SUPPORT As String _
		= "Transaction DDL"
	' Values returned by the provider for the property
	Const DBPROPVAL_TC_NONE As Long = &H0
	Const DBPROPVAL_TC_DML As Long = &H1
	Const DBPROPVAL_TC_DDL_COMMIT As Long = &H2
	Const DBPROPVAL_TC_DDL_IGNORE As Long = &H4
	Const DBPROPVAL_TC_ALL As Long = &H8

	On Error GoTo ErrorHandler

	' Check Transaction support property of
	' connection object.
	' It can have one of the following values
	Select Case oConnection.Properties _
		(DBPROP_TRANSACTION_SUPPORT)
		Case DBPROPVAL_TC_NONE
			MsgBox "Transactions are not supported"
		Case DBPROPVAL_TC_DML
			MsgBox "Transactions can only contain " & _
				"DML statements. " & vbCrLf & _
				"DDL statements within a transaction " & _
				"cause an error"
		Case DBPROPVAL_TC_DDL_COMMIT
			MsgBox "Transactions can contain " & _
			"DML statements. " & vbCrLf & _
			"DDL statements within a transaction " & _
			"cause the transaction to be committed"
		Case DBPROPVAL_TC_DDL_IGNORE
			MsgBox "Transactions can only " & _
				"contain DML statements. " & vbCrLf & _
				"DDL statements within a transaction " & _
				"are ignored"
		Case DBPROPVAL_TC_ALL
			MsgBox "Transactions can contain " & _
				"both DDL and DML statements"
	End Select
	Exit Sub
ErrorHandler:
	' Display error message
	MsgBox "Error Number = " & _
		Err.Number & ", Description = " & Err.Description, _
		vbCritical, "Check Transaction Support Error"
End Sub
Deepak Pant
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date