Limitations of Static SQL in Software

Creator QR Code 2d barcode in Software Limitations of Static SQL

CHAPTER
QR Code ISO/IEC18004 Reader In None
Using Barcode Control SDK for Software Control to generate, create, read, scan barcode image in Software applications.
Drawing QR Code In None
Using Barcode creation for Software Control to generate, create QR Code JIS X 0510 image in Software applications.
Dynamic SQL*
QR Code JIS X 0510 Scanner In None
Using Barcode scanner for Software Control to read, scan read, scan image in Software applications.
QR Code 2d Barcode Maker In C#
Using Barcode generator for Visual Studio .NET Control to generate, create QR Code 2d barcode image in VS .NET applications.
he embedded SQL programming features described in 17 are collectively known as static SQL. Static SQL is adequate for writing all of the programs typically required in a data processing application. For example, in the order-processing application of the sample database, you can use static SQL to write programs that handle order entry, order updates, order inquiries, customer inquiries, customer file maintenance, and programs that produce all types of reports. In every one of these programs, the pattern of database access is decided by the programmer and hard-coded into the program as a series of embedded SQL statements. There is an important class of applications, however, where the pattern of database access cannot be determined in advance. A graphic query tool or a report writer, for example, must be able to decide at runtime which SQL statements it will use to access the database. A personal computer spreadsheet that supports host database access must also be able to send a query to the host DBMS for execution on the fly. These programs and other general-purpose database front-ends cannot be written using static SQL techniques. They require an advanced form of embedded SQL, called dynamic SQL, described in this chapter.
Painting QR Code In Visual Studio .NET
Using Barcode creation for ASP.NET Control to generate, create QR image in ASP.NET applications.
Drawing QR Code 2d Barcode In .NET Framework
Using Barcode encoder for .NET framework Control to generate, create QR Code JIS X 0510 image in VS .NET applications.
Limitations of Static SQL
Quick Response Code Creator In Visual Basic .NET
Using Barcode generator for VS .NET Control to generate, create Quick Response Code image in .NET applications.
ECC200 Drawer In None
Using Barcode generation for Software Control to generate, create Data Matrix image in Software applications.
As the name static SQL implies, a program built using the embedded SQL features described in 17 (host variables, cursors, and the DECLARE CURSOR, OPEN, FETCH, and CLOSE statements) has a relatively stable pattern of database access. For each embedded SQL statement in the program, the tables and columns referenced by that statement are determined in advance by the programmer and hard-coded into the embedded SQL statement. Input host variables provide some flexibility in static SQL, but they don t fundamentally alter its static nature. Recall that a host variable can appear anywhere a constant is allowed in a SQL statement. You can use a host variable to alter a search condition:
Drawing EAN / UCC - 13 In None
Using Barcode maker for Software Control to generate, create EAN / UCC - 13 image in Software applications.
Make Bar Code In None
Using Barcode maker for Software Control to generate, create barcode image in Software applications.
exec sql select name, quota, sales from salesreps where quota > :cutoff_amount;
Code-128 Encoder In None
Using Barcode creation for Software Control to generate, create ANSI/AIM Code 128 image in Software applications.
Making UPC-A In None
Using Barcode generator for Software Control to generate, create UPC-A Supplement 2 image in Software applications.
Part V:
USPS POSTal Numeric Encoding Technique Barcode Generation In None
Using Barcode generation for Software Control to generate, create Postnet image in Software applications.
UCC - 12 Encoder In Java
Using Barcode generation for Java Control to generate, create GS1-128 image in Java applications.
Programming with SQL
Bar Code Maker In Java
Using Barcode maker for BIRT Control to generate, create bar code image in BIRT applications.
Paint 2D Barcode In Java
Using Barcode creator for Java Control to generate, create Matrix Barcode image in Java applications.
You can also use a host variable to change the data inserted or updated in a database:
Painting Bar Code In Java
Using Barcode generation for Android Control to generate, create bar code image in Android applications.
UCC - 12 Drawer In None
Using Barcode generation for Microsoft Word Control to generate, create UCC - 12 image in Microsoft Word applications.
exec sql update salesreps set quota = quota + :increase where quota > :cutoff_amount;
Linear 1D Barcode Generator In VS .NET
Using Barcode maker for Visual Studio .NET Control to generate, create Linear image in .NET applications.
Scan Barcode In None
Using Barcode recognizer for Software Control to read, scan read, scan image in Software applications.
However, you cannot use a host variable in place of a table name or a column reference. The attempted use of the host variables which_table and which_column in these statements is illegal:
exec sql update :which_table set :which_column = 0; exec sql declare cursor cursor7 for select * from :which_table;
Even if you could use a host variable in this way (and you cannot), another problem would immediately arise. The number of columns produced by the query in the second statement would vary, depending on which table was specified by the host variable. For the OFFICES table, the query results would have six columns; for the SALESREPS table, they would have nine columns. Furthermore, the data types of the columns would be different for the two tables. But to write a FETCH statement for the query, you must know in advance how many columns of query results there will be and their data types, because you must specify a host variable to receive each column:
exec sql fetch cursor7 into :var1, :var2, :var3;
As this discussion illustrates, if a program must be able to determine at runtime which SQL statements it will use, or which tables and columns it will reference, static SQL is inadequate for the task. Dynamic SQL overcomes these limitations. Dynamic SQL has been supported by the IBM SQL products since their introduction, and it has been supported for many years by the minicomputer-based and UNIX-based commercial RDBMS products. However, dynamic SQL was not specified by the original ANSI/ISO SQL1 standard; the standard defined only static SQL. The absence of dynamic SQL from the SQL1 standard is ironic, given the popular notion that the standard allowed you to build front-end database tools that are portable across many different DBMS brands. In fact, such front-end tools must almost always be built using dynamic SQL. In the absence of an ANSI/ISO standard, DB2 set the de facto standard for dynamic SQL. The other IBM databases of the day (SQL/DS and OS/2 Extended Edition) were nearly identical to DB2 in their dynamic SQL support, and most other SQL products also followed the DB2 standard. Official support for dynamic SQL was added to the SQL standard in 1992 (version SQL2), mostly following the path set by IBM. The SQL standard does not require dynamic SQL support at the lowest level of compliance (Entry), but dynamic SQL support is required for products claiming the Intermediate or Full compliance levels to the SQL standard.
18:
Dynamic SQL*
Copyright © OnBarcode.com . All rights reserved.