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.