ODBC benefits

Good portability
Can access different databases at the same time
Share multiple data resources

ODBC overview

Causes of ODBC

Due to the existence of different database management systems, applications written under one relational database management system can not run under another relational database management system
Many applications need to share data resources from multiple departments and access different relational database management systems

ODBC

It is an integral part of the database in Microsoft's open services architecture (WOSA)
Provides a set of application programming interfaces (API s) for accessing databases

ODBC binding

Standardized application development
Standardizing the application interface of relational database management system

Overview of ODBC working principle

Architecture of ODBC application system

1. User applications
2.ODBC driver manager
3. Database driver
4. Data source

User application

What ODBC applications include
Request connection to database
Send SQL statement to data source
Allocate storage space for SQL statement execution results and define the read data format
Get database operation results or processing errors
Conduct data processing and submit processing results to users
The commit and rollback operations of the requested transaction
Disconnect from data source

ODBC driver manager

Driver Manager: used to manage various drivers
Included in ODBC 32 DLL
Manage communication between applications and drivers
Create, configure or delete data sources, and view the database ODBC drivers currently installed in the system
Main functions:
Loading ODBC drivers
Select and connect the correct driver
Manage data sources
Check the validity of ODBC call parameters
Record the call of ODBC function, etc

Database driver

ODBC provides the independence of application system and database platform through driver
ODBC applications cannot access the database directly
Its various operation requests are submitted by the driver manager to the ODBC driver of a relational database management system
Access the database by calling functions supported by the driver
The operation results of the database are also returned to the application through the driver
If an application wants to manipulate different databases, it must be dynamically linked to different drivers
ODBC driver type
Single beam
The data source and application are on the same machine
The driver directly completes the I/O operation to the data file
The driver is equivalent to a data manager
Multi beam
Support data access in network environments such as client server, client application server / database server
The driver completes the submission of database access request and the reception of result set
The application uses the result set management interface provided by the driver to manipulate the result data after execution

ODBC data source management

Data source: refers to the data that the end user needs to access, including database location, database type and other information. It is an abstraction of data connection
Data sources are transparent to end users
ODBC assigns a unique Data Source Name (DSN) to each accessed data source and maps it to all necessary low-level software used to access data
In the connection, the data source name is used to represent the user name, server name, connected database name, etc
End users do not need to know database management systems or other data management software, networks, and details about ODBC drivers
For example, suppose a school creates two databases on SQL Server and kingbases: school personnel database and teaching and scientific research database.
The school information system needs to access data from these two databases
In order to connect with the two databases conveniently, a data source named PERSON is created for the school personnel database, and a data source named EDU is created for the teaching and scientific research database
When you want to access each database, you only need to connect with PERSON and EDU. You don't need to remember the driver, server name and database name

ODBC API Foundation

Consistency of ODBC application programming interface

API consistency
Including core level, extension level 1 and extension level 2
Grammatical consistency
It includes minimum SQL syntax level, core SQL syntax level and extended SQL syntax level

Function overview

The ODBC 3.0 standard provides 76 function interfaces
Allocate and release environment handle, connection handle, statement handle
Connection function (SQLDriverconnect, etc.)
Information related functions (SQLGetinfo, sqlgetfusion, etc.)
Transaction functions (such as SQLEndTran)
Execute related functions (SQLExecdirect, SQLExecute, etc.)
For cataloging functions, ODBC 3.0 provides 11 cataloging functions, such as SQLTables, SQLColumn, etc. The application program can call the catalog function to obtain the information of the data dictionary, such as permission, table structure and so on
The functions and functions used in different versions of ODBC are different. Readers must pay attention to the version used. At present, the latest version is ODBC 3.8

Handle and its properties

A handle is a 32-bit integer value that represents a pointer
Handle classification in ODBC 3.0
Environment Handles
connection handle
Statement Handle
Descriptor handle
Relationship between application handles
Each ODBC application needs to establish an ODBC environment, allocate an environment handle, and access the global background of data, such as environment state, current environment state diagnosis, currently allocated connection handle on the environment, etc
An environment handle can establish multiple connection handles, and each connection handle realizes the connection with a data source

Multiple statement handles can be established in a connection. It is not only an SQL statement, but also the result set generated by the SQL statement and related information
In ODBC 3.0, the concept of descriptor sentence handle is proposed. It is a metadata collection that describes the parameters, result set and columns of SQL statements

data type

ODBC data type
SQL data type: used for data source
C data type: C code for application
Applications can obtain the support of different drivers for data types through SQLGetTypeInfo
Conversion rules between SQL data types and C data types

ODBC workflow

[example 8.11] back up the data of Student table in KingbaseES database to SQL Server database.

The application involves two different data sources in relational database management system
Using ODBC to develop applications, you can connect drivers of different relational database management systems and two data sources as long as you change the parameters of the connection function (SQLConnect) in the application

