1 / 11

Embedded SQL

Embedded SQL. Unit 5.1. Interactive SQL. So far in the module we have considered only the SQL queries which you can type in at the SQL prompt. We refer to this as ‘interactive’ SQL. SQL is a ‘non-procedural’ language SQL specifies WHAT is required, not HOW this requirement is to be met.

thanos
Download Presentation

Embedded SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Embedded SQL Unit 5.1 Unit 5.1 - Embedded SQL - V3.0

  2. Interactive SQL So far in the module we have considered only the SQL queries which you can type in at the SQL prompt. We refer to this as ‘interactive’ SQL. • SQL is a ‘non-procedural’ language • SQL specifies WHAT is required, not HOW this requirement is to be met. • INTERACTIVE SQL is good for: • defining database structure • generating low-volume, ad hoc queries • prototyping • INTERACTIVE SQL is not good for the more sophisticated applications for which a programming language with links to SQL might be better. Unit 5.1 - Embedded SQL - V3.0

  3. Embedded SQL SQL can be embedded within procedural programming languages. These language (sometimes referred to as 3GLs) include C/C++, Cobol, Fortran, and Perl. Thus the embedded SQL provides the 3GL with a way to manipulate a database, supporting: • highly customized applications • background applications running without user intervention • database manipulation which exceeds the abilities of simple SQL • applications linking to Oracle packages, e.g. forms and reports • applications which need customized window interfaces Unit 5.1 - Embedded SQL - V3.0

  4. SQL Precompiler Precompilers are used to translate SQL statements embedded in a host language into DBMS library calls which can be implemented in the host language. This method of embedding was common where the links between database and program were to be blurred. In recent times, the database-program link is shown in the program much more explicitly, and the need for precompilers has been greatly reduced. Unit 5.1 - Embedded SQL - V3.0

  5. Sharing Variables In general the variables of the program are isolated from the variables (the rows and columns) of the database system. This gives the programmer a much better understanding of what is actually being changed when a variable is set. Some variables are still globally shared between databases and programming languages. These are mostly to do with error tracking. For instance, executing an SQL statement which results in an error may return an error code, but a global variable may also be set with a much more detailed error report. Unit 5.1 - Embedded SQL - V3.0

  6. Connecting to the DBMS In this lecture, Perl examples are used. However, no understanding of Perl should be necessary to understand the examples… Connecting to the database is little more than: $dbh = DBI->connect(“dbname”,”username”,”password”) This gives an “handle” on the database connection which can be used to instigate further commands. Unit 5.1 - Embedded SQL - V3.0

  7. Executing SQL SQL to be executed goes through a two phase process of interpretation (prepare) and then execution (execute). $cmd = $dbh->prepare(“ SELECT custname FROM customers WHERE cno = ?”) $res = $cmd->execute(10) Thus $res is a link to the results of executing this SQL where the ? Is replaced with the number “10”. Unit 5.1 - Embedded SQL - V3.0

  8. Cursors - SELECT many rows • A cursor provides a pointer to a single row in the result of a selection query (which may return may rows) • One row at a time is accessed through the cursor, which is moved to the next row before each data transfer • The columns of that one row are ‘fetched’ into the program variables which can then be manipulated in the normal way by the host program. • A cursor can also be used to update values in tables if it is linked to an INSERT SQL command rather than a SELECT query. Unit 5.1 - Embedded SQL - V3.0

  9. Simple SELECT Cursor $cmd = $dbh->prepare(“SELECT custname FROM customers WHERE cno = ?”) $res = $cmd->execute(10) While ( ($name) = $res->fetchrow_array()) { print “The customer name is $name” } This prints out all the names of customers where the cno is 10. Unit 5.1 - Embedded SQL - V3.0

  10. Simple INSERT cursor $cmd = $dbh->prepare(“INSERT into EMPCOURSE values (?,?)”); @records = ([10,20],[10,30]); For $r (@records) { $cmd->bind_param(1,$r->[0],SQL_INTEGER); $cmd->bind_param(2,$r->[1],SQL_INTEGER); $cmd->execute(); $dbh->commit(); } Unit 5.1 - Embedded SQL - V3.0

  11. Summary • Cursors provide a means of integrating traditional 3GL procedural languages and databases by enabling row-at-a-time access. • Languages such as Visual Basic and Visual C++ also have build-in statements that enable this type of processing with a dedicated database, like that provided by MS-Access. • Java has a well-defined interface to enable easy access to databases through a Database Connectivity library - JDBC. • Perl makes use of the DBI standard, which is fast becoming the main contender to ODBC. • Unfortunately, there are many ‘standards’. Unit 5.1 - Embedded SQL - V3.0

More Related