Updating Database Records

The following example uses the ExecuteSQL method to execute an update statement.

Set OO4OSession = CreateObject("OracleInProcServer.XOraSession")

Set EmpDb = OO4OSession.OpenDatabase("ExampleDb", "scott/tiger", 0)

EmpDb.Parameters.Add "ENAME", "JONES", ORAPARM_INPUT

EmpDb.ExecuteSQL ("UPDATE emp SET sal = sal + 1000

WHERE ename = :ENAME")

Another way to execute the update statement is to use the CreateSQL method:

Set sqlStatement = EmpDb.CreateSQL("UPDATE emp SET sal = sal + 1000

WHERE ename = :ENAME", 0&)

Both ExecuteSQL and CreateSQL execute the update statement given.

The difference is that, CreateSQL returns a reference to an OraSQLStmt interface, in addition to executing the statement. This interface can later be used to execute the same query using the Refresh method. Because the query has already been parsed by the server, subsequent execution of the same query results in faster execution, especially if bind parameters are used.

For example, to increase the salary of another employee whose name is KING by 1000, change the value of the place holder and refresh the sqlStatement object as follows:

EmpDb.Parameters("ENAME").Value = "KING"

sqlStatement.Refresh

Using parameters with OraSqlStmts for DML statements that are frequently executed is a more optimized method than using the ExecuteSql statement repeatedly. When the refresh method of the OraSqlStmt is executed, the statement no longer needs to be parsed by the database server. In application servers, such as Web servers where the same queries with different parameters values are executed frequently and for a long period of time, this can lead to significant savings in Oracle database server processing.