Saturday, June 8, 2013

Adaptive Server IDENTITY quirks

In my previous posts I tried to provide some key information to stimulate your interest in SAP Sybase products. If you are an application developer on any SAP platform, it will pay to know more about Adaptive Server Enterprise ( the former Sybase ASE)  platform as the SAP business suite is successfully ported on it now. If you are just starting to explore ASE, you will be in a similar boat as I was about 2 years back. As I work more and more on it for various projects, more and more things continually surprise me. Having come to ASE from other popular databases, some of the features about ASE totally threw me a curve ball.

One such feature is called IDENTITY. This is very similar to AUTOINCREMENT column in other databases. IDENTITY is an automatically incremented counter, the value of which can be assigned to a column of a table. As one would expect, you can assign a starting value for the column and ASE increments the value with every insert into the table without any problem. In addition, ASE allows you to bulk add records to any table with an identity column, there by accepting explicit identity column values.

It's important to understand that IDENTITY columns are routinely used as primary keys on a table because of its auto-increment capability.You might also believe that the name 'IDENTITY' itself suggests that the value of the column in each row is unique and it is, as long as you leave it to ASE but the strange thing is ASE does not create a unique constraint on the column, there by leaving it to the user to keep it unique when IDENTITY_INSERT on the table is turned ON. By using this option, the table owner or db administrator can insert any number of duplicate entries into IDENTITY column as they want, which in my mind defeats the purpose of having an identity column. By default IDENTITY_INSERT is turned off, hence identity column receiving a duplicate entry is not normal. But once you turn it ON for any reason, you have to be extra careful while entering values manually. You can avoid this problem by denoting the identity column as a primary key column. Which bring me to the next point, how to assign a primary key to the table in ASE.
There are two ways you could do it, using Create or Alter Table syntax or through sp_primarykey stored procedure. A thing you may want to keep in mind while using sp_primarykey is that the stored procedure does not create a unique constraint on the column, which again is a huge surprise to me. Here is what ASE documentation says about sp_primarykey stored procedure.
  • Executing sp_primarykey adds the key to the syskeys table. Only the owner of a table or view can define its primary key. sp_primarykey does not enforce referential integrity constraints; use the primary key clause of the create table or alter table command to enforce a primary key relationship.
  • Define keys with sp_primarykeysp_commonkey, and sp_foreignkey to make explicit a logical relationship that is implicit in your database design. An application program can use the information.
  • A table or view can have only one primary key. To display a report on the keys that have been defined, execute sp_helpkey.
  • The installation process runs sp_primarykey on the appropriate columns of the system tables.
Notice the lack of mention of 'unique constraint' from above which will make you believe that unique constraint is implicitly implied because you are making a column a primary key. But that's not so.
So a unique combination of denoting a column as identity column and a primary key you will have unintended consequences of getting duplicate values in the identity primary columns. Here is an example.\

CREATE TABLE  test (id int IDENTITY, desc varchar(10))
sp_primarykey  test, id
INSERT INTO test (desc) VALUES('abcd')
INSERT INTO test(id, desc) VALUES(1, 'fghk')

Now you have two rows with duplicate id value of 1. Neither, defining as IDENTITY nor PRIMARY KEY could prevent getting a duplicate value in the id column. This could have disaster effect on your business application if it relies on this column to get a unique row. So, to avoid this type of situation follow the guidelines as below.

Always define primary keys through Create or Alter table syntax.
Check the uniqueness of identity column of table after you insert its value manually OR create a unique constraint on the identity column right after table creation.
If you are turning IDENTITY_INSERT on then turn it off in the same transaction, so as to avoid any other transaction in the same session adding a duplicate value to identity. It should be somewhat reassuring to developers that the change in IDENTITY_INSERT setting is not persistent. It's persistence is lost once the session ends.

I hope this important point helps you use IDENTITY carefully and saves you some trouble shooting in case of erroneous results from your business application. Happy development.

Saturday, February 9, 2013

ODATA for Dummies

