📄 manual.htm
字号:
<!--------------------------------------------------------------------------->
<!-- IGNORE THIS SECTION -->
<html>
<head>
<title>DB2 Access Classes - Manual</title>
<style>
BODY, P, TD { font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 10pt }
H2,H3,H4,H5 { color: #ff9900; font-weight: bold; }
H2 { font-size: 13pt; }
H3 { font-size: 12pt; }
H4 { font-size: 10pt; color: black; }
PRE { BACKGROUND-COLOR: #FBEDBB; FONT-FAMILY: "Courier New", Courier, mono; WHITE-SPACE: pre; }
CODE { COLOR: #990000; FONT-FAMILY: "Courier New", Courier, mono; }
</style>
<link rel="stylesheet" type=text/css href="http://www.codeproject.com/styles/global.css">
</head>
<body bgcolor="#FFFFFF" color=#000000>
<!--------------------------------------------------------------------------->
<!------------------------------- STEP 1 --------------------------->
<!-- Fill in the details (CodeProject will reformat this section for you) -->
<pre>
Title: DB2 Access Classes
Author: Jeff Lee
Email: jeffleef2@yahoo.com
Environment: VC++ 5.0-6.0, Windows, AIX
Keywords: SQL, DB2, DB2 CLI
Description: A set of classes to encapsulate DB2 CLI APIs in ADO style.
</pre>
<!------------------------------- STEP 2 --------------------------->
<!-- Include download and sample image information. -->
<!------------------------------- STEP 3 --------------------------->
<!-- Add the article text. Please use simple formatting (<h2>, <p> etc) -->
<ul>
<li><a href="#Overview">Overview</a>
<li><a href="#Environment">Environment</a>
<li><a href="#using">Using the code</a>
<ul>
<li><a href="#Connect">Connect to database and execute SQL statements</a>
<li><a href="#Execute">Execute parameterized SQL statements</a>
<li><a href="#Query">Perform a query</a>
<li><a href="#CallSP">Call a stored procedure</a>
<li><a href="#LOB">Access large object data</a>
<li><a href="#Error">Handle errors or exceptions</a>
</ul>
<li><a href="#CSqlDatabase">CSqlDatabase class</a>
<li><a href="#CSqlCommand">CSqlCommand class</a>
<li><a href="#CSqlRecordset">CSqlRecordset class</a>
<li><a href="#CSqlParameter">CSqlParameter class</a>
<li><a href="#CSqlField">CSqlField class</a>
<li><a href="#CSqlLocator">CSqlLocator class</a>
<li><a href="#CSqlErrorInfo">CSqlErrorInfo class</a>
</ul>
<h2><a name="Overview">Overview</a></h2>
<p>These classes encapsulate the complicated DB2 CLI APIs, so that developers can access DB2
database in a simply, ADO style way.
<ul>
<table>
<tr><td><a href="#CSqlDatabase">CSqlDatabase</td><td>Represents a connection to a database</a></td></tr>
<tr><td><a href="#CSqlCommand">CSqlCommand</a></td><td>Defines a specific command (SQL statement)</td></tr>
<tr><td><a href="#CSqlRecordset">CSqlRecordset</a></td><td>Represents a set of records selected from table(s) or the results of an executed command</td></tr>
<tr><td><a href="#CSqlParameter">CSqlParameter</a></td><td>Represents a parameter or argument associated with a SQL statement or stored procedure</td></tr>
<tr><td><a href="#CSqlField">CSqlField</a></td><td>Represents a column of data in a recordset</td></tr>
<tr><td><a href="#CSqlLocator">CSqlLocator</a></td><td>Manipulates a locator to access large object data</td></tr>
<tr><td><a href="#CSqlErrorInfo">CSqlErrorInfo</a></td><td>Provides information associated with a DB2 error</td></tr>
</table>
</ul>
<h2><a name="Environment">Environment</a></h2>
<p>These classes can be used in either Windows or AIX environment. The compilers use predefined
macro to identify themselves. for MSVC it's _MSC_VER; for xlC it's __xlC__.
<p>In the current version, I use STL (Standard Template Library) to manipulate string and collections.
Unfortunately the current version of AIX C++ compiler (xlC) doesn't support STL. So I used
STLport, a free, multiplatform ANSI STL library.
<p>STLport can be downloaded from:
<a target=_blank href=http://www.stlport.org>www.stlport.org</a>
<h2><a name="Using">Using the code</a></h2>
<p>To use DB2 Access Classes in your program, you need to add the 2 .cpp files and 4 .h files
in your project, and include sqldb2.h in your source file.
<p>While compile sqldb2.cpp or sqldata.cpp with VC++, you might meet the error:
fatal error C1010: unexpected end of file while looking for precompiled header directive.
To solve this problem, you should include the precompiled header file (usually it is stdafx.h) in
sqldb2.cpp and sqldata.cpp.
<h2><a name="#Connect">Connect to database and execute SQL statements</a></h2>
<p>The application must at first call the global function CSqlDatabase::Initialize to initialize
the DB2 CLI environment before accessing DB2, and call CSqlDatabase::Uninitialize to
release the environment afterwards.
<p>Use CSqlDatabase class to connect to a database; use CSqlCommand class to execute a SQL statement.
<pre>
#include <stdio.h>
#include "sqldb2.h"
void main()
{
// Step 1: initialize the DB2 CLI environment
CSqlDatabase::Initialize();
// Step 2: Establish a connection to database
CSqlDatabase db;
db.Connect("mydb", "user_name", "password");
// Step 3: Execute SQL statement
CSqlCommand cmd;
cmd.Create(&db, CSqlCommand::execDirect);
cmd.SetCommand("CREATE TABLE Employee (Name VARCHAR(20), Sex CHAR(1), EmployDate DATE, Age INT)");
cmd.Execute();
// Step 4: Execute another SQL statement
cmd.Reset();
cmd.SetCommand("INSERT INTO Employee VALUES ('John', 'M', '2000-1-1', 25)");
cmd.Execute();
// Step 5: Close database connection
db.Close();
// Step 6: Cleanup environment and release resources
CSqlDatabase::Uninitialize();
}
</pre>
<h2><a name="#Execute">Execute parameterized SQL statements</a></h2>
<p>Parameterized commands are more efficient if you need to execute a command repeatedly while only
a few parameters need be to changed.
<pre>
// Assume the database connection has been established
CSqlCommand cmd;
cmd.Create(&db);
cmd.SetCommand("INSERT INTO Employee VALUES (?, ?, ?, ?)");
// Add parameters to the command
CSqlParameter param;
param.CreateParameter(SQL_CHAR, SQL_PARAM_INPUT, 20);
cmd.Parameters().Add(param);
param.CreateParameter(SQL_CHAR, SQL_PARAM_INPUT, 1);
cmd.Parameters().Add(param);
param.CreateParameter(SQL_TYPE_DATE, SQL_PARAM_INPUT);
cmd.Parameters().Add(param);
param.CreateParameter(SQL_INTEGER, SQL_PARAM_INPUT);
cmd.Parameters().Add(param);
// Set the value of the parameters and execute the command
cmd.Parameters()[0] = "Mike";
cmd.Parameters()[1] = 'M';
cmd.Parameters()[2] = "2000-1-1";
cmd.Parameters()[3] = 25;
cmd.Execute();
cmd.Parameters()[0] = "Jane";
cmd.Parameters()[1] = 'F';
cmd.Parameters()[2] = "1999-10-20";
cmd.Parameters()[3] = 22;
cmd.Execute();
cmd.Close();
</pre>
<h2><a name="#Query">Perform a query</a></h2>
<p>CSqlCommand class provides a way to execute a SQL statement, but doesn't provide a way to
access the result set that might be generated by that statement. To perform a query we should
use CSqlRecordset class.
<pre>
// Assume the database connection has been established
// Create and initialize a CSqlRecordset object
CSqlRecordset rs;
rs.Create(&db);
// Specify the search condition of the query
rs.m_strFilter = "Age > ? AND Sex = ?";
CSqlParameter param;
param.CreateParameter(SQL_INTEGER, SQL_PARAM_INPUT);
rs.Parameters().Add(param);
param.CreateParameter(SQL_CHAR, SQL_PARAM_INPUT, 1);
rs.Parameters().Add(param);
// Search condition: Age > 20 AND Sex = 'M'
rs.Parameters()[0] = 20;
rs.Parameters()[1] = "M";
// Specify the return columns in the result set.
// If we don't specify the return columns, CSqlRecordset returns all columns as default.
CSqlField fd;
fd.CreateField("Name", SQL_C_CHAR, 20);
rs.Fields().Add(fd);
fd.CreateField("Age", SQL_C_LONG);
rs.Fields().Add(fd);
// Set the size of local row cache, by default the cache size is 1.
rs.SetCacheSize(10);
// Perform query
rs.Open("Employee", CSqlRecordset::sqlCmdTable);
rs.MoveNext();
while (!rs.IsEOF())
{
printf("Name:%s Age:%d\n", (char*)rs.Fields()[0], (int)rs.Fields()[1]);
rs.MoveNext();
}
// Specify another search condition: Age > 30 AND Sex = 'F'
rs.Parameters()[0] = 30;
rs.Parameters()[1] = "F";
// Perform query again
rs.Requery();
rs.MoveNext();
...
</pre>
<h2><a name="#CallSP">Call a stored procedure</a></h2>
<pre>
// Assume the database connection has been established
// Create and initialize a CSqlRecordset object
CSqlRecordset rs;
rs.Create(&db);
// Call AutoBindProcParams() to retrieve parameter information of the procedure from database
// and bind these parameters automatically
rs.AutoBindProcParams("ProcName", "SchemaName");
// Sepcify the value of input parameters
rs.Parameters()[0] = 1;
...
// Call the stored procedure
rs.Open("ProcName", CSqlRecordset::sqlCmdStoreProc, SQL_CURSOR_FORWARD_ONLY);
printf("Store procedure return code = %d\n", rs.GetRetStatus());
// Print the result sets if available
rs.MoveNext();
while (!rs.IsEOF())
{
for (int n=0; n<rs.GetFields(); n++)
{
string strVal;
rs.Fields()[n].ToString(strVal);
printf("%s ", strVal.data());
}
printf("\n");
rs.MoveNext();
}
rs.Close();
</pre>
<h2><a name="#LOB">Access large object data</a></h2>
<p>There are two way to access large object (BLOB, CLOB or DBCLOB) data:
<ul>
<li>Bind the LOB field (or parameter) to a file, by calling CSqlField::CreateFileField
(or CSqlParameter::CreateFileParam);
<li>Bind the LOB field to a locator, then use a CSqlLocator object to manipulate this locator.
</ul>
<pre>
// Assume the database connection has been established
CSqlRecordset rs;
rs.Create(&db);
// There are two LOB columns in the table. uses file binding for the column lob_col1
CSqlField fd;
fd.CreateFileField("lob_col1");
fd = "lob_col1.txt"; // Specify the file name to be bound
rs.Fields().Add(fd);
// Bind the column lob_col2 to a locator
fd.CreateField("lob_col2", SQL_C_CLOB_LOCATOR);
rs.Fields().Add(fd);
rs.Open("lob_table", CSqlRecordset::sqlCmdTable);
rs.MoveNext();
while (!rs.IsEOF())
{
// Open file lob_col1.txt to get value of lob_col1
FILE *pFile = fopen("lob_col1.txt", "r");
...
// Use CSqlLocator to get the value of lob_col2
CSqlLocator loc;
loc.Open(&db, rs.Fields()[1]); // Associate the locator to CSqlLocator object
int nSize = loc.GetLength(); // Get the length of the LOB data
char buf[100];
loc.GetSubString(buf, 40, 80); // Retrieve a substring from the LOB data
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -