There are also a lot of articles about OTL on the Internet, but it seems that it is only official documents. OTL is very easy to use. Combined with some official examples, you can try more to understand it. After about a month of project development, I have some knowledge about OTL. Here I will summarize it and hope it will help new users who have just started OTL. Some of them, such as the handling of network exceptions, took me two days to solve the problem. Where is my sweat. Start with the question:
I. Preparations before the start
Before using OTL for programming, you must first determine the Oralce version used and the selected character set. OTL supports almost all mainstream databases. You can use the database operation interfaces in otlv4.h through macro.
For example, if you use Oracle 11g R2 and select UTF8 as the character set, you can declare the following two macros before including otlv4.h:
# Define OTL_ORA11G_R2
# Define OTL_ORA_UTF8
# Include "otlv4.h"
Ii. Common classes and common members
1. otl_connect class
static int otl_initialize(const int threaded_mode=0):
The static function used to initialize the OTL environment. The parameter specifies whether it is used for multithreading or single thread. It does not guarantee thread security. That is to say, if multiple threads share the same otl_connect object, they need to be locked for control. A colleague used the default parameter 0 in a multi-threaded environment, causing program exceptions. However, in a single-threaded environment, parameter setting 1 is correct. Therefore, you can set this parameter to 1 directly.
This function has multiple versions. Please refer to the function declaration corresponding to the corresponding database version in the official documentation. The parameters used in 11g are described as follows:
Const char * connect_str: connection string in the format of "User Name/password @ Database Service name"
Const int aauto_commit: automatic submission mode. If this parameter is set to 0 (default), transactions executed through this connection object are not automatically committed. For example, when you use direct_exec to delete a record, you need to manually call the commit () member function to submit the transaction. If it is set to 1, the transactions enabled through this otl_connect object are automatically committed.
Long direct_exec (...):
Const char * sqlstm: Specifies the "static SQL statement" to be executed, that is, no input or output SQL statement is generated. For example, delete from book where name = 'C ++ '. However, statements such as select sysdate from dual or select * from book or delete from book where name =: f1 <char > cannot be executed because they contain input or output, this type of SQL statement can be implemented through otl_stream, which will be described below.
Int ignore_error: whether to ignore the exception. You can specify otl_exception: disable to disable exceptions. Otherwise, the program needs to use try... catch (otl_exception & e)... to capture and handle exceptions.
This member variable identifies whether the connection object is successfully connected. Once the connection is successful, its value is 1. Even if the network is disconnected, the value remains unchanged. After logoff (), the value changes to 0. Therefore, this variable can only be used to check whether the connection to the database is successful, rather than checking whether the connection to the database is normal.
2. otl_stream class
Associate a stream object with an SQL statement, which can be an SQL statement with input or output or a PL/SQL block.
Const int arr_size: Specifies the size of the stream buffer. When used as an output stream, if the number of records in the output buffer reaches this value, that is, when the buffer is full, the buffer is automatically refreshed. If Automatic submission is set, data is submitted together (default );
Const char * sqlstm: SQL statement, which can be used to specify binding variables, for example, delete from book where id =: f1 <int> and price =: f2 <int>;
Otl_connect & db: the database connection object used by the stream.
void set_commit(int auto_commit=0):
Sets whether to automatically submit transactions when a stream is refreshed. The following two conditions will be refreshed: a. The buffer is full; B. Manually call the flush member function.
Execute the SQL statement associated with the stream. For example:
DelStream. open (100, delete from book where id =: f1 <int> and price =: f2 <int>, dbConnect ); // deletion is performed only when 100 records are added to the stream under normal conditions.
DelSteam <'C ++ ';
DelStream. flush (); // immediately perform the delete operation, although there is only one record in the current stream
Obtain the number of records affected by the execution of SQL statements in the stream. For example, if 100 records are inserted, 100 is returned when this function is called.
Checks whether the stream object has been opened normally. If yes, 1 is returned. Note: If you reuse a stream object, you must first call the close function to close it, and then call open to reopen it.
Obtains the number of records in the buffer zone of the current stream object. The maximum value is the buffer size-1. The system will automatically refresh when the buffer is full. If you call this function after refresh, 0 is returned.
3. otl_exception class
Several Members of this class are used to indicate the exception information, such:
Char pai_text : SQL statement with an error;
Char var_info : If a bound variable that does not match the actual type is used in the stream, the value of this array is the information of the bound variable;
Unsigned char msg : I prefer to use it. This array displays specific exception information (including error codes returned by oracle), such as connection timeout.