Hubbry Logo
Embedded SQLEmbedded SQLMain
Open search
Embedded SQL
Community hub
Embedded SQL
logo
7 pages, 0 posts
0 subscribers
Be the first to start a discussion here.
Be the first to start a discussion here.
Contribute something
Embedded SQL
Embedded SQL
Embedded SQL
View on Wikipedia
from Wikipedia

Embedded SQL is a method of combining the computing power of a programming language and the database manipulation capabilities of SQL. Embedded SQL statements are SQL statements written inline with the program source code, of the host language. The embedded SQL statements are parsed by an embedded SQL preprocessor and replaced by host-language calls to a code library. The output from the preprocessor is then compiled by the host compiler. This allows programmers to embed SQL statements in programs written in any number of languages such as C/C++, COBOL and Fortran. This differs from SQL-derived programming languages that don't go through discrete preprocessors, such as PL/SQL and T-SQL.

The SQL standards committee defined the embedded SQL standard in two steps: a formalism called Module Language was defined, then the embedded SQL standard was derived from Module Language.[1] The SQL standard defines embedding of SQL as embedded SQL and the language in which SQL queries are embedded is referred to as the host language. A popular host language is C. Host language C and embedded SQL, for example, is called Pro*C in Oracle and Sybase database management systems, ESQL/C in Informix, and ECPG in the PostgreSQL database management system. SQL may also be embedded in languages like PHP etc.

The SQL standard SQL:2023 is available through purchase and contains chapter 21 Embedded SQL and its syntax rules.[2]

Database systems that support embedded SQL

[edit]

Altibase

[edit]
C/C++
APRE is an embedded SQL precompiler provided by Altibase Corp. for its DBMS server.

IBM Db2

[edit]

IBM Db2 for Linux, UNIX and Windows supports embedded SQL for C, C++, Java, COBOL, FORTRAN and REXX although support for FORTRAN and REXX has been deprecated.[3]

IBM Informix

[edit]

IBM Informix version 14.10 for Linux, Unix, and Windows supports embedded SQL for C. [4]}

Microsoft SQL Server

[edit]
C/C++
Embedded SQL for C has been deprecated as of Microsoft SQL Server 2008 although earlier versions of the product support it.[5]

Mimer SQL

[edit]

Mimer SQL for Linux, macOS, OpenVMS and Windows support embedded SQL.[6]

C/C++
Embedded SQL for C/C++ is supported on Linux, macOS, OpenVMS and Windows.
COBOL
Embedded SQL for COBOL is supported on OpenVMS.
Fortran
Embedded SQL for Fortran is supported on OpenVMS.

Oracle Database

[edit]
Ada
Pro*Ada was officially desupported by Oracle in version 7.3. Starting with Oracle8, Pro*Ada was replaced by SQL*Module but appears to have not been updated since.[7] SQL*Module is a module language that offers a different programming method from embedded SQL. SQL*Module supports the Ada83 language standard for Ada.
C/C++
Pro*C became Pro*C/C++ with Oracle8. Pro*C/C++ is currently supported as of Oracle Database 11g.
COBOL
Pro*COBOL is currently supported as of Oracle Database 11g.
Fortran
Pro*FORTRAN is no longer updated as of Oracle8 but Oracle will continue to issue patch releases as bugs are reported and corrected.[8]
Pascal
Pro*Pascal was not released with Oracle8.[8]
PL/I
Pro*PL/I was not released with Oracle8. The Pro*PL/I Supplement to the Oracle Precompilers Guide, however, continued to make appearances in the Oracle Documentation Library until release 11g. As of release 12c, the Pro*PL/I has been removed from the Oracle Documentation Library.[8]

PostgreSQL

[edit]
C/C++
ECPG is part of PostgreSQL since version 6.3.
COBOL
Cobol-IT is now distributing a COBOL precompiler for PostgreSQL[citation needed]
Micro Focus provides support via their OpenESQL preprocessor[citation needed]

SAP Sybase

[edit]

SAP Sybase ASE 15.7 supports embedded SQL for C and COBOL as part of the Software Developer Kit Sybase.[9]

SAP Sybase SQL Anywhere supports embedded SQL for C and C++ as part of the SQL Anywhere database management system SQL Anywhere.[10]

SAP Sybase IQ supports embedded SQL for C and C++ as part of the Sybase IQ database management system Sybase IQ.[11]

Embedded SQL through domain-specific languages

[edit]

See also

[edit]

References

[edit]
[edit]
Revisions and contributorsEdit on WikipediaRead on Wikipedia

Embedded SQL

View on Grokipedia
from Grokipedia
Embedded SQL is a standardized programming technique that allows SQL statements to be directly incorporated into the source code of a host programming language, such as C, COBOL, Fortran, PL/I, Ada, Pascal, or MUMPS, to enable applications to query and manipulate relational database data seamlessly within procedural logic. This approach contrasts with dynamic SQL, where statements are constructed and executed at runtime as strings, by permitting static SQL statements to be precompiled for optimized performance and type safety. First standardized in ANSI X3.168-1989 as Database Language - Embedded SQL, and later incorporated into ISO/IEC 9075 standards, with the current revision in ISO/IEC 9075-2:2023 specifying its syntax and semantics for embedding into these host languages while ensuring portability across compliant database systems. The process of developing Embedded SQL applications typically involves a precompiler that translates SQL statements into host language function calls, followed by compilation, linking, and binding to the database to create executable packages. For instance, in environments like , Pro*C/C++, or SAP SQL Anywhere, developers embed executable SQL statements—such as SELECT for querying, INSERT/UPDATE/DELETE for manipulation, and DDL for management—using delimiters like EXEC SQL, with host variables facilitating exchange between the program and database. Key features include support for cursors to handle multi-row results, indicator variables to manage NULL values and truncation, and both static (pre-known queries for efficiency) and dynamic (runtime-built statements for flexibility) variants, making it suitable for enterprise applications requiring robust database integration. Widely adopted in legacy and high-performance systems, Embedded SQL remains relevant in modern database management systems from vendors like , , and , where it provides a low-level, comprehensive interface equivalent to native APIs while adhering to ISO/ANSI portability standards. Although newer paradigms like object-relational mapping (ORM) tools have gained popularity for higher-level abstractions, Embedded SQL's direct control and optimization capabilities continue to serve critical applications in sectors such as , , and scientific .

Introduction

Definition and Core Concepts

Embedded SQL is a standardized method for integrating SQL statements directly into application programs written in host programming languages such as C, COBOL, Fortran, or Ada, enabling seamless database access within procedural code. This technique uses special directives to embed SQL constructs, which are then processed by a dedicated preprocessor to translate them into native host language calls and database API invocations, allowing the resulting code to be compiled and executed as part of the application. Unlike standalone SQL executed interactively or via command-line tools, embedded SQL facilitates compiled, performant database interactions embedded in business logic. At its core, embedded SQL emphasizes static SQL statements, which are fully specified and precompiled during the preprocessing stage, permitting the (DBMS) to optimize execution plans in advance for improved efficiency and reliability. This static approach contrasts with dynamic SQL, where statements are generated and prepared at runtime, providing greater flexibility for variable queries but incurring overhead from on-the-fly and optimization. The is essential in this paradigm, as it scans the source code, validates against the target DBMS , binds host variables, and generates intermediate code that bridges the SQL and host environments without altering the original program structure. Key components of embedded SQL include the EXEC SQL directive, which delimits SQL statements within the host code to signal the preprocessor; host variables, which serve as intermediaries for passing data values between the application and database queries; and declaration sections that define communication structures such as the SQL Communication Area (SQLCA), a common structure used in many implementations, which contains fields for execution status, error diagnostics, and information like SQLCODE. The ANSI/ISO SQL standards specify as the standardized mechanism for diagnostics to ensure robust data exchange and error management.

Purpose and Common Applications

Embedded SQL serves primarily to enable direct database access within application logic, allowing developers to embed SQL statements into host programming languages without relying on separate query tools or interfaces. This approach facilitates the combination of procedural code with declarative database operations, streamlining data manipulation in complex programs. By integrating SQL directly, it supports efficient handling of database interactions, such as querying and updating data, within the application's execution flow. A key motivation for using Embedded SQL is the achieved through pre-compilation of SQL statements, which reduces runtime overhead associated with query preparation and execution. This pre-compilation allows static SQL statements to be optimized and bound at , minimizing network communication and enabling faster execution in data-intensive scenarios. Additionally, it promotes tight integration for transaction-heavy applications, where consistent and reliable database operations are essential, by executing SQL statements on the from the host application, which enhances performance and ensures consistent database operations. Common applications of Embedded SQL include client-server architectures, where it supports access in distributed environments, and systems that handle large-scale data operations efficiently. In sectors like and , it is deployed in embedded systems requiring low-latency database interactions, such as secure in banking or . Historically, it has been prominent in mainframe environments for high-volume tasks. Use cases often involve stacks, including systems for bulk employee data updates and real-time reporting tools that generate dynamic insights from transactional data. Examples of host languages include and , which are commonly used for such integrations.

