Wednesday, April 29, 2009

Fluent Interface Updated

This is a update on Fluent Interface patter based on my previous blog. The update is based on a much simplified interface for generic domain object.

First, I defined the following interface:

public interface IDomainObjReaderFluent<T> where T: class
{
IDomainObjFluent ReadData(int byIndex);
IDomainObjFluent ReadData(string byName);
T GetObject();
}


The interface defines two ways to read data, by index or by name. For the example of Employee class, the implementation class is:

class EmployeeReaderFluent : IDomainObjReaderFluent
{
private IDbReader _reader;
private int _id;
private string _name;
private DateTime _dob;
//...
public EmployeeReaderFluent<Employee>(IDbReader reader)
{
_id = 0;
_name = null;
_bod = DateTime.Now;
_reader = reader;
}
IDomainObjReaderFluent ReadData(int byIndex)
{
if ( byIndex == EmployeeConst.IdIndex )
{ _id = _reader.GetInt32(byIndex); }
else if ( byIndex == EmployeeConst.NameIndex )
{ _name = _reader.GetString(byIndex); }
else if ( byIndex == EmployeeConst.BODIndex )
{ _bod = _reader.GetDateTime(byIndex); }
//...
}
// ReadData(string byName) skipped ...
Employee GetObject() {
return new Employee(_id, _name, _dob, ...);
}
}


In the implementation of the interface for a domain object, the class has complete knowledge of how each field is retrieved from IDbReader or a data reader. In other words, it will be up to the implementation class to decide how to get data from the data reader. The data reader instance can be passed in through its CTOR.

Here is an example of its usage:

Employee empl = new
EmployeeReaderFluent<Employee>(myDbReader).
ReadData(EmployeeConst.IdIndex).
ReadData(EmployeeConst.NameIndex).
ReadData(EmployeeConst.BodIndex).
...
GetObject();


where myDbReader is an instance of IDbReader, and EmployeeConst is a class with only constants of index values for each field. I prefer to use const class to embedded literal constants.

Read More...

Tuesday, April 21, 2009

StructureMap and Fluent Interface

Recently I have been working a project to process data. The case is very simple. It reads data from database, process data and finally out put data to either database or text file. I have worked on the similar scenario for many cases. This time, I decided to design a generic pattern for developing similar type applications.

Based on past experience, I think I need a dependency framework for decoupling components and integrating various implementations of interfaces into a specific application. StructureMap got my attention. After about one investigation, I really like this framework. It is just for DI but very powerful. One interesting feature of this tool is its code-based registration to map DIs, which is based on Fluent Interface.

After reading the definition and examples of Fluent Interface, I really like its readability feature. Just coming up to my mind, I envision its implementation of mapping database element to domain object.

For example, the following is a domain class as Employee:

public class Employee {
public int ID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public datetime BOD { get; set; }
public float Salary { get; set; }
}


My employee reader interface is based on Fluent Interface like this:

interface IEmployeeReaderFluent {
IEmployeeReaderFluent ReadID(IDataReader);
IEmployeeReaderFluent ReadFirstName(IDataReader);
IEmployeeReaderFluent ReadLastName(IDataReader);
IEmployeeReaderFluent ReadBOD(IDataReader);
IEmployeeReaderFluent ReadSalary(IDataReader);
Employee GetObject();
}


And the implementation class is defined as:

class EmployeeReaderFluent : IEmployeeReaderFluent {
private Employee = new Employee();
IEmployeeReaderFluent ReadID(IDataReader reader) {
Employee.ID = IDataReader.GetInt32(
IDataReader.GetOrdinal("ID"));
}
IEmployeeReaderFluent ReadFirstName(IDataReader reader) {
Employee.FirstName = reader.GetString(
reader.GetOrdinal("FirstName"));
}
IEmployeeReaderFluent ReadLastName(IDataReader reader) {
Employee.LastName = reader.GetString(
reader.GetOrdinal("LastName"));
}
IEmployeeReaderFluent ReadBOD(IDataReader) {
Employee.BOD = reader.GetDateTime(
reader.GetOrdinal("BOD"));
}
IEmployeeReaderFluent ReadSalary(IDataReader) {
Employee.Salary = reader.GetFloat(
reader.GetOrdinal("Salary"));
}
Employee GetObject() { return Employee; }
}


Example of the usages:

IEmployeeReaderFluent employeeReader = 
new EmployeeReaderFluent()
.ReadID(reader)
.ReadFirstName(reader)
.ReadLastName(reader)
.ReadBOD(reader)
.ReadSalary(reader);
Employee employeeObj = employeeReader.GetObject();

Read More...

Monday, April 13, 2009

Yahoo YUI Reading Blinds

Yahoo YUI Reading Blinds is a very nice browser tool based on JavaScript and YUI. Recently I tried to dig out more about this tool by looking its codes. Here are some of my findings.

First, the tool is based on a group of JavaScript codes. It is very long one like this:

javascript:var%20x=%20function(){var%20h=document.createElement('script');h.src='http://yuiblog.com/assets/readingblinds.js';h.type='text/javascript';document.getElementsByTagName('head')[0].appendChild(h)}();


This long line of codes does not have spaces. The spaces are actually specified by "%20" in codes. Then I break the codes in a nice format like this:

javascript:var x= function(){
var h=document.createElement('script');
h.src='http://yuiblog.com/assets/readingblinds.js';
h.type='text/javascript';
document.getElementsByTagName('head')[0].appendChild(h)
}();


In stead of putting this codes to toolbar, I added this tool as a new bookmark to my bookmark (I am using Vimperator and there is no toolbar visible). From the bookmark, I click on this. It works fine with reading blinds visible on top and bottom, very nice! I like this reading blinds more than others (you may find other reading blinds on web). It works well with with mouse movement and vertical scroll bar.



The only problem I had was that my Vimperator caches all the keystrokes. I could not use s, l, and b keys for smaller, larger and toggle. Vimperator blockes them to the tool. Fortunately, Vimperator has a "PASS THOUGH" mode to let all the keys passed: Control-Z.

This tool actually injects YUI JavaScript codes into the page: loading YUI scripts into <head> area. The scripts then adds top and bottom <div>s as blinds. In addition to that, it adds key and mouse event to move blinds.

I further hacker this tool to change the URL to the script src to my local Mac's Sites folder like this:

...
h.src='http://localhost/~[username]/YahooYUIReadingBlinds/readingblinds.js';
...


I added this to another bookmark as "YUI Reading Blinds Local Scripts". It works great! Since I download the readingblinds.js to my local site, I then made some changes to figure out how it works and to test its features with different settings. It was really fun to work with this tool.

The reading blinds will become darker if you click on the tool more than once. With FireBug enabled, I noticed that the scripts are injected more than once.

To get rid of this reading blinds, just refresh the web page. The original web page is reloaded and the JavaScript inject is gone.

The reading blinds does not work with https web URLs since https URL is an encrypted HTML protocol and no JavaScript injection are allowed.

This tool is a very good example to enhance web page display by using JavaScript injection!

By the way, I feel much easy to read web pages and my eyes are more relaxed with this great tool for a long period of time, especially with the browser maximized. I think this may protect screen as well.

Read More...

Friday, April 10, 2009

Code Syntax Highlight

toolsThe main reason I prefer to use Blogger than WordPress is that Blogger allows me to customize my blog layout with customized scripts and css styles in my blog template. For example, I have some blogs with source codes in C#, JavaScript, HTML, and SQL. It is very easy to include codes in a blog, by using <pre> tag. However, to highlight syntax, you need to sepcify style or to use css class style. Blogger provides this feature for free but you have to pay for this feature at WordPress.

