Databases Reference
In-Depth Information
help available with access to database and database object structures, and easy
access to documentation. You can check commands for syntax errors before run-
ning them and easily edit your commands using a tool called Query Analyzer.
The biggest problem with using Query Analyzer is that it is resource inten-
sive and is not always available. If you want to run it from a client computer,
which is what you would normally want to do for security reasons, then you
must install the SQL Server client tools on that computer. The client tools include
SQL Server Management Studio.
Embedded SQL
Embedded SQL is best suited to activities that must be performed periodically
and that you want performed in the same way each time. Embedded SQL is also
a critical part of any database application. Embedded SQL uses the same SQL
language commands as when you are running interactive SQL statements. The
difference is that they are included as part of an executable program.
SQL Server, for example, has two basic programmable objects that provide
embedded SQL support. These are stored procedures and user-defined functions.
Both are similar in that they are sets of executable statements, they can accept
parameters to help control execution, and they can perform actions and return
results. The primary difference is how they are used. Stored procedures are most
often used to automate periodic procedures or to hide the details of those pro-
cedures from the users. User-defined functions are used when you want to return
either a scalar value to a user or a result formatted as a table.
For example, you might create a stored procedure to process customer pay-
ments. The user would need to provide the stored procedure name, the customer
ID, and the payment amount. Statements inside the stored procedure would han-
dle the details of exactly what changes need to be made to the table or tables involved
and could detect and respond to errors that might occur during the process. Not
only do you ensure that the payments are posted properly, this also helps to ensure
data security. Users do not need to know how the tables involved in the process are
structured. Because they don't know how the data is structured, it's harder for some-
one to retrieve information from the database without proper authorization.
Embedded SQL is also used in application programs. The programming envi-
ronment provides the connectivity tools to communicate with the database server
and execute SQL commands. This can be done by passing literal command
strings to the database server for execution or through application programming
interfaces (APIs) that provide the necessary functionality. For example, SQL
Server 2005 provides a set of.NET Framework management objects that make it
easy to build management applications. Also, the data objects provided with
ADO.NET support direct manipulation of database tables and other database
objects. In fact, ADO.NET is able to mirror the database and table structure,
down to column names and table constraints, in memory.