History and Evolution

Origins in Early Database Systems

Embedded SQL originated in the as part of IBM's research efforts to implement the of data, first proposed by E.F. Codd in his seminal 1970 paper. Codd's model emphasized declarative data manipulation over procedural navigation, aiming to overcome the complexities of early database systems like IBM's Information Management System (IMS), which relied on rigid hierarchical structures and call-level interfaces such as DL/I calls. At IBM's San Jose Research Laboratory, where Codd worked, a team initiated the System R project in 1974 to prototype a full management system (DBMS), evaluating concepts like data independence and high-level query languages. The System R project, spanning 1974 to 1979, introduced the initial concepts of embedding —originally named (Structured English Query Language)—directly into host programming languages to facilitate seamless integration for application developers. Designed by and , was intended as a non-procedural sublanguage for both queries and embedded use in languages like and , where SQL statements were prefixed with dollar signs ($) to distinguish them from host code and processed via a precompiler called XPREP. This embedding approach addressed the limitations of call-level interfaces in early DBMS, which required programmers to manage low-level navigation and data access manually, by allowing declarative SQL statements to be compiled into efficient runtime calls to the system's Relational Data Interface (RDI). System R's prototypes demonstrated improved concurrency and compared to IMS, using relational views and cursors to simplify repetitive transactions without fixed hierarchical paths. The concepts from System R paved the way for commercial adoption, with IBM's first commercial implementation of embedded SQL in SQL/DS, released in 1981 for VM/CMS and DOS/VSE. This was followed by widespread adoption in IBM's DB2 product, announced in 1983 and generally available in 1985 on the mainframe platform. Influenced directly by Codd's and System R's prototypes, DB2 marked a significant transition from hierarchical systems like IMS to relational DBMS, enabling easier data retrieval and maintenance for enterprise applications through embedded SQL in and other languages. This shift reduced the procedural burden on developers and supported broader adoption of relational principles in production environments.

Standardization and Key Milestones

The initial standardization of Embedded SQL occurred with the ANSI SQL-86 standard (ANSI X3.135-1986), which introduced annexes specifying embedded syntax for integrating SQL data manipulation statements into host languages such as , , PL/I, Pascal, and Ada. This was followed shortly by the ISO adoption as ISO/IEC 9075:1987, which mirrored the ANSI provisions for embedded SQL modules, establishing a foundational framework for embedding SQL within procedural programs to ensure database access portability. Subsequent milestones refined these foundations. The SQL-89 standard (ANSI X3.135-1989) provided minor updates to the embedded syntax, primarily clarifying ambiguities in host language bindings without major structural changes. A significant advancement came with (ANSI X3.135-1992, ISO/IEC 9075:1992; also known as FIPS 127-2), which introduced persistent SQL modules—named collections of SQL statements that could be invoked across sessions—enhancing reusability and integration in embedded contexts through Clause 19 on Embedded SQL. The SQL:1999 standard (ISO/IEC 9075:1999) further extended embedded capabilities by incorporating SQL/PSM (Persistent Stored Modules) as Part 4, enabling procedural constructs like routines and condition handling directly within embedded SQL for more robust application logic. Later, the SQL:2011 revision (ISO/IEC 9075:2011) improved by standardizing additional module language features and bindings, facilitating better cross-system portability for embedded applications. Over time, Embedded SQL standards evolved from language-specific annexes in early versions—often tailored to dominant host languages like —to a more modular, approach via the SQL Module Language, which derives embedded syntax and promotes broader portability across diverse programming environments. Subsequent revisions, including SQL:2016 (ISO/IEC 9075:2016) and SQL:2023 (ISO/IEC 9075-2:2023), continued to refine these features, updating syntax and semantics for embedding, such as enhanced , to support modern host languages and ensure ongoing portability. This shift addressed initial limitations in vendor interoperability, enabling developers to write more portable code without extensive rewrites for different host languages or database systems.

Technical Mechanisms

Preprocessing and Compilation Process

The preprocessing and compilation process for Embedded SQL involves transforming source code containing SQL statements embedded within a host language, such as C or COBOL, into executable application code that can interact with a database management system (DBMS). This process typically begins with a dedicated SQL preprocessor, which is a database-specific tool that analyzes the source file to identify and handle SQL constructs while preserving the host language structure. The preprocessor ensures that the resulting code is compilable by the host language compiler and linkable with DBMS client libraries, enabling runtime execution of SQL operations. The preprocessing steps are systematic and follow a defined sequence to maintain code integrity and correctness. First, the scans the source code for embedded SQL blocks, typically delimited by directives like EXEC SQL, extracting SQL statements and associated host variables. This scanning identifies all SQL-related elements, such as queries, declarations, and control structures, while copying non-SQL host code verbatim to the output file. For instance, in Pro*C, the preprocessor assumes a .pc file extension and processes the input specified by the INAME option. Similarly, Db2's precompiler examines the program for EXEC SQL statements, and PostgreSQL's ECPG tool targets .pgc files to locate these blocks. Second, the preprocessor validates the SQL syntax and, where possible, semantics against the database schema or predefined declarations. Basic syntax checking ensures statements conform to SQL standards, while semantic validation—often optional and requiring database connectivity or schema declarations—verifies elements like table and column references. In Db2, validation relies on DECLARE TABLE statements generated by tools like DCLGEN rather than live schema access, whereas ProC offers levels like SYNTAX, SEMANTICS, or FULL checking via the SQLCHECK option, with the latter connecting to the database for thorough verification. ECPG performs compatibility checks against PostgreSQL's SQL dialect during this phase. Errors or warnings, such as invalid references, are reported with prefixes like PCC- in ProC. Third, the preprocessor replaces the embedded SQL statements with equivalent host language constructs, primarily API function calls that interface with the DBMS client library. These replacements handle data exchange, such as binding host variables to SQL parameters, and generate runtime code for execution. For example, ProC substitutes SQL with calls to SQLLIB routines and defines structures for host variables, while ECPG converts statements into libpq function invocations. In Db2, SQL is modified into API calls that allow the host compiler to succeed, often producing a Database Request Module (DBRM) containing the extracted SQL for bind-time optimization. Additionally, the preprocessor generates or includes header files for key structures like the SQL Communication Area (SQLCA) and SQL Descriptor Area (SQLDA), which manage SQL execution status and dynamic data handling. The SQLCA, in particular, includes the SQLCODE field for error and status reporting post-execution, with macros like #define SQLCODE sqlca.sqlcode optionally defined in ProC when DEF_SQLCODE=YES is set. Following preprocessing, the output file—typically a .c file from a .pc or .pgc input—is compiled using the host language compiler, such as gcc for C programs. The compilation produces object code that is then linked with the DBMS client libraries (e.g., -lecpg for PostgreSQL or SQLLIB for Oracle) to form the final executable. In Informix ESQL/C, the esql command automates this by invoking the preprocessor, C compiler, and linker in sequence. Error handling during runtime relies on the SQLCA's SQLCODE, where values like 0 indicate success, negative numbers denote errors, and positive values signal warnings, allowing applications to respond appropriately to DBMS feedback. This integrated flow ensures that Embedded SQL programs are portable across supported host languages while adhering to DBMS-specific requirements.

Host Language Integration Methods