Here are the steps I use to highlight my code syntax:

  1. Add some style to my blog HTML. This can be done through Blogger's Layout|Edit HTML. Check the Expand Widget Tempates to get the whole HTML layout settings and save it as backup before you make any changes.



    Add css classes (defined in { and } area) will be used to highlight code syntax.

  2. Generate HTML codes for my codes. I like to use some web's tools to get the job done. The tool I use is ASP.Net's forum page. I use this forum's post new topic editor to write/past my codes from its Source Code widget. The widget supports several languages which covers my areas. Those snap-shots are examples how I get my codes to HTML.

    1. Click on Source Code widget button




    2. Type in my codes




    3. Click OK to close it.

    4. Click on Edit HTML widget button




    5. Edit or copy the HTML codes



    6. Copy section HTLM codes between <pre ...> and </pre>


    Note: you may notice that ASP.NET's HTML codes contain some css classes. I added those classes to my Blogger's HTML template.

  3. In my blog entry, I paste the HTML codes to my blog. Normally I put the HTML codes between div tags. If the codes are not too long, I use the div with border. If the codes are too long, then I use a div with fixed height so that codes are within a block with scroll bar available to view codes.

    You can see those HTML tags by viewing section of codes (right click a selection of text).


There are many good tools available for syntax highlighting such as Google's syntaxhighlihter, SHJS, HIGHLIGHT.JS, and Highlight. The problem is that most of those tools are for people who have web server. I only have hosted web server like Blogger or WordPress. I cannot put any of my codes and css style files to the server. Blogger provides a way to embed css styles, but I would not want too many css definition codes there. It is just hard for me to maintain.

That's why I like to use ASP.NET's tool to get HTML codes with classes. Another great advantage of ASP.NET's widget is that it will be able to convert some special HTML codes such as > and <.

Read More...

Saturday, April 04, 2009

SQLite Manager Add-on for Fixfox

toolsI found a very handy tool: SQLite Manager an add-on for FireFox. I have been using another application called as SQLite Database Browser in my Mac for my SQLite databases. FireFox add-on actually is much better than the application, at least from the UI.

For example, the UI provides Database Settings for database generic settings and UIs for Views, Indexes and Triggers. The table UI layout is also much better than SQLite Browser.



I have not tried this one in Windows yet. I think it should work without any problem.

Read More...

Sunday, March 29, 2009

Dojo Chart Library

I read an article about Dojo Now With Chart Tools by Mattew Russell, the author of the book Dojo The Definitive Guide by O'Reilly.

The article lists several demos on generating charting by Dojox.charting library. I then started to copy the codes to an HTML file. Instead of loading Dojo libraries to my local computer even I have a personal web site on my Mac, I used xDomain reference to Google's Dojo CDN library. In this way, I don't need any web server and I can send this demo HTML to my colleagues to share it. My colleagues at my work really enjoyed it.

This demo does not only lists original JS codes. I enhanced the charts with different CSS styles and colors. It is so easy to do that. In addition to that, I allocate an 600x600 area at top as an area for chart and list my demos a UL list underneath it. This arrangement needs to clean the area for any chart previously displayed. The Dodo library does not provide APIs to clean chart. I finally found a way to clean it: simply removing all the childen nodes in the area. Even this one works, but it is not recommended. An Dojo insider pointed out this method does not clean all the objects created for the chart. I posted my question to StackOverflow and Dojo's forum.

My previous blog also mentioned the issue about xDomain reference. All these were really challenges for me since I am new in Dojo. I have not done much development in Dojo. However, the demo and enhancements let me learn a lots.

Here is my demo on Google's Code.

Read More...

Saturday, March 28, 2009

FireBug and xDomain Reference for JS APIs

Last week I read a very interesting article on Dojo's charting library. There are some demo codes in the web page. Instead of downloading Dojo's library to my local computer web site, I choose to use CDN fashion to load Dojo library from Google's CDN. This method is called Cross Domain Resource Reference or xDomain Reference.

In this way, I don't even need a web server. I just use VI to write an HTML file with JavaScript and Dojo API calls. That's the basic of web application: HTML + JS. I dropped the HTML file to my browser (FireFox) and it worked right away.

However, for the same codes which I found them in Dojo's ToolKit web page, one of feature does not work. It is the animation of curve chart. This feature is added by calling Magnify() API function to the chart. Behind sense, animation parts are created to each point. In the Dojo's web page, the magnification works fine but not in my HTML.

I spent about one day's time finally I figured it out. What I missed is to add the following codes to the head section of HMLT:

<head>
...
<script type="text/javascript">
...
dojo.require("dojo.fx");
...
</script>
</head>


FirBug helped me to find this one out. When I checked my HTML source codes in FirBug, the script loading section shows the loaded source codes in FireBug window. However, for the xDomain reference HTML page, the source codes are partially and they are displayed as one very very long line like this:


