SQL Server Lookup Table for Daylight Savings Time (DST)

Oracle is DST-aware since 9i (2001). SQL Server, on the other hand, still has no built-in DST support in the latest version (SQL Server 2012). A quick-and-dirty trick to steal DST-support from Oracle to SQL Server is to create a lookup table in Oracle and copy it to SQL Server. Here is an example table.

Generate Insert/Update SQL for DataStage Oracle Connector Automatically (Updated)

CodeProject
Last year I published a post for automatically generating insert/update SQL used in DataStage Oracle connectors. I used Oracle 11g's new function LISTAGG(), which resulted in 2 major limitations.

1. It only applies to Oracle 11g or later.
2. LISTAGG has 4000-character length limit. So it doesn't work on wide tables with more than 100 columns.

Here is an updated version without using LISTAGG(), hence removing the limitations.

Insert SQL:

Update SQL:

DataStage Netezza Connector Sparse Lookup Error: Count field incorrect

CodeProject
Error Message:

Unexpected ODBC error occured. Reason: [SQLCODE=07002][Native=8] Count field incorrect (CC_NZStatement::executeSelect, file CC_NZStatement.cpp, line 137)

Cause:
 One or more fields in the data stream are used multiple times in the lookup SQL statement.

Work-around:
Duplicate the field(s) so that each is used exactly once.

Mimic Row Pattern Matching in Oracle 11g

CodeProject
Oracle 12c introduced MATCH_RECOGNIZE clause for row pattern matching. If you are still using Oracle 11g, you can use listagg() and regexp_like() to implement row pattern matching. For example, in order to find users with event A followed by event D (other events allowed in between),

User	Sequence	Event
1	1	A
1	2	D
1	3	C
1	4	B
2	1	C
2	2	B
2	3	A
3	1	A
3	2	C
3	3	D

you can use the following query:

SignalR Pushes ISD Applications to Next Level

CodeProject

Introduction

ISD applications, web applications in general, are built on top of HTTP, which uses a pull-based communication mechanism. All communication requests are initiated or pulled from clients. Web servers can only respond to the requests. They cannot initiate or push communications to clients. Put it another (simplified) way. ISD applications are a collection of server-side methods to be called from client-side. When you type in a URL in a browser, you call its Page_Load() method. When you click a button or make a selection in a dropdown list, you call their corresponding Button_Click() or DDL_SelectedIndexChanged() methods.

The limitation of pull-only communication makes it difficult to implement push-based functionality in ISD applications. For example, if several users try to modify the same record at the same time, only the first user can save his changes. When the others try to save their changes, they will be greeted with a popup message, "The record has been changed. Please refresh." It would be much better if,  after the first user saves his changes, the server immediately pushes out a notification to all other users, so that they don't waste any time or effort unnecessarily.

Solution

It is possible to mimic such push functionality within the current pull-only framework. For example, embed a Timer control in the EditRecord page, and check the record's timestamp or check-sum periodically. However, the implementation is extremely inefficient because of repeated unnecessary web and database traffics. An ideal solution is to call a client-side method from server-side as soon as the first user saves his changes. I am not talking about using RegisterStartupScript()to inject JavaScript code, which is executed only at the single client that initiates the request. What I suggest is to call a JavaScript method on ALL clients from server-side. Impossible? SignalR enables me to do just that.

SignalR is an ASP.NET library which allows bi-directional communication between server and client. In other words, it allows web applications to push content to clients by calling client code from servers, and vice versa. In the following demo project, I will show you how simple it is to implement a push notification in an ISD application.

Implementation

Step 1: Build a regular ISD application with required configuration

SignalR 2.0 is pretty picky on system configuration. Check its requirement page to make sure your server is supported, and your intended users have compatible browsers. To create the demo application, I used the following configurations:

• ISD v10.2.1
• VS 2012
• .NET 4.5
• Web site
• C#
• Develop under Windows 7
• Deploy to Windows Azure
• IIS Express

C# web site is just my own preference. VB web application should also work.

Step 2: Add SignalR library

Open the application in Visual Studio, add SignalR library via NuGet package management.

Step 3: Create a hub in server

Add the following 2 classes in App_Code\Shared folder.

Step 4: Define notification method in client

In ISD, add the following JavaScript code to the EditRecord page's prologue.
Pay attention to the version numbers of jQuery and SignalR. You might download a different version from mine. Change them accordingly.

Step 5: Call notification from server

The best time to push the notification is after changes have been saved, i.e. after the transaction is committed successfully in database. In Visual Studio, override CommitTransaction() in EditRecord's page class.

Demo page

That's all to add the push notification. Click here to open a demo page. Open it in 2 or more different browsers. Click "Save" button in one browser, and all other browsers will popup the notification. Of course, there are rooms for improvement. For example,

• Use a less invasive notification, e.g. toastr instead of alert.
• Send record ID as a parameter in the notification method. Show notification only if the ID matches the current one in editing.

Conclusion

SignalR enables web applications to push content from server to client. This new dimension in server-client communication can push your ISD applications to the next level.