Before the application runs, Student relationship tables have been established in KingbaseES and SQL Server respectively

Action to be performed by the application
Execute SELECT * FROM Student on KingbaseES;
INSERT the obtained result set into the Student table of SQL Server by executing INSERT statement multiple times

Operation steps

1. Configure data source
2. Initialize the environment
3. Establish connection
4. Allocate statement handle
5. Execute SQL statement
6. Result set processing
7. Suspension of processing

Configure data sources

There are two ways to configure a data source
Run the data source management tool to configure
Use the ConfigDsn function provided by Driver Manager to add, modify, or delete data sources
In [example 8.12], the first method is used to create a data source. Because kingbases and SQL Server are used at the same time, two data sources are established respectively, named kingbases ODBC and SQL Server
[example 8.12] detailed process of creating data source

#include <stdlib.h>
#include <stdio.h>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <Sqltypes.h>
#define SNO_LEN 30
#define NAME_LEN 50
#define DEPART_LEN 100
#define SSEX_LEN 5

Create data source - step 1: define handles and variables

int main()
{	/* Step 1 Define handles and variables */
	/*Those starting with king represent variables that connect kingbases*/
	/*Variables starting with server represent variables connected to SQL server*/
	SQLHENV    kinghenv,serverhenv;        /*Environment Handles */
	SQLHDBC 	 kinghdbc,serverhdbc;         /*connection handle*/
	SQLHSTMT kinghstmt,serverhstmt;  	/*Statement Handle */
	SQLRETURN   ret;
	SQLCHAR  sName[NAME_LEN],sDepart[DEPART_LEN],
	sSex[SSEX_LEN],sSno[SNO_LEN];
	SQLINTEGER   sAge;
	SQLINTEGER  cbAge=0,cbSno=SQL_NTS,cbSex=SQL_NTS,
	cbName=SQL_NTS,cbDepart=SQL_NTS;

Initialize environment

It is not associated with a specific driver. It is controlled by the Driver Manager and configured with environment properties
After the application connects with a data source by calling the connection function, the Driver Manager calls the SQLAllocHandle in the connected driver to truly allocate the data structure of the environment handle
Create data source - step 2: initialize the environment

/* Step 2 Initialize environment */
ret=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE， 			          &kinghenv);
ret=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE， 			          &serverhenv);
ret=SQLSetEnvAttr(kinghenv,SQL_ATTR_ODBC_VERSION, 				        (void*)SQL_OV_ODBC3, 0);
ret=SQLSetEnvAttr(serverhenv,SQL_ATTR_ODBC_VERSION, 			        (void*)SQL_OV_ODBC3, 0);

Establish connection

The application calls SQLAllocHandle to allocate the connection handle and connect with the data source through SQLConnect, SQLDriverConnect or SQLBrowseConnect
The input parameters of SQLConnect connection function are:
Configured data source name
User ID
Password
In [example 8.12], kingbases ODBC is the data source name, SYSTEM is the user name, and MANAGER is the user password
Create data source - step 3: establish connection

/* Step 3 Establish connection */
ret=SQLAllocHandle(SQL_HANDLE_DBC, kinghenv, &kinghdbc);
ret=SQLAllocHandle(SQL_HANDLE_DBC, serverhenv,                          		          &serverhdbc);
ret=SQLConnect(kinghdbc,"KingbaseES ODBC", SQL_NTS,"SYSTEM",SQL_NTS, "MANAGER",SQL_NTS);
if (!SQL_SUCCEEDED(ret))	/*An error value is returned when the connection fails*/
	return -1;	
ret=SQLConnect(serverhdbc, "SQLServer", SQL_NTS, "sa",SQL_NTS,"sa",SQL_NTS);
if (!SQL_SUCCEEDED(ret) )	/*An error value is returned when the connection fails*/
	return -1;

Allocate statement handle

Before processing any SQL statement, the application also needs to allocate a statement handle first
The statement handle contains information such as the specific SQL statement and the output result set
Applications can also set statement properties through sqltstmattr (or use default values)
Create data source - step 4

/* Step 4 Initialize statement handle */
ret=SQLAllocHandle(SQL_HANDLE_STMT,kinghdbc,
				&kinghstmt);
ret=SQLSetStmtAttr(kinghstmt,
				SQL_ATTR_ROW_BIND_TYPE, 			 	 (SQLPOINTER)
          			 SQL_BIND_BY_COLUMN,			  	 	 SQL_IS_INTEGER);
ret=SQLAllocHandle(SQL_HANDLE_STMT,serverhdbc, 			&serverhstmt);

Execute SQL statement

