SQL Send Email (3)

This is the continuing part of my previous two posts:

• SQL Send Email (2), and
• SQL Send Email(1)

As I mentioned in the previous post, what I need is to save the content to an html file. The main reasona are: I need to get the size of the html content, and I have optiont to send the html content by attachment file. I could do the first one easily by calculating the length of variables which hold html strings and xml content. However, when I come to the point how to send the html content, I have to decide to sent it in the body of email or by a file attachment.

If the file size is too big, more than 10mb in some cases, it will cause mail application (outlook) hanging for a long time when the email is opened. In this case, it is better to send html as an attachment file. In addition to that, I could save the html file to a reporting server site so that a light notifiation email with a link to the html file could be sent.

I have posted several blogs on SQL Server Porjects. It is very easy to create a .Net project to save string to a file. Therefore, I created a SQL procedure .Net project to do the job. The store procedure is very simple:

public partial class StoreProcedures
{
 [Microsoft.SqlServer.Server.SqlProdure]
 public static void SaveToFile(
   string content,
   int overwrite0OrAppend1,
   string fileName)
 {
   ...
 }
 ...
}

In the same way, I have created another .Net based SP to get file size. With these SPs,
here are some SQLs to save content vars to a file:

EXEC SaveToFile @htmlBefore, 0, @fileName;
EXEC SaveToFile @xml, 1, @fileName;

Finally, I use the following SQLs to send the HTML content by email:

IF @fileSize < @fileSizeLimit
BEGIN
  EXEC msdb.dbo.sp_send_dbmail -- SQL SP to send email
    @receipients = @p_recipents, -- recipients passed by parameter
    @subject = @v_subject,
    @body = @v_content,
    @body_format = 'HTML'; -- set content as HTML
END
ELSE
BEGIN
  EXEC msdb.dbo.sp_send_dbmail -- SQL SP to send email
    @receipients = @p_recipents, -- recipients passed by parameter
    @subject = @v_subject,
    @file_attachments = @fileName, -- set attachment file
    @body = @v_msg,
    @body_format = 'HTML'; -- set content as HTML
END

Read More...

Collapse...

SproutCore Presentation

I just finished watching a show on SproutCore by Charles Jolley. Note: the real show starts after 20 minutes (the first part is on Google App Engine). It is very inspiration and a fresh view. I really like the SproutCore infrastructure.

His view on the current web applications is true. There are too much loads on the server side, data, html presentation, and business logic. Click and wait is a pain process for most web applications, and you rely on server availability to see web application content.

I have started to learn jQuey and Dojo recently. This is my evening part time exploring. All these tools are very good ones to move html presentation part to client side so that data can be retrieved and updated by using Ajax on cient's requests. SproutCore framework jumps one more further step, moving the server side business logic to client side. As a result, the server is very thin!

I think this is a new way to develop web applications and we will see more smooth and desktop like web applications on web. Enjoying the exploration!


Skip 20 minutes to start SproutCore presentation.

Read More...

Collapse...

Value2 in Excel VBA

I did a lots of VB programs long time ago. Actually, I started to develop Windows applications by using VB3.0 to VB6.0. Then since the .Net, I switched to .Net C# and VB.Net. However, VB6.0 still has its market. During this year my contract job, I have done a lots VBA applications for Excel, Word and some legacy applications which provides VB6.0 code scripts.

Anyway, I encountered one interesting issue today. I tried to use Value2 in Excel VBA codes to compare two date date types. I know that I put the same date from one worksheet to another worksheet. However, when I tried to search for the same date in another worksheet, the cells with dates are not matched:

v1 = sheet1.Cells(row, col).Value2
v2 = sheet2.Cells(col, row).Value2
If v1 = v2 Then
...
End If

When I changed Value2 to Value, these cells with the same dates are found.

I googled the Value2 and found out that Value2 does not use Currency and Date data types (ref MSDN Office developer center). These values are converted to double type. This conversion caused a very insignificant difference 0.###E-11. This difference makes them different!

By the way, I know that when float values saved in variables, the actual value is very close the "float value" such as 1.2 as 1.199999999, and the value might be dependent on CPUs or OS. That means a float values in one machine may not the exactly same as the value in another machine. For safe gard, when I compare two floating values, I always use absolute the difference to epsilon, same if less then epsilon, not same others.

However, when I looked at C#'s floating data types, I see Equal() operator or == for these data types. Not sure why they are there.

Read More...

Collapse...

Thin Server Archetecture Using Dojo

Just watched a tech show on Youtube.com on Practical Thin Server Architecture Using Dojo:



This well explained the server oriented web development and thin server web development by using client side JavaScript. I have done many ASP.NET web applications. It is true that the server side dominates everything including client side UI's by sending HTML & XML streams from server side. It is very heavy coupled between server and client.

Actually, the most important part between server and client is the data to fill for the client side UI, or passing data with request to server to data source such as DB.

Dojo provides a very good way to provide client side JavaScript codes in Dojo API form so that UI can be easily build and quickly respond by utilizing client side CPU and resources. The Widget I just learned is very clean one, Rating widget. It encapsulates all the UI codes to display icons and events to update icons. The testing HTML page is very clean.

Then web service is another great tool to use: sending request to get and put data between client side and server side. The server could be different web servers. In this way, the web application is very scalable and can be done by components. I can see that many parts are all reusable. Compared to the current way to build web applications, I am getting better picture of those new tools I am interested: REST, Dojo, jQuery, GWT, and YUI.

Read More...

Collapse...

A Dojo Widget: Rating

The title is a tutorial article on how to create a Dojo Widget: Rating by mindtrove' blog. I spent some evenings on this new adventure: Dojo Widget programming. I made some changes and tests to undertand how the Dojo's API working. It is a rewarding process.

Not All Browsers Support Dojo Widget Events

I tried it on Mac and Windows, Firefox, IE and Safari. The widget works fine in Firefox and IE. For Firefox, there was only one minor issue. When I have to Vimperaotr enabled, some keys are not working such as Left, Right, Home and End keys. What I found out is that those key events are trapped by Vimperator and never passed to the widget. After I set pass-through mode(Control-Z), the keys are working fine.

However, Safari is tough. It is a UI mouse driven based browser. It does not let key events to passed to the widget. Only a few HTML DOM controls such as button and text can receive focus but not for others like span. Not sure if there is any way to change Safari's blocking.

Separate Dojo Library from Widget Project

Mindtrove' structure is to put his widget in the folder of Dojo. I don't like this. I prefer to put my customer projects outside of Dojo. Therefore, I tried to put my files like in this structure:

-html (all the test hmtl files)
-images (all the image files)
-scripts
-dojo-release-1.2.0
 -[dojo folders]
 -mindtrove (customer widget)

The customer widget codes are in a separate folder mindtrove, in the same level of other Dojo API library folders.

Here is the codes to load Dojo library files:

<script type="text/javascript"
src="../scripts/dojo-release-1.2.0/dojo/dojo.js"
</script>

And JavaScripts codes to load the widget:

<script type="text/javascript">
  dojo.registerModulePath("info.mindtrove",
    "../../../scripts/dojo-release-1.2.0/mindtrove"
  );
  dojo.require("dojo.parser");
  dojo.require("info.mindtrove.Rating");
  ...
</script>

I tried to move my widget up one level, but the localization files could be not loaded by i18n.js. It looks like that i18n.js does not support cross domain scripts (widget codes outside of Dojo library folder could be treated as cross domain loading).

Add Debug and Double Click Event

When I learn something like this Dojo widget, I always like to add something at the same time. This helps me a lot to better understand structure, logic and codes. As I mentioned above to move my testing html page and Dojo library files not at the root like original codes did.

In addition to that, I have added one more attribute or property called debugKeyCode in the widget. This property is a bool(true or false) data type. I use this property to display keyCode value in the span's Title or tooltip. The keyCode value is saved in the local var preKeyCode when onkeypress event is fired on span object. In this way, I'll be able to see the key codes. Here are some codes I added to Rating.js(under mintrove folder):

dojo.declare('info.mindtrove.Rating', [dijit._Widget, dijit._Templated], {
  ...
  // After currentValue property I added two vars.
  // initial value
  currentValue: 0,
  // Add a property for deug key code, see Rating.html template as well.
  debugKeyCode: false,
  // this is a related property value to hold previous key code
  preKeyCode: "",
  ...
  postCreate: function() {
    ...
    this.connect(span, 'onclick',
      dojo.hitch(this, this._onClick, i+1));
      // listern for mouse doubleclick on the span with the value it
      // represents in the index of star
      this.connect(span, 'ondblclick',
        dojo.hitch(this, this._onDoublClick, i+1));
    ...
  },
  ...
  /*
   * Called when the user doubleclick a star.
   */
  _onDoublClick: function(value, event) {
    if ( this.currentValue >= value )
      this.currentValue -= 1
      this.currentValue = Math.max(this.currentValue, this.minimumValue);
      this._update();
  },
  ...
  _getDescription: function() {
    ...
    var str = dojo.string.substitute(template, [this.currentValue]);
        if ( this.debugKeyCode &&
            this.preKeyCode )
        {
          str = str + ' previous key code: ' + this.preKeyCode;
        }
        return str;
  },
  ...
  _onKeyDown: function(event) {
    ...
    this.preKeyCode = event.keyCode;
    ...
  }
});

In above codes I also added codes to handle double click event. Since Safari does allow span object to get focused and to get onkeypress event, I added this feature so that by using mouse, rating can be added and removed.

Using Firebug Addon
Firebug add-on is a great tool for developers. No only it saves you a lots of time and effort to debug JavaScript codes, but it also provides some information normally you cannot get from the browser.

For example, Dojo supports Firebug by using some attributes when Dojo library is loaded. If you enable two attributes in djConfig like in following codes, you will see the Get Dojo library files in Firebug's console tab. That great to see what is downloaded and what are failed if you set them in wrong location:

<script type="text/javascript">
  var djConfig={
    ...
    parseOnLoad:true,
    isDebug:false
    };
</script>

Another great feature of Firefbug is that you can view the dynamically created DOM elements. By using Dojo and jQuery, you can create DOM elements, change style class, do animations on fly. Normally, I would like to see those elements in html but they are not available from browser's View Page Source or View Selection Source. Firebug does reveal the dynamic changes. That's great help.

For example, if you view the HTML tab in Firebug for this Rating widget, you will see the spans dynamically created and hidden text as well. By learning this Widget tutorial, I discovered a lots of new things, both in Dojo and Firebug.

Read More...

Collapse...

JavaScript and Ajax for Web Applications

Just read an article about Fixing Web, Part 1 referred by Ajaxian blog. It mentioned leading three tools used by developers: jQuery, Dojo and YUI. I know the first two already, actually in the learning progress and very impressed by them, the last one is new.

YUI is Yahoo! User Interface Library with set of utilities and controls based on JavaScript. I went to Yahoo! developer site for YUI and watched the overview vedio. It is another very impressive tool I am going to learn.

As the article says, "We've made major progress on the web since 2005 and the rise of Ajax. JavaScript toolkits like JQuery, Dojo, and YUI have expanded what we can do with web browsers and increased our productivity...", I really like to sharp my skills with these great tools.

Read More...

Collapse...

Dojo Does Raise Exceptions

Dojo does raise exceptions in some cases. As I tried out some example codes from Dojo Quick Start Guide: Events, I got exceptions.

The following codes explains the case:

var mineObj = {
  aMethod: function() {
    console.log("Running mineObj method A");
  },
  bMethod: function() {
    console.log("Running mineObj method B");
    }
};
dojo.addOnLoad(function() {
  // run bMethod() whenever aMthod gets run
  dojo.connect(mineObj, "aMethod", mineObj, "bMethod");
  //dojo.connect(mineObj, "aMethod", mineObj, "bMethod1"); // exception!
  //dojo.connect(mineObj, "aMethod1", mineObj, "bMethod"); // OK
  // start chain of events
  mineObj.aMethod();
  //mineObj.aMethod1(); // JavaScript error!
});

The second connect(commented out) will throw an exception in FireBug:

uncaught exception: dojo.hitch: scope["bMethod1"] is null (scope="[object Object]")

The connect fails to invoke method bMethod1 as it is not defined. However, the third connect call is OK.

The last commented out code is a JavaScript error in FireBug:

mineObj.aMethod1 is not a function

Read More...

Collapse...

No Exception On Dojo Chain Calls

I spend about 1 or 2 days a week on learning and exploring Dojo. I really like this JavaScript based API framework. I found one very nice feature of Dojo. Not only you can call Dojo methods by using Dojo chain calls, and you don't need to worry about if any middle part of the chain returns undefined or not.

For example, I added the following codes to the head's script section:

dojo.addOnLoad(function() {
  console.log("OnLoad ready to add events");
  dojo.query("#testHeadingEvent")
    .style("cursor", "pointer")
    .connect("onclick", function() {
      this.innerHTML="I've been clicked";
      });
  console.log("Onload fires adding event on headEvent");
});

the query() function returns a DOM node by id. If the node is not found, it's still OK. No exception is thrown. I can still see the messages print out on the console(by using FireBug add-on). Isn't that nice?

Read More...

Collapse...

SQL Send Email (2)

Microsoft SQL Sever 2005 supports xml data type. This data type provides a very convenient way to hold data from SQL SELECT statement. Continue from the previous blog, the following codes set xml with data from MyData table(id, name and value three columns):

