Embedding SQL in C# and Java

The following is a part of the document that made the project report for my undergraduate degree.
The purpose of the project was to investigate how SQL is embedded in C#.NET database applications as well as Java database applications.
Part of the project was to implement a working prototype database application with C#.NET and also Java to further investigate the result of the research work.

Here you will find information on how to connect to a MS SQL Server database from C#.NET database application as well as a Java database application. It also describes how to pass embedded SQL queries (SELECT, INSERT, UPDATE, DELETE), calling stored procedures, pass parameters and much more.

If you have any queries you may contact me at info@shahriarnk.com.


| Contents | |

Share |


Contents

  1. Introduction

  2. Embedding SQL in C#.NET database application

    1. Data Access Technologies

    2. ADO.NET

    3. The .NET Framework data providers

    4. Connecting to a data source

    5. Creating a SQL command

    6. Executing a SQL command

    7. Passing parameters to queries/commands

    8. Calling stored procedures

  3. Embedding SQL in Java database application

    1. The JDBC API

    2. Connecting to a data source

    3. DriverManager Class

    4. Querying the data source

    5. ResultSet Object

  4. Manipulating data in a data source programmatically (without SQL)

    1. Accessing data without SQL in C#.NET database application

    2. Accessing data without SQL in Java database application

  5. Appendix A

    1. Table1: Java Types Mapped to JDBC Types

    2. Table2: JDBC Types Mapped to Java Types

  6. Appendix B

    1. Table: Mappings between SQL Server, JDBC, and Java programming language data types
  7. Appendix C
    1. Table: Conversions by ResultSet.getXXX Methods from JDBC Types (to Java Types)
  8. References
  9. Bibliography

 

 

Introduction | Next | Go To Top |

SQL or Structured Query Language is a programming language used by the popular database management systems (DBMSs) to store, manipulate and retrieve any data in the database. It has been standardized for any one who wants to use SQL as the language for storing and manipulating data in a database.

Embedding SQL is the process of sending 'pure' SQL statements or queries directly to the database from a programming language such as Java, referred to as the 'host language', to perform actions in the database. 'Pure SQl' in this case means that the SQL statement/query sent must follow the exact SQL syntax as defined by the SQL standard or the syntax supported by the DBMS which is the same as the international standard (but sometimes not) . In other words, embedding SQL enables placing SQL statements directly into the logic of the program or application.

The only other way applications can interact with data in the database is by using functions and procedures provided by the host languages (i.e. without any embedded SQL statement).

Why Embed SQL
The only purpose SQL was built for is to interact with the database. Hence, it constructs were designed to be intuitive for raw data manipulation, for e.g. 'CREATE TABLE', 'SELECT FROM EMPLOYEE TABLE' etc. It also enables the creation of manipultaion of database optimizers such as indexes which are necessary for any real database. These are just a few of the SQL functionalities.

On the other hand, a language such as Java was created for a range of purposes wider than the scope of a database. Hence, functions or procedures native to the host language, inherently fall short of SQL either in their functionality or flexibity.

Embedding SQL produces programs that are easy to understand as the queries are 'inside' the program. The produce more efficient applications too.

2. Embedding SQL in C#.NET database application | Go To Top | |

2.1. Data Access Technologies | Next | Previous | Go To Top |
The .NET framework is the foundation for several programming languages provided by Microsoft including C# (or Visual C#.NET) and Visual Basic.NET. It provides three data access technologies: ADO.NET, ADO, and OLE DB.

ADO.NET is the strategic application-level interface for providing data access services in the Microsoft .NET Platform. In practise, ADO.NET is a set of classes that expose data access services to the .NET programmer. ADO.NET supports a variety of development needs, including the creation of front-end database clients that have SQL embedded in them.

For applications written in native code, ADO provides a COM-based application-level interface for OLE DB data providers.

OLE DB is the strategic system-level programming interface for accessing data, and is the underlying technology for ADO as well as a source of data for ADO.NET. OLE DB is an open standard for accessing all kinds of data — both relational and non-relational data including: mainframe ISAM/VSAM and hierarchical databases; e-mail and file system stores; text, graphical, and geographical data; and custom business objects.

2.2. ADO.NET | Next | Previous | Go To Top |
ADO.NET classes were used to implement the prototype C#.NET database application used to investigate the concept of embedding SQL in C#.NET. ADO.NET provides several ways to access data.
Datasets which are in-memory cache of records are used to manipulate data while being disconnected from the data source. It uses XML-based persistence and transmission format which maximises data sharing between multiple sources and applications.
As an alternative, ADO.NET provides data commands and data readers to communicate directly with the data source. Direct database operations include running queries and stored procedures, creating database objects, and performing direct updates and deletes using DDL commands. Most of these operations can be performed using embedded SQL.

Data sources are accessed by ADO.NET using the .NET Framework data providers (described in the next section).

Table below presents a comparison between the two strategies for data access.

Data Access Strategies in C# required for embedding SQL in C#.NET database applications
Advantages of Dataset Advantages of staying connected
1. Can contain multiple tables of results and can work with the tables individually or navigate between them as parent-child tables. 1. Extra functionality such as executing DDL commands.
2. Dataset tables can store data from different sources such as different databases, from XML files, spreadsheets, etc., all in the same dataset. In the dataset, they can be manipulated in a homogeneous format. 2. More direct control over how and when an SQL statement or stored procedure is executed and what becomes of the results or return values.
3. Data can be easily moved between the different tiers of an application. 3. Reduced application overhead as records don’t need to be stored in memory as in datasets. Especially important in cases such as making a search and simply displaying the results.
4. Provides a powerful way to exchange data with other components of an application and with other applications. 4. Less programming in some instances such as web applications.
5. Data in a dataset is easy to bind with controls  
6. A dataset allows working with the same records repeatedly without requerying the database  
7. When working with a dataset, a class file can be generated that represents its structure as an object (a Customers class for a Customers table) making programming them easy.  

Recommendations for Accessing Data in C#.NET database applications:

C#.NET Web Forms database applications:
In general, use data commands. Because Web Forms pages and their controls and components are recreated each time the page makes a round trip, it often is not efficient to create and fill a dataset each time, unless data is to be cached between round trips.

Web Forms applications are good candidates for using embedded SQL statements.

