 |
 |
| Index Search Add FAQ Ask Question |
|---|
$Date: 08-Jun-2002 $
$Revision: 1.14 $
$Author: Frank Naudé $
One can bind grids, text box widgets, and so on to OO4O via its own Data Control, one can also create dynasets, execute stored procedure code, or just about any other Oracle command one can think of. Oracle Objects consists of the following three components:
OO4O closely follows the ODBC query interface model, one can retain over 95% code compatibility between the OO4O and ODBC versions of data layer procedures. This translates to very short retooling times when porting between OO4O and ODBC.
| OraSession | The first top-level object needed before we can connect to an Oracle database. |
| OraServer | Represents a physical connection to an Oracle database server instance. The OpenDatabase function can be used to create client sessions by returning an OraDatabase object. |
| OraDatabase | Represents a single login to an Oracle database. Similar to the ADO Connection object. OraDatabase objects are returned by the OraSession.OpenDatabase function. |
| OraConnection | Returns various pieces of user information about the current OraSession object. It can be shared by many OraDatabase objects, but each OraDatabase must exist in the same OraSession object. |
| OraDynaset | Similar to an ADO Recordset object. Represents the results retrieved by a call to the OraDatabase.CreateDynaset function. |
| OraField | Represents a column of data within an OraDynaset object. Similar to the ADO Field object of an ADO Recordset. |
| OraClient | Automatically created by OO4O as needed. Maintains a list of all active OraSession objects currently running on the workstation. |
| OraParameter | Represents a bind variable for a SQL statement or PL/SQL block to be executed using the OraDynaset object. Similar to the Parameter object in an ADO Command object. |
| OraParamArray | Allows arrays of parameters to be set for the OraDatabase.Parameters function. |
| OraSQLStmt | Represents a single SQL statement. Typically used with SQL statements that include bind variables to improve performance as Oracle does not have to parse the statement each time it is executed. Can be thought of as conceptually similar to the ADO Command object. |
| OraMetaData | Returns meta data to describe a particular schema such as column names. Similar to the SQL Server DMO object library. See the meta data example below. |
| OraAQ | The CreateAQ method of the OraDatabase returns an OraAQ object. This provides access to Oracle's Advanced Queuing message system that allows messages to be passed between applications, much like MSMQ. |
Dim OraSession As Object 'Declare variables as OLE Objects
Dim OraDatabase As Object
Dim OraDynaset As Object
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.DbOpenDatabase("SQL*Net_Connect_String", "scott/tiger", 0&)
MsgBox "Connected to " & OraDatabase.Connect & "@" & OraDatabase.DatabaseName
MsgBox "OO4O Version: " & OraSession.OIPVersionNumber
MsgBox "Oracle Version: " & OraDatabase.RDBMSVersion
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.DbOpenDatabase("SQL*Net_Connect_String", "scott/tiger", 0&)
Set OraDynaset = OraDatabase.DbCreateDynaset("select empno, ename from emp", 0&)
MsgBox "Employee " & OraDynaset.Fields("empno").value & ", #" & OraDynaset.Fields("ename").value
One can also loop through the result set using code like this:
Do While(OraDynaset.EOF = FALSE)
Response.write(OraDynaset.Fields("TNAME") & "<BR>")
osRecordSet.MoveNext
Loop
| 1 | ORAPARM_INPUT | Use as input variable only |
| 2 | ORAPARM_OUTPUT | Use as output variable only |
| 3 | ORAPARM_BOTH | Use the variable for input and output |
sqlStmt = "SELECT tname FROM tab WHERE tname LIKE :var1"
OraDatabase.Parameters.add "var1", "%A%", 1 ' Substitute Name=Value of type ORAPARM_INPUT
Set osRecordSet = OraDatabase.DbCreateDynaset(sqlStmt, cint(0))
Do While(osRecordset.EOF = FALSE)
' Use data: osRecordset.Fields("TNAME") in this example...
osRecordSet.MoveNext
Loop
rowcount = OraDatabase.ExecuteSQL("create table X (A date)")
rowcount = OraDatabase.ExecuteSQL("insert into X values (sysdate)")
OraSession.CommitTrans ' or OraSession.Rollback
sql1 = "begin test_sproc(param2=>'two_b', param1=>'one_b'); end;" rowcnt = OraDatabase.DbExecuteSQL(sql1)More complex example using input and output parameters. Note that the output parameter returns a cursor:
OraDatabase.Parameters.Add "NAME", "SUPERSPORT", ORAPARM_INPUT
OraDatabase.Parameters("NAME").ServerType = ORATYPE_VARCHAR2
OraDatabase.Parameters.Add "ORDCURSOR", 0, ORAPARM_OUTPUT
OraDatabase.Parameters("ORDCURSOR").ServerType = ORATYPE_CURSOR
Set OraSQLStmt = OraDatabase.CreateSql("begin CUSTOMERS.GetCutomerSalesOrder(:Name,:OrdCursor);end;", ORASQL_FAILEXEC)
Set OrderDynaset = OraDatabase.Parameters("ORDCURSOR").Value
'Now display the Dynaset's field value
MsgBox "Order Details are " & OrderDynaset.fields("ordid").Value & " " &
Response.write("<H1>Describe Table EMP:</H1>")
Set objOraMetaData = OraDatabase.Describe("EMP")
Set objOraMDAttribute = objOraMetaData("ColumnList")
Set objColumnList = objOraMDAttribute.Value
For iColCount = 0 To objColumnList.Count - 1
Set objColumnDetails = objColumnList(iColCount).Value
Response.Write "<P >Column: " & objColumnDetails("Name") & _
"<BR>Type: " & objColumnDetails("DataType") & _
"<BR>Size: " & objColumnDetails("DataSize") & _
"<BR>IsNull: " & objColumnDetails("IsNull") & _
"<BR>Precision: " & objColumnDetails("Precision") & _
"<BR>Scale: " & objColumnDetails("Scale")
Next
|
|