Embedded SQL integrates with host languages through structured declaration sections that define SQL objects and host variables, enabling seamless data exchange between the database and the application program. Declaration sections are delimited by directives such as EXEC SQL BEGIN DECLARE SECTION and EXEC SQL END DECLARE SECTION, within which host variables are declared and mapped to compatible SQL data types, such as SQLCHAR or SQLINTEGER for languages like C or COBOL. These sections ensure type-safe communication, with host variables referenced in SQL statements using a colon prefix (e.g., :host_var). For SQL objects like cursors, the DECLARE CURSOR statement associates a cursor name with a query expression, specifying attributes such as sensitivity (SENSITIVE, INSENSITIVE, or ASENSITIVE) and scrollability, which must precede any use of the cursor in the program. Organization of SQL code occurs via inclusion mode or module mode, each suited to different integration needs. In inclusion mode, SQL statements are directly embedded within the host language source code using EXEC SQL delimiters, allowing inline mixing of procedural logic and database operations for straightforward applications. Module mode, conversely, employs SQL-client modules—separate compilable units defined with an <SQL-client module definition> and a <language clause> specifying the host language—containing procedures invoked externally from the host program, which promotes modularity and reusability across applications. This approach uses an SQL-agent to manage communication between the module and the SQL-server during execution. Parameter binding in Embedded SQL supports both static and dynamic approaches to accommodate varying runtime requirements. Static binding involves precompiling fixed SQL statements with host variables or parameter markers (?), where the database optimizer determines access paths at bind time, ensuring efficient execution for repetitive queries without runtime overhead. Dynamic binding, executed via statements like PREPARE followed by EXECUTE or EXECUTE IMMEDIATE, constructs and optimizes SQL at runtime, using host variables for input parameters (IN mode) or output (OUT/INOUT modes), which is essential for queries with conditional logic or user-driven variations. Parameter modes are explicitly declared (e.g., IN, OUT, INOUT) or inferred as IN by default, with binding ensuring compatibility through locators for complex types like arrays or large objects. At runtime, Embedded SQL statements are executed within an SQL session established through connection handles managed by the SQL-agent or database-specific interfaces, facilitating interaction with the database server. Connection establishment occurs implicitly upon the first SQL statement or explicitly via CONNECT directives, with subsequent statements like OPEN for cursors or EXECUTE for prepared queries leveraging these handles to transmit data and retrieve results. Error and status checking relies on standardized mechanisms such as SQLSTATE codes—a five-character string where "00000" indicates success and others denote specific conditions like "22002" for truncation—accessible via host variables or the SQL Communications Area (SQLCA). Transaction support integrates via embedded COMMIT and ROLLBACK statements, which delineate atomic units of work, with optional savepoints for partial rollbacks and isolation levels (e.g., READ COMMITTED) set through SET TRANSACTION to control concurrency. Language-specific adaptations address and , particularly for host variables handling special SQL values. Indicator variables, declared alongside host variables (e.g., :var INDICATOR :ind), manage NULL values by setting the indicator to -1 upon retrieval of a NULL, preventing errors and allowing the host program to detect and handle unknown or appropriately; values of 0 confirm non-NULL, while positive integers may indicate truncation length. Memory allocation for these indicators follows host conventions—such as dynamic allocation in C or fixed in —ensuring sufficient space to avoid overflows during data transfer, with the precompiler generating calls to database APIs that respect these constraints. This mechanism extends to parameter styles (SQL or GENERAL), influencing how NULLs and indicators are passed to external routines.

Syntax and Programming Constructs

Basic Directives and Statement Embedding

Embedded SQL utilizes specific directives to integrate SQL statements into host language programs, enabling seamless execution within the context of procedural code. The primary directive, EXEC SQL, prefixes executable SQL statements such as Data Manipulation Language (DML) operations like INSERT and UPDATE, allowing them to be embedded directly in the host code. For instance, an INSERT statement might appear as EXEC SQL INSERT INTO employees (id, name) VALUES (1, 'John Doe');, which instructs the SQL precompiler to translate the SQL into host language function calls during preprocessing. This directive ensures that SQL code is distinguishable from native host language instructions, facilitating compilation by the database system's precompiler before standard host compilation. To manage communication between the database and the host program, the INCLUDE SQLCA directive incorporates the SQL Communications Area (SQLCA), a that captures execution status, codes, and diagnostic after each SQL statement. Declared as EXEC SQL INCLUDE SQLCA;, it populates fields like SQLCODE for success (0), warnings (>0), or (<0), enabling the program to check outcomes programmatically. This is essential for robust handling in embedded applications, as the SQLCA provides standardized feedback across compliant systems. Additionally, the WHENEVER directive specifies actions for SQL conditions, such as EXEC SQL WHENEVER SQLERROR CONTINUE; to proceed despite or EXEC SQL WHENEVER SQLERROR GOTO error_label; to branch to a handler, applying to all subsequent executable statements until redefined. These directives promote reliable program flow without constant manual checks after each statement. Embedding rules ensure compatibility between SQL and the host language, with all embedded SQL statements required to terminate with a semicolon (;) in languages like C, Ada, Pascal, and PL/I, though COBOL uses END-EXEC instead. To avoid syntax conflicts, only SQL-style comments (-- or /* */) are permitted within EXEC SQL blocks, preventing interference with host language comment delimiters, while the precompiler resolves any ambiguities. String literals in SQL statements use standard single or double quotes, but host variables for dynamic values are prefixed with a colon (e.g., :host_var), declared in a BEGIN DECLARE SECTION to distinguish them from SQL literals and avoid parsing issues with host string handling. Support for conditional compilation is achieved through host language mechanisms, such as #ifdef in C, allowing selective inclusion of SQL blocks based on build conditions, with the precompiler processing only active sections. Both Data Definition Language (DDL) and DML statements can be embedded using these rules, emphasizing static parameterization where values are bound at precompile time via host variables for performance and security. For DDL, an example is EXEC SQL CREATE TABLE departments (dept_id INTEGER PRIMARY KEY, name VARCHAR(50));, which defines schema elements directly in the program. DML embedding, such as EXEC SQL UPDATE products SET price = :new_price WHERE id = :prod_id;, leverages static host variable binding to parameterize queries, reducing injection risks compared to dynamic SQL; variable binding details are covered in subsequent sections on data handling. This approach supports portable, type-safe integration across standard host languages like C and COBOL.

Variable Handling and Cursor Management

In Embedded SQL, host variables enable the transfer of data between the host language application and the database server, serving as placeholders for input values in SQL statements or receptacles for output from queries. These variables are declared within a host language declaration section, such as EXEC SQL BEGIN DECLARE SECTION in C, and referenced in embedded SQL using a colon prefix to avoid confusion with SQL column names, for example, :emp_id in a SELECT statement. The data types of host variables must be compatible with corresponding SQL types, ensuring seamless binding during execution. To manage NULL values, which cannot be directly assigned to host variables, indicator variables are paired with host variables using the INDICATOR keyword in FETCH or INTO clauses. For instance, if a fetched value is NULL, the corresponding indicator variable is set to -1; a value of 0 indicates a non-NULL result, while negative values other than -1 signal errors like truncation. This mechanism, defined in the SQL standard, prevents unintended data corruption and allows applications to handle optional or missing database values explicitly. Cursors provide a mechanism for iterating over multi-row result sets in Embedded SQL, essential for operations beyond single-row queries. The standard sequence begins with the DECLARE CURSOR statement to define the cursor name and associated SELECT query, potentially incorporating host variables for dynamic filtering, followed by OPEN to execute the query and establish the result set, FETCH to retrieve rows into host variables, and CLOSE to release resources.

EXEC SQL DECLARE emp_cursor CURSOR FOR SELECT employee_id, name FROM employees WHERE department = :dept_id; EXEC SQL OPEN emp_cursor; while (1) { EXEC SQL FETCH emp_cursor INTO :emp_id, :emp_name; if (sqlca.sqlcode == 100) break; /* End of data */ /* Process row */ } EXEC SQL CLOSE emp_cursor;

EXEC SQL DECLARE emp_cursor CURSOR FOR SELECT employee_id, name FROM employees WHERE department = :dept_id; EXEC SQL OPEN emp_cursor; while (1) { EXEC SQL FETCH emp_cursor INTO :emp_id, :emp_name; if (sqlca.sqlcode == 100) break; /* End of data */ /* Process row */ } EXEC SQL CLOSE emp_cursor;

This forward-only navigation processes rows sequentially. In modern implementations compliant with SQL:1999 and later standards, scrollable cursors—declared with the SCROLL keyword—support bidirectional and positioned fetching, using directives like FETCH PRIOR, FETCH FIRST, FETCH ABSOLUTE n, or FETCH RELATIVE m to enable random access within the result set, improving flexibility for user interfaces or complex traversals. For dynamic SQL scenarios, parameter markers represented by a question mark (?) act as placeholders in PREPARE statements, allowing runtime substitution of values via host variables in subsequent EXECUTE statements without recompiling the application. This approach supports flexible queries where the structure is known but parameters vary, as in interactive applications. Additionally, array host variables extend scalar handling to bulk operations, permitting arrays of host variables in INSERT, UPDATE, DELETE, or FETCH statements to process multiple rows in a single execution, reducing round-trips and enhancing performance in data-intensive tasks.

EXEC SQL PREPARE stmt FROM 'INSERT INTO log_table (id, value) VALUES (?, ?)'; EXEC SQL EXECUTE stmt USING :log_id, :log_value;

EXEC SQL PREPARE stmt FROM 'INSERT INTO log_table (id, value) VALUES (?, ?)'; EXEC SQL EXECUTE stmt USING :log_id, :log_value;

For arrays, the host language declares multidimensional or one-dimensional arrays, and the SQL engine binds them element-wise up to the specified count, with indicator arrays handling NULLs per element.

Standards and Specifications

ANSI/ISO Core Standards for Embedded SQL