DECLARE @xml xml;
DECLARE @htmlAfter NVARCHAR(MAX);
..
SET @xml =
-- Checks if count is 0 or not. Avoid NULL value in the result.
CASE
 -- @count is a var to get the count from MyData table.
 WHEN @count is null OR @count = 0 THEN null
 ELSE
 -- Get data from MyData table
 -- Note: the three columns should match the previous
 --       table header column definition (3 cols)
   (SELECT
      td = CAST(t.ID AS VARCHAR), '',
      td = t.NAME, '',
      td = t.VALUE, ''
    FROM MyData t
    FOR XML PATH('tr'), TYPE -- As XML output
   )
END;

SET @htmlAfter = N'</table></div>'

The comments should well explain how to set xml in this example case. The reason I use xml data type is that it can hold large amount of data. I tried to use NVARVAR(MAX) before. In one case, the data retrieved by SELECT are millions rows of data and my var was overflowed with some data missing. When this happened, there was not any errors or indications. It took me a day to figure it out. With xml data type, The problem is gone.

The next article will continue to discuss how to use a SQL Database Project to save results to a file and get back text size information before sending out email.

Read More...

Collapse...

SQL Send Email (1)

I have created a stored procedure to send an email notification of some data. This is quite convenient to get notifications about database status, reports and any related business needs.

Basically, this is done by using Microsoft SQL 2005's build-in msdb.dbo.sp_send_dbmail. It looks like that some of email related SPs have been faded out since the SQL Reporting Service launched. The reporting services provides much versatile features and allow developers to design more ad-hoc web based reports. However, email notification still has its uniqueness and useful applications.

Since my SP is used for specific notification about data status, therefore, only one parameter is defined, as recipients of emails separated by comma.

The content of the email is HTML text. Depending on the length of text, if it less than the size of email limit(see my previous SQL Server Database Mail setting), then the data are formatted as HTML; otherwise, the file will be saved as a file and it will be attached to the email out.

The following codes set HTML header part:

