Embedded Sql

Embedded SQL is offered by some database vendors where there are SQL calls made from within (or embedded in) some other language such as C.

Typical embedded SQL:

 void FetchCustomer(int custNbr, char *custName, int *birthYear) {
   int someVar = 0;
   EXEC SQL BEGIN DECLARE SECTION;
     int custNbrForSql;
     char *custNameForSql;
     int birthYearForSql;
   EXEC SQL END DECLARE SECTION;
   custNbrForSql = custNbr;
   custNameForSql = custName;
   someVar = SomeFunctionCall();
   EXEC SQL
     SELECT CUSTOMER_NAME, BIRTH_YEAR
     INTO :custNameForSql, :birthYearForSql
     FROM CUSTOMER_TABLE
     WHERE CUSTOMER_NUMBER = :custNbrForSql
   END-EXEC;
   *birthYear = birthYearForSql;
   return;
 }

As you can see, the SQL is embedded, or mixed with the source code, as if it were an extension to the language -- not a function call. A preprocessor is used to extract the SQL statements and translate them into function calls.

Embedded SQL is popular with traditional languages (like COBOL) and traditional programmers; it's generally easier to use than an API consisting of numerous function or member calls.

Examples:

• http://www.willcam.com/sql/sql_005a.htm

Embedded SQL tools:

• Sybase & C/C++ -- http://manuals.sybase.com/onlinebooks/group-cn/cng1000e/cguide/@Generic__BookView/356;cs=default;ts=default
• Sybase & COBOL -- http://manuals.sybase.com/onlinebooks/group-cn/cng1110e/esqlcob/@Generic__BookView/363;cs=default;ts=default
• Oracle & C/C++ -- Pro*C
• Oracle & COBOL -- Pro*COBOL
• Microsoft SQL Server -- not supported (...unless someone else can find it!)
• Java - EmbeddedSqlInJava

---

OCI (Oracle Call Interface) is not embedded SQL; it's a call interface. PRO*C is the preprocessor for Oracle's embedded SQL.

---

---

My experience has been that Embedded SQL is not very compatible with C or C++ -- it's preprocessor tends to compete with the C/C++ preprocessor, and its understanding of the host language (at least with the Oracle product) is very limited. -- JeffGrigg

You can do DynamicQueries? with the EmbeddedSql interface. It kinda' defeats the purpose of doing EmbeddedSql, but many corporations require that you use EmbeddedSql, in spite of its inappropriateness to your task, so you just have to live with it and do the extra work. -- JeffGrigg

---

---

I've always wanted the opposite -- where I use a programming language as it is intended to make queries that get turned into SQL behind the scenes.

E.g. in an object oriented language I would like to create relationship objects that represent relationships between sets of tuples. To make a query I would pass a relationship to a database connection and the relationship would know how serialise itself as SQL. Such an API would be slightly more abstract than the java.sql package (where you create strings containing SQL statements and have to worry about SQL syntax) but less abstract than the domain model in a multi-tiered system.

Does anything like this exist?

---

Yes. At work, I use Lispworks from Xanalys. You can map classes to rows in tables. You can express join relationships as containment (i.e. one object has a slot of objects of another class, when you access that slot, it fires off the correct SELECT.)

So we tend not to think of our tables as fundamental. We design our object model first, then we implement the tables to reflect those class definitions. You end up with things like:

 (define-persistent-class customer ()
 ((name :db-kind :base
        :db-type (string 64)
        :accessor name))
 ((accounts :db-kind :join
 :accessor accounts
 (:join-info ((:db-class account)
       :home-key customer-id
       :foreign-key account-customer-id))))
 (etc))


 (with-transaction ()
 (dolist (account (accounts some-customer))
 ;; if this changes account, SQL queries
 ;; are fired off to update account slots...
 ;; with-transaction makes the whole thing atomic
 (process-account account)))

What it comes down to is that it acts like a poor man's ObjectDatabase, implemented on a standard SQL engine. We considered using a real ObjectDatabase, but rejected that for business reasons. --AlainPicard

---

---

The PhpLanguage has great SQL support (MySql, particularly), does this count as embedded? -- MatthewTheobalds

Probably not, but I'd have to see an example of its code. (If the SQL is in strings, then it's not embedded SQL.)

And where do you obtain this definition?

Embedded SQL is defined by the SQL standard. Standardized in 1989. See http://www.postgresql.org/idocs/index.php?sql.html

---

In my experience, if you are doing a lot of work with SQL, then dynamically-typed languages seem to generate simpler code. You don't have as much code devoted to converting types back and forth. Static typing is best for "insular" systems where almost everything from cradle-to-grave is handled in a single programming language.

---

"I've always wanted the opposite -- where I use a programming language as it is intended to make queries that get turned into SQL behind the scenes. "

Have you seen "Simple and Safe SQL Queries with C++ Templates" by Joseph (Yossi) Gil and Keren Lenz? They encode relational algebra as usual C++ code with templates, transforming it to SQL for executing. -- ygrek