As you can see how hard to read when I copied the codes to VI:



Fortunately, the same codes are available on DojoToolKit web page, where Dojo's library files are not xDomain references. They are in the same domain. By using FireBug to take a peek inside the web page, I found the Magnify.js source codes are in nice layout:



By the way, I also tried to get source codes directly from Google's CDN web site, for example, Dojo.xd.js. It also displayed as a very very long line. But I went to DojoToolKit's web page to load the source code from their domain, I was prompt to save or open js. The download js file is in nice layout format. Here it reveals that CDN is not only for fast downloading, the size of the file is also very small. All the unnecessary codes such as line breaks are removed!

Read More...

Sunday, March 22, 2009

Using CTE in TSQL

Last week I found CTE (Common Table Expressions) when I posted a question to StackOverflow. CTE is an ANSI SQL-99 standard and it was added to Microsoft SQL Server 2005.

For me, it was new. I applied answers to my TSQL codes and then I really like it. It is so simple and readable. With its recursive power, I resolved my issue in a couple lines of codes. Today I further googled out more information about this and here are some links with good view of CTE:


Another example I tried was to replace T-SQL cursors by using both CTE and a table variable:
DECLARE @row INT;
DECLARE @rowMax INT;
DECLARE @myTable (
id INT IDENTITY(1,1) -- key with auto creament
name VARCHAR(100),
dt DATETIME );
-- Using CTE to select data into @myTable
WITH CTE_Temp(name, dt) AS
(
-- SELECT to hold a temp set of data
SELECT tagName as name, dt FROM myTagTable
WHERE version = 0;
)
INSERT INTO @myTable (name, dt) -- Insert to @myTable
SELECT name, dt FROM CTE_Temp;
SELECT @row = MIN(id), @rowMax = MAX(id)
FROM @myTable;
WHILE (@row <= @rowMax)
BEGIN
-- do someting about data in @myTable
SET @row = @row + 1; -- move to next row
END

Of course, this can be done with SELECT INTO FROM statement. This is just an example to use CTE to hold a section of data from a base table.

The power of CTE is its unique feature: recursiion. Within the WITH block, you can define two basic queries: base or anchor query and recursive query. Here is an example of Split like function to split a string by delimiter into a table as return:
CREATE FUNCTION [dbo].[udf_Split]
(
-- Add the parameters for the function here
@p_StringList NVARCHAR(4000),
@p_Delimiter NVARCHAR(512) = ';'
)
-- Return table definition
RETURNS @Results TABLE (
position int NOT NULL,
item NVARCHAR(MAX)
)AS
BEGIN

-- Use WITH statement with recursive power
WITH Pieces(pn, start, stop) AS
(
-- start from 1, 1, stop(=CHARINDX())
SELECT 1, 1, CHARINDEX(@p_Delimiter, @p_StringList)
UNION ALL
-- next to update pn, start and stop values
-- by recursive call

SELECT
pn + 1,
stop + 1,
CHARINDEX(@p_Delimiter, @p_StringList, stop + 1)
FROM Pieces
WHERE stop IS NOT NULL AND stop > 0
)
-- Test the result
-- SELECT * FROM Pieces;

INSERT INTO @Results
SELECT
pn, -- as position
SUBSTRING(@p_StringList, start,
CASE WHEN stop IS NOT NULL AND stop > 0
THEN stop-start
ELSE LEN(@p_StringList) END)
AS s -- as item by using SUBSTRING() function to get sub string from Pieces
FROM Pieces;

RETURN;
END

Read More...

Saturday, March 14, 2009

Use sp_who2() to Get Current Log-in User Information

Recently I have been working on Microsoft SQL Server 2005 to resolve some security issues. One question was to get the current log in user information.

As usual, when I cannot find an answer in 10 minutes, I post my request to StackOverFlow web site. I posted my question early in a morning before I went to my work. Quickly I got several answers when I arrived to my office by biking. I tried all of them and found out sp_who2, a undocumented stored procedure by Microsoft SQL Server, is the tool to get information I need. By using this tool, actually I can find out who is using the SQL server anytime. For example, if a critical data refreshing or migration job is scheduled, I could add this tool to find out if there is any user access to SQL server and send out warning emails if any one there, before the job is about executing.