DECLARE @headerHTML  NVARCHAR(MAX);
...
SET @headerHTML =
N'<html>
<head>
<script type=''text/javascript''>
function toggleIt(id) {
  var post = document.getElementById(id);
  if (post.style.display != ''none'') {
    post.style.display = ''none'';
  }
  else {
    post.style.display = '''';
  }
}
</script>
</head>';

Here I added some JavaScript to hide and display detail views of data by function toggleIt(id). For example, the following codes define a title node with a link referenced to the function:

DECLARE @htmlTitle1 NVARCHAR(MAX);
...
SET @htmlBefore =
N'<a href=''javascript:void(0)''
onclick=''javascript:toggleIt("1")'';
style=''text-decoration:none'' title=''Expand/collapse this item''>
<H2>[+/-]My Data</H2></a>
<div class=''post-body''
id="1" style="display:none">
<table border="1">
<tr>
<th>ID</th>
<th>Name</th>
<th>Value</th>
</tr>';

After the link tag(a), the above codes add a table tag with a row of headers defined for the next data retrieved from SQL.

The next blog will continue on the topic on how to use xml type to hold SQL retrieved data.

Read More...

Collapse...

Enabling PHP for Apache Server in Leopard

While I was doing coding to learn Dojo, I reached one point that I needed PHP module enabled for my Mac Apache server. By default, it is not enabled.

I found information about enabling PHP for Apache Server. The steps are very simple, however, I did not have the right to edit the file httpd.config in /private/etc/apache2. The file is owned by root user. Soon, I found a way to edit the file: logging as root user, see my Mac blog entry.

After all these settings, I made my Dojo page working with php page. Basically, Dojo web page calls the server side php page to pass information back. It is very cool. This is also my first experience with PHP web page development. One lesson I learned is that for a php page, you have to indicate it by <?php and no space between ? and php.

Read More...

Collapse...

Doio: JavaScript Tool for Web Page Development

Today, I just started to learn Dojo. It is something very similar to jQuery. However, it provides core and APIs directly for Web Page development. It is very cool!

Just tried the Hello World example to display a button, to run Dojo script, to Get and Post back to server. I used my Mac with PHP to test it. It is very interest! The tool provides universal JavaScript APIs and you don't need to worry about brower syntax for DOM elements. I like it very much. This is another area I would like to invest my energy into it.

One neat thing about Dojo is that you don't need to install Dojo package or core APIs into your server. You can define <script ... src="http//..." /script> to either AOL developer's site or Google's developer's site. You will always have the updated engine for your Dojo scripts.

Read More...

Collapse...

VIM, Initial Configuration and PlugIns

I have been used VIM for about weeks and started to fall in love with it. I am still in learning stage and found many some settings very neat.

The first thing is to configure VIM initial settings. This is done through the configuration file ~/.vimrc for Mac OS and C:\_rimrc for Windows. You can find many sites with good .vimrc file and use it as start. If you don't like some settings, you can change it.

Based on vimrc in the article of VIM Introduction and Tutorial, I made some changes. For example, I prefer the background color as white and tab as 2:

  "Tabs are 2 spaces:
  set tabstop=2
  "We use a white background, don't we?
  set bg=white

I found some settings and added them in .vimrc:

  "Set characters shown for special cases such as:
  "wrap lines, trail spaces, tab key, and end of line.
  "(must be turned on whith set list)
  set listchars=extends:»,trail:°,tab:>¤,eol:¶
  "Set moving around at the end of line back to previous line by
  " key and coursor keys, and normal movememt h and l keys
  set whichwrap=b,s,<,>,h,l

  "Enable C style indention
  set cindent

I love special char setting, which displays tab, trial spaces, and end of line as special characters. I have to use command ":set list" to enable the setting. The move around setting enables backspace, arrow keys, and move keys h and l to move back previous lines.

There are many VIM plugins available. This is quite new for me. I found only one is working for me so far: Align. To use it, just type in the command ":Aligh =" when you in visual mode with several lines selected. Then the text will be aligned by = sign to separate left part and right part aligned by = sign.



After aligning, the text are aligned by =:



To install it, find the folder where the file "Align.vba.gz" located in Terminal. Type in the command:

  vim Align.vba.gz

The file is opened by VIM. Then typing the command to read scripts and then quit:

  :so %
  :q

Reopen VIm again. The command :Align is available!

Read More...

Collapse...

Scaling SVG Graphics with Javascript (3)

Here I would like to summarize my experience and tips about scaling svg with JavaScript.

Change Only Red Marked Scripts

First, I have shown two ways to scale svg graphics. The first way is to add scripts to both html and svg files. Even the scripts are quite long, actually you need to do is just copy-and-paste scripts. As I explained in demo, the blue area of scripts are ones you do not need to make change. The only scripts are marked as red or marked by '!!!...'.

I tried to make the scripts as generic as possible so that you do not need to touch. The only scripts are variables you have to set. As in the first way to call functions from html to svg, you need to set the object tag id in html being consistent with a var in svg. This will enable the svg to attach functions to object element in html.

I would recommend you to set the var in svg first as unique as possible. For example, you can name the var as first 10 char of svg plus a time stamp as the var name like 'ClipArtDog200810281259'. If you do this, it most likely there will be no other svg in your htlm file have the same svg with this name. This may reduce your touch svg file. What you need is to verify svg's top g id name.

The second way to scale svg is done by using JavaScript only in svg file. Normally, you don't need to change the scripts. I have defined some 'constant' vars in the top part of JavaScript for the size of circle and colors for the scale down and up. You may change them at your preference. I'll add one more method to enable and disable in-svg-scaling-scripts. So you will have control from html or in svg to disable the behavior.

SVG Grphics

As you know, svg graphics are defined as in xml files. It offers great control for art designer and programmers to create vector graphics with animation effect and unlimited potentials.

I used the InkScape tool to create svg with scripts(actually vim for adding scripts). When you create svg files, you may see the scaling scripts not working well. There are many reasons that may cause the problem. You should examine the svg xml file by a text editor. One issue is the transform function or tag used in the top g tag. This is not necessary because you could place the graphics in the right position by setting other position and size tag attributes. If you find it, you can remove it and use InkScape to make proper adjustment, selection and save.

Another issue is that you should not try to use scripts to create graphics dynamically since some hard-coded size values may not be scaled. As you can see my small circle with size of 5 px as radium is small enough but they are not scaled. These circles are created by script for scaling UI.

Not Working for SVG on Different Sites

As I discussed in the past two blogs, if you place your svg on different sites other than the size where your html file is hosted, the JavaScripts will not work! This is JavaScript security issue. I think there is nothing we can do. Just image if you would refer to another site to execute JavaScript codes, that would open a door for hackers to pull valuable information about the client sites and you have not place to trace down.

JavaScript has is security concern for adding scripts on html file. The browser will not allow you to execute scripts on a mysterious site.

Therefore, in order to make it to work, you have to have the total control of your html file and svg file on one site.

I am so sorry that I could not show you the demo on my blog since I cannot place svg to my blog site.

That's great experience for me to explore svg and JavaScript. I may add some more features like mirror-flip and transform to svg. I'll blog those and update my demo on my blog. Enjoy programming!

Read More...

Collapse...