SADataAdapter .net (source SQL Anywhere)

The SADataAdapter retrieves the result set into a DataSet. A DataSet is a collection of tables and the relationships and constraints between those tables. The DataSet is built into the .NET Framework, and is independent of the Data Provider used to connect to your database.

When you use the SADataAdapter, you must be connected to the database to fill the DataSet and to update the database with changes made to the DataSet. However, once the DataSet is filled, you can modify the DataSet while disconnected from the database.

If you do not want to apply your changes to the database right away, you can write the DataSet, including the data and/or the schema, to an XML file using the WriteXML method. Then, you can apply the changes at a later time by loading a DataSet with the ReadXML method.

For more information, see the .NET Framework documentation for WriteXML and ReadXML.

When you call the Update method to apply changes from the DataSet to the database, the SADataAdapter analyzes the changes that have been made and then invokes the appropriate commands, INSERT, UPDATE, or DELETE, as necessary. When you use the DataSet, you can only make changes (inserts, updates, or deletes) to data that is from a single table. You cannot update result sets that are based on joins. If another user has a lock on the row you are trying to update, an exception is thrown.

Caution
Any changes you make to the DataSet are made while you are disconnected. This means that your application does not have locks on these rows in the database. Your application must be designed to resolve any conflicts that may occur when changes from the DataSet are applied to the database in the event that another user changes the data you are modifying before your changes are applied to the database.


Resolving conflicts when using the SADataAdapter
When you use the SADataAdapter, no locks are placed on the rows in the database. This means there is the potential for conflicts to arise when you apply changes from the DataSet to the database. Your application should include logic to resolve or log conflicts that arise.

Some of the conflicts that your application logic should address include:

Unique primary keys If two users insert new rows into a table, each row must have a unique primary key. For tables with autoincrement primary keys, the values in the DataSet may become out of sync with the values in the data source.

For information about obtaining primary key values for autoincrement primary keys, see Obtaining primary key values.

Updates made to the same value If two users modify the same value, your application should include logic to determine which value is correct.

Schema changes If a user modifies the schema of a table you have updated in the DataSet, the update will fail when you apply the changes to the database.

Data concurrency Concurrent applications should see a consistent set of data. The SADataAdapter does not place a lock on rows that it fetches, so another user can update a value in the database once you have retrieved the DataSet and are working offline.

Many of these potential problems can be avoided by using the SACommand, SADataReader, and SATransaction objects to apply changes to the database. The SATransaction object is recommended because it allows you to set the isolation level for the transaction and it places locks on the rows so that other users cannot modify them.

For more information about using transactions to apply your changes to the database, see Inserting, updating, and deleting rows using the SACommand object.

To simplify the process of conflict resolution, you can design your INSERT, UPDATE, or DELETE statement to be a stored procedure call. By including INSERT, UPDATE, and DELETE statements in stored procedures, you can catch the error if the operation fails. In addition to the statement, you can add error handling logic to the stored procedure so that if the operation fails the appropriate action is taken, such as recording the error to a log file, or trying the operation again.

To insert rows into a table using the SADataAdapter
Declare and initialize an SAConnection object.

SAConnection conn = new SAConnection(
c_connStr );Open the connection.

conn.Open();Create a new SADataAdapter object.

SADataAdapter adapter = new SADataAdapter();
adapter.MissingMappingAction =
MissingMappingAction.Passthrough;
adapter.MissingSchemaAction =
MissingSchemaAction.Add;Create the necessary SACommand objects and define any necessary parameters.

The following code creates a SELECT and an INSERT command and defines the parameters for the INSERT command.