The ANSI/ISO core standards for Embedded SQL are outlined in the SQL:2023 specification, particularly through Part 2 (Foundation) in ISO/IEC 9075-2:2023, which establishes the foundational syntax and semantics for integrating SQL statements into host language programs. This part details the preprocessor's role in parsing embedded SQL directives, replacing them with equivalent host language constructs that invoke database operations, thereby enabling static compilation of SQL code. These elements ensure that Embedded SQL applications can achieve consistent behavior across compliant systems by standardizing how SQL interacts with the host environment. For Java host languages, ISO/IEC 9075-10:2023 defines similar embedding features known as SQLJ. A central requirement of these standards is mandatory support for key host languages, including C and COBOL, to facilitate broad portability in enterprise and legacy applications. For instance, the standards mandate precise mappings between SQL data types and host language variables, along with rules for parameter passing and indicator variables to handle nulls. The SQL Communications Area (SQLCA) is defined as a standardized structure comprising variables like SQLCODE and SQLSTATE for capturing diagnostics, error codes, and execution status after each SQL statement, allowing developers to implement robust error handling without vendor-specific extensions. Furthermore, rules for embedding persistent modules require that SQL modules be precompiled into the host program, supporting declarative sections for variable definitions and cursor management while prohibiting certain dynamic features in core compliance to maintain predictability. Compliance with these core standards is tiered to balance minimal functionality with advanced capabilities, with the Core level focusing on basic embedding of declarative SQL statements such as SELECT, INSERT, UPDATE, and DELETE, without procedural control flow. In contrast, the Enhanced level incorporates procedural SQL elements, like conditional logic and loops within embedded modules, building on the Foundation to support more complex applications. These levels provide interoperability guarantees, ensuring that applications conforming to a given level can execute portably across any ANSI/ISO-compliant DBMS, as verified through standardized conformance testing for preprocessor output and runtime behavior.

Vendor Extensions and Compliance Variations