In a world full of open source and open standards what's so important about one more protocol or specification, right? Why should you know about it, right? Right, but technology professionals are required to know a lot more technologies today than they were in the past because of the rapidly shifting software landscape. Gone are the days when you could master one technology and keep your job forever. At any moment you could be asked to look into some new technology and be expected to work on it or create a proof of concept in an effort to evaluate its fit to your current project. ODATA may be one such technology you may not have worked in or heard of, but worth knowing about.

If you already know about ODATA specification and how it works, please stop reading. This is a very high level but useful information on ODATA meant for newcomers.

Promoted by Microsoft ( do I hear Microsoft haters leaving already? ) ODATA is a protocol to access information over the web. ODATA defines a standard way of exposing any information through  industry standard HTTP protocol. It also follows the architecture popularly known as REST. You can get details of the protocol and much more here. If you want to expose your data to your clients using ODATA, you will need much deeper understanding of the specification. However if you just want to consume the data as a client, you could be up and running in a few minutes to a few hours depending on your level of expertise. This post will have you create a sample and test it within minutes.

Let's take an example of a company that we all know about - Netflix. It uses odata service to publish its movie catalog. Viewing the movie catalog or searching for a title could easily be done by just accessing certain urls through your browser. You just need to know how to build further urls based on the information you get from a url. ODATA provides the response to your http request in a standard XML format that follows either Atom or Json format. Try these urls yourself and see how easy it is get to the information. The odata services are consumed through  urls that are intuitive and easy to understand. will show what type of information you could get from this website. The URL will resolve to The results will show that you could get a list of Genres, Titles and Languages. Now, suppose you want to see all the titles Netflix has, then simply add 'Titles' to the previous url as- This will generate all the titles and associated information. If you want to know all the details (metadata) about all the entities (collections) simply type in this url.$metadata.This results in information on collections, the entities they are made up of and the attributes of each entity there in. This just shows you how easy it is to get to this basic information. Now you are ready to dig deeper.

The data that shows up on your screen may have been stored in a table in a relational database or  in an xml database or in a a file in the file system, it does not matter. The information is available to you through the http get method (browser sends 'get' method to the end point). Irrespective of the type and location of the data storage, specific data is likely identified by a unique key. If you know the unique identifier of the entity you want to access, then you could use it to get to more information.  The following example shows how e Titles are uniquely identified by an 'id' field. This information - that id is the unique identifier of a title is gotten from the previous metadata query we executed. Spend a few minutes reviewing the results of metadata query.'13bLK').

When you don't know the unique identifier you could always search  titles based on a name (name is an attribute of Title entity as revealed by the metadata query.)$filter=(Name%20eq%20'Rod%20Stewart')

The count of total number of titles Netflix owns is easily found by using the following url. As of 2/8/2013 the count is 160050. Odata defines many such functions count, length, replace, trim etc.$count

So this is what odata specification provides. It specifies the format of the url, the functions and operators you could use in the url to get to the data you want. REST based architecture exposes all the information through http metods - in this case - 'GET' is what we have seen so far.

Now a little bit on to how providers like Netflix create these services. If service provider is exposing mechanisms for creating, updating and deleting the data through odata producer and if the information that's being exposed is in relational database, they need to map the standard CRUD methods to http methods. So create is mapped to post, delete to delete, update to put and select to get. The essential tasks of the producer would be to parse the request, get the http method, get the content, call the back end where the data is stored, perform the mapped functions and convert the retrieved data to Atom or Json format before it's sent back to the client. Vendors like Microsoft and Google (odata4j) are offering function libraries that makes it easy to create your own producer. If you just want to consume the odata services there are many client libraries like popular datajs.

Now you are ready to create your own client.
Here is a snippet of code I got from datajs tutorial. Save this as any html document and use it in any modern browser. You will see all the genres of all the content from the Netflix. Before running the sample don't forget to download datajs-1.1.0.min.js directory from here and save it to your c:\datajs directory.

I hope this post will give you enough information to try things on your own.  I also hope to have another post some time later on the same subject. Odata is actively used by companies such as Microsoft, SAP, Netflix, Facebook and Ebay. Only time will tell if this standard becomes widely used technology or not, but it already has gathered enough following to attract our attention. website. Good luck. Your comments are welcome. Please feel free to share if you like it.