It is very easy to user SP:

EXEC sp_who2;

The SP returns a list of log in users in a table view if you use Microsoft SQL Server Management Studio's query. However, I only want to see related column information and filter users by WHERE and ORDER BY clauses. Then I found out a way to define a table and output the result to a variable table like this:
DECLARE @retTable TABLE (
SPID int not null
, Status varchar (255) not null
, Login varchar (255) not null
, HostName varchar (255) not null
, BlkBy varchar(10) not null
, DBName varchar (255) null
, Command varchar (255) not null
, CPUTime int not null
, DiskIO int not null
, LastBatch varchar (255) not null
, ProgramName varchar (255) null
, SPID2 int not null
, REQUESTID INT
)
INSERT INTO @retTable EXEC sp_who2
SELECT Status, Login, HostName, DBName, Command,
CPUTime, ProgramName, BlkBy AS [Last CMD Time] -- *
FROM @retTable
--WHERE Login not like 'sa%' -- if not intereted in sa
ORDER BY Login, HostName;

To view all the user information, you need to login as sa or user with sa administrative privileges. Otherwise, you may only see yourself or limited information.

Read More...

Thursday, March 05, 2009

Using EXEC() AT Continued

My previous blogs on this topic demonstrate a way to use EXEC(...) AT ... to pass through a query to a linked server. The performance of this method is much better than a T-SQL query directly with the linked server. I tried it with an very big Oracle database table with great speed.

Today, I found another very interest issue with this pass-through query method. If the execution on the remote server has any error such conflict with constrains or wrong field name, the execution does not stop. The errors will be thrown at the end of execution. For example, the following codes will be executed completely:

DECLARE @sql NVARCHAR(MAX);
DECLARE @myCount INT;
-- Initialize setting
SET @myCount = -1; -- Initailize it
-- Build sql query

SET @sql = N'
BEGIN
SELECT COUNT(*) INTO :myCount
FROM owner.myTable
WHERE id1 = '
+ CAST(@id AS VARCHAR) + N';
END;'
;
-- id1 is an incorrect field name
EXEC (@sql, @myCount OUTPUT) AT linedOracleServer;
PRINT 'Count: ' + CAST(@myCount AS VARCHAR);
-- Prints Count: -1 Not stopped!


I tried similar codes with T-SQL directly using the lined server:
DECLARE @myCount INT;
-- Initialize setting
SET @myCount = NULL;
-- Use T_SQL query
SELECT @myCount = COUNT(*)
FROM linkedOracleServer.owner.myTable
WHERE id1 = 1;
-- NO prints, syntax error right away!
PRINT 'Count: ' + CAST(@myCount AS VARCHAR);


I found this interesting problem when I run a stored procedure with EXEC() AT to update value on Oracle side. It runs fine but today I found one error at the end of execution. The SP did not stop. Finally I figured out there were several violations of constraints. Since the whole stored procedure was completed and no exception to stop the program, the continuous codes set flags to mark insertion successful in another log table.

I tried to run a test scheduled job with this problem SP. The job log does indicate exception and step failure. However, the SP was executed completed.

EXEC() AT is a good way to leverage a remote server's full power; however, you have to be very careful about the process. Test your codes thoroughly before putting it into production. Another way may be to schedule a job to run the codes. If there is any failure, rollback any changes.

Read More...

Saturday, February 28, 2009

Using EXEC() AT with Oracle DB

My previous blog was about using OPENQUERY() method with an Oracle DB. The advantage of using this method call is to pass through a query to a linked server such as Oracle server and to leverage the Oracle server to do the job. The great benefit of this strategy is to obtain much fast performance compared to using a T-SQL query directly against an Oracle database object locally at SQL server.

However, when I tried OPENQUERY next day. I was so disappointed. What I found is that the query passed to OPENQUERY() must be a literal string, no variable or expression is supported. That means I cannot build a query string dynamically with different constrains. This greatly limits the usage of OPENQUERY. I just cannot use it in my projects.