Database vendors often extend the core ANSI/ISO Embedded SQL standards to integrate with their proprietary features, enhancing functionality for specific database types and host languages while introducing variations in compliance that can complicate portability. Oracle's ProC/C++ precompiler, for instance, adds support for Oracle-specific data types such as VARCHAR2, which includes a 2-byte length field and handles null termination via options like CHAR_MAP=VARCHAR2 or CHARZ, allowing seamless integration with Oracle's character varying semantics beyond standard SQL CHARACTER VARYING. Additionally, ProC incorporates optimizer hints embedded as comments in SQL statements (e.g., /*+ ALL_ROWS */), enabling developers to influence query execution plans in ways not specified in the ISO standards, and extends dynamic SQL with Method 4 using SQLDA descriptors for variable binding counts. These extensions improve performance in Oracle environments but deviate from strict ANSI compliance, as MODE=ORACLE permits omitting the DECLARE SECTION, flagging non-standard features with FIPS=YES. IBM Db2 provides robust Embedded SQL support for C and C++, including a template-based DECLARE SECTION for host variables and data structures, which facilitates type-safe integration without explicit C++ template mechanisms but allows C++ comments (//) in dynamic SQL statements. Db2 enforces EXEC SQL initializers and semicolons for all statements, with variations in comment handling—such as permitting SQL comments (--) only in static statements and C/C++ block comments (/* */) across both—potentially causing migration issues from other vendors where comment syntax is more flexible. Compliance adheres closely to SQL standards for host variable exchange but introduces platform-specific precompilation steps, like using db2 PRECOMPILE for .sqc files, which may require adjustments for error codes differing from ANSI SQLSTATE formats. Microsoft SQL Server supports Embedded SQL primarily through ODBC APIs, embedding SQL statements in C programs via precompilation steps that translate to function calls, as exemplified in simple SELECT-UPDATE loops using host variables. This approach integrates with OLE DB for broader connectivity in embedded scenarios, allowing data sources to be bound at runtime, but lacks a dedicated precompiler like Pro*C, leading to partial adherence where dynamic SQL relies on ODBC descriptors rather than full ISO module language constructs. Variations include ODBC-specific error handling via SQLSTATE and SQLCODE, which may not align perfectly with other vendors' cursor types or binding persistence, posing challenges for migrating applications that assume uniform directive syntax. PostgreSQL's ECPG preprocessor targets C and C++ exclusively, offering compliance with core SQL standards for cursors, transactions, and prepared statements through pgtypes library for data type handling, but omits support for COBOL or other legacy languages, limiting its use in mixed environments. Extensions include Informix and Oracle compatibility modes, adding non-standard types and functions like dynamic SQL with descriptor areas, which enhance portability from those systems but introduce variations in error reporting—such as ECPG-specific warnings for incomplete C++ construct recognition—that differ from ANSI error codes. These deviations can hinder migrations, as non-standard directives like compatibility flags require recompilation and testing for cursor management inconsistencies. SAP Sybase Adaptive Server Enterprise (ASE) extends Embedded SQL/C as a Transact-SQL superset, featuring persistent binding options (-p for inputs, -b for all) that retain cursor bindings across executions for performance gains, and scrollable cursors with insensitive scroll and absolute fetch, beyond basic ISO cursor semantics. For embedded analytics in SQL Anywhere (a Sybase variant), Watcom SQL dialect extensions allow function creation and array-based SELECT INTO for bulk data handling, supporting real-time querying in applications. Compliance issues arise from Transact-SQL exclusions like print statements and maximum 1024-column limits in SELECT, with variations in WHENEVER directive actions (e.g., call for custom handlers) that may conflict with standard CONTINUE or GOTO, complicating cross-vendor error code mapping and directive portability. Mimer SQL achieves full ISO compliance for Embedded SQL preprocessors per SQL-99 and later standards, supporting C/C++, COBOL, and Fortran with consistent data manipulation and transaction control across interactive and embedded modes. Vendor extensions include block fetching via SET SESSION FETCH SIZE for optimized real-time data retrieval and WITH HOLD cursors for persistent sessions in high-throughput systems, enhancing efficiency without altering core syntax. While variations in dynamic SQL processing are minimal, migration challenges stem from platform-specific preprocessors (e.g., Linux vs. VMS), where error codes align with SQLSTATE but require vendor tools for full descriptor support, contrasting with partial implementations elsewhere.
VendorKey ExtensionCompliance VariationExample Impact on Migration
Oracle Pro*CVARCHAR2 with length field; optimizer hintsPermits no DECLARE SECTION in ORACLE mode; flags non-ANSI with FIPSRequires mode switches and indicator variables for NULLs, differing from strict ANSI hosts
IBM Db2C++ comment support in dynamic SQL; DECLARE templateEXEC SQL mandatory; comment placement restrictionsAdjustments for semicolon terminators and SQL vs. C comment mixing
Microsoft SQL Server (ODBC)Runtime binding via descriptors; OLE DB integrationRelies on ODBC for dynamic SQL; no native precompilerPorting needs ODBC API wrappers, varying from precompiler-based error handling
PostgreSQL ECPGInformix/Oracle compat modes; pgtypes libraryC/C++ only; incomplete C++ recognitionLanguage limitations and mode flags add recompilation steps for legacy COBOL code
SAP Sybase ASE/SQL AnywherePersistent binding; scrollable cursors; Watcom functionsTransact-SQL limits (e.g., 1024 columns); WHENEVER call actionDialect-specific updates and binding options require precompiler flags for portability
Mimer SQLBlock fetching; multi-language preprocessorsFull SQL-99 adherence; platform-specific toolsMinimal syntax changes but vendor preprocessors needed for real-time cursor holds

Database System Support

Commercial Database Implementations

IBM Db2 provides robust support for through its precompiler, which processes source files containing embedded SQL statements in host languages such as C, COBOL, and C++, converting them into Db2 runtime API calls for seamless integration. The precompiler scans the program, extracts SQL statements and host variables, and generates a database request module (DBRM) while replacing the SQL with appropriate function calls. As of November 2025, the current release is Db2 version 12.1 for Linux, Unix, and Windows (with version 12.1.3 generally available since November 5, 2025), offering full compliance with SQL:2016 standards in its embedded SQL capabilities, with base support for version 11.5 extending until April 30, 2027. Oracle Database supports Embedded SQL primarily via ProC/C++ and ProCOBOL precompilers, enabling developers to embed SQL statements directly into C/C++ or COBOL programs, with additional integration of PL/SQL blocks treated as single embedded SQL units for enhanced procedural logic. These tools allow for the execution of DDL, DML, and transaction control statements within host applications, supporting object-oriented features like CREATE and DESTROY for server-side objects. In recent versions such as Oracle AI Database 26ai (released October 2025), Pro*C/C++ includes explicit request boundaries for embedded SQL to optimize workload distribution across database shards. Microsoft SQL Server supports SQL integration in host languages primarily through the ODBC API for dynamic SQL execution, rather than a dedicated Embedded SQL precompiler. This allows embedding SQL statements via API calls in C or .NET environments, with host compilation, linking, and connection steps preparing the program for execution against the server. This approach supports dynamic assembly of SQL statements at runtime, with examples demonstrating cursor management and error handling in C programs connected via ODBC drivers. As of SQL Server 2022, this API-based technique remains integral for applications requiring direct SQL integration without full ORM layers. SAP Adaptive Server Enterprise (ASE) offers Embedded SQL/C and Embedded SQL/COBOL, which enable the creation of programs that access and update ASE data by embedding SQL statements in C or COBOL source code, with direct mapping between ASE datatypes and host language equivalents. These implementations include support for dynamic SQL preparation and execution within multithreaded applications, though connections and cursors are not shared across threads by default. In ASE version 16.1 (released February 2025), these tools are part of the SDK, including Open Client libraries for robust connectivity, with version 16.0 reaching end of mainstream maintenance on December 31, 2025. Altibase, a hybrid in-memory database, optimizes embedded queries through its in-memory storage engine, which accelerates SQL execution in embedded contexts by minimizing disk I/O and leveraging RAM for high-speed data access in real-time applications. This approach ensures low-latency performance for embedded SQL statements, particularly in scenarios involving frequent reads and writes, as part of Altibase's unified handling of in-memory and on-disk data. Mimer SQL extends Embedded SQL with real-time capabilities, allowing applications to perform database operations with guaranteed response times while accessing data through standard SQL interfaces, including FETCH for row-by-row retrieval and SELECT INTO for single-row results. These extensions, available in Mimer SQL version 11.0 and later (with 11.0.9E released September 2025), integrate real-time data handling into embedded programs without requiring specialized APIs, supporting features like metadata access for SQL statements. IBM Informix supports Embedded SQL via ESQL/C, which accommodates both static and dynamic SQL statements, providing a fallback mechanism where dynamic SQL can be assembled and prepared at runtime if static embedding is insufficient for user-driven queries. This allows programs to build SQL strings interactively and execute them through PREPARE and EXECUTE statements, ensuring flexibility in embedded mode. In Informix version 15.0 (current as of November 2025), ESQL/C remains the primary tool for C-based embedded applications, with multithreading support for active connections per thread.

Open-Source and Embedded Database Support

Embedded SQL finds significant support in open-source database systems, enabling integration of SQL statements directly into application code for efficient data access in resource-constrained environments. , a leading open-source relational database, provides robust embedded SQL capabilities through its ECPG preprocessor, which converts C programs containing embedded SQL statements into standard C code for compilation and execution. This tool supports ANSI SQL standards and facilitates seamless database connectivity in C applications, with features like cursor management and error handling. Additionally, partial support for embedded SQL in is available via extensions, such as IBM COBOL's integration with PostgreSQL, where a coprocessor converts EXEC SQL statements to COBOL-compatible code using the SQL(PGSQL) compiler option. SQLite, a lightweight open-source embedded database engine, offers limited embedded SQL support primarily through its C API and amalgamation process, allowing developers to include the entire SQLite library (sqlite3.c and sqlite3.h) directly in C applications without a dedicated preprocessor. This approach enables SQL statements to be executed inline via function calls like sqlite3_exec, suitable for single-file embedding in mobile or desktop apps, though it lacks full preprocessor directives for host language integration and relies on macros for basic SQL-in-C constructs. For COBOL environments, third-party tools like GixSQL provide open-source preprocessing to access , extending embedded SQL functionality to legacy systems. InterSystems IRIS supports full embedded SQL within its ObjectScript programming environment and C applications, allowing SQL statements to be interspersed with host code and optimized at runtime for high-performance data processing. This includes dynamic execution of queries, cursor operations, and integration with IRIS's data platform for scalable applications, though its open-source components are limited to specific modules like iKnow NLP. Among other open-source options, MySQL provides embedding through its C API and connectors rather than a native preprocessor, enabling SQL execution in C/C++ programs via libraries like libmysqlclient for custom integrations. For Java-based applications, embedded databases like H2 and Apache Derby offer SQL support via JDBC drivers, allowing direct embedding in JVM environments without separate server processes; H2 emphasizes in-memory and disk-based modes for fast prototyping, while Derby focuses on standards-compliant relational features for enterprise Java apps. As of 2025, embedded SQL adoption is growing in IoT applications, driven by lightweight preprocessors and embedded databases like and H2 that minimize overhead in edge devices while supporting real-time data querying. This contrasts with commercial implementations, which often prioritize enterprise-scale features over the portability of open-source variants.

Usage in Specialized Languages

Integration with COBOL and Legacy Environments

Embedded SQL integration with COBOL has been a cornerstone of enterprise database access since the early days of relational databases, particularly in mainframe environments where COBOL dominates legacy applications. In COBOL programs, SQL statements are embedded using the EXEC SQL directive followed by the SQL code and terminated by END-EXEC, allowing seamless incorporation within the host language structure. These statements are typically placed in the PROCEDURE DIVISION for executable operations, while declarations such as DECLARE CURSOR or DECLARE TABLE can appear in the DATA DIVISION; host variables, defined in the WORKING-STORAGE SECTION using PIC clauses (e.g., PIC S9(4) COMP for integer types aligning with SQL INTEGER), facilitate data exchange between COBOL and the database by prefixing variable names with a colon (e.g., :HOST-VAR). Additionally, COPY statements in the DATA or PROCEDURE DIVISION include SQL-related copybooks, such as those for the SQL Communication Area (SQLCA), enabling standardized error handling and metadata access without duplicating definitions across programs. This embedding mechanism is especially prevalent in legacy banking and mainframe applications, where COBOL with Embedded SQL powers mission-critical systems on platforms like IBM z/OS using Db2. For instance, financial institutions rely on it for transaction processing, account management, and reporting, as it allows direct SQL queries within long-standing COBOL codebases that handle high-volume, secure data operations. A key legacy aspect involves migration challenges from flat-file systems like VSAM to relational databases via Embedded SQL; this process requires analyzing VSAM file structures from COBOL FILE SECTION or copybooks, unloading data to flat files, and reloading into Db2 tables, often complicated by JCL indirections, redefinitions in data layouts, and the need for repository building to resolve ambiguities in batch or CICS online applications. Such migrations demand careful handling of embedded SQL to preserve business logic while transitioning from sequential access to declarative queries, frequently necessitating manual interventions for complex data dependencies. Modern adaptations in COBOL, such as those in IBM Enterprise COBOL 6.5 released in June 2025, extend support for cloud integration while retaining for backward compatibility in legacy environments. Enhancements like COBOL/Java interoperability (via the JAVAIOP compiler option) and AMODE 64 addressing enable hybrid applications that connect mainframe COBOL programs with cloud services, allowing embedded SQL statements to interact with distributed databases without full rewrites. The integrated Db2 coprocessor further ensures compatibility by supporting existing EXEC SQL syntax in a single compilation step, including COPY and REPLACE directives, while adding features like dynamic SQL and improved host variable handling for Unicode and large datasets—facilitating gradual modernization of banking systems to cloud-native architectures without disrupting embedded SQL usage.

Applications in Other Domain-Specific Languages

Embedded SQL has been integrated into various domain-specific languages (DSLs) beyond legacy business-oriented ones, enabling database interactions in specialized environments such as scientific computing and real-time systems. In Fortran, a language predominant in numerical and scientific simulations, embedded SQL allows direct incorporation of database queries into computational workflows, facilitating data management for large-scale simulations without shifting to general-purpose APIs. For instance, Fortran applications in scientific domains leverage embedded SQL with systems like DB2 or Ingres to store and retrieve simulation results, maintaining the language's efficiency for high-performance computing tasks. In safety-critical and real-time systems, Ada historically supported embedded SQL through preprocessors like Oracle's ProAda (desupported since Oracle 7.3 in 1998), which translated SQL statements into native Ada calls, ensuring predictable behavior in embedded applications such as avionics or control systems. This integration preserved Ada's strong typing and runtime checks while providing database access for logging sensor data or querying configuration parameters in real-time environments. Historical efforts, including SQLModule for Ada, further standardized this embedding to support relational data handling in mission-critical software. PL/I, historically used in IBM mainframe environments for complex data processing, incorporates embedded SQL via DB2 preprocessors, allowing seamless database operations within its structured programming paradigm for enterprise simulations and reporting. Vendor tools like Micro Focus Open PL/I extend this to modern DB2 access, embedding SQL for batch processing in legacy scientific and financial models. In business-oriented DSLs like RPG on IBM i (formerly AS/400), embedded SQL is widely applied for transactional applications, where SQL statements are precompiled into RPG code to handle inventory, order processing, and reporting directly within the system's integrated environment. This approach simplifies data manipulation in midrange business apps, using dynamic SQL for flexible queries against native files. MUMPS (now often called M), used in healthcare and real-time systems, supports embedded SQL per standards in implementations like InterSystems Caché, which provides embedded SQL for ANSI-standard queries within MUMPS routines, facilitating database operations in legacy medical information systems. Overall, while embedded SQL in these DSLs persists in niches like scientific Fortran codes and RPG business systems due to legacy compatibility and performance needs, its adoption has declined in favor of modern APIs and ORMs, which offer greater portability across platforms. However, it remains relevant in specialized embedded controllers and real-time applications where tight integration and predictability are paramount.

Benefits and Challenges

Advantages in Performance and Security

Embedded SQL provides notable performance improvements primarily through its pre-compilation phase, where SQL statements embedded in host language code are translated into runtime library calls, eliminating the need for parsing and initial optimization at execution time. This process allows the database management system (DBMS) to generate and cache optimized execution plans during preprocessing, enabling faster subsequent executions without repeated compilation overhead. For applications involving repetitive queries, such as those in loops, this static SQL approach ensures consistent and efficient performance by reusing precomputed plans, reducing CPU and network latency compared to dynamic SQL alternatives. Studies on query pre-compilation in embedded environments have shown reductions in parse times by up to 90% and overall execution times by up to 50%, highlighting its efficacy for resource-constrained or high-volume scenarios. On the security front, embedded SQL's use of static, precompiled statements inherently prevents SQL injection vulnerabilities, as user inputs are bound as parameters rather than concatenated into query strings, ensuring that malicious code cannot alter the SQL structure. This parameterization aligns with established defenses like prepared statements, treating inputs as data rather than executable code, and integrates seamlessly with the DBMS's native authorization controls to enforce access privileges at the database level. Consequently, embedded SQL minimizes exposure to runtime injection risks prevalent in dynamic SQL, promoting a more secure interaction between application code and the database. Additional benefits include enhanced debugging capabilities through the SQL Communications Area (SQLCA), a structure that captures detailed runtime diagnostics such as error codes, row counts, and status flags after each SQL execution, facilitating traceability and issue resolution without extensive logging overhead. In multi-threaded applications, embedded SQL supports efficient resource management by allowing independent database contexts per thread, preventing contention and ensuring thread-safe access to shared database connections while optimizing memory and handle usage.

Limitations and Common Pitfalls

Embedded SQL, while effective for integrating database operations into host language applications, exhibits several key limitations that can impact its suitability for certain development scenarios. One primary constraint is its reduced flexibility compared to dynamic SQL, as embedded statements are statically defined at compile time and cannot be constructed or modified at runtime without recompilation. This makes it less ideal for ad-hoc queries or applications requiring variable query structures, where dynamic SQL allows for on-the-fly adjustments but incurs higher runtime overhead. Portability across different database management systems (DBMS) poses another significant challenge, stemming from vendor-specific extensions and variations in SQL dialect support, which often necessitate code modifications when migrating between platforms. Additionally, the reliance on a preprocessor to translate SQL into host language constructs introduces dependencies that can limit interoperability, as not all DBMS provide consistent embedded SQL bindings, and language-specific implementations (e.g., in Ada or C) may enforce restrictions like name length limits or scoping rules that hinder seamless porting. The preprocessor step also increases overall code complexity by generating intermediate code that mixes SQL logic with host language elements, potentially complicating maintenance and version control. Common pitfalls in embedded SQL implementations frequently arise from schema evolution and resource handling. Changes to database schemas, such as altering table structures or adding columns, can invalidate embedded queries, requiring full recompilation of the application to regenerate the executable with updated SQL bindings. This process disrupts development workflows and can lead to deployment delays, especially in large-scale systems where multiple modules depend on the same schema. Another frequent issue involves cursor management, where failing to properly close cursors after use can result in resource exhaustion, such as exceeding the maximum open cursors limit or causing persistent memory allocation that mimics leaks in long-running applications. In mixed SQL and host code environments, debugging presents further challenges, as errors may span both domains—e.g., SQLCODE values indicating database issues must be correlated with host language exceptions, often without integrated tools for stepping through the preprocessed code. This hybrid nature can obscure root causes, necessitating specialized diagnostics like SQLCA inspection or indicator variables to trace data mismatches or null handling failures. In modern development, these limitations and pitfalls have prompted a shift toward alternatives like object-relational mapping (ORM) frameworks or call-level APIs such as JDBC, which offer greater agility for dynamic queries and reduced vendor lock-in without the preprocessing overhead.

Implementation Examples

Basic Query Execution in C

Embedded SQL in C enables developers to integrate SQL queries directly into C programs, where SQL statements are embedded using the EXEC SQL directive and preprocessed into host language calls by a database-specific precompiler. This approach facilitates seamless data access within application logic, particularly for relational databases like IBM Db2. A basic query execution typically involves declaring host variables to exchange data between the C program and the database, defining a cursor for result set navigation, and handling operations like opening, fetching, and closing the cursor, with error checking via the SQL Communication Area (SQLCA). The following example demonstrates a simple SELECT query on a sample employee database table named EMPLOYEES, which contains columns emp_id (INTEGER) and name (VARCHAR(50)). The program connects to the database, declares a cursor to retrieve employee names for a specific ID, fetches the data into host variables, and outputs the result to the console. Host variables are prefixed with a colon (:) in SQL statements to indicate data transfer to C variables. The code assumes a pre-established connection to a Db2 database named "SAMPLE" and includes basic error handling using sqlca.sqlcode.

c

#include <stdio.h> #include <string.h> #include <sqlca.h> #include <sqlda.h> EXEC SQL BEGIN DECLARE SECTION; int emp_id; char name[50]; EXEC SQL END DECLARE SECTION; int main() { EXEC SQL INCLUDE SQLCA; /* Assume connection is established; in practice, use EXEC SQL CONNECT TO "SAMPLE"; */ EXEC SQL DECLARE emp_cursor CURSOR FOR SELECT emp_id, name FROM EMPLOYEES WHERE emp_id = :emp_id; emp_id = 123; /* Host variable assignment */ EXEC SQL OPEN emp_cursor; if (sqlca.sqlcode < 0) { printf("Error opening cursor: SQLCODE = %d\n", sqlca.sqlcode); return 1; } EXEC SQL FETCH emp_cursor INTO :emp_id, :name; if (sqlca.sqlcode == 0) { printf("Employee ID: %d, Name: %s\n", emp_id, name); } else if (sqlca.sqlcode == 100) { printf("No data found for emp_id %d\n", emp_id); } else { printf("Error fetching data: SQLCODE = %d\n", sqlca.sqlcode); EXEC SQL CLOSE emp_cursor; return 1; } EXEC SQL CLOSE emp_cursor; if (sqlca.sqlcode < 0) { printf("Error closing cursor: SQLCODE = %d\n", sqlca.sqlcode); } /* In practice, end with EXEC SQL COMMIT; and EXEC SQL CONNECT RESET; */ return 0; }

#include <stdio.h> #include <string.h> #include <sqlca.h> #include <sqlda.h> EXEC SQL BEGIN DECLARE SECTION; int emp_id; char name[50]; EXEC SQL END DECLARE SECTION; int main() { EXEC SQL INCLUDE SQLCA; /* Assume connection is established; in practice, use EXEC SQL CONNECT TO "SAMPLE"; */ EXEC SQL DECLARE emp_cursor CURSOR FOR SELECT emp_id, name FROM EMPLOYEES WHERE emp_id = :emp_id; emp_id = 123; /* Host variable assignment */ EXEC SQL OPEN emp_cursor; if (sqlca.sqlcode < 0) { printf("Error opening cursor: SQLCODE = %d\n", sqlca.sqlcode); return 1; } EXEC SQL FETCH emp_cursor INTO :emp_id, :name; if (sqlca.sqlcode == 0) { printf("Employee ID: %d, Name: %s\n", emp_id, name); } else if (sqlca.sqlcode == 100) { printf("No data found for emp_id %d\n", emp_id); } else { printf("Error fetching data: SQLCODE = %d\n", sqlca.sqlcode); EXEC SQL CLOSE emp_cursor; return 1; } EXEC SQL CLOSE emp_cursor; if (sqlca.sqlcode < 0) { printf("Error closing cursor: SQLCODE = %d\n", sqlca.sqlcode); } /* In practice, end with EXEC SQL COMMIT; and EXEC SQL CONNECT RESET; */ return 0; }

This code snippet illustrates the core flow of basic query execution: after declaring host variables in the BEGIN DECLARE SECTION, the cursor is declared with a static SQL SELECT statement that uses the host variable :emp_id in the WHERE clause. The cursor is then opened, which executes the query and prepares the result set; data is fetched into the host variables :emp_id and :name, with sqlca.sqlcode checked to handle success (0), no data (100), or errors (negative values). Finally, the cursor is closed to release resources. For multiple rows, a loop would enclose the FETCH statement until sqlca.sqlcode == 100. The cursor syntax follows the SQL standard for embedded interfaces, allowing row-by-row processing without loading the entire result set into memory. To compile and execute this program with IBM Db2, the source file (e.g., query.sqc) must first undergo precompilation using the Db2 precompiler, which replaces embedded SQL with C function calls and generates a bind file. The command db2 prep query.sqc bindfile processes the file, producing a modified query.c and a package for database binding. Subsequently, compile the C code with a standard compiler like cc -o query query.c -lsql (linking the Db2 libraries), bind the package using db2 bind query.bnd, and run the executable ./query. This two-step process—precompilation followed by host language compilation—ensures SQL statements are validated against the database schema during preparation. Error checking via sqlca.sqlcode is essential, as it provides diagnostic information after each SQL operation, with values like 0 indicating success and negative codes signaling failures such as invalid syntax or connection issues.

Advanced Transaction Handling

In Embedded SQL for C, advanced transaction handling extends basic query operations by providing explicit control over data consistency and recovery, particularly in multi-statement sequences that modify the database. Transactions implicitly begin with the first executable SQL statement after a connection or previous commit/rollback, allowing developers to group operations like inserts and updates into atomic units. To ensure durability, programmers use EXEC SQL COMMIT to persist changes or EXEC SQL ROLLBACK to undo them, while SAVEPOINT enables partial rollbacks for finer-grained error recovery within a transaction. Consider a sample banking database with an accounts table (columns: account_id, balance) and a transactions table (columns: txn_id, from_account, to_account, amount, timestamp). The following C snippet demonstrates a fund transfer: it updates the source account balance, inserts a transaction record, sets a savepoint before the destination update, and handles potential failures with rollback options. Variable binding for parameters, as introduced in basic query execution, is used here for account IDs and amounts. Assume a sequence txn_seq has been created for generating transaction IDs.

c

#include <stdio.h> #include <sqlca.h> /* For SQL communication area */ int main() { int from_id = 1001; int to_id = 1002; float amount = 500.0; /* Assume database connection established earlier */ EXEC SQL WHENEVER SQLERROR GOTO error_handler; /* Redirect errors to handler */ /* Implicit transaction start with first DML */ EXEC SQL UPDATE accounts SET balance = balance - :amount WHERE account_id = :from_id; if (sqlca.sqlcode != 0) { printf("Update source account failed: SQLSTATE %s\n", sqlca.sqlstate); EXEC SQL ROLLBACK; return 1; } EXEC SQL INSERT INTO transactions (txn_id, from_account, to_account, amount, [timestamp](/page/Timestamp)) VALUES (NEXT VALUE FOR txn_seq, :from_id, :to_id, :amount, CURRENT [TIMESTAMP](/page/Timestamp)); if (sqlca.sqlcode != 0) { [printf](/page/Printf)("Insert transaction failed: [SQLSTATE](/page/SQLSTATE) %s\n", sqlca.[sqlstate](/page/SQLSTATE)); EXEC SQL [ROLLBACK](/page/Rollback); return 1; } EXEC SQL SAVEPOINT before_dest_update; /* Mark point for partial [rollback](/page/Rollback) */ EXEC SQL UPDATE accounts SET balance = balance + :amount WHERE account_id = :to_id; if (sqlca.sqlcode != 0) { [printf](/page/Printf)("Update destination account failed: [SQLSTATE](/page/SQLSTATE) %s\n", sqlca.[sqlstate](/page/SQLSTATE)); EXEC SQL [ROLLBACK](/page/Rollback) TO SAVEPOINT before_dest_update; /* Undo only last update */ EXEC SQL COMMIT; /* Commit partial success */ return 1; } EXEC SQL COMMIT; /* Commit full transaction */ printf("Transfer completed successfully.\n"); return 0; error_handler: EXEC SQL ROLLBACK; /* Full rollback on unhandled error */ printf("Transaction rolled back due to error: SQLSTATE %s\n", sqlca.sqlstate); return 1; }

#include <stdio.h> #include <sqlca.h> /* For SQL communication area */ int main() { int from_id = 1001; int to_id = 1002; float amount = 500.0; /* Assume database connection established earlier */ EXEC SQL WHENEVER SQLERROR GOTO error_handler; /* Redirect errors to handler */ /* Implicit transaction start with first DML */ EXEC SQL UPDATE accounts SET balance = balance - :amount WHERE account_id = :from_id; if (sqlca.sqlcode != 0) { printf("Update source account failed: SQLSTATE %s\n", sqlca.sqlstate); EXEC SQL ROLLBACK; return 1; } EXEC SQL INSERT INTO transactions (txn_id, from_account, to_account, amount, [timestamp](/page/Timestamp)) VALUES (NEXT VALUE FOR txn_seq, :from_id, :to_id, :amount, CURRENT [TIMESTAMP](/page/Timestamp)); if (sqlca.sqlcode != 0) { [printf](/page/Printf)("Insert transaction failed: [SQLSTATE](/page/SQLSTATE) %s\n", sqlca.[sqlstate](/page/SQLSTATE)); EXEC SQL [ROLLBACK](/page/Rollback); return 1; } EXEC SQL SAVEPOINT before_dest_update; /* Mark point for partial [rollback](/page/Rollback) */ EXEC SQL UPDATE accounts SET balance = balance + :amount WHERE account_id = :to_id; if (sqlca.sqlcode != 0) { [printf](/page/Printf)("Update destination account failed: [SQLSTATE](/page/SQLSTATE) %s\n", sqlca.[sqlstate](/page/SQLSTATE)); EXEC SQL [ROLLBACK](/page/Rollback) TO SAVEPOINT before_dest_update; /* Undo only last update */ EXEC SQL COMMIT; /* Commit partial success */ return 1; } EXEC SQL COMMIT; /* Commit full transaction */ printf("Transfer completed successfully.\n"); return 0; error_handler: EXEC SQL ROLLBACK; /* Full rollback on unhandled error */ printf("Transaction rolled back due to error: SQLSTATE %s\n", sqlca.sqlstate); return 1; }

This example integrates host language exception handling via the WHENEVER SQLERROR GOTO directive, which redirects SQL errors to a labeled handler for custom recovery logic, such as checking SQLSTATE (a five-character code in the SQLCA structure) to identify failure types like constraint violations (e.g., '23000' for integrity errors). After each DML statement, the code inspects sqlca.sqlcode (0 for success, negative for errors) to trigger conditional rollbacks, ensuring atomicity even in partial failures. For concurrency, isolation levels can be set explicitly at transaction start using EXEC SQL SET TRANSACTION ISOLATION LEVEL READ COMMITTED;, which prevents dirty reads in multi-user environments like banking systems by controlling visibility of uncommitted changes from other sessions. Performance considerations in Embedded SQL transactions include disabling autocommit mode (default in implementations like , where each statement is not automatically committed) to batch multiple DML operations, reducing round-trips to the and improving throughput for high-volume applications. However, prolonged transactions without savepoints can increase lock contention and resource usage, so developers should minimize transaction scope and use savepoints judiciously to balance recovery flexibility with overhead.

References

  1. https://www.ibm.com/docs/en/db2/12.1.x?topic=da-embedded-sql
  2. https://sql-99.readthedocs.io/en/latest/chapters/01.html
  3. https://blog.ansi.org/ansi/sql-standard-iso-iec-9075-2023-ansi-x3-135/
  4. https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpcc/embedded-SQL.html
  5. https://help.sap.com/docs/SAP_SQL_Anywhere/98ad9ec940e2465695685d98e308dff5/3bd7ac5d6c5f10148137bc349a883855.html
  6. https://docs.oracle.com/cd/A57673_01/DOC/api/doc/PAD18/ch2.htm
  7. https://www.ibm.com/docs/en/db2-for-zos/12.0.0?topic=zos-embedded-sql-programming
  8. https://learn.microsoft.com/en-us/sql/odbc/reference/embedded-sql?view=sql-server-ver17
  9. https://learn.microsoft.com/en-us/sql/odbc/reference/static-sql?view=sql-server-ver17
  10. https://www.ibm.com/docs/en/db2/11.1.0?topic=tables-sqlca-sql-communications-area
  11. https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/SQL-Stored-Procedures-and-Embedded-SQL/Stored-Procedure-and-Embedded-SQL-Overview/Embedded-SQL-Overview
  12. https://www.eng.utah.edu/~cs5530/Lectures/sql-apis.pdf
  13. https://columbia.edu/sec/acis/db2/db2a0/db2a009.htm
  14. https://www.ibm.com/docs/en/i/7.5.0?topic=programming-common-concepts-rules-using-embedded-sql
  15. https://jcsites.juniata.edu/faculty/rhodes/dbms/embeddedsql.htm
  16. https://dl.acm.org/doi/10.1145/362384.362685
  17. https://www.cs.cmu.edu/~natassa/courses/15-721/papers/p97-astrahan.pdf
  18. https://www.ibm.com/history/relational-database
  19. https://dl.acm.org/doi/10.1145/800296.811515
  20. https://dl.acm.org/doi/10.1145/319540.319550
  21. https://www.techtarget.com/searchdatamanagement/definition/Db2
  22. https://nvlpubs.nist.gov/nistpubs/Legacy/FIPS/fipspub127.pdf
  23. https://learnsql.com/blog/history-of-sql-standards/
  24. https://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
  25. https://web.cecs.pdx.edu/~len/sql1999.pdf
  26. https://sigmodrecord.org/publications/sigmodRecord/1203/pdfs/10.industry.zemke.pdf
  27. https://www.jcc.com/index.php/resources/sql-standards
  28. https://www.iso.org/standard/76584.html
  29. https://docs.oracle.com/cd/A58617_01/server.804/a58233/precomp.htm
  30. https://www.ibm.com/docs/en/db2-for-zos/12.0.0?topic=preparation-processing-sql-statements-by-using-db2-precompiler
  31. https://www.postgresql.org/docs/current/ecpg-process.html
  32. https://webdocs.cs.ualberta.ca/~yuan/courses/db_readings/ansi-iso-9075-2-1999.pdf
  33. https://www.ibm.com/docs/en/db2-for-zos/13.0.0?topic=zos-embedded-sql-programming
  34. https://www.cs.rpi.edu/~sibel/csci4380/fall2022/course_notes/sql_procedural2.html
  35. https://sql-99.readthedocs.io/en/latest/chapters/39.html
  36. https://docs.oracle.com/database/121/LNPCB/pco05sql.htm
  37. https://www.ibm.com/docs/en/db2-as-a-service?topic=procedures-parameter-markers
  38. https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpcc/host-arrays.html
  39. https://www.iso.org/standard/84805.html
  40. https://docs.oracle.com/cd/B19306_01/appdev.102/b14407.pdf
  41. https://www.ibm.com/docs/en/db2/11.5.x?topic=language-embedded-sql-statements-in-c-c-applications
  42. https://www.ibm.com/docs/en/db2/11.1?topic=c-example-sql-declare-section-template
  43. https://learn.microsoft.com/en-us/sql/odbc/reference/embedded-sql-example?view=sql-server-ver17
  44. https://www.postgresql.org/docs/current/ecpg.html
  45. https://help.sap.com/doc/ea859e87667249dd87425a32bfe95f8e/16.0.4.5/en-US/Embedded_SQL-C_Programmers_Guide_en.pdf
  46. https://help.sap.com/docs/SAP_SQL_Anywhere/93079d4ba8e44920ae63ffb4def91f5b/3be640936c5f10149013d54460eaabb0.html
  47. https://docs.mimer.com/MimerSqlManual/latest/Manuals/Embedded_SQL/Embedded_SQL.htm
  48. https://www.ibm.com/docs/en/db2/12.1.0?topic=building-precompilation-precompile-command
  49. https://www.ibm.com/new/announcements/ibm-db2-product-annoucements-at-techxchange
  50. https://www.ibm.com/docs/en/db2/11.5.x?topic=database-db2-eos-dates
  51. https://www.ibm.com/docs/en/db2/11.5.x?topic=database-standards-compliance
  52. https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpcc/precompiler-concepts.html
  53. https://docs.oracle.com/en/database/oracle/oracle-database/21/adobj/pro-c-cpp.html
  54. https://docs.oracle.com/en/database/oracle/oracle-database/26/lnpcc/changes-c-c.html
  55. https://support.oracle.com/knowledge/Oracle%2520Database%2520Products/742060_1.html
  56. https://learn.microsoft.com/en-us/sql/odbc/reference/compiling-an-embedded-sql-program?view=sql-server-ver17
  57. https://learn.microsoft.com/en-us/sql/odbc/reference/structured-query-language-sql?view=sql-server-ver17
  58. https://help.sap.com/docs/SAP_ASE_SDK/88a9e3fab98a4042945742811ff78b57/b260cc9ebbf91014b2939d5a6bf461a6.html?&version=16.0.2.7
  59. https://help.sap.com/docs/SAP_ASE_SDK/882ef48c7e9c4d6e845d98f34378db40/b21b7c31bbf91014ae38f7f5cc782b0b.html
  60. https://www.cockroachlabs.com/blog/sybase-legacy-risk/
  61. https://altibase.com/en/product/features.php
  62. https://manual.altibase.com/7.3/en/admin/tuning/1.-Introduction-to-Performance-Tuning/
  63. https://docs.mimer.com/MimerTechnicalDescription/latest_mimertechdesc.pdf
  64. https://www.mimer.com/category/product/
  65. https://developer.mimer.com/download/mimer-sql-11-0-9e-windows-64-bit/
  66. https://www.ibm.com/docs/en/informix-servers/14.10?topic=programming-embed-sql-statements
  67. https://www.ibm.com/docs/en/informix-servers/12.10.0?topic=sql-assemble-prepare-statement
  68. https://www.ibm.com/docs/en/informix-servers/15.0.0?topic=programs-sql-in-sql-apis
  69. https://www.ibm.com/docs/en/cobol-linux-x86/1.2.0?topic=environments-programming-postgresql-environment
  70. https://github.com/mridoni/gixsql
  71. https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=GSQL_esql
  72. https://www.intersystems.com/news/intersystems-announces-enhanced-open-source-availability-for-intersystems-iknow-technology/
  73. https://www.h2database.com/html/features.html
  74. https://kestra.io/blogs/embedded-databases
  75. https://www.ibm.com/docs/en/db2-for-zos/12.0.0?topic=programming-cobol-applications-that-issue-sql-statements
  76. https://www.microfocus.com/documentation/net-express/nx50/sjcesc.htm
  77. https://kumaran.com/wp-content/uploads/2021/06/KS_COB_1.pdf
  78. https://www.ibm.com/docs/en/SS6SG3_6.5/pdf/mg.pdf
  79. https://www.ibm.com/docs/SSSNY3_10.1.0/com.ibm.db2.luw.apdv.embed.doc/doc/c0006166.html
  80. https://developer.mimer.com/article/embedded-sql-in-fortran/
  81. https://docs.actian.com/actianx/12.0/EmbedSQL/Embedded_SQL_for_Fortran.htm
  82. https://docs.oracle.com/cd/A58617_01/server.804/a58243/migo7app.htm
  83. https://docs.oracle.com/cd/A91034_01/DOC/appdev.901/a58231.pdf
  84. https://www.ibm.com/docs/en/i/7.4.0?topic=programming-embedded-sql
  85. https://www.microfocus.com/documentation/openpli/80/pusqlo.htm
  86. https://www.ibm.com/docs/ssw_ibm_i_74/rzajp/rzajpkickoff.htm
  87. https://try-as400.pocnet.net/wiki/Embedding_SQL_in_RPG
  88. https://www.quora.com/Does-anyone-still-actively-write-Embedded-SQL
  89. https://dl.acm.org/doi/pdf/10.1145/24645.24648
  90. https://docs.oracle.com/cd/E18283_01/appdev.112/e10825/pc_01int.htm
  91. https://cmps-people.ok.ubc.ca/rlawrenc/research/Papers/precompiler_sac2014.pdf
  92. https://community.intersystems.com/post/avoiding-sql-injection-intersystems-iris-case-secure-query-practices
  93. https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html
  94. https://www.techtarget.com/searchdatamanagement/feature/Static-SQL-vs-dynamic-SQL-for-database-application-performance
  95. https://www.ibm.com/docs/en/db2/11.1.0?topic=designing-concurrent-transactions-multi-threaded-database-access
  96. https://dl.acm.org/doi/pdf/10.1145/143557.143982
  97. https://www.ibm.com/docs/en/ssw_ibm_i_74/pdf/rzajppdf.pdf
  98. https://www.jhkinfotech.com/blog/what-is-a-database-cursor
  99. https://www.ibm.com/docs/en/db2/11.5?topic=language-embedded-sql-statements-in-c-c-applications
  100. https://www.ibm.com/docs/en/db2/11.5?topic=programming-application-template-in-c
  101. https://www.ibm.com/docs/en/db2/11.5?topic=commands-precompile
  102. https://www.ibm.com/docs/en/db2/11.5?topic=language-embedded-sql-statements-c-c-applications
  103. https://www.ibm.com/docs/en/db2/11.5?topic=sqlca-sqlca
Add your contribution
Related Hubs
Contribute something
User Avatar
No comments yet.