C#.NET Windows Forms database applications:
In general, in a Windows Form, use a dataset. Windows Forms are typically used on rich clients where the form is not created and discarded (along with its data) with each user operation, as with Web Forms. Windows Forms applications also traditionally offer data-access scenarios that benefit from maintaining a cache of records, such as displaying records one by one in the form.

Specifically, use dataset under the following circumstances:

Use a data command under the following circumstances (embedded SQL may be implemented when using data commands):

2.3. The .NET Framework data providers | Next | Previous | Go To Top |
ADO.NET uses the .NET Framework data providers to access data sources. A data provider in the .NET Framework serves as a bridge between an application and a data source. A data provider is used to retrieve data from a data source and to reconcile changes to that data back to the data source.


.NET Framework data proividers, required for accessing a database/datasource
.NET Framework data provider Description
.NET Framework Data Provider for SQL Server For Microsoft® SQL Server™ version 7.0 or later.
.NET Framework Data Provider for OLE DB For data sources exposed using OLE DB.
.NET Framework Data Provider for ODBC For data sources exposed using ODBC.
.NET Framework Data Provider for Oracle For Oracle data sources.

The Connection, Command, DataReader, and DataAdapter objects represent the core classes of the .NET Framework data provider model that are used for embedding SQL in C#.NET database applications.
The following table describes these objects/classes.

Core classes of the .NET Framework data provider model that are required for embedding SQL
Object Description
Connection Establishes a connection to a specific data source.
Command Executes a command against a data source.
DataReader Reads a forward-only, read-only stream of data from a data source.
DataAdapter Populates a DataSet and resolves updates with the data source.

Along with the core classes listed in the preceding table, there are several other classes that are useful in embedding SQL in C#.NET database applications. These are listed below:

Useful classes when embedding SQL in C#.NET database applications
Object Description
Parameter Defines input, output, and return value parameters for commands and stored procedures. (More details provide later.)
Exception Returned when an error is encountered at the data source. For an error encountered at the client, .NET Framework data providers throw a .NET Framework exception.
Error Exposes the information from a warning or error returned by a data source.

 

2.4. Connecting to data source | Next | Previous | Go To Top |
One of the first things required for embedding SQL in C#.NET database applications is connecting to the datasource. Connection with a SQL Server data source is established in C# by passing a “connection string” that describes the data source, such as its location, as parameter to one of the SqlConnection class constructors (of two overloaded constructors). This is illustrated in the following lines of code.

private SqlConnection connection;
private string connectionString =
@"Server=(local);Database=Embedding_SQL_Test;User ID=sa;Password=123";
…
connection = new SqlConnection( connectionString );

A SqlConnection object represents a unique session to a SQL Server data source. It is used in conjunction with SqlDataAdapter and SqlCommand to increase performance when connecting to a Microsoft SQL Server database. For all third-party SQL server products, as well as other OLE DB-supported data sources, OleDbConnection must be used.

connection.Open();
connection.Close();

can be used to open and close a connection respectively. This class cannot be inherited.

2.5. Creating a Sql command | Next | Previous | Go To Top |
Embedded SQL queries are passed to a SQL Server database from a C#.NET database application in the form of Sql commands which are created using the SqlCommand constructor. One of the constructors (of four overloaded constructors) takes a string as the query or the name of a stored procedure and a SqlConnection object, as illustrated in the following lines of code.

SqlCommand cmd = new SqlCommand( "select * from Customer where CustomerID = @Cid", connection);

The SqlCommand class cannot be inherited. Two of its properties or public members are: CommandText, which can get or set the Transact-SQL statement or stored procedure to execute at the data source. E.g.,

myCommand.CommandText = "SELECT * FROM Categories ORDER BY CategoryID"

CommandType, which gets or sets a value indicating how the CommandText property is to be interpreted. It takes three values: StoredProcedure, Table Direct and Text. The default value is Text. Note that the .NET Framework Data Provider for SQL Server does not support the question mark (?) placeholder for passing parameters to a SQL Statement or a stored procedure called by a Command of CommandType.Text. In this case, named parameters must be used. For example:

SELECT * FROM Customers WHERE CustomerID = @CustomerID

2.6. Executing a Sql command | Next | Previous | Go To Top |
The SqlCommand class features the following methods for executing commands at a SQL Server database:


Methods used for executing embedded SQL in C#.NET database applications
Item Description
ExecuteReader Executes commands that return rows.
ExecuteNonQuery Executes commands such as Transact-SQL INSERT, DELETE, UPDATE, and SET statements.
ExecuteScalar Retrieves a single value (for example, an aggregate value) from a database.
ExecuteXmlReader Sends the CommandText to the Connection and builds an XmlReader object.

Below is a description of two of the above methods, frequently used in developing database applications in C#.NET that use embedded SQL.

Method ExecuteReader
ExecuteReader is used in C#.NET database applications to execute (embedded SQL) commands that return rows. Such a command can be a SQL Select statement or a stored procedure. ExecuteReader sends the CommandText (property of the SqlCommand object) to the Connection and builds a SqlDataReader. It may be used as in the following line of code:

//cmd is an SqlCommand object defined with a SELECT query
SqlDataReader reader = cmd.ExecuteReader();

The returned rows are stored in a SqlDataReader object. The SqlDataReader class provides a means of reading a forward-only stream of rows from a SQL Server database. This class cannot be inherited. To create a SqlDataReader the ExecuteReader method of the SqlCommand object must be called, rather than directly using a constructor. Only one SqlDataReader per associated SqlConnection may be open at a time, and any attempt to open another will fail until the first one is closed. Similarly, while the SqlDataReader is in use, the associated SqlConnection is busy serving it until Close is called. Read method of SqlDataReader advances the SqlDataReader to the next record. The default position of the SqlDataReader is prior to the first record. Therefore, Read must be called to begin accessing any data. E.g.:

while(reader.Read()) {

}

Return value is true if there are more rows; otherwise, false.

Method ExecuteNonQuery
When used in C#.NET database application, it executes a (embedded) Transact-SQL statement, such as UPDATE, INSERT, or DELETE, against the connection and returns the number of rows affected. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1. ExequteNonQuery does not return any rows. ExecuteNonQuery can be used to perform catalogue operations (for example, querying the structure of a database or creating database objects such as tables), or to change the data in a database without using a DataSet by executing UPDATE, INSERT, or DELETE statements.

 

