API for Database access that I used:
- ODBC (Open Database Connectivity)
- ADO (ActiveX Data Objects)
- OCI (Oracle Call Interface)
- JDBC
ODBC
Open Database Connectivity is a widely accepted application programming interface (API) for database access. It is based on the Call-Level Interface (CLI) specifications from X/Open and ISO/IEC for database APIs and uses Structured Query Language (SQL) as its database access language.
The ODBC architecture has four components:
· Application. Performs processing and calls ODBC functions to submit SQL statements and retrieve results.
· Driver Manager. Loads and unloads drivers on behalf of an application. Processes ODBC function calls or passes them to a driver.
· Driver. Processes ODBC function calls, submits SQL requests to a specific data source, and returns results to the application. If necessary, the driver modifies an application's request so that the request conforms to syntax supported by the associated DBMS.
· Data source. Consists of the data the user wants to access and its associated operating system, DBMS, and network platform (if any) used to access the DBMS.
ADO
The ActiveX Data Objects (ADO)
ADO can be used with a variety of programming languages, including Visual C++, Visual Basic, VBScript, JScript...
The Connection Object The Connection object represents a connection to the data source. To execute any kind of command, you use the Execute method of the Connection object. If the command returns rows, a default Recordset object is created and returned. To specify a more complex Recordset, you create a new Recordset object, associate it with the Connection, and open the cursor.
The Command Object The Command object represents a command (also known as a query or statement) that can be processed by the data source. Commands can return rows or not, and if the provider is capable, can also handle parameters. The Command object is actually optional in the ADO model because some data providers cannot supply command execution, but the object is supported if the provider supports commands.
Commands can be simple SQL statements (or some other language the data provider recognizes) or calls to stored procedures in the database. Commands can then be executed using the Command's Execute method, or you can create a Recordset object and associate it with the Command object when opening the cursor.
The Parameter Object The Parameter object represents a parameter of a Command. As noted in the Command description, you can explicitly create Parameter objects and add them to the Parameters collection to avoid the often unnecessary and expensive task of going to the system catalog to automatically populate the parameter binding information.
The Recordset Object The Recordset object looks much like it does in existing models, a number of improvements have been made, such as removing unnecessary elements, adding optional arguments that reduce the number of lines of code for common scenarios, and changing defaults that didn't make sense in today's technologies.
The Field Object The Field interface represents a column in a Recordset that you can use to obtain values, modify values, and learn about column meta-data.
The Error Object The Error object represents an error returned from a data source.
The Oracle Call Interface (OCI)
The Oracle Call Interface (OCI) is an application programming interface (API) that allows you to create applications that use the native procedures or function calls of a third-generation language to access an Oracle database server and control all phases of SQL statement execution. OCI supports the datatypes, calling conventions, syntax, and semantics of a number of third-generation languages including C, C++, COBOL and FORTRAN.
Advantages of OCI 8
OCI provides significant advantages over other methods of accessing an Oracle database:
- more fine-grained control over all aspects of the application design.
- high degree of control over program execution.
- use of familiar 3GL programming techniques and application development tools such as browsers and debuggers.
- supports of dynamic SQL.
- availability on the broadest range of platforms of all the Oracle Programmatic Interfaces.
- dynamic bind and define using callbacks.
- describe functionality to expose layers of server metadata.
- asynchronous event notification for registered client applications.
- enhanced array data manipulation language (DML) capability for array INSERTs, UPDATEs, and DELETEs.
- ability to associate a commit request with an execute to reduce roundtrips.
- optimization for queries using transparent prefetch buffers to reduce roundtrips.
- thread safety; you do not have to use mutual exclusive locks (mutexes) on OCI handles.
