Sql server tutorial 18 Accessing SQL Data from Your Host Program - XADAT.COM
post-template-default,single,single-post,postid-15819,single-format-standard,qode-quick-links-1.0,ajax_fade,page_not_loaded,,qode-theme-ver-11.1,qode-theme-bridge,wpb-js-composer js-comp-ver-5.1.1,vc_responsive

Sql server tutorial 18 Accessing SQL Data from Your Host Program No ratings yet.

Sql server tutorial 18 Accessing SQL Data from Your Host Program

Key Skills & Concepts

  • Invoke SQL Directly
  • Embed SQL Statements in Your Program
  • Create SQL Client Modules
  • Use an SQL Call-Level Interface

hroughout this book, you have been performing Try This exercises and testing
examples by using a client application to work interactively with your SQL database.
For example, you might have been using SQL Server Management Studio to access a SQL
Server database, SQL Developer to access an Oracle database, MySQL Workbench to
access a MySQL database, or perhaps a command-line SQL client. This method of data
access is referred to as direct invocation, or interactive SQL. The SQL standard also
provides for the use of other types of data access, including embedded SQL, SQL client
modules, and the call-level interface (CLI); however, the types of data access supported by
an SQL implementation often vary from product to product. Some, for example, do not
support embedded SQL, and few support SQL client modules. In this chapter, I introduce
you to the four types of data access methods and explain how they can be used to retrieve
and modify data in your SQL database. Because SQL and CLI are the two methods most
commonly used by programs to access SQL data, I cover these two topics in greater detail
than direct invocation and SQL client modules, although I do provide a foundation in all
four access types.

Invoke SQL Directly

If you’ve gotten this far in the book, you should already be very comfortable with
interactive SQL. By using your client application, which comes with most database
management products, you’ve been able to create ad hoc SQL statements that return
immediate results to the application. These results are normally displayed in a window
separate from where you executed your SQL statement. For example, let’s take a look at
Figure 18-1, which shows SQL Server Management Studio. Notice that the top window
includes a SELECT statement and the bottom window includes the query results from
executing that statement. Most direct invocation client applications behave in a manner
similar to this.

Figure 18-1 SQL Server Management Studio
The types of SQL statements supported by the direct invocation method can vary from
one SQL implementation to the next. Although most implementations will allow you to
execute basic types of statements, such as SELECT or UPDATE, they might not allow you
to execute statements specific to another method of data access. For example, some
implementations might not allow you to declare a cursor within an interactive

Despite the differences among SQL implementations, the SQL standard does define
which types of statements should be supported in an interactive environment. These
include SELECT, INSERT, UPDATE, and DELETE statements and statements related to
schema definitions, transactions, connections, and sessions. You should also be able to
declare temporary tables in an interactive environment. In fact, nearly any actions critical
to the maintenance of data and of the underlying database structure should be supported
by direct invocation.
One of the main advantages to interactive SQL—in addition to the ability to execute ad

hoc statements—is the elimination of any impedance mismatch. As you’ll recall from
earlier discussions, an impedance mismatch can occur because of differences in data types
between SQL and application programming languages and in how query results (result
sets) are handled between SQL and those languages. However, interactive SQL is a pure
SQL environment, which means that only the data types supported by the implementation
can be used, and result sets pose no problems to the client application because you can
simply scroll through query results. It is also common for application developers to use
interactive SQL to test the SQL statements they intend to embed in other modules. Even
so, direct invocation represents only a small percentage of users. You’ll find that most data
access is through embedded SQL and CLI-type mechanisms, and some through SQL client
modules, but relatively few users rely solely on interactive SQL.
Embed SQL Statements in Your Program
In Chapter 16, when I discuss SQL cursors, I introduce you to embedded SQL. As you’ll
recall from that discussion, embedded SQL refers to SQL statements that are interspersed
in some type of application programming language. The SQL statements are blended into
the host language to allow the source program to be able to access and modify SQL data
and the underlying database structure.

According to the SQL standard, you can embed SQL statements in the following
programming languages:


Although the standard supports embedded SQL statements in these languages, SQL
implementations rarely support embedding statements in all these languages. An
implementation might be limited to only one or two programming languages, and some
implementations might not support embedded SQL at all (although most implementations

0 >1>2>3>4>5>6>7>8>9>10>11>12>13>14>15>16>17>18>19>20>

No Comments

Sorry, the comment form is closed at this time.