Chapter 4. Embedded SQL Programming

Ability to connect to databases within an embedded SQL programming application

[Note]

Embedded SQL database applications connect to databases and execute embedded SQL statements. Embedded SQL statements are embedded within a host language application. Embedded SQL database applications support the embedding of SQL statements to be executed statically or dynamically.

You can develop embedded SQL applications for DB2 in the following host programming languages: C, C++, COBOL, FORTRAN, and REXX.

Building embedded SQL applications involves two prerequisite steps prior to application compilation and linking.

  • Preparing the source files containing embedded SQL statements using the DB2 precompiler.

    The PREP (PRECOMPILE) command is used to invoke the DB2 precompiler, which reads your source code, parses and converts the embedded SQL statements to DB2 run-time services API calls, and finally writes the output to a new modified source file. The precompiler produces access plans for the SQL statements, which are stored together as a package within the database.

  • Binding the statements in the application to the target database.

    Binding is done by default during precompilation (the PREP command). If binding is to be deferred (for example, running the BIND command later), then the BINDFILE option needs to be specified at PREP time in order for a bind file to be generated.

Once you have precompiled and bound your embedded SQL application, it is ready to be compiled and linked using the host language-specific development tools.

SQL statements can be executed in one of two ways: statically or dynamically.

  • Statically executed SQL statements

    For statically executed SQL statements, the syntax is fully known at precompile time. The structure of an SQL statement must be completely specified for a statement to be considered static. For example, the names for the columns and tables referenced in a statement must be fully known at precompile time. The only information that can be specified at run time are values for any host variables referenced by the statement. However, host variable information, such as data types, must still be precompiled. You precompile, bind, and compile statically executed SQL statements before you run your application. Static SQL is best used on databases whose statistics do not change a great deal.

  • Dynamically executed SQL statements

    Dynamically executed SQL statements are built and executed by an application at run-time. An interactive application that prompts the end user for key parts of an SQL statement, such as the names of the tables and columns to be searched, is a good example of a situation suited for dynamic SQL.

Package privileges

A package is a database object that contains the information needed by the database manager to access data in the most efficient way for a particular application program. Package privileges enable a user to create and manipulate packages. The user must have CONNECT authority on the database to use any of the following privileges:

  • CONTROL provides the user with the ability to rebind, drop, or execute a package as well as the ability to extend those privileges to others. The creator of a package automatically receives this privilege. A user with CONTROL privilege is granted the BIND and EXECUTE privileges, and can also grant these privileges to other users by using the GRANT statement. (If a privilege is granted using WITH GRANT OPTION, a user who receives the BIND or EXECUTE privilege can, in turn, grant this privilege to other users.) To grant CONTROL privilege, the user must have SYSADM or DBADM authority.

  • BIND privilege on a package allows the user to rebind or bind that package and to add new package versions of the same package name and creator.

  • EXECUTE allows the user to execute or run a package.

Connecting to DB2 databases in embedded SQL applications

Before working with a database, you need to establish a connection to that database. Embedded SQL provides multiple ways in which to include code for establishing database connections. Depending on the embedded SQL host programming language there might be one or more way of doing this.

Database connections can be established implicitly or explicitly. An implicit connection is a connection where the user ID is presumed to be the current user ID. This type of connection is not recommended for database applications. Explicit database connections, which require that a user ID and password be specified, are strongly recommended.

When working with C and C++ applications, a database connection can be established by executing the following statement:

 EXEC SQL CONNECT TO sample;
					
If you want to use a specific user id (herrick) and password (mypassword), use the following statement:
EXEC SQL CONNECT TO sample USER herrick USING mypassword;
					

int DbConnect(char dbAlias[], char user[], char pswd[])
{
  struct sqlca sqlca;

  /* connect to a database */
  if (strlen(user) > 0)
  {
    EXEC SQL CONNECT TO :dbAlias USER :user USING :pswd;
  }
  else
  {
    EXEC SQL CONNECT TO :dbAlias;
  }

  return 0;
}
					

Introduction to SQLJ

SQLJ enables you to embed SQL statements into Java programs. SQLJ is an ANSI standard developed by a consortium of leading providers of database and application server software including IBM Corporation, Microsoft Corporation, Sun Microsystems, and Oracle.

The SQLJ translator translates an SQLJ source file into a standard Java source file plus an SQLJ serialized profile that encapsulates information about static SQL in the SQLJ source. The translator converts SQLJ clauses to standard Java statements by replacing the embedded SQL statements with calls to the SQLJ runtime library. An SQLJ customization script binds the SQLJ profile to the database, producing one or more database packages. The Java file is compiled and run (with the packages) on the database. The SQLJ runtime environment consists of an SQLJ runtime library that is implemented in pure Java. The SQLJ runtime library calls the JDBC driver for the target database, such as DB2.

  • SQLJ and JDBC

    Although both SQLJ and JDBC provide Java applications with the capability to access relational databases, there are several differences between them:

    • JDBC uses dynamic SQL. In contrast SQLJ can use static SQL. To use static SQL, you run a customization script to bind the SQL statements to the database before you run the application.

    • SQLJ requires precompilation. The SQLJ translator converts code that contains SQLJ clauses embedded in Java code into pure Java code that calls into the SQLJ runtime library.

  • Dynamic SQL versus static SQL

    In general, dynamic SQL is more flexible than static SQL, because it does not require SQL statements to be created in advance. Static SQL is more efficient, because the database has done much of the work required to run the SQL statements before run time.

    Dynamic SQL provides the ability to query and update tables when, for example, you do not know the number and types of the columns until run time. Using dynamic SQL in JDBC, a Java program can create SQL statements at run time.

    With dynamic SQL, database access and authorization are determined at run time. The user of the application must have all required database privileges, and the database must determine the best way to access the required data at run time. However, with static SQL, access and authorization are determined at customization and bind time. The privileges of the user who performs the bind process determine what the application can do. The database determines the best way to access data during customization and bind time, which improves runtime performance.

    Many applications do not need to dynamically create SQL statements because the database metadata (such as table and column names) does not change at run time. Such applications can use static SQL, which provides better performance than dynamic SQL.

  • Advantages of SQLJ

    In addition to better performance through using static SQL, SQLJ has the following advantages over JDBC:

    • SQLJ generally requires fewer lines of code than JDBC to perform the same tasks, which means less time spent developing, debugging, and maintaining an application.

    • The SQLJ translator checks the syntax of SQL statements during translation. This check prevents any runtime SQL syntax errors. JDBC performs no such checks, which can allow runtime SQL syntax errors to occur.

    • SQLJ uses database connections to type-check static SQL code. JDBC does not perform type-checking until run time.

    • With SQLJ, you can embed Java variables in SQL statements. In contrast, JDBC requires separate get and set calls for each variable, and you must specify the binding by position number.

    • SQLJ provides strong typing of query output and return parameters and allows type-checking on calls. JDBC does not perform compile-time type-checking of variables passed to and received from SQL.

    • SQLJ provides static package-level security with compile-time encapsulation of database authorization.

    JDBC can do one thing that SQLJ cannot do: run dynamic SQL statements. However, if an application needs to use dynamic SQL, you can include some JDBC code in your program with SQLJ clauses.

Professional hosting     Belorussian informational portal         Free SCWCD 1.4 Study Guide     Free SCDJWS 1.4 Study Guide     SCDJWS 1.4 Quiz     Free IBM Certified Associate Developer Study Guide     IBM Test 000-287. Enterprise Application Development with IBM WebSphere Studio, V5.0 Study Guide     SCDJWS 5.0 Quiz