Fortunately, I found another pass-through way to leverage Oracle's power to query data, and it is possible to pass result back. I spent about one and half day to figure it out. I posted several questions to StackOverflow web site but I did not get any answer there. Eventually I got the solution through Google searching. I posed my answer to StackOverflow as well.

The method is to use "EXEC (...) AT ..." to the job:

EXEC (@sql) AT linedOracleServer;

Actually, there are some good examples of using this method. However, all those examples are using either Microsoft SQL server or Express SQL server. By using pass-through query, the syntax of the query is very different from Microsoft SQL server and Oracle SQL server. That's why it took me a while to figure it out.

I can build an Oracle SQL query to combine all the inputs into a query string. That's an easy part. The most challenge issue is to pass results as output parameters. For example, I need to get total count of rows in an Oracle table with WHERE clause, and pass the result out to my SQL server.

Here is what I did:

DECLARE @sql NVARCHAR(MAX);
DECLARE @myCount INT;
-- Initialize setting
SET @myCount = NULL;
-- Build sql query
SET @sql = N'
BEGIN
SELECT COUNT(*) INTO :myCount
FROM owner.myTable
WHERE id = '
+ CAST(@id AS VARCHAR) + N';
END;'
;
EXEC (@sql, @myCount OUTPUT) AT linedOracleServer;
IF (@myCount IS NOT NULL)
BEGIN
PRINT
'I get the count from my Oracle table: ' +
CAST(@myCount AS VARCHAR);
END
ELSE
BEGIN
PRINT
'It must be an query syntax error';
END

The key points to build an Oracle query with an output parameter is to follow these steps:
  • making sure the query being a valid Oracle PL/SQL query such as SELECT ... INTO ... to assign result to a variable or parameter;
  • using ":" before a parameter name;
  • using an anonymous block (BEGIN...END) to wrap the query; and
  • Appending ";" at the "END" block.


One lesson I learned from this practice is that exceptions raised by "EXE () AT" may provide misleading error messages. For example, when I used "@" for parameter or missed ";" after END. I got somethink like "illegal characters in expression". You have to open your view in all different angles.

Read More...

Wednesday, February 25, 2009

Using OPENQUERY for Oracle DB

Today I googled an alternative solution to run an Oracle PL/SQL query or stored procedure from Microsoft SQL server 2005. I am very impressed by the result of this alternative way.

What I mean by an alternative way is that I have been using T-SQL queries directly against linked servers defined in Microsoft SQL server. A linked server is a server added to Server Objects->Linked Servers by using Microsoft SQL Sever Management Studio tool.



The linked server that Microsoft SQL server supports covers a variety of database servers, including Oracle server. By using T-SQL queries, you can easily query data from to any database object such as a table or view on this server in the same way as querying data from a local table or view, as in the following example:

SELECT COUNT(*) AS MyCount 
FROM myOracleServer..owner.tableName
WHERE ...;

where myOracleServer could be a linked server to an Oracle server.

One problem I troubles me is that when a table on Oracle side is very big(with millions of rows of data), the query may take a while to execute, sometimes more than 1 minute just for a query call. I had many cases to take more than 10 hours to update data from my Microsoft SQL server to an Oracle server with thousands of T-SQL calls(checking, updating and inserting data).

The alternative way I found today is to use OPENQUERY call like this:

SELECT MyCount 
FROM OPENQUERY(myOracleServer,
N'SELECT COUNT(*) AS MyCount FROM owner.tableName WHERE ...');

The execution time is 00:00:00 comparing 00:01:09 with the similar direct T-SQL query as above. It was a stunning when I saw the result back right away.

The information I found is from a discussion on Bytes web forum, where one person responded a recommendation by using OPENQUERY in a Google Groups' discussion. Then I figured out the solution that I need to improve my current T-SQLs.

The reason for such a big difference, I think o as my understanding, is that the SQL server may create a temporary table or allocate a cache for the database object referenced by a linked server in a T-SQL' query, while OPENQUERY is just a OLE DB connection call to the linked server with a SQL query by which the server or Oracle does its job with its full power.

Read More...

Sunday, February 22, 2009

T-SQL Tip: Combine Column Names as a String