2.7. Passing parameters to queries/commands | Next | Previous | Go To Top |
String.Format
There are several ways to pass parameters to embedded SQL queries in C#.NET database applications. The simplest way is to use the method String.Format which supports what is known as composite formatting, as illustrated in the code snippet below.

// A simple example.
string myName = "Fred";
String.Format("Name = {0}, hours = {1:hh}", myName, DateTime.Now);

// Bigger example
string qry = "INSERT INTO Customer (CustomerName, CustomerType, CreditLimit, Certificate, DepositWaiver, Address, TelNo)" + "VALUES ('{0}', '{1}', {2}, '{3}', '{4}','{5}','{6}')";

qry = string.Format( qry, tbINm.Text, cbType.Text, float.Parse(tbCredLim.Text),tbICert.Text, cbIDepWav.Text, tbIAdr.Text, tbITelno.Text );

SqlCommand command = new SqlCommand( qry, connection );

The .NET Framework composite formatting feature enables providing a list of values and a source string consisting of alternating fixed text and indexed placeholders, and easily obtaining a result string consisting of the original fixed text intermixed with formatted values.
Each indexed placeholder, or format item, corresponds to one element in a list of values. The composite formatting feature returns a new result string where each format item embedded in the source string is replaced by the corresponding formatted value.
The source string consists of zero or more runs of fixed text interrupted by one or more format items. The fixed text can contain any content.
Each format item takes the following form.

{index[,alignment][:formatString]}

Parameters property of the SqlCommand class
A more “formal” approach to sending parameters to embedded SQL queries in C#.NET database applications is to use the Parameters property of the SqlCommand class [the class used for passing embedded SQL queries from C#.NET applications to the datasource]. The Parameters property returns an object of type SqlParameterCollection which represents a collection of parameters relevant to a SqlCommand.
The SqlParameterCollection class provides method Add which actually has six overloaded versions. One of them adds a SqlParameter (described next) to the SqlParameterCollection given the specified parameter name and value. The index of the new SqlParameter object is returned. E.g.:

cmd.Parameters.Add("@clim",float.Parse(tbCredLimInPt.Text));

Another version simply adds the specified SqlParameter object to the SqlParameterCollection. It returns the index of the new SqlParameter object. E.g.:

cmd.Parameters.Add(param);

SqlParameter class
Yet another way of passing parameters to embedded SQL queries in C#.NET database applications is to create a parameter object with the SqlParameter class and then using the Parameters property (of SqlCommand) and the Add method as above to relate the parameter to the command. The SqlParameter class has several properties, some of them are described below, which can be set to define the parameter before passing it to the SqlCommand object. The parameter is then added to the list (of parameters or SqlParameterCollection) using the second Add method described above.

DbType: Gets or sets the DbType of the parameter. Specifying the type converts the value of the Parameter to the data provider Type before passing the value to the data source. If the type is not specified, ADO.NET infers the data provider Type of the Parameter from the Value property of the Parameter object.

ParameterName: Gets or sets the name of the SqlParameter.

Value: Gets or sets the value of the parameter.

Direction: Gets or sets a value indicating whether the parameter is input-only, output-only, bidirectional, or a stored procedure return value parameter.

These are used as in the following example:

SqlParameter param = new SqlParameter();
param.ParameterName = "@Cid";
int input = int.Parse(tbCID.Text);
param.Value = input;

// add new parameter to command object
cmd.Parameters.Add(param);

But some of these properties may be set when using the SqlParameter constructor to initialise a SqlParameter object. There are four overloaded constructors; of them,

E.g.:

SqlParameter param = new SqlParameter( "@CsID", SqlDbType.Int );

These three constructors are frequently used in C#.NET database applications that have SQL embedded in them.

2.8. Calling stored procedures | Next | Previous | Go To Top |
To call a stored procedure from a C#.NET database application, the CommandType property of the Command object must be set to StoredProcedure. E.g.:

cmd.CommandType = CommandType.StoredProcedure;

To specify the name of the stored procedure, CommandText property must be set to the name of the stored procedure. The name may also be specified while initialising the SqlCommand object as in the example below:

// InsertIntoHires is a stored proc in the database
SqlCommand cmd = new SqlCommand("InsertIntoTable", connection);

Input parameters may be defined/indicated as described in the previous section. An output parameter is defined by setting the Direction property of the SqlCommand object to Output as illustrated below.

SqlParameter param11 = new SqlParameter( "@DelvVehID", SqlDbType.Int );
param11.Direction = ParameterDirection.Output;
cmd.Parameters.Add(param11);

The name of the parameter passed to the SqlParameter constructor must be spelled exactly the same as in the stored procedure parameter.
When one of the Execute methods is called on the command, the specified stored procedure is executed.


3. Embedding SQL in Java database application | Go To Top | |
Java database applications use the classes and interfaces defined in the JDBC API (Application Programming Interface). The JDBC API provides classes and methods that connect to a data source, load the appropriate driver, send (embedded SQL) queries, retrieve results etc. This section describes all these in detail.


3.1. The JDBC API | Next | Previous | Go To Top |
The JDBC API is a Java API for accessing tabular data. The JDBC API consists of a set of classes and interfaces written in the Java programming language that provide a standard API for tool/database developers and makes it possible to write Java database applications using an all-Java API.

The JDBC API makes it easy to send embedded SQL statements to relational database systems and supports all dialects of SQL. But the JDBC 2.0 API goes beyond SQL, also making it possible to interact with other kinds of data sources, such as files containing tabular data.

The value of the JDBC API is that a Java application can access virtually any data source and run on any platform with a Java Virtual Machine.

