Embedded SQL
Encyclopedia
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
.
The ANSI
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. 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. The mixed C and embedded SQL is called Pro*C in Oracle
and Sybase
database management systems. In the PostgreSQL
database management system this precompiler is called ECPG. Other embedded SQL precompilers are Pro*Ada
, Pro*COBOL, Pro*FORTRAN, Pro*Pascal
, and Pro*PL/I
.
version 9 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.
in version 7.3. Starting with Oracle8, Pro*Ada has been replaced by SQL*Module but appears to have not been updated since. 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.
Pascal : Pro*Pascal was not released with Oracle8.
PL/I : Pro*PL/I was not released with Oracle8. The Pro*PL/I Supplement to the Oracle Precompilers Guide, however, continues to make appearances in the Oracle Documentation Library (current as of release 11g).
COBOL
2008 although earlier versions of the product support it.
Computing
Computing is usually defined as the activity of using and improving computer hardware and software. It is the computer-specific part of information technology...
power of a programming language
Programming language
A programming language is an artificial language designed to communicate instructions to a machine, particularly a computer. Programming languages can be used to create programs that control the behavior of a machine and/or to express algorithms precisely....
and the database
Database
A database is an organized collection of data for one or more purposes, usually in digital form. The data are typically organized to model relevant aspects of reality , in a way that supports processes requiring this information...
manipulation
Data Manipulation Language
A data manipulation language is a family of syntax elements similar to a computer programming language used for inserting, deleting and updating data in a database...
capabilities of SQL
SQL
SQL is a programming language designed for managing data in relational database management systems ....
. Embedded SQL statements
Statement (programming)
In computer programming a statement can be thought of as the smallest standalone element of an imperative programming language. A program written in such a language is formed by a sequence of one or more statements. A statement will have internal components .Many languages In computer programming...
are SQL statements written inline with the program source code
Source code
In computer science, source code is text written using the format and syntax of the programming language that it is being written in. Such a language is specially designed to facilitate the work of computer programmers, who specify the actions to be performed by a computer mostly by writing source...
of the host language. The embedded SQL statements are parsed
Parsing
In computer science and linguistics, parsing, or, more formally, syntactic analysis, is the process of analyzing a text, made of a sequence of tokens , to determine its grammatical structure with respect to a given formal grammar...
by an embedded SQL preprocessor
Preprocessor
In computer science, a preprocessor is a program that processes its input data to produce output that is used as input to another program. The output is said to be a preprocessed form of the input data, which is often used by some subsequent programs like compilers...
and replaced by host-language calls
Call site
In programming, a call site of a function is a line in the code which calls a function. A call site passes zero or more arguments to the function, and receives zero or more return values.-Example:...
to a code library. The output from the preprocessor is then compiled by the host compiler
Compiler
A compiler is a computer program that transforms source code written in a programming language into another computer language...
. This allows programmers to embed SQL statements in programs written in any number of languages such as: C/C++, COBOL
COBOL
COBOL is one of the oldest programming languages. Its name is an acronym for COmmon Business-Oriented Language, defining its primary domain in business, finance, and administrative systems for companies and governments....
and Fortran
Fortran
Fortran is a general-purpose, procedural, imperative programming language that is especially suited to numeric computation and scientific computing...
.
The ANSI
Ansi
Ansi is a village in Kaarma Parish, Saare County, on the island of Saaremaa, Estonia....
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. 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. The mixed C and embedded SQL is called Pro*C in Oracle
Oracle Database
The Oracle Database is an object-relational database management system produced and marketed by Oracle Corporation....
and Sybase
Sybase
Sybase, an SAP company, is an enterprise software and services company offering software to manage, analyze, and mobilize information, using relational databases, analytics and data warehousing solutions and mobile applications development platforms....
database management systems. In the PostgreSQL
PostgreSQL
PostgreSQL, often simply Postgres, is an object-relational database management system available for many platforms including Linux, FreeBSD, Solaris, MS Windows and Mac OS X. It is released under the PostgreSQL License, which is an MIT-style license, and is thus free and open source software...
database management system this precompiler is called ECPG. Other embedded SQL precompilers are Pro*Ada
Ada (programming language)
Ada is a structured, statically typed, imperative, wide-spectrum, and object-oriented high-level computer programming language, extended from Pascal and other languages...
, Pro*COBOL, Pro*FORTRAN, Pro*Pascal
Pascal (programming language)
Pascal is an influential imperative and procedural programming language, designed in 1968/9 and published in 1970 by Niklaus Wirth as a small and efficient language intended to encourage good programming practices using structured programming and data structuring.A derivative known as Object Pascal...
, and Pro*PL/I
PL/I
PL/I is a procedural, imperative computer programming language designed for scientific, engineering, business and systems programming applications...
.
IBM DB2
IBM DB2IBM DB2
The IBM DB2 Enterprise Server Edition is a relational model database server developed by IBM. It primarily runs on Unix , Linux, IBM i , z/OS and Windows servers. DB2 also powers the different IBM InfoSphere Warehouse editions...
version 9 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.
Oracle Corporation
Ada : Pro*Ada was officially desupported by OracleOracle Corporation
Oracle Corporation is an American multinational computer technology corporation that specializes in developing and marketing hardware systems and enterprise software products – particularly database management systems...
in version 7.3. Starting with Oracle8, Pro*Ada has been replaced by SQL*Module but appears to have not been updated since. 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.
Pascal : Pro*Pascal was not released with Oracle8.
PL/I : Pro*PL/I was not released with Oracle8. The Pro*PL/I Supplement to the Oracle Precompilers Guide, however, continues to make appearances in the Oracle Documentation Library (current as of release 11g).
PostgreSQL
C/C++- ECPG is part of PostgreSQLPostgreSQLPostgreSQL, often simply Postgres, is an object-relational database management system available for many platforms including Linux, FreeBSD, Solaris, MS Windows and Mac OS X. It is released under the PostgreSQL License, which is an MIT-style license, and is thus free and open source software...
since version 6.3.
COBOL
- Cobol-IT is now distributing a COBOL precompiler for PostgreSQL
Altibase
C/C++- SESC is an embedded SQL precompiler provided by AltibaseAltibaseAltibase helps its customers maximize their data investments by providing real-time data performance solutions. In today’s competitive business environment, Altibase enables companies to drastically improve the speed of data access and analysis across the enterprise....
Corp. for its DBMS server.
Data Access Corporation
With DataFlex 3.2 and Visual DataFlex you can pass SQL statements via one of the Data Access CLI connectivity kits to Microsoft SQL Server, IBM DB2 or any ODBC supporting database. The results can be retrieved and processed.Microsoft SQL Server
COBOL- Cobol-IT is distributing a Embedded SQL precompiler for COBOL.
MySQL
COBOL- Cobol-IT is distributing a Embedded SQL precompiler for COBOL.
Microsoft SQL Server
Embedded SQL for C has been deprecated as of Microsoft SQL ServerMicrosoft SQL Server
Microsoft SQL Server is a relational database server, developed by Microsoft: It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network...
2008 although earlier versions of the product support it.