1092-1094

Previous Table of Contents Next

Page 1092

Listing 48.3. The ExecuteSQL method can be used to call Oracle stored procedures.

 Dim iRows   As Integer Dim szStmt  As String szStmt = "{call insert_individual(`" & txtLastName.Text & ", `" szStmt = szStmt & txtFirstName.Text & ", `" & txtNotes.Text szStmt = szStmt & ", `" & txtDateOfBirth.Text & "`)}" iRows = dbOracle.ExecuteSQL(szStmt) 

The ExecuteSQL method returns the number of rows affected by the transaction, and this value can be tested to determine the success or failure of the operation.

Unfortunately, because Visual Basic does not support prepared execution of SQL statements, it cannot call Oracle stored procedures that use output parameters, or add parameters to the procedure call. The statement must be built dynamically, as in Listing 48.3.

A second possible means of overcoming the AutoCommit problem is to use a third-party product, such as Oracle Objects for OLE. This product provides direct replacements for the Visual Basic objects and methods for communicating with Oracle databases. For example, the following code fragment establishes an Oracle session through VB that can be used for transaction control:

 Dim Session   As Object Dim dbOracle  As Object Set Session = CreateObject("OracleInProcServer.XOraSession") Set dbOracle = Session.OpenDatabase("ORACLE", "scotty/tiger", 0&) 

The preceding example uses the Visual Basic generic (OLE) Object datatype and the CreateObject function to request a new XOraSession object from Oracle's OLE server. The methods of the Session object can then be used to create other objects, and to manage transactions, using the BeginTrans, CommitTrans, and Rollback methods. A full discussion of the features of Oracle Objects for OLE is beyond the scope of this appendix. However, it is important to note that it can provide Visual Basic applications with the capability to control transactions, add parameters to SQL and procedure calls, and make use of output parameters when accessing Oracle databases, among other things.

The potential shortcoming of the previous two means of bypassing Visual Basic's AutoCommit behavior is that they are not portable. If a client application needs to access different RDBMSs, these solutions might not be feasible . A third approach to overcoming the AutoCommit problem is to use the ODBC API directly, which opens numerous possibilities, including the creation of a truly portable client application.

A small subset of the ODBC API can be used to provide transaction control in Visual Basic applications accessing ODBC data sources. The declarations in Listing 48.4 should be placed in a module, and will provide access to all functions needed to connect, apply transactions through embedded SQL, and roll back or commit them, as needed.

Page 1093

Listing 48.4. ODBC API declarations for Visual Basic.

 Declare Function SQLAllocConnect Lib "odbc.dll" (                  ByVal hEnv As Long, hDBc As Long) As Integer Declare Function SQLAllocEnv Lib "odbc.dll" (                  hEnv As Long) As Integer Declare Function SQLAllocStmt Lib "odbc.dll" (                  ByVal hDBc As Long, hStmt As Long) As Integer Declare Function SQLDisconnect Lib "odbc.dll" (                  ByVal hDBc As Long) As Integer Declare Function SQLDriverConnect Lib "odbc.dll" (                  ByVal hDBc As Long, ByVal hWnd As Integer,                  ByVal szCSin As String, ByVal cbCSin As Integer,                  ByVal szCSOut As String, ByVal cbCSMax As Integer,                  cbCSOut As Integer, ByVal f As Integer) As Integer Declare Function SQLExecDirect Lib "odbc.dll" (                  ByVal hStmt As Long, ByVal SQLString As String,                  ByVal SQLStringLen As Long) As Integer Declare Function SQLFreeConnect Lib "odbc.dll" (                  ByVal hDBc As Long) As Integer Declare Function SQLFreeEnv Lib "odbc.dll" (                  ByVal hEnv As Long) As Integer Declare Function SQLFreeStmt Lib "odbc.dll" (ByVal hStmt As Long,                  ByVal EndOption As Integer) As Integer Declare Function SQLSetConnectOption Lib "odbc.dll" (                  ByVal hDBc As Long, ByVal fOption As Integer,                  ByVal vParam As Long) As Integer Declare Function SQLTransact Lib "odbc.dll" (                  ByVal hEnv As Long, ByVal hDBc As Long,                  ByVal fnType As Integer) As Integer Global Const SQL_NTS = 3 Global Const SQL_DRIVER_NOPROMPT = 0 Global Const SQL_COMMIT = 0 Global Const SQL_ROLLBACK = 1 Global Const SQL_MAX_MESSAGE_LENGTH = 512 Global Const SQL_AUTOCOMMIT = 102 Global Const SQL_DROP = 1 

After establishing a connection for retrieving results with the OpenDatabase method, the application should establish a second connection to the database using the API functionsfor applying transactions. After establishing this connection, SQLSetConnectOption() should be used to disable AutoCommit. Listing 48.5 demonstrates how this might be accomplished.

Listing 48.5. Establishing a database connection using the ODBC API.

 Dim hEnv            As Long Dim hDBc            As Long Dim szConnectString As String Dim iError          As Integer Dim hWnd            As Integer Dim iLenCSOut       As Integer Dim szCSOut         As String * 254                                           continues 

Page 1094

Listing 48.5. continued

 szConnectString = "ODBC;DSN=ORACLE;UID=scotty;PWD=tiger;" hWnd = frmMDIFrame.hWnd ` Allocate environment iError = SQLAllocEnv(hEnv) ` Allocate connection iError = SQLAllocConnect(hEnv, hDBc) ` Load driver & connect to ODBC data source iError = SQLDriverConnect(hDBc, hWnd, szConnectString, SQL_NTS,          szCSOut, 254, iLenCSOut, SQL_DRIVER_NOPROMPT) ` Disable autocommit iError = SQLSetConnectOption(hDB, SQL_AUTOCOMMIT, 0) 

Obviously, in practice, the connect string would not be hard-coded and the return value of each function should be checked. The example in Listing 48.5 is intended only to demonstrate the use of the ODBC API to establish a database connection.

Once a connection has been established, the application can apply transactions using SQLExecDirect() after allocating a statement handle with SQLAllocStmt(). SQLTransact() can then be used to commit or roll back a transaction based on the return value of SQLExecDirect(). After applying the transaction, the application should call SQLFreeStmt() to free the resources allocated to the statement handle. Listing 48.6 provides an example of the calls to these functions.

Listing 48.6. Applying a transaction using embedded SQL.

 Dim hStmt        As Long ` Allocate a statement handle iError = SQLAllocStmt(hDBc, hStmt) For i = 0 To iStmts     ` Apply SQL     iError = SQLExecDirect(hStmt, szSQL(i), SQL_NTS)     If iError Then         ` Rollback         iNextErr = SQLTransact(hEnv, hDB, SQL_ROLLBACK)         Exit For     End If Next i If (iError = 0) Then     ` Commit     iError = SQLTransact(hEnv, hDB, SQL_COMMIT) End If ` Free the statement handle iError = SQLFreeStmt(hStmt, SQL_DROP) 

Previous Table of Contents Next