I found following T-SQL query to build a string of selected column names together separated by command for a table. This string can be used then for another SQL query as a column list.

The first thing what I did is to use Coaslesce function to combine a list of row values into a string, separated by ',' if a value is not null or '' if null.

The second trick is to use information_schema.colums to get column names by column_name. Notice that table_name and column_name are a special key words here for table and column names.

1    DECLARE @v_FiledList NVARCHAR(MAX);
2 -- Use Coalesce to combine rows to a string
3 SELECT @v_FieldList = COALESCE(@v_FieldList + ',','') + column_name
4 FROM (
5 -- Use information_schema to get table's column names
6 SELECT column_name FROM information_schema.columns
7 WHERE table_name = 'myTable' AND
8 column_name NOT LIKE 'keyCol' -- filter col 'keyCol' out
9 ) AS A;
10 PRINT 'Field list: ' + @v_FieldList;

You can get more detail information such as data type, length, and so on from information_schema.columns:
SELECT column_name, * 
FROM information_schema.columns
WHERE table_name = 'myTable'

Read More...

Tuesday, February 17, 2009

jQuery Tutorial Videos

Today Ajaxian posted a group of tutorial videos on jQuery. I just finished the first one by John Resig, the designer and developer of jQuery. I saw a number of his training videos. The first one by Ajaxian's blog is for entry level:



Not sure what link is used for this embedded video, Adobe flash? The quality of the video is quite good, comparing to similar ones on YouTube.com.

By the way, the editor John used for the video is SubEthaEdit, an interesting editor with multi-user to work on one text file.

Read More...

Thursday, February 12, 2009

Vimperator Tip of the Day: xall Command

Tip of the DayIn my Firefox Preference, I have set up my tab settings to save all the tab as a session when it is closed and reopen the session when it starts.

However, sometimes this feature does not work. I get a blank tab occasionally and lost my previous tabs. I don't know why.

Today I found a Vimperator command to resolve the issue: xall command. Just type :x and then tab, this command is available for use. The description for this command is:

    Save the session and quit
or a snap-short:



Just remember to use this command close my Firefox from now on. I have add this key to My favorite Vimperator Keys.

The Power of Muscle Memory with Vimperator!

Read More...

Tuesday, February 10, 2009

Command Line Tool: curl

toolsToday I watched a video on Atom Publishing Protocol at YouTube.com GoogleDevelopers. Further reading on this topic, I found an article on this by IBM: Getting to know the Atom Publishing Protocol Part 1.

One thing that got my attention is the command line tool: curl. In my Mac terminal, I found this one is available. That's Unix world benefit. According to the manual information of curl(man curl):

curl is a tool to transfer data from or to server, using one of the supported protocols (HTTP, HTTPS, FTP, FTPS, SCP, SETP, TFTP, DICT, TELNET, LDAP, or FILE). The command is designed to work without user interaction.

That's a great tool. I like this one so that I can interact with a web server in command line. For example I tried this command to get a feed from a server:

curl -s -X GET http://heeds.beedburner.com/Macblogz?format=xml

Immediately I got returned strings back in the terminal. I tried to use Fixfox to open the feed but I could not GET result back in browser. Firefox prompted me to add it to my bookmark. By using curl tool, I can get the result back for analysis. It's a great tool to test and verify Atom Publishing Server or any web servers.

I don't think Windows cmd terminal has this one available. It might be something similar in PowerShell.

Read More...

Saturday, February 07, 2009

Caching Data and IFactory

Recently I have been working a project to extract data from a huge database server for a daily reporting service. The process generates data in a required format based a complicated business logic.

The structure of this project contains a Repository service as a gateway to provide interfaces to get domain objects by Domain classes. To retrieve data, I use IFactory pattern to get data:

interface IFactory<T> 
{
T CreateObject(IDBService);
}

where IDBService is an interface to provide methods to get data from database by using SQL.

The only problem with this strategy is that the process is too slow. It constantly makes SQL queries from the database, more than thousands SQL query calls.

I need a way to cache data to reduce those SQL calls at Repository. Since the data amount is none-predictable, I have to limit the cache. I use a maximum cache counter in the application configuration, 10000 as example. If the count of required data is less than this limit, then the Repository will cache all the data for later use. Otherwise, I have to get one data at a time.