adapter.SelectCommand = new SACommand(
"SELECT * FROM Departments", conn );
adapter.InsertCommand = new SACommand(
"INSERT INTO Departments( DepartmentID, DepartmentName )
VALUES( ?, ? )", conn );
adapter.InsertCommand.UpdatedRowSource =
UpdateRowSource.None;
SAParameter parm = new SAParameter();
parm.SADbType = SADbType.Integer;
parm.SourceColumn = "DepartmentID";
parm.SourceVersion = DataRowVersion.Current;
adapter.InsertCommand.Parameters.Add(
parm );
parm = new SAParameter();
parm.SADbType = SADbType.Char;
parm.SourceColumn = "DepartmentName";
parm.SourceVersion = DataRowVersion.Current;
adapter.InsertCommand.Parameters.Add( parm );Fill the DataTable with the results of the SELECT statement.

DataTable dataTable = new DataTable( "Departments" );
int rowCount = adapter.Fill( dataTable );Insert the new rows into the DataTable and apply the changes to the database.

DataRow row1 = dataTable.NewRow();
row1[0] = 600;
row1[1] = "Eastern Sales";
dataTable.Rows.Add( row1 );
DataRow row2 = dataTable.NewRow();
row2[0] = 700;
row2[1] = "Western Sales";
dataTable.Rows.Add( row2 );
recordsAffected = adapter.Update( dataTable );Display the results of the updates.

dataTable.Clear();
rowCount = adapter.Fill( dataTable );
dataGrid.DataSource = dataTable;Close the connection.

conn.Close();To update rows using the SADataAdapter object
Declare and initialize an SAConnection object.

SAConnection conn = new SAConnection( c_connStr );Open the connection.

conn.Open();Create a new SADataAdapter object.

SADataAdapter adapter = new SADataAdapter();
adapter.MissingMappingAction =
MissingMappingAction.Passthrough;
adapter.MissingSchemaAction =
MissingSchemaAction.Add;Create an SACommand object and define its parameters.

The following code creates a SELECT and an UPDATE command and defines the parameters for the UPDATE command.

adapter.SelectCommand = new SACommand(
"SELECT * FROM Departments WHERE DepartmentID > 500",
conn );
adapter.UpdateCommand = new SACommand(
"UPDATE Departments SET DepartmentName = ?
WHERE DepartmentID = ?", conn );
adapter.UpdateCommand.UpdatedRowSource =
UpdateRowSource.None;
SAParameter parm = new SAParameter();
parm.SADbType = SADbType.Char;
parm.SourceColumn = "DepartmentName";
parm.SourceVersion = DataRowVersion.Current;
adapter.UpdateCommand.Parameters.Add( parm );
parm = new SAParameter();
parm.SADbType = SADbType.Integer;
parm.SourceColumn = "DepartmentID";
parm.SourceVersion = DataRowVersion.Original;
adapter.UpdateCommand.Parameters.Add( parm );Fill the DataTable with the results of the SELECT statement.

DataTable dataTable = new DataTable( "Departments" );
int rowCount = adapter.Fill( dataTable );Update the DataTable with the updated values for the rows and apply the changes to the database.

foreach ( DataRow row in dataTable.Rows )
{
row[1] = ( string ) row[1] + "_Updated";
}
recordsAffected = adapter.Update( dataTable );Bind the results to the grid on the screen.

dataTable.Clear();
adapter.SelectCommand.CommandText =
"SELECT * FROM Departments";
rowCount = adapter.Fill( dataTable );
dataGrid.DataSource = dataTable;Close the connection.

conn.Close();To delete rows from a table using the SADataAdapter object
Declare and initialize an SAConnection object.

SAConnection conn = new SAConnection( c_connStr );Open the connection.

conn.Open();Create an SADataAdapter object.

SADataAdapter adapter = new SADataAdapter();
adapter.MissingMappingAction =
MissingMappingAction.Passthrough;
adapter.MissingSchemaAction =
MissingSchemaAction.AddWithKey;Create the required SACommand objects and define any necessary parameters.

The following code creates a SELECT and a DELETE command and defines the parameters for the DELETE command.

adapter.SelectCommand = new SACommand(
"SELECT * FROM Departments WHERE DepartmentID > 500",
conn );
adapter.DeleteCommand = new SACommand(
"DELETE FROM Departments WHERE DepartmentID = ?",
conn );
adapter.DeleteCommand.UpdatedRowSource =
UpdateRowSource.None;
SAParameter parm = new SAParameter();
parm.SADbType = SADbType.Integer;
parm.SourceColumn = "DepartmentID";
parm.SourceVersion = DataRowVersion.Original;
adapter.DeleteCommand.Parameters.Add( parm );Fill the DataTable with the results of the SELECT statement.

DataTable dataTable = new DataTable( "Departments" );
int rowCount = adapter.Fill( dataTable );Modify the DataTable and apply the changes to the database.

for each ( DataRow in dataTable.Rows )
{
row.Delete();
}
recordsAffected = adapter.Update( dataTable )Bind the results to the grid on the screen.

dataTable.Clear();
rowCount = adapter.Fill( dataTable );
dataGrid.DataSource = dataTable;Close the connection.

conn.Close();

JDBC

JDBC
The Java Database Connectivity API (JDBC) is used to connect Java programs with databases.

The API provides for a call level API for SQL based database access. It allows you to establish a connection with a database, send SQL statements, and process the results.

The JDBC API consists of two major sets of interfaces; the first is for application writers and the second is the JDBC driver for driver writers.

The connection to the database can be through direct connections to the database, through client libraries, or through JDBC-ODBC bridges.

The bridge connection is typically used for a device that does not have client libraries or native connections supported.

The different Oracle JDBC drivers are

Thin - pure Java driver used on the client side that does not need an Oracle client installation. It is recommended that you use this driver unless you need support for non-TCP/IP networks because it provides for maximum portability and performance.
Oracle Call Interface driver (OCI). This uses the Oracle client installation libraries and interfaces. If you want to support connection pooling or client side caching of requests, use this driver. You will also need this driver if you are using transparent application failover (TAF) from your application as well as strong authentication like Kerberos and PKI certificates.
JDBC-ODBC bridge. This uses the ODBC driver in Windows to connect to the database

The JDBC drivers that come with the 10gR2 Client and server are compatible with JDK 1.2.x and later. The JDK 1.0.x and 1.1.x are no longer supported.

A sample code segment for connecting to a database from Java looks like:


String url = "jdbc:odbc:mydatabase";
Connection con = DriverManager.getConnection(url, "userID", "password");
if (con) {
System.out.println(" connection good");
} else {
System.out.println(" connection failed");
}


In this example we use the ODBC-JDBC bridge to connect to the database using the ODBC data source named mydatabase.

We could have used "jdbc:dbnet://mydatabase.oracle.com:1521/mydatabase" to connect directly to the listener.

We could also add attributes to the ODBC connection to define things like CacheSize, AlternatServer, or connection timeout.

To create a failover or load balance definition for a JDBC connection you can do this with the following url definitions


String url = "jdbc:inetora:localhost:1521:mydatabase?failover=true&host1 =standbyDB.oracle.com&port1=1521&host2=remoteDB.oracle.com&port2=1521"; String url = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
(HOST=example.com)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)
(HOST=failover.example.com)(PORT=1521))
(FAILOVER=true)(LOAD_BALANCE=false)
(CONNECT_DATA=(SERVER=DEDICATED)(SID=foo)))";