There are two ways for applications to process SQL statements
Preprocessing (SQLPrepare and SQLExecute are applicable to multiple executions of statements)
Direct execution (SQLExecdirect)
If the SQL statement contains parameters, the application calls SQLBindParameter for each parameter and binds them to the application variable
The application program can dynamically change the specific execution of SQL statements in the program by directly changing the content of the application buffer
Application processing based on statement type
If there is a statement with a result set (select or catalog function), the result set is processed
For functions without result set, you can directly use the statement handle to continue executing new statements, or obtain the row count (the number of rows affected by this execution) and then continue executing
When inserting data, the precompiled method is adopted. First, SQL statements are preprocessed through SQLPrepare, and then each column is bound to the user buffer
Create data source - step 5: execute SQL statement

/* Step 5 There are two ways to execute statements */
/* Precompiled statements with parameters */
ret=SQLPrepare(serverhstmt,"INSERT INTO 				  STUDENT(SNO,SNAME,
              SSEX, SAGE,SDEPT) VALUES (?, ?, ?, ?, ?)", SQL_NTS);
if (ret==SQL_SUCCESS || ret==SQL_SUCCESS_WITH_INFO)
{
	ret=SQLBindParameter(serverhstmt,1,SQL_PARAM_INPUT,
    		SQL_C_CHAR,SQL_CHAR,SNO_LEN,0,sSno,0, &cbSno); 
	ret=SQLBindParameter(serverhstmt,2,SQL_PARAM_INPUT, 
    	SQL_C_CHAR,SQL_CHAR,NAME_LEN,0,sName,0,&cbName);
	ret=SQLBindParameter(serverhstmt,3,SQL_PARAM_INPUT,
     	SQL_C_CHAR,SQL_CHAR,2,0,sSex,0,&cbSex);
	ret=SQLBindParameter(serverhstmt,4,SQL_PARAM_INPUT,
     	SQL_C_LONG,SQL_INTEGER,0,0,&sAge,0,&cbAge);
ret=SQLBindParameter(serverhstmt,5,SQL_PARAM_INPUT,  
       SQL_C_CHAR,SQL_CHAR, DEPART_LEN, 0, sDepart,0,    
       &cbDepart);}
 /*Execute SQL statement*/
ret=SQLExecDirect(kinghstmt,"SELECT * FROM 				         STUDENT",SQL_NTS);
if (ret==SQL_SUCCESS || ret==SQL_SUCCESS_WITH_INFO) 
{
ret=SQLBindCol(kinghstmt,1,SQL_C_CHAR,sSno,
			SNO_LEN,&cbSno);
ret=SQLBindCol(kinghstmt,2,SQL_C_CHAR,sName,
			NAME_LEN,&cbName);
ret=SQLBindCol(kinghstmt,3,SQL_C_CHAR,sSex,
			SSEX_LEN,&cbSex);
ret=SQLBindCol(kinghstmt,4,SQL_C_LONG,&sAge,
			0,&cbAge);
ret=SQLBindCol(kinghstmt,5,SQL_C_CHAR,sDepart, 				DEPART_LEN,&cbDepart);
}

Result set processing

Applications can get the number of columns in the result set through SQLNumResultCols
Obtain the name, data type, precision and range of each column of the result set through SQL DescribeCol or SQLColAttrbute function
Using cursors to process result set data in ODBC
Cursor type in ODBC
Forward only cursor is the default cursor type of ODBC
Scroll able cursor
static
dynamic
Keyset driven
mixed
Result set processing steps
The opening method of ODBC cursor is different from that of embedded SQL. Instead of explicit declaration, the system automatically generates a cursor. When the result set is just generated, the cursor points to the front of the first row of data
The application binds the query results to the application buffer through SQLBindCol, and moves the cursor through SQLFetch or SQLFetchScroll to obtain each row of data in the result set
For special data types such as images, when one buffer is not enough to hold all data, it can be obtained multiple times through SQLGetdata
Finally, close the cursor through SQLClosecursor
Create data source - step 6: result set processing

/* Step 6 Process the result set and execute the precompiled statement*/
while ((ret=SQLFetch(kinghstmt))!=SQL_NO_DATA_FOUND) 
	{  
if(ret==SQL_ERROR)
		printf("Fetch error\n");
else  
     	ret=SQLExecute(serverhstmt);
}

Abort processing

Application abort step
Release statement handle
Release database connection
Disconnect from database server
Release ODBC environment
Create data source - step 7: abort processing

/* Step 7 Abort processing*/
SQLFreeHandle(SQL_HANDLE_STMT,kinghstmt);
SQLDisconnect(kinghdbc);
SQLFreeHandle(SQL_HANDLE_DBC,kinghdbc);
SQLFreeHandle(SQL_HANDLE_ENV,kinghenv);
SQLFreeHandle(SQL_HANDLE_STMT,serverhstmt);
SQLDisconnect(serverhdbc);
SQLFreeHandle(SQL_HANDLE_DBC,serverhdbc);
SQLFreeHandle(SQL_HANDLE_ENV,serverhenv);
return 0;
}

Welcome to join me for wechat communication and discussion (Please add notes on csdn)