Embedded SQL

Last updated

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 [1] 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.

Contents

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. [2] 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. [3]

Database systems that support embedded SQL

Altibase

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

IBM Db2

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. [4]

IBM Informix

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

Microsoft SQL Server

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

Mimer SQL

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

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

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. [8] 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. [9]
Pascal
Pro*Pascal was not released with Oracle8. [9]
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. [9]

PostgreSQL

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

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

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

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

Embedded SQL through domain-specific languages

See also

Related Research Articles

Structured Query Language (SQL) is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). It is particularly useful in handling structured data, i.e., data incorporating relations among entities and variables.

<span class="mw-page-title-main">Ingres (database)</span>

Ingres Database is a proprietary SQL relational database management system intended to support large commercial and government applications.

In computing, Open Database Connectivity (ODBC) is a standard application programming interface (API) for accessing database management systems (DBMS). The designers of ODBC aimed to make it independent of database systems and operating systems. An application written using ODBC can be ported to other platforms, both on the client and server side, with few changes to the data access code.

Mimer SQL is a proprietary SQL-based relational database management system produced by the Swedish company Mimer Information Technology AB, formerly known as Upright Database Technology AB. It was originally developed as a research project at the Uppsala University, Uppsala, Sweden in the 1970s before being developed into a commercial product.

In computing, the Perl DBI offers a standardized way for programmers using the Perl programming language to embed database communication within their programs. The latest DBI module for Perl from CPAN can run on a range of operating systems.

Oracle Database is a proprietary multi-model database management system produced and marketed by Oracle Corporation.

<span class="mw-page-title-main">PL/pgSQL</span>

PL/pgSQL is a procedural programming language supported by the PostgreSQL ORDBMS. It closely resembles Oracle's PL/SQL language. Implemented by Jan Wieck, PL/pgSQL first appeared with PostgreSQL 6.4, released on October 30, 1998. Version 9 also implements some ISO SQL/PSM features, like overloading of SQL-invoked functions and procedures.

The following tables compare general and technical information for a number of relational database management systems. Please see the individual products' articles for further information. Unless otherwise specified in footnotes, comparisons are based on the stable versions without any add-ons, extensions or external programs.

GeneXus is a low code, cross-platform, knowledge representation-based development tool, mainly oriented towards enterprise-class applications for web applications, smart devices, and the Microsoft Windows platform.

Simple Features is a set of standards that specify a common storage and access model of geographic features made of mostly two-dimensional geometries used by geographic databases and geographic information systems. It is formalized by both the Open Geospatial Consortium (OGC) and the International Organization for Standardization (ISO).

In database computing, Oracle Real Application Clusters (RAC) — an option for the Oracle Database software produced by Oracle Corporation and introduced in 2001 with Oracle9i — provides software for clustering and high availability in Oracle database environments. Oracle Corporation includes RAC with the Enterprise Edition, provided the nodes are clustered using Oracle Clusterware.

IBM Informix C-ISAM is an X/Open standards-compliant Application programming interface (API) to an Indexed Sequential Access Method or ISAM.

In relational databases, the information schema is an ANSI-standard set of read-only views that provide information about all of the tables, views, columns, and procedures in a database. It can be used as a source of the information that some databases make available through non-standard commands, such as:

 => SELECT count(table_name) FROM information_schema.tables;  count   -------  99    => SELECT column_name, data_type, column_default, is_nullable  FROM information_schema.columns WHERE table_name='alpha';  column_name | data_type | column_default | is_nullable   -------------+-----------+----------------+-------------  foo | integer | | YES  bar | character | | YES    => SELECT * FROM information_schema.information_schema_catalog_name;  catalog_name   --------------  johnd

SQL/PSM is an ISO standard mainly defining an extension of SQL with a procedural language for use in stored procedures. Initially published in 1996 as an extension of SQL-92, SQL/PSM was later incorporated into the multi-part SQL:1999 standard, and has been part 4 of that standard since then, most recently in SQL:2023. The SQL:1999 part 4 covered less than the original PSM-96 because the SQL statements for defining, managing, and invoking routines were actually incorporated into part 2 SQL/Foundation, leaving only the procedural language itself as SQL/PSM. The SQL/PSM facilities are still optional as far as the SQL standard is concerned; most of them are grouped in Features P001-P008.

A hierarchical query is a type of SQL query that handles hierarchical model data. They are special cases of more general recursive fixpoint queries, which compute transitive closures.

SQL:2011 or ISO/IEC 9075:2011 is the seventh revision of the ISO (1987) and ANSI (1986) standard for the SQL database query language. It was formally adopted in December 2011. The standard consists of 9 parts which are described in detail in SQL. The next version is SQL:2016.

ECPG is the standard, in the PostgreSQL database built-in, client programming interface for embedding SQL in programs written in the C programming language. It provides the option for accessing the PostgreSQL database directly from the C code in the application, using SQL commands.

References

  1. ↑ "Embedded SQL in DBMS". UseMyNotes . Retrieved 2023-10-13.
  2. ↑ "The Module Language Concept". SQL*Module for Ada Programmer's Guide, Release 8.0, Chapter 1. Introduction to SQL*Module. Oracle Corporation . Retrieved 2008-07-14.
  3. ↑ "SQL Standard 2023 Chapter 21 Embedded SQL". ISO/IEC 9075-2:2023 Information technology — Database languages — SQL — Part 2: Foundation (SQL/Foundation). ISO . Retrieved 2023-08-17.
  4. ↑ "DB2 Database for Linux, UNIX and Windows". Developing Embedded SQL Applications. IBM . Retrieved 2009-04-10.
  5. ↑ "Informix Dynamic Server". IBM® Informix® ESQL/C. IBM . Retrieved 2020-03-31.
  6. ↑ "Deprecated Database Engine Features in SQL Server 2008". SQL Server 2008 Books Online (March 2009). Microsoft . Retrieved 2009-04-10.
  7. ↑ "Embedded SQL". Mimer SQL Programmer's Manual latest version, Embedded SQL. Mimer SQL.
  8. ↑ "Ada Support in Version 8". Oracle9i Database Migration, Release 2 (9.2), Chapter 5. Compatibility and Interoperability. Oracle Corporation. Retrieved 2008-07-14.
  9. 1 2 3 "Language Alternatives". Pro*COBOL Precompiler Programmer's Guide, Release 8.0, Chapter 1. Introduction. Oracle Corporation. Retrieved 2008-07-14.
  10. ↑ "EmbeddedSQL". SAP Sybase Products. SAP Sybase. Retrieved 2012-09-05.
  11. ↑ "SAP Sybase SQL Anywhere". SAP Sybase Products. SAP Sybase. Archived from the original on 2013-05-01. Retrieved 2013-05-02.
  12. ↑ "SAP Sybase IQ". SAP Sybase Products. SAP Sybase. Archived from the original on 2013-05-04. Retrieved 2013-05-02.
This page is based on this Wikipedia article
Text is available under the CC BY-SA 4.0 license; additional terms may apply.
Images, videos and audio are available under their respective licenses.