0974-0976

Previous Table of Contents Next

Page 974

The following is another example that uses the CreatePlsqlDynaset method to retrieve all the rows in the department table:

 `ORAPARM_OUTPUT, ORATYPE_NUMBER are constants whose values can be found in `the oraconst.txt in the directory where OO4O is installed. `Declare the dynaset object variable Dim deptCursorDynaset As Object `Create the dynaset Set OraDynaset = OraDatabase.CreatePLSQLDynaset("Begin Department.GetDepartments (:DeptCursor); end;", "DeptCursor", &H0&) `Now deptCursorDynaset is used as an ordinary dynaset while not deptCursorDynaset.EOF    msgbox deptCursorDynaset!dname wend 

Here is the definition of the procedure GetDeptartments:

 procedure GetDeptartments(DeptCursor in out deptCursor) is begin       open deptCursor for select * from dept; end GetDeptartments; 

The deptCursor type is defined in the department package as the following:

 cursor c1 is  select * from dept; type deptCursor is ref cursor return c1%rowtype; 

The OraDynaset Edit, Update, and AddNew Methods

The OraDynaset interface provides methods for adding new rows and updating and deleting the rows it contains. These methods can be invoked only if the dynaset updatable property is true. This property is set to false under the following scenarios:

• The READ_ONLY option is used in the CreateDynaset method.
• CreatePlsqlDynaset is used to create the dynaset.
• The SQLSELECT statement query is a join or retrieves rows from a read-only table.
• None of the columns in the dynaset represent a column in the base table or view that allows updates.

All modification operations are performed to the current row in the dynaset.

The following example uses the Delete method to delete the current row in the database:

 Employees = empDatabase.CreateDynaset("Select * from emp where ename = `CLARK'", &H0&) Employees.Delete `Deletes the current row viewed in the database 

Page 975

After the Delete operation is successful, the current row position becomes invalid. The current row position remains invalid until one of the navigational functions such as MoveNext is called.

To update the current row, do the following:

1. Call the Edit function to signal the start of an edit operation.
2. Modify the column values.
3. Call the Update method to flush the changes to the database:

 Employees = empDatabase.CreateDynaset("Select * from emp where ename = `CLARK'", &H0&) Employees.Edit Employees.Fields("sal").Value = 5000 `Set CLARK's salary to 00 
 Empoyees.Update ` Flush this change to the database 

When the Edit method is called, OO4O locks the current row in the database and compares the original column values kept in the local cache to the values that it fetches from the database (using the SELECT ... FOR UPDATE statement). If the values are the same (the row has not been updated in the database by someone else), the row is locked; otherwise , an error is issued. This row remains locked until the Update method is called.

To insert rows into the base table from which the dynaset rows originated, call the AddNew method, set the column values, and call Update.

The following example demonstrates an update operation in OraDynaset.

 Employees.AddNew Employees.Fields("EMPNO").Value = "1000" Employees.Fields("ENAME").Value = "RODMAN" Employees.Fields("JOB").Value = "SALESMAN" Employees.Fields("MGR").Value = "7698" Employees.Fields("HIREDATE").Value = "20-AUG-90" Employees.Fields("SAL").Value = 9000 Employees.Fields("COMM").Value = 1000 Employees.Fields("DEPTNO").Value = 30 Employees.Update 

Do not use this method in batch insert situations. It is much more efficient to use parameter arrays with the CreateSQL methods for that pupose.

LONG and LONG RAW Column Support

LONG and LONG RAW columns in an Oracle 7.x database can contain up to 2 ^{31 -1} bytes of data. They can represent large text (such as an entire book) or binary data, such as images, audio, and video segments.

Due to their potentially large size, additional methods (GetChunk and AppendChunk) are provided by the OraField interface of a dynaset to read from and write to these columns in pieces, if the size of the column data exceeds 64KB.

Page 976

The GetChunk method of OraField is used to retrieve pieces that are 64KB or less in a dynaset.

The GetChunk method has the following syntax:

 GetChunk(offset, numbytes) 

where offset is the zero-based offset of the first byte in the LONG type column to be fetched and numbytes is the number of bytes to read. numbytes should not exceed 64KB.

It is more efficient to retrieve data from low to high offsets than high to low, or in a random fashion.

The following code fragment demonstrates retrieving the content of a LONG column in 10KB pieces. The CreateDynaset method that executes the query to retrieve the LONG column is skipped to keep the code fragment small:

 i = 0 ChunkSize = 10240     `retrieve 10K chunks set blob = OraDynaset.Fields("blob_column") `Keep getting 10K chunks until the size of the last chunk retrieved is less than '10K Do  CurChunk = blob.GetChunk(i * ChunkSize, ChunkSize)  CurSize = Len(CurChunk) `Get the length of the previous piece.  `Do something with the piece of data fetched   ...  i = i + 1 Loop Until CurSize < ChunkSize 

AppendChunk is used to perform a piece-wise insert of data into long columns. It takes a value (string or binary) as its input and appends it to a local buffer that it maintains. The content of the buffer is flushed to the database when the Update method of the dynaset is called.

The sample below demonstrates the AppendChunk method:

 `Calculate the number of times AppendChunk should be called to write the `entire content. NumChunks = TotalSize / ChunkSize `Figure out the remainder RemChunkSize = TotalSize Mod ChunkSize `Append all the data to the local buffer For I = 0 To NumChunks `Calculate the new chunk size.    If I = NumChunks Then       ChunkSize = RemChunkSize    End If    `Get a chunk of data - i.e. read the next chunk from a file    `Append the piece    dynaset.Fields("AUDIO").AppendChunk (CurChunk) Next I `Flush the content to the database dynaset.Update 

Previous Table of Contents Next