Oracle Database

RDO and the RemoteData control can help Us meet a specific set of client/server requirements. Some of there uses are:

• Gain high-performance data access against remote ODBC data sources. The ability to quickly retrieve the results from complex queries is a goal of every data access application. RDO provides a level of performance rivaled only by the ODBC and VBSQL API programming models. By leveraging the remote data engine, RDO greatly improves response time and user productivity.
• Manage return codes and both input and output parameters from stored procedures. Output parameters are the only way to extract information from an Oracle stored procedure, and are used heavily for singleton queries and many administrative functions. In many cases, you cannot determine if a stored procedure completed successfully without accessing the procedure's return value. RDO supports access to each of these parameters through the rdoParameter object.
• Manage multiple result sets. By using a single query that returns several sets of results, you can use the query processor and system resources more efficiently. You can improve performance by running a single query to gather data to fill multiple data-driven list boxes and menus. In addition, by combining a row-count query with a SELECT query, you can accurately set up scroll bars and progress status bars.
• Submit multiple action queries in a single batch. In many cases, your application can submit a set of INSERT, DELETE or UPDATE operations as a single SQL statement. This increases performance, as it reduces network and remote processing overhead and lets you manage transactions more easily.
• Limit the number of returned or processed rows. In situations where users might select more rows than it is practical to handle, RDO implements a query governor to limit the number of rows returned from any data source. This way, you can predict query response time and more easily manage the workstation or server resources required to maintain cursor keysets. Using the same mechanism, you can limit the number of rows affected by a data-modification query.
• Utilize server-side cursors. Some servers, such as Microsoft SQL Server, support cursor keysets that are created on the server, instead of on the workstation. Under the right conditions, this type of cursor management can significantly improve performance and reduce network load and workstation resource requirements.
• Execute queries asynchronously. If a query takes an extended period of time to run, you should have the option of either executing code while the query is being processed or canceling the query. RDO provides an asynchronous query option that you can use when executing any query, as well as a way to cancel an asynchronous query. RDO also provides a unique event-driven asynchronous programming interface that eliminates the need for polling loops. Asynchronous processing extends to opening connections and when using the MoveLast method.
• Continue queries after initial timeout period. When a query exhausts the period set in the QueryTimeout property, RDO permits you to override query cancellation and continue waiting for another timeout period.
• Support improved polymorphism and "free-standing" object creation. RDO now supports creation of rdoConnection and rdoQuery objects using the Dim statement. These free-standing objects can be associated with other objects to perform actions. For example, you can create an rdoQuery object independent of any rdoConnection object and associate it with an open connection at a later time.
• Support dissociated result sets. RDO permits you to create a static read-write cursor and break the connection with the remote server. The data in the rdoResultset object remains available for access or changes. Once you re-associate the result set with another rdoConnection object, you can use the BatchUpdate method to post these offline changes to the database.
• Create and manage optimistic batch updates. While the ODBC cursor library supports the concept of optimistic updates, it does so on a row by row basis rather than a batch basis. This type of update operation consumes considerable network and server bandwidth. RDO leverages the new Client Batch cursor library that groups sets of rows to be inserted, updated, or deleted. This way, only one round trip to the server is needed, thus improving update performance and decreasing network traffic.
• Make the use of stored procedures easier. RDO permits you to express parameterized queries and stored procedures as methods of a parent rdoConnection object. You can pass parameters just as you would to any Visual Basic function without having to manipulate any rdoParameter objects.
• Expose underlying ODBC handles. In cases where you need more flexibility or control than is available in the object model, you should have a way to directly access the data source. RDO provides access to the ODBC environment, connection, and statement handles.
• Reduce memory footprint. In many cases, the system that hosts a client/server front-end application is limited in RAM capacity. Because of this, it is important that applications designed for these systems economize their use of RAM and other workstation system resources. The RDO memory footprint is dramatically smaller than other programming models, and it does not require the use of local memory or disk space for its lowest-level cursors.

Ten common software packages that provide DBMS functionality include:

1. Ingres
2. Microsoft Access
3. MongoDB
4. MS-SQL Server
5. MS FoxPro
6. MySQL
7. NoSQL
8. Oracle
9. Postgres
10. SQLite

A DBMS, or database Management System, is a collection of programs that enables you to store, modify, and extract information from a database.

Rollback:

This is used for undoing the work done in the current transaction. This command also releases the locks if any hold by the current transaction. The command used in SQL for this is simply:

ROLLBACK;

Savepoint:

This is used for identifying a point in the transaction to which a programmer can later roll back. That is it is possible for the programmer to divide a big transaction into subsections each having a savepoint defined in it. The command used in SQL for this is simply:

SAVEPOINT savepointname;

For example:

UPDATE…..

DELETE….

SAVEPOINT e1;

INSERT….

UPDATE….

SAVEPOINT e2;

……

It is also possible to define savepoint and rollback together so that programmer can achieve rollback of part o a transaction. Say for instance in the above

ROLLBACK TO SAVEPOINT e2;

This results in the rollback of all statements after savepoint e2

Commit:

This is used to end the transaction and make the changes permanent. When commit is performed all save points are erased and transaction locks are released. In other words commit ends a transaction and marks the beginning of a new transaction. The command used in SQL for this is simply:

COMMIT;