Oracle Objects for OLE Version 2.3

Copyright	Other Information Sources

Introduction

Overview

Requirements

New Features in Version 2.2 and 2.3

Getting Started

Objects and their Relationships

Error Handling

Quick Tour

The OLE Server

About the OLE Server

Objects

Methods

Properties

The Data Control

About the Data Control

Events

Methods

Properties

Oracle-Specific Notes

Locks and Editing

Long and Long Raw Columns

Transactions

PL/SQL Supports

Technical Notes

Coding Techniques

Enhancing Performance

Error Handling

Method And Property Name Conflicts

Oracle Objects for OLE and Visual Basic

Redistributable Files

Sample Code Conventions

Troubleshooting

Tuning and Customization


Modification History

Version 1.0.42.0	December 1994

Finalized Oracle Objects for OLE

Version 1.0.53.0 (Internal)	March 27, 1995

Added Modification History Topic

Fixed references to COUNT property of the OraParameters Collection

Modified Troubleshooting Topic and added section about duplicate DLLs

Added Copyright Topic

Removed Add/Remove methods from OraParameter Object since these are only valid for the OraParameters Collection

Fixed typos in the OraSession Remarks section

Fixed underline typo at the end of the Transactions topic

Fixed typo in the Redistributable file topic

Changed reference of method to property in the RecordCount property

Fixed GetChunk Link in See Also of Error Handling Topic

Fixed all See Also/Properties/Methods in OLESERV.DOC to be consistent as far as not repeating the words property/method, etc.

Fixed alpha ordering of See Also in FieldSize Method

Fixed alpha ordering of See Also in Move Methods

Changed CreateNameSession to CreateNamedSession

Added code to the CreateNamedSession Example

Changed code samples to use DbOpenDatabase instead of OpenDatabase

Added See Also to the following Data Control properties: BackColor, DragIcon, DragMode, Enabled, FontBold, FontItalics, FontName, FontSize, FontStrikethru, FontUnderline, ForeColor, Height, Index, Left, MousePointer, Top, Visible, Width

Added See Also to the following Data Control Methods: Drag, Move

Added See Also to the following Data Control Events: DragDrop, DragOver, Error, MouseDown, MouseMove, MouseUp, Reposition, Validate

Added extra information to the RecordCount property about nocache

Added ORADYN_NOMOVEFIRST option to CreateDynaset

Fixed ConnectSession & Example, CreateNamedSession & Example and CreateSession about no cross application session sharing

Added version numbers for C++ compilers to the Requirements topic.

Updated Copyright topic.

Added the return value to ExecuteSQL

Added some clarification to the Data Aware Controls section

Updated the "OO4O and VB" section

Version 1.0.55.0	April 1995

Added new values to the Type property and rearranged the table

Added more information to "Data Aware Controls"

Added Standard/Pro to Requirements for VB3

Added information to AddNew/Edit/Delete about canceling each other

Removed NoMoveFirst option of CreateDynaset

Rephrased the AddNew/Delete/Edit canceling each other sentences in each of those topics

Updated Add, Remove, and ExecuteSQL methods, and ServerType property examples to show how a stored function would be called.

Version 1.0.57.0	May 1995

Updated the Data Aware Controls topic to add that the MS Checkbox doesn't work and fixed the Spinoza check box info.

Added the OralDataType, OraMaxDSize, OraMaxSize, OraNullOK, OraPrecision, and OraScale properties to the OraField object. Modified all necessary property tables.

Added a clarification about columns of type NUMBER to the Type property.

Version 2.0.1.0	December 1995

Added topic describing Oracle Objects for OLE 2.0 features and changes from Version 1.0.

Version 2.2.0

Added topics on OCX data control, CopyToClipboard, and CreatePlsqlDynaset methods.


Copyright

Oracle Objects for OLE, Version 2.3

Copyright (c) Oracle Corporation 1994-1998

Authors:  Prabha Krishna Chari, Gopal Kirsur, Mahadevan Lakshminarayanan, Denis Raphaely, Nasser Shariatpanahy, Christian Shay.

This software was not developed for use in any nuclear, aviation, mass transit, medical, or other inherently dangerous applications. It is the customer's responsibility to take all appropriate measures to ensure the safe use of such applications if the programs are used for such purposes.

This software/documentation contains proprietary information of Oracle Corporation; it is provided under a license agreement containing restrictions on use and disclosure and is also protected by copyright law. Reverse engineering of the software is prohibited. If this software/documentation is delivered to a U.S. Government Agency of the Department of Defense, then it is delivered with Restricted Rights and the following legend is applicable:

Restricted Rights Legend Use, duplication, or disclosure by the Government is subject to restrictions as set forth in subparagraph (c)(1)(ii) of DFARS 252.227-7013, Rights in Technical Data and Computer Software (October 1988).

Oracle Corporation, 500 Oracle Parkway, Redwood City, CA  94065.

If this software/documentation is delivered to a U.S. Government Agency not within the Department of Defense, then it is delivered with "Restricted Rights," as defined in FAR 52.227-14, Rights in Data - General, including Alternate III (June 1987).

The information in this document is subject to change without notice. If you find any problems in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this document is error-free.

Oracle is a registered trademarks of Oracle Corporation. Windows, Windows NT, Visual C++, Visual Basic, and OLE are trademarks of Microsoft Corporation. All other trade names referenced are the service mark, trademark, or registered trademark of the respective manufacturer.


Other Information Sources

The following Oracle publications contain more information about various topics mentioned here:

	Oracle8 Server Application Developer's Guide
	Oracle8 Server Concepts Manual
	Oracle8 Server SQL Language Quick Reference
	Oracle8 Server SQL Language Reference Manual
	PL/SQL User's Guide and Reference
	PL/SQL V2.2 and Precompiler's V1.6 Addendum
	Oracle8 Server Documentation Addendum
See Also: Oracle Objects for OLE C++ Class Library Help


Requirements

See Also

Design Time

•	The Oracle Object Server requires an application that supports OLE Automation such as:

Visual Basic 4.0 (Standard or Professional)

Excel 5.0/7.0

Access 2.0

•	The Oracle Data Control requires Visual Basic 4.0 (Standard or Professional).

•	The Oracle Objects for OLE C++ Class Library requires the following compiler Microsoft Visual C++ Version 2.X or later

Neither the OLE SDK nor OLE development knowledge is necessary.

Run Time

•	For 32-bit platforms, Windows 95 or Windows NT.

•	A local or remote Oracle8 database.

•	Oracle SQL*Net Version 1.x or 2.x if connecting to a remote Oracle8 or Oracle8 database. Note that OO4O v2.3 and earlier versions do not support all the new Oracle8 features such as ADTs, REFs, and so on.

•	Microsoft OLE 2.0.1 or greater run time files (included with the Oracle Objects for OLE installation).

Note

•	Oracle Objects for OLE v2.3 does not support Oracle8 object features.


See Also

Oracle Data Control

Oracle Object Server

Redistributable Files


Overview

See Also

Oracle Objects for OLE

Oracle Objects for OLE is a collection of programmable objects that simplifies the development of applications designed to communicate with an Oracle8 database (see Objects and their Relationships ). Oracle Objects for OLE is particularly well suited for any programming environment that supports Visual Basic custom controls or OLE Automation. Oracle Objects for OLE consists of three principal components: the Oracle Object Server, the Oracle Data Control, and the Oracle Objects for OLE C++ Class Library.

The Oracle Object Server

The Oracle Object Server is an OLE "In Process" server that supports a collection of programmable objects for Oracle8 databases running either locally or remotely. An OLE In Process server is a special kind of OLE server, running in a Windows DLL, that supports the OLE Automation interface. An OLE In Process server has no user interface and is not embeddable. You can access the Oracle Object Server through the Oracle Data Control, through any application that supports OLE Automation (such as in Visual Basic for Applications, in applications such as Microsoft Excel Version 5.0/7.0 and Access 2.0), and through the Oracle Objects for OLE C++ Class Library.

The Oracle Data Control

The Oracle Data Control is a Visual Basic custom control for use with development tools that support custom controls. The Oracle Data Control is compatible with the Microsoft data control included with Visual Basic. If you are familiar with that data control, learning to use the Oracle Data Control is quick and easy.

The Oracle Objects for OLE C++ Class Library

The Oracle Objects for OLE C++ Class Library is a collection of C++ classes that provide programmatic access to the Oracle Object Server. Although the class library is implemented using OLE Automation, neither the OLE development kit nor any OLE development knowledge is necessary to use it. In addition to the object classes, the class library provides a bound class, which allows controls such as text and list boxes to be linked directly to a field of a dynaset (columns in the database). The bound class supports late, runtime binding, as is available in Visual Basic. The Oracle Objects for OLE C++ Class Library is supported for Microsoft Visual C++ and (for the bound class) the Microsoft Foundation Classes.

Available Objects

•	OraClient  An OraClient object defines a workstation domain and manages collections of OraSession objects.

•	OraSession  An OraSession object manages collections of OraDatabase objects and provides connection sharing and transactional control.

•	OraConnection An OraConnection object represents a single connection to an Oracle database.

•	OraDatabase An OraDatabase object represents a single virtual login to an Oracle database.

•	OraParamArray An OraParamArray object represents an array of parameters.

•	OraParameter  An OraParameter object represents a bind variable in a SQL statement or PL/SQL block.

•	OraDynaset  An OraDynaset object represents the rows and columns returned from a SQL SELECT statement.

•	OraSQLStmt An OraSqlStmt object represents a single SQL statement.

•	OraField An OraField object represents a single column within a row of an OraDynaset object.

There are objects that represent collections of OraSession, OraConnection, OraParameter, and OraField objects.

Oracle Objects for OLE, OLE, and the Oracle Database

Figure 1 shows the high-level relationship between the Oracle Data Control, the Oracle Objects for OLE C++ Class Library, the Oracle Object Server, OLE, and the Oracle8 database.


See Also

Oracle Data Control

Oracle Object Server

OraConnections Collection

OraFields Collection

OraParameters Collection

OraSessions Collection


New Features in Version 2.2 and 2.3

Thread Safety

OO4O version 2.2 and higher is thread-safe and can be used effectively in multithreaded applications and environments such as such as the Microsoft Internet Information Server (IIS). OO4O supports both the free and apartment threading models in COM/DCOM.

Access to OO4O object attributes are serialized when used with multiple threads of execution. To achieve maximum concurrency in query execution in a multithreaded application with OO4O, avoid sharing objects in multiple threads .

You should also avoid using commit and rollback operations on a session object that is shared among multiple threads since all connections associated with that session will be committed or rolled-back. If you want to perform commit and rollback operations on a session object, you should create a unique session object for each database object used.

Using the Connection Pool Management Facility

OO4O 2.3 introduces a new interface for pooling of database connections. The pool may be created with zero or more OraDatabase objects and can grow to a maximum that is specified at the time the pool is created. Connections in the pool are automatically closed if not used for a specified time.

This facility is essentially a resource manager for OraDatabase objects containing database connections. This facility maintains the open state on frequently used objects, and thereby eliminates the need to continuously create and destroy connections. It is particularly useful in heavily used mid-tier application server components such as ASP scripts in IIS that connect to Oracle databases to execute queries on every invocation. New OraDatabase objects are created only if there are more objects required than are available in the pool, thus, significantly improving performance and scalability.

For an example of this in IIS, please refer to the ASP sample directory.

PL/SQL Multi-Cursor Return

Versions of OO4O from v2.2 can return multiple cursors to the client from a single query. This is useful, for instance, if a user needs the result set of two disjointed queries.

This feature improves performance by lowering network traffic. OO4O's PL/SQL table return capability can send (or receive) an array of values with a single network transaction. Only a single network round-trip is required for returning data from multiple cursors.

Enhancing Performance

Field collection access in a dynaset is improved. If loops are not coded properly, the system is optimized to create default field collection objects so that these objects are not created and destroyed with each dynaset iteration.

The help file now contains a number of tips and techniques for improving performance. Coding methods, such as early binding and dynaset cache tuning, can significantly improve performance.

A number of bug fixes are included. The eradication of some bugs, such as very small memory leaks, represent significant gains when OO4O is used in a 7-by-24 environment.

General

OO4O is available on Windows '95 and NT, and can run in an NT server environment. There will not be any 16-bit version from OO4O 2.2 onwards, and the current 16-bit OO4O 2.1 has now entered maintenance mode.


Relationships


Object Relationships
Objects and their Relationships

An operational hierarchy of the objects expresses "has-a" and "belongs-to" relationships. This hierarchy can be drawn as follows:

The "crow's feet" indicate the "many" ends of one-to-many or many-to-one relationships.

Each OraClient object can have many OraSession objects.

Each OraSession object can be associated with only one OraClient object.

Each OraSession object can have many OraConnection objects.

Each OraConnection object can be shared by many OraDatabase objects, although these OraDatabase objects must be within the same OraSession object.

Each OraDatabase object belongs to only one OraSession object.

Each OraDynaset object belongs to only one OraDatabase object.

Each OraSQLstmt object belongs to only one OraDatabase object.

Each OraField object belongs to only one OraDynaset object.

Each OraParameter object belongs to only one OraDatabase object.

Each OraParameterArray object belongs to only one OraDatabase object.

You can create the OraSession, OraDatabase, OraDynaset, and OraParameter objects explicitly; the OraClient, OraConnection, and OraFields objects are created implicitly when necessary.

One OraClient object exists per workstation. This object is created when the first OraSession object is created. An OraConnection object may be created when an OraDatabase is created if that OraDatabase is not sharing a previously created connection. One OraField object is created per database column of your SQL SELECT statement when an OraDynaset is created. OraParameter objects are user created.


See Also

OraClient Object

OraConnection Object

OraDatabase Object

OraDynaset Object

OraField Object

OraParameter Object

OraSession Object


Getting Started

Oracle Objects for OLE is designed to provide access to the data of an Oracle database using the dynaset object. The dynaset object is not the topmost object according to the Object Relationship diagram of Oracle Objects for OLE. This means that you must create or instantiate all of the objects a dynaset depends on (client, session, connection, and database)

Using OLE Automation

When accessing the Oracle Object Server using the OLE Automation interface, you must create each object explicitly (except for the client object, which is always created automatically). The following code fragment demonstrates how to create first all of the objects required by a dynaset and then the dynaset itself.

...

'Declare variables as OLE Objects.

Dim OraSession As Object

Dim OraDatabase As Object

Dim OraDynaset As Object

'Create the OraSession Object. Notice that this is the

'only object created via the CreateObject method. The

'argument to CreateObject is the name by which the

'OraSession object is known to the OLE system.

Set OraSession = CreateObject("OracleInProcServer.XOraSession")

'Create the OraDatabase Object by opening a

'connection to Oracle.

Set OraDatabase = OraSession.DbOpenDatabase("ExampleDb", "scott/tiger", 0&)

'Create the OraDynaset Object.

Set OraDynaset = OraDatabase.DbCreateDynaset("select * from emp", 0&)

'You can now display or manipulate the data in the dynaset

'named 'OraDynaset'.

...

Using the Oracle Data Control

To use the Oracle Data Control with Visual Basic 4.0, create a new project and then use the Custom Controls option of the Tools menu to add "Oracle Data Control" to your project. The Oracle Data Control will be added to your Visual Basic tool palette and will look like this:

The Oracle Data Control makes creating a dynaset easier, because it does not require you to create the underlying objects. When the Oracle Data Control is refreshed, a client (if needed), session, database, and dynaset are created automatically.

The following code fragment demonstrates how to programmatically set the properties of the Oracle Data Control required to create a dynaset. Alternatively, you could set these properties by way of the Properties window of Visual Basic.

...

 'Set the username and password.

 oradata1.Connect = "scott/tiger"

 'Set the databasename.

 oradata1.DatabaseName = "ExampleDb"

 'Set the recordsource.

 oradata1.RecordSource = "select * from emp"

 'Refresh the data control.

 oradata1.Refresh

...

You now have a valid client, session, database, and dynaset. The client, session, database, and dynaset are referenced using oradata1.database.session.client, oradata1.database.session, oradata1.database, and oradata1.recordset, respectively.

Using Microsoft C++

Please read the Oracle Objects for OLE C++ Class Library documentation for further details of using C++ with Oracle Objects for OLE.


See Also

Objects and their Relationships

Oracle Data Control

OraClient Object

OraConnection Object

OraDatabase Object

OraDynaset Object

OraField Object

OraSession Object


Error Handling

See Also

OLE Automation Errors

The programmatic interface of the Oracle Object Server is OLE Automation. Therefore, errors that occur during execution of methods are frequently reported simply as an OLE Automation Error (ERR = 440, ERROR$ = "OLE Automation Error"). If such an error occurs, you should check the LastServerErr property of the OraSession and OraDatabase objects to determine whether an Oracle database error has occurred. If LastServerErr is non-zero, then an error has been raised by the Oracle Object Server. To find the Oracle Object Server Error, scan the string returned by the ERROR$ function for the string "OIP-NNNN" where NNNN is some error number included in the table below.

Constant	Value	Description
OERROR_ADVISEULINK	4096	Internal error: Invalid advisory connection.
OERROR_POSITION	4098	An attempt was made to retrieve a field value from an empty dynaset.
OERROR_NOFIELDNAME	4099	An invalid field name was specified.
OERROR_TRANSIP	4101	A BeginTrans was specified while a transaction is already in progress
OERROR_TRANSNIPC	4104	A CommitTrans was specified without first executing BeginTrans.
OERROR_TRANSNIPR	4105	A Rollback was specified without first executing BeginTrans.
OERROR_NODSET	4106	Internal error: System attempted to destroy non-existent dynaset.
OERROR_INVROWNUM	4108	An attempt was made to reference an invalid row. This will happen when EOF or BOF is True or when the current row has been deleted and no record movement has occurred.
OERROR_TEMPFILE	4109	An error occurred while trying to create a temporary file for data caching.
OERROR_DUPSESSION	4110	An attempt was made to create a named session using CreateSession or CreateNamedSession that already exists.
OERROR_NOSESSION	4111	Internal error: System attempted to destroy non-existent session.
OERROR_NOOBJECTN	4112	An attempt was made to reference a named object of a collection (other than the fields collection) that does not exist.
OERROR_DUPCONN	4113	Internal error: Duplicate connection name.
OERROR_NOCONN	4114	Internal error: System attempted to destroy non-existent connection.
OERROR_BFINDEX	4115	An invalid field index was specified. The range of indices is 0 to Count -1.
OERROR_CURNREADY	4116	Internal error: System attempted to move to row in invalid dynaset.
OERROR_NOUPDATES	4117	An attempt was made to change the data of a non-updatable dynaset.
OERROR_NOTEDITING	4118	An attempt was made to change a fields value without first executing Edit.
OERROR_DATACHANGE	4119	An attempt was made to Edit data in the local cache, but the data on the Oracle server has been changed.
OERROR_NOBUFMEM	4120	Out of memory for data binding buffers.
OERROR_INVBKMRK	4121	An invalid bookmark was specified.
OERROR_BNDVNOEN	4122	Internal error: Bind variable not enabled.
OERROR_DUPPARAM	4123	An attempt was made to create a named parameter using Add, but that name already exists.
OERROR_INVARGVAL	4124	An invalid offset or length parameters was passed to GetChunk or an internal error has occurred using AppendChunk.
OERROR_INVFLDTYPE	4125	An attempt was made to use GetChunk or Append Chunk on a field that was not of the type Long or Long Raw.
OERROR_INVARG	4126	An Invalid argument value was entered.
OERROR_TRANSFORUP	4127	A SELECT ... FOR UPDATE was specified without first executing BeginTrans.
OERROR_NOTUPFORUP	4128	A SELECT ... FOR UPDATE was specified but the query is non-updatable.
OERROR_TRANSLOCK	4129	A Commit or Rollback was executed while a SELECT ... FOR UPDATE is in progress.
OERROR_CACHEPARM	4130	An invalid cache parameter was specified. Note that the maximum value for CacheBlocks is 127.
OERROR_FLDRQROWID	4131	An attempt was made to reference a field that requires a ROWID (Long or Long Raw), but the ROWID was not available.
OERROR_OUTOFMEMORY	4132	Internal Error
OERROR_MAXSIZE	4135	Element size specified in AddTable exceeds the maximum allowed size for that variable type. See AddTable Method for more details.
OERROR_INVDIMENSION	4136	Dimension specified in AddTable is invalid (ie. negative). See AddTable Method for more details.
OERROR_MAXBUFFER	4137	Buffer size for parameter array variable exceeds 32512 bytes (OCI limit).
OERROR_ARRAYSIZ	4138	Dimensions of array parameters used in insert/update/delete statements are not equal.
OERROR_ARRAYFAILP	4139	Error processing arrays. For details refer to OO4OERR.LOG in the windows directory.
OERROR_CREATEPOOL	4147	Database Pool Already exists for this session.
OERROR_GETDB	4148	Unable to obtain a free database object from the pool.

These values can be found in the file ORACONST.TXT.

Find Method Parser Errors

Find method parser errors occur when the parser cannot parse the expression (find clause) in a Find method. These errors specify what part of the expression caused the error.

Constant	Value	Description
OERROR_STOVER	4496	Stack overflow.
OERROR_SYNTAX	4497	Syntax error
OERROR_MISPLC	4498	Misplaced parenthesis
OERROR_MISQTE	4499	Misplaced quotation marks
OERROR_MISPAR	4500	WARNING: Missing closing parenthesis
OERROR_EXPCTD	4501	Usually open parentheses expected
OERROR_PRSERR	4502	Unknown parser error condition
OERROR_DUMMY	4503	Syntax not supported
OERROR_INVCOL	4504	Invalid column name
OERROR_TOKEN	4505	Maximum token size exxceed in token.
OERROR_COLTYPE	4506	Unsupported data type
OERROR_UNXPTD	4507	Unexpected token found.
Find Method Runtime Errors

Find method runtime errors occur when the system cannot evaluate a find expression. Such erorrs are rare. When one occurs, the parser might have generated incorrect code.

Constant	Value	Description
OERROR_INSTR	4516	Internal error: Invalid instruction
OERROR_STACK	4517	Internal error: Stack over/under-flow
OERROR_CONVERT	4518	Invalid type conversion
OERROR_DATA	4519	Invalid datatype
OERROR_SQLFUNC	4520	SQL function missing argument
OERROR_COMP	4521	Invalid comparison
OERROR_SQLEXEC	4522	Select from dual failed
OERROR_SQLTYPE	4523	Invalid datatype in Select from dual
Oracle Errors

The most recent Oracle database error and error text is available from the OraSession or OraDatabase object properties LastServerErr and LastServerErrText.

The LastServerErr and LastServerErrText properties of the OraSession object return all errors related to connections, such as errors on OpenDatabase.

The LastServerErr and LastServerErrText properties of the OraDatabase object return all errors related to an Oracle cursor, such as errors on CreateDynaset and ExecuteSQL.

Oracle Data Control Error

Oracle Data Control errors are specific to oracle data control. During the visual access of data control, Oracle Object server specific errors are reported as OLE Automation Error with error code of ODCERR_AUTOMATION. Oracle Data control specic error code are retrieved from the DataErr parameter of Error() event.

Constant	Value	Description
ODCERR_INITOIP	28000	Intialization of Oracle InProc server is failed. Check the registry for the correct location of Oracle Inproc Server.
ODCERR_OLEQE	28001	Internal error. Querying Inproc server interface is failed.
ODCERR_AUTOMATION	28003	Oracle Inproc server error occured.
ODCERR_NODYNASET	28007	Attempting to access Oracle Data Control prior to initialization.
ODCERR_FIELDINDEX	28009	Bound controls trying to access with invalid field index.
ODCERR_FIELDNAME	28013	Bound controls trying to access with invalid field name.
ODCERR_MEMORY	28014	Internal error. Failure in allocating memory for the Requested bindings from the bound control.
ODCERR_BMKTYPE	28015	Oracle Data Control does not supprt the requested bookmark type.
ODCERR_CONVERSION	28016	Oracle Data Control cannot convert Field Value to Requested type.
ODCERR_SETSESSION	28017	Setting session property is not allowed.
ODCERR_SETDATABASE	28018	Setting database property is not allowed.
ODCERR_BLOBUPDATE	28019	Oracle Data Control does not update Picture or Raw data directly from the bound control. Use AppenChunk () method Method
ODCERR_DYN_NOCACHE	28019	Recordset property cannot be set to a dynaset created with ORADYN_NOCACHE option ( bound control connected to data control often requires bi-directional navigation)
ODCERR_DYN_NOMOVEFIRST	28020	Recordset property cannot be set to a dynaset created with                         ORADYN_NOMOVEFIRST option.


See Also

Add Method

AppendChunk Method

BeginTrans Method

BOF Property

CommitTrans Method

CreateNameSession Method.

CreateSession Method

Edit Method

EOF Property

ExecuteSQL Method

GetChunk Method

LastServerErr Property

LastServerErrPos Property

LastServerErrText Property

Locks and Editing

Long and Long Raw Columns

OpenDatabase Method

OraDatabase Object

OraSession Object

Rollback Method

SELECT ... FOR UPDATE


PLSQL_Supports
PL/SQL Supports

Oracle Objects for OLE provides tight integration with PL/SQL stored procedures . Oracle Objects for OLE (OO4O), supports PL/SQL stored procedures, PL/SQL tables and PL/SQL cursors etc. The PL/SQL bind (in/out) variables are supported through the OraParameter's Add method. The PL/SQL table (in/out) variable is supported through the AddTable method.

The stored procedure block is executed either through the CreateSQLmethod or ExecuteSQL method. Oracle Objects for OLE supports the SQL statement objects for PL/SQL stored procedures.

The Refresh method on this SQL statement object is a faster and easier way to talk to stored procedure for different values for PL/SQL bind (in/out) variables without reparsing the PL/SQL block.

Oracle Objects for OLE returns the cursor created in the stored procedure or anonymous PL/SQL block as a READONLY dynaset object, which is helpful in cases in which the business rules are encapsulated centrally in the Oracle server. In order to do this, you must assign the cursor variable as an OraParameter object of type ORATYPE_CURSOR. After executing the stored procedure, the Value property of this OraParameter object returns a read only dynaset object. For more information see Multiple Cursor Return from a PL/SQL Procedure . This dynaset object can be treated in the same way as other dynaset objects. OO4O also returns a PL/SQL cursor as a read only dynaset object through the CreatePLSQLDynaset method.


Locks and Editing

One of the defining features of client-server computing is that many clients may be accessing the server simultaneously. This feature means that several clients may access the same table or record simultaneously. In Oracle8 this issue is generally resolved using locks. Locks allow one client to restrict other client’s use of a table or record. Locks are placed temporarily on database entities to prevent confusion and data corruption.

When you use Oracle Objects for OLE, locks are not placed on data until an Edit method is executed. The Edit method attempts to obtain a lock (using "SELECT ... FOR UPDATE") on the current record of the dynaset. This is done as late as possible to minimize the time that locks are placed on the records. The Edit method can fail for several reasons:

The SQL query violates the Oracle SQL updatability rules, for instance, by using calculated columns or table joins.

The user does not have the privileges needed to obtain a lock.

The record has been locked already by another user. The OpenDatabase method has an option so that you can decide whether to wait on locks.


See Also

Edit Method

OpenDatabase Method

Using "SELECT ... FOR UPDATE"


Transactions

A transaction is a logical unit of work that comprises one or more SQL statements executed by a single user. A typical example is transferring money from one bank account to another. Two operations take place:

1. Money is taken out of one account.

2. Money is put into the other account.

These operations need to be performed together. If one were to be done and the other not done (for example, if the network connection went down), the bank’s books would not balance correctly.

Normally, when you execute an Update method on a dynaset, the changes are committed to the database immediately. Each operation is treated as a distinct transaction. Using the BeginTrans, CommitTrans, and Rollback transactional control methods of the OraSession object allows operations to be grouped into larger transactions. BeginTrans tells the session that you are starting a group of operations. CommitTrans makes the entire group of operations permanent. Rollback cancels the entire group. CommitTrans and Rollback end the transaction and the program returns to normal operation: one transaction per operation. Experienced Oracle users should note the following differences between the operation of Oracle Objects for OLE and many Oracle tools:

Oracle tools such as SQL*Plus execute as if the BeginTrans method was called when the tool was started. This means that updates are not committed immediately, but are held until a commit or rollback is executed.

SQL*Plus always starts a new transaction every time a commit or rollback is executed.

The autocommit setting in SQL*Plus results in behavior similar to the default of the Oracle Objects for OLE.

SQL*Plus does not take a row lock in the case of a failed Update or Delete statement. However, in the case of OO4O, if Update or Delete methods fail on a given row in a dynaset in a global transaction (i.e. in cases in which you issued a BeginTrans), be aware that locks will remain on those rows on which you called Update or Delete. These locks will persist until you call CommitTrans or Rollback.

If you are connected to more than one database and use the transaction methods, you should understand that Oracle Objects for OLE commits each database separately. This is not the same as the two-phase commit that Oracle8 provides. If your application needs to guarantee data integrity across databases, you should connect to a single database and then access additional databases by way of the Oracle8 database link feature. This method gives you the benefit of Oracle8’s two-phase commit. Consult your Oracle8 documentation for more information about two-phase commit, database links, and distributed transactions.

Transactions apply only to the Data Manipulation Language (DML) portion of the SQL language (such as INSERT, UPDATE, and DELETE). Transactions do not apply to the Data Control Language (DCL) or Data Definition Language (DDL) portions (such as CREATE, DROP, ALTER, etc.) of the SQL language. DCL and DDL commands always force a commit, which in turn commits everything done before them.


See Also

BeginTrans Method

CommitTrans Method

OraConnection Object

OraSession Object

ResetTrans Method

Rollback Method


Long and Long Raw Columns

Putting

Long and long raw columns of an Oracle database can contain up to 2 gigabytes of data. Data of less than 64K bytes can be put into the database using simple assignment, but data exceeding 64K bytes must be put into the database using AppendChunk. The Oracle database libraries do not allow putting of data in multiple pieces, so the data is held internally and put in one piece upon an Update.

Fetching

Long and long raw columns of an Oracle database can contain up to 2 gigabytes of data. This makes it impractical to retrieve all data from a long or long raw column automatically when it is selected. Instead, the first 64K bytes is retrieved and the Oracle ROWID is cached locally so that the row containing the long or long raw column can be located and then retrieved when using the GetChunk method. If the Oracle ROWIDs cannot be obtained on a query with a long or long raw column, then the long or long raw column will not be accessible and an error will be generated if field access is attempted.

Editing

Before an Edit can be started, a column's locally cached value is compared with its current database value. If the values match, the edit proceeds; otherwise an error is generated. Since long and long raw columns may contain up to 2 gigabytes of data, no comparison is done before an Edit is started.


See Also

AppendChunk Method

FieldSize Method

Edit Method

GetChunk Method

OraDynaset Object

Update Method


"SELECT ... FOR UPDATE"

Normally, when a dynaset is created, rows are not locked in the database until Edit is invoked. If this is not desirable, you might include the FOR UPDATE construct in the SQL SELECT statement. Unfortunately, the FOR UPDATE construct undermines normal dynaset operations, so Oracle does not recommend its use.

Dynasets created with FOR UPDATE are handled correctly in most cases by scanning the SQL statement for the FOR UPDATE construct (This is necessary because the Oracle database functions do not distinguish between SELECT and SELECT FOR UPDATE SQL statements.) It is possible that some exotic FOR UPDATE SQL statement will be treated as "not FOR UPDATE"—meaning that rows are not locked during the lifetime of the dynaset. If the FOR UPDATE is not recognized, rows are locked only during an Edit/Update sequence. However, during the Edit/Update sequence, the row is verified as unchanged before the Edit is permitted.

The use of FOR UPDATE on dynasets requires that a session level transaction be in progress at the time the dynaset is created. Further, before the session can be committed or rolled back, all objects which reference the dynaset must be set to "Nothing" or an error is returned. In the case of a data control, change the record source and Refresh the data control or Recordset.

Note that if an error results and the application terminates, uncommitted data is rolled back, including pending FOR UPDATE dynasets.


See Also

Edit Method

Locks and Editing

Refresh Method

Recordset Property

Update Method


SELECT ... FOR UPDATE Example
''SELECT ... FOR UPDATE'' Example

This example demonstrates the use of SELECT ... FOR UPDATE to lock all the rows of a dynaset while it is being updated. Copy this code into the definition section of a form. Then press F5.

Sub Form_Load ()

 'Declare variables as OLE Objects.

 Dim OraSession As Object

 Dim OraDatabase As Object

 Dim OraDynaset As Object

 Dim fld As Object

 'Create the OraSession Object.

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 'Create the OraDatabase Object by opening a connection to Oracle.

 Set OraDatabase = OraSession.DbOpenDatabase("ExampleDb", "scott/tiger", 0&)

 'Start Transaction processing before creating the dynaset

 'with FOR UPDATE, or an error will occur.

 OraSession.DbBeginTrans

 'Create the OraDynaset Object

 Set OraDynaset = OraDatabase.DbCreateDynaset("select * from emp FOR UPDATE", 0&)

 ' Create a field object for faster access.

 ' This will cause a reference to the Dynaset to be held.

 Set fld = OraDynaset.Fields("sal")

 'Traverse until EOF is reached, setting

 'each employee's salary to zero.

 Do Until OraDynaset.EOF

  OraDynaset.DbEdit

  fld.value = 0

  OraDynaset.DbUpdate

  OraDynaset.DbMoveNext

 Loop

 MsgBox "All salaries set to ZERO."

 'When using FOR UPDATE, you must close (reduce the

 ' reference count to zero) the dynaset by setting it to

 ' Nothing, or an error will occur.

 Set OraDynaset = Nothing

 'You must also set fld to Nothing since it contains a

 ' reference to the dynaset.

 Set fld = Nothing

 'End Transaction processing

 OraSession.DbCommitTrans

End Sub


Tuning and Customization

A number of working parameters of Oracle Objects for OLE can be customized. Access to these parameters is provided through the Windows Registry or the Oracle initialization file, by default named ORAOLE.INI. For Windows 3.1, the parameters can also be set in the ORACLE.INI file (though it will still work on NT and Windows 95).The section name is "OO4O" for all parameter values (when using ORACLE.INI) and value strings remain the same. If the values cannot be found in ORACLE.INI, they are obtained from ORAOLE.INI under the original section names for backward compatibility. The complete order for determining the parameter values is: 1) Registry, 2) ORACLE.INI file, 3) file specified by ORAOLE environment variable (default is ORAOLE.INI).

Tuning and Customization using Oracle Initialization file

The location  of the ORAOLE.INI (default file) file is specified by the ORAOLE environment variable. Note that this variable should specify a full pathname to the Oracle initialization file, which is not necessarily named ORAOLE.INI. If this environment variable is not set, or does not specify a valid file entry, then Oracle Objects for OLE looks for a file named ORAOLE.INI in the Windows directory. If this file does not exist, all of the default values listed will apply.

[Cache Parameters]

A cache consisting of temporary data files is created to manage amounts of data too large to be maintained exclusively in memory. This cache is needed primarily for dynaset objects, where, for example, a single LONG RAW column can contain more data than exists in physical (and virtual) memory.

The default values have been chosen for simple test cases, running on a machine with limited Windows resources. Tuning with respect to your machine and applications is recommended.

Note that the values specified below are for a single cache, and that a separate cache is allocated for each object that requires one. For example, if your application contains three dynaset objects, three independent data caches are constructed, each using resources as described below.

SliceSize = 256 (default)

This entry specifies the minimum number of bytes used to store a piece of data in the cache. Items smaller than this value are allocated the full SliceSize bytes for storage; items larger than this value are allocated an integral multiple of this space value. An example of an item to be stored is a field value of a dynaset.

PerBlock = 16 (default)

This entry specifies the number of Slices (described in the preceding entry) that are stored in a single block. A block is the minimum unit of memory or disk allocation used within the cache. Blocks are read from and written to the disk cache temporary file in their entirety. Assuming a SliceSize of 256 and a PerBlock value of 16, then the block size is 256 * 16 = 4096 bytes.

CacheBlocks = 20 (default)

This entry specifies the maximum number of blocks held in memory at any one time. As data is added to the cache, the number of used blocks grows until the value of CacheBlocks is reached. Previous blocks are swapped from memory to the cache temporary disk file to make room for more blocks. The blocks are swapped based upon recent usage. The total amount of memory used by the cache is calculated as the product of (SliceSize * PerBlock * CacheBlocks). The maximum value for CacheBlocks is 127.

Recommended Values: You may need to experiment to find optimal cache parameter values for your applications and machine environment. Here are some guidelines to keep in mind when selecting different values:

The larger the (SliceSize * PerBlock) value, the more disk I/O is required for swapping individual blocks.

The smaller the (SliceSize * PerBlock) value, the more likely it is that blocks will need to be swapped to or from disk.

The larger the CacheBlocks value, the more memory is required, but the less likely it is that swapping will be required.

A reasonable experiment for determining optimal performance might proceed as follows:

Keep the SliceSize >= 128 and vary PerBlock to give a range of block sizes from 1K through 8K.

Vary the CacheBlocks value based upon available memory. Set it high enough to avoid disk I/O, but not so high that Windows begins swapping memory to disk.

Gradually decrease the CacheBlocks value until performance degrades or you are satisfied with the memory usage. If performance drops off, increase the CacheBlocks value once again as needed to restore performance.

[Fetch Parameters]

FetchLimit = 20 (default)

This entry specifies the number of elements of the array into which data is fetched from Oracle. If you change this value, all fetched values are immediately placed into the cache, and all data is retrieved from the cache. Therefore, you should create cache parameters such that all of the data in the fetch arrays can fit into cache memory. Otherwise, inefficiencies may result.

Increasing the FetchLimit value reduces the number of fetches (calls to the database) calls and possibly the amount of network traffic. However, with each fetch, more rows must be processed before user operations can be performed. Increasing the FetchLimit increases memory requirements as well.

FetchSize = 4096 (default)

This entry specifies the size, in bytes, of the buffer (string) used for retrieved data. This buffer is used whenever a long or long raw column is initially retrieved.

[General]

TempFileDirectory = [Path]

This entry provides one method for specifying disk drive and directory location for the temporary cache files. The files are created in the first legal directory path given by:

1.	The drive and directory specified by the TMP environment variable (this method takes precedence over all others);

2.	The drive and directory specified by this entry (TempFileDirectory) in the [general] section of the ORAOLE.INI file;

3.	The drive and directory specified by the TEMP environment variable; or

4.	The current working drive and directory.

HelpFile = [Path and File Name]

This entry specifies the full path (drive/path/filename) of the Oracle Objects for OLE help file as needed by the Oracle Data Control. If this entry cannot be located, the file ORACLEO.HLP is assumed to be in the directory where ORADC is located (normally \WINDOWS\SYSTEM).

Tuning and Customization using Windows Registry

For Windows NT and Windows 95, the Registry key is HKEY_LOCAL_MACHINE and the subkey is software\oracle\OO4O. For Windows 3.1, the parameters can also be set in the ORACLE.INI file (though it will still work on NT and Windows 95). For Windows NT and Windows 95, OO4O installation will create the following section in the Registry.

"TempFileDirectory" =

"Helpfile" = "oracleo.hlp"

"FetchLimit" = 20

"FetchSize" = 4096

"PerBlock" = 16

"CacheBlocks" = 20

"SliceSize" = 256


See Also

CreateDynaset Method

Long and Long Raw Columns

OraDynaset Object


Coding Techniques

See Also

Avoiding Excessive Object References

Oracle Objects for OLE is based on OLE Automation. While OLE Automation provides an excellent method of extending applications, it is not without cost. Poor coding techniques can cause extra object references and result in poor performance.

When using OLE Automation, it is preferable to reduce the number of object references. If you find you are frequently referencing a particular object (a column for example), it is much faster to declare a second object and set it to the value of the much-referenced object. This is true for singular objects as well as objects that are part of a collection.

Consider the following code fragment:

…

' Declare variables as OLE Objects.

Dim OraSession As Object

Dim OraDatabase As Object

Dim OraDynaset As Object

' Create the OraSession Object

Set OraSession = CreateObject("OracleInProcServer.XOraSession")

' Create the OraDatabase Object by opening a connection to Oracle

Set OraDatabase = OraSession.DbOpenDatabase("ExampleDb", "scott/tiger", 0&)

'Create the OraDynaset Object

Set OraDynaset = OraSession.Database.DbCreateDynaset("select * from emp", 0&)

The dynaset creation above is inefficient, because the Database property of the OraSession object is used instead of using the OraDatabase object that was already created. That code results in one extra object reference. The correct code is:

'Create the OraDynaset Object

Set OraDynaset = OraDatabase.DbCreateDynaset("select * from emp", 0&)

This example is only a small improvement and will not likely result in much time saved. Consider the following code fragment:

...

'Create the OraDynaset Object

Set OraDynaset = OraDatabase.DbCreateDynaset("select * from emp", 0&)

' Traverse until EOF is reached, setting each employee's

' salary and commission to zero.

Do Until OraDynaset.EOF

OraDynaset.DbEdit

OraDynaset.Fields("sal").value = 0

OraDynaset.Fields("comm").value = 0

OraDynaset.DbUpdate

OraDynaset.DbMoveNext

Loop

...

In version 2.3 field referencing has been optimized for better performance. However having individual field object references will have better performance as in the code below:

...

Dim flds() As Object

Dim i,fldcount As Integer

' Create the OraDynaset Object

Set OraDynaset = OraDatabase.DbCreateDynaset("select * from emp", 0&)

' Get the field count, and output the names

fldcount = OraDynaset.Fields.Count

ReDim flds(0 To fldcount - 1)

For i = 0 To fldcount - 1

Set flds(i) = OraDynaset.Fields(i)

Next I

' Traverse until EOF is reached, setting each employee's

' salary and commission to zero.

Do Until OraDynaset.EOF

OraDynaset.DbEdit

flds(5).value = 0 'SAL is column 5 (zero based).

flds(6).value = 0 'COMM is column 6 (zero based).

OraDynaset.DbUpdate

OraDynaset.DbMoveNext

Loop

...

This example shows how to reference fields through a field object and not through the fields collection of the dynaset. Testing proves that this small amount of extra code greatly improves performance.

Any method or object that is referenced through more than one object is potentially inefficient, but the extra coding is not always worth the time saved (as in the first example above). The best place to start is with field references, because they are most likely to occur multiple times.


See Also

Database Property

Fields Property

OraDatabase Object

OraField Object

OraFields Collection

OraSession Object


Sample Code Conventions

The following conventions apply throughout the sample code included here and the sample applications shipped with Oracle Objects for OLE:

The user "scott" with password "tiger" ("scott/tiger") is used for the Connect property.

The SQL*Net alias "ExampleDb" is used for the DatabaseName property.

The data tables referenced are the standard Oracle demonstration tables. You can create them with the script DEMOBLD7.SQL. (You can drop these tables and views with the script DEMODRP7.SQL.)

You can create the stored procedures referenced with the script ORAEXAMP.SQL.

The file ORACONST.TXT contains constant values such as True and False (defined as the corresponding values in Visual Basic), and other constants used for options flags and property values.

If the Oracle Data Control is used, its name is "oradata1".


See Also

Connect Property

DatabaseName Property


Method And Property Name Conflicts

The Oracle Objects for OLE programmatic interface is provided via OLE Automation. In some cases, the method or property names used in Oracle Objects for OLE may conflict with reserved words or built-in function names of some applications. If this is the case, you can prefix ANY Oracle Objects for OLE method or property name with "Db". All known application conflicts are listed below.

Visual Basic 4.0

The method names listed below are reserved words in Visual Basic 4.0. When Visual Basic 4.0 performs syntax checking, an error will be generated if these methods are not being referenced using a standard data object (of type "database" or "dynaset"). Therefore, in Visual Basic 4.0, these methods can only be used with the "Db" prefix.

Method	Applicable Object	Example
AddNew	OraDynaset	oradyanset.DbAddNew
AppendChunk	OraField	orafield.DbAppendChunk
BeginTrans	OraSession	orasession.DbBeginTrans
Clone	OraDynaset	oradynaset.DbClone
Close	OraDynaset	oradynaset.Dbclose
CommitTrans	OraConnection OraSession	oraconnection.DbCommitTrans orasession.DbCommitTrans
CreateDynaset	OraDynaset	oradynaset.DbCreateDynaset
Delete	OraDynaset	oradynaset.DbDelete
Edit	OraDynaset	oradynaset.DbEdit
ExecuteSQL	OraDatabase	oradynaset.DbExecuteSQL
FieldSize	OraDynaset	oradynaset.DbFieldSize
FindFirst	OraDynaset	oradynaset.DbFindFirst
FindLast	OraDynaset	oradynaset.DbFindLast
FindPrevious	OraDynaset	oradynaset.DbFindPrevious
FindNext	OraDynaset	oradynaset.DbFindNext
GetChunk	OraField	orafield.DbGetChunk
MoveFirst	OraDynaset	oradynaset.DbMoveFirst
MoveLast	OraDynaset	oradynaset.DbMoveLast
MoveNext	OraDynaset	oradynaset.DbMoveNext
MovePrevious	OraDynaset	oradynaset.DbMovePrevious
NoMatch	OraDynaset	oradynaset.DbNoMatch
Refresh	OraDynaset	oradynaset.DbRefresh
Rollback	OraConnection OraSession	oraconnection.DbRollback orasession.DbRollback
Update	OraDynaset	oradynaset.DbUpdate
UpdateControls	Data Control Recordset	oradata1.recordset.DbUpdateControls
UpdateRecord	Data Control Recordset	oradata1.recordset.DbUpdateRecord
Access 2.0

The method names listed below are built-in function names in Access 2.0. When Access 2.0 compiles a module, an error will be generated if these methods are not being referenced using a standard data object (of type "database" or "dynaset"). Therefore, in Access 2.0, these methods can only be used with the "Db" prefix.

Method	Applicable Object	Example
AddNew	OraDynaset	oradyanset.DbAddNew
AppendChunk	OraField	orafield.DbAppendChunk
BeginTrans	OraSession	orasession.DbBeginTrans
Clone	OraDynaset	oradynaset.DbClone
CommitTrans	OraConnection OraSession	oraconnection.DbCommitTrans orasession.DbCommitTrans
Close	OraDynaset	oradynaset.Dbclose
CreateDynaset	OraDynaset	oradynaset.DbCreateDynaset
Delete	OraDynaset	oradynaset.DbDelete
Edit	OraDynaset	oradynaset.DbEdit
ExecuteSQL	OraDatabase	oradatabase.DbExecuteSQL
FieldSize	OraDynaset	oradynaset.DbFieldSize
FindFirst	OraDynaset	oradynaset.DbClose
FindLast	OraDynaset	oradynaset.DbFindLast
FindPrevious	OraDynaset	oradynaset.DbFindFirst
FindLast	OraDynaset	oradynaset.DbFindLast
GetChunk	OraField	orafield.DbGetChunk
MoveFirst	OraDynaset	oradynaset.DbMoveFirst
MoveLast	OraDynaset	oradynaset.DbMoveLast
MoveNext	OraDynaset	oradynaset.DbMoveNext
MovePrevious	OraDynaset	oradynaset.DbMovePrevious
NoMatch	OraDynaset	oradynaset.DbNoMatch
OpenDatabase	OraSession	orasession.DbOpenDatabase
Refresh	OraDynaset	oradynaset.DbRefresh
Rollback	OraConnection OraSession	oraconnection.DbRollback orasession.DbRollback
Update	OraDynaset	oradynaset.DbUpdate


See Also

AddNew Method

AppendChunk Method

BeginTrans Method

Clone Method

CommitTrans Method

CreateDynaset Method

Delete Method

Edit Method

ExecuteSQL Method

FieldSize Method

GetChunk Method

MoveFirst Method

MoveLast Method

MoveNext Method

MovePrevious Method

OpenDatabase Method

Refresh Method

Rollback Method

Update Method

UpdateControls Method

UpdateRecord Method


Redistributable Files

Oracle Objects for OLE

The following redistributable files (all 32-bit) are part of Oracle Objects for OLE and must be distributed with your application developed using Oracle Objects for OLE:

OIP22.DLL

OIP22.TLB

ORAANSI.DLL

These files should be installed in the [ORACLE_HOME]\BIN directory. In addition to including these files with your application, you must register in the registration database the information found in ORAIPSRV.REG. OO4OPARM.REG has also been provided to register OO4O configuration information. Use REGEDIT or REGEDT32 to do this.

Finally, you must also distribute the file from the following list corresponding to the development software you used to build your application:

ORACLM.DLL	(for Microsoft C++)

ORADC.OCX

Note that the Ora Data Control (ORADC.OCX) will have to be registered for it to function. The OCX may be registered by running "regsvr32.exe ORADC.OCX".


Troubleshooting

OLE Initialization or OLE Automation Errors

The most frequent cause of OLE Initialization and Automation errors is missing or incorrectly installed software. Ensure correct installation of the software specified below. Then make sure that you have specified method and property names correctly and that you have declared all "Oracle objects" as type "object".

Possible Cause	Solution

Your system does not contain the Microsoft OLE 2.0 runtime files or these files are out of date. Note that Visual Basic 4.0 does not include the OLE file TYPELIB.DLL.	Reinstall Oracle Objects for OLE and select the "Microsoft OLE 2.0 Libraries" option.
OLE 2.0 information was not registered in the Windows registration database.	Run REGEDIT.EXE or REGEDT32.EXE (for 32 bit) and merge the information from the file OLE2.REG, which is normally located in the SYSTEM directory.
The Oracle Objects for OLE object information was not registered in the Windows registration database.	Reinstall Oracle Objects for OLE and select the "Oracle Objects Server" option.
The Oracle Objects for OLE object information was not registered in the Windows registration database.	Run REGEDIT.EXE or REGEDT32.EXE (for 32 bit) and merge the information from the file ORAIPSRV.REG, which is normally located in the ORAHOME\OO4O directory.
Your system does not contain the Oracle Required Support Files ( ORA 7*.DLL, CORE*.DLL, NLS*.DLL, or ORAANSI.DLL).	Reinstall Oracle Objects and select the "Oracle Required Support Files" option or add to your PATH environment variable the directory where these files are located.
Your system does not contain Oracle SQL*Net or its files are not on the PATH.	Install Oracle SQL*Net or add to your PATH environment variable the directory where these files are located.
You have misspelled a method or property name.	Check the documentation to determine the correct spelling.
You have referenced a method or property from the wrong object.	Check the documentation to determine the correct object.
Your system does not contain ORAANSI.DLL	Reinstall Oracle Objects for OLE and select the "Oracle Objects Server" option, or add to your PATH environment variable the directory in which these files are located.
Oracle SQL*Net Errors

The most frequent cause of Oracle SQL*Net errors is incorrectly specified connection information. The connection information for Oracle Objects for OLE is specified differently than when using ODBC. Please verify that you have specified connection information correctly, and then make sure your SQL*Net connection is working properly before using Oracle Objects for OLE. The appropriate Oracle SQL*Net documentation contains information about testing your connection and about any Oracle SQL*Net error that you may receive.

Possible Cause	Solution

Incorrect Connect property or argument to the OpenDatabase method.	See the topics on the Connect property or the OpenDatabase method for examples.
Incorrect DatabaseName property or argument to the OpenDatabase method.	See the topics on the DatabaseName property or the OpenDatabase method for examples.
Your system does not contain Oracle SQL*Net.	Install Oracle SQL*Net.
General Protection Faults

The most frequent cause of GPFs is installing Oracle Objects for OLE while other applications are running that require the Oracle Object Server, Oracle Required Support Files or OLE 2.0. To avoid this, install Oracle Objects for OLE immediately after starting Windows and before running any other application.

Possible Cause	Solution

Duplicate Oracle Objects for OLE files exist in SYSTEM directories or along the PATH.	Remove any duplicate files. The files OIP21.DLL and OIP21.TLB should only be located in the ORAHOME\BIN directory.
Duplicate Oracle Required Support Files DLLs exist in the SYSTEM directories or along the PATH.	Remove any duplicate files. Typically, the Oracle Required Support Files DLLs (ORA7*.DLL, CORE*.DLL, and NLS*.DLL) are located in ORAHOME\BIN.
Duplicate OLE 2.0 DLLs exist in the SYSTEM directories or along the PATH.	Remove any duplicate files. The OLE 2.0 DLLs (listed in the Redistributable Files topic) should only be located in SYSTEM directories.


See Also

Connect Property

DatabaseName Property

OpenDatabase Method

Redistributable Files


Oracle Objects for OLE and Visual Basic

Data Access Objects/Methods/Properties

This topic compares the objects, methods and properties of Oracle Objects for OLE to the Data Access Objects (DAO) found in Visual Basic. This section should not be considered a replacement for the full reference, but rather a place to start when trying to determine if a feature of DAO is available in Oracle Objects for OLE.

Objects	OO	VB	Comments
Client	YES	NO	OO: One client object exists per workstation.
Connection	YES	NO	OO: Represents a single connection to the database
Database	YES	YES
Dynaset	YES	YES
Fields	YES	YES
Index	N/A	YES	OO: Indexes are not required on tables.
Parameter Array	YES	NO	OO:Used in PL/SQL procedures, and for array insert/update/detete.
Parameter	YES	NO	OO: Provides SQL and PL/SQL bind variable support.
QueryDef	NO	YES	OO: Queries are not saved in the database. Views should be used as an alternative.
Session	YES	NO	OO: Used to manage collections of databases, connections, and dynasets,
Snapshot	NO	YES3	OO: Set the READONLY flag when creating a dynaset.
SqlStmt	YES	NO	OO: Represents single statement.
Table	NO	YES3	OO: Tables are part of the data dictionary.
TableDef	NO	YES	OO: Table definitions are part of the data dictionary.

NOTE: "Oracle objects" are declared as data type (OLE) "object", not as native data types.

Client Methods	OO	VB	Comments
CreateSession	YES	N/A	OO: Creates a named session that can be located by name.
Client Properties	OO	VB	Comments
Name	YES	N/A	OO: Returns the name of the Client object.
Sessions	YES	N/A	OO: Returns the associated Sessions collections.
Session Methods	OO	VB	Comments
BeginTrans1	YES	N/A	OO: Begins a Session-wide transaction.
CreateNamedSession	YES	N/A	OO: Same as Client.CreateSession.
ConnectSession	YES	N/A	OO: Same as finding a session in the Client.Sessions collection.
CommitTrans1	YES	N/A
OpenDatabase	YES	N/A	VB: OpenDatabase is a standalone method.
LastServerErrReset	YES	N/A	OO: Clears the error in LastServerErr to zero and sets LastServerErrText to NULL.
Rollback1	YES	N/A	OO: Rolls back a Session-wide transaction.
ResetTrans	YES	N/A	OO: Unconditionally rolls back a Session-wide transaction.
Session Properties	OO	VB	Comments
Name	YES	N/A	OO: Returns the Session name.
Client	YES	N/A	OO: Returns the associated Client object.
Connections	YES	N/A	OO: Returns the associated Connections collection.
LastServerErr	YES	N/A	OO: Returns the connection related error.
LastServerErrText	YES	N/A	OO: Returns the text of the last connection related error.
OIPVersionNumber	YES	N/A	OO: Returns the current internal version number.
Connection Properties	OO	VB	Comments
Connect	YES	N/A	OO: Returns the Connect property minus the password.
DatabaseName	YES	N/A	OO: Returns the DatabaseName property.
Session	YES	N/A	OO: Returns the associated Session object.
Connection Methods	OO	VB	Comments
CommitTrans	YES1	N/A	OO: Commits transactions at the connection level.
Rollback	YES1	N/A	OO: Rolls back transactions at the connection level.
Database Properties	OO	VB	Comments
CollatingOrder	NO	YES	VB: Determines the method for comparing text values. OO: Use 'ALTER SESSION ... NLS_SORT ...
Connect	YES	N/A	OO: Same as Connection.Connection.
Connection	YES	NO	OO: Returns the Connection object.
DatabaseName	YES	NO	OO: Same as Connection.DatabaseName.
Exclusive	N/A	YES	OO: Clients generally have no control over database access.
LastServerErr	YES	NO	OO: Returns the number of the last cursor related error.
LastServerErrPos	YES	NO	OO: Returns the position in the SQL statement that a parse error occurred.
LastServerErrText	YES	NO	OO: Returns the text of the last cursor related error.
Name	NO	YES
Options	YES	NO	OO: Returns the options flag originally passed to the Session.OpenDatabase method.
Parameters	YES	N/A	OO: Returns the associated parameters collection.
QueryDefs	N/A	YES
QueryTimeout	NO	YES	VB: Determines the number of seconds Visual Basic waits before a timeout error occurs when executing a query on an ODBC database. OO: See the Lock Wait option of the OpenDatabase method.
Session	YES	N/A	OO: Returns the associated Session object.
RDMS_Version	YES	N/A	OO: Returns the Oracle Database Server version.
Transactions	NO	YES	OO: This flag is available through the Dynaset Object.
Updatable	NO	YES	OO: This flag is available through the Dynaset Object.
Database Methods	OO	VB	Comments
BeginTrans	NO	YES3	OO: This method is available via the Session Object.
Close	N/A	YES	OO: Once the database has gone out of scope and there are no references to it, it closes automatically.
CommitTrans	NO	YES3	OO: This method is available via the Session Object.
CreateDynaset	YES1	YES3	The options flags for Oracle and VB CreateDynaset methods are significantly different. Please consult the respective documentation for details.
CreatePlsqlDynaset	YES	NO	OO: Creates dynaset object from PL/SQL cursor.
CreateQueryDef	N/A	YES
CreateSnapshot	N/A	YES	OO: Set the READONLY property when creating the dynaset.
DeleteQueryDef	N/A	YES
Execute	N/A	YES	OO: Use ExecuteSQL.
ExecuteSQL	YES1	YES
LastServerErrReset	YES	N/A	OO: Clears the error in LastServerErr to zero and sets LastServerErrText to NULL.
ListFields	NO	YES
ListTables	NO	YES
OpenQueryDef	N/A	YES
OpenTable	N/A	YES
Rollback	NO	YES2	OO: This method is available through the Session Object.
Parameter Properties	OO	VB	Comments
MinimumSize	YES	N/A	OO: Minimum size of a parameter (for binding).
Name	YES	N/A	OO: Name of the parameter.
ServerType	YES	N/A	OO: Specifies the Oracle Data Type.
Status	YES	N/A	OO: Bit flag to indicate state (Input/Output/AutoBound/Enabled).
Type	YES	N/A	OO: Indicates the variant data type that is actually bound to the SQL statement.
Value	YES	N/A	OO: Parameter value.
Parameter Methods	OO	VB	Comments
Add	YES	N/A	OO: Add to the Parameter collection.
AddTable	YES	N/A	OO: Add arrays to the parameter collection.
AutoBindEnable	YES	N/A	OO: Bind on Dynaset Create/Refresh.
AutoBindDisable	YES	N/A	OO: Do Not Bind on Dynaset Create/Refresh.
Remove	YES	N/A	OO: Remove from the Parameter collection.
Parameter Array Methods	OO	VB	Comments
AutoBindEnable	YES	N/A	OO: Bind on Dynaset Create/Refresh.
AutoBindDisable	YES	N/A	OO: Do Not Bind on Dynaset Create/Refresh.
Parameter Array Properties	OO	VB	Comments
ArraySize	YES	N/A	OO: Gets or sets the array size.
LastErrorText	YES	N/A	OO: Get the last eror text.
MinimumSize	YES	N/A	OO: Set the minimum size of the parameter object.
Name	YES	N/A	OO: Returns name to identify object.
Type	YES	N/A
Value	YES	N/A
SQL Statement Methods	OO	VB	Comments
Close	YES	N/A	OO: Close the SQL object.
Refresh	YES	N/A	OO: Re-execute a SQL statement where only a parameter value has changed.
SQL Statement Properties	OO	VB	Comments
Connection	YES	N/A	OO: Returns the associated Connection object.
Database	YES	N/A	OO: Returns the associated Database object.
Session	YES	N/A	OO: Returns the associated Session object.
SQL	YES	N/A	OO: Returns the SQL statement associated with the object.
Dynaset Methods	OO	VB	Comments
AddNew	YES1	YES
Clone	YES1	YES3
Close	N/A	YES	OO: Once the dynaset has gone out of scope and there are no references to it, it will be closed automatically.
CopyToClipboard	YES	NO	OO: Copies field value to the clipboard in text format.
Delete	YES1	YES
Edit	YES1	YES
FindFirst	YES	YES
FindLast	YES	YES
FindNext	YES	YES
FindPrevious	YES	YES
ListFields	NO	YES1	VB: Suggests that this method should not be used.
ListIndexes	NO	YES3	VB: Suggests that this method should not be used.
MoveFirst	YES1	YES
MoveLast	YES1	YES
MoveNext	YES1	YES
MovePrevious	YES1	YES
MovePreviousN	YES	N/A	OO: Moves "N" rows backward.
MoveNextN	YES	N/A	OO: Moves "N" rows forward.
MoveRel	YES	N/A	OO: Moves the rows relative to the current row.
MoveTo	YES	N/A	OO: Move directly to the row number.
Refresh	YES1	NO	OO: Cancels all edit operations, executes the current contents of the SQL statement buffer, and moves to the first row of the resulting dynaset. Used to re-execute a SQL statement where only a parameter value has changed.
Update	YES1	YES
UpdateControls	YES1	NO	VB: Implemented as a Data Control method.
UpdateRecord	YES1	NO	VB: Implemented as a Data Control method.
Dynaset Properties	OO	VB	Comments
BOF	YES	YES
Bookmark	YES	YES
Bookmarkable	YES	YES	OO: All dynasets are bookmarkable except if created with the NoCache option.
CacheBlocks	YES	N/A	OO: Sets or gets number of cache blocks.
CacheChanged	YES	N/A	OO: Returns TRUE if cache or fetch parameters have changed.
CacheSlicePerBlock	YES	N/A	OO: Gets or sets cache slice per block.
CacheSliceSize	YES	N/A	OO: Gets or sets cache slice size.
Connection	YES	N/A	OO: Returns the associated Connection object.
Database	YES	NO	OO: Returns the associated Database object.
DateCreated	N/A	YES	OO: Check the Data Dictionary.
EOF	YES	YES
EditMode	YES	YES
FetchLimit	YES	N/A	OO: Gets or sets the Fetch Array size.
FetchSize	YES	N/A	OO: Gets or sets the Fetch Array buffer size.
Fields	YES	YES
Filter	NO	YES3	OO: Use a WHERE clause.
Index	NO	YES
LastModified	YES	YES
LastUpdated	N/A	YES	OO: Check the Data Dictionary.
LockEdits	N/A	YES	OO: Rows are locked when updated or if the SQL statement contains a 'FOR UPDATE' clause.
Name	NO	YES	VB: The name of the Dynaset is either the Name property of a Table or QueryDef used to create it, or the SQL statement used to create it.
NoMatch	YES	YES1	VB: Indicates whether any of the "Find" methods were successful in locating a record.
RecordCount	YES	YES
Session	YES	N/A	OO: Returns the associated Session object.
SQL	YES	N/A	OO: The SQL statement used to create the dynaset.
Sort	NO	YES3	OO: Use an ORDER BY clause.
Transactions	YES	YES	OO: Dynasets ALWAYS support Transactions.
Updatable	YES	YES
Field Properties	OO	VB	Comments
Attributes	NO	YES	OO: This is part of the Data Dictionary.
CollatingOrder	NO	YES	OO: See the 'Alter Session' NLS_SORT option of the Oracle database.
Name	YES	YES
OrdinalPosition	NO	YES
Size	YES	YES
SourceField	NO	YES	OO: You must examine your SQL statement.
SourceTable	NO	YES	OO: You must examine your SQL statement.
Truncated	YES	NO	OO: Indicates a LONG or LONG RAW was truncated on fetch. This indicates that you need to use GetChunk to retrieve the complete data.
Type	YES	YES
Value	YES	YES
Field Methods	OO	VB	Comments
AppendChunk	YES	YES
FieldSize	YES	YES	OO: Available for fields up to 64KB otherwise -1 is returned.
Get Chunk	YES	YES
Read Chunk	YES	N/A	OO: Same as Get Chunk method but returns number number of bytes actually read.
Data Control Properties	OO	VB	Comments
AllowMoveLast	YES	NO	OO: Allow user to press MoveLast button of the data control?
AutoBinding	YES	N/A	OO: Allow automatic binding of database Parameters?
BackColor	YES	YES
CacheBlocks	YES	NO
CacheSliceSize	YES	NO
CacheSlicePerBlock	YES	NO
Caption	YES	YES
Connect	YES	YES
Database	YES	YES
DatabaseName	YES	YES
DirtyWrite	YES	NO
DragIcon	YES	YES
DragMode	YES	YES
EditMode	YES	YES
Enabled	YES	YES
FetchLimit	YES	NO
FetchSize	YES	NO
ForeColor	YES	YES
Height	YES	YES
Index	YES	YES
Left	YES	YES
MousePointer	YES	YES
Name	YES	YES
NoRefetch	YES	NO
Options	YES	YES	The Options flags for Oracle and VB are significantly different. Please consult the respective documentation for details.
OracleMode	YES	NO
ReadOnly	YES	YES
RecordSet	YES	YES
RecordSource	YES	YES	OO: The SQL statement may be any arbitrary SELECT statement, including joins, references to views, nested selects, or remote database references.
Session	YES	N/A	OO: Returns the associated Session object.
Tag	YES	YES
Top	YES	YES
TrailingBlanks	YES	NO	OO: Strip trailing blanks from text retrieved from the database?
Visible	YES	YES
Width	YES	YES
Data Control Methods	OO	VB	Comments
Drag	YES	YES
Move	YES	YES
Refresh	YES	YES
UpdateControls	YES	YES	OO: Implemented as a dynaset method, due to a VB limitation.
UpdateRecord	YES	YES	OO: Implemented as a dynaset method, due to a VB limitation.
Data Control Events	OO	VB	Comments
DragDrop	YES	YES
DragOver	YES	YES
Error	YES	YES
MouseDown	YES	YES
MouseMove	YES	YES
MouseUp	YES	YES
Reposition	YES	YES
Validate	YES	YES
1: These Oracle Objects methods can be prefixed by "Db" if necessary since many of these names are reserved words in Visual Basic 4.0 and Access 2.0.

2: Visual Basic documentation suggests that this method not be used.

3: Only available with the Visual Basic Professional Edition.


See Also

Method And Property Name Conflicts

OraConnection Object

OraClient Object

OraDatabase Object

OraDynaset Object

OraField Object

OraParameter Object

OraSession Object


See VB
See Visual Basic Help

Please refer to the on-line help provided with Visual Basic for information on this property.


Multiple Cursor Return from PL/SQL Procedure

Example

Application

An OraParameter object represents a PL/SQL cursor bind variable in a PL/SQL block. This supports usage of PL/SQL procedures containing more than one cursor variable (i.e. CreatePLSQLDynaset supports only one cursor variable) .

An OraParameter object representing a cursor variable should be assigned type ORATYPE_CURSOR using the ServerType property and should be treated as an output variable only. Upon output, the Value property of OraParameter object of type ORATYPE_CURSOR returns a read-only OraDynaset object. This Dynaset object can be treated as any other dynaset object.

Usage

set mydynaset = OraParameters("EmpCursor").Value

You can set the dynaset options and cache parameters for the Dynaset object created from PL/SQL cursors using the DynasetOption property and DynasetCacheParams of the OraParameter object. You should call the above property and method before executing the PL/SQL procedure.

It is better to use the CreateSQL method for executing PL/SQL procedures containing cursors. Depending upon cursor implementation under a PL/SQL stored procedure, the Refresh method on the OraSQLStmt object can result in modified PL/SQL cursors. In such a case these modified cursors are automatically associated with the already existing dynaset object and no new dynaset object is created.

You cannot set the SQL property of the dynaset object, and will raise an error if you try to do so.

PL/SQL stored procedures containing cursors as table parameters are not supported.

You should call the Remove method on the parameter object. This will help in cleaning the dynaset object and local temporary cache files.

You can attach the dynaset object to the Recordset property of Oracle Data Control.


See Also

OraParameter object

OraDynaset object

Value property


Coding Example - Multiple Cursor Return

This example demonstrates the use of OraParameter object supporting PL/SQL cursor This example returns PL/SQL cursor as a dynaset for the different values of Customer name parameter. Make sure that 'Customers' stored procedure (found in EMPCUR.SQL) is available in the Oracle Server and paste this code into the definition section of a form, then press F5.

Private Sub Example_Click()

'Declare variables as OLE Objects.

Dim MySession as OraSession

Dim MyDatabase as OraDatabase

Dim OrderDynaset As OraDynaset

Dim SalesDynaset As OraDynaset

'Create the OraSession Object.

Set MySession = CreateObject("OracleInProcServer.XOraSession")

'Create the OraDatabase Object by opening a connection to Oracle.

Set MyDatabase = MySession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

'Create the Deptno parameter

MyDatabase.Parameters.Add "NAME", "JOCKSPORTS", ORAPARM_INPUT

MyDatabase.Parameters("NAME").ServerType = ORATYPE_VARCHAR2

MyDatabase.Parameters.Add "ORDCURSOR", 0, ORAPARM_OUTPUT

MyDatabase.Parameters("ORDCURSOR").ServerType = ORATYPE_CURSOR

MyDatabase.Parameters.Add "SALESCURSOR", 0, ORAPARM_OUTPUT

MyDatabase.Parameters("SALESCURSOR").ServerType = ORATYPE_CURSOR

Set OraSQLStmt = MyDatabase.CreateSql("Begin Customers.GetCutomerSalesOrder(:Name,:OrdCursor,:SalesCursor);end;" ,ORASQL_FAILEXEC)

Set OrderDynaset = MyDatabase.Parameters("ORDCURSOR").Value

Set SalesDynaset = MyDatabase.Parameters("SALESCURSOR").Value

'Now display the Dynaset's field value

MsgBox "Order Details are " & OrderDynaset.fields("ordid").Value & " " & OrderDynaset.fields("orderDate").Value

MsgBox "Sales Details are " & SalesDynaset.fields("Prodid").Value & " " & SalesDynaset.fields("ProdName").Value

'Now Change the customer name to VOLLYRITE

MyDatabase.Parameters("NAME").Value = "VOLLYRITE"

'Now refreshes the SQLStmt object

OraSQLStmt.Refresh

'Now display the Dynaset's field value

MsgBox "Order Details are " & OrderDynaset.fields("ordid").Value & " " & OrderDynaset.fields("orderDate").Value

MsgBox "Sales Details are " & SalesDynaset.fields("Prodid").Value & " " & SalesDynaset.fields("ProdName").Value

'Now remove the Parameters object

'MUST BE CALLED for OraParameter of type ORATYPE_CURSOR

MyDatabase.Parameters.Remove ("ORDCURSOR")

MyDatabase.Parameters.Remove ("SALESCURSOR")

MyDatabase.Parameters.Remove ("NAME")

End Sub


Enhancing Performance

The following guidelines are intended to help tune performance of Oracle Objects for OLE based applications.

Early Binding of OO4O Objects

The early binding technique tightly type casts OO4O objects to their native object types rather than the generic object type provided by Visual Basic. In other words, objects are declared directly as OO4O objects, rather than as generic objects which are later reclassified as OO4O objects. The early binding technique improves performance by reducing frequent access to the OO4O type library.

Note the following example:

 'Early binding of OO4O objects

 Dim OraSession as OraSession

 Dim OraDatabase as OraDatabase

 Dim OraDynaset as OraDynaset

 'Generic binding of OO4O objects

 Dim OraSession as object

 Dim OraDatabase as object

 Dim OraDynaset as object

In order to use early binding of OO4O objects, the Oracle InProcess Server type library must be referenced in the Visual Basic projects.

Tuning and Customization of Dynaset objects

Data access can be tuned and customized by altering a Dynaset's cache and fetch parameters. In a client/server environment, setting FetchLimit to a higher value increases the number of rows that are fetched with each request, thus reducing the number of network trips to the Oracle Server, which in turn improves performance.

The cost of increasing the FetchLimit is that it increases memory requirements on the client-side, and causes more data to be swapped to and from the temporary cache file on disk. So the proper FetchLimit and cache parameters should be set according to the client machine configuration and the anticipated size of the query result.

FetchLimit can be set in the following ways:

1) By using the CreateCustomDynaset method,

2) By modifying parameters of the OO4O entry in the Windows Registry,

3) By modifying the Oracle.ini file.

For more information, see Tuning and Customization section of the documentation.

Avoiding multiple object reference

Improper coding techniques with unnecessary object references can also affect performance. Particularly, during Dynaset object navigation, you should rather reduce the number of object references to OraField collection and OraField object. Here is an inefficient code block:

 'Create the OraDynaset Object

 Set OraDynaset = OraDatabase.DbCreateDynaset("select * from emp", 0&)

 'Traverse until EOF is reached

 Do Until OraDynaset.EOF

 msgbox OraDynaset.Fields("sal").value

 OraDynaset.DbMoveNext

 Loop

In the above example, OraDynaset, OraField collections and OraField object are referenced for each iteration. Even though OO4O provides improvement in handling the field collections object, multiple reference to the automation object goes though underlying OLE/COM automation layer , which in turn slows down the execution. You should rather declare a second object and set it to the value of the much-referenced object. For example,

 Dim Field As OraField

 'Create the OraDynaset Object

 Set OraDynaset = OraDatabase.DbCreateDynaset("select * from emp", 0&)

 'Now get the 'Salary' field object from the Dynaset field collection object

 set Field = OraDynaset.Fields("SAL")

 'Traverse until EOF is reached

 Do Until OraDynaset.EOF

 msgbox Field.Value

 OraDynaset.DbMoveNext

 Loop

The above example creates an OraField object corresponding to 'Sal' column from dynaset field collection. So Oradynaset,OraField collection object are referenced only once. Later Field values are retrieved from OraField object which in turn reduces the unnecessary reference to OraDynaset object and OraField collection object

For more information on how to avoid multiple references on OO4O object , see

Coding Technique section.

Parameter bindings

OO4O provides a way of enabling and disabling Parameter object binding at the time it processes the SQL statement. This can be done through AutoBindDisableand AutoBindEnable methods of the OraParameter object. If the SQL statement does not contain the parameter name, it is better to disable the OraParameter because it avoids an unnecessary reference of the parameter object. This is especially effective when the application is written primarily using PL/SQL procedures. For example,

 Set OraDatabase = OraSession. DbOpenDatabase("ExampleDb",

"scott/tiger", 0&)

 'Add the job input parameter with initial value MANAGER.

 OraDatabase.Parameters.Add "job", "MANAGER", 1

 'Add the deptno input parameter with initial value 10.

 OraDatabase.Parameters.Add "deptno", 10, 1

 'Add the job input parameter with initial value MANAGER.

 OraDatabase.Parameters.Add "EmpCur", 0, 1

 OraDatabase.Parameters("Empcur").ServerType = ORATYPE_CURSOR

 'Disable the job parameter for now.

 OraDatabase.Parameters("job").AutoBindDisable

 set OraSqlStmt = CreateSQL("Begin GetEmpData(:Empcur,:deptno)

 End;",0&)

Note how the "job" parameter object is not referenced while processing PL/SQL statement.

Array Processing

OO4O supports an array interface to an Oracle database through OraParamArray object. The array interface enables the transfer of bulk of data in single network trip. This is especially helpful while processing a PL/SQL or SQL statement via the 'ExecuteSQL' or 'CreateSQL' method. For example, in order to insert 100 rows into remote database without array processing, ExecuteSQL/CreateSQL must be called for 100 times, which in turn make 100 network trips. The follwing example explains this,

 For I = 1 to 100

OraParameter("EMPNO").Value = xxxx

OraParameter("ENAME").Value = 'yyyy'

OraParameter("DEPTNO").Value = zz

OraDatabase.ExecuteSql("insert into emp values (:EMPNO,:ENAME,:DEPTNO)");

 Next I

The following example make use of arrays and do only one network trip.

' :ENAMEARR,:EMPNOARR,:DEPTNOARR are paramater arrays

 For I = 1 to 100

OraParameter("EMPNOARR").Put_Value xxxx, I

OraParameter("ENAMEARR").Put_Value 'yyyy' ,I

OraParameter("DEPTNOARR").Put_Value zz, I

 Next I

 'Now call the ExecuteSQL only once

 OraDatabase.ExecuteSql("insert into emp values(:EMPNOARR,:ENAMEARR,:DEPTNOARR)");

For more information on using arrays , please refer OraParamArray object.


See Also

CreatePlsqlDynasetIDH_CreatePlsqlDynaset_Method Method


Data Control

Description

A data control provides a programmatic representation of a database and a graphical representation of the "Move" methods of the underlying dynaset (MoveFirst, MovePrevious, MoveNext, and MoveLast). You can bind other controls to a data control.

Remarks

A data control allows you to bind to it other controls that display a field, a record, or multiple records of the underlying dynaset. When record movement occurs, data in bound controls stays in sync with the current record of the dynaset.

If a user changes that data in a control that is bound to a data control, the changes are automatically reflected in the underlying dynaset and database.

A data control allows you to perform most data access operations without writing any code at all. To create a dynaset with a data control, set the Connect, DatabaseName, and RecordSource properties and execute Refresh.


See Also

Connect Property

DatabaseName Property

MoveFirst, MoveLast, MoveNext, MovePrevious Methods

OraDynaset Object

RecordSource Property

Refresh Method


Properties

AllowMoveLast AutoBinding BackColor CacheBlocks  CacheSliceSize CacheSliceSizePerBlock Caption Connect Database DatabaseName DirtyWrite DragIcon DragMode EditMode Enabled	FetchLimit  FetchSize Font ForeColor Height Index Left MousePointer	Name NoRefetch Options OracleMode ReadOnly Recordset RecordSource Session Tag TrailingBlanks Top Visible Width


Methods

Drag

Move

Refresh

UpdateControls

UpdateRecord

ZOrder


Events

DragDrop DragOver Error MouseDown	MouseMove MouseUp Reposition Validate


Data Control Properties

AllowMoveLast AutoBinding BackColor CacheBlocks  CacheSliceSize CacheSliceSizePerBlock Caption Connect Database DatabaseName DirtyWrite DragIcon DragMode EditMode Enabled	FetchLimit  FetchSize Font ForeColor Height Index Left MousePointer	Name NoRefetch Options OracleMode ReadOnly Recordset RecordSource Session Tag TrailingBlanks Top Visible Width


Data Control Properties

AllowMoveLast AutoBinding BackColor Caption Connect Database DatabaseName DragIcon DragMode EditMode Enabled	Font ForeColor Height Index Left MousePointer	Name Options ReadOnly Recordset RecordSource Session Tag TrailingBlanks Top Visible Width


Data Control Methods

Drag

Move

Refresh

UpdateControls

UpdateRecord

ZOrder


Data Control Methods

Drag

Move

Refresh

UpdateControls

UpdateRecord

ZOrder


Data Control Events

DragDrop DragOver Error MouseDown	MouseMove MouseUp Reposition Validate


Data Control Events

DragDrop DragOver Error MouseDown	MouseMove MouseUp Reposition Validate


AllowMoveLast Property

Applies To

Oracle Data Control

Description

Determines whether the user can move to the last record using the Data Control's MoveLast button. Read/write at design time and run time.

Usage

oradata1.AllowMoveLast = [True | False]

Remarks

By default, AllowMoveLast is True, in which case the user has no restriction upon record motion, even when moving to the last record may be very time consuming.

When AllowMoveLast is False, the Data Control’s MoveLast button is grayed out and disabled. However, once the last record has been encountered (either because the user has navigated to the end of the set, or because code has positioned the record pointer to the last record), the button is enabled. This gives the user visual feedback about whether or not the entire query has been fetched. Setting this property to False does not prevent you from using the MoveLast method.

Changing this property has no effect until a Refresh method is sent to the data control.

Data Type

Integer (Boolean)


See Also

MoveLast Method

Refresh Method


AutoBinding Property

Applies To

Oracle Data Control.

Description

Determines whether the automatic binding of database object parameters will occur. Read/write at design time and run time.

Usage

oradata1.AutoBinding = [ True | False ]

Remarks

By default, AutoBinding is True, in which case the parameters in the OraParameters collection are bound to the SQL statement of the RecordSource property before data control refresh (before the SQL statement is executed). Technically speaking, the parameters are rebound when the recordset is re-created.

Setting Autobinding to False takes effect only if the SQL statement of the RecordSource property needs to be rebound and reexecuted. This is not the case when you simply change a parameter value and refresh the data control or simply refresh the recordset (the SQL statement only needs to be reexecuted). This is the case if you alter the RecordSource property and change the SQL statement.

Use this property to disable all parameter binding when executing a SQL statement that does not contain any parameters (using CreateDynaset, Refresh or ExecuteSQL).

Changing this property does not take effect until a Refresh method is sent to the data control (and the appropriate conditions apply). Changing this property has no effect when a recordset.Refresh is executed.

Data Type

Integer (Boolean)


See Also

Add Method

AutoBindDisable Method

AutoBindEnable Method

CreateDynaset Method

ExecuteSQL Method

OraParameter Object

OraParameters Collection

RecordSource Property

Refresh Method


AutoBinding Property Example

This example demonstrates the use of AutoBinding to show how it affects data control and recordset refresh. Copy this code into the definition section of a new form, then press F5 to run.

Sub Form_Load ()

 'Set the username and password.

 oradata1.Connect = "scott/tiger"

 'Set the databasename.

 oradata1.DatabaseName = "ExampleDb"

 'Refresh the data control without setting the

 'recordsource. This has the effect of creating

 'the underlying database object so that parameters

 'can be added.

 oradata1.Refresh

 'Set the recordsource and use a SQL parameter for job.

 oradata1.RecordSource = "select * from emp where job = :job"

 'Add the job input parameter with initial value MANAGER.

 oradata1.Database.Parameters.Add "job", "MANAGER", 1

 'Add the deptno input parameter with initial value 10.

 oradata1.Database.Parameters.Add "deptno", 10, 1

 'Refresh the data control.

 oradata1.Refresh

 MsgBox "Employee #" & oradata1.Recordset.fields("empno") & ", Job=" & oradata1.Recordset.fields("job")

 'Only employees with job=MANAGER will be contained

 'in the dynaset.

'Turn off Automatic parameter binding.

 oradata1.AutoBinding = False

 'Change the value of the job parameter to SALESMAN.

 oradata1.Database.Parameters("job").Value = "SALESMAN"

 'Refresh ONLY the recordset.

 oradata1.Recordset.DbRefresh

 MsgBox "Employee #" & oradata1.Recordset.fields("empno") & ", Job=" & oradata1.Recordset.fields("job")

 'The query will still execute even with AutoBinding=False

 'because the dynaset has not been re-created.

 'Set the recordsource and use a SQL parameter for deptno.

 oradata1.RecordSource = "select * from emp where deptno = :deptno"

 On Error GoTo paramerr

 'Attempt to refresh the data control.

 'An error should occur, because AutoBind=False, the SQL

 'statement contains a parameter, and the SQL statement

 'needs to be bound before execution.

 oradata1.Refresh

Exit Sub

paramerr:

 MsgBox oradata1.Database.Session.LastServerErrText

Exit Sub

End Sub


BackColor Property

Applies To

Oracle Data Control.

Description

Determines the background color of an object.

For more information, see the MS Visual Basic help and documentation.


See Also

ForeColor Property


Caption Property

Applies To

Oracle Data Control.

Description

Determines the text displayed in or next to a control.

For more information, see the MS Visual Basic help and documentation.


Connect Property: Data Control
Connect Property

Applies To

Oracle Data Control.

Description

The username and password to be used when connecting the data control to an Oracle database. Read/write at design time and run time.

Usage

oradata1.Connect = [ username/password ]

Remarks

This string is passed to the OpenDatabase method of the OraSession object when the control is refreshed. Changing this property does not take effect until a Refresh method is sent to the data control.

If the data control is refreshed and the Connect property has not been specified, the refresh will fail.

Examples of valid Connect properties include:

"scott/tiger"

"system/manager"

Data Type

String


See Also

OpenDatabase Method

OraSession Object

Refresh Method


Database Object Property: Data Control
Database Property

Applies To

Oracle Data Control.

Description

Returns the OraDatabase object associated with the data control. Not available at design time and read-only at run time.

Usage

oradatabase = oradata1.Database

Remarks

If the data control has not been refreshed, any references to this property results in an "Object variable not set" runtime error.

Changing this property has no effect until a Refresh method is sent to the data control.

Data Type

OLE Object (OraDatabase)


See Also

OraDatabase Object

Refresh Method


DatabaseName Property: Data Control
DatabaseName Property

Applies To

Oracle Data Control.

Description

The Oracle SQL*Net specifier used when connecting the data control to an Oracle database. Read/write at design time and run time.

Usage

oradata1.DatabaseName = [ DatabaseName ]

Remarks

The Oracle SQL*Net specifier should include the Oracle SQL*Net protocol identifier, Oracle database name, and optional database instance (SQL*Net aliases can also be used). This string is passed to the OpenDatabase method of the OraSession object when the control is refreshed. Changing this property does not take effect until a Refresh method is sent to the data control.

If the data control is refreshed and DatabaseName has not been specified, the refresh fails.

Examples of valid DatabaseName properties include:

"t:oracle:PROD"

"p:Oracle8:demo"

"x:orasrv"

"mydbalias"	(Where mydbalias represents "t:mfg:prod")

Data Type

String


See Also

OpenDatabase Method

OraSession Object

Refresh Method


DragIcon Property

Applies To

Oracle Data Control.

Description

Determines the icon to be displayed as the pointer in a drag-and-drop operation.

For more information, see the MS Visual Basic help and documentation.


See Also

Drag Method

DragDrop Event

DragMode Property

DragOver Event


DragMode Property

Applies To

Oracle Data Control.

Description

Determines manual or automatic dragging mode for a drag-and-drop operation.

For more information, see the MS Visual Basic help and documentation.


See Also

Drag Method

DragDrop Event

DragIcon Property

DragOver Event


EditMode Property: Data Control
EditMode Property

Applies To

Oracle Data Control.

Description

Returns the current editing state for the current row. Not available at design time and read-only at run time.

Usage

edit_mode = oradata1.EditMode

Remarks

The EditMode property values are:

Constant	Value	Description

ORADATA_EDITNONE	0	No editing in progress
ORADATA_EDITMODE	1	Editing is in progress on an existing row
ORADATA_EDITADD	2	A new record is being added and the copy buffer does not currently represent an actual row in the database.

These values are located in the file ORACONST.TXT and are intended to match similar constants in the Visual Basic file CONSTANT.TXT.

This property is affected only by the Edit, AddNew, and Update methods.

Data Type

Integer


See Also

AddNew Method

Edit Method

Update Method


Enabled Property

Applies To

Oracle Data Control.

Description

Determines whether the control can respond to user-generated events.

For more information, see the MS Visual Basic help and documentation.


See Also

Visible Property


ForeColor Property

Applies To

Oracle Data Control.

Description

Determines the foreground color used to display text and graphics in an object.

For more information, see the MS Visual Basic help and documentation.


See Also

BackColor Property


Height Property

Applies To

Oracle Data Control.

Description

Determines the height dimension of an object.

For more information, see the MS Visual Basic help and documentation.


See Also

Left Property

Move Method

Top Property

Width Property


Index Property

Applies To

Oracle Data Control.

Description

Specifies the number that uniquely identifies a control in a control array. Available at design time only if the control is part of a control array; read-only at run time.

For more information, see the MS Visual Basic help and documentation.


See Also

Tag Property


Left Property

Applies To

Oracle Data Control.

Description

Determines the distance between the internal left edge of an object and the left edge of its container.

For more information, see the MS Visual Basic help and documentation.


See Also

Move Method

Top Property


MousePointer Property

Applies To

Oracle Data Control.

Description

Determines the type of mouse pointer displayed when the mouse is over a particular part of a form or control at run time.

For more information, see the MS Visual Basic help and documentation.


See Also

DragIcon Property

MouseMove Event


Name Property: Data Control
Name Property

Applies To

Oracle Data Control.

Description

Specifies the name used in code to identify a form, control, or data access object. Not available at run time.

For more information, see the MS Visual Basic help and documentation.


Options Property: Data Control
Options Property

Applies To

Oracle Data Control.

Description

Determines one or more characteristics of the database and all dynasets associated with the data control. Read/write at design time and run time.

Usage

oradata1.Options = database_options

database_options = oradata1.Options

Remarks

This property is a bit flag word used to set the optional modes of the database. If options = 0, the default settings will apply. The following modes are available:

Column Defaulting mode.

The default mode is called VB mode. In VB mode, field (column) values not explicitly set are set to NULL when using  AddNew or Edit.

Optionally you can use Oracle mode. Oracle mode indicates that changes made to fields (columns) are immediately reflected in the local mirror by retrieving the changed row from the database, thus allowing Oracle to set defaults for the columns and perform required calculations. Column Defaulting mode affects the behavior of the AddNew and Edit methods.

Lock Wait mode.

The default mode is called Wait mode. In Wait mode, when dynaset rows are about to be modified (using Edit), the existing row in the database is retrieved using "SELECT ... FOR UPDATE" to lock the row in the database. If the row about to be changed has been locked by another process (or user), the "SELECT ... FOR UPDATE", waits until the row is unlocked before proceeding.

Optionally you may use NoWait mode. NoWait mode results in an immediate return of an error code, indicating that the row about to be updated is locked.

Lock Wait mode also affects any SQL statements processed using ExecuteSQL.

No Refetch mode.

In this  mode  NULLs are not explicitly inserted as in the ORADB_ORAMODE. In ORADB_NO_REFETCH mode, performance is boosted, since data is not refetched to the local cache.

The options flag values are:

Constant	Value	Description

ORADB_DEFAULT	&H0&	Accept the default behavior.
ORADB_ORAMODE	&H1&	Let Oracle set default field (column) values.
ORADB_NOWAIT	&H2&	Do not wait on row locks when executing a "SELECT ... FOR UPDATE".
ORADB_NO_REFETCH	&H4&	Let Oracle set default field (column) values as in ORADB_ORAMODE, but data is not refetched after the insert or update operation.

These values can be found in the file ORACONST.TXT.

Options may be combined by adding their respective values.

This property is the same as the options passed to the OpenDatabase method. Just as with OpenDatabase, these options affect the OraDatabase object and all associated dynasets created from that database.

Changing this property does not take effect until a Refresh method is sent to the data control.

Data Type

Long Integer


See Also

AddNew Method

Edit Method

CreateDynaset Method

OpenDatabase Method

OraDatabase Object

OraDynaset Object

Refresh Method

Locks and Editing


ReadOnly Property

Applies To

Oracle Data Control.

Description

Determines whether the dynaset will be used for read-only operations. Read/write at design time and run time.

Usage

oradata1.ReadOnly = [ True | False ]

Remarks

By default, ReadOnly is False which means that an attempt will be made to create an updatable dynaset by selecting ROWIDs from the database. If ReadOnly is set to True, a nonupdatable dynaset is created (ROWIDs are not selected from the database and cached) and operations will be somewhat faster.

If the SELECT statement contains a LONG or LONG RAW column, ROWIDs are needed whether the dynaset will be updatable or not.

Changing this property does not take effect until a Refresh method is sent to the data control.

Data Type

Integer (Boolean)


See Also

CreateDynaset Method

Long and Long Raw Columns

Refresh Method


Recordset Property

Applies To

Oracle Data Control.

Description

Returns a dynaset defined by the data control's Connect, DatabaseName, and RecordSource properties. Not available at design time and read and write at run time.

Usage

Set oradynaset = oradata1.Recordset

Set oradata1.Recordset=Oradynaset

Remarks

The properties and methods of this dynaset are the same as those of any other dynaset object. The Recordset property of the Oracle Data Control (.OCX) can be set to external dynaset, or the recordset property of the other data control. After the setting ,Oracle Data control Database,session,options properties now set to the corresponding properties of the external dynaset. Oracle data control shares the advisaries of the external dynaset. This is very useful when attaching dynaset returned from the PLSQL cursor by CreatePlsqlDynaset Method.

Data Type

OLE Object (OraDynaset)


RecordSet Property Example

This example demonstrates setting recordset property to external dynaset created by  CreatePlsqlDynaset method. This example returns PLSQL cursor as a external dynaset for the different values of DEPTNO parameter. Make sure that corresponding stored procedure (found in EMPCUR.SQL) is available in the Oracle Server and paste this code into the definition section of a form, then press F5.

Sub Form_Load ()

 'Declare variables as OLE Objects.

 Dim OraSession As Object

 Dim OraDatabase As Object

 Dim OraDynaset As Object

 'Create the OraSession Object.

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 'Create the OraDatabase Object by opening a connection to Oracle.

 Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

' Create the Deptno parameter

 OraDatabase.Parameters.Add "DEPTNO", 10, ORAPARM_INPUT

 OraDatabase.Parameters("DEPTNO").ServerType = ORATYPE_NUMBER

' Create OraDynaset based on "EmpCursor" created in stored procedure.

 Set OraDynaset = OraDatabase.CreatePLSQLDynaset("Begin Employee.GetEmpData (:DEPTNO,:EmpCursor); end;", "EmpCursor", 0&)

' Now attach the Oradynaset to Data control's recordset.

  set oradata1.recordset = OraDynaset

'

'

'  Do some operation

'

'

 ' Now set the deptno value to 20

 OraDatabase.Parameters("DEPTNO").Value = 20

 'Refresh the sqlstmt

 Oradata1.recordset.Refresh

  'Remove the parameter.

 OraDatabase.Parameters.Remove ("DEPTNO")

 End Sub


See Also

Connect Property

DatabaseName Property

MoveFirst, MoveLast, MoveNext, MovePrevious Methods

OraDynaset Object

OraFields Collection

OraParameters Collection

RecordSource Property


RecordSource Property

Applies To

Oracle Data Control.

Description

The SQL select statement to be used to create the data control’s RecordSet. Read/write at design time and run time.

Usage

oradata1.RecordSource = [ SQL SELECT Statement ]

Remarks

The SQL statement must be a SELECT statement; otherwise an error is returned. Features such as views, synonyms, column aliases, schema references, table joins, nested selects, and remote database references can be used freely; object names are not modified in any way.

The updatability of the resultant dynaset depends on the Oracle SQL rules of updatability, on the access you have been granted, and on the ReadOnly property. In order to be updatable, three conditions must be met:

1.	the SQL statement must refer to a simple column list or to the entire column list (*),

2.	the SQL statement must not set the read-only flag of the options argument, and

3.	Oracle must permit ROWID references to the selected rows of the query.

Any SQL statement that does not meet these criteria is processed, but the results are not updatable and the dynaset’s Updatable property returns False.

Changing this property does not take effect until a Refresh method is sent to the data control.

You can use SQL bind variables in conjunction with the OraParameters collection.

If this property is NULL or empty, then an OraDynaset object is not created, but OraSession, OraConnection, and OraDatabase objects are created for the data control. This behavior enables access to these objects prior to creation of a dynaset. For example, a NULL RecordSource might be used to instantiate the database object for the purpose of adding parameters. RecordSource can then be set at run time, making use of the automatic binding of database parameters.

Changing this property and calling Refresh Method of RecordSet Property will create a new dynaset object, but the old dynaset continues to be available for use until all references to it are removed.

Data Type

String


See Also

Connect Property

DatabaseName Property

OraConnection Object

OraDatabase Object

OraDynaset Object

OraParameters Collection

OraSession Object

RecordSet Property

Refresh Method

Updatable Property


RecordSource Property Example

This example demonstrates the use of SQL bind variables (parameters) in the RecordSource property of the data control. Copy this code into the definition section of a form containing a data control named oradata1. Then press F5.

Sub Form_Load ()

 'Set the username and password.

 oradata1.Connect = "scott/tiger"

 'Set the databasename.

 oradata1.DatabaseName = "ExampleDb"

 'Refresh the data control without setting the

 ' recordsource. This has the effect of creating

 ' the underlying database object so that parameters

 ' may be added.

 oradata1.Refresh

 'Set the recordsource and use a SQL parameter.

 oradata1.RecordSource = "select * from emp where job = :job"

 'Add the job input parameter with initial value MANAGER.

 oradata1.Database.Parameters.Add "job", "MANAGER", 1

 'Refresh the data control.

 'Only employees with the job MANAGER will be contained

 'in the dynaset.

 oradata1.Refresh

 'Change the value of the job parameter to SALESMAN.

 oradata1.Database.Parameters("job").Value = "SALESMAN"

 'Refresh ONLY the recordset.

 'Only employees with the job SALESMAN will be contained

 ' in the dynaset.

 oradata1.Recordset.DbRefresh

End Sub


Session Object Property: Data Control
Session Property

Applies To

Oracle Data Control.

Description

The session object associated with the data control. Not available at design time and read-only at run time.

Usage

orasession = oradata1.Session

Remarks

This property is equivalent to referencing oradata1.Database.Session. If the data control has not been refreshed, any references to this property result in an "Object variable not set" runtime error.

Data Type

OLE Object (OraSession)


See Also

OraDatabase Object

OraSession Object

OraSessions Collection


Tag Property

Applies To

Oracle Data Control.

Description

Stores any extra data needed by your application.

For more information, see the MS Visual Basic help and documentation.


Top Property

Applies To

Oracle Data Control.

Description

Determines the distance between the internal top edge of an object and the top edge of its container.

For more information, see the MS Visual Basic help and documentation.


See Also

Move Method

Left Property


TrailingBlanks Property

Applies To

Oracle Data Control.

Description

Determines whether trailing blanks should be stripped from character string data retrieved from the database. Read/write at design time and run time.

Usage

oradata1.TrailingBlanks = [ True | False ]

Remarks

By default, TrailingBlanks is False  This means that trailing blanks will be stripped from character string data retrieved from the database.

Changing this property has no effect until a Refresh method is sent to the data control.

Data Type

Integer (Boolean)


See Also

CreateDynaset Method

Refresh Method


Visible Property

Applies To

Oracle Data Control.

Description

Determines whether an object is visible or hidden.

For more information, see the MS Visual Basic help and documentation.


See Also

Enabled Property


Width Property

Applies To

Oracle Data Control.

Description

Determines the width dimension of an object.

For more information, see the MS Visual Basic help and documentation.


See Also

Height Property

Left Property

Move Method

Top Property


Drag Method

Applies To

Oracle Data Control.

Description

Begins, ends, or cancels dragging controls.

For more information, see the MS Visual Basic help and documentation.


See Also

DragDrop Event

DragIcon Property

DragMode Property

DragOver Event

MousePointer Property


Move Method

Applies To

Oracle Data Control.

Description

Moves a form or control.

For more information, see the MS Visual Basic help and documentation.


See Also

Height Property

Left Property

Top Property

Width Property


Refresh Method: Data Controls
Refresh Method

Applies To

Oracle Data Control.

Description

This method re-creates the OraDatabase and OraDynaset objects referenced within the data control and reestablishes a dynaset using  the SQL statement from the RecordSource property and the connection information from the Connect and DatabaseName properties.

Usage

oradata1.Refresh

Remarks

If an existing dynaset has been assigned to an object variable in Visual Basic, then Refresh creates a new dynaset for the data control, but the old dynaset continues to be available for use until all references to it are removed.


See Also

Connect Property

DatabaseName Property

OraDatabase Object

OraDynaset Object

RecordSource Property

SQL Property


UpdateControls Method

Applies To

Recordset property of the Oracle Data Control.

Description

Gets the current record from a data control's recordset and displays the appropriate data in controls bound to that data control.

Usage

oradata1.Recordset.UpdateControls

oradata1.Recordset.DbUpdateControls (Required for Visual Basic 3.0 users)

Remarks

Use this method to allow the user to cancel changes made to bound controls and restore the contents of those controls to their original values.

This method has the effect of making the current record current again, except that no events occur.

Note: For backward compatibility with earlier .VBX control, this method is also available as the method of data control’s Recordset.


See Also

Recordset Property

UpdateRecord Method


UpdateControls Method Example

This example cancels changes made to bound controls and restores the data to the original values. Copy this code into the definition section of a form that has a data control named oradata1 (which has been successfully refreshed) and has the KeyPreview property set to True. Then press F5.

Sub Form_KeyDown (KeyCode As Integer, Shift As Integer)

Const KEY_ESCAPE = &H1B

If KeyCode = KEY_ESCAPE Then

oradata1.recordset.DbUpdateControls

End If

End Sub


UpdateRecord Method

Applies To

Recordset property of the Oracle Data Control.

Description

Saves the current values of bound controls.

Usage

oradata1.UpdateRecord

oradata1.DbUpdateRecord (Required for Visual Basic 3.0 users)

Remarks

This method allows you to save the current value of bound controls during a Validate event without generating another Validate event.

This method has the effect of executing the Edit method, changing a field, and executing the Update method, except that no events occur.

Note: For backward compatibility with earlier .VBX control, this method is also available as the method of data control’s Recordset.


See Also

Edit Method

Recordset Property

Update Method

Validate Event


ZOrder Method

Applies To

Oracle Data Control.

Description

Places a specified form or control at the front or back of the z-order within its graphical level.

For more information, see the MS Visual Basic help and documentation.


DragDrop Event

Applies To

Oracle Data Control.

Description

Occurs when a drag-and-drop operation is completed as a result of either dragging a control over a form or control and releasing the mouse button, or using the Drag method with its action argument = 2 (Drop).

For more information, see the MS Visual Basic help and documentation.


See Also

Drag Method

DragIcon Property

DragMode Property

DragOver Event

MouseDown Event

MouseMove Event

MouseUp Event


DragOver Event

Applies To

Oracle Data Control.

Description

Occurs when a drag-and-drop operation is in progress. You can use this event to monitor when the mouse pointer enters, leaves, or is directly over a valid target. The mouse pointer position determines which target object receives this event.

For more information, see the MS Visual Basic help and documentation.


See Also

Drag Method

DragDrop Event

DragIcon Property

DragMode Property

MouseDown Event

MouseMove Event

MouseUp Event


Error Event

Applies To

Oracle Data Control.

Description

This event is fired whenever an interactive operation causes an error. You can perform some operations directly with the data control, such as using the data control buttons or when the data control refreshes automatically when the form loads. In these cases, the Error event is fired instead of causing a normal runtime error.

For more information, see the MS Visual Basic help and documentation.


See Also

AddNew Method

Delete Method

MoveFirst, MoveLast, MoveNext, MovePrevious Methods


MouseDown Event

Applies To

Oracle Data Control.

Description

This event is fired whenever a mouse button is pressed (MouseDown) and the mouse pointer is over the data control, or has been captured by the data control. The mouse is captured if a mouse button has been pressed previously over the data control until all corresponding MouseUp events have been received.

For more information, see the MS Visual Basic help and documentation.


See Also

MouseMove Event

MousePointer Property

MouseUp Event


MouseMove Event

Applies To

Oracle Data Control.

Description

This event is fired continuously whenever the mouse pointer moves across the data control. Unless another object has not captured the mouse, the data control recognizes a MouseMove event whenever the mouse position is within its borders.

For more information, see the MS Visual Basic help and documentation.


See Also

MousePointer Property

MouseDown Event

MouseUp Event


MouseUp Event

Applies To

Oracle Data Control.

Description

This event is fired whenever a mouse button is released (MouseUp) and the mouse pointer is over the data control, or has been captured by the data control. The mouse is captured if a mouse button has been pressed previously over the data control until all corresponding MouseUp events have been received.

For more information, see the MS Visual Basic help and documentation.


See Also

MouseMove Event

MousePointer Property

MouseDown Event


Reposition Event

Applies To

Oracle Data Control.

Description

This event is fired whenever the database record pointer is successfully repositioned to a new location. The Validate event is always fired before Reposition.

For more information, see the MS Visual Basic help and documentation.


See Also

Error Event

FindFirst, FindLast, FindNext, FindPrevious Methods

MoveFirst, MoveLast, MoveNext, MovePrevious Methods

Validate Event


Validate Event

Applies To

Oracle Data Control.

Description

This method is called whenever a variety of circumstances occur. It is sent when an attempt is made to move to a new record position, to delete a record, add a record, move to a bookmark, or to roll back the dynasets in the session. Validate is always called before the operation proceeds and any action is taken.

For more information, see the MS Visual Basic help and documentation.


See Also

AddNew Method

BookMark Property

Close Method

Delete Method

Edit Method

EditMode Property

FindFirst, FindLast, FindNext, FindPrevious Methods

MoveFirst, MoveLast, MoveNext, MovePrevious Methods

Update Method

UpdateRecord Method


OLE Server Methods

Add AddNew AddTable  AppendChunk AutoBindDisable AutoBindEnable BeginTrans Clone Close  CommitTrans ConnectSession CreateCustomDynaset  CreateDatabasePool  CreateDynaset CreateNamedSession CreateSession CreateSQL  Delete DestroyDatabasePool  Edit ExecuteSQL FieldSize	FindFirst  FindLast  FindNext  FindPrevious  GetDatabaseFromPool   GetChunk GetChunkByte GetChunkByteEx LastServerErrReset MoveFirst MoveLast MoveNext MovePrevious  MoveNextn  MovePreviousn  MoveRel  MoveTo  OpenDatabase ReadChunk Refresh Remove ResetTrans Rollback Update


OLE Server Methods

Add AddNew AddTable  AppendChunk AppendChunkByte AutoBindDisable AutoBindEnable BeginTrans Clone Close  CommitTrans ConnectSession CopyToClipboard CreateCustomDynaset  CreateDatabasePool  CreateDynaset CreateNamedSession CreatePlsqlCustomDynaset CreatePlsqlDynaset CreateSession CreateSQL  Delete DestroyDatabasePool  Edit ExecuteSQL FieldSize	FindFirst  FindLast  FindNext  FindPrevious  GetChunk GetChunkByte GetChunkByteEx GetDatabaseFromPool   LastServerErrReset MoveFirst MoveLast MoveNext MovePrevious  MoveNextn  MovePreviousn  MoveRel  MoveTo  OpenDatabase OriginalItem OriginalName ReadChunk Refresh Remove ResetTrans Rollback Update


OLE Server Methods

Add AddNew AddTable  AppendChunk AutoBindDisable AutoBindEnable BeginTrans Clone Close  CommitTrans ConnectSession CreateCustomDynaset  CreateDynaset CreateNamedSession CreateSession CreateSQL  Delete DestroyDatabasePool  Edit ExecuteSQL FieldSize	FindFirst  FindLast  FindNext  FindPrevious  GetChunk LastServerErrReset MoveFirst MoveLast MoveNext MovePrevious  MoveNextn  MovePreviousn  MoveRel  MoveTo  OpenDatabase ReadChunk Refresh Remove ResetTrans Rollback Update


Add Method

See Also	Example

Applies To

OraParameters

Description

Adds a parameter to the OraParameters collection.

Usage

oraparameters.Add  Name, Value, IOType

Arguments

Name	The name of the parameter to be added to the parameters collection. This name isused both for parameter identification and as the placeholder in associated SQL and PL/SQL statements.
Value	A variant specifying the initial value of the parameter. The initial value of the parameter is significant, as it defines the data type of the parameter.
IOType	An integer code specifying how the parameter is to be used in SQL statements and PL/SQL blocks.

The IOType settings are:

ORAPARM_INPUT	1	This should be used for input variables only.
ORAPARM_OUTPUT	2	This should be used for output variables only.
ORAPARM_BOTH	3	This should be used for variables that are both input and output.

These values can be found in the file ORACONST.TXT.

By default, the maximum size of the ORAPARM_OUTPUT variable for ServerType VAR and VARCHAR2 is set to 128 bytes. Use the MinimumSize property to change this value. The minimum size of an ORAPARM_OUTPUT variable for VAR and VARCHAR2 must always be greater than the size of the expected data from the database column.

You should be careful to make sure that this value is correct. If you set an incorrect option, such as ORAPARAM_BOTH for a stored procedure parameter type IN, this can result in errors. In other words ORAPARAM_BOTH means "for IN OUT parameters only". It does not mean that you should use the parameter against one stored procedure that has an IN parameter and then use it in another that has an OUT param. In such a case you should use two parameters. Errors caused in this way are rare, but in the case of parameter-related errors, you should verify that the IOTYPE is correct.

Remarks

Use parameters to represent SQL bind variables (as opposed to rebuilding the SQL statement). SQL bind variables are especially useful because you can change a parameter value without having to reparse the query. Use SQL bind variables only as input variables.

You can also use parameters to represent PL/SQL bind (in/out) variables. You can use PL/SQL bind variables as both input and output variables.


See Also

OraParameter Object

OraParameters Collection

Remove Method


Add Method Example

This example demonstrates the Add and Remove parameter methods, the ServerType parameter property and the the ExecuteSQL database method to call a Stored Procedure and Function (located in ORAEXAMP.SQL). Copy and paste this code into the definition section of a form. Then press F5.

Sub Form_Load ()

'Declare variables as OLE Objects.

 Dim OraSession As Object

 Dim OraDatabase As Object

 Dim OraDynaset As Object

 'Create the OraSession Object.

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 'Create the OraDatabase Object.

 Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

 'Add EMPNO as an Input/Output parameter and set its initial value.

 OraDatabase.Parameters.Add "EMPNO", 7369, ORAPARM_INPUT

 OraDatabase.Parameters("EMPNO").ServerType = ORATYPE_NUMBER

 'Add ENAME as an Output parameter and set its initial value.

 OraDatabase.Parameters.Add "ENAME", 0, ORAPARM_OUTPUT

 OraDatabase.Parameters("ENAME").ServerType = ORATYPE_VARCHAR2

 'Add SAL as an Output parameter and set its initial value.

 OraDatabase.Parameters.Add "SAL", 0, ORAPARM_OUTPUT

 OraDatabase.Parameters("SAL").ServerType = ORATYPE_NUMBER

 'Execute the Stored Procedure Employee.GetEmpName to retrieve ENAME.

 ' This Stored Procedure can be found in the file ORAEXAMP.SQL.

 OraDatabase.DbExecuteSQL ("Begin Employee.GetEmpName (:EMPNO, :ENAME); end;")

 'Display the employee number and name.

 'Execute the Stored Function Employee.GetSal to retrieve SAL.

 ' This Stored Function can be found in the file ORAEXAMP.SQL.

 OraDatabase.DbExecuteSQL ("declare SAL number(7,2); Begin :SAL:=Employee.GetEmpSal (:EMPNO); end;")

 'Display the employee name, number and salary.

 MsgBox "Employee " & OraDatabase.Parameters("ENAME").value & ", #" & OraDatabase.Parameters("EMPNO").value & ",Salary=" & OraDatabase.Parameters("SAL").value

 'Remove the Parameters.

 OraDatabase.Parameters.Remove "EMPNO"

 OraDatabase.Parameters.Remove "ENAME"

 OraDatabase.Parameters.Remove "SAL"

End Sub


AddNew Method

See Also	Example

Applies To

OraDynaset

Description

Clears the copy buffer and begins a record insertion operation into the specified dynaset and associated database.

Usage

oradynaset.AddNew

oradynaset.DbAddNew

Remarks

Upon initiation of a AddNew operation, values of fields present within the dynaset are maintained in a copy buffer and do not yet reflect the actual contents of the database.

The values of the fields are modified through the OraField object, and committed with Update or when database movement occurs, which discards the new row. Field values that have not been explicitly assigned are either set to NULL or allowed to default by way of the Oracle default mechanism, depending on the Column Defaulting mode of the options flag used when the OpenDatabase method was called. In either case, fields that appear in the database table but not in the dynaset are always defaulted by the Oracle default mechanism.

Internally, records are inserted by AddNew using the "INSERT into TABLE (...) VALUES (...)" SQL statement, and are therefore added to the end of the table.

Note: A call to Edit, AddNew, or Delete, will cancel any outstanding Edit or AddNew calls before proceeding. Any outstanding changes not saved using Update will be lost during the cancellation.


See Also

Delete Method

Edit Method

EditMode Property

Long and Long Raw Columns

MoveFirst, MoveLast, MoveNext, MovePrevious Methods

Update Method

Validate Event


AddNew Method Example

This example demonstrates the use of AddNew and Update to add a new record to a dynaset. Copy this code into the definition section of a form. Then press F5.

Sub Form_Load ()

 'Declare variables as OLE Objects.

 Dim OraSession As Object

 Dim OraDatabase As Object

 Dim OraDynaset As Object

 'Create the OraSession Object.

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 'Create the OraDatabase Object by opening a connection to Oracle.

 Set OraDatabase = OraSession.DbOpenDatabase("ExampleDb", "scott/tiger", 0&)

 'Create the OraDynaset Object.

 Set OraDynaset = OraDatabase.DbCreateDynaset("select * from emp", 0&)

 'Begin an AddNew.

 OraDynaset.DbAddNew

 'Set the field(column) values.

 OraDynaset.Fields("EMPNO").Value = "1000"

 OraDynaset.Fields("ENAME").Value = "WILSON"

 OraDynaset.Fields("JOB").Value = "SALESMAN"

 OraDynaset.Fields("MGR").Value = "7698"

 OraDynaset.Fields("HIREDATE").Value = "19-SEP-92"

 OraDynaset.Fields("SAL").Value = 2000

 OraDynaset.Fields("COMM").Value = 500

 OraDynaset.Fields("DEPTNO").Value = 30

 'End the AddNew and Update the dynaset.

 OraDynaset.DbUpdate

 MsgBox "Added one new employee."

End Sub


AddTable Method

Example

 Applies To

OraParameters

Description

Adds an array parameter to the OraParameters collection.

Usage

oraparamarray.AddTable  Name, IOType, ServerType, ArraySize , ElementSize

Note that ElementSize is optional in all cases except when bound to text strings.

Arguments

Name	The name of the parameter to be added to the parameters collection. This name is used both for parameter identification and as the placeholder in associated SQL and PL/SQL  statements.

IOType	An integer code specifying how the parameter is to be used in SQL statements and PL/SQL blocks.

The IOType settings are:

Constant	Value	Description

ORAPARM_INPUT	1	This should be used for input variables only.
ORAPARM_OUTPUT	2	This should be used for output variables only.
ORAPARM_BOTH	3	This should be used for variables that are both input and output.

You should be careful to make sure that this value is correct. If you set an incorrect option, such as ORAPARAM_BOTH for a stored procedure parameter type IN, this can result in errors. In other words ORAPARAM_BOTH means "for IN OUT parameters only". It does not mean that you should use the parameter against one stored procedure that has an IN parameter and then use it in another that has an OUT param. In such a case you should use two parameters. Errors caused in this way are rare, but in the case of parameter-related errors, you should verify that the IOTYPE is correct.

These values (the constants defined as 1, 2, and 3) can be found in the file ORACONST.TXT.

Server Type

Specifies the basic type of the parameter. Valid values are the following:

ORATYPE_VARCHAR2	…1

ORATYPE_NUMBER	…2

ORATYPE_SINT 	…3

ORATYPE_FLOAT	…4

ORATYPE_STRING	…5

ORATYPE_VARCHAR	…9

ORATYPE_DATE	…12

ORATYPE_UINT	…68

ORATYPE_CHAR	…96

ORATYPE_CHARZ	…97

Note:

•	External data type ORATYPE_NUMBER allows  decimal precision of 1 to 38.

•	The maximum positive number is  0.99999999999999999999 E + 38      (decimal followed by 20 nines)

•	The minimum positive number is 0.1 E -38

•	The minumum negative  number is  - 0.99999999999999999999 E + 38  (decimal followed by 20 nines)

•	The maximum negative number is 0.1 E -38

ArraySize

Defines the number of elements in the parameter array.. The  maximum length of  the buffer is 32512 bytes. This parameter is used to calculate the maximum buffer length. The valid range for this parameter depends on the VarType parameter.

VarType	Dimension
ORATYPE_NUMBER	Valid range from 1 to 1477
ORATYPE_SINT
ORATYPE_FLOAT
ORATYPE_UINT
ORATYPE_VARCHAR2	Valid range from 1 to 32512. The maximum legal value of Dimension  depends on the Size  parameter. Size * Dimension should be less than 32512 bytes.
ORATYPE_STRING
ORATYPE_VARCHAR
ORATYPE_CHARZ
ORATYPE_CHAR
ORATYPE_DATE	Valid range from 1 to 4644.
ElementSize (optional)

Valid for only character and string type table (array) parameters. The valid size for ElementSize depends on the VarType. These ranges are listed below.

VarType	Size
ORATYPE_VARCHAR2	Valid range from 1 to 1999
ORATYPE_VARCHAR
ORATYPE_STRING
ORATYPE_CHAR	Valid range from 1 to 255.
ORATYPE_CHARZ
Remarks

Use parameters to represent SQL bind variables for array insert/update/delete (as opposed to rebuilding the SQL statement). SQL bind variables are especially useful because you can change a parameter value without having to reparse the query. Use SQL bind variables only as input variables.

You can also use parameters to represent PL/SQL bind (in/out) variables. You can use PL/SQL bind variables as both input and output variables.


Using OraParamArrays With PL/SQL

The following is an example using OraParamArrays with PL/SQL.

 Dim OraSession As Object

 Dim OraDatabase As Object

 Dim OraDynaset As Object

 Dim OraPArray1 As Object

 Dim OraPArray2 As Object

 On Error GoTo Err_plsqlarr

Set OraSession = CreateObject("OracleInProcServer.XOraSession")

Set OraDatabase = OraSession.OpenDatabase("exampledb", "scott/tiger", 0&)

OraDatabase.Parameters.Add "ArraySize", 3, ORAPARM_INPUT

OraDatabase.Parameters.AddTable "EMPNOS", ORAPARM_INPUT, ORATYPE_NUMBER, 3, 22

OraDatabase.Parameters.AddTable "ENAMES", ORAPARM_OUTPUT, ORATYPE_VARCHAR2, 3,

10

If OraDatabase.LastServerErr <> 0 Or OraDatabase.LastServerErrText <> "" Then

  MsgBox "Error"

End If

Set OraPArray1 = OraDatabase.Parameters("EMPNOS")

Set OraPArray2 = OraDatabase.Parameters("ENAMES")

'Initialize the newly created input parameter table EMPNOS

OraPArray1.put_Value 7698, 0

OraPArray1.put_Value 7782, 1

OraPArray1.put_Value 7654, 2

OraDatabase.DbExecuteSQL ("Begin Employee.GetEmpNamesInArray(:ArraySize,

:EMPNOS, :ENAMES); End;")

If OraDatabase.LastServerErr <> 0 Or OraDatabase.LastServerErrText <> "" Then

  MsgBox "Error " & Err.Description

End If

'Print out Enames

MsgBox OraPArray2.Get_Value(0)

MsgBox OraPArray2.Get_Value(1)

MsgBox OraPArray2.Get_Value(2)

Exit Sub

Err_plsqlarr:

    MsgBox Err.Description


AppendChunk Method

See Also	Example

Applies To

OraField

Description

Appends data from a string to a LONG or LONG RAW field in the copy buffer.

Usage

orafield.AppendChunk(string)

orafield.DbAppendChunk(string)

Arguments

string	Data to append to the specified field.

Remarks

AppendChunk allows the manipulation of data fields that are larger than 64KB.


See Also

FieldSize Method

GetChunk Method

Long and Long Raw Columns

OraField Object

Type Property


AppendChunk Method Example

This example demonstrates the use of AppendChunk to read a file into a LONG RAW column of a database. This example expects a valid dynaset named OraDynaset representing a table with a column named "longraw". Copy this code into the definition section of a form. Call this procedure with a valid filename.

Sub AppendChunkExample (FName As String)

 'Declare various variables.

 Dim NumChunks As Integer, RemChunkSize As Integer

 Dim TotalSize As Long, CurChunk As String

 Dim I As Integer, FNum As Integer, ChunkSize As Integer

 'Set the size of each chunk.

 ChunkSize = 10240

 frmChunk.MousePointer = HOURGLASS

 'Begin an add operation.

 OraDynaset.DbAddNew

 'Clear the LONGRAW field.

 OraDynaset.Fields("LONGRAW").Value = ""

 'Get a free file number.

 FNum = FreeFile

 'Open the file.

 Open FName For Binary As #FNum

 'Get the total size of the file.

 TotalSize = LOF(FNum)

 'Set number of chunks.

 NumChunks = TotalSize \ ChunkSize

 'Set number of remaining bytes.

 RemChunkSize = TotalSize Mod ChunkSize

 'Loop through the file.

 For I = 0 To NumChunks

  'Calculate the new chunk size.

  If I = NumChunks Then

   ChunkSize = RemChunkSize

  End If

  CurChunk = String$(ChunkSize, 32)

  'Read a chunk from the file.

  Get #FNum, , CurChunk

  'Append chunk to LONGRAW field.

  OraDynaset.Fields("LONGRAW").DbAppendChunk (CurChunk)

 Next I

'Complete the add operation and update the database.

OraDynaset.DbUpdate

 'Close the file.

 Close FNum

 frmChunk.MousePointer = DEFAULT

End Sub


AppendChunkByte Method

Example

Applies To

OraField Object

Description

Appends data from byte array to a LONG or LONG RAW field in the copy buffer.

Usage

orafield.AppendChunkByte(Byte Array)

Arguments

Byte Array	Data to append to the specified field.

Remarks

AppendChunkByte allows the manipulation of data fields that are larger than

64KB.


AppendChunkByte Method Example

This example demonstrates the use of AppendChunk to read a file into a LONG RAW column of a database. This example expects a valid dynaset named OraDynaset representing a table with a column named ?longraw?. Copy this code into the definition section of a form. Call this procedure  with a valid filename.

 Sub AppendChunkByteExample (FName As String)

 'Declare various variables.

 Dim NumChunks As Integer, RemChunkSize As Integer

 Dim TotalSize As Long, CurChunkByte() As Byte

 Dim I As Integer, FNum As Integer, ChunkSize As Integer

 'Set the size of each chunk.

 ChunkSize = 10240

 frmChunk.MousePointer = HOURGLASS

 'Begin an add operation.

 OraDynaset.DbAddNew

 'Clear the LONGRAW field.

 OraDynaset.Fields("LONGRAW").Value = ""

 'Get a free file number.

 FNum = FreeFile

 'Open the file.

 Open FName For Binary As #FNum

 'Get the total size of the file.

 TotalSize = LOF(FNum)

 'Set number of chunks.

 NumChunks = TotalSize \ ChunkSize

 'Set number of remaining bytes.

 RemChunkSize = TotalSize Mod ChunkSize

 'Loop through the file.

 For I = 0 To NumChunks

  'Calculate the new chunk size.

  If I = NumChunks Then

   ChunkSize = RemChunkSize

  End If

  ReDim CurChunkByte(ChunkSize)

  'Read a chunk from the file.

  Get #FNum, , CurChunkByte

  'Append chunk to LONGRAW field.

  OraDynaset.Fields("LONGRAW").DbAppendChunkByte (CurChunkByte)

 Next I

'Complete the add operation and update the database.

OraDynaset.DbUpdate

 'Close the file.

 Close FNum

 frmChunk.MousePointer = DEFAULT

End Sub


AutoBindDisable Method

See Also	Example

Applies To

OraParameter

OraParamArray

Description

Resets the AutoBind status of a parameter.

Usage

oraparameter.AutoBindDisable

Remarks

If a parameter has AutoBindDisabled status, it is not automatically bound to a SQL or PL/SQL statement.


See Also

AutoBindEnable Method

OraParameter Object


AutoBindDisable Method Example

This example demonstrates the use of AutoBindDisable and AutoBindEnable to prevent unnecessary parameter binding  while creating various dynasets that use different parameters. Copy this code into the definition section of a form. Then press F5.

Sub Form_Load ()

 'Declare variables as OLE Objects.

 Dim OraSession As object

 Dim OraDatabase As object

 Dim OraDynaset As object

 'Create the OraSession Object.

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 'Create the OraDatabase Object by opening a connection to Oracle.

 Set OraDatabase = OraSession. DbOpenDatabase("ExampleDb", "scott/tiger", 0&)

 'Add the job input parameter with initial value MANAGER.

 OraDatabase.Parameters.Add "job", "MANAGER", 1

 'Add the deptno input parameter with initial value 10.

 OraDatabase.Parameters.Add "deptno", 10, 1

 'Disable the deptno parameter for now.

 OraDatabase.Parameters("deptno").AutoBindDisable

 'Create the OraDynaset Object using the job parameter.

 Set OraDynaset = OraDatabase.DbCreateDynaset("select * from emp where job = :job", 0&)

 'Only employees with job=MANAGER will be contained in the dynaset.

 MsgBox "Employee #" & OraDynaset.Fields("empno").value & ", Job=" & OraDynaset.Fields("job").value

 'Enable the deptno parameter and disable the job parameter.

 OraDatabase.Parameters("deptno").AutoBindEnable

 OraDatabase.Parameters("job").AutoBindDisable

 'Create the OraDynaset Object using the deptno parameter.

 Set OraDynaset = OraDatabase.DbCreateDynaset("select * from emp where deptno = :deptno", 0&)

 'Only employees with deptno=10 will be contained in the dynaset.

 MsgBox "Employee #" & OraDynaset.Fields("empno").value & ", DeptNo=" & OraDynaset.Fields("deptno").value

End Sub


AutoBindEnable Method

See Also	Example

Applies To

OraParameter

OraParamArray

Description

Sets the AutoBind status of a parameter.

Usage

oraparameter.AutoBindEnable

Remarks

If a parameter has AutoBindEnabled status, it is automatically bound to a SQL or PL/SQL statement.


See Also

AutoBindDisable Method

OraParameter Object


AutoBindEnable Method Example

This example demonstrates the use of AutoBindDisable and AutoBindEnable to prevent unnecessary parameter binding  while creating various dynasets that use different parameters. Copy this code into the definition section of a form. Then press F5.

Sub Form_Load ()

 'Declare variables as OLE Objects.

 Dim OraSession As object

 Dim OraDatabase As object

 Dim OraDynaset As object

 'Create the OraSession Object.

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 'Create the OraDatabase Object by opening a connection to Oracle.

 Set OraDatabase = OraSession. DbOpenDatabase("ExampleDb", "scott/tiger", 0&)

 'Add the job input parameter with initial value MANAGER.

 OraDatabase.Parameters.Add "job", "MANAGER", 1

 'Add the deptno input parameter with initial value 10.

 OraDatabase.Parameters.Add "deptno", 10, 1

 'Disable the deptno parameter for now.

 OraDatabase.Parameters("deptno").AutoBindDisable

 'Create the OraDynaset Object using the job parameter.

 Set OraDynaset = OraDatabase.DbCreateDynaset("select * from emp where job = :job", 0&)

 'Only employees with job=MANAGER will be contained in the dynaset.

 MsgBox "Employee #" & OraDynaset.Fields("empno").value & ", Job=" & OraDynaset.Fields("job").value

 'Enable the deptno parameter and disable the job parameter.

 OraDatabase.Parameters("deptno").AutoBindEnable

 OraDatabase.Parameters("job").AutoBindDisable

 'Create the OraDynaset Object using the deptno parameter.

 Set OraDynaset = OraDatabase.DbCreateDynaset("select * from emp where deptno = :deptno", 0&)

 'Only employees with deptno=10 will be contained in the dynaset.

 MsgBox "Employee #" & OraDynaset.Fields("empno").value & ", DeptNo=" & OraDynaset.Fields("deptno").value

End Sub


BeginTrans Method

See Also	Example

Applies To

OraSession

OraConnection Object

Description

Begins a database transaction within the specified session.

Usage

orasession.BeginTrans

oraconnection.BeginTrans

Remarks

Once this method has been called, no database transactions are committed until a CommitTrans is issued. Alternatively, the session can be rolled back using Rollback. If a transaction has already been started, repeated use of BeginTrans causes an error.

If Update or Delete methods fail on a given row in a dynaset in a global transaction  (i.e. once you issue a BeginTrans), be aware that locks will remain on those rows on which you called Update or Delete. These locks will persist until you call CommitTrans or Rollback.


See Also

OraSession Object

OraConnection Object

CommitTrans Method

ResetTrans Method

Rollback Method


BeginTrans Method Example

This example demonstrates the use of BeginTrans to group a set of dynaset edits into a single transaction and Rollback to cancel those changes. Copy this code into the definition section of a form. Then press F5.

Sub Form_Load ()

 'Declare variables as OLE Objects.

 Dim OraSession As Object

 Dim OraDatabase As Object

 Dim OraDynaset As Object

 Dim fld As Object

 'Create the OraSession Object.

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 'Create the OraDatabase Object by opening a connection to Oracle.

 Set OraDatabase = OraSession. DbOpenDatabase("ExampleDb", "scott/tiger", 0&)

 'Create the OraDynaset Object.

 Set OraDynaset = OraDatabase.DbCreateDynaset("select * from emp", 0&)

 'Start Transaction processing.

 OraSession.DbBeginTrans

 'Setup a field object to save object references.

 Set fld = OraDynaset.Fields("sal")

 'Traverse until EOF is reached, setting

 'each employees salary to zero.

 Do Until OraDynaset.EOF = True

   OraDynaset.DbEdit

   fld.value = 0

   OraDynaset.DbUpdate

   OraDynaset.DbMoveNext

 Loop

 MsgBox "All salaries set to ZERO."

 'Currently, the changes have NOT been committed

 'to the database.

 'End Transaction processing.

 'Using RollbackTrans means the rollback can

 'be canceled in the Validate event.

 OraSession.DbRollback

 'MsgBox "Salary changes rolled back."

End Sub


Clone Method

See Also

Applies To

OraDynaset

Description

Returns a duplicate dynaset of the specified dynaset.

Usage

Set oradynaset2 = oradynaset1.Clone

Set oradynaset2 = oradynaset1.DbClone

Remarks

The method creates a duplicate dynaset of the one specified. The original and duplicate dynasets have their own current record. However, the new dynaset is not positioned on any row and has its EOF and BOF conditions set both to True. To change this, you must explicitly set a current row on the new duplicate with a Move or Find method.

Using Clone has no effect on the original dynaset. You cannot add, update, or remove records from a dynaset clone.

Use Clone to perform an operation on a dynaset that requires multiple current records.

A clone dynaset does not have all the properties set similar to a normal dynaset. The CacheBlock, CacheSliceSize, CacheSlicePerBlock, and FetchLimit are all set to Null.

Bookmarks of a dynaset and its clone are interchangeable; bookmarks of dynasets created with separate CreateDynaset methods are not interchangeable.


See Also

BookMark Property

CreateDynaset Method


Close Method

See Also

Applies To

OraDatabase

OraDynaset

OraSQLStmt

Description

Does nothing. Added for compatibility with Visual Basic.

Remarks

Neither the OraDatabase nor the OraDynaset object supports this method. Once an OraDatabase or OraDynaset object has gone out of scope and there are no references to it, the object closes automatically.


See Also

CreateDynaset Method

OpenDatabase Method

OraDatabase Object

OraDynaset Object

OraSQLStmt  Object


CommitTrans Method

See Also	Example

Applies To

OraConnection

OraSession

Description

Ends the current transaction and commits all pending changes to the database.

Usage

oraconnection.CommitTrans

oraconnection.DbCommitTrans

orasession.CommitTrans

orasession.DbCommitTrans

Remarks

oraconnection.CommitTrans commits all pending transactions for the specified connection. If not participating in a session-wide transaction (by using the session object's BeginTrans method) this method has no effect, because all operations are autocommitted whenever they are performed. However, when a session-wide transaction is in progress, you can use this method to commit the transactions for the specified connection prematurely.

Use this method with care. It undermines the normal operation of session-wide transactions. In many cases you can use the OraSession object's transaction protocol instead.

orasession.CommitTrans commits all transactions present within the session. CommitTrans is valid only when a transaction has been started with BeginTrans. If a transaction has not been started, use of CommitTrans causes an error.

Each connection within the session is committed separately, but no two-phase commit is used. This means that if one connection fails to commit, the session may be partially committed. However, the OraSession object is designed to serve the purpose of a transaction manager object; in the future, two-phase commit may be implemented among all connections within the transaction.

If transaction integrity is necessary, the application developer should ensure that all databases are opened using the same Connect and DatabaseName properties and thus share only one connection.


See Also

BeginTrans Method

OraConnection Object

OraSession Object

ResetTrans Method

Rollback Method


CommitTrans Method Example

This example demonstrates the use of BeginTrans to group a set of dynaset edits into a single transaction and ComitTrans to accept the changes.. Copy this code into the definition section of a form. Then press F5.

Sub Form_Load ()

 'Declare variables as OLE Objects.

 Dim OraSession As Object

 Dim OraDatabase As Object

 Dim OraDynaset As Object

 Dim fld As Object

 'Create the OraSession Object.

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 'Create the OraDatabase Object by opening a connection to Oracle.

 Set OraDatabase = OraSession. DbOpenDatabase("ExampleDb", "scott/tiger", 0&)

 'Create the OraDynaset Object.

 Set OraDynaset = OraDatabase.DbCreateDynaset("select * from emp", 0&)

 'Start Transaction processing.

 OraSession.DbBeginTrans

 'Setup a field object to save object references.

 Set fld = OraDynaset.Fields("sal")

 'Traverse until EOF is reached, setting

 'each employees salary to zero.

 Do Until OraDynaset.EOF = True

   OraDynaset.DbEdit

   fld.value = 0

   OraDynaset.DbUpdate

   OraDynaset.DbMoveNext

 Loop

 MsgBox "All salaries set to ZERO."

 'Currently, the changes have NOT been committed

 'to the database.

 'End Transaction processing.

 'Commit the changes to the database

 OraSession.DbCommitTrans

 MsgBox "Salary changes committed."

End Sub


ConnectSession Method

See Also	Example

Applies To

OraSession

Description

Returns the OraSession object with the specified name that is associated with the specified session's OraClient object.

Usage

Set orasession2 = orasession1.ConnectSession(session_name)

Arguments

session_name	A string, specifying the name of the session.

Remarks

This method is provided for simplicity and is equivalent to iterating through the OraSessions collection of the current session's OraClient object and searching for a session named session_name. The OraSessions collection will contain only sessions created via the current application and no others. This means that it is not possible to share sessions across applications, only within applications.


See Also

CreateSession Method

OraClient Object

OraSession Object

OraSessions Collection


ConnectSession Method Example

This example demonstrates the use of ConnectSession and CreateNamedSession to allow an application to use a session it previously created, but did not save. Copy this code into the definition section of a form. Then press F5.

Sub Form_Load ()

 'Declare variables as OLE Objects.

 Dim dfltsess As object

 Dim OraSession As object

 'Create the default OraSession Object.

 Set dfltsess = CreateObject("OracleInProcServer.XOraSession")

 'Try to connect to "ExampleSession". If it does not exist

 'an error is generated.

 On Error GoTo SetName

 Set OraSession = dfltsess.ConnectSession("ExampleSession")

 On Error GoTo 0

 'You can specify other processing here, such as creating a

 ' database and/or dynaset.

Exit Sub

SetName:

'The session named "ExampleSession" was not found, so create it.

Set OraSession = dfltsess.Client.CreateSession("ExampleSession")

Resume Next

End Sub


CopyToClipboard Method

Example

Applies To

OraDynaset

Description

Copy the rows from the dynaset to the Clipboard in text format.

Usage

OraDynaset.CopyToClipboard(NumOfRows ,colsep [optional],rowsep [optional]  )

Arguments

NumOfRows	Number of rows to be copied to the dynaset ,

colsep	Column seperater in CHAR to be inserted between columns

rowsep	Row seperater in CHAR to be inserted between Rows

Remarks

This method is used to facilitate transfer of data between Oracle Object for OLE’s cache (dynaset) and Windows applications such as Excel or Word. CopyToClipboard copies data starting from current position of the dynaset up to the last row.

Default column separator is TAB (ASCII 9).
Default row separator is ENTER (ASCII 13).


CopyToClipboard Method Example

The following example copies data from the dynaset to the clipboard. Paste this code into the definition section of a form, then press F5.

Sub Form_Load ()

 'Declare variables as OLE Objects.
 Dim OraSession As Object
 Dim OraDatabase As Object
 Dim OraDynaset As Object

 'Create the OraSession Object.
 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 'Create the OraDatabase Object by opening a connection to Oracle.
 Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
 Set OraDynaset = OraDatabase.CreateDynaset("select * from emp", 0&)

 'Now call CopyToClipboard to copy the entire dynaset
 OraDynaset.CopyToClipboard -1, chr(9), chr(13)

End Sub


CreateCustomDynaset Method

 Example

Applies To

OraDatabase

Description

Creates a dynaset using custom cache and fetch parameters

Usage

Set oradynaset = oradatabase.CreateCustomDynaset(sql_statement, options,  slicesize, perblock, blocks, FetchLimit, Fetchsize)

 Arguments

sql_statement	Any valid Oracle SQL SELECT statement.
slicesize	Cache slice size.
perblock	Cache slices per block.
blocks	Cache maximum number of blocks.
FetchLimit	Fetch array size.
FetchSize	Fetch array buffer size.
options	A bit flag indicating the status of any optional states of the dynaset. You can combine one or more options by adding their respective values. Specifying &H0& gives the following defaults for the dynaset: • Behave like Visual Basic Mode for a database: Field values not explicitly set are set to NULL, overriding server column defaults. • Perform automatic binding of database parameters. • Strip trailing blanks from character string data retrieved from the database. • Create an updatable dynaset. • Cache data on client. • Force a MoveFirst on dynaset creation. • Maintain read-consistency.
Constant	Value	Description
ORADYN_DEFAULT	&H0&	Accept the default behavior.
ORADYN_NO_AUTOBIND	&H1&	Do not perform automatic binding of database parameters.
ORADYN_NO_BLANKSTRIP	&H2&	Do not strip trailing blanks from character string data retrieved from the database.
ORADYN_READONLY	&H4&	Force dynaset to be read-only.
ORADYN_NOCACHE	&H8&	Do not create a local dynaset data cache. Without the local cache, previous rows within a dynaset are unavailable; however, increased performance results during retrieval of data from the database (move operations) and from the rows (field operations). Use this option in applications that make single passes through the rows of a dynaset for increased performance and decreased resource usage.
ORADYN_ORAMODE	&H10&	Same as Oracle Mode for a database except it affects only the dynaset being created. If  database was created in Oracle Mode, dynaset inherits the property from it (for compatability)
ORADYN_NO_REFETCH	&H20&	Behaves same as ORADB_NO_REFETCH mode for a database except this mode affects only the dynaset being created. If the database was created in ORADB_NO_REFETCH mode, the dynaset inherits the property for compatability.
ORADYN_N_MOVEFIRST	&H40&	Does not force a movefirst on dynaset creation. BOF and EOF are both TRUE.
ORADYN_DIRTY_WRITE	&H60&	Update and Delete will not check for read consistency.

These values can be found in the file ORACONST.TXT.

Remarks

The SQL statement must be a SELECT statement or an error is returned. Features such as simple views and synonyms can be used freely. You can also schema references, column aliases, table joins, nested selects and remote database references, but in each case you will end up with a read-only dynaset.

If you use a complex expression or SQL function on a column, such as "sal + 100" or "abs(sal)" , you will get an updatable dynaset, but the column associated with the complex expression will not be updatable.

Object names generally are not modifed, but in certain cases they can be changed. For example, if you use a column alias, you must use the alias to refer to the field by name. Also if you use spaces in a complex expression, you must refer to the column without the spaces, since the database strips spaces. Note that you can always refer to a field by number, that is, by its ordinal position in the SELECT statement.

Executing the SQL SELECT statement generates a commit to the database by default. To avoid this, use BeginTrans on the session object before using CreateDynaset.

The updatability of the resultant dynaset depends on the Oracle SQL rules of updatability, on the access you have been granted, and on the options flag. For the dynaset to be updatable, three conditions must be met:

1.	A SQL statement must refer to a simple column list or to the entire column list (*)

2.	The statement must not set the read-only flag of the options argument

3.	Oracle must permit ROWID references to the selected rows of the query.

Any SQL statement that does not meet these criteria is processed, but the results are not updatable and the dynaset’s Updatable property returns False.

This method automatically moves to the first row of the created dynaset.

You can use SQL bind variables in conjunction with the OraParameters collection.


CreateCustomDynaset Method Example

This example demonstrates CreateCustomDynaset. Copy and paste this code into the definition section of a form, then press F5.

Sub Form_Load ()

 'Declare variables as OLE Objects.

 Dim OraSession As Object

 Dim OraDatabase As Object

 Dim OraDynaset As Object

 'Create the OraSession Object.

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 'Create the OraDatabase Object by opening a connection to Oracle.

 Set OraDatabase = OraSession.DbOpenDatabase("ExampleDb", "scott/tiger", 0&)

 'Create the OraDynaset Object using sliceSize as 256,perblock size as 16, no. of blocks as 20,

 'fetchLimit as 20,FetchSize as 4096

 Set OraDynaset = OraDatabase.DbCreateCustomDynaset("select empno, ename from emp", 0&,256,16,20,20,4096)

 'Display the first record.

 MsgBox "Employee " & OraDynaset.Fields("empno").value & ", #" & OraDynaset.Fields("ename").value

End Sub


CreateDynaset Method

See Also	Example

Applies To

OraDatabase

Description

Creates an OraDynaset object from the specified SQL SELECT statement and options.

Usage

Set oradynaset = oradatabase.CreateDynaset(sql_statement, options)

Set oradynaset = oradatabase.DbCreateDynaset(sql_statement, options)

Arguments

sql_statement	Any valid Oracle SQL SELECT statement.
options	A bit flag indicating the status of any optional states of the dynaset. You can combine one or more options by adding their respective values. Specifying &H0& gives the following defaults for the dynaset: • Behave like Visual Basic Mode for a database: Field values not explicitly set are set to NULL, overriding server column defaults. • Perform automatic binding of database parameters. • Strip trailing blanks from character string data retrieved from the database. • Create an updatable dynaset. • Cache data on client. • Force a MoveFirst on dynaset creation. • Maintain read-consistency.

The table below describes the options flag values.

Constant	Value	Description

ORADYN_DEFAULT	&H0&	Accept the default behavior.
ORADYN_NO_AUTOBIND	&H1&	Do not perform automatic binding of database parameters.
ORADYN_NO_BLANKSTRIP	&H2&	Do not strip trailing blanks from character string data retrieved from the database.
ORADYN_READONLY	&H4&	Force dynaset to be read-only.
ORADYN_NOCACHE	&H8&	Do not create a local dynaset data cache. Without the local cache, previous rows within a dynaset are unavailable; however, increased performance results during retrieval of data from the database (move operations) and from the rows (field operations). Use this option in applications that make single passes through the rows of a dynaset for increased performance and decreased resource usage.
ORADYN_ORAMODE	&H10&	Behaves same as Oracle Mode for a database except it affects only the dynaset being created. If  database was created in Oracle Mode, dynaset inherits the property from it (for compatability)
ORADYN_NO_REFETCH	&H20&	Behaves same as ORADB_NO_REFETCH mode for a database except this mode affects only the dynaset being created. If the database was created in ORADB_NO_REFETCH mode, the dynaset inherits the property for compatability.
ORADYN_NO_MOVEFIRST	&H40&	Does not force a movefirst on dynaset creation. BOF and EOF are both TRUE.
ORADYN_DIRTY_WRITE	&H80&	Update and Delete will not check for read consistency.

These values can be found in the file ORACONST.TXT.

Remarks

Features such as simple views and synonyms can be used freely. You can also use schema references, column aliases, table joins, nested selects and remote database references, but in each case you will end up with a read-only dynaset.

If you use a complex expression or SQL function on a column, such as "sal + 100" or "abs(sal)" , you will get an updatable dynaset, but the column associated with the complex expression will not be updatable.

Object names generally are not modifed, but in certain cases they can be changed. For example, if you use a column alias, you must use the alias to refer to the field by name. Also if you use spaces in a complex expression, you must refer to the column without the spaces, since the database strips spaces. Note that you can always refer to a field by number, that is, by its ordinal position in the SELECT statement.

Executing the SQL SELECT statement generates a commit to the database by default. To avoid this, use BeginTrans on the session object before using CreateDynaset.

The updatability of the resultant dynaset depends on the Oracle SQL rules of updatability, on the access you have been granted, and on the options flag. For the dynaset to be updatable, three conditions must be met:

1.	A SQL statement must refer to a simple column list or to the entire column list (*)

2.	The statement must not set the read-only flag of the options argument

3.	Oracle must permit ROWID references to the selected rows of the query.

Any SQL statement that does not meet these criteria is processed, but the results are not updatable and the dynaset’s Updatable property returns False.

This method automatically moves to the first row of the created dynaset.

You can use SQL bind variables in conjunction with the OraParameters collection.


See Also

Clone Method

Long and Long Raw Columns

MoveFirst Method

MovePrevious Method

OpenDatabase Method

OraDatabase Object

OraDynaset Object

OraParameter Object

OraParameters Collection

Updatable Property


CreateDynaset Method Example

This example demonstrates CreateObject, OpenDatabase and CreateDynaset. Copy and paste this code into the definition section of a form. Then press F5.

Sub Form_Load ()

 'Declare variables as OLE Objects.

 Dim OraSession As Object

 Dim OraDatabase As Object

 Dim OraDynaset As Object

 'Create the OraSession Object.

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 'Create the OraDatabase Object by opening a connection to Oracle.

 Set OraDatabase = OraSession.DbOpenDatabase("ExampleDb", "scott/tiger", 0&)

 'Create the OraDynaset Object.

 Set OraDynaset = OraDatabase.DbCreateDynaset("select empno, ename from emp", 0&)

 'Display the first record.

 MsgBox "Employee " & OraDynaset.Fields("empno").value & ", #" & OraDynaset.Fields("ename").value

End Sub


CreateNamedSession Method

See Also	Example

Applies To

OraSession

Description

Creates and returns a new named OraSession Object.

Usage

orasession = orasession.CreateNamedSession(session_name)

Arguments

session_name	A string specifying the name of the session.
Remarks

Using this method, you can create named sessions that can be referenced later in the same application without having to explicitly save the OraSession object when it is created. Once a session has been created, the application can reference it by way of the ConnectSession method or the OraSessions collection of their respective OraClient object. The OraSessions collection only contains sessions created within the current application. This means that it is not possible to share sessions across applications, only within applications.

This method is provided for simplicity and is equivalent to the CreateSession method of the OraClient object.


See Also

ConnectSession Method

CreateSession Method

OraClient Object

OraSession Object

OraSessions Collection


CreateNamedSession Method Example

This example demonstrates the use of ConnectSession and CreateNamedSession to allow an application to use a session it previously created, but did not save. Copy this code into the definition section of a form. Then press F5.

Sub Form_Load ()

 'Declare variables as OLE Objects.

 Dim dfltsess As object

 Dim OraSession As object

 Dim OraDatabase As Object

Dim OraDynaset As Object

 'Create the default OraSession Object.

 Set dfltsess = CreateObject("OracleInProcServer.XOraSession")

 'Try to connect to "ExampleSession". If it does not exist

 'an error is generated.

 On Error GoTo SetName

 Set OraSession = dfltsess.ConnectSession("ExampleSession")

 On Error GoTo 0

'Create the OraDatabase Object by opening a connection to Oracle.

 Set OraDatabase = OraSession.DbOpenDatabase("ExampleDb", "scott/tiger", 0&)

 'Create the OraDynaset Object.

 Set OraDynaset = OraDatabase.DbCreateDynaset("select * from emp", 0&)

 'Display or manipulate data here

Exit Sub

SetName:

'The session named "ExampleSession" was not found, so create it.

Set OraSession = dfltsess.CreateNamedSession("ExampleSession")

Resume Next

End Sub


CreatePLSQLDynaset Method

Example

Applies To

OraDatabase Object

Description

Create a Dynaset from a PL/SQL cursor. The SQL statement should be a stored procedure or an anonymous block. Resulting Dynaset is READONLY and attempting to set SQL property results in error. Dynasets can be refreshed with new parameters similar to non-cursor dynasets.

Usage

set OraDynaset = CreatePLSQLDynaset( SQLStatement, CursorName , options )

Arguments

SQLStatement	Any valid Oracle PLSQL stored procedure or anonymous block.

CursorName	Name of the cursor created in the PLSQL stored procedure.

options	A bit flag indicating the status of any optional states of the dynaset. You can combine one or more options by adding their respective values.

The options flag values are:

Constant	Value	Description
ORADYN_DEFAULT	&H0&	Accept the default behavior.
ORADYN_NO_BLANKSTRIP	&H2&	Do not strip trailing blanks from character string data retrieved from the database.
ORADYN_NOCACHE	&H8&	Do not create a local dynaset data cache. Without the local cache, previous rows within a dynaset are unavailable; however, increased performance results during retrieval of data from the database (move operations) and from the rows (field operations). Use this option in applications that make single passes through the rows of a dynaset for increased performance and decreased resource usage.
ORADYN_NO_MOVEFIRST	&H40&	Does not force a movefirst on dynaset creation. BOF and EOF are both TRUE.

These values can be found in the file ORACONST.TXT.

Remarks

The SQL statement must be a PL/SQL stored procedure with BEGIN and END around the call, as if it were executed as an anonymous PL/SQL block; otherwise an error is returned. CursorName should exactly match the cursor created inside the stored procedure or anonymous PL/SQL block; otherwise error is returned. Cursor created inside the stored procedure should represent a valid SELECT SQL.

You do not need to bind the PL/SQL cursor variable using OraParameters.Addmethod if the stored procedure returns cursor as a output parameter. You can still use PL/SQL bind variables in conjunction with the OraParameters collection.

This method automatically moves to the first row of the created dynaset.

Specifying ORADYN_READONLY, ORADYN_ORAMODE, ORADYN_NO_REFETCH, ORADYN_DIRTY_WRITE options have no effect on the Dynaset creation.


CreatePlsqlDynaset  Method Example

This example demonstrates the use of PL/SQL cursor in the  CreatePlsqlDynaset method and Refresh method.

This example returns PL/SQL cursor as a dynaset for the different values of DEPTNO parameter.

Make sure that corresponding stored procedure (found in EMPCUR.SQL) is available in the Oracle Server.

and paste this code into the definition section of a form, then press F5.

Sub Form_Load ()

 'Declare variables as OLE Objects.

 Dim OraSession As Object

 Dim OraDatabase As Object

 Dim OraDynaset As Object

 'Create the OraSession Object.

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 'Create the OraDatabase Object by opening a connection to Oracle.

 Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

' Create the Deptno parameter

 OraDatabase.Parameters.Add "DEPTNO", 10, ORAPARM_INPUT

 OraDatabase.Parameters("DEPTNO").ServerType = ORATYPE_NUMBER

' Create OraDynaset based on "EmpCursor" created in stored procedure.

 Set OraDynaset = OraDatabase.CreatePLSQLDynaset("Begin Employee.GetEmpData (:DEPTNO,:EmpCursor); end;", "EmpCursor", 0&)

 'Should display KING

 MsgBox OraDynaset.Fields("ENAME").Value

 'Should display 7839

 MsgBox OraDynaset.Fields("EMPNO").Value

 ' Now set the deptno value to 20

 OraDatabase.Parameters("DEPTNO").Value = 20

 'Refresh the sqlstmt

 OraDynaset.Refresh

 'Should display JONES

 MsgBox OraDynaset.Fields("ENAME").Value

 'Should display 7566

 MsgBox OraDynaset.Fields("EMPNO").Value

  'Remove the parameter.

 OraDatabase.Parameters.Remove ("DEPTNO")

 End Sub


CreatePlsqlCustomDynaset Method

Applies To

OraDatabase Object

Description

Create a Dynaset from a PLSQL cursor using custom cache and fetch parameters. SQL statement should be a stored procedure or anonymous block. Resulting Dynaset is READONLY. Attempting to set SQL property  results in error. Dynaset can be refreshed with new parameters as normal.

Usage

set OraDynaset = CreatePlsqlCustomDynaset(  SQLStatement, CursorName , options slicesize,perblock,blocks,FetchLimit,Fetchsize)

Arguments

SQLStatement	Any valid Oracle PLSQL stored procedure or anonymous block.

CursorName	Name of the cursor created in the PLSQL stored procedure.

Options	A bit flag indicating the status of any optional states of the dynaset. You can combine one or more options by adding their respective values.

slicesize      	Cache slice size

perblock	Cache slices per block

blocks	Cache maximum number of blocks

FetchLimit	Fetch array size

FetchSize	Fetch array buffer size

The options flag values are:

Constant	Value	Description
ORADYN_DEFAULT	&H0&	Accept the default behavior.
ORADYN_NO_AUTOBIND	&H1&	Do not perform automatic binding of database parameters.
ORADYN_NO_BLANKSTRIP	&H2&	Do not strip trailing blanks from character string data retrieved from the database.
ORADYN_NOCACHE	&H8&	Do not create a local dynaset data cache. Without the local cache, previous rows within a dynaset are unavailable; however, increased performance results during retrieval of data from the database (move operations) and from the rows (field operations). Use this option in applications that make single passes through the rows of a dynaset for increased performance and decreased resource usage.
ORADYN_NO_MOVEFIRST	&H40&	Does not force a movefirst on dynaset creation. BOF and EOF are both TRUE.

These values can be found in the file ORACONST.TXT.

Remarks

The SQL statement must be a PL/SQL stored procedure with BEGIN and END around the call, as if it were executed as an anonymous PL/SQL block; otherwise an error is returned. CursorName should exactly match the cursor created inside the stored procedure or anonymous PL/SQL block; otherwise error is returned. Cursor created inside the stored procedure should represent a valid SELECT SQL.

You do not need to bind the PL/SQL cursor variable using OraParameters Addif the stored procedure returns cursor as a output parameter. You can still use PL/SQL bind variables in conjunction with the OraParameters collection.

This method automatically moves to the first row of the created dynaset.

Specifying ORADYN_READONLY, ORADYN_ORAMODE, ORADYN_NO_REFETCH, ORADYN_DIRTY_WRITE options have no effect on the Dynaset creation.


CreateSession Method

See Also	Example

Applies To

OraClient

Description

Creates a new named OraSession object.

Usage

orasession = oraclient.CreateSession(session_name )

Arguments

session_name	A string specifying the name of the session.

Remarks

Using this method, you can create named sessions that can be referenced later in the same application without having to explicitly save the OraSession object when it is created. Once a session has been created, the application can reference it by way of the ConnectSession method or the OraSessions collection of their respective OraClient object. The OraSessions collection only contains sessions created within the current application. This means that it is not possible to share sessions across applications, only within applications.


See Also

OraClient Object

OraSession Object


CreateSession Method Example

This example demonstrates how to create an session object using the CreateSession method of client object. Copy and paste this code into the definition section of a form. Then press F5.

Sub Form_Load ()

 'Declare variables as OLE Objects.

 Dim OraClient As Object

 Dim OraSession As Object

 Dim NamedOraSession As Object

 Dim OraDatabase As Object

 Dim OraDynaset As Object

 'Create the OraSession Object.

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 'Get the OraClient object.

 Set OraClient = OraSession.Client

 'Create a named OraSession Object

 'Alternatively, you could use the CreateNamedSession

 'method of the OraSession Object.

 Set NamedOraSession = OraClient.CreateSession("ExampleSession")

 'Create the OraDatabase Object by opening a connection to Oracle.

 Set OraDatabase = NamedOraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

 'Create the OraDynaset Object.

 Set OraDynaset = OraDatabase.DbCreateDynaset("select * from emp", 0&)

End Sub


CreateSQL Method

See Also  	Example

Applies To

OraDatabase

Description

Executes the SQL statement and creates an OraSqlStmt object from the specified SQL statement and options.

Usage

Set orasqlstmt = oradatabase.CreateSQL(sql_statement, options)

Arguments

sql_statement	Any valid Oracle SQL statement.

options	A bit flag indicating the status of any optional states of the sqlstmt object. You can combine one or more options by adding their respective values.

The options flag values are:

Constant	Value	Description
ORASQL_NO_AUTOBIND	&H1&	Do not perform automatic binding of database parameters.
ORASQL_FAILEXEC	&H2&	Raise error and do not create SQL statement object.

These values can be found in the file ORACONST.TXT.

Remarks

The SQL statement can be one continuous line with no breaks. If it is necessary to break the line, be sure to use line feeds (ASCII 10). Do not use carriage returns (ASCII 13), because the underlying Oracle database functions treat carriage returns as null terminators.

You can use PL/SQL bind variables in conjunction with the OraParameters collection.

Executing the SQL statement generates a commit to the database by default. To avoid this, use BeginTrans on the session object before using CreateSQL.

When executing PL/SQL blocks or calling stored procedures, you must include a BEGIN and END around your call as if you were executing an anonymous PL/SQL block. This is equivalent to the EXECUTE command of SQL*Plus and SQL*DBA.

If SQL_FAILEXEC is used, an error will  be raised during SQLstatement object creation failure (on SQLstatement object refresh). SQLstatement object will not be created, and cannot be refreshed.

Note: Use CreateSQL with care, since any SQL statement or PL/SQL block that is executed might cause errors afterwards when you use the Edit method on open dynasets.

Data Type

String


See Also

CreateSQL Method

ExecuteSQL Method

Refresh  Method

BeginTrans  Method

OraSQLStmt Object


CreateSQL Method Example

This example demonstrates the use of parameters, the CreateSQL method the Refresh method, and the SQL property for SqlStmt obeject.  Copy and paste this code into the definition section of a form, then press F5.

Sub Form_Load ()

 'Declare variables as OLE Objects.

 Dim OraSession As Object

 Dim OraDatabase As Object

 Dim OraSqlStmt As Object

 'Create the OraSession Object.

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 'Create the OraDatabase Object by opening a connection to Oracle.

 Set OraDatabase = OraSession.DbOpenDatabase("ExampleDb", "scott/tiger", 0&)

 OraDatabase.Parameters.Add "EMPNO", 7369, 1

 OraDatabase.Parameters("EMPNO").ServerType = 2  'ORATYPE_NUMBER

 OraDatabase.Parameters.Add "ENAME", 0, 2

 OraDatabase.Parameters("ENAME").ServerType = 1  'ORATYPE_VARCHAR2

 Set OraSqlStmt = OraDatabase.CreateSQL("Begin Employee.GetEmpName (:EMPNO, :ENAME); end;", 0&)

 'Notice that the SQL statement is NOT modified.

 MsgBox OraSqlStmt.SQL

 'Should display SMITH

 MsgBox OraDatabase.Parameters("ENAME").Value

 'Change the value of the empno parameter.

 OraDatabase.Parameters("EMPNO").Value = 7499

 'Refresh the sqlstmt

 OraSqlStmt.DbRefresh

 'Should display ALLEN

 MsgBox OraDatabase.Parameters("ENAME").Value

 'Notice that the SQL statement is NOT modified.

 MsgBox OraSqlStmt.SQL

 'Remove the parameter.

 OraDatabase.Parameters.Remove ("job")

 End Sub


Delete Method

See Also	Example

Applies To

OraDynaset

Description

Deletes the current row of the specified dynaset.

Usage

oradynaset.Delete

oradynaset.DbDelete

Remarks

A row must be current before you can use Delete; otherwise an error occurs.

Note that once you call Delete on a given row in a dynaset in a global transaction (i.e. once you issue a BeginTrans), locks will remain on the selected rows until you call CommitTrans or Rollback.

Any references to the deleted row produce an error. The deleted row, as well as the next and previous rows, remain current until database movement occurs (using the MoveFirst, MovePrevious, MoveNext, or MoveLast methods). Once such movement occurs, you cannot make the deleted row current again.

You cannot restore deleted records except by using transactions.

Note: A call to Edit, AddNew, or Delete, will cancel any outstanding Edit or AddNew calls before proceeding. Any outstanding changes not saved using Update will be lost during the cancellation.


See Also

AddNew Method

BeginTrans Method

CommitTrans Method

Edit Method

MoveFirst, MoveLast, MoveNext, MovePrevious Methods

ResetTrans Method

Rollback Method


Delete Method Example

This example demonstrates the use of Delete to remove records from a database. Copy this code into the definition section of a form. Then press F5.

Sub Form_Load ()

'Declare variables as OLE Objects.

Dim OraSession As Object

Dim OraDatabase As Object

Dim OraDynaset As Object

'Create the OraSession Object.

Set OraSession = CreateObject("OracleInProcServer.XOraSession")

'Create the OraDatabase Object by opening a connection to Oracle.

Set OraDatabase = OraSession.DbOpenDatabase("exampledb", "scott/tiger", 0&)

'Create the OraDynaset Object.

'Only select the employees in Department 10.

Set OraDynaset = OraDatabase.DbCreateDynaset("select * from emp where deptno=10", 0&)

 Do Until OraDynaset.EOF

   OraDynaset.DbDelete

   OraDynaset.DbMoveNext

 Loop

 MsgBox "All employees from department 10 removed."

End Sub


Edit Method

See Also	Example

Applies To

OraDynaset

Description

Begins an edit operation on the current row by copying the data to the copy buffer.

Usage

oradynaset.Edit

oradynaset.DbEdit

Remarks

Edit causes the locally cached data to be compared with the corresponding row of the Oracle database. An error will be generated if the Oracle database data is not the same as the data currently being browsed. If this operation succeeds, the row is locked using "SELECT ... FOR UPDATE" until the edit is completed with Update or until database movement occurs, which discards any edits in progress. The behavior of the "SELECT ... FOR UPDATE" is affected by the Lock Wait mode of the options flag used when the OpenDatabase method was called.

During editing, changes made to fields are kept in a shadowed copy buffer and do not yet reflect the actual contents of the database. However, all references to the row return the newly modified data as long as the edit operation is still in progress.

When data is modified within a data control attached to this dynaset, the Edit method is invoked automatically upon the next record movement. Thus, this method is required only when modifications are made to field data within code.

Note: A call to Edit, AddNew, or Delete, will cancel any outstanding Edit or AddNew calls before proceeding. Any outstanding changes not saved using Update will be lost during the cancellation.


See Also

AddNew Method

CreateDynaset Method

Delete Method

OpenDatabase Method

Locks and Editing

Long and Long Raw Columns

Update Method

Validate Event.


Edit Method Example

This example demonstrates the use of Edit and Update to update values in a database. Copy this code into the definition section of a form. Then press F5.

Sub Form_Load ()

'Declare variables as OLE Objects.

Dim OraSession As Object

Dim OraDatabase As Object

Dim OraDynaset As Object

'Create the OraSession Object.

Set OraSession = CreateObject("OracleInProcServer.XOraSession")

'Create the OraDatabase Object by opening a connection to Oracle.

Set OraDatabase = OraSession.DbOpenDatabase("ExampleDb", "scott/tiger", 0&)

'Create the OraDynaset Object.

Set OraDynaset = OraDatabase.DbCreateDynaset("select * from emp", 0&)

 'Traverse until EOF is reached, setting

 'each employee’s salary to zero.

 Do Until OraDynaset.EOF

   OraDynaset.DbEdit

   OraDynaset.Fields("sal").value = 0

   OraDynaset.DbUpdate

   OraDynaset.DbMoveNext

 Loop

 MsgBox "All salaries set to ZERO."

End Sub


ExecuteSQL Method

See Also	Example

Applies To

OraDatabase

Description

Executes a single non-SELECT SQL statement or a PL/SQL block.

Usage

rowcount = oradatabase.ExecuteSQL(sql_statement)

rowcount = oradatabase.DbExecuteSQL(sql_statement)

Arguments

sql_statement	Any valid Oracle non-SELECT SQL statement.
Remarks

Executes a SQL statement and returns the number of rows processed by that statement.

The sql_statement can be one continuous line with no breaks. If it is necessary to break the line, be sure to use line feeds (ASCII 10). Do not use carriage returns (ASCII 13), because the underlying Oracle database functions treat carriage returns as null terminators.

Executing the SQL statement generates a commit to the database by default. To avoid this, use BeginTrans on the session object before using ExecuteSQL.

You can use PL/SQL bind variables in conjunction with the OraParameters collection.

When executing PL/SQL blocks or calling stored procedures, you must include a "BEGIN" and "END" around your call as if you were executing an anonymous PL/SQL block. This is equivalent to the EXECUTE command of SQL*Plus and SQL*DBA.

Note: ExecuteSQL should be used with care since any SQL statement or PL/SQL block that is executed can adversely affect currently open dynasets. This is especially true if the OraDatabase object used for the ExecuteSQL method is the same as the one that was used to create the dynaset. Use a different OraDatabase object if you are unsure.

Normal dynaset operations can be adversely affected, if in transactional mode, a database commit is issued. This can happen if either a SQL commit command or a Data Control Language (DCL) or Data Definition Language (DDL) command is issued. DCL and DDL SQL commands, such as CREATE, DROP, ALTER, GRANT and REVOKE always force a commit, which in turn commits everything done before them. Consult the Oracle8 Server SQL Language Reference Manual for more details about DCL, DDL and transactions.

Data Type

Long Integer


See Also

CreateDynaset Method

OraDatabase Object

OraParameters Collection

Transactions


ExecuteSQL Method Example

This example uses the Add and Remove parameter methods, the ServerType parameter property, and the ExecuteSQL database method to call a Stored Procedure and Function (located in ORAEXAMP.SQL). Copy and paste this code into the definition section of a form. Then press F5.

Sub Form_Load ()

'Declare variables as OLE Objects.

 Dim OraSession As Object

 Dim OraDatabase As Object

 Dim OraDynaset As Object

 'Create the OraSession Object.

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 'Create the OraDatabase Object.

 Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

 'Add EMPNO as an Input/Output parameter and set its initial value.

 OraDatabase.Parameters.Add "EMPNO", 7369, ORAPARM_INPUT

 OraDatabase.Parameters("EMPNO").ServerType = ORATYPE_NUMBER

 'Add ENAME as an Output parameter and set its initial value.

 OraDatabase.Parameters.Add "ENAME", 0, ORAPARM_OUTPUT

 OraDatabase.Parameters("ENAME").ServerType = ORATYPE_VARCHAR2

 'Add SAL as an Output parameter and set its initial value.

 OraDatabase.Parameters.Add "SAL", 0, ORAPARM_OUTPUT

 OraDatabase.Parameters("SAL").ServerType = ORATYPE_NUMBER

 'Execute the Stored Procedure Employee.GetEmpName to retrieve ENAME.

 ' This Stored Procedure can be found in the file ORAEXAMP.SQL.

 OraDatabase.DbExecuteSQL ("Begin Employee.GetEmpName (:EMPNO, :ENAME); end;")

 'Display the employee number and name.

 'Execute the Stored Function Employee.GetSal to retrieve SAL.

 ' This Stored Function can be found in the file ORAEXAMP.SQL.

 OraDatabase.DbExecuteSQL ("declare SAL number(7,2); Begin :SAL:=Employee.GetEmpSal (:EMPNO); end;")

 'Display the employee name, number and salary.

 MsgBox "Employee " & OraDatabase.Parameters("ENAME").value & ", #" & OraDatabase.Parameters("EMPNO").value & ",Salary=" & OraDatabase.Parameters("SAL").value

 'Remove the Parameters.

 OraDatabase.Parameters.Remove "EMPNO"

 OraDatabase.Parameters.Remove "ENAME"

 OraDatabase.Parameters.Remove "SAL"

End Sub


FieldSize Method

See Also

Applies To

OraField

Description

Returns the number of bytes stored in a LONG or LONG RAW field. Not available at design time and read-only at run time.

Usage

data_size = orafield.FieldSize( )

data_size = orafield.DbFieldSize( )

Remarks

Returns the number of bytes stored in a LONG or LONG RAW field, up to a value of around 64KB. If the field contains more than 64KB, FieldSize returns -1 (negative one).

Oracle does not return the length of columns that are greater than 64KB, so the only way to determine the length is to retrieve the column. To conserve resources, columns of length greater than 64KB are not retrieved automatically.

Data Type

Long Integer


See Also

AppendChunk Method

GetChunk Method

Long and Long Raw Columns

OraField Object

Type Property


FindFirst, FindLast, FindNext, FindPrevious Methods

See Also   	Example

FindFirst Method

Applies To

OraDynaset

Usage

oradynaset.FindFirst FindClause

Where FindClause is any valid SQL where clause without the 'WHERE'.

Description

Finds the first row in the dynaset that matches FindClause. FindClause can be any valid where clause without the 'WHERE'. If the current FindClause matches the last clause from the previous Find, the current FindClause is not re-parsed.

This method moves the current row directly to a matched row without calling any advisories except when the matched row is reached. If a matching row cannot be found the Nomatch property is set to TRUE and you are left at the row from which you called FindFirst.

Remarks

You can use the following types of expressions in the FindClause:

•	simple queries, such as "deptno = 20"

•	queries involving complex expressions, such as  "sal + 100 > 1000".

•	SQL function calls, such as  "UPPER(ename) = ‘SCOTT’ " or  "NVL(comm, 0) =  0" .

•	subqueries, such as "deptno in (select deptno from dept)".

FindLast Method

Applies To

OraDynaset

Usage

oradynaset.FindLast FindClause

Where FindClause is any valid SQL where clause without the 'WHERE'.

Description

Finds the last record in the dynaset that matches FindClause. FindClause can be any valid where clause without the 'WHERE'. If the current FindClause matches the last clause from the previous Find, the current FindClause is not re-parsed.

This method moves the current row directly to a matched row without calling any advisories except when the matched row is reached. If a matching row cannot be found the Nomatch property is set to TRUE and you are left at the row from which you called FindLast.

FindNext Method

Applies To

OraDynaset

Usage

oradynaset.FindNext FindClause

Where FindClause is any valid SQL where clause without the 'WHERE'

Description

Finds the next row in the dynaset that matches FindClause. FindClause can be any valid where clause without the 'WHERE'. If the current FindClause matches the last clause from the previous Find, the current FindClause is not re-parsed.

This method moves the current row directly to a matched row without calling any advisories except when the matched row is reached. If a matching row cannot be found the Nomatch property is set to TRUE and you are left at the row from which you called FindNext.

FindPrevious Method

Applies To

OraDynaset

Usage

oradynaset.FindPrevious FindClause

Where FindClause is any valid SQL where clause without the 'WHERE'

Description

Finds previous record in dynaset starting from current row that matches FindClause. FindClause can be any valid where clause without the 'WHERE'. If the current FindClause matches the last clause from the previous Find, the current FindClause is not re-parsed.

This method moves the current row directly to a matched row without calling any advisories except when the matched row is reached. If a matching row cannot be found the Nomatch property is set to TRUE and you are left at the row from which you called FindPrevious.


 Find Methods Example

This example demonstrates the use of  the FindFirst, FindNext, FindPrevious  methods. Copy and paste this code into the definition section of a form. Then press F5.

 Sub Form_Load ()

  Dim OraSession As Object

  Dim OraDatabase As Object

  Dim OraDynaset As Object

  Dim OraFields As Object

  Dim FindClause As String

  Set OraSession = CreateObject("OracleInProcServer.XOraSession")

  Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "SCOTT/TIGER", 0&)

  Set OraDynaset = OraDatabase.DbCreateDynaset("select * from emp where empno >= 7654 and empno <= 7844 ", ORADYN_NO_BLANKSTRIP)

  Set OraFields = OraDynaset.Fields

  OraDynaset.DbMoveFirst

  'FindClause for job as MANAGER

  FindClause = "job LIKE '%GER'"

  OraDynaset.DbFindFirst FindClause

  'NoMatch property set to true , if no rows found

  If OraDynaset.NoMatch Then

    MsgBox "Couldn't find rows "

  else

    MsgBox OraFields("ename").Value  ' Should display BLAKE

    OraDynaset.DbFindNext FindClause

    MsgBox OraFields("ename").Value  ' Should display CLARK

    OraDynaset.DbFindPrevious FindClause

    MsgBox OraFields("ename").Value  ' Should display BLAKE

   endif

End Sub


See Also

MoveFirst, MoveLast, MoveNext, MovePrevious Methods

NoMatch  Property

OraDatabase


GetChunk Method

See Also	Example

Applies To

OraField

Description

Returns a string containing the bytes of all or a portion of a long or long raw field.

Usage

data_string = orafield.GetChunk( offset, numbytes)

data_string = orafield.DbGetChunk( offset, numbytes)

Arguments

offset	The number of bytes of the field to skip before copying data

numbytes	The number of bytes to copy

Remarks

GetChunkByte typically retrieves the specified bytes from the local cache. If data is not found in the cache then GetChunkByte requests it from the database.

Data from all fields (except the Long or Long Raw field) in the dynaset are retrieved and compared with the cached values for consistency. If any changes have occurred since the last fetch then GetChunkByte  aborts the operation with an error and returns a NULL string.

If a long or long raw field is less than 64KB in size, it is quicker to retrieve the data using the Value property than using GetChunkByte.

You cannot use GetChunkByte on a long or long raw field that you have aliased.


See Also

AppendChunk Method

FieldSize Method

Long and Long Raw Columns

OraField Object

Type Property

Value Property


GetChunk Method Example

This example demonstrates the use of GetChunk to retrieve a LONG RAW column of a database and save it into a file. This example expects a valid dynaset named OraDynaset representing a table with a column named 'longraw'. Copy and paste this code into the definition section of a form. Call this procedure with a valid filename.

Sub GetChunkExample (FName As String)

'Declare various variables

Dim CurSize As Integer, ChunkSize  As Long

Dim I As Integer, FNum As Integer, CurChunk As String

'Set the size of each chunk

ChunkSize = 10240

frmChunk.MousePointer = HOURGLASS

'Get a free file number

FNum = FreeFile

'Open the file

Open FName For Binary As #FNum

 I = 0

'Loop through all of the chunks

'Oracle does not return the size of columns > 64KB.

'We should loop until the length of our block is

'less than we asked for.

Do

 CurChunk = OraDynaset.Fields("LONGRAW").DbGetChunk(I * ChunkSize, ChunkSize)

 CurSize = Len(CurChunk) 'Get the length of the current chunk.

 Put #FNum, , CurChunk   'Write chunk to file.

 I = I + 1

Loop Until CurSize < ChunkSize

'Close the file.

Close FNum

frmChunk.MousePointer = DEFAULT

End Sub


GetChunkByte Method

Example

Applies To

OraField Object

Description

Read the the data from the long or long raw field into byte array and returns the size of  data read.

Usage

Size_read = orafield.GetChunkByte( ByteArray, offset, numbytes)

Arguments

ByteArray	the first element of the ByteArray which will hold the data

offset	the number of bytes of the field to skip before copying data

numbytes	the number of bytes to copy

Remarks

When possible, GetChunkByte retrieves the specified bytes from the local cache. However, to conserve resources, some of the data might not be stored locally. In these  cases, GetChunkByte requests the necessary data from the database as required. As part of this process, data from all fields (except the Long or Long Raw field) in the  dynaset are retrieved and compared with the cached values for consistency. If any changes have occurred since the fetch of the original partial data, then GetChunkByte  aborts the operation with an error. In the case of an abort, the returned string is NULL.

If a long or long raw field is less than 64KB in size, it is quicker to retrieve the data using the Value property than using GetChunkByte.

You cannot use GetChunkByte on a long or long raw field that you have aliased.


GetChunkByte Method Example

This example demonstrates the use of GetChunkByte to retrieve a LONG RAW column of a database and save it into a file. This example expects a valid dynaset named OraDynaset representing a table with a column named 'longraw'. Copy and paste this code into the definition section of a form. Call this procedure with a valid filename.

Sub GetChunkByteExample (FName As String)

'Declare various variables

Dim CurSize As Integer, ChunkSize  As Long

Dim I As Integer, FNum As Integer, CurChunk() As Byte

'Set the size of each chunk

ChunkSize = 10240

'Redim CurChunk Array

ReDim CurChunk(ChunkSize)

frmChunk.MousePointer = HOURGLASS

'Get a free file number

FNum = FreeFile

'Open the file

Open FName For Binary As #FNum

 I = 0

'Loop through all of the chunks

'Oracle does not return the size of columns > 64KB.

'We should loop until the length of our block is

'less than we asked for.

Do

 CurSize = OraDynaset.Fields("type_longraw").DbGetChunkByte(CurChunk(0), I * ChunkSize, ChunkSize)

 If CurSize < ChunkSize Then

    ReDim CurChunk(CurSize)

    CurSize = OraDynaset.Fields("type_longraw").DbGetChunkByte(CurChunk(0), I * ChunkSize, CurSize)

 End If

 Put #FNum, , CurChunk   'Write chunk to file.

 I = I + 1

Loop Until CurSize < ChunkSize

'Close the file.

Close FNum

frmChunk.MousePointer = DEFAULT

End Sub


GetChunkByteEx Method

Applies To

OraField Object

Description

This method reads the the data from a long or long raw field into a variant  and returns the amount of data read.

Usage

amount_read = orafield.GetChunkByteEx( ByteArray, offset, numbytes)

Arguments

ByteArray	the name of the variant ByteArray which will hold the data

offset	the number of bytes of the field to skip before copying data

numbytes	the number of bytes to copy

Remarks

When possible, GetChunkByteEx retrieves the specified bytes from the local cache. However, to conserve resources, some of the data might not be stored locally. In such cases, GetChunkByteEx requests the necessary data from the database as required. As part of this process, data from all fields (except the Long or Long Raw field) in the dynaset are retrieved and compared with the cached values for consistency. If any changes have occurred since the fetch of the original partial data, then GetChunkByteEx aborts the operation with an error.

Since GetChunkByteEx takes in a variant as the first parameter, instead of the first element of the ByteArray as in GetChunkByte, only GetChunkByteEx can be used within an ASP/IIS environment.

If a long or long raw field is less than 64KB in size, it is quicker to retrieve the data using the Value property than using GetChunkByteEx.


GetChunkByteEx Method Example

This example demonstrates the use of GetChunkByte to retrieve a LONG RAW column of a database and save it into a file. This example expects a valid dynaset named OraDynaset representing a table with a column named 'type_longraw'. Copy and paste this code into the definition section of a form. Call this procedure with a valid filename.

Sub GetChunkByteExExample (FName As String)

'Declare various variables

Dim bytesread As Integer, ChunkSize As Long ,

bytearr() as byte

Dim I As Integer, FNum As Integer, CurChunk

'Set the size of each chunk

ChunkSize = 10240

frmChunk.MousePointer = HOURGLASS

'Get a free file number

FNum = FreeFile

'Open the file

Open FName For Binary As #FNum

I = 0

'Loop through all of the chunks

'Oracle does not return the size of columns > 64KB.

'We should loop until the length of our block is

'less than we asked for.

Do

bytesread =

OraDynaset.Fields("type_longraw").GetChunkByteEx(CurChunk,

I * ChunkSize, ChunkSize)

'redim byte array

redim bytearr(bytesread - 1)

bytearr = CurChunk

Put #FNum, , bytearr 'Write chunk to file.

I = I + 1

Loop Until bytesread < ChunkSize

'Close the file.

Close FNum

frmChunk.MousePointer = DEFAULT

End Sub


LastServerErrReset Method

See Also

Applies To

OraDatabase

OraSession

Description

Clears the LastServerErr to a zero value and sets LastServerErrText to NULL for the specified object.

Usage

oradatabase.LastServerErrReset

orasession.LastServerErrReset

Remarks

This method allows user programs to better determine which program requests generated the Oracle error.


See Also

LastServerErr Property

LastServerErrText Property

OraDatabase Object

OraSession Object


MoveFirst, MoveLast, MoveNext, MovePrevious Methods

See Also	Example

Applies To

OraDynaset

Description

These methods change the cursor position to the first, last, next, or previous row within the specified dynaset.

Usage

oradynaset.MoveFirst

oradynaset.DbMoveFirst

oradynaset.MoveLast

oradynaset.DbMoveLast

oradynaset.MovePrevious

oradynaset.DbMovePrevious

oradynaset.MoveNext

oradynaset.DbMoveNext

Remarks

The data control buttons map (from left to right or from top to bottom) to the MoveFirst, MovePrevious, MoveNext, and MoveLast methods. The BOF and EOF properties are never True when using the data control buttons.

When the first or last record is current, record movement does not occur if you use MoveFirst or MoveLast, respectively.

You force the query to completion if you use MoveLast on a dynaset.

If you use MovePrevious and the first record is current, there is no current record and BOF is True. Using MovePrevious again causes an error, though BOF remains True. If you use MoveNext and the last record is current, there is no current record and EOF is True. Using MoveNext again causes an error, though EOF remains True. Note that when the dynaset is created with the ORADYN_NO_MOVEFIRST option, BOF and EOF are true whether the dynaset is empty or not.

When you open a dynaset, BOF is False and the first record is current. If a dynaset is empty, BOF and EOF are both True and there is no current record.

If an Edit or AddNew operation is pending and you use one of the "Move" methods indirectly by way of the data control, then Update is invoked automatically (although it may be stopped during the Validate event).

If an Edit or AddNew operation is pending and you use one of the "Move" methods directly without the data control, pending Edit or AddNew operations cause existing changes to be lost, though no error occurs.

Data is fetched from the database as necessary, so performing a MoveFirst followed by MoveNext will incrementally build the mirrored (cached) local set without requiring read-ahead of additional data. However, executing a MoveLast will require that the entire query be evaluated and stored locally.

When a dynaset is attached to a data control, these methods first notify the data control's Validate event that record motion is about to occur. The Validate handler may deny the request for motion, in which case the request is ignored. If the record pointer is successfully moved, then all custom controls attached to the data control are notified automatically of the new record position.


See Also

AddNew Method

BOF Property

Edit Method

EditMode Property

EOF Property

RecordCount Property

Tuning and Customization

Update Method

Validate Event


MoveFirst, MoveLast, MoveNext, MovePrevious Methods Example

This example demonstrates record movement within a dynaset using MoveFirst, MoveNext, MoveLast, MovePrevious. Copy and paste this code into the definition section of a form.. Then press F5.

Sub Form_Load ()

 'Declare variables as OLE Objects.

 Dim OraSession As Object

 Dim OraDatabase As Object

 Dim OraDynaset As Object

 'Create the OraSession Object.

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 'Create the OraDatabase Object by opening a connection to Oracle.

 Set OraDatabase = OraSession.DbOpenDatabase("ExampleDb", "scott/tiger", 0&)

 'Create the OraDynaset Object.

 Set OraDynaset = OraDatabase.DbCreateDynaset("select empno, ename from emp", 0&)

MsgBox "Employee #" & OraDynaset.Fields("empno").value & ", " & OraDynaset.Fields("ename").value

 'Move to the next record and display it.

 OraDynaset.DbMoveNext

 MsgBox "Employee #" & OraDynaset.Fields("empno").value & ", " & OraDynaset.Fields("ename").value

 'Move to the last record and display it.

 OraDynaset.DbMoveLast

 MsgBox "Employee #" & OraDynaset.Fields("empno").value & ", " & OraDynaset.Fields("ename").value

 'Move to the previous record and display it.

 OraDynaset.DbMovePrevious

 MsgBox "Employee #" & OraDynaset.Fields("empno").value & ", " & OraDynaset.Fields("ename").value

End Sub


MoveFirstn, MoveLastn, MoveRel Method
MovePreviousn, MoveNextn, MoveRel, MoveTo Methods

Applies To

OraDynaset Object

Description

These methods change the cursor position to the specified row within the specified  dynaset.

Usage

oradynaset.MovePreviousn offset

oradynaset.MoveNextn  offset

oradynaset.MoveRel  offset

oradynaset.MoveTo   offset

MoveNextn Method

Move offset records forward.

MovePreviousn Method

Move offset  records backward.

MoveRel Method

Move offset records relative to current row. A positive value (represented by a "+" sign) will move the cursor "down" the table, and vice versa.

MoveTo Method

Move directly to row number offset.

Remarks

EOF will be set when cusor moves beyond the end of dynaset using MoveNextn, MoveRel, Moveto methods.

BOF will be set when cusor moves beyond the start of dynaset using MovePrevoiusn, MoveRel, Moveto method.

MoveNextn, MovePrevoiusn, Moveto accepts offset as a postive integer only. MoveRel accepts offset as both positiveand negative integer.

DataType

Long Integer

Example

This example demonstrates the use of  MovePreviousn, MoveNextn, MoveRel, MoveTo  method. Copy and paste this code into the definition section of a form. Then press F5.

 Private Sub Form_Load()

  Dim OraSession As Object

  Dim OraDatabase As Object

  Dim OraDynaset As Object

  Dim OraFields As Object

  Set OraSession = CreateObject("OracleInProcServer.XOraSession")

  Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "SCOTT/TIGER", 0&)

  Set OraDynaset = OraDatabase.DbCreateDynaset("select * from emp where empno >=7654 and empno <= 7844 ", ORADYN_NO_BLANKSTRIP)

  Set OraFields = OraDynaset.Fields

  'Move to 3rd record from the first record

  OraDynaset.DbMoveNextn 3  'Should set EOF to true

  MsgBox OraFields("ename").Value  ' Should be display SCOTT

  If OraDynaset.EOF = True Then

    MsgBox "End of the record reached"

  End If

  'Move back from the current record by the offset 2

  OraDynaset.DbMovePreviousn 2

  MsgBox OraFields("ename").Value  ' Should be display BLAKE

  If OraDynaset.BOF = True Then

    MsgBox "Start of the record reached"

  End If

  'Move relative in the forward direction

  OraDynaset.DbMoveRel 2

  MsgBox OraFields("ename").Value  ' Should be display SCOTT

  If OraDynaset.EOF = True Then

    MsgBox "End of the record reached"

  End If

  'Move relative in the backward direction

  OraDynaset.DbMoveRel -2

  MsgBox OraFields("ename").Value  ' Should be display BLAKE

  If OraDynaset.BOF = True Then

    MsgBox "Start of the record reached"

  End If

  'Move to the record position 4 in the current dynaset

   OraDynaset.DbMoveTo 4

   MsgBox OraFields("ename").Value  ' Should be display SCOTT

End Sub


OpenDatabase Method

See Also	Example

Applies To

OraSession

Description

Creates an OraDatabase object using the given database name, connect string and specified options.

Usage

Set oradatabase=orasession.OpenDatabase(database_name,connect_string,options)

Set oradatabase=orasession.DbOpenDatabase(database_name,connect_string,options)

Arguments

database_name	The Oracle SQL*Net specifier used when connecting the data control to a database.
connect_string	The username and password to be used when connecting to  an Oracle database.
options	A bit flag word used to set the optional modes of the database. If options = 0, the default mode settings will apply. The table below shows the different modes you can use, which can be combined by adding their respective values:
Constant	Value	Description
ORADB_DEFAULT	&H0&	Visual Basic Mode (Default)  • Field (column) values not explicitly set are set to NULL when using AddNew or Edit. The NULL values override any server column defaults.   • Wait on row locks when using Edit ("SELECT... FOR UPDATE"). • Non-blocking SQL functionality will not be enabled.
ORADB_ORAMODE	&H1&	Oracle Mode Let Oracle database set the default field (column) values when using AddNew. The Oracle default column values are refetched from database immediately after the insert or add operation. Note: If you use triggers, we recommend you refetch data using the full Oracle Mode.
ORADB_NOWAIT	&H2&	Lock No-Wait Mode Do not wait on row locks. When you use Edit to update a row that is locked by another user or process, Lock No Wait mode results in an immediate return of an error code.  Note: (1) Lock No-Wait modes also affect any SQL statements processed using ExecuteSQL. (2) This option may be of limited use with Dynasets based on heavily used tables. As documented, this option causes ORA 54 errors to occur with Row Locks. But it will also do the same for other resource contentions. In certain cases a considerable number of ORA 54 errors may be raised. You will want to test your application with a realistic database load to see if this affects you.
ORADB_NO_REFETCH	&H4&	Oracle Mode (No Refetch) Like Oracle Mode, but data is not refetched to the local cache, boosting performance.  Caution: Use the No Refetch mode only when you intend to insert rows without editing them, because server column defaults will cause inconsistencies between server data and the local cache. Attempting to edit after inserting in this mode will cause a  "Data has been modified"  (4119) error.
ORADB_NONBLK	&H8&	Non-Blocking Mode This mode turns on Non-Blocking mode on SQL statement execution. Non-Blocking mode affects the SQL statements processed using the ExecuteSQL, CreateDynaset or CreateSQL methods. Note that this parameter only applies to Windows 3.1 where it is used solely to prevent hanging during cooperative multitasking. It has no effect in Windows 95 or NT, in which cases the parameter is ignored. You cannot use this parameter to implement non-blocking via polling or other asynchronous callbacks.

These values can be found in the file ORACONST.TXT.

For creating a valid database alias please refer to the net8 administration guide.

Examples of valid connect_string arguments include:

"scott/tiger"

"system/manager"

"/"

Remarks

An OraConnection object is created automatically and appears within the OraConnections collection of the session. Opening a database has the effect of opening a connection but does not perform any SQL actions.


See Also

AddNew Method

Edit Method

ExecuteSQL Method

Locks and Editing

OraConnection Object

OraConnections Collection

OraDatabase Object

OraField Object

OraSession Object


OpenDatabase Method Example

This example demonstrates how to create a dynaset and all of the underlying objects programmatically. Copy and paste this code into the definition section of a form with textboxes named txtEmpNo and txtEName. Then press F5.

Sub Form_Load ()

 'Declare variables as OLE Objects.

 Dim OraSession As Object

 Dim OraDatabase As Object

 Dim OraDynaset As Object

 'Create the OraSession Object.

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 'Create the OraDatabase Object by opening a connection to Oracle.

 Set OraDatabase = OraSession.DbOpenDatabase("ExampleDb", "scott/tiger", 0&)

 'Create the OraDynaset Object.

 Set OraDynaset = OraDatabase.DbCreateDynaset("select empno, ename from emp", 0&)

 'Display the first record.

 txtEmpNo = OraDynaset.Fields("empno").value

 txtEName = OraDynaset.Fields("ename").value

End Sub


OriginalItem Method

Example

Applies To

OraFields Collection

Description

Returns the OraField object based on original column name used in the "select" statement in  the dynaset. Not available at design time and read-only at run time.

Usage

set OraField = OraFields.OriginalItem(field_index)

set OraField = OraFields.OriginalItem(original_name)

Arguments

field_index	Field index of the original column name

original_name	Original field name specified in the SQL statement

Remarks

This is property is useful when SQL statement contains  'schema.table.col' as the 'Name' of the field and retrieves the field object specific to that original name.


OriginalItem Method Example
OriginalItem Example

The following example shows the use of OriginalItem property. Paste this code

into the definition section of a form, then press F5.

Sub Form_Load ()

 'Declare variables as OLE Objects.

 Dim OraSession As Object

 Dim OraDatabase As Object

 Dim OraDynaset As Object

 Dim OraFields As Object

 Dim OraField As Object

 'Create the OraSession Object.

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 'Create the OraDatabase Object by opening a connection to Oracle.

 Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

 Set OraDynaset = OraDatabase.CreateDynaset("select scott.emp.deptno, dept.deptno from scott.emp, scott.dept where dept.deptno = emp.deptno", 0&)

 'Get the Field collection object

 Set OraFields = OraDynaset.Fields

 'get the original field object

 Set OraField = OraFields.OriginalItem("scott.emp.deptno")

 'Returns "scott.emp.deptno"

 MsgBox OraField.OriginalName

 Set OraField = OraFields.OriginalItem(1)

 'Returns "dept.deptno"

 MsgBox OraField.OriginalName

End Sub


OriginalName Method

Example

Applies To

OraField Object

Description

Returns the original column name used in the "select" statement in the dynaset .(as opposed to the name of the field as it appears on the server returned by the Name property). Not available at design time and read-only at run time.

Usage

field_name = Orafield.OriginalName

Remarks

orafield.OriginalName returns the name of the specified OraField object. This returns the Original column name specified in the SQL statement during dynaset creation. This property is useful when SQL statement contains  'schema.table.col' as the 'Name' of the field. It enables duplicate Col names to be referenced. (Duplicate column names could be avoided altogether by using aliases in the SQL statement.)


OriginalName Method Example

The following example shows the use of OriginalName property. Paste this code

into the definition section of a form, then press F5.

Sub Form_Load ()

 'Declare variables as OLE Objects.

 Dim OraSession As Object

 Dim OraDatabase As Object

 Dim OraDynaset As Object

 Dim OraField As Object

 'Create the OraSession Object.

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 'Create the OraDatabase Object by opening a connection to Oracle.

 Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

 Set OraDynaset = OraDatabase.CreateDynaset("select scott.emp.deptno, dept.deptno from scott.emp, scott.dept where dept.deptno = emp.deptno", 0&)

 Set OraFields = OraDynaset.Fields

 'Returns "DEPTNO"

 MsgBox OraFields(0).Name

 'Returns "scott.emp.deptno"

 MsgBox OraFields(0).OriginalName

 'Returns "dept.deptno"

 MsgBox OraFields(1).OriginalName

End Sub


ReadChunk Method

Applies To

OraField Object

 Description

Returns a string containing the bytes of all or a portion of a long or long raw field.

Usage

data_string = orafield.ReadChunk(offset, numbytes, bytesread)

Arguments

offset	The number of bytes of the field to skip before copying data.

numbytes	The number of bytes to copy.

bytesread	The number of bytes actually read.

Remarks

ReadChunk behaves exactly like GetChunk method, but returns the actual number of bytes read in the bytesread argument.


Refresh Method

See Also	Examples

Applies To

OraDynaset

OraSQLStmt

Description

Forces an immediate update of the dynaset given the current Connect, DatabaseName, and SQL properties.

Forces an immediate update of the dynaset by re-executing the SQL statement in the SQL statement object.

Usage

oradynaset.Refresh

oradynaset.DbRefresh

orasqlstmt.Refresh

orasqlstmt.DbRefresh

Remarks

This method cancels all edit operations (Edit and AddNew), executes the current contents of the SQL statement buffer, and moves to the first row of the resulting dynaset. Any dynaset objects created before issuing the Refresh method, including bookmarks, record counts, and field collections, are considered invalid. The OraConnection and OraSession objects associated with the previous dynaset remain unchanged.

Performing a refresh operation with this method can be more efficient than refreshing with a data control. This method also lets you execute a modified SQL statement without creating a new dynaset or sqlstmt object.

Using oradynaset.Refresh or orasqlstmt.Refresh is the preferred refresh method when changing parameter values, because required database operations are minimized (SQL parsing, binding, etc.). This can improve performance when only parameter values have changed.

If you call the Refresh method after assigning an invalid SQL statement to the SQL property of a dynaset or SQL statement object, these objects remain valid. However, a dynaset in this state will not permit any row or field operations. Bound controls will also exhibit unusual behaviors similar to those that occur when the standard Visual Basic data control RecordSource is set to an invalid SQL statement at run time and then Refreshed.

You can regain the normal dynaset and SQL statement operations by refreshing the object with a valid SQL statement. Refresh treats NULL or empty SQL statements as invalid.


See Also

AddNew Method

Connect Property

CreateDynaset Method

DatabaseName Property

Edit Method

OraConnection Object

OraDynaset Object

OraSession Object

RecordSource Property

SQL Property


Refresh Method Examples

OraDynaset

OraSqlStmt


Refresh Method Example (OraDynaset)

This example demonstrates the use of parameters, the Refresh method, and the SQL property to restrict selected records. Copy and paste this code into the definition section of a form. Then press F5.

Sub Form_Load ()

 'Declare variables as OLE Objects.

 Dim OraSession As Object

 Dim OraDatabase As Object

 Dim OraDynaset As Object

 'Create the OraSession Object.

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 'Create the OraDatabase Object by opening a connection to Oracle.

 Set OraDatabase = OraSession.DbOpenDatabase("ExampleDb", "scott/tiger", 0&)

 'Create a parameter with an initial value.

 OraDatabase.Parameters.Add "job", "MANAGER", 1

 'Create the OraDynaset Object.

 Set OraDynaset = OraDatabase.DbCreateDynaset("select * from emp where job=:job", 0&)

 'Notice that the SQL statement is NOT modified.

 MsgBox OraDynaset.SQL

 'Currently, OraDynaset only contains employees whose

 'job is MANAGER.

 'Change the value of the job parameter.

 OraDatabase.Parameters("job").Value = "SALESMAN"

 'Refresh the dynaset.

 OraDynaset.DbRefresh

 'Currently, OraDynaset only contains employees whose

 'job is SALESMAN.

 'Notice that the SQL statement is NOT modified.

 MsgBox OraDynaset.SQL

 'Remove the parameter.

 OraDatabase.Parameters.Remove ("job")

 End Sub


Refresh Method Example (OraSqlStmt)

This example demonstrates the use of parameters, the Refresh method, and the SQL property for the SqlStmt object. Copy and paste this code into the definition section of a form, then press F5.

Sub Form_Load ()

 'Declare variables as OLE Objects.

 Dim OraSession As Object

 Dim OraDatabase As Object

 Dim OraSqlStmt As Object

 'Create the OraSession Object.

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 'Create the OraDatabase Object by opening a connection to Oracle.

 Set OraDatabase = OraSession.DbOpenDatabase("ExampleDb", "scott/tiger", 0&)

 OraDatabase.Parameters.Add "EMPNO", 7369, 1

 OraDatabase.Parameters("EMPNO").ServerType = 2  'ORATYPE_NUMBER

 OraDatabase.Parameters.Add "ENAME", 0, 2

 OraDatabase.Parameters("ENAME").ServerType = 1  'ORATYPE_VARCHAR2

 Set OraSqlStmt = OraDatabase.CreateSQL("Begin Employee.GetEmpName (:EMPNO, :ENAME); end;", 0&)

 'Notice that the SQL statement is NOT modified.

 MsgBox OraSqlStmt.SQL

 'Should display SMITH

 MsgBox OraDatabase.Parameters("ENAME").Value

 'Change the value of the empno parameter.

 OraDatabase.Parameters("EMPNO").Value = 7499

 'Refresh the dynaset.

 OraSqlStmt.DbRefresh

 'Should display ALLEN

 MsgBox OraDatabase.Parameters("ENAME").Value

 'Notice that the SQL statement is NOT modified.

 MsgBox OraSqlStmt.SQL

 'Remove the parameter.

 OraDatabase.Parameters.Remove ("job")

 End Sub


Remove Method

See Also	Example

Applies To

OraParameters Collection

Description

Removes a parameter from the OraParameters collection.

Usage

oraparameters.Remove(member_name)

Arguments

member_name	A variant specifying an integer subscript from 0 to Count - 1, or the parameter name.

Remarks

Instead of repeatedly removing and adding unwanted parameters, use AutoBindDisable and AutoBindEnable.

For OraParameter of  type ORATYPE_CURSOR, this method destroys the dynaset object associated with the cursor, and clears the local cache temporary files.


See Also

Add Method

AutoBindEnable Method

AutoBindDisable Method

OraDatabase Object

OraParameter Object

OraParameters Collection


Remove Method Example

This example demonstrates the Add and Remove parameter methods, the ServerType parameter property and the the ExecuteSQL database method to call a Stored Procedure and Function (located in ORAEXAMP.SQL). Copy and paste this code into the definition section of a form. Then press F5.

Sub Form_Load ()

'Declare variables as OLE Objects.

 Dim OraSession As Object

 Dim OraDatabase As Object

 Dim OraDynaset As Object

 'Create the OraSession Object.

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 'Create the OraDatabase Object.

 Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

 'Add EMPNO as an Input/Output parameter and set its initial value.

 OraDatabase.Parameters.Add "EMPNO", 7369, ORAPARM_INPUT

 OraDatabase.Parameters("EMPNO").ServerType = ORATYPE_NUMBER

 'Add ENAME as an Output parameter and set its initial value.

 OraDatabase.Parameters.Add "ENAME", 0, ORAPARM_OUTPUT

 OraDatabase.Parameters("ENAME").ServerType = ORATYPE_VARCHAR2

 'Add SAL as an Output parameter and set its initial value.

 OraDatabase.Parameters.Add "SAL", 0, ORAPARM_OUTPUT

 OraDatabase.Parameters("SAL").ServerType = ORATYPE_NUMBER

 'Execute the Stored Procedure Employee.GetEmpName to retrieve ENAME.

 ' This Stored Procedure can be found in the file ORAEXAMP.SQL.

 OraDatabase.DbExecuteSQL ("Begin Employee.GetEmpName (:EMPNO, :ENAME); end;")

 'Display the employee number and name.

 'Execute the Stored Function Employee.GetSal to retrieve SAL.

 ' This Stored Function can be found in the file ORAEXAMP.SQL.

 OraDatabase.DbExecuteSQL ("declare SAL number(7,2); Begin :SAL:=Employee.GetEmpSal (:EMPNO); end;")

 'Display the employee name, number and salary.

 MsgBox "Employee " & OraDatabase.Parameters("ENAME").value & ", #" & OraDatabase.Parameters("EMPNO").value & ",Salary=" & OraDatabase.Parameters("SAL").value

 'Remove the Parameters.

 OraDatabase.Parameters.Remove "EMPNO"

 OraDatabase.Parameters.Remove "ENAME"

 OraDatabase.Parameters.Remove "SAL"

End Sub


ResetTrans Method

See Also	Example

Applies To

OraSession

Description

Unconditionally rolls back all transactions and clears the transaction mode initiated by BeginTrans.

Usage

orasession.ResetTrans

Remarks

This method does not generate events or produce errors. Since ResetTrans does not generate events, you cannot cancel ResetTrans in a Validate event, as you can RollBack or Commit.


See Also

BeginTrans Method

CommitTrans Method

OraSession Object

Rollback Method

Validate Event


ResetTrans Method Example

This example demonstrates the use of BeginTrans and ResetTrans to group a set of dynaset edits into a single transaction. Copy this code into the definition section of a form. Then press F5.

Sub Form_Load ()

'Declare variables as OLE Objects.

Dim OraSession As Object

Dim OraDatabase As Object

Dim OraDynaset As Object

'Create the OraSession Object.

Set OraSession = CreateObject("OracleInProcServer.XOraSession")

'Create the OraDatabase Object by opening a connection to Oracle.

Set OraDatabase = OraSession.DbOpenDatabase("ExampleDb", "scott/tiger", 0&)

'Create the OraDynaset Object.

Set OraDynaset = OraDatabase.DbCreateDynaset("select * from emp", 0&)

 'Start Transaction processing.

 OraDynaset.Session.DbBeginTrans

 'Traverse until EOF is reached, setting

 'each employee’s salary to zero.

 Do Until OraDynaset.EOF

   OraDynaset.DbEdit

   OraDynaset.Fields("sal").value = 0

   OraDynaset.DbUpdate

   OraDynaset.DbMoveNext

 Loop

 MsgBox "All salaries set to ZERO."

 'Currently, the changes have NOT been committed

 'to the database.

 'End Transaction processing.

 'Using ResetTrans means the rollback cannot

 'be canceled in the Validate event.

 OraDynaset.Session.DbResetTrans

 MsgBox "Salary changes rolled back."

End Sub


Rollback Method

See Also	Example

Applies To

OraConnection

OraSession

Description

Ends the current transaction and rolls back all pending changes to the database.

Usage

oraconnection.Rollback

oraconnection.DbRollback

orasession.Rollback

orasession.DbRollback

Remarks

When this method is invoked, all OraDynaset objects that share the specified session or connection are given the opportunity to cancel the rollback request. If they do not cancel the request, they are then advised when the rollback succeeds.

This feature is useful primarily for dynasets that are created as part of an Oracle Data Control's operation. For such dynasets, the Validate event is sent to allow them to cancel the rollback request.

OraConnection.Rollback rolls back all pending transactions within the specified connection. If not participating in a session-wide transaction (by using the session object's BeginTrans method), this method has no effect, because all operations are autocommitted whenever they are performed. However, when a session-wide transaction is in progress, you can use this call to prematurely roll back the transactions for the specified connection.

Use this method with care. It undermines the normal operation of session-wide transactions. In many cases, you can use the OraSession object's transaction protocol instead.

OraSession.Rollback rolls back all pending transactions within the specified session. Rollback is valid only when a transaction has been started using BeginTrans. Using Rollback without BeginTrans results in an error.

Transactions are rolled back by rolling back each connection separately. If an error occurs, some connections are rolled back and others are not. If the error can be corrected, executing Rollback again rolls back the remaining connections.


See Also

BeginTrans Method

CommitTrans Method

OraConnection Object

OraSession Object

ResetTrans Method

Validate Event


Rollback Method Example

This example demonstrates the use of BeginTrans and Rollback to group a set of dynaset edits into a single transaction. Copy this code into the definition section of a form. Then press F5.

Sub Form_Load ()

'Declare variables as OLE Objects.

Dim OraSession As Object

Dim OraDatabase As Object

Dim OraDynaset As Object

'Create the OraSession Object.

Set OraSession = CreateObject("OracleInProcServer.XOraSession")

'Create the OraDatabase Object by opening a connection to Oracle.

Set OraDatabase = OraSession.DbOpenDatabase("ExampleDb", "scott/tiger", 0&)

'Create the OraDynaset Object.

Set OraDynaset = OraDatabase.DbCreateDynaset("select * from emp", 0&)

 'Start Transaction processing.

 OraDynaset.Session.DbBeginTrans

 'Traverse until EOF is reached, setting

 'each employee’s salary to zero.

 Do Until OraDynaset.EOF

   OraDynaset.DbEdit

   OraDynaset.Fields("sal").value = 0

   OraDynaset.DbUpdate

   OraDynaset.DbMoveNext

 Loop

 MsgBox "All salaries set to ZERO."

 'Currently, the changes have NOT been committed

 'to the database.

 'End Transaction processing.

 OraDynaset.Session.DbRollback

 MsgBox "Salary changes rolled back."

End Sub


Update Method

See Also	Example

Applies To

OraDynaset

Description

Saves the copy buffer to the specified dynaset.

Usage

oradynaset.Update

oradynaset.DbUpdate

Remarks

Update completes an AddNew or Edit operation and immediately commits changes to the database unless BeginTrans is pending for the session.

Note that once you call Update on a given row in a dynaset in a global transaction (i.e. once you issue a BeginTrans), locks will remain on the selected rows until you call CommitTrans or Rollback.

The mirrored data image is also updated so that the query does not have to be reevaluated to continue browsing and updating data. The method used for updating the mirror image is subject to the options flag that was passed to the OpenDatabase that created this dynaset’s OraDatabase object.

If this dynaset is attached to a data control, then the data control's Validate event code may optionally cancel the update request. If the update completes, then all bound controls associated with the dynaset are notified of the update so they can reflect the data changes automatically.


See Also

AddNew Method

Edit Method

Long and Long Raw Columns

OpenDatabase Method

OraDatabaseObject

Validate Event


Update Method Example

This example demonstrates the use of AddNew and Update to add a new record to a dynaset. Copy this code into the definition section of a form. Then press F5.

Sub Form_Load ()

'Declare variables as OLE Objects.

Dim OraSession As Object

Dim OraDatabase As Object

Dim OraDynaset As Object

'Create the OraSession Object.

Set OraSession = CreateObject("OracleInProcServer.XOraSession")

'Create the OraDatabase Object by opening a connection to Oracle.

Set OraDatabase = OraSession.DbOpenDatabase("ExampleDb", "scott/tiger", 0&)

'Create the OraDynaset Object.

Set OraDynaset = OraDatabase.DbCreateDynaset("select * from emp", 0&)

'Begin an AddNew.

OraDynaset.DbAddNew

'Set the field(column) values.

OraDynaset.Fields("EMPNO").Value = "1000"

OraDynaset.Fields("ENAME").Value = "WILSON"

OraDynaset.Fields("JOB").Value = "SALESMAN"

OraDynaset.Fields("MGR").Value = "7698"

OraDynaset.Fields("HIREDATE").Value = "19-SEP-92"

OraDynaset.Fields("SAL").Value = 2000

OraDynaset.Fields("COMM").Value = 500

OraDynaset.Fields("DEPTNO").Value = 30

'End the AddNew and Update the dynaset.

OraDynaset.DbUpdate

End Sub


DynasetCacheParams

Applies To

OraParameter

Description

Specifies dynaset cache,Fetch parameter for the dyanaset created from the PL/SQL cursor.

Usage

oraparameter.DynasetCacheParams  SliceSize,Perblock,Blocks, FetchLimit,FetchSize

Arguments

SliceSize	Cache slice size.

perblock	Cache slices per block.

Blocks	Cache maximum number of blocks.

FetchLimit	Fetch array size.

FetchSize	Fetch array buffer size.

Remarks

This should be called before executing the PL/SQL procedure containing cursor variable. By default, the dynaset is created with default cache and fetch parameter specified in the registry.


CreateDatabasePool Method

See Also

Applies To

OraSession

Description

This method creates a pool of OraDatabase objects. Only one pool can be created per OraSession.

Usage

CreateDatabasePool (long initialSize, long maxSize, long timeoutValue, BSTR connectString, BSTR usernamePassword, long options)

Arguments

initialSize	the Initial Size of the pool

maxSize	the maximum size to which the pool can grow

timeoutValue	if an OraDatabase object in the pool is idle for the timeoutValue value specified,

 the database connection that it contains will be disconnected. The connection will be

reopened if the pool item is used again. This value is in seconds.

connectString, usernamePassword, and options are the same parameters used in the OpenDatabase method of OraSession

Remarks

The OpenDatabase method of the OraSession object is used to establish a connection to an Oracle database. This method returns a reference to the OraDatabase object which is then used for executing SQL statements and PL/SQL blocks. The Connection Pool in OO4O is a pool of OraDatabase objects. The pool is created by invoking the CreateDatabasePool method of the OraSession interface.

Exceptions are raised by this call if:

•	a pool already exists

•	an error occurs in creating a connection to the Oracle database

•	invalid values for arguments are passed (i.e. initialSize > maxSize)

The LastServerErr property of OraSession will contain the code for the specific cause of the exception resulting from an Oracle database error.


GetDatabaseFromPool

See Also

Applies To

OraSession

Description

This method returns the next available OraDatabase object from the pool.

Usage

GetDatabaseFromPool(long waitTime)

Arguments

waitTime	is the number of milliseconds this call should wait for an object to be available, if the

pool contains the maximum number of objects allowed and all are used

Remarks

To retrieve an OraDatabase object from the pool, the GetDatabaseFromPool method is called. This function returns a reference to an OraDatabase object. If the pool does not contain the maximum number of objects allowed, and all objects in the pool are used, then an additional OraDatabase object is created implicitly. In addition, if a pool item contains an OraDatabase object that has been timed out, then a new object is created and returned. The OraDatabase object obtained from the pool is then marked as in use and will be returned to the pool when the object is no longer referenced by the application.

Exceptions are raised by this call if:

•	the Connection Pool does not exist

•	the pool contains no objects

•	a time-out has occurred

The LastServerErr property of OraSession will contain the code for the specific cause of the exception.


DestroyDatabasePool Method
DestroyDatabasePool

Applies To

OraSession

Description

The pool is implicitly destroyed if the parent session object that it belong to is destroyed. It can also be destroyed at any time by invoking the DestroyDatabasePool method.

Usage

void DestroyDatabasePool()

Remarks

An exceptions is raised by this call if:

•	the pool does not exist


OLE Server Properties

ArraySize BOF Bookmark BookMarkable CacheBlocks CacheChanged CacheSliceSize CacheSlicesPerBlock Client Connect Connection Count Database DatabaseName DbPoolCurrentSize  DbPoolInitialSize  DbPoolMaxSize  DirtyWrite EditMode EOF FetchLimit FetchSize Fields Filter Font Property  Get_Value LastModified LastErrorText LastServerErr	LastServerErrPos LastServerErrText MinimumSize Name NoMatch OIPVersionNumber Options OraIDataType OraMaxDSize OraMaxSize OraNullOK OraPrecision OraScale Parameters Put_Value RDMSVersion RecordCount ServerType Session Sessions Size Sort SQL Status Transactions Truncated Type Updatable Value


OLE Server Properties

ArraySize BOF Bookmark  BookMarkable  CacheBlocks  CacheChanged  CacheSliceSize CacheSlicesPerBlock Client Connect Connection Count Database DatabaseName DbPoolCurrentSize  DbPoolInitialSize  DbPoolMaxSize  EditMode EOF FetchLimit  FetchSize  Fields Filter Font Get_Value  LastModified LastErrorText LastServerErr LastServerErrPos	LastServerErrText MinimumSize Name  NoMatch OIPVersionNumber Options OraIDataType OraMaxDSize OraMaxSize OraNullOK OraPrecision OraScale Parameters Put_Value  RDMSVersion RecordCount ServerType Session Sessions Size Sort SQL Status Transactions Truncated Type Updatable Value


OLE Server Properties

ArraySize BOF Bookmark  BookMarkable  CacheBlocks  CacheChanged  CacheSliceSize CacheSlicesPerBlock Client Connect Connection Count Database DatabaseName EditMode EOF FetchLimit  FetchSize  Fields Filter Font Property  Get_Value  LastModified LastErrorText LastServerErr LastServerErrPos LastServerErrText MinimumSize	Name  NoMatch OIPVersionNumber Options OraIDataType OraMaxDSize OraMaxSize OraNullOK OraPrecision OraScale Parameters Put_Value  RDMSVersion RecordCount ServerType Session Sessions Size Sort SQL Status Transactions Truncated Type Updatable Value


ArraySize Property

Applies To

OraParamArray

Description

Specifies the array size (i.e. number of elements in array) of an OraParameter string buffer. Not available at design time and read only at run time.

Usage

OraParamArray.ArraySize

Remarks

You specify the ArraySize during AddTable. Refer to the AddTable method for the OraParamArray object.

Data Type

Integer


BOF Property

See Also	Example

Applies To

OraDynaset

Description

Returns whether the current record position in a dynaset is before the first record. Not available at design time and read-only at run time.

Usage

bof_status = oradynaset.BOF

Remarks

Returns True if an attempt has been made to move before the first record in the dynaset using MovePrevious. Returns False otherwise.

If a recordset is empty, both BOF and EOF return True.

Data Type

Integer (Boolean)


See Also

EOF Property

MoveFirst Method

MovePrevious Method

OraDynaset Object


BOF Property Example

This example demonstrates the use of the BOF and EOF properties to detect the limits of a recordset. Copy and paste this code into the definition section of a form. Then press F5.

Sub Form_Load ()

 'Declare variables as OLE Objects.

 Dim OraSession As object

 Dim OraDatabase As object

 Dim OraDynaset As object

 'Create the OraSession Object

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 'Create the OraDatabase Object by opening a connection to Oracle

 Set OraDatabase = OraSession.DbOpenDatabase("ExampleDb", "scott/tiger", 0&)

 'Create the OraDynaset Object

 Set OraDynaset = OraDatabase.DbCreateDynaset("select empno, ename from emp", 0&)

 'Traverse until EOF is reached

 Do Until OraDynaset.EOF

  OraDynaset.DbMoveNext

  Loop

 MsgBox "Reached EOF"

 'When EOF is True there is no current record.

 'The current recordset position is now AFTER

 'the last record.

 OraDynaset.DbMoveLast

 Do Until OraDynaset.BOF

  OraDynaset.DbMovePrevious

 Loop

 MsgBox "Reached BOF"

 'When BOF is True there is no current record.

 'The current recordset position is now BEFORE

 'AFTER the last record.

 OraDynaset.DbMoveFirst

 'The recordset is now positioned at the first

 'record.

End Sub


Bookmark Property

See Also	Example

Applies To

OraDynaset

Description

Determines the current record of a recordset. Not available at design time and read/write at run time.

Usage

row_bookmark = oradynaset.Bookmark

oradynaset.Bookmark = row_bookmark

Remarks

The first form returns a bookmark for the current row. The second form repositions the bookmark to refer to a specific record within the dynaset.

Bookmarks exist only for the life of the dynaset and are specific to a particular dynaset (they cannot be shared among dynasets). (Note, however, that bookmarks of a dynaset and its clone are interchangeable.)

Before attempting to use bookmarks, check the Bookmarkable property of that dynaset to see if it supports bookmarks.

Data Type

The value is a string of binary data, but can be stored in a variable of String or Variant data type. The length of the string cannot be predicted, so do not use a fixed-length string.


See Also

Bookmarkable Property

Clone Method

LastModified Property

OraDynaset Object


Bookmark Property Example

This example demonstrates the use of the Bookmark property to return to a previously known record quickly. Copy and paste this code into the definition section of a form. Then press F5.

Sub Form_Load ()

 'Declare variables as OLE Objects.

 Dim OraClient As Object

 Dim OraSession As Object

 Dim OraDatabase As Object

 Dim OraDynaset As Object

 Dim Bookmark2 As String

 'Create the OraSession Object.

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 'Get the client object.

 Set OraClient = OraSession.Client

 'Create the OraDatabase Object by opening a connection to Oracle.

 Set OraDatabase = OraSession.DbOpenDatabase("ExampleDb", "scott/tiger", 0&)

 'Create the OraDynaset Object.

 Set OraDynaset = OraDatabase.DbCreateDynaset("select * from emp", 0&)

 'Move to the second record and display empno.

 OraDynaset.DbMoveNext

 MsgBox "Second Record, Employee #" & OraDynaset.Fields("EMPNO").value

 Bookmark2 = OraDynaset.Bookmark

 'Move to the last record and display empno.

 OraDynaset.DbMoveLast

 MsgBox "Last Record, Employee #" & OraDynaset.Fields("EMPNO").value

 'Move back to the second record using the bookmark.

 OraDynaset.Bookmark = Bookmark2

 MsgBox "Second Record, Employee #" & OraDynaset.Fields("EMPNO").value

End Sub


BookMarkable Property

See Also

Applies To

OraDynaset

Description

Indicates whether the specified dynaset can supports bookmarks. Not available at design time and read-only at run time.

Usage

if_bookmarkable = oradynaset.Bookmarkable

Remarks

This property returns True unless the No Cache mode was set when the specified dynaset was created, in which case it returns False.

Data Type

Integer (Boolean)


See Also

BookMark Property

CreateDynaset Method

OraDynaset Object


CacheSliceSize Property

Applies To

OraDynaset

Description

Get or set cache slice size.

Usage

 set Slicesize = oradynaset.CacheSliceSize

 oradynaset.CacheSliceSize = Slicesize

Data Type

Integer


IDH_CacheSliceSizePerBlock_Property
CacheSlicesPerBlock Property

Applies To

OraDynaset

Description

Get or set cache slices per block

Usage

set Perblock = oradyanset.CacheSlicePerBlock

oradyanset.CacheSlicePerBlock  = Perblock

Data Type

Integer


DirtyWrite Property

Applies To

Oracle Data Control

Description

Determines whether or not Update and Delete will or will not check for read inconsistencies.

Usage

oradata1.DirtyWrite = [ True | False ]

Data Type

Integer (Boolean)

Remarks

By default, DirtyWrite is False,  This means that read consistency will be maintained for Update and Delete operation on underlying recordset/dynaset object.

Changing this property has no effect until a Refresh method is sent to the data control


CacheBlocks Property

Applies To

OraDynaset

Description

Get or set cache maximum number of blocks

Usage

set blocks = oradyanset.CacheBlocks

oradynaset.CacheBlocks = blocks

Data Type

Integer


FetchLimit Property

Applies To

OraDynaset

Description

Get or set fetch array size.

Usage

set Limit = oradynaset.FetchLimit

oradynaset.FetchLimit = Limit

Data Type

Integer


FetchSize Property

Applies To

OraDynaset

Description

Get or set fetch array buffer size.

Usage

set Size = oradynaset.FetchSize

oradynaset.FetchSize = Size

Data Type

Integer


CacheChanged Property

Applies To

OraDynaset

Usage

set Changed = oradyanset.CacheChanged

Description

TRUE if cache or fetch parameters have been changed.

Data Type

Boolean


Client Property

Applies To

OraSession

Description

Returns the OraClient object associated with the specified session. Not available at design time and read-only at run time.

Usage

Set oraclient = orasession.Client

Remarks

Each machine has only one client object, so this property returns the same object for all sessions on the same workstation.

Data Type

OLE Object (OraClient)


See Also

OraClient Object

OraSession Object


Connect Property: OLE Server
Connect Property

See Also	Example

Applies To

OraConnection

OraDatabase

Description

Returns the username of the connect string associated with the connection. Not available at design time and read-only at run time.

Usage

connect_string = oraconnection.Connect

connect_string = oradatabase.Connect

Remarks

OraConnection.Connect returns the username of the connect string associated with the connection.

OraDatabase.Connect returns the username of the connect string associated with the specified database. It is equivalent to referencing OraDatabase.Connection.Connect.

The password associated with the username is never returned.

Data Type

String


See Also

OpenDatabase Method

OraConnection Object

OraDatabase Object

DatabaseName Property


Connect Property Example

This example demonstrates the use of the Connect and DatabaseName properties to display the username and database name that have been connected to. Copy and paste this code into the definition section of a form. Then press F5.

Sub Form_Load ()

 'Declare variables as OLE Objects.

 Dim OraSession As Object

 Dim OraDatabase As Object

 Dim OraDynaset As Object

 'Create the OraSession Object.

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 'Create the OraDatabase Object by opening a connection to Oracle.

 Set OraDatabase = OraSession.DbOpenDatabase("ExampleDb", "scott/tiger", 0&)

 'Display the username and database to which we are connected.

 MsgBox "Connected to " & OraDatabase.Connect & "@" & OraDatabase.DatabaseName

End Sub


Connection Property

See Also

Applies To

OraDatabase

OraDynaset

OraSQLStmt

Description

Returns the OraConnection object associated with the specified database, dynaset, or sqlstmt object. Not available at design time and read-only at run time.

Usage

Set oraconnection = oradatabase.Connection

Set oraconnection = oradynaset.Connection

Set oraconnection = orasqlstmt.Connection

Remarks

OraDatabase.Connection returns the connection object associated with the specified database. Each database is associated with one connection object, but many databases can share the same connection object.

OraDynaset.Connection returns the connection object associated with this dynaset. This is equivalent to referencing oradynaset.Database.Connection.

OraSQLStmt.Connection returns the connection object associated with this sqlstmt object. This is equivalent to referencing orasqlstmt.Database.Connection.

Data Type

OLE Object (OraConnection)


See Also

OraConnection Object

OraConnections Collection

OraDatabase Object

OraDynaset Object

OraSQLStmt Object


Connections Property

See Also

Applies To

OraSession

Description

Returns the OraConnections collection of the specified session. Not available at design time and read-only at run time.

Usage

Set oraconnections_collection = orasession.Connections

Remarks

You can access the connections in this collection by subscripting (using ordinal integer numbers). You can obtain the number of connections in the collection using the Count property of the returned collection. Integer subscripts begin with 0 and end with Count - 1. Out-of-range indices and invalid names return a NULL OraConnection object.

Data Type

OLE Object (OraParameters)


See Also

CountProperty

OraConnection Object

OraConnections Collection

OraSession Object


Count Property

See Also	Example

Applies To

OraConnections Collection

OraFields Collection,

OraParameters Collection

OraSessions Collection

Description

Returns the number of objects in the specified collection. Not available at design time and read-only at run time.

Usage

connection_count = oraconnections.Count

field_count = orafields.Count

parameter_count = oraparameters.Count

session_count = orasessions.Count

Remarks

Use this property to determine the number of objects in the specified collection.

Data Type

Integer


See Also

OraConnection Object

OraConnections Collection

OraField Object

OraFields Collection

OraSession Object

OraSessions Collections


Count Property Example

This example demonstrates the use of the Count property to display the number of objects in the OraSessions, OraConnections, and OraFields Collections. Copy and paste this code into the definition section of a form. Then press F5.

Sub Form_Load ()

 'Declare variables as OLE Objects.

 Dim OraClient As Object

 Dim OraSession As Object

 Dim OraDatabase As Object

 Dim OraDynaset As Object

 'Create the OraSession Object.

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 'Get the client object.

 Set OraClient = OraSession.Client

 'Create the OraDatabase Object by opening a connection to Oracle.

 Set OraDatabase = OraSession.DbOpenDatabase("ExampleDb", "scott/tiger", 0&)

 'Create the OraDynaset Object.

 Set OraDynaset = OraDatabase.DbCreateDynaset("select * from emp", 0&)

 MsgBox "You have " & OraClient.Sessions.Count & " OraSession Object(s)."

 MsgBox "You have " & OraSession.Connections.Count & " OraConnection Object(s)."

 MsgBox "You have " & OraDynaset.Fields.Count & " OraField Object(s)."

End Sub


Database Property: OLE Server
Database Property

See Also

Applies To

OraDynaset

OraSQLStmt

Description

Returns the OraDatabase object associated with the specified dynaset or SQL statement object. Not available at design time and read-only at run time.

Usage

Set oradatabase = oradynaset.Database

Set oradatabase = orasqlstmt.Database

Remarks

OraDynaset.Database returns the OraDatabase object from which the specified dynaset  was created.

OraSQLStmt.Database returns the OraDatabase object from which the specified SQLStmt object was created.

Data Type

OLE Object (OraDatabase)


See Also

CreateDynaset Method

OraDatabase Object

OraDynaset Object

OraSQLStmt Object


DatabaseName Property: OLE Server
DatabaseName Property

See Also	Example

Applies To

OraConnection

OraDatabase

Description

Returns the name of the database associated with the specified object. Not available at design time and read-only at run time.

Usage

database_name = oraconnection.DatabaseName

database_name = oradatabase.DatabaseName

Remarks

oraconnection.DatabaseName returns the name of the database, as specified in the OpenDatabase method.

oradatabase.DatabaseName returns the database name associated with the connection. It is the same as the referencing oradatabase.Connection.DatabaseName.

Data Type

String


See Also

Connect Property

OpenDatabase Method

OraConnection Object

OraDatabase Object


DatabaseName Property Example

This example demonstrates the use of the Connect and DatabaseName properties to display the username and database to which you have connected. Copy and paste this code into the definition section of a form. Then press F5.

Sub Form_Load ()

 'Declare variables as OLE Objects.

 Dim OraSession As Object

 Dim OraDatabase As Object

 Dim OraDynaset As Object

 'Create the OraSession Object.

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 'Create the OraDatabase Object by opening a connection to Oracle.

 Set OraDatabase = OraSession.DbOpenDatabase("ExampleDb", "scott/tiger", 0&)

 'Display the username and database to which you have connected.

 MsgBox "Connected to " & OraDatabase.Connect & "@" & OraDatabase.DatabaseName

End Sub


EditMode Property: OLE Server
EditMode Property

See Also

Applies To

OraDynaset

Description

Returns the current editing state for the current row. Not available at design time and read-only at run time.

Usage

edit_mode = oradynaset.EditMode

Remarks

The EditMode property values are:

Constant	Value	Description
ORADATA_EDITNONE	0	No editing in progress.
ORADATA_EDITMODE	1	Editing is in progress on an existing row.
ORADATA_EDITADD	2	A new record is being added and the copy buffer does not currently represent an actual row in the database.

These values are located in the file ORACONST.TXT and are intended to match similar constants in the Visual Basic file CONSTANT.TXT.

This property is affected only by the Edit, AddNew, and Update methods.

Data Type

Integer


See Also

AddNew Method

Edit Method

OraDynaset Object

Update Method


EOF Property

See Also	Example

Applies To

OraDynaset

Description

Returns whether the current record position in a dynaset is after the last record. Not available at design time and read-only at run time.

Usage

eof_status = oradynaset.EOF

Remarks

Returns True if an attempt has been made to move after the last record in the dynaset using MoveNext. Returns False otherwise.

If a recordset is empty, both BOF and EOF return True.

Data Type

Integer (Boolean)


See Also

BOF Property

MoveLast Method

MoveNext Method

OraDynaset Object


EOF Property Example

This example demonstrates the use of the BOF and EOF properties to detect the limits of a recordset. Copy and paste this code into the definition section of a form. Then press F5.

Sub Form_Load ()

 'Declare variables as OLE Objects.

 Dim OraSession As object

 Dim OraDatabase As object

 Dim OraDynaset As object

 'Create the OraSession Object

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 'Create the OraDatabase Object by opening a connection to Oracle

 Set OraDatabase = OraSession.DbOpenDatabase("ExampleDb", "scott/tiger", 0&)

 'Create the OraDynaset Object

 Set OraDynaset = OraDatabase.DbCreateDynaset("select empno, ename from emp", 0&)

 'Traverse until EOF is reached

 Do Until OraDynaset.EOF

  OraDynaset.DbMoveNext

  Loop

 MsgBox "Reached EOF"

 'When EOF is True there is no current record.

 'The current recordset position is now AFTER

 'the last record.

 OraDynaset.DbMoveLast

 Do Until OraDynaset.BOF

  OraDynaset.DbMovePrevious

 Loop

 MsgBox "Reached BOF"

 'When BOF is True there is no current record.

 'The current recordset position is now BEFORE

 'AFTER the last record.

 OraDynaset.DbMoveFirst

 'The recordset is now positioned at the first

 'record.

End Sub


Fields Property

See Also

Applies To

OraDynaset

Description

Returns the collection of fields for the current row. Not available at design time and read-only at run time.

Usage

Set orafields_collection = oradynaset.Fields

Remarks

You can access the fields in this collection by subscripting (using ordinal integer numbers) or by using a string denoting the field (column) name. You can obtain the count of the number of fields using the Count property on the returned collection. A subscript that does not lie within the collection (0 to Count - 1) results in the return of a NULL OraField object.

Data Type

OLE Object (OraFields)


See Also

Count Property

OraDynaset Object


Filter Property

See Also

Remarks

The OraDynaset object does not support this property. Refine your record selection by using a SQL WHERE clause or by using SQL parameters.


See Also

CreateDynaset Method

OraDynaset Object

OraFields Collection

OraParameter Object


Font Property

Applies To

Oracle Data Control

Description

Determines the font object to be used for text displayed in a data control.

Usage

Oradata1.Font.Bold = True


Get_Value Property

Applies To

OraParamArray

Description

Returns the value of a particular element of the array at the specified index. Not available at design time and read only at run time.

Usage

OraParamArray.Get_Value(array index)

Remarks

OraParamArray.Get_Value returns the value of the field as a variant.

data_value = oraparameter.Value sets the contents of the parameter.

Note that fields of type DATE are returned in the default Visual Basic format as specified in the control panel, even though the default Oracle date format is "DD-MMM-YY".

Data Type

Variant


LastErrorText Property

Applies To

OraParamArray

Description

Gets the last error message. Not available at design time and read only at run time.

Usage

OraParamArray.LastErrorText

Data Type

String


LastModified Property

See Also

Applies To

OraDynaset

Description

Returns the bookmark of the row that was last modified by an Edit or an AddNew operation. Not available at design time and read-only at run time.

Usage

last_modified_bookmark = oradynaset.LastModified

Remarks

Use this property to make the last modified record the current record.

Data Type

The value is a string of binary data, but can be stored in a variable of String or Variant data type. The length of the string cannot be predicted, so do not use a fixed-length string.


See Also

AddNew Method

BookMark Property

Edit Method


LastServerErr Property

See Also	Example

Applies To

OraDatabase

OraSession

Description

Returns the last non-zero error code generated by an Oracle database function for the specified object. Not available at design time and read-only at run time.

Usage

error_number = oradatabase.LastServerErr

error_number = orasession.LastServerErr

Remarks

This property represents the last non-zero return value from an Oracle Call Interface (OCI) database function, or 0 if no error has occurred since the last LastServerErrReset request. For efficiency, only non-zero return values are returned; therefore, a non-zero value does not necessarily indicate that the most recently called OCI database function generated the error (because zero return values are not returned by way of LastServerErr).

Orasession.LastServerErr returns all errors related to connections, such as errors on OpenDatabase, BeginTrans, CommitTrans, Rollback and ResetTrans.

Oradatabase.LastServerErr returns all errors related to an Oracle cursor, such as errors on dynasets and from ExecuteSQL.

Data Type

Long Integer


See Also

ExecuteSQL Method

LastServerErrReset Method

LastServerErrText Property

OpenDatabase Method

OraDatabase Object

OraSession Object


LastServerErr Property Example

This example demonstrates the use of the LastServerErr and LastServerErrText properties to determine whether an Oracle error has occurred using CreateDynaset and to display the error message, respectively. Copy and paste this code into the definition section of a form. Then press F5.

Sub Form_Load ()

 'Declare variables as OLE Objects.

 Dim OraSession As Object

 Dim OraDatabase As Object

 Dim OraDynaset As Object

 'Create the OraSession Object.

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 'Set up an error handler.

 On Error GoTo errhandler

 'Create the OraDatabase Object by opening a connection to Oracle.

 Set OraDatabase = OraSession.DbOpenDatabase("ExampleDb", "scott/tiger", 0&)

 'Attempt to Create the OraDynaset Object.

 'Notice that the FROM keyword is missing from the SQL statement.

 Set OraDynaset = OraDatabase.DbCreateDynaset("select * emp", 0&)

Exit Sub

errhandler:

 'Check to see if an Oracle error has occurred.

 If OraDatabase.LastServerErr <> 0 Then

  MsgBox OraDatabase.LastServerErrText

 Else 'Must be some non-Oracle error

  MsgBox "VB:" & Err & " " & Error(Err)

 End If

 Exit Sub

End Sub


LastServerErrPos Property

See Also

Applies To

OraDatabase

Description

Returns the position in a SQL statement at which a parse error occurred. Not available at design time and read-only at run time.

Usage

error_pos = oradatabase.LastServerErrPos

Remarks

LastServerErrPos returns 0 if no SQL statements have been parsed, -1 if the last parse was successful, and >= 0 if the last parse failed. Parsing is done on SQL statements before execution (using CreateDynaset or ExecuteSQL).

Data Type

Integer


See Also

CreateDynaset Method

ExecuteSQL Method

LastServerErr Property

LastServerErrText Property

OraDatabase Object


LastServerErrText Property

See Also	Example

Applies To

OraDatabase

OraSession

Description

Returns the textual message associated with the current LastServerErr of the specified object. Not available at design time and read-only at run time.

Usage

error_text = orasession.LastServerErrText

error_text = oradatabase.LastServerErrText

Remarks

The returned value indicates one of three possible states:

1.	If NULL is returned, an Oracle Call Interface (OCI) database function has not returned an error since the most recent LastServerErrReset.

2.	If a non-NULL value is returned, an OCI function has returned an error code; the returned string is the associated message.

3.	If the message is empty, then additional information was not available.

Data Type

String


See Also

LastServerErr Property

LastServerErrReset Method

OraDatabase Object

OraSession Object


LastServerErrText Property Example

This example demonstrates the use of the LastServerErr and LastServerErrText properties to determine whether an Oracle error has occurred using CreateDynaset and to display the error message, respectively. Copy and paste this code into the definition section of a form. Then press F5.

Sub Form_Load ()

 'Declare variables as OLE Objects.

 Dim OraSession As Object

 Dim OraDatabase As Object

 Dim OraDynaset As Object

 'Create the OraSession Object.

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 'Set up an error handler.

 On Error GoTo errhandler

 'Create the OraDatabase Object by opening a connection to Oracle.

 Set OraDatabase = OraSession.DbOpenDatabase("ExampleDb", "scott/tiger", 0&)

 'Attempt to Create the OraDynaset Object.

 'Notice that the FROM keyword is missing from the SQL statement.

 Set OraDynaset = OraDatabase.DbCreateDynaset("select * emp", 0&)

Exit Sub

errhandler:

 'Check to see if an Oracle error has occurred.

 If OraDatabase.LastServerErr <> 0 Then

  MsgBox OraDatabase.LastServerErrText

 Else 'Must be some non-Oracle error.

  MsgBox "VB:" & Err & " " & Error(Err)

 End If

 Exit Sub

End Sub


MinimumSize Property

See Also	Example

Applies To

OraParameter

OraParamArray

Description

Returns the minimum size of an OraParameter or OraParamArray string buffer. For OraParamArrays, the minimum size property is read-only at run time. For OraParameters, the minimum size is read/write at run time.

Usage

oraparameter.MinimumSize

oraparamarray.MinimumSize

Remarks

Use this property to get the minimum number of characters or bytes to be allocated for each element of the array. For OraParamArrays, the size is specified using the AddTable method.

Data Type

Integer


MinimumSize Property Example

The following example assumes a PLSQL procedure EmployeeLong that returns a long ename of approximately 200 characters.

Sub Form_Load ()

' Declare variables as OLE Objects.

Dim OraSession As Object

Dim OraDatabase As Object

Dim OraDynaset As Object

' Create the OraSession Object.

Set OraSession = CreateObject("OracleInProcServer.XOraSession")

' Create the OraDatabase Object.

Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

' Add EMPNO as an Input/Output parameter and set its initial value.

OraDatabase.Parameters.Add "EMPNO", 9999, ORAPARM_INPUT

' Add ENAME as an Output parameter and set its initial value.

OraDatabase.Parameters.Add "ENAME_LONG", "foo", ORAPARM_OUTPUT

OraDatabase.Parameters("ENAME_LONG").MinimumSize = 201  'Since we require to hold a value

of more than 128 bytes

' Execute the Stored Procedure Employee.GetEmpName to retrieve ENAME_LONG.

OraDatabase.DbExecuteSQL ("Begin EmployeeLong.GetEmpName (:EMPNO, :ENAME); end;")


See Also

Add Method

AddTable

ExecuteSQL Method

OraParameter Object

OraParamArray Object


Name Property: OLE Server
Name Property

See Also

Applies To

OraClient

OraField

OraParameter

OraSession

OraParamArray

Description

Returns the name used to identify the given object. Not available at design time and read-only at run time.

Usage

client_name = oraclient.Name

field_name = orafield.Name

parameter_name = oraparameter.Name

paramarray_name = oraparamarray.Name

session_name = orasession.Name

Remarks

oraclient.Name returns the name of the specified OraClient object. This value is always "<local>".

orafield.Name returns the name of the specified OraField object. If this is a true database field (not an alias), this usage returns the name of the field as it appears on the server. If a SQL statement was executed that contains, for example, calculated select list items or column aliases, then the name is the actual text provided in the SELECT statement.

oraparameter.Name returns the name of the specified OraParameter object. In addition to identifying the parameter within a parameters collection, the parameter name is also used to match placeholders within SQL and PL/SQL statements for the purposes of parameter binding.

oraparamarray.Name returns the name of the specified OraParamArray object. In addition to  identifying the parameter within a parameters collection, the parameter name is also used to match placeholders within SQL and PL/SQL statements for the purposes of parameter binding.

orasession.Name returns the name of the specified OraSession object. For automatically created sessions, this is the name assigned by the system (usually a hexadecimal number). For user-created sessions, this is the name originally provided in the CreateSession method. Once created, a session name cannot be changed.

Data Type

String


See Also

CreateSession Method

OraClient Object

OraField Object

OraParameter Object

OraParamArray Object

OraSession Object


NoRefetch Property

Applies To

Oracle Data Control

Description

By default, NoRefetch is False,  this means that default data set by oracle will not be refetched to the local cache. If the database option is having this ORADB_NO_REFETCH, by default underlying recordset/dynaset will inherit this property.

Changing this property has no effect until a Refresh method is sent to the data control.

Usage

oradata1.NoRefetch = [ True | False ]

Data Type

Integer (Boolean)


NoMatch Property

Applies To

OraDynaset

Usage

Set nomatch_status = oradyanset.NoMatch

Description

True if the last call to FindFirst, FindLast, FindNext or FindPrevious failed.

Data Type

Boolean


OIPVersionNumber Property

See Also

Applies To

OraSession

Description

Returns the version number of the Oracle Object Server. Not available at design time and read-only at run time.

Usage

version_number = orasession.OIPVersionNumber

Remarks

This property returns a unique identifier for each release of the Oracle Object Server.

Data Type

String


See Also

OraSession Object


OracleMode: OLE Server
OracleMode Property

Applies To

Oracle Data Control

Description

Determine whether the  changes made to fields (columns) are immediately reflected  in the local mirror by retrieving the changed row from the database, thus allowing Oracle to set defaults for the columns and perform required calculations.

Usage

oradata1.OracleMode = [ True | False ]

Remarks

By default, OracleMode is True,  this means that  changes made to fields (columns) are immediately reflected  in the local cache.If the database option is having this ORADB_ORAMODE, by default underlying recordset/dynaset will inherit this property.

Changing this property has no effect until a Refresh method is sent to the data control.

Data Type

Integer (Boolean)


Options Property: OLE Server
Options Property

See Also

Applies To

OraDatabase

OraDynaset

OraSQLStmt

Description

Returns the options flag originally passed to the specified object. Not available at design time and read-only at run time.

Usage

options = oradatabase.Options

options = oradynaset.Options

options = orasqlstmt.Options

Remarks

Refer to the OpenDatabase method for a description of the possible values of oradatabase.Options.

Refer to the CreateDynaset method for a description of the possible values of oradynaset.Options.

Refer to the CreateSQL method for a description of the possible values of orasqlstmt.Options

Data Type

Long Integer


See Also

CreateDynaset Method

OpenDatabase Method

OraDatabase Object

OraDynaset Object

OraSQLStmt Object


OraIDataType Property

See Also

Applies To

OraField

Description

Returns the Oracle internal data type code for the field specified. Not available at design time and read-only at run time.

Usage

field_idatatype = orafield.OraIDataType

Remarks

The following Oracle Internal Data Types will be returned.

Constant	Value	Internal Data Type

ORAITYPE_VARCHAR2	1	VARCHAR2
ORAITYPE_NUMBER	2	NUMBER
ORAITYPE_LONG	8	LONG
ORAITYPE_ROWID	11	ROWID
ORAITYPE_DATE	12	DATE
ORAITYPE_RAW	23	RAW
ORAITYPE_LONGRAW	24	LONG RAW
ORAITYPE_CHAR	96	CHAR
ORAITYPE_MLSLABEL	106	MLSLABEL

These values can be found in the file ORACONST.TXT.

Data Type

Long Integer


See Also

OraMaxDSize Property

OraMaxSize Property

OraNullOK Property

OraPrecision Property

OraScale Property


OraMaxDSize Property

See Also

Applies To

OraField

Description

Returns the Oracle maximum display size for the field specified. Not available at design time and read-only at run time.

Usage

field_maxdisplaysize = orafield.OraMaxDSize

Remarks

This value is meaningful only when the value is returned as a character string, especially when using functions like SUBSTR or TO_CHAR to modify the representation of the column.

Data Type

Long Integer


See Also

OraIDataType Property

OraMaxSize Property

OraNullOK Property

OraPrecision Property

OraScale Property


OraMaxSize Property

See Also

Applies To

OraField

Description

Returns the Oracle maximum column size as stored in the ORACLE data dictionary. Not available at design time and read-only at run time.

Usage

field_maxsize = orafield.OraMaxSize

Remarks

The return value is dependent on the Oracle Internal Data Type. The following values will be returned.

Oracle Column Type	Value

CHAR, VARCHAR2, RAW	length of the column in the table
NUMBER	22 (the internal length)
DATE	7 (the internal length)
LONG, LONG RAW	0
ROWID	(system dependent)
Functions returning internal data type 1 (such as TO_CHAR())	same as orafield.MaxDSize
Data Type

Long Integer


See Also

OraIDataType Property

OraMaxDSize Property

OraNullOK Property

OraPrecision Property

OraScale Property


OraNullOK Property

See Also

Applies To

OraField

Description

Returns whether or not NULL values are permitted for this column. Not available at design time and read-only at run time.

Usage

field_nullok = orafield.OraNullOK

Remarks

This property returns True if NULL values are permitted and False otherwise.

Data Type

Integer (Boolean)


See Also

OraIDataType Property

OraMaxDSize Property

OraMaxSize Property

OraPrecision Property

OraScale Property


OraPrecision Property

See Also

Applies To

OraField

Description

Returns the precision of a numeric column. Not available at design time and read-only at run time.

Usage

field_precision = orafield.OraPrecision

Remarks

This value is meaningful only when the value returned is numeric. Precision is the total number of digits of a number.

Data Type

Long Integer


See Also

OraIDataType Property

OraMaxDSize Property

OraMaxSize Property

OraNullOK Property

OraScale Property


OraScale Property

See Also

Applies To

OraField

Description

Returns the scale of a numeric column. Not available at design time and read-only at run time.

Usage

field_scale = orafield.OraScale

Remarks

This value is meaningful only when the value returned is numeric. The SQL types REAL, DOUBLE PRECISION, FLOAT and FLOAT(N) return a scale of -127.

Data Type

Long Integer


See Also

OraIDataType Property

OraMaxDSize Property

OraMaxSize Property

OraNullOK Property

OraPrecision Property


Parameters Property

See Also

Applies To

OraDatabase

Description

Returns the OraParameters collection of the specified database. Not available at design time and read-only at run time.

Usage

Set oraparameters_collection = oradatabase.Parameters

Remarks

You can access the parameters in this collection by subscripting (using ordinal integer numbers) or by using the name the parameter was given at its creation. You can obtain the number of parameters in the collection using the Count property of the returned collection. Integer subscripts begin with 0 and end with Count-1. Out-of-range indices and invalid names return a NULL OraParameter object.

In addition to accessing the parameters of the collection, you can also use the collection to create and destroy parameters using the Add and Remove methods, respectively.

Data Type

OLE Object (OraParameters)


See Also

Add Method

Count Property

OraParameter Object

OraParameters Collection

Remove Method


Put_Value Property

Applies To

OraParamArray

Description

This command is used to insert values into the table parameter. A row does not contain any valid value until a row is assigned a value. Any reference to an unassigned row in the table will raise "OLE Automation error".

Usage

OraParamArray.Put_Value(value, array index)

Data Type

Variant


RDMSVersion Property

Applies To

OraDatabase

Usage

Set Version = oradatabase.RDBMSVersion

Description

Returns database version.

Data Type

String


Record Count Example for OraSQLStmt
Record Count Example (OraSQLStmt)

The following example shows the number of records inserted into the database after using an insert statement with OraSQLStmt.

Dim OraSession As Object

Dim OraDatabase As Object

Dim OraDynaset As Object

Dim OraSqlStmt As Object

Dim OraPArray1 As Object

Dim OraPArray2 As Object

Dim I As Integer

On Error GoTo ERR_array_sql

'Test case for inserting/updating/deleting multiple rows using parameter arrys

with SQL statements

Set OraSession = CreateObject("OracleInProcServer.XOraSession")

Set OraDatabase = OraSession.OpenDatabase("exampledb", "scott/tiger", 0&)

'Create table

OraDatabase.DbExecuteSQL ("create table part_nos(partno number, description

char(50), primary key(partno))")

OraDatabase.Parameters.AddTable "PARTNO", ORAPARM_INPUT, ORATYPE_NUMBER, 10, 22

OraDatabase.Parameters.AddTable "DESCRIPTION", ORAPARM_INPUT, ORATYPE_CHAR,

10, 50

If OraDatabase.LastServerErr <> 0 Or OraDatabase.LastServerErrText <> "" Then

  MsgBox "Error"

End If

Set OraPArray1 = OraDatabase.Parameters("PARTNO")

Set OraPArray2 = OraDatabase.Parameters("DESCRIPTION")

'Initialize arrays

For I = 0 To 9

  achar = "Description" + Str(I)

  OraPArray1.put_Value 1000 + I, I

  OraPArray2.put_Value achar, I

Next I

Set OraSqlStmt = OraDatabase.CreateSql("insert into

part_nos(partno,description) values(:PARTNO,:DESCRIPTION)", 0&)

If OraDatabase.LastServerErr <> 0 Or OraDatabase.LastServerErrText <> "" Then

  MsgBox "Error"

End If

MsgBox "# of records inserted : " & OraSqlStmt.RecordCount

Exit Sub

ERR_array_sql:

    MsgBox Err.Description


RecordCount Property

See Also	Examples

Applies To

OraDynaset

OraSQLStmt

Description

For the OraDynaset object, RecordCount returns the total number of records in the dynaset.

For the OraSQLStmt object, RecordCount returns the number of records processed in an insert, update, or delete statement, even when there is a failure executing the SQL statement.

Not available at design time and read-only at run time.

Usage

record_count = oradynaset.RecordCount

record_count = orasqlstmt.RecordCount

Remarks

Referencing this property requires that the entire result table be fetched immediately from Oracle in order to determine the count of records. While it would have been possible to execute a separate query (using COUNT(*)), the consistency of the count could not be ensured since row locking is not done to ensure repeatable reads.

Referencing this property while using the ORADYN_NOCACHE option of the CreateDynaset method causes an implicit MoveLast and makes the current record the last record in the dynaset.

Data Type

Long Integer


See Also

CreateDynaset Method

Locks and Editing

MoveLast Method

LastServerErrText Property

OraDynaset Object


RecordCount Examples


RecordCount Example (OraDynaset)

This example demonstrates the use of the RecordCount property to determine the number of records retrieved. Copy and paste this code into the definition section of a form. Then press F5.

Sub Form_Load ()

 'Declare variables as OLE Objects.

 Dim OraSession As Object

 Dim OraDatabase As Object

 Dim OraDynaset As Object

 'Create the OraSession Object.

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 'Create the OraDatabase Object by opening a connection to Oracle.

 Set OraDatabase = OraSession.DbOpenDatabase("ExampleDb", "scott/tiger", 0&)

 'Create the dynaset.

 Set OraDynaset = OraDatabase.DbCreateDynaset("select * from emp", 0&)

 'Display the number of records. Note that this causes

 'all records to be fetched to ensure an accurate count.

 MsgBox OraDynaset.RecordCount & " records retrieved."

End Sub


ServerType Property

See Also	Example

Applies To

OraParameter

OraParamArray

Description

Specifies the Oracle external type of SQL or PL/SQL bind variable. Not available at design time and read/write at run time.

Read only for the OraParamArray object. (You specify ServerType during the AddTable method.)

Usage

oraparameter.ServerType = oracle_type

Remarks

Used to specify the external data type of SQL or PL/SQL (in/out) bind variables. This is necessary since no local parsing of the SQL statement or PL/SQL block is done to match the data types of placeholders in the SQL statement or PL/SQL block.

The following Oracle external data types are supported.

Constant	Value	External Data Type

ORATYPE_VARCHAR2	1	VARCHAR2
ORATYPE_NUMBER	2	NUMBER
ORATYPE_SINT	3	SIGNED INTEGER
ORATYPE_FLOAT	4	FLOAT
ORATYPE_STRING	5	Null Terminated STRING
ORATYPE_VARCHAR	9	VARCHAR
ORATYPE_DATE	12	DATE
ORATYPE_UINT	68	UNSIGNED INTEGER
ORATYPE_CHAR	96	CHAR
ORATYPE_CHARZ	97	Null Terminated CHAR

These values can be found in the file ORACONST.TXT.

Data Type

Integer


See Also

Add Method

OraParameter Object

OraParamArray Object


ServerType Property Example

This example demonstrates the Add and Remove parameter methods, the ServerType parameter property and the the ExecuteSQL database method to call a Stored Procedure and Function (located in ORAEXAMP.SQL). Copy and paste this code into the definition section of a form. Then press F5.

Sub Form_Load ()

'Declare variables as OLE Objects.

 Dim OraSession As Object

 Dim OraDatabase As Object

 Dim OraDynaset As Object

 'Create the OraSession Object.

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 'Create the OraDatabase Object.

 Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

 'Add EMPNO as an Input/Output parameter and set its initial value.

 OraDatabase.Parameters.Add "EMPNO", 7369, ORAPARM_INPUT

 OraDatabase.Parameters("EMPNO").ServerType = ORATYPE_NUMBER

 'Add ENAME as an Output parameter and set its initial value.

 OraDatabase.Parameters.Add "ENAME", 0, ORAPARM_OUTPUT

 OraDatabase.Parameters("ENAME").ServerType = ORATYPE_VARCHAR2

 'Add SAL as an Output parameter and set its initial value.

 OraDatabase.Parameters.Add "SAL", 0, ORAPARM_OUTPUT

 OraDatabase.Parameters("SAL").ServerType = ORATYPE_NUMBER

 'Execute the Stored Procedure Employee.GetEmpName to retrieve ENAME.

 ' This Stored Procedure can be found in the file ORAEXAMP.SQL.

 OraDatabase.DbExecuteSQL ("Begin Employee.GetEmpName (:EMPNO, :ENAME); end;")

 'Display the employee number and name.

 'Execute the Stored Function Employee.GetSal to retrieve SAL.

 ' This Stored Function can be found in the file ORAEXAMP.SQL.

 OraDatabase.DbExecuteSQL ("declare SAL number(7,2); Begin :SAL:=Employee.GetEmpSal (:EMPNO); end;")

 'Display the employee name, number and salary.

 MsgBox "Employee " & OraDatabase.Parameters("ENAME").value & ", #" & OraDatabase.Parameters("EMPNO").value & ",Salary=" & OraDatabase.Parameters("SAL").value

 'Remove the Parameters.

 OraDatabase.Parameters.Remove "EMPNO"

 OraDatabase.Parameters.Remove "ENAME"

 OraDatabase.Parameters.Remove "SAL"

End Sub


Session Property: OLE Server
Session Property

See Also

Applies To

OraConnection

OraDatabase

OraDynaset Object

OraSQLStmt

Description

Returns the OraSession object associated with the specified object. Not available at design time and read-only at run time.

Usage

Set orasession = oraconnection.Session

Set orasession = oradatabase.Session

Set orasession = oradynaset.Session

Set orasession = orasqlstmt.Session

Remarks

oraconnection.Session returns the OraSession object in which this OraConnection object resides.

oradatabase.Session returns the OraSession object associated with this OraDatabase object. Each database is a part of one and only one session, which is by default the session associated with the application.

oradynaset.Session returns the OraSession object associated with this OraDynaset object.

orasqlstmt.Session returns the OraSession object associated with this OraSqlStmt object.

Data Type

OLE Object (OraSession)


See Also

OraConnection Object

OraDatabase Object

OraDynaset Object

OraSession Object

OraSessions Collection

OraSQLStmt Object


Sessions Property

See Also

Applies To

OraClient

Description

Returns the collection of all sessions for the specified OraClient object. Not available at design time and read-only at run time.

Usage

Set orasessions_collection = oraclient.Sessions

Remarks

You can access a session in this collection by subscripting (using ordinal numbers) or by using the name the session was given at its creation. You can obtain the total number of sessions in the collection by using the Count property of the returned collection. Integer subscripts begin with 0 and end with Count-1. Out-of-range indices and invalid names return a NULL OraSession object.

Data Type

OLE Object (OraSessions)


See Also

Count Property

OraSession Object

OraSessions Collection


Size Property

See Also

Applies To

OraField

Description

Returns the number of characters or bytes of the variant associated with the returned value of this field. Not available at design time and read-only at run time.

Usage

field_size = orafield.Size

Remarks

This usage always returns 0 for LONG or LONG RAW fields. Use the FieldSize method to determine the length of LONG or LONG RAW fields.

Data Type

Long Integer


See Also

OraFields Collection

FieldSize Property

Long and Long Raw Columns

Type Property


Sort Property

See Also

Remarks

The OraDynaset object does not support this property. Sort your recordset by using a SQL ORDER BY clause.


See Also

CreateDynaset Method

OraDynaset Object


SQL Property

See Also	Example

Applies To

OraDynaset

OraSQLStmt

Description

Returns or sets the SQL statement used to create the specified dynaset or sqlstmt object. Not available at design time and read/write at run time.

Usage

SQL_statement  = oradynaset.SQL

SQL_statement  = orasqlstmt.SQL

oradynaset.SQL = SQL_statement

orasqlstmt.SQL = SQL_statement

Remarks

The first form above  returns the contents of the SQL statement buffer.

The second form above sets the contents of the SQL statement buffer.

The SQL statement buffer initially contains the SQL statement used to create the dynaset or sqlstmt object. The contents of the SQL statement buffer are executed whenever the Refresh method is issued.

Data Type

String


See Also

OraDynaset Object

OraSQLStmt Object

Refresh Method


SQL Property Example

This example demonstrates the use of parameters, the Refresh method and the SQL property to restrict selected records. Copy and paste this code into the definition section of a form. Then press F5.

Sub Form_Load ()

 'Declare variables as OLE Objects.

 Dim OraSession As Object

 Dim OraDatabase As Object

 Dim OraDynaset As Object

 'Create the OraSession Object.

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 'Create the OraDatabase Object by opening a connection to Oracle.

 Set OraDatabase = OraSession.DbOpenDatabase("ExampleDb", "scott/tiger", 0&)

 'Create a parameter with an initial value.

 OraDatabase.Parameters.Add "job", "MANAGER", 1

 'Create the OraDynaset Object.

 Set OraDynaset = OraDatabase.DbCreateDynaset("select * from emp where job=:job", 0&)

 'Notice that the SQL statement is NOT modified.

 MsgBox OraDynaset.SQL

 'Currently, OraDynaset only contains employees whose

 'job is MANAGER.

 'Change the value of the job parameter.

 OraDatabase.Parameters("job").Value = "SALESMAN"

 'Refresh the dynaset.

 OraDynaset.DbRefresh

 'Currently, OraDynaset only contains employees whose

 'job is SALESMAN.

 'Notice that the SQL statement is NOT modified.

 MsgBox OraDynaset.SQL

 'Remove the parameter.

 OraDatabase.Parameters.Remove ("job")

 End Sub


Status Property

See Also	Example

Applies To

OraParameter

OraParamArray

Description

Returns an integer indicating the status of the specified parameter. Not available at design time and read-only at run time.

Usage

parameter_status = oraparameter.Status

parameter_status = oraparamarray.Status

Remarks

The Status property is interpreted as a series of bits, each providing information about the parameter. Parameters can be bound only if they are enabled, and can be enabled only if they are autoenabled.

The parameter Status property bit values are:

Constant	Value	Description

ORAPSTAT_INPUT	&H1&	Parameter can be used for input.
ORAPSTAT_OUTPUT	&H2&	Parameter can be used for output.
ORAPSTAT_AUTOENABLE	&H4&	Parameter is AutoBindEnabled.
ORAPSTAT_ENABLE	&H8&	Parameter is Enabled. This bit is always set.

These values are located in the file ORACONST.TXT.

Data Type

Integer


See Also

Add Method

AutoBindDisable Method

AutoBindEnable Method

OraParameter Object

OraParamArray Object

Remove Method


Status Property Example

This example demonstrates the use of parameters and ExecuteSQL to call a Stored Procedure (located in ORAEXAMP.SQL). After calling the Stored Procedure, the Status property of each parameter is checked. Copy and paste this code into the definition section of a form. Then press F5.

Sub Form_Load ()

 'Declare variables as OLE Objects.

 Dim OraSession As Object

 Dim OraDatabase As Object

 Dim OraDynaset As Object

 'Create the OraSession Object.

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 'Create the OraDatabase Object by opening a connection to Oracle.

 Set OraDatabase = OraSession.DbOpenDatabase("ExampleDb", "scott/tiger", 0&)

 'Add EMPNO as an Input parameter and set its initial value.

 OraDatabase.Parameters.Add "EMPNO", 7369, ORAPARM_INPUT

 'Add ENAME as an Output parameter and set its initial value.

 OraDatabase.Parameters.Add "ENAME", 0, ORAPARM_OUTPUT

 'Execute the Stored Procedure Employee.GetEmpName to retrieve ENAME.

 ' This Stored Procedure is located in the file ORAEXAMP.SQL.

 OraDatabase.DbExecuteSQL ("Begin Employee.GetEmpName (:EMPNO, :ENAME); end;")

 If OraDatabase.Parameters("EMPNO").Status & ORAPSTAT_INPUT Then

  MsgBox "Parameter EMPNO used for input."

 End If

 If OraDatabase.Parameters("ENAME").Status & ORAPSTAT_OUTPUT Then

  MsgBox "Parameter ENAME used for output."

 End If

'Display the employee number and name.

 MsgBox OraDatabase.Parameters("EMPNO").value

 MsgBox OraDatabase.Parameters("ENAME").value

 'Remove the Parameters.

 OraDatabase.Parameters.Remove "EMPNO"

 OraDatabase.Parameters.Remove "ENAME"

End Sub


Transactions Property

See Also

Applies To

OraDynaset

Description

Returns whether or not the given dynaset can support transaction processing. Not available at design time and read-only at run time.

Usage

if_transactions = oradynaset.Transactions

Remarks

This property never returns False and has only been implemented for compatibility.

Data Type

Integer (Boolean)


See Also

BeginTrans Method

CommitTrans Method

OraDynaset Object

ResetTrans Method

Rollback Method


Truncated Property

See Also

Applies To

OraField

Description

Returns whether or not a field value was truncated when fetched. Not available at design time and read-only at run time.

Usage

field_status = orafield.Truncated

Remarks

This property returns True if truncated data will be returned and False otherwise. Truncation can only occur for LONG or LONG RAW fields. Use this property to decide whether more data needs to be retrieved from Oracle using the GetChunk method.

Data Type

Integer (Boolean)


See Also

GetChunk Method

OraField Object

Type Property


Type Property

See Also

Applies To

OraField

OraParameter

OraParamArray

Description

Returns the variant type of the specified object. Not available at design time and read-only at run time.

Usage

data_type = orafield.Type

data_type = oraparameter.Type

data_type = oraparamarray.Type

Remarks

orafield.Type returns the variant data type (see Visual Basic documentation) associated with the returned value of this field.

Users can expect the following mapping from Oracle internal data types:

Oracle Data Type	Constant	Value	Data Type
CHAR	ORADB_TEXT	10	String
DATE	ORADB_DATE	8	Variant
LONG	ORADB_MEMO	12	String
LONG RAW	ORADB_LONGBINARY	11	String
NUMBER (1-4,0)	ORADB_INTEGER	3	Integer
NUMBER (5-9,0)	ORADB_LONG	4	Long Integer
NUMBER (10-15,0)	ORADB_DOUBLE	7	Double
NUMBER (16-38,0)	ORADB_TEXT	10	String
NUMBER (1-15,n)	ORADB_DOUBLE	7	Double
NUMBER (16-38,n)	ORADB_TEXT	10	String
RAW	ORADB_LONGBINARY	11	String
VARCHAR2	ORADB_TEXT	10	String

These values are located in the file ORACONST.TXT and are intended to match similar constants in the Visual Basic file DATACONS.TXT.

oraparameter.Type returns an integer indicating the variant data type that is actually bound to the SQL statement. This may differ from the variant data type of oraparameter.Value, because internal conversions may be necessary to obtain a data type common to both Visual Basic and the Oracle database.

Note that fields of type DATE are returned in the default Visual Basic format as specified in the control panel, even though the default Oracle date format is "DD-MMM-YY".

Note that columns defined simply as "NUMBER" instead of "NUMBER(precision,scale)" are, by definition, floating point numbers with a precision of 38. This means that the Type property will return a type of ORADB_TEXT for these columns.

Data Type

Integer


See Also

OraField Object

OraParameter Object

OraParamArray Object

Value Property


Updatable Property

See Also	Example

Applies To

OraDynaset

Description

Returns whether or not the specified dynaset is updatable. Not available at design time and read-only at run time.

Usage

if_updatable = oradynaset.Updatable

Remarks

Returns True if the rows in the specified dynaset can be updated and False otherwise.

The updatability of the resultant dynaset depends on the Oracle SQL rules of updatability, on the access you have been granted, and on the read-only flag of the CreateDynaset method.

In order to be updatable, three conditions must be met:

1.	the SQL statement must refer to a simple column list or to the entire column list (*),

2.	the SQL statement must not set the read-only flag of the options argument, and

3.	Oracle must permit ROWID references to the selected rows of the query.

Any SQL statement that does not meet these criteria is processed, but the results are not updatable and this property returns False.

Data Type

Integer (Boolean)


See Also

CreateDynaset Method

RecordSource Property

SQL Property


Updatable Property Example

This example demonstrates the use of Updatable. Copy and paste this code into the definition section of a form. Then press F5.

Sub Form_Load ()

 'Declare variables as OLE Objects.

 Dim OraSession As Object

 Dim OraDatabase As Object

 Dim OraDynaset As Object

 'Create the OraSession Object.

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 'Create the OraDatabase Object by opening a connection to Oracle.

 Set OraDatabase = OraSession.DbOpenDatabase("ExampleDb", "scott/tiger", 0&)

 'Create an updatable dynaset using a simple query.

 Set OraDynaset = OraDatabase.DbCreateDynaset("select * from emp", 0&)

 Call IsDynUpdatable(OraDynaset)

 'Create a nonupdatable dynaset using column aliases.

 Set OraDynaset = OraDatabase.DbCreateDynaset("select ename EmployeeName, empno EmployeeNumber, sal Salary from emp", 0&)

 Call IsDynUpdatable(OraDynaset)

 'Create a nonupdatable dynaset using a join.

 Set OraDynaset = OraDatabase.DbCreateDynaset("select ename, emp.deptno, loc from emp,dept where emp.deptno = dept.deptno", 0&)

 Call IsDynUpdatable(OraDynaset)

End Sub

Sub IsDynUpdatable (odyn As Object)

 'Check to see if the dynaset is updatable.

 If odyn.Updatable = True Then

  MsgBox "Created an UPDATABLE dynaset from: '" & odyn.SQL & "'"

 Else

  MsgBox "Created a READ-ONLY dynaset from: '" & odyn.SQL & "'"

 End If

End Sub


Value Property

See Also

Applies To

OraField

OraParameter

Description

Returns or sets the value of the given object. Not available at design time and read/write at run time.

Usage

orafield.Value = data_value

data_value = orafield.Value

oraparameter.Value = data_value

data_value = oraparameter.Value

Remarks

Orafield.Value returns the value of the field as a variant. data_value = orafield.Value sets the contents of the field. The variant type reflects the data type as defined by the Type property. Fields can contain NULL values. You can test the Value property with the Visual Basic function IsNull() to determine whether the value is null upon return. You can also assign NULL to the Value property whenever the current record is editable. Field values are cached locally as the data is retrieved from the database. However, in the case of a LONG or LONG RAW fields, some data may not be retrieved and stored locally. In these cases, data is retrieved as required using the methods described in the GetChunk field method. The maximum size of a LONG or LONG RAW field that can be retrieved directly through the Value property is approximately 64KB. You must retrieve data fields larger than 64KB indirectly, using the GetChunk method.

OraParameter.Value returns the value of the parameter as a variant. data_value = oraparameter.Value sets the contents of the parameter. Note that changing the variant data type of the value can have significant impact on the processing of associated SQL and PL/SQL statements.

Note that fields of type DATE are returned in the default Visual Basic format of "MM/DD/YY" even though the default Oracle date format is "DD-MMM-YY".

Data Type

Variant


See Also

GetChunk Method

OraField Object

OraParameter Object

OraParamArray Object

Type Property


RowPosition

Applies To

OraDynaset

Description

Returns the current row number of the current row in the dynaset. Not available in design time and read only in runtime.

Usage

rownum = OraDynaset.RowPosition


DbPoolMaxSize

Applies To

OraSession

Description

This property contains the maximum pool size. It is a read-only property.

Usage

max_size = OraSession.DbPoolMaxSize


DbPoolCurrentSize

Applies To

OraSession

Description

This property contains the number of currently active database objects. It is a read-only property.

Usage

curr_size = OraSession.DbPoolCurrentSize


DbPoolInitialSize

Applies To

OraSession

Description

This property contains the  initial size of the pool. It is a read-only property.

Usage

init_size = OraSession.DbPoolInitialSize


OLE Server

See Also	Objects	Methods	Properties

Description

The OLE Server implements, using OLE 2.0, the various Oracle objects and their programmatic interfaces.

Remarks

The Oracle Object Server is an OLE In Process server that supports a collection of programmable objects for Oracle8 databases running either locally or remotely. An OLE In Process supports the OLE Automation interface, has no user interface and is not embeddable.


See Also

Getting Started

Overview

Requirements


OLE Server Objects
Objects

OraClient

OraConnection

OraDatabase

OraDynaset

OraField

OraParameter

OraParamArray

OraSession

OraSQLStmt

OraConnections Collection

OraFields Collection

OraParameters Collection

OraSessions Collection


OLE Server Objects

OraClient

OraConnection

OraDatabase

OraDynaset

OraField

OraParameter

OraParamArray

OraSession

OraSQLStmt

OraConnections Collection

OraFields Collection

OraParameters Collection

OraSessions Collection


OLE Server Objects

OraClient

OraConnection

OraDatabase

OraDynaset

OraField

OraParameter

OraParamArray

OraSession

OraSQLStmt

OraConnections Collection

OraFields Collection

OraParameters Collection

OraSessions Collection


OraClient Object

See Also	Properties	Methods

Description

An OraClient object defines a workstation domain, and all of that workstation’s OraSession objects are listed in the OraClient object's OraSessions collection.

Remarks

Only one OraClient object can exist for each workstation, and it is created automatically by the system when it is needed.


See Also

OraSession Object

OraSessions Collection


Properties

Name

Sessions


Methods

CreateSession


OraConnection Object

See AlsoIDH_See_Also_OConnObj	PropertiesIDH_Properties_OConnObj	MethodsIDH_Methods_OConnObj

Connect

DatabaseName

Session


Methods

BeginTrans Method

CommitTrans

Rollback


OraDatabase Object

See Also	Properties	Methods

Description

An OraDatabase object represents a single virtual login to an Oracle database.

Remarks

The OraDatabase object is created using a username, password, and database name combination that establishes access and security. An OraDatabase object can establish a connection if one does not exist, or share an existing connection if one does exist. A connection is shared when the current Connection and DatabaseName properties of an OraDatabase object exactly match those properties of an existing OraDatabase object.


See Also

Connection Property

DatabaseName Property

OpenDatabase Method.


Properties

Connect

Connection

DatabaseName

LastServerErr

LastServerErrPos

LastServerErrText

Options

Parameters

RDMSVersion

Description

An OraConnection object represents a single connection to an Oracle database.

Remarks

An OraConnection object is created automatically whenever an OraDatabase object is instantiated within the session, and is destroyed automatically whenever all databases using the connection are discarded. Currently there is no way to create an OraConnection object explicitly, only by creating an OraDatabase object that requires a connection.


See Also

OraConnections Collection

OraDatabase Object


Properties

Session


Methods

Close Method

CreateCustomDynaset

CreateDynaset

CreateSQL

ExecuteSQL

LastServerErrReset


OraDynaset Object

See Also	Properties	Methods

Description

An OraDynaset object permits browsing and updating of data created from a SQL SELECT statement.

Remarks

The OraDynaset object can be thought of as a cursor, although in actuality several real cursors may be used to implement the OraDynaset's semantics. An OraDynaset automatically maintains a local cache of data fetched from the server and transparently implements scrollable cursors within the browse data. Large queries may require significant local disk space; application implementors are encouraged to refine queries to limit disk usage. Placement (temporary file storage) and tuning of the local cache is accomplished through the ORAOLE.INI file

This object provides transparent mirroring of database operations, such as updates. When data is updated via the Update method, the local mirror image of the query is updated so that the data appears to have been changed without reevaluating the query. The same procedure is used automatically when records are added to the dynaset. Integrity checking is performed to ensure that the mirrored image of the data always matches the actual data present on the Oracle database. This integrity checking is performed only when necessary (such as just before updates occur).

During create and refresh, OraDynaset objects automatically bind all relevant, enabled, input parameters to the specified SQL statement, using the parameter names as placeholders in the SQL statement. This can simplify dynamic query building and increase the efficiency of multiple queries using the same SQL statement with varying WHERE clauses.


See Also

CreateDynaset Method

ORAOLE.INI File

OraParameter Object

RowPosition Property

Update Method


Properties

BOF

Bookmark

BookMarkable

CacheBlocks

CacheChanged

CacheSliceSize

CacheSlicesPerBlock

Connection

Database

EditMode

EOF

FetchLimit

FetchSize

Fields

LastModified

NoMatch

Options

RecordCount

RowPosition

Session

SQL

Transactions

Updatable


Methods

AddNew

Clone

Close

Delete

Edit

FindFirst FindLast FindNext FindPrevious

MoveNextn MovePreviousn MoveRel MoveTo

MoveFirst MoveLast MovePrevious MoveNext

Refresh

Update


OraField Object

See Also	Properties	Methods

Description

An OraField object represents a single column or data item within a row of a dynaset.

Remarks

An OraField object is accessed indirectly by retrieving a field from the OraFields collection of an OraDynaset object.

If the current row is being updated, then the OraField object represents the currently updated value, although the value may not yet have been committed to the database.

Assignment to the Value property of a field is permitted only if a record is being edited (using Edit) or a new record is being added (using AddNew). Other attempts to assign data to a field's Value property results in an error.


See Also

AddNew Method

Edit Method

OraDynaset Object

OraFields Collection

Value Property


Properties

OraIDataType

OraMaxDSize

OraMaxSize

OraNullOK

OraPrecision

OraScale

Name

Size

Truncated

Type

Value


Methods

AppendChunk

FieldSize

GetChunk

GetChunkByteEx


OraParameter Object

See Also	Properties	Methods

Description

An OraParameter object represents a bind variable in a SQL statement or PL/SQL block.

Remarks

OraParameter objects are created, accessed, and removed indirectly through the OraParameters collection of an OraDatabase object. Each parameter has an identifying name and an associated value. You can automatically bind a parameter to SQL and PL/SQL statements of other objects (as noted in the objects’ descriptions), by using the parameter’s name as a placeholder in the SQL or PL/SQL statement. Such use of parameters can simplify dynamic queries and increase program performance.

Parameters are bound to SQL statements and PL/SQL blocks before execution. In the case of a SQL SELECT statement, binding occurs before dynaset creation.

The OraParameters collection is part of the OraDatabase object so that all parameters are available to any SQL statement or PL/SQL block executed within the database (via CreateDynaset or ExecuteSQL). Before a SQL statement or PL/SQL block is executed an attempt is made to bind all parameters of the associated OraDatabase object. The bindings that fail (because the parameter doesn't apply to that particular SQL statement or PL/SQL block), are noted and no attempt is made to rebind them if the SQL statement or PL/SQL block is re-executed but doesn't change.

Since neither SQL statements nor PL/SQL blocks are parsed locally (all parsing is done by Oracle), any unnecessary binding results in performance degradation. To prevent unnecessary parameter binding, make use of the AutoBindDisable and AutoBindEnable methods.

By default, the maximum size of the ORAPARM_OUTPUT variable for ServerType CHAR

and VARCHAR2 is set to 127 bytes. Use the MinimumSize property to change this value. The minimum size of an ORAPARM_OUTPUT variable for CHAR and VARCHAR2 must always be greater than the size of the expected data from the database column.


See Also

AutoBindDisable Method

AutoBindEnable Method

CreateDynaset Method

DynasetCacheParams Method

DynasetOption Property

ExecuteSQL Method

Name Property

OraDatabase Object

OraParameters Collection

Value Property


Properties

DynasetOption

MinimumSize

Name

ServerType

Status

Type

Value


Methods

AutoBindDisable

AutoBindEnable

DynasetCacheParams


OraParamArray Object

See Also	Properties	Methods	Examples

Description

An OraParamArray object represents an "array" type bind variable in a SQL statement or PL/SQL block as opposed to a "scalar" type bind variable represented by the OraParameter object.

Remarks

OraParamArray objects are created, accessed, and removed indirectly through the OraParameters collection of an OraDatabase object. Each parameter has an identifying  name and an associated value. You can automatically bind a parameter to SQL and PL/SQL  statements of other objects (as noted in the objects descriptions) by using the parameters name as a placeholder in the SQL or PL/SQL statement. Such use of parameters can simplify dynamic queries and increase program performance.

Parameters are bound to SQL statements and PL/SQL blocks before execution.

The OraParameters collection is part of the OraDatabase object so that all parameters are available to any SQL statement or PL/SQL block executed within the database (via CreateDynaset, ExecuteSQL, or CreateSQL). Before a SQL statement or PL/SQL block is executed, an attempt is made to bind all parameters of the associated OraDatabase object. The bindings that fail (because the parameter doesn't apply to that particular SQL statement or PL/SQL block) are noted and no attempt is made to rebind them if the SQL statement or PL/SQL block is re-executed but doesn't change.

Since neither SQL statements nor PL/SQL blocks are parsed locally (all parsing is done by Oracle), any unnecessary binding results in performance degradation. To prevent unnecessary parameter binding, make use of the AutoBindDisable and AutoBindEnable methods.


OraParamArray Examples

With SQL Statements

With PL/SQL


Using OraParamArrays with SQL Statements

The follwing example shows how to use the OraParamArray object with SQL statements:

Dim OraSession As Object

Dim OraDatabase As Object

Dim OraDynaset As Object

Dim OraSqlStmt As Object

Dim OraPArray1 As Object

Dim OraPArray2 As Object

Dim I As Integer

On Error GoTo ERR_array_sql

'Test case for inserting/updating/deleting multiple rows using parameter arrys

with SQL statements

Set OraSession = CreateObject("OracleInProcServer.XOraSession")

Set OraDatabase = OraSession.OpenDatabase("exampledb", "scott/tiger", 0&)

'Create table

OraDatabase.DbExecuteSQL ("create table part_nos(partno number, description

char(50), primary key(partno))")

OraDatabase.Parameters.AddTable "PARTNO", ORAPARM_INPUT, ORATYPE_NUMBER, 10, 22

OraDatabase.Parameters.AddTable "DESCRIPTION", ORAPARM_INPUT, ORATYPE_CHAR,

10, 50

If OraDatabase.LastServerErr <> 0 Or OraDatabase.LastServerErrText <> "" Then

  MsgBox "Error"

End If

Set OraPArray1 = OraDatabase.Parameters("PARTNO")

Set OraPArray2 = OraDatabase.Parameters("DESCRIPTION")

'Initialize arrays

For I = 0 To 9

  achar = "Description" + Str(I)

  OraPArray1.put_Value 1000 + I, I

  OraPArray2.put_Value achar, I

Next I

If OraDatabase.LastServerErr <> 0 Or OraDatabase.LastServerErrText <> "" Then

  MsgBox "Error"

End If

Set OraSqlStmt = OraDatabase.CreateSql("insert into

part_nos(partno,description) values(:PARTNO,:DESCRIPTION)", 0&)

If OraDatabase.LastServerErr <> 0 Or OraDatabase.LastServerErrText <> "" Then

  MsgBox "Error"

End If

Set OraDynaset = OraDatabase.DbCreateDynaset("select count(*) as no_rows from

part_nos", 0&)

If OraDynaset.Fields("no_rows") <> 10 Then

    MsgBox "Error"

End If

'Update the newly created part_nos table

For I = 0 To 9

  achar = "Description" + Str(1000 + I)

  OraPArray2.put_Value achar, I

Next I

If OraDatabase.LastServerErr <> 0 Or OraDatabase.LastServerErrText <> "" Then

  MsgBox "Error"

End If

'Update table

ErrNum = ORASQL_SQL

Set OraSqlStmt = OraDatabase.CreateSql("update part_nos set DESCRIPTION =

:DESCRIPTION where PARTNO = :PARTNO", 0&)

'Check if updated

Set OraDynaset = OraDatabase.DbCreateDynaset("select * from part_nos", 0&)

Do Until OraDynaset.EOF = True

    achar = "Description" + Str(1000 + (OraDynaset.Fields("partno") - 1000)) '

I = OraDynaset.Fields("partno") - 1000 as OraDynaset.Fields("partno") = 1000 +

I

    If OraDynaset.Fields("description") <> achar Then

        MsgBox "Error"

    End If

    OraDynaset.DbMoveNext

Loop

'Deleting rows

Set OraSqlStmt = OraDatabase.CreateSql("delete  from  part_nos where

DESCRIPTION=: Description ", 0&)

Set OraDynaset = OraDatabase.DbCreateDynaset("select count(*) as no_rows from

part_nos", 0&)

If OraDynaset.Fields("no_rows") <> 0 Then

    MsgBox "Error"

End If

OraDatabase.DbExecuteSQL ("drop table part_nos")

 'Remove the Parameters.

 OraDatabase.Parameters.Remove 0

 OraDatabase.Parameters.Remove 1

 If OraDatabase.LastServerErr <> 0 Or OraDatabase.LastServerErrText <> "" Then

   MsgBox "Error"

 End If

'Boilerplate error handling:

Exit Sub

ERR_array_sql:

    MsgBox Err.Description


See Also

OraParameters Collection

OraParameter Object


Properties

ArraySize

Get_Value

LastErrorText

MinimumSize

Name

Put_Value

ServerType

Status

Type


Methods

AutoBindDisable

AutoBindEnable


OraSession Object

See Also	Properties	Methods

Description

An OraSession object manages collections of OraDatabase, OraConnection, and OraDynaset objects used within an application.

Remarks

Typically, a single OraSession object is created for each application, but you can create named OraSession objects for shared use within and between applications.

The OraSession object is the top-most level object for an application. It is the only object created by the CreateObject VB/VBA API and not by an Oracle Objects for OLE method. The following code fragment shows how to create an OraSession object:

Dim OraSession as Object

Set OraSession = CreateObject("OracleInProcServer.XOraSession")


See Also

OraClient Object

OraConnection Object

OraDatabase Object

OraDynaset Object


Properties

Client

Connections

LastServerErr

LastServerErrText

Name

OIPVersionNumber

DbPoolCurrentSize

DbPoolInitialSize

DbPoolMaxSize


Methods

BeginTrans

CommitTrans

ConnectSession

CreateDatabasePool

CreateNamedSession

DestroyDatabasePool

GetDatabaseFromPool

OpenDatabase

LastServerErrReset

ResetTrans

Rollback


OraSQLStmt Object

See Also 	Properties 	Methods

Description

An OraSQLStmt Object represents a single SQL statement. Use the CreateSQL  method to create the OraSQLStmt object from an OraDatabase.

During create and refresh, OraSQLStmt objects automatically bind all relevant, enabled input parameters to the specified SQL statement, using the parameter names as placeholders in the SQL statement. This can improve the performance of SQL statement execution without reparsing the SQL statement.


See Also

CreateSQL Method

OraParameter Object


Properties

Connection

Database

Options

RecordCount

Session

SQL


Methods

Refresh

Close


OraConnections Collection

See Also	Properties

Description

The OraConnections collection maintains a list of OraConnection objects. The list is not modifiable; you cannot add to or remove from this collection.

Remarks

You can access the OraConnection objects in this collection by subscripting (using ordinal integers) or by using the name the object was given at its creation. You can obtain the number of OraConnection objects in the collection by using the Count property. Referencing a subscript that does not lie within the collection (0 to Count-1) results in the return of a NULL OraConnection object.


See Also

Count Property

OraConnection Object


Properties

Count


OraFields Collection

See Also	Properties

Description

The OraFields collection maintains a list of OraField objects. The list is not modifiable; you cannot add to or remove from this collection.

Remarks

You can access the OraField objects in this collection by subscripting (using ordinal integers) or by using the name the object was given at its creation. You can obtain the number of OraField objects in the collection by using the Count property. Referencing a subscript that does not lie within the collection (0 to Count-1) results in the return of a NULL OraField object.


See Also

OraField Object


Properties

Count


OraParameters Collection

See Also	Properties	Methods

Description

The OraParameters collection maintains a list of OraParameter objects. Unlike the other collection objects, this list is modifiable; you can add to and remove from the collection.

Remarks

You can access the OraParameter objects in this collection by subscripting (using ordinal integers) or by using the name the object was given at its creation. You can obtain the number of OraParameter objects in the collection by using the Count property. Referencing a subscript that does not lie within the collection (0 to Count-1) results in the return of a NULL OraParameter object.

In addition to accessing the OraParameter objects of the collection, you can use the collection to create and destroy parameters by using the Add and Remove methods, respectively.


See Also

OraParameter Object

OraParamArray


Properties

Count


Methods

Add

AddTable

Remove


OraSessions Collection

See Also	Properties

Description

The OraSessions collection maintains a list of OraSession objects. The list is not modifiable; you cannot add to or remove from this collection.

Remarks

You can access the OraSession objects in this collection by subscripting (using ordinal integers) or by using the name the object was given at its creation. You can obtain the number of OraSession objects in the collection by using the Count property. Referencing a subscript that does not lie within the collection (0 to Count-1) results in the return of a NULL OraSession object.


See Also

Count Property

OraSession Object


Properties

Count


DynasetOption

Applies To

OraParameter

Description

Specifies dynaset option for the dyanaset created from the PL/SQL cursor.

Usage

oraparameter.DynasetOption = dyn_opts

Remarks

This should be called before executing the PL/SQL procedure containing cursor variable. By default, the dynaset is created with ORADYN_READONLY option.

The possible values are

ORADYN_DEFAULT	&H0& - Accept the default behavior.

ORADYN_NO_BLANKSTRIP	&H2& - Do not strip trailing blanks from character string data retrieved from the database.

ORADYN_NOCACHE	&H8& - Do not create a local dynaset data cache. Without the local cache, previous rows within a dynaset are unavailable; however, increased performance results during retrieval of data from the database (move operations) and from the rows (field operations). Use this option in applications that make single passes through the rows of a dynaset for increased performance and decreased resource usage.

ORADYN_NO_MOVEFIRST	&H40& - Does not force a movefirst on dynaset creation. BOF and EOF are both TRUE.


Quick Tour

Employee Form 	Batch Insert Form

This quick tour is designed to get you started with Oracle Objects for OLE for Visual Basic.  We use an example employee database application to show you how to code basic database operations such as navigating through data, adding, modifying, and querying records.  A more advanced section is also included on how to perform batch inserts using parameter arrays and SQL statement objects.

The entire code for this example application is provided in the directory \OO4O2\SAMPLES\QT\.

This quick tour covers the topics below.  You can also learn how to code a particular function by clicking the button in the Employee Form window.

Introduction

About the Employee Database Application

Getting Started: Steps to Accessing Oracle Data

Functions

Navigating Through Data

Adding Records

Updating Records

Deleting Records

Querying the Database

Inserting Rows in Batch

Using the Connection Pool Management Facility


 Employee Form

To learn how to code a particular function, click on the button in the graphic below.


Batch Insert Form

To learn how to code a particular function, click on the button in the graphic below.


About the Employee Database Application

Quick Tour 	Employee Form   Batch Insert Form

Our example employee database application lets the user do the following:

•	Browse through data

•	Add records

•	Update records

•	Query the database

•	Add records in batch

To provide these functions, our user-interface has the following two forms:

•	Employee Form

•	Batch Insert Form


About the Employee Form

Quick Tour 	Employee Form

The Employee form displays the fields of database EMP table and has functional buttons to allow the user to browse, add, update and query records.   You can click on a button in the  Employee Form  window to learn how to code a particular function.

Each field corresponds to a column in the database EMP table.  The Employee field is the indexed column and is mandatory for each record.  The field datatypes and sizes are defined as follows in the EMP table:

 Name                            Null?    Type

 ------------------------------- -------- ----

 EMPNO                           NOT NULL NUMBER(4)

 ENAME                                    VARCHAR2(10)

 JOB                                      VARCHAR2(9)

 MGR                                      NUMBER(4)

 HIREDATE                                 DATE

 SAL                                      NUMBER(7,2)

 COMM                                     NUMBER(7,2)

 DEPTNO                          NOT NULL NUMBER(2)

The Employee Number (EMPNO) and Department (DEPTNO) columns are NOT NULL thus always require a value when a record is added.  We enforce the length of each field by setting the MaxLength property of each textbox to the appropriate number.


About the Batch Insert Form

Quick Tour 	Batch Insert Form

The Batch Insert Form allows user to enter records in batch.  To learn more about it, refer to the Inserting Rows in Batch section or click on a button in the Batch Insert Form window.


See Also

Other Quick Tour Topics

Getting Started: Steps to Accessing Oracle Data

Navigating Through Data

Adding Records

Updating Records

Deleting Records

Querying the Database

Inserting Rows in Batch


Getting Started: Steps to Accessing Oracle Data

See Also  	Quick Tour 	Employee Form

Before we can manipulate server data, our Visual Basic code must first complete the following steps, typically in the Form_Load() procedure:

1.  Start the Oracle in Process Server.

See Starting the Oracle In Process Server

2.  Connect to the Oracle database.

See Connecting to the Oracle Database

3.  Create a global OraDynaset object to manipulate data.

See Creating a Dynaset

4.  Refresh the Employee form with dynaset data.

See Refreshing Screen Data

In our example employee application, the Form_Load() procedure creates the OIP server, connects to the database, creates a global dynaset, and calls the function EmpRefresh to display the field values on the Employee form.  The code for Form_Load() looks like:

Private Sub Form_Load()

'OraSession and OraDatabase are global

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 Set OraDatabase = OraSession.OpenDatabase("Exampledb", "scott/tiger", 0&)

 Set EmpDynaset = OraDatabase.DbCreateDynaset(“select * from emp”, 0&)

 Call EmpRefresh

End Sub

The following variables are defined globally in EMP_QT.BAS:

Global OraSession As Object

Global OraDatabase As Object

Global EmpDynaset As Object


See Also

Reference

OpenDatabase Method

CreateDynaset Method

Other Quick Tour Topics

About the Employee Database Application

Navigating Through Data

Adding Records

Updating Records

Deleting Records

Querying the Database

Inserting Rows in Batch


Starting the Oracle In Process Server

See Also 	Quick Tour 	Employee Form

The Oracle In Process server (OIP) provides the interface between your Visual Basic application and the Oracle database.  To start the Oracle in process server, you must create an OraSession object using Visual Basic’s CreateObject() function as follows:

Set OraSession = CreateObject("OracleInProcServer.XOraSession")

When creating the OraSession Object, the argument you supply to the CreateObject() function must always be “OracleInProcServer.XOraSession” . The left hand side of the “.” defines the application name as registered in your system, in this case “OracleInProcServer”.  The part after the “.” identifies the type of object to create, in this case the XOraSession object.   The net effect of executing this command is starting the Oracle in Process Server.


See Also
Other Quick Tour Topics

Getting Started: Steps to Accessing Oracle Data

Connecting to the Oracle Database

Creating a Dynaset


Connecting to the Oracle Database

See Also 	Quick Tour 	Employee Form

Once the OIP server is running, you can connect to your local or remote Oracle database.  To do so, you must create the OraDatabase object as follows:

Set OraDatabase = OraSession.OpenDatabase("Exampledb", "scott/tiger", 0&)

You create an OraDatabase object using the OraSession.OpenDatabase() method.  You must specify the database name, the connect string, and a bit flag word representing the database mode. The default database mode applies when you specify 0&.  When Visual Basic executes this line, you have a connection to the specified database.


See Also
Reference

OpenDatabase Method

Other Quick Tour Topics

Getting Started: Steps to Accessing Oracle Data

Starting the Oracle In Process Server

Creating a Dynaset


Creating a Dynaset

See Also 	Quick Tour 	Employee Form

Oracle Objects for OLE lets you browse and update data using an object called a dynaset.  When you create the dynaset using the OraDatabase.DbCreateDynaset() method, you must specify a valid SQL select statement.   DbCreateDynaset() returns a pointer to the result of the SQL select statement.

In our employee application example, we must create global dynaset to which the rest of the program has access.  We can create a dynaset that selects all the rows from the “emp” table and assign the resulting dynaset to the global EmpDynaset variable as follows:

Set EmpDynaset = OraDatabase.DbCreateDynaset("select * from emp", 0&)

The 0& parameter is a bit flag option that specifies the default dynaset state.  In the default state,  Oracle Objects for OLE will set unset fields to NULL while adding records using DbAddNew.  We prefer this behavior since our emp table has no column defaults defined.  You can also specify other options to allow server column defaults when adding records.  See the CreateDynaset Method  for more details.

The code to connect to the server and to create a global dynaset is typically the first thing your Visual Basic application executes.  In our example employee application, the Form_Load() procedure creates the OIP server, connects to the database, creates a global dynaset, and calls the function EmpRefresh to display the field values on the Employee form.  The code for Form_Load() looks like:

Private Sub Form_Load()

'OraSession and OraDatabase are global

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 Set OraDatabase = OraSession.OpenDatabase("Exampledb", "scott/tiger", 0&)

 Set EmpDynaset = OraDatabase.DbCreateDynaset(“select * from emp”, 0&)

 Call EmpRefresh

End Sub

The following variables are defined globally in EMP_QT.BAS:

Global OraSession As Object

Global OraDatabase As Object

Global EmpDynaset As Object


See Also

Reference

OpenDatabase Method

CreateDynaset Method

Other Quick Tour Topics

Getting Started: Steps to Accessing Oracle Data

Starting the Oracle In Process Server

Connecting to the Oracle Database


Refreshing Screen Data

Quick Tour 	Employee Form

Our Employee form displays database records one row at a time.  Whenever we change the curent row, such as by navigating to a different row, we must reflect this change on the screen. The subroutine EmpRefresh()  updates our textboxes with the current dynaset row.  For NULL field values, we display the empty string.


EmpRefresh Subroutine

 Our EmpRefresh() subroutine looks like:

  Private Sub EmpRefresh()

 'check if the current dynaset row is valid

 If EmpDynaset.BOF <> True And EmpDynaset.EOF <> True Then

  txtEmpno = EmpDynaset.Fields("empno").Value

  ' we can't display nulls, so display ""  for NULL fields

  If Not IsNull(EmpDynaset.Fields("ename").Value) Then

    txtEname = EmpDynaset.Fields("ename").Value

    Else

    txtEname = ""

    End If

  If Not IsNull(EmpDynaset.Fields("job").Value) Then

    txtJob = EmpDynaset.Fields("job").Value

    Else

    txtJob = ""

    End If

  'check if mgr=nul

  If Not IsNull(EmpDynaset.Fields("mgr").Value) Then

   txtMgr = EmpDynaset.Fields("mgr").Value

   Else

   txtMgr = ""

   End If

  If Not IsNull(EmpDynaset.Fields("hiredate").Value) Then

    txtHireDate = EmpDynaset.Fields("hiredate").Value

  Else

    txtHireDate = ""

  End If

  If Not IsNull(EmpDynaset.Fields("hiredate").Value) Then

    txtSal = EmpDynaset.Fields("sal").Value

  Else

    txtSal = ""

    End If

  'check if comm=nul

  If Not IsNull(EmpDynaset.Fields("comm").Value) Then

   txtComm = EmpDynaset.Fields("comm").Value

  Else

   txtComm = ""

  End If

  txtDeptno = EmpDynaset.Fields("deptno").Value

'if the current dynaset row is invalid, display nothing

 Else

  txtEmpno = ""

  txtEname = ""

  txtJob = ""

  txtMgr = ""

  txtHireDate = ""

  txtSal = ""

  txtComm = ""

  txtDeptno = ""

 End If

End Sub


Navigating Through Data

See Also 	Quick Tour 	Employee Form

A typical requirement for a database application is to allow the user to view data in the database. Our example employee form has the following four buttons that let the user scroll through data:

Button	Function
|<	Moves to the first record
<	Moves to the previous record.
>	Moves to the next record.
>|	Moves to the last record.
To navigate through the records of the employee database, we first create a global dynaset that selects all the records (rows).  We then use one of the following dynaset move methods to code the navigation button:

Method	See...
DbMoveFirst	Moving to First and Last Rows
DbMovePrevious	Moving to the Previous Row
DbMoveNext	Moving to the Next Row
DbMoveLast	Moving to First and Last Rows


See Also

Reference

Move Methods

Other Quick Tour Topics

About the Employee Database Application

Getting Started: Steps to Accessing Oracle Data

Adding Records

Updating Records

Deleting Records

Querying the Database

Inserting Rows in Batch


Moving to First and Last Rows

See Also 	Quick Tour 	Employee Form

To move to the first row of a dyanset, we use the DbMoveFirst method. We then call EmpRefresh() routine to refresh the data in the Employee form.

In our employee example, the First click event procedure looks like:

Private Sub cmdFirst_Click()

 EmpDynaset.DbMoveFirst

 Call EmpRefresh

End Sub

To move to the last row, we use the DbMoveLast method. We then call EmpRefresh() routine to refresh the data in the Employee form.

In our employee example, the Last click event procedure looks like:

Private Sub cmdLast_Click()

 EmpDynaset.DbMoveLast

 Call EmpRefresh

End Sub


See Also
Reference

MoveFirst and MoveLast Methods

Other Quick Tour Topics

Moving to the Previous Row

Moving to the Next Row


Moving to the Previous Row

See Also 	Quick Tour 	Employee Form

We can position ourselves on any row of a dynaset. When positioned in the middle of a dynaset (current row is not the first row), we navigate to the previous row using DbMovePrevious.

However, when we are positioned on the first row (current row is the first row) and then execute DbMovePrevious, the beginning-of-file (BOF) condition becomes TRUE and the current row becomes invalid.  In this case, we reset the current row to the first row using DbMoveFirst.

The click event procedure for our Previous button looks like:

Private Sub cmdPrevious_Click()

  EmpDynaset.DbMovePrevious

  If EmpDynaset.BOF = True Then

   MsgBox WarnFirstEmp$

   EmpDynaset.DbMoveFirst

  End If

 Call EmpRefresh

End Sub


See Also
Reference

MovePrevious Method

Other Quick Tour Topics

Moving to the Next Row

Moving to First and Last Rows


Moving to the Next Row

See Also 	Quick Tour 	Employee Form

We can position ourselves on any row of a dynaset. When positioned in the middle of a dynaset (current row is not the last row), we navigate to the next row using DbMoveNext.

However, when we are positioned on the last row (current row is the last row) and then execute DbMoveNext, the end-of-file condition (EOF) becomes TRUE and current row becomes invalid.  In this case, we reset the current row using the DbMoveLast method.

The click event procedure for our Next button looks like:

Private Sub cmdNext_Click()

  EmpDynaset.DbMoveNext

  If EmpDynaset.EOF = True Then

   MsgBox WarnLastEmp$

   EmpDynaset.DbMoveLast

  End If

 Call EmpRefresh

End Sub


See Also

Reference

MoveNext Method

Other Quick Tour Topics

Moving to First and Last Rows

Moving to the Previous Row


Adding Records

See Also 	Quick Tour 	Employee Form

In our application, the following buttons allow users to add employee records to the database:

•	Add

•	Commit

To add a record, the user clicks on the Add button, enters the new fields in the text boxes, and clicks the Commit button to save the data to the database.

The code for the Add function is split between the event procedures for the Add and Commit buttons.  Refer to the following sections:

Coding the Add Button

Coding the Commit Button (Add)


See Also

Other Quick Tour Topics

About the Employee Database Application

Getting Started: Steps to Accessing Oracle Data

Navigating Through Data

Updating Records

Querying the Database

Deleting Records

Inserting Rows in Batch


Coding the Add Button

See Also 	Quick Tour 	Employee Form

In the Add event procedure, we perform the following steps:

1.	Blank out the fields on the form.

2.	Disable the Add button

3.	Enable the Commit button

4.	Let the user enter new field values

The Add event procedure looks like:

Private Sub AddNew_Click()

'Blank out the fields

  txtEmpno = ""

  txtEname = ""

  txtJob = ""

  txtMgr = ""

  txtHireDate = ""

  txtSal = ""

  txtComm = ""

  txtDeptno = ""

'Disable the Add button and enable the commit button

  AddNew.Enabled = False

  Commit.Enabled = True

End Sub

When AddNew_Click() exits, control returns to the Employee form where the user enters field values in the text boxes.


See Also

Other Quick Tour Topics

Coding the Commit Button (Add)


Coding the Commit Button (Add)

See Also 	Quick Tour 	Employee Form

To commit an add, we place the dynaset in add mode using DbAddNew.  We then assign the new data to the dynaset fields and update the database using DbUpdate.  To make our program robust, we validate some fields before attempting to add them to the database.

Our Commit_Click() event procedure for adding records does the following:

1.	Checks that at least the Employee Number and Department fields are not null.

2.	Checks that the new Employee Number is not a duplicate entry.

3.	Places the dynaset in add mode using DbAddNew.

4.	Assigns entered data to dynaset fields using the Fields().Value property.

5.	Updates database with new record, using DbUpdate.

6.	Disables the Commit button.

7.	Enables the Add button.

To check for duplicate entries in Step 2, we create a local dynaset with the NOCACHE option, using a SQL statement that counts the rows matching the entered Employee Number field.  If we get a match (row count greater than 0), the entered employee number is a duplicate entry and an error is displayed. In this case where the SQL select statement returns only a number, creating the dynaset without a cache is a more efficient error check than catching a duplicate entry error from the server.


Commit_Click() Procedure (Add)

 The Commit_Click() event procedure for adding records looks like:

Private Sub Commit_Click()

Dim DupDyn As Object

Dim DupDynQry As String

On Error GoTo err_commit

'Put all this into a function returning error code

'Check for NULLs

ErrMsg = ""

If txtEmpno = "" Then

    ErrMsg = "You must enter a value for Employee Number"

    Error 1

End If

If txtHireDate <> "" And Not IsDate(txtHireDate) Then

    ErrMsg = "Enter date as dd-mmm-yy."

    Error 2

End If

If txtDeptno = "" Then

    ErrMsg = "You must enter a value for Department Number"

    Error 3

End If

'Check for duplicate empno value by attempting to count rows with

'same value.

'Build Query

DupDynQry = "select count(*) from emp where empno = " & txtEmpno

Set DupDyn = OraDatabase.DbCreateDynaset(DupDynQry, ORADYN_NOCACHE)

If DupDyn.Fields(0).Value <> 0 Then

    ErrNum = DUPLICATE_KEY

    ErrMsg = "Empno already present!!!"

    Error ErrNum

End If

'Add the new record to dynaset

EmpDynaset.DbAddNew

EmpDynaset.Fields("empno").Value = txtEmpno

EmpDynaset.Fields("ename").Value = txtEname

EmpDynaset.Fields("job").Value = txtJob

EmpDynaset.Fields("mgr").Value = txtManager

EmpDynaset.Fields("hiredate").Value = txtHireDate

EmpDynaset.Fields("sal").Value = txtSal

EmpDynaset.Fields("comm").Value = txtComm

EmpDynaset.Fields("deptno").Value = txtDepno

'Update the database

EmpDynaset.Update

Commit.Enabled = False

AddNew.Enabled = True

Exit Sub

err_commit:

    If ErrMsg <> "" Then

        MsgBox ErrMsg

    Else

        MsgBox Error$

    End If

End Sub


See Also
Reference

AddNew Method

Update Method

Value Property

Other Quick Tour Topics

Coding the Add Button


Updating Records

See Also    	Quick Tour 	Employee Form

To allow users to update existing records in the database, we include an update button in our Employee form.  Users navigate to a particular record, click on the Update button, make changes, and then commit.

While in update mode, our application makes the following restrictions:

•	users cannot navigate to another record or perform another function

•	users cannot change the employee number since this is the primary key

To code the Update function, we write an event procedure for the Update button and modify our Commit procedure to handle both updating and adding records.  Refer to the following sections:

Coding the Update Button

Coding the Commit Button (Update and Add)


See Also
Other Quick Tour Topics

About the Employee Database Application

Getting Started: Steps to Accessing Oracle Data

Navigating Through Data

Adding Records

Deleting Records

Querying the Database

Inserting Rows in Batch


Coding the Update Button

See Also 	Quick Tour 	Employee Form

To code the Update button, we disable the Employee Number text box to disallow changes to this field while updating, since this is a primary key.  We also disable the other buttons to disallow other functions such as navigation while updating.

We set the DoUpdate boolean to TRUE, so the commit procedure knows we are updating and not adding a record.

The Update event procedure does the following:

1.	Disables Update button.

2.	Enables the Commit button.

3.	Disables other buttons to disallow  functions such as navigation during update.

4.	Disables the Employee Number text box.

5.	Sets the DoUpdate flag to true.

6.	Lets user enter changes.

The Update event procedure looks like:

Private Sub cmdUpdate_Click()

'Disable the Update button and enable the commit button

  cmdUpdate.Enabled = False

  Commit.Enabled = True

'Disable all other buttons

  DisableNavButtons

  txtEmpno.Enabled = False

  DoUpdate = True

End Sub

The Update and Add event procedures call the DisableNavButtons() subroutine to disable navigation and other functions during an add or update.

Private Sub DisableNavButtons()

'disable all buttons while adding and updating

cmdFirst.Enabled = False

cmdPrevious.Enabled = False

cmdNext.Enabled = False

cmdLast.Enabled = False

cmdFind.Enabled = False

cmdUpdate.Enabled = False

AddNew.Enabled = False

End Sub


See Also
Other Quick Tour Topics:

Coding the Commit Button (Update and Add)


Coding the Commit Button (Update and Add)

See Also 	Quick Tour 	Employee Form

The procedure for committing an update is similar for committing an Add, except that we place the dynaset in edit mode using DbEdit and then assign the new dynaset values.

Since we use the same commit button and the same commit event procedure to add and update, we introduce two global flags DoAdd and DoUpdate to distinguish between adding and updating.  The Add and Update click event procedures set these flags.

The Commit event procedure for adding and updating does the following:

1.	Validates entered data.  Handles errors when necessary.

2.	Uses DbAddNew to add records, else uses DbEdit for updates.

3.	Assigns entered data to dynaset fields, using the Fields().Value property.

4.	Updates database with new record, using DbUpdate.

5.	Disables the Commit button.

6.	Re-enables all other functional buttons including the Add and Update button.

7.	Sets the DoUpdate and DoAdd flags to false.

For clarity, we placed the data validation checks in Step 1 in a separate subroutine called DoValidationChecks().  We also moved the code that changes button and flag states in Steps 5 through 7 to a subroutine called SetAfterCommitFlags().

The code for the Commit function is broken into the following routines:

Commit_Click() Event Procedure

DoValidationChecks() Function

Update_Dynaset_Fields() Function

SetAfterCommitFlags() Subroutine


See Also

Reference

AddNew Method

Edit Method

Update Method

Other Quick Tour Topics

Coding the Update Button


  Commit_Click() Event Procedure

Private Sub Commit_Click()

Dim DupDyn As Object

Dim DupDynQry As String

On Error GoTo err_commit

ErrMsg = ""

'Do validation checks on entered data

If DoValidationChecks Then 'If validation checks have passed

    'If we are adding a record use DbAddNew

    If DoAdd = True Then

        EmpDynaset.DbAddNew

    End If

    'If we are updating a record use DbEdit

    If DoUpdate = True Then

       EmpDynaset.DbEdit

    End If

    'Update the dynaset fields and then update database if there is

    'no error.

    If UpdateDynasetFields Then

        EmpDynaset.Update

    End If

    SetAfterCommitFlags

End If 'Endif for DoValidationChecks

Exit Sub

err_commit:

    If ErrMsg <> "" Then

        MsgBox ErrMsg

    Else

        MsgBox Error$

    End If

End Sub


 DoValidationChecks() Function

The function DoValidationChecks() returns True if the entered data is valid; otherwise it returns False.

Function DoValidationChecks() As Boolean

Dim DupDyn As Object

Dim DupDynQry As String

On Error GoTo err_ValidationCheck

ErrMsg = ""

'Empno cannot be changed while in Update mode, so we can skip over validation

If DoAdd Then

    If txtEmpno = "" Then

        ErrMsg = "You must enter a value for Employee Number"

        Error 1

    End If

End If

If txtHireDate <> "" And Not IsDate(txtHireDate) Then

    ErrMsg = "Enter date as dd-mmm-yy."

    Error 2

End If

If txtDeptno = "" Then

    ErrMsg = "You must enter a value for Department Number"

    Error 3

End If

'If adding a record, check for Duplicate empno value by

'attempting to count rows with same value

'Build Query:

If DoAdd Then

    DupDynQry = "select count(*) from emp where empno = " & txtEmpno

    Set DupDyn = OraDatabase.DbCreateDynaset(DupDynQry, ORADYN_NOCACHE)

    If DupDyn.Fields(0).Value <> 0 Then

        ErrNum = DUPLICATE_KEY

        ErrMsg = "Employee Number already exists."

        Error ErrNum

    End If

End If

'Succesful validation with no errors returns True

DoValidationChecks = True

Exit Function

err_ValidationCheck:

    If ErrMsg <> "" Then

        MsgBox ErrMsg

    Else

        MsgBox Error$

    End If

    'Validation returns false on failure

    DoValidationChecks = False

End Function


Update_Dynaset_Fields() Function

The commit event procedure calls this function after putting the dynaset in either Edit or AddNew mode.  Update_Dynaset_Fields() sets the dynaset fields to the values entered in the text boxes.  The function returns TRUE if successful, FALSE on an error.

 Function UpdateDynasetFields() As Integer

'This function sets the dynaset field value to those

'entered in the text boxes.  The function returns true

'on success, false on error.

ErrMsg = ""

On Error GoTo err_updatedynasetfields

EmpDynaset.Fields("empno").Value = txtEmpno

EmpDynaset.Fields("ename").Value = txtEname

EmpDynaset.Fields("job").Value = txtJob

EmpDynaset.Fields("mgr").Value = txtManager

EmpDynaset.Fields("hiredate").Value = txtHireDate

EmpDynaset.Fields("sal").Value = txtSal

EmpDynaset.Fields("comm").Value = txtComm

EmpDynaset.Fields("deptno").Value = txtDeptno

UpdateDynasetFields = True

Exit Function

err_updatedynasetfields:

    If ErrMsg <> "" Then

        MsgBox ErrMsg

    Else

        MsgBox Error$

    End If

    UpdateDynasetFields = False


 SetAfterCommitFlags() Subroutine

The SetAfterCommitFlags() subroutine is called at the end of the commit event procedure.  SetAfterCommitFlags() re-enables disabled buttons and textboxes and sets the DoUpdate and DoAdd flags to false.

Sub SetAfterCommitFlags()

'disable commit and re-enable add and update buttons

Commit.Enabled = False

AddNew.Enabled = True

cmdUpdate.Enabled = True

'enable the other buttons

cmdFirst.Enabled = True

cmdPrevious.Enabled = True

cmdNext.Enabled = True

cmdLast.Enabled = True

cmdFind.Enabled = True

cmdUpdate.Enabled = True

AddNew.Enabled = True

DoUpdate = False

DoAdd = False

txtEmpno.Enabled = True

End Sub


Deleting Records

See Also 	Quick Tour 	Employee Form

Users can delete records by navigating to a particular record and clicking on the delete button.  Our application prompts the user to verify the deletion, then deletes the record using DbDelete.  The program then refreshes the screen with the next record or with the previous record if the user deleted the last record in the dynaset.

The delete click event procedure looks like:

Private Sub cmdDelete_Click()

'prompt user

Response = MsgBox("Do you really want to Delete?", vbYesNo + vbExclamation)

If Response = vbYes Then

    EmpDynaset.DbDelete

    'attempt to move to next record

    EmpDynaset.DbMoveNext

    If EmpDynaset.EOF Then 'If deleted last record

        EmpDynaset.DbMovePrevious

    End If

    Call EmpRefresh

End If

End Sub


See Also

Reference

Delete Method

Move Methods

Other Quick Tour Topics

About the Employee Database Application

Getting Started: Steps to Accessing Oracle Data

Navigating Through Data

Adding Records

Updating Records

Deleting Records

Inserting Rows in Batch


Querying the Database

See Also 	Quick Tour 	Employee Form

We can configure our application to allow users to search for particular records in the database.  For demonstration purposes, we include a Find button to allow users to query only employee names.  At any time, the user can enter the query in the Employee Name textbox and click on the Find button.  Our application then either displays the result, or displays a message if the name cannot be found.

To search for records, we use the DbFindFirst method.  When the find succeeds, we display the record.  When the find fails, we display a message.  We also reset the current row to the first, since failures cause the dynaset to be BOF, effectively making the current row invalid.

Our Find_Click() event procedure does the following:

1.	Builds a find clause to find the record where the ENAME column matches the entered string.

2.	Executes the find using DbFindFirst.

3.	Displays the record if found; if not found, displays a message and resets the current row to first.


Find_Click() Event Procedure

 Our Find click event procedure looks like:

Private Sub cmdFind_Click()

Dim FindClause As String

Dim SingleQuote As String

ErrMsg = ""

SingleQuote = "'"

On Error GoTo err_find

'build the find clause:

'make our query case insensitive by converting the names to upper case

FindClause = "UPPER(ename) = " & SingleQuote & UCase(txtEname) & SingleQuote

EmpDynaset.DbFindFirst FindClause

If EmpDynaset.NoMatch Then

    MsgBox "Could not find record matching Employee Name " & txtEname

    EmpDynaset.DbMoveFirst

End If

Call EmpRefresh

Exit Sub

err_find:

     If ErrMsg <> "" Then

        MsgBox ErrMsg

    Else

        MsgBox Error$

    End If

End Sub


See Also
Reference

FindFirst Method

Other Quick Tour Topics

About the Employee Database Application

Getting Started: Steps to Accessing Oracle Data

Navigating Through Data

Adding Records

Updating Records

Deleting Records

Inserting Rows in Batch


Inserting Rows in Batch

See Also  	Quick Tour 	Batch Insert Form

The Batch Insert Form allows users to insert rows in batch, that is, to insert more than one record into the database by using only one command.  We implement this functionality using parameter arrays and SQL statements.

Users navigate to the Batch Insert form by clicking on the Batch Insert button on the Employee form.  The Batch Insert form has a grid that displays the entered data and a row of text boxes where the user enters each record.  (We make the user enter records here because the Visual Basic grid does not allow user updates.)  To keep our example simple, we allow the users to enter only Employee Number, Employee Name, and Department Number fields.

The user enters a record in the text boxes and clicks on the “Add to Grid” button.  The program displays the entered records in the grid.  To insert the whole batch to the database, the user clicks on the “CommitGrid” button.

The batch insert form uses three procedures.  The Form_Load() procedure initializes the grid with the column headers. The CmdAddtoGrid_click() procedure copies the entered data from the text boxes to the grid.  The CommitGrid_Click() procedure contains the parameter array and SQL statement code used to make the batch insert.  Refer to the following sections or click on the button in the Batch Insert Form window:

Coding the Batch Insert Form_Load() Procedure

Coding the CmdAddtoGrid() Procedure

Coding the CommitGrid_Click() Procedure


See Also

Other Quick Tour Topics:

About the Employee Database Application

Getting Started: Steps to Accessing Oracle Data

Navigating Through Data

Adding Records

Updating Records

Deleting Records


Coding the Batch Insert Form_Load() Procedure

See Also 	Quick Tour 	Batch Insert Form

The Batch Insert Form_Load() procedure sets the column headings for the grid.  It looks like:

Private Sub Form_Load()

Grid1.Enabled = True

CurrRow = 0  'Top row

ReadRow = 0

ReadCol = 0

'Set column headings

Grid1.Row = CurrRow

Grid1.Col = 0

Grid1.Text = "Employee Number"

Grid1.Col = 1

Grid1.Text = "Employee Name"

Grid1.Col = 2

Grid1.Text = "Department Number"

NoOfCols = 3

CurrRow = CurrRow + 1

End Sub


See Also

Other Quick Tour Topics

Coding the CmdAddtoGrid() Procedure

Coding the CommitGrid_Click() Procedure


Coding the CmdAddtoGrid() Procedure

See Also 	Quick Tour 	Batch Insert Form

This procedure copies the data entered in the text boxes to the next empty grid row.  The global variable CurrRow always points to the first empty row in the grid.

The CmdAddtoGrid_Click() procedure looks like:

Private Sub CmdAddtoGrid_Click()

'Update the grid

'Update Empno column

Grid1.Row = CurrRow

Grid1.Col = 0

Grid1.Text = txtEmpno

'Update Ename column

Grid1.Row = CurrRow

Grid1.Col = 1

Grid1.Text = txtEname

'Update Deptno column

Grid1.Row = CurrRow

Grid1.Col = 2

Grid1.Text = txtDeptno

'Increment CurrCol

CurrRow = CurrRow + 1

NoOfRows = CurrRow - 1

End Sub


See Also

Other Quick Tour Topics

Coding the Batch Insert Form_Load() Procedure

Coding the CommitGrid_Click() Procedure


Coding the CommitGrid_Click() Procedure

See Also  	Quick Tour 	Batch Insert Form

The CommitGrid_Click() procedure inserts the grid data into the database.  To do so, this procedure creates a parameter array object for each column in the EMP table, which corresponds to a column in the grid.  We use the parameter AddTable() method to define each parameter array.  For example, we define a parameter array called “EMPNO_ARR” to hold all Employee Number column elements.

After defining the parameter arrays, we populate them with grid column elements using the Put_Value property.

To commit the parameter array elements to the database, this procedure uses the CreateSQL() method with a SQL INSERT statement containing the parameter arrays.  Since the CreateSQL() method executes the SQL INSERT statement in addition to creating a SQL statement object, all column elements (parameter array elements) are inserted into the EMP table with this one statement.

When an error occurs executing a SQL INSERT statement containing parameter arrays, the SQL statement object is still created with no explicitly raised error.  To identify such errors, we always check the OraDatabase.LastServerErr and OraDatabase.LastServerErrText properties immediately after executing CreateSQL.

The CreateSQL method updates the database directly and has no effect on the dynaset EmpDynaset.  We must refresh this dynaset to reflect the newly inserted records by executing DbRefresh.

The CommitGrid_Click()  event procedure does the following:

1.	Defines a parameter array for each grid (database) column, using AddTable.

2.	Copies grid column elements into parameter arrays, using the Put_Value property within a nested loop.

3.	Creates a SQL statement object using CreateSQL to insert parameter array elements into the EMP table.

4.	Checks the LastServerErrText and LastServerErr properties to catch SQL statement execution errors.

5.	Refreshes the global dyanset to reflect newly inserted records, using DbRefresh.


CommitGrid_Click() Procedure

The cmdCommitGrid_Click() procedure looks like:

Private Sub cmdCommitGrid_Click()

Dim OraSqlStmt As Object

Dim OraPArray(2) As Object

On Error GoTo err_CommitGrid

ErrMsg = ""

'Define parameter arrays, one for each column

OraDatabase.Parameters.AddTable "EMPNO_ARR", ORAPARM_INPUT, ORATYPE_NUMBER, NoOfRows

OraDatabase.Parameters.AddTable "ENAME_ARR", ORAPARM_INPUT, ORATYPE_VARCHAR2, NoOfRows, 10

OraDatabase.Parameters.AddTable "DEPTNO_ARR", ORAPARM_INPUT, ORATYPE_NUMBER, NoOfRows

If OraDatabase.LastServerErr <> 0 Or OraDatabase.LastServerErrText <> "" Then

  Error 1

End If

'Initialize local array to hold parameter arrays

Set OraPArray(0) = OraDatabase.Parameters("EMPNO_ARR")

Set OraPArray(1) = OraDatabase.Parameters("ENAME_ARR")

Set OraPArray(2) = OraDatabase.Parameters("DEPTNO_ARR")

'Init the param array variables.

'Add loop to read thru grid ROWS

For ReadRow = 0 To (NoOfRows - 1)

Grid1.Row = ReadRow + 1

'Loop to read thru grid CELLS

    For ReadCol = 0 To NoOfCols - 1

        Grid1.Col = ReadCol

        OraPArray(ReadCol).Put_Value Grid1.Text, ReadRow

    Next ReadCol

Next ReadRow

'create a sqlstmt to insert array values into table

Set OraSqlStmt = OraDatabase.CreateSql("insert into emp(empno,ename,deptno) values(:EMPNO_ARR,:ENAME_ARR,:DEPTNO_ARR)", 0&)

If OraDatabase.LastServerErr <> 0 Or OraDatabase.LastServerErrText <> "" Then

  ErrMsg = OraDatabase.LastServerErrText

  Error 1

End If

'Refresh the Dynaset

EmpDynaset.DbRefresh

Exit Sub

err_CommitGrid:

    If ErrMsg <> "" Then

        MsgBox ErrMsg

    Else

        MsgBox Error$

    End If

End Sub


See Also

Reference

AddTable Method

CreateSQL Method

Refresh Method

Put_Value Property

LastServerErr Property

LastServerErrText Property

Other Quick Tour Topics

Coding the Batch Insert Form_Load() Procedure

Coding the CmdAddtoGrid() Procedure


Using the Connection Pool Management Facility

Creating the Connection Pool

The Connection Pool in OO4O is a pool of OraDatabase objects. The pool is created by invoking the CreateDatabasePool method of the OraSession interface. An OraDatabase object represents a connection to an Oracle database and contains methods for executing SQL statements and PL/SQL blocks.

Obtaining and returning elements to and from the pool

To retrieve an OraDatabase object from the pool, the GetDatabaseFromPool method is called. This function returns a reference to an OraDatabase object.

Destroying the pool

The pool is implicitly destroyed if the parent session object that it belong to is destroyed. It can also be destroyed at any time by invoking the DestroyDatabasePool method.

Accessing the Pool attributes

The following are the Database Pool Properties. These properties are read-only.

DbPoolMaxSize	// Maximum Pool Size

DbPoolCurrentSize	// Current Size of the Pool

DbPoolInitialSize	// Initial Size of the Pool

Doing Transaction processing using the Database from the Connection Pool

This is the recommended way of doing transactions

set Odb = OraSession.GetDatabaseFromPool(0)

Odb.Connection.BeginTrans

Odb.Connection.CommitTrans