Embedded SQL Programming

--Embedded SQL programming

--SQL2005 Administrator Daquan

-An application that embeds an SQL statement is called a host program, and the language in which the program is written is called the host language.

Host language can be C, C + +, Java, and so on. Embedded SQL statements are syntactically similar to interactive SQL

--but Embedded SQL expands on individual statements

--There are two main types of embedded SQL statements: Executive SQL statements and descriptive SQL statements. Executive SQL statements can be used to

--defining data, querying, and manipulating data in a database, each execution statement actually operates on the database. Descriptive statements

--Used to describe the variables in the communication domain and SQL statements. Descriptive statement does not generate execution code

--Using Embedded SQL in the C language

--sqlserver provides support for the embedded language of C, embedded SQL for C (hereinafter referred to as ESQL/C)

Most of the grammar comes from TSQL syntax, but it is slightly different in some detail.

-In ESQL/C, both keywords and SQL statements are case-insensitive, but for cursors, connections, and dynamic SQL statements

--the definition is case-sensitive, for example, the two cursors defined below are completely different

DECLARE Red_cursor CURSOR

DECLARE Red_cursor CURSOR

--1, connecting to SQL Server

--in ESQL/C, connect to SQL Server with the Connect to statement, the syntax is as follows:

CONNECT to Servername.databasename as Connection_Name USER LOGIN. PASSWORD

--when there is only one connection, you can not specify the name of the connection, but if the application uses two or more connections

--You must specify a different name for each connection. When a connection is defined with connect to, all subsequent operations are

--On the current connection, if you want to use another connection, use Set connection to change the current connection, the syntax is as follows:

SET CONNECTION Connection_Name

--Close the connection to the data with disconnect, you can close the current connection or close all connections, the syntax is as follows

DISCONNECT Connection_Name | All | Current

--Example: Connection machine Red SQL Server database named Book and test, database user named SA

--The password is empty, then close them separately

EXEC SQL CONNECT to Red.book as Con1 USER sa;

EXEC SQL CONNECT to red.test as Con2 USER sa;

EXEC SQL DISCONNECT CON1

EXEC SQL DISCONNECT CON2

--2, using host variables

--host variables are defined as standard C variables in ESQL/C, using host variables to specify input parameters or receive output parameters in SQL statements

--The statement that defines the host variable is written between the Begin DECLARE section and end DECLARE section statement

EXEC SQL BEGIN DECLARE section

INT CompanyID

CHAR Companyname[30]

EXEC SQL End DECLARE Section

--When you use a host variable, you add a colon before the host variable as a flag to reference the host variable

"WHERE Companyid=:companid"

-Note: If the value passed from the database to the host variable is NULL, then the host variable converts the null value to 1 processing, for example: while giving

--Host variable address is given a null value, and the following SQL statement does not return a record with address null

EXEC SQL SELECT from company

WHERE address=:address