Saturday, May 28, 2016

The Miracle Of SQL

SQL or the Structured Query Language, first appeared in 1974. The two blokes credited with writing it, Donald D. Chamberlin and Raymond F. Boyce got that remit so right that it stands today as a tower of software genius. It has been around for 42 years and is still the undisputed defacto standard for managing a relational data store. 42 years and counting! In the software industry that is eternal.

One of the precious few good things to ever come out of IBM, it went ANSI in 1986, 30 years ago and since then has only seen minor revisions. Of course there are issues, forgetting to specifically define the date, time and datetime standards arguably being one, but forgivable considering the enormity of the gift. Since 1986 there have been many diversions from the definition by many different vendors but the core is largely unchanged.

To Don and Ray, Thankyou.

Friday, May 20, 2016

Solving the unique name on delete issue

A common requirement for data management is to have a table that contains a name that must be unique within a table. If rows cannot be physically deleted from the table, a status field is usually used to indicate that the record has been deleted. If another row is added with the same name, an error will be generated because the new row fails the unique constraint on the index.

To solve this issue, add another field called (say) DeletionId that defaults to zero and populate that field with the id of the record when the record is flagged as deleted. Include this field in the unique index.

For example, assume the following table

create table MyTable

   MyTableId int identity(1,1) not null
  ,MyTableName varchar(100) not null 

  ,StatusId smallint not null 
  ,DeletionId int not null constraint DF_MyTable_DeletionId default 0 
  ,etc... 
  ,constraint PK_MyTable primary key (MyTableId) 
  ,constraint UK_MyTable_MyTableName Unique (MyTableName, DeletionId) 


To flag as deleted (if 2 means deleted and 5 is the id of the record):

update MyTableName set StatusId = 2, DeletionFlag = MyTableId where MyTableId = 5

By using the DeletionId it is possible to have many deleted records in the table with the same name but only one record in the table that is not deleted with the name. That is, the undeleted name is unique.

Tuesday, May 3, 2016

Oracle Linked Server Issues

If you get the error message below while trying to connect to an Oracle linked server:

Msg 7399, Level 16, State 1, Line 3
The OLE DB provider “OraOLEDB.Oracle” for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 3
Cannot fetch a row from OLE DB provider “OraOLEDB.Oracle” for linked server “(null)”.


...you may need to set the "Allow Inprocess" setting on the provider to enabled.

To Fix:

  1. Go to Server Objects\Linked Servers\Providers folder in SQL Server Management Studio on the Sql Server that has the linked server, locate the Oracle Provider (OraOLEDB.Oracle) and right click to Properties.
  2. Ensure Allow Inprocss is checked
  3. Recreate your linked server.
Also, make sure you have restarted the SQL Server service after you have installed the Oracle OleDB driver.

Look here for details.

Saturday, April 16, 2016

Classloader Notes

Notes about Java Class Loaders

  1. The system class loader loads classes where it can find them. This is typically defined by the classpath setting at the environment level. A custom class loader can extend this by sub classing the default class loader and by allowing the user to specify a series of locations or jar files that can be searched to locate a class. This provides more flexibility than the standard class loader.
  2. Most custom class loaders will provide a cache so that searching for a class is faster. The cache is typically a HashMap with the name of the class being the key. The constructed class is stored against the key name. To construct a class is relatively expensive and is performed by the defineClass() method. This method takes an array of bytes and returns a constructed class. The constructed class is registered with the JVM, which means you can't call defineClass() on the same class name by the same classLoader twice or you will get a duplicate class definition exception. There is no other mechanism to return a constructed class from an array of bytes. The array of bytes is easy enough to read from the file, the construction of the class is done at the JVM level. That is so that the checks for linkages, security and other class related activities can be achieved.
  3. A class "signature" is made up of the classLoader that loaded the class and the actual class name. That means is you load a class from a custom class loader it will have a different class "signature" to the class "signature" generated by the system class loader or by another custom class loader instance. The only way I've found to dynamically refresh a class is to create an instance of a custom class loader, load the class, change the class file by modifying the code and recompiling, copying the compiled class file to a place where the custom class loader can find it, recreate the instance of the class loader and then reload the class. In this way, the class "signature" of the second instance of the class will be different to the "signature" of the first instance of the class as they used a different instantiation of the class loader. 
  4. You can't cast an object across class loaders. That means if you load an object into a custom class loader it will NOT be the same reference to the class from the original class loader. If you consider the previous point, each of these classes will have a different "signature" therefor would not be compatible. That is, assuming all classes are visible, this is not allowed:
// assume we are in classLoaderA which may be the 
// system class loader.
// the TestClass reference is therefore relative to 

// classLoaderA
TestClass test;


// create a new class loader
MyClassLoader classLoaderB = new MyClassLoader();


// the class that is returned from the following is 

// relative to classLoaderB but the cast class is 
// relative to classLoaderA. 
// This will cause a class cast exception
test = (TestClass) classLoaderB.loadClass("TestClass");


If you need to use classes across classLoaders you have to use interfaces.