Using javax.swing.JDialog

public class MyDiaglog extends JDialog { private JButton jButton1 = new JButton(); private JTextField jTextField1 = new JTextField(); public MyDiaglog() { this(null, "", false); setVisible(true); } public MyDiaglog(Frame parent, String title, boolean modal) { super(parent, title, modal); try { jbInit(); } catch (Exception e) { e.printStackTrace(); } } private void jbInit() throws Exception { this.setSize( new Dimension( 400, 300 ) ); this.getContentPane().setLayout( null ); jButton1.setText("jButton1"); jButton1.setBounds(new Rectangle(270, 50, 73, 22)); jTextField1.setBounds(new Rectangle(130, 85, 110, 20)); this.getContentPane().add(jTextField1, null); this.getContentPane().add(jButton1, null); jButton1.addActionListener(new ActionListener() { public void actionPerformed(ActionEvent e) { jButton1_actionPerformed(e); } }); } private void jButton1_actionPerformed(ActionEvent e) { jTextField1.setText("hello"); } }

using ThreadLocal()

Have you ever needed variables that were local to the scope of a thread, where each thread managed its storage, and it would be impossible for one thread to access the state information of another thread?

import java.util.Random; public class ThreadLocal1 { // Define/create thread local variable static ThreadLocal threadLocal = new ThreadLocal(); // Create class variable static volatile int counter = 0; // For random number generation static Random random = new Random(); // Displays thread local variable, counter, // and thread name private static void displayValues() { System.out.println ( threadLocal.get() + "\t" + counter + "\t" + Thread.currentThread().getName()); } public static void main (String args[]) { // Each thread increments counter // Displays variable info // And sleeps for the random amount of time // Before displaying info again Runnable runner = new Runnable() { public void run() { synchronized(ThreadLocal1.class) { counter++; } threadLocal.set( new Integer(random.nextInt(1000))); displayValues(); try { Thread.sleep ( ((Integer)threadLocal.get()).intValue()); displayValues(); } catch (InterruptedException e) { e.printStackTrace(); } } }; // Increment counter, access thread local from // a different thread, and display the values synchronized(ThreadLocal1.class) { counter++; } threadLocal.set( new Integer(random.nextInt(1000))); displayValues(); // Here's where the other threads // are actually created for (int i=0; i<5; i++) { Thread t = new Thread(runner); t.start(); } } }

JSR-227 Standard Data Binding & Data Access Facility

The JSR-227 is the result of Oracle work and development of the ADF Data Binding model.

What is JSR-227: http://www.oracle.com/technology/tech/java/newsletter/articles/jsr227_interview.html

There is some concerns about the scope of this JSR:
http://www.theserverside.com/news/thread.tss?thread_id=20274

But if you use ADF Data binding you may want to see this video:
http://www.parleys.com/display/PARLEYS/Home#slide=17;talk=7857;title=JSR-227%20Standard%20Data%20Binding

Web Service Security using Security Token Service (STS)

Securing a single web service can be done at transport level using SSL or at message level using the web Services Security protocol (WS-Security). Both of these solutions may involve the creation and use of cryptographic certificates (x509) on client and server.

if this approach works fine in an end point to end point perspective it may quickly becomes difficult to maintain this solution at an enterprise level when the number of services increase in volume and when web services are distributed in different locations.

Maintenance, monitoring, and administration becomes a key factor at a enterprise level.

The need for a integrated security solution for web services is increasing.

The following links introduce an enterprise solutions based on the security Token Service solution (STS).

Very good white paper (read the introduction part): Web Service Security Guide for Enhancements 3.0 (source Microsoft)
http://msdn.microsoft.com/en-us/library/aa480545.aspx


Single Sign On Service based on tokens

http://www.theserverside.com/tt/articles/article.tss?l=Systinet-web-services-part-6
http://www.sun.com/software/products/opensso_enterprise/index.xml

The token standard: SAML http://en.wikipedia.org/wiki/SAML_2.0

Token aware Firewalls: http://www.layer7tech.com/main/products/xml-firewall.html


Other good articles a bit older:

web service Security, part 1
http://www.xml.com/pub/a/ws/2003/03/04/security.html
web service Security, part 2
http://webservices.xml.com/pub/a/ws/2003/04/01/security.html
web service Security, part 3
http://webservices.xml.com/pub/a/ws/2003/05/13/security.html
web service Security, part 4
http://webservices.xml.com/pub/a/ws/2003/07/22/security.html

Oracle Magazine Archives

http://www.oracle.com/technology/oramag/oracle/index.html