In simplest terms, a JDBC technology-based driver ("JDBC driver” – normally provided by the database vendors or third parties) makes it possible to do three things:

  1. Enables a Java (database) application to establish a connection with a data source
  2. Send (embedded SQL) queries and update statements to the data source
  3. Process the results
Java Software provides three JDBC product components:

The primary function of the JDBC DriverManager class is to connect Java database applications to the correct JDBC driver and then get out of the way.

The JDBC driver test suite provides some confidence that JDBC drivers will run the program.

The JDBC-ODBC bridge allows ODBC drivers to be used as JDBC drivers. It provides a way to access some of the data sources for which there are no JDBC drivers.

3.2. Connecting to a data source | Next | Previous | Go To Top |
A Connection object represents a connection with a database. A connection session includes the embedded SQL statements that are executed and the results that are returned over that connection. A single Java database application can have one or more connections with a single database, or it can have connections with many different databases.

The traditional way to establish a connection with a database from a Java database application is to call the method DriverManager.getConnection. This method takes a string containing a URL (Uniform Resource Locator). The DriverManager class, referred to as the JDBC management layer, attempts to locate a driver that can connect to the database represented by that URL. The DriverManager class maintains a list of registered Driver classes, and when the method getConnection is called, it checks with each driver in the list until it finds one that can connect to the database specified in the URL. The Driver method Connect uses this URL to actually establish the connection.
The JDBC 2.0 Standard Extension API provides the DataSource interface as an alternative to the DriverManager for establishing a connection with a database from the Java database application.

A JDBC URL provides a way of identifying a data source so that the appropriate driver will recognize it and establish a connection with it. Driver writers are the ones who actually determine what the JDBC URL that identifies a particular driver will be. Users simply use the URL supplied with the drivers they are using.

The standard syntax for JDBC URLs used in Java database applications is shown here. It has three parts, which are separated by colons.

jdbc:<subprotocol>:<subname>

The three parts of a JDBC URL are broken down as follows:

  1. jdbc - the protocol. The protocol in a JDBC URL is always jdbc.
  2. <subprotocol> - the name of the driver or the name of a database connectivity mechanism, which may be supported by one or more drivers. A prominent example of a subprotocol name is odbc, which has been reserved for URLs (used by Java database applications to connect to a database) that specify ODBC-style data source names. For example, to access a database from a Java database applications through the JDBC-ODBC bridge, one might use a URL such as the following:

    jdbc : odbc : fred

    In this example, the subprotocol is odbc, and the subname fred is a local ODBC data source.
    The subprotocol odbc is a special case. It has been reserved for URLs that specify ODBC-style data source names and has the special feature of allowing any number of attribute values to be specified after the subname (the data source name). The full syntax for the odbc subprotocol is:

    jdbc:odbc:<data-source-name>[;<attribute-name>=<attribute-value>]

    A subprotocol can be registered by sending an email to jdbc@eng.sun.com

  3. <subname> - a way to identify the data source. The subname can vary, depending on the subprotocol, and it can have any internal syntax the driver writer chooses, including a subname. The point of a subname is to give enough information to locate the data source. In the previous example, fred is enough because ODBC provides the remainder of the information.
    If the subname is "FYP", the URL would be:

    jdbc : odbc : FYP

How to define the subname for the JDBC URL used in Java database applications: The sample code below illustrates how the material discussed above is used in practise.

private String DATABASE_URL = "jdbc:odbc:embedded_sql_app";
// establish connection to database
Connection connection = DriverManager.getConnection( DATABASE_URL,"sa","123" );

3.3. Driver Manager Class | Next | Previous | Go To Top |
The DriverManager class is the traditional management layer of the JDBC API (used by Java database applications), working between the user and the drivers. It keeps track of the drivers that are available and handles establishing a connection between a database and the appropriate driver. In addition, the DriverManager class attends to things like driver login time limits and the printing of log and tracing messages.

The DriverManager class maintains a list of Driver classes that have registered themselves by calling the method DriverManager.registerDriver. All Driver classes should be written with a static section (a static initializer) that creates an instance of the class and then registers it with the DriverManager class when it is loaded. Thus, a user would not normally call DriverManager.registerDriver directly; it should be called automatically by a Driver class when it is loaded. One way of loading a Driver class and therefore automatically registering it with the DriverManager is to call the method Class.forName. This explicitly loads the driver class. Since it does not depend on any external setup, this way of loading a driver is the recommended one for using the DriverManager framework. The following code loads the class acme.db.Driver:

Class.forName("acme.db.Driver");

If acme.db.Driver has been written so that loading it causes an instance to be created and also calls DriverManager.registerDriver with that instance as the parameter (as it should do), then it is in the DriverManager's list of drivers and available for creating a connection.
Once the Driver classes have been loaded and registered with the DriverManager class, they are available for establishing a connection with a database. When a request for a connection is made with a call to the DriverManager.getConnection method, the DriverManager tests each driver in turn to see if it can establish a connection.
The following code is an example of all that is normally needed to set up a connection with a driver such as a JDBC-ODBC bridge driver.

Class.forName("jdbc.odbc.JdbcOdbcDriver"); //loads the driver
String url = "jdbc:odbc:fred";
Connection con = DriverManager.getConnection( url, "userID", "passwd");

The variable con represents a connection to the data source "fred" that can be used to create and execute (embedded) SQL statements.

3.4. Querying the data source | Next | Previous | Go To Top |
The JDBC API (used by Java database applications) provides three interfaces for sending embedded SQL statements to the database, and corresponding methods in the Connection interface create instances of them. The interfaces for sending embedded SQL statements and the Connection methods that create them are as follows:

  1. Statement - created by the Connection.createStatement methods. A Statement object is used for sending embedded SQL statements with no parameters.
  2. PreparedStatement - created by the Connection.prepareStatement methods. A PreparedStatement object is used for precompiled SQL statements. These can take one or more parameters as input arguments (IN parameters). PreparedStatement has a group of methods that set the value of IN parameters, which are sent to the database from the Java database application, when the statement is executed. PreparedStatement extends Statement and therefore includes Statement methods. A PreparedStatement object has the potential to be more efficient than a Statement object because it has been precompiled and stored for future use. Therefore, in order to improve performance, a PreparedStatement object is sometimes used for an (embedded) SQL statement that is executed many times.
  3. CallableStatement - created by the Connection.prepareCall methods. CallableStatement objects are used to execute SQL stored procedures from Java database applications. A CallableStatement object inherits methods for handling IN parameters from PreparedStatement; it adds methods for handling OUT and INOUT parameters.

Statement
The Statement interface provides three different methods for executing embedded SQL statements: executeQuery, executeUpdate, and execute. The correct method to use is determined by what the embedded SQL statement produces.
The method executeQuery is designed for embed SQL statements that produce a single result set, such as SELECT statements.

The method executeUpdate is used to execute INSERT, UPDATE, or DELETE statements and also SQL DDL (Data Definition Language) statements like CREATE TABLE, DROP TABLE, and ALTER TABLE. The return value of executeUpdate is an integer (referred to as the update count) that indicates the number of rows that were affected by the embedded SQL statement sent to the database from the Java database application. For statements such as CREATE TABLE or DROP TABLE, which do not operate on rows, the return value of executeUpdate is always zero.

The method execute is used to execute embedded SQL statements that return more than one result set, more than one update count, or a combination of the two. It is an advanced feature that the majority of programmers will never use.
Once a connection to a particular database is established from a Java database application, that connection can be used to send embedded SQL statements. A Statement object is created with the Connection method createStatement, as in the following code fragment:

Connection con = DriverManager.getConnection(url, "sunny", "");
Statement stmt = con.createStatement();

The embedded SQL statement that will be sent to the database is supplied as the argument to one of the execute methods on a Statement object. This is demonstrated in the following example, which uses the method executeQuery:

ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table2");

ResultSet objects are used to hold rows returned by a embedded SQL query and are explained in a later section.

PreparedStatement
The PreparedStatement interface inherits from Statement and differs from it in two ways:

  1. Instances of PreparedStatement contain an embedded SQL statement that has already been compiled. This is what makes a statement "prepared."
  2. The embedded SQL statement contained in a PreparedStatement object may have one or more IN parameters. An IN parameter is a parameter whose value is not specified when the SQL statement is created. Instead, the statement has a question mark ("?") as a placeholder for each IN parameter. The "?" is also known as a parameter marker. A Java database application must set a value for each question mark in a prepared statement before executing the prepared statement.

Also, note that the PreparedStatement interface, which inherits all of the methods in the Statement interface, has its own versions of the methods executeQuery, executeUpdate and execute. Statement objects do not themselves contain an SQL statement; therefore, one must be provided as the argument to the Statement.execute methods. PreparedStatement objects do not supply an SQL statement as a parameter to these methods because they already contain a precompiled SQL statement. CallableStatement objects inherit the PreparedStatement forms of these methods. Supplying a parameter to the PreparedStatement or CallableStatement versions of these methods will cause an SQLException to be thrown.

Because PreparedStatement objects are precompiled, their execution can be faster than that of Statement objects. Consequently, an embedded SQL statement that is executed many times is often created as a PreparedStatement object to increase efficiency.

The following code fragment, where con is a Connection object, creates a PreparedStatement object containing an SQL update statement with two placeholders for IN parameters:

PreparedStatement pstmt = con.prepareStatement( "UPDATE table4 SET m = ? WHERE x = ?");

The object pstmt now contains the statement "UPDATE table4 SET m = ? WHERE x = ?", which has already been sent to the DBMS and been prepared for execution.

Before a Java database application executes a PreparedStatement object, the value of each ? parameter must be set. This is done by calling a setXXX method, where XXX is the appropriate type for the parameter. For example, if the parameter is of type long in the Java programming language, the method to use is setLong. The first argument to the setXXX methods is the ordinal position of the parameter to be set, with numbering starting at 1. The second argument is the value to which the parameter is to be set. For example, the following code sets the first parameter to 123456789 and the second parameter to 100000000:

pstmt.setLong(1, 123456789);
pstmt.setLong(2, 100000000);

Once a parameter value has been set for a given statement, it can be used for multiple executions of that statement until it is cleared by a call to the method clearParameters or until a new value is set.

The XXX in a setXXX method is a type in the Java programming language. It also implicitly specifies a JDBC type because the driver will map the Java type to its corresponding JDBC type (following the mapping specified in Appendix A, Table 1: Java Types Mapped to JDBC Types) and send that JDBC type to the database.

A Java database application developer can explicitly convert an input parameter to a particular JDBC type by using the method setObject. This method can take a third argument, which specifies the target JDBC type. The driver will convert the Object in the Java programming language to the specified JDBC type before sending it to the database.

Callable Statement
A CallableStatement object provides a way for Java database applications to call stored procedures in a standard way for all RDBMSs. The call is written in an escape syntax that may take one of two forms: one form with a result parameter, and the other without one. A result parameter, a kind of OUT parameter, is the return value for the stored procedure. Both forms may have a variable number of parameters used for input (IN parameters), output (OUT parameters), or both (INOUT parameters). A question mark serves as a placeholder for a parameter.

The syntax for invoking a stored procedure using the JDBC API (used by Java database applications) is shown here. Note that the square brackets indicate that what is between them is optional; they are not themselves part of the syntax.

{call procedure_name[(?, ?, ...)]}

The syntax for a procedure that returns a result parameter is:

{? = call procedure_name[(?, ?, ...)]}

The syntax for a stored procedure with no parameters would look like this:

{call procedure_name}

CallableStatement inherits Statement methods, which deal with embedded SQL statements in general, and it also inherits PreparedStatement methods, which deal with IN parameters. All of the methods defined in CallableStatement deal with OUT parameters or the output aspect of INOUT parameters: registering the JDBC types of the OUT parameters, retrieving values from them, or checking whether a returned value was JDBC NULL.
CallableStatement objects are created with the Connection method prepareCall. The following example, in which con is an active JDBC Connection object, creates an instance of CallableStatement.

CallableStatement cstmt = con.prepareCall(
"{call getTestData(?, ?)}");

The variable cstmt contains a call to the stored procedure getTestData, which has two input parameters and no result parameter.

Passing in any IN parameter values to a CallableStatement object is done using the setXXX methods inherited from PreparedStatement. The type of the value being passed in determines which setXXX method to use.

If the stored procedure returns OUT parameters, the JDBC type of each OUT parameter must be registered before the CallableStatement object can be executed. This is necessary because some DBMSs require the SQL type (which the JDBC type represents). JDBC types, a set of generic SQL type identifiers that represent the most commonly used SQL types, is presented in Appendix B.

Registering the JDBC type is done with the method registerOutParameter. Then after the statement has been executed, CallableStatement's getXXX methods can be used to retrieve OUT parameter values. The correct CallableStatement.getXXX method to use is the type in the Java programming language that corresponds to the JDBC type registered for that parameter. (The standard mapping from JDBC types to Java types is shown in Appendix 1, Table 2: JDBC Types Mapped to Java Types) In other words, registerOutParameter uses a JDBC type (so that it matches the data type that the database will return), and getXXX casts this to a Java type.

To illustrate, the following code registers the OUT parameters, executes the stored procedure called by cstmt, and then retrieves the values returned in the OUT parameters. The method getByte retrieves a Java byte from the first OUT parameter, and getBigDecimal retrieves a java.math.BigDecimal object (with three digits after the decimal point) from the second OUT parameter. The method executeQuery is used to execute cstmt because the stored procedure that it calls returns a result set (explained in the next section).

CallableStatement cstmt = con.prepareCall( "{call getTestData(?, ?)}");
cstmt.registerOutParameter(1, java.sql.Types.TINYINT);
cstmt.registerOutParameter(2, java.sql.Types.DECIMAL, 3);
ResultSet rs = cstmt.executeQuery();
// . . . retrieve result set values with rs.getXXX methods
byte x = cstmt.getByte(1);
java.math.BigDecimal n = cstmt.getBigDecimal(2);

When a method takes an int specifying which parameter to act upon (setXXX, getXXX, and registerOutParameter), that int refers to ? placeholder parameters only, with numbering starting at one. The parameter number does not refer to literal parameters that might be supplied to a stored procedure call. For example, the following code fragment illustrates a stored procedure call with one literal parameter and one ? parameter:

CallableStatement cstmt = con.prepareCall( "{call getTestData(25, ?)}");
cstmt.registerOutParameter(1, java.sql.Types.TINYINT);

In this code, the first argument to registerOutParameter, the int 1, refers to the first ? parameter (and in this case, the only ? parameter). It does not refer to the literal 25, which is the first parameter to the stored procedure.

For maximum portability of the Java database application, it is recommended that all of the results in a ResultSet object generated by the execution of a CallableStatement object should be retrieved before OUT parameters are retrieved.

3.5. ResultSet Object | Next | Previous | Go To Top |
A ResultSet is a Java object that contains the results of executing an embedded SQL query. The data stored in a ResultSet object is retrieved through a set of get methods that allows access to the various columns of the current row. The ResultSet.next method is used to move to the next row of the ResultSet, making it the current row.

The following code fragment returns a collection of rows, with column a as an int, column b as a String, and column c as a float:

java.sql.Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table1");

while (rs.next()) {
// retrieve and print the values for the current row
int i = rs.getInt("a");
String s = rs.getString("b");
float f = rs.getFloat("c");
System.out.println("ROW = " + i + " " + s + " " + f);
}

When a ResultSet object is first created by the Java database application, its cursor is positioned before the first row, so the first call to the next method puts the cursor on the first row, making it the current row. ResultSet rows can be retrieved in sequence from top to bottom. This ability to move its cursor only forward is the default behaviour for a ResultSet and is the only cursor movement possible with drivers that implement only the JDBC 1.0 API. This kind of result set is referred to as a forward only result set.

A scrollable result which can be implemented with the JDBC 2.0 core API can move the cursor both forward and backward as well as to a particular row.

When a cursor is positioned on a row in a ResultSet object (not before the first row or after the last row), that row becomes the current row. This means that any methods called while the cursor is positioned on that row will (1) operate on values in that row (methods such as getXXX), (2) operate on the row as a whole, or (3) use that row as a starting point for moving to other rows.

A cursor remains valid until the ResultSet object or its parent Statement object is closed.
Either the column name or the column number can be used to designate the column from which to retrieve data. Columns are numbered from left to right starting with column 1. Also, column names used as input to getXXX methods are case insensitive.

JDBC drivers support type coercion. When a getXXX method is invoked, the driver attempts to convert the underlying data to the type XXX in the Java programming language and then returns a suitable value. For example, if the getXXX method is getString, and the data type of the data in the underlying database is VARCHAR, the JDBC Compliant driver will convert the VARCHAR value to a String object in the Java programming language. That String object will be the value returned by getString. Appendix C provides a table containing type conversions by ResultSet.getXXX Methods.

The method getObject will retrieve any data type.

Results sets may have different levels of functionality. For example, they may be scrollable or nonscrollable. Also, result sets may be sensitive or insensitive to changes made while they are open; that is, they may or may not reflect changes to column values that are modified in the database. A Java database application developer should always keep in mind the fact that adding capabilities to a ResultSet object incurs additional overhead, so it should be done only as necessary.

Because PreparedStatement and CallableStatement objects inherit the methods defined in the Statement interface, they, too, can create different types of ResultSet objects.


4. Manipulating data in a data source programmatically (without SQL) | Go To Top | |
Instead of using embedded SQL queries, data in a database may be modified using the different class methods provided by the languages C# and Java. The two languages implement this concept in different ways which are described below in detail.

 

4.1. Accessing data without SQL in C#.NET database application | Next | Previous | Go To Top |
In C#.NET database applications, Dataset objects are used to hold the data that is to be manipulated. DataSets store data in a disconnected cache. The structure of a dataset is similar to that of a relational database; it exposes a hierarchical object model of tables, rows, and columns. In addition, it contains constraints and relationships defined for the dataset.

In Visual Studio and the .NET Framework, XML is the format for storing and transmitting data of all kinds. As such, datasets have a close affinity with XML which provides advantages such as being able to read an XML document or stream into a dataset and write a dataset out as XML.

A dataset is a container; therefore, it must be filled with data. When a dataset is populated, various events are raised, constraint checking applies, and so on. One way of populating a dataset is to call the Fill method of a data adapter. This causes the adapter to execute an SQL statement or stored procedure and fill the results into a table in the dataset.

Because a dataset is a fully disconnected container for data, datasets (unlike ADO recordsets) do not need or support the concept of a current record. Instead, all records in the dataset are available. Because there is no current record, there is no specific property that points to a current record and there are no methods or properties for moving from one record to another.

But one can access individual tables in the dataset as objects; each table exposes a collection of rows. This collection of rows may be treated like any collection, accessing rows via the collection's index or using collection-specific statements in C#.

A dataset has no inherent knowledge of any relationships between tables in a dataset; to work with data in related tables, therefore, DataRelation objects can be created that describe the relations between the tables in the dataset. DataRelation objects can be used to programmatically fetch related child records for a parent record, and a parent record from a child record.

As in most databases, datasets support constraints as a way to ensure the integrity of data. Two types of constraints may be defined:

In a dataset, constraints are associated with individual tables (foreign-key constraints) or columns (a unique constraint). Constraints are implemented as objects of type UniqueConstraint or ForeignKeyConstraint. They are then added to a table's Constraints collection.
When changes are made to records in the dataset, the changes have to be written back to the database. To write changes from the dataset to the database, the Update method of the DataAdapter is called that communicates between the dataset and its corresponding data source.

DataAdapter object
The .NET Framework Data Provider for SQL Server includes a SqlDataAdapter object, the provider for OLE DB includes an OleDbDataAdapter object, and the provider for ODBC includes an OdbcDataAdapter object. A DataAdapter is used to retrieve data from a data source and populate tables within a DataSet. The DataAdapter also resolves changes made to the DataSet back to the data source. The DataAdapter uses the Connection object of the .NET Framework data provider to connect to a data source and Command objects to retrieve data from and resolve changes to the data source.

The SelectCommand property of the DataAdapter is a Command object that retrieves data from the data source. The InsertCommand, UpdateCommand, and DeleteCommand properties of the DataAdapter are Command objects that manage updates to the data in the data source according to modifications made to the data in the DataSet.
The Fill method of the DataAdapter is used to populate a DataSet with the results of the SelectCommand of the DataAdapter. Fill takes as its arguments a DataSet to be populated, and a DataTable object, or the name of the DataTable to be filled with the rows returned from the SelectCommand.

The Fill method uses the DataReader object implicitly to return the column names and types used to create the tables in the DataSet, as well as the data to populate the rows of the tables in the DataSet. Tables and columns are only created if they do not already exist; otherwise Fill uses the existing DataSet schema. Column types are created as .NET Framework types according to the mapping .NET Data Provider Data Types to .NET Framework Data Types rules. Primary keys are not created unless they exist in the data source and DataAdapter.MissingSchemaAction is set to MissingSchemaAction.AddWithKey. If Fill finds that a primary key exists for a table, it will overwrite data in the DataSet with data from the data source for rows where the primary key column values match those of the row returned from the data source. If no primary key is found, the data is appended to the tables in the DataSet. Fill uses any TableMappings that may exist when populating the DataSet.

Additionally, the Fill method implicitly opens the Connection that the DataAdapter is using if it finds that the connection is not already open. If Fill opened the connection, it will also close the connection when Fill is finished. However, if performing multiple operations that require an open connection, performance can improved by explicitly calling the Open method of the Connection, performing the operations against the data source, then calling the Close method of the Connection.

Example use of DataAdapter and DataSet:

SqlCommand selectCMD = new SqlCommand("SELECT CustomerID, CompanyName FROM Customers", nwindConn);

SqlDataAdapter custDA = new SqlDataAdapter();
custDA.SelectCommand = selectCMD;

nwindConn.Open();

DataSet custDS = new DataSet();
custDA.Fill(custDS, "Customers");

nwindConn.Close();

Additional classes that are necessary to manipulate DataSets:

DataTable
DataTable objects can store tables from DataSet objects. The names of DataTable objects are conditionally case-sensitive. For example, if one DataTable is named "mydatatable" and another is named "Mydatatable", a string used to search for one of the tables is regarded as case-sensitive. However, if "mydatatable" exists and "Mydatatable" does not, the search string is regarded as case-insensitive.

DataSet.Tables [TableName or Index] - returns the table in the dataset.

The DataRow and DataColumn objects are primary components of a DataTable.

Method dataTable.Rows gets the collection of rows that belong to this table. It returns a DataRowCollection that contains DataRow objects; otherwise a null value if no DataRow objects exist.

DataRow
Represents a row of data in a DataTable. The DataRow class includes the RowState property, whose values indicate whether and how the row has been changed since the data table was first loaded from the database. Possible values include Deleted, Modified, New, and Unchanged. The Update method examines the value of the RowState property to determine which records need to be written to the database and what specific database command (add, edit, delete) should be invoked.

4.2. Accessing data without SQL in Java database application | Next | Previous | Go To Top |
In Java, rows returned by a SELECT query that are stored in a ResultSet object may be updated using the updatexxx method of the ResultSet interface provided by the JDBC 2.0 API. These changes can then be reflected in the database.

 

Whether a ResultSet is updatable is indicated by specifying the either of the constants CONCUR_READ_ONLY and CONCUR_UPDATABLE in the Connection method createStatement (or prepareStatement or prepareCall). The following code fragment creates a resultset object that is updatable (by specifying ResultSet.CONCUR_UPDATABLE) as well as scrollable (by specifying ResultSet.TYPE_SCROLL_SENSITIVE);

 

Statement stmt = con.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

ResultSet rs = stmt.executeQuery("SELECT EMP_NO, SALARY FROM EMPLOYEES");

As stated previously, there is a cost to making a result set scrollable or updatable, so it is good practice to create result sets with these features only when they are needed.

The updateXXX methods take two parameters, the first to indicate which column is to be updated, and the second to give the value to assign to the specified column. The column index used with ResultSet methods refers to the column number in the result set, not the column number in the database table, which might well be different.

The updateXXX methods update a value in the current row of the result set, but they do not update the value in the underlying database table. It is the method updateRow that updates the database. It is very important that the updateRow method be called while the cursor is still on the current row (the row to be updated). In fact, if an application moves the cursor before it calls updateRow, the driver must discard the update, and neither the result set nor the database will be updated.

The following example demonstrates updating the second and third columns in the current row of the ResultSet object rs. Next the method updateString is called to change the value in the second column of rs to 321 Kasten. The method updateFloat changes the value in the third column of rs to 10101.0. Finally, the method updateRow is called to update the row in the database that contains the two modified column values.

rs.absolute(4);
rs.updateString(2, "321 Kasten");
rs.updateFloat(3, 10101.0f);
rs.updateRow();

The JDBC 2.0 API provides the method deleteRow so that a row in a ResultSet object can be deleted using only methods in the Java programming language. This method deletes the current row, so before calling deleteRow, an application must position the cursor on the row it wants to delete. Unlike the updateXXX methods, which affect only a row in the result set, this method affects both the current row in the result set and the underlying row in the database.

New rows may be inserted into a result set table and into the underlying database table using new methods in the JDBC 2.0 core API. To make this possible, the API defines the concept of an insert row. This is a special row, associated with the result set but not part of it, that serves as a staging area for building the row that is to be inserted. To access the insert row, an application calls the ResultSet method moveToInsertRow, which positions the cursor on the insert row. Then it calls the appropriate updateXXX methods to add column values to the insert row. When all of the columns of the row to be inserted have been set, the application calls the method insertRow. This method adds the insert row to both the result set and the underlying database simultaneously. Finally, the application needs to position the cursor on a row back in the result set.

The following code fragment demonstrates these steps for inserting a row from an application written in the Java programming language.

rs.moveToInsertRow();
rs.updateFloat (1, 2.20f);
rs.updateInt(2, 3857);
rs.updateString(3, "Mysteries");
rs.insertRow();
rs.first(); // moves cursor to first row



5. Appendix A | Next | Previous | Go To Top | |

Table 1: Java Types Mapped to JDBC Types
Java Type JDBC Type
String CHAR, VARCHAR, or LONGVARCHAR
java.math.BigDecimal NUMERIC
boolean BIT
byte TINYINT
short SMALLINT
int INTEGER
long BIGINT
float REAL
double DOUBLE
byte[] BINARY, VARBINARY, or LONGVARBINARY
jva.sql.Date DATE
java.sql.Time TIME
java.sql.Timestamp TIMESTAMP
Clob CLOB
Blob BLOB
Array ARRAY
Struct STRUCT
Ref REF
Java class JAVA_OBJECT

Table 2: JDBC Types Mapped to Java Types
JDBC Type Java Type
CHAR String
VARCHAR String
LONGVARCHAR String
NUMERIC java.math.BigDecimal
DECIMAL java.math.BigDecimal
BIT boolean
TINYINT byte
SMALLINT short
INTEGER int
BIGINT long
REAL float
FLOAT double
DOUBLE double
BINARY byte[]
VARBINARY byte[]
LONGVARBINARY byte[]
DATE java.sql.Date
TIME java.sql.Time
TIMESTAMP java.sql.Timestamp
CLOB Clob
BLOB Blob
ARRAY Array
DISTINCT mapping of underlying type
STRUCT Struct
REF Ref
JAVA_OBJECT underlying Java class

6. Appendix B | Next | Previous | Go To Top | |
Table below presents the default mappings between the basic SQL Server, JDBC, and Java programming language data types.

Table: Default mappings between the basic SQL Server, JDBC, and Java programming language data types
SQL Server Types JDBC Types (java.sql.Types) Java Language Types
Bigint BIGINT long
timestamp
binary		 BINARY byte[]
Bit BIT Boolean
char
nchar
 CHAR String
decimal
money
smallmoney		 DECIMAL java.math.BigDecimal
Float DOUBLE double
Int INTEGER int
image LONGVARBINARY byte[]
text
ntext		 LONGVARCHAR
 String
numeric NUMERIC java.math.BigDecimal
real REAL float
smallint
tinyint
 SMALLINT short
datetime
smalldatetime
 TIMESTAMP java.sql.Timestamp
varbinary VARBINARY byte[]
varchar
nvarchar
uniqueidentifier
 VARCHAR String

7. Appendix C | Next | Previous | Go To Top | |

Table: Conversions by ResultSet.getXXX Methods from JDBC Types (to Java Types)
Table: Conversions by ResultSet.getXXX Methods from JDBC Types (to Java Types)

An "x" means that the method can retrieve the JDBC type. An "X" means that the method is recommended for retrieving the JDBC type.

8. References | Next | Previous | Go To Top | |
The MSDN document can be found by entering the string in quotes in the MSDN search facility. Same with SQL Server Books Online.

  1. MSDN: “String.Format Method”

  2. MSDN: “SqlParameterCollection Class”

  3. MSDN: “Using Stored Procedures with a Command”

  4. MSDN: “ADO.NET DataSet”

  5. MSDN: “Creating And Using DataSet”

  6. MSDN: “Creating A DataSet”

  7. MSDN: “DataTable.Rows Property ”

  8. MSDN: “DataTable Members”

  9. MSDN: “DataTable Class”

  10. MSDN “Executing SQL Command”

  11. MSDN: “SQLCommand Class”

  12. MSDN: “Using .NET Framework Data Providers to Access Data”

  13. MSDN: “Recommendations for Data Access Strategies”

  14. MSDN: “Sql Connection Class”

  15. MSDN: “SqlDataReader Class”

  16. MSDN: “SqlDataReader.Read Method”

  17. MSDN: “Populating a DataSet from DataAdapter”

  18. Webpage: The C# Station ADO.Net Tutorial

  19. j2sdk-1_4_2-doc/docs/guide/jdbc/getstart/intro.html#1014182

  20. j2sdk-1_4_2-doc/docs/guide/jdbc/getstart/connection.html#1001869

  21. j2sdk-1_4_2-doc/docs/guide/jdbc/getstart/drivermanager.html#999674

  22. j2sdk-1_4_2-doc/docs/guide/jdbc/getstart/statement.html#1000062

  23. j2sdk-1_4_2-doc/docs/guide/jdbc/getstart/resultset.html

  24. j2sdk-1_4_2-doc/docs/guide/jdbc/getstart/callablestatement.html#999652

  25. j2sdk-1_4_2-doc/docs/guide/jdbc/getstart/preparedstatement.html#1000039

  26. SQL Server Books Online: “Case Sensitivity”

9. Bibliography | Previous | Go To Top | |

http://www.itjungle.com/mpo/mpo042502-story01.html
Provides a good description/definition of what is embedding SQL and how SQL is embedded in COBOL.

http://www.lsbu.ac.uk/oracle/oracle7/server/doc/PLS23/preface.htm#toc003
Describes PL/SQL which is “Oracle Corporation's procedural language extension to SQL”.

http://www.oreillynet.com/pub/a/oreilly/java/news/whyjava_0600.html
A description of the Java language and why use java.

http://www.jelovic.com/articles/why_java_is_slow.htm
a comparison between Java and C++.

http://www.phptr.com/articles/article.asp?p=29659&rl=1
A description of the C# language.

http://www.code-magazine.com/Article.aspx?quickid=0607081
A description of “Database Concurrency Conflicts in the Real World”

http://publib.boulder.ibm.com/infocenter/wasinfo/v6r0/index.jsp?topic=/com.ibm.websphere.express.doc/info/exp/ae/cejb_cncr.htm
 Different concurrency controls.

http://en.wikipedia.org/wiki/Optimistic_concurrency_control
Discussion on Optimistic concurrency control

 

| Contact Shahriar | Research (Embedding SQL) | Bonus (Winamp Skin) |