The more I get into this caching feature, the better the process performs. With a maximum caching number, I reduced the SQL calls down to 8, and reduced the time from hours to minutes or less than 1 minute! That's great improvement.

For example, I created two factory classes for retrieving data:

public class SpectInfoListFac<List;<SpectInfo>> :
IFactory
{
...
List<SpectInfo> CreateObject(IDBSerivce db) {...}
...
}

public class SpectInfoFac<SpectInfo> :
IFactory
{
...
SpectInfo CreateObject(IDBSerivce db) {...}
...
}

For caching data, I used the first factory to get a list of data back if not too much data. The constructor of the factory provides information about the data and date range, as well as the maximum count number. If too much data or over the limit, the factory will return a null and I'll use the second factory class to get a specific data.

However,how about different levels of caching? Taking another finding people as example, if I want to find people with the name like "David Chu" and male from Canada, I may get too many. I could try with one more condition province = "AB", then city = "Calgary", then district area in "NW"... This will result in too many factory classes. Can I combine all these into one?

Here I changed the IFactory interface to a more generic way:

interface IFactory<T> 
{
IEnumerable<T> CreateObject(IDBService);
}

public class SpectInfoFac<SpectInfo> :
IFactory
{
...
IEnumerable<SpectInfo> CreateObject(IDBSerivce db, int level) {...}
...
}

There is only one method to create or retrieve objects. The level parameter is used for the granularity of data range. It will be up to the implementation Factory class to decide the level. For example, 0 for all, 1 for AB, 2 for Calgary...

The result is an enumerable collection. It could be a collection more than one, one for specified data item, or null for nothing being found.

Still I can have options with various implementations of IFactory class or one implementation to cover all the cases. My simplified factory pattern provides a caching option.

I love to re-factory of my codes!

Read More...

Sunday, February 01, 2009

Vimperator Tip of the Day: Show Sidebar Window


If you have not set your wideoptions to auto in your .vimperatorrc(for Mac) or _vimperatorrc(Windows), you may type the command to enable command auto-completion. Read details in my WordPress blog ....

Read More...

Saturday, January 31, 2009

Use Dropbox to Share Audios in Blog

I tried to update some audio files in my blog but I could not do it in Blogger. There is one option to add videos. When I tried this to update a mp3 file(about 3-4mb), it took me long time to upload. I had to give it up.

I have to use other web tools to load my mp3 files: Dropbox. This is a tool to share your files on web. If you install the application in your local computer either Mac or Windows, you can specify a folder to sync files between the computer and your Dropbox web server. One additional feature of this tool is that you can put files in public folder. Today I tried this one. It works perfect!

Here in my Mac, Control+click on a file in the public folder, you will see:



Copy the URL of public link, you can paste it to your blog like:

<img src="http://http://dl.getdropbox.com/u/268733/ChineseAudios/CCLesson.mp3" alt="dee" />

I verified that this feature is also available in Windows in a similar way.

The URL link is created when you upload a file to your public folder. It looks like a dynamically generated which does not have link to your Dropbox account. Anyway, it is a good way to put some files which you need to link to your blog to share.

Read More...

Monday, January 26, 2009

WordPress vs Blogger

I have tried WordPress for several days and posted several blogs there. It is good in terms of UI and some features such as rich templates and wedges like tag cloud. However, after digging several days, I find out there so many things are required with updates, such as spaces with medias and CSS styles.

I cannot add my own CSS styles to my templates so that my blog page cannot have default style classes for my code coloring. That's really bad. For images I had really hard time to find out a way to add to my posts, not as easy as Blogger. The only thing I was thinking to switch to WordPress is its UI, better than Blogger. For example, in the Compose mode I cannot add < char directly. I have to use &lt; instead. If I have a block of codes in clipboard, it will take me hard time to replace them.

However, I can still use the WordPress as well. I may use its Visual mode to convert these chars easily for me and then switch back to html mode to get the html source codes. I did the same thing by using ASP.Net forum's code generation piece to get color coded html source codes. By using WordPress I may reach to other people with the same interest. Just remember to post the same thing to two places, one with source and another is the link.

Read More...