Sunday, November 21, 2010

iPhone Dev: Build + Analyze

There are many ways to detect iPhone project's code potential issues such as memory leak and performance issues. One very simple way is to build with Analyze. There is shortcut key for it: Shift+Command+A.

My current project is targeted to Simulator - 4.1. When I first time tried to use this Analyze feature, I got several messages saying "Analyzer skipped this file due to parse errors" for a lots of files. There were no analyze result for those files. Soon I found a solution to resolve most skips. SO has several posts on this issue. One of solution is to add a line to project settings (from project Get Info):

However, I still got this skip warning for MyLogger class. Eventually, I found is a C code issue. Here is a definition of enum type:

typedef enum {
LogLevelDebug = 1,
LogLevelWarning = 2,
LogLevelError = 3,
LogLevelInfo = 4,
LogLevelNone = 100
} UIUInteger, MyLoggerLevel;

After I removed UIUInteger, I got no skip warnings.


Saturday, November 20, 2010

Powershell Scripts and Batch File

Normally I create a PS script project and then run it as a job in a batch file. Occasionally, I need to execute a batch job from my PS script. Here are two tips.

First to execute a PS script in batch file, run it from PS with script name and additional arguments:

Powershell myscript.ps1 argument1, 'argument two'

Notice that if there is a space in an argument, use single quote instead of double quotes.

Second tip is about calling a batch file from PS script, use the cmd /c to execute a batch file. For example, the following case is to use PS script to format the current date time asa string and then run a bat with the string as its argument:

# get the current date time
$date = Get-Date
# format the date time as a string
$argDateTime = "{0:d4}{1:d2}{2:d2}_{3}{4}{5}" `
-f $date.Year, $date.Month, $date.Day, $date.Hour, $date.Minute, $date.Second
# build a command line: $args[0] is a bat file
$cmdApp = ("{0} {1}" -f $args[0], $argDateTime)
# run the bat with a formatted date time string as argument
cmd /c $cmdApp


Saturday, November 13, 2010

NANT: Exec a Target with Arguments

Recently I encountered an minor issue when I tried to use NANT to build a Visual Studio solution. Finally I narrowed down to the problem. It was the Exec target failure when one of its parameter argument contains a space. I thought that I could use a function or tool to convert the argument (a path) from a long name to DOS short name. Unfortunately, there is not such function in NANT for Windows build.

I posted a question to SO. I soon got a answer. In addition to that, I also figured out a way to resolve the issue. See my answer for detail explanation with a simple test case.

By the way, I used NANT extensively long time ago when I was a system analyst in a company's RTD, about 10 years ago. I created a NANT project to build an open source project by using Microsoft .Net Framework SDK (1.0) without using Visual Studio. Later I also used NANT in an Agile environment as a way to test UNIT tests, and to deploy the project. I really enjoy this great tool.


Saturday, November 06, 2010

Using Excel to Compare SQL Database Tables

It is a very common task to compare data content between two SQL database tables. If two tables are within a SQL database, I normally use TSQL EXCEPT and UNION to get difference, detail in my previous blog: Comparing Two Tables By SQL Stored Procedure.

I could add linked server in SQL database so that the same method could be used to compare two tables in two difference SQL databases. However, it would cause too much trouble, such as change SQL settings, or caching millions rows of data from linked server.

The alternative way is very simple. I just run a SQL query to get data in SQL server management studio. Then copy the result to excel, either copy or export. Do the same thing against another SQL server again. By using Excel, it is very easy to compare cell by cell to get any difference.

The following are steps I use in Excel.

Excel Worksheets

The excel file contains four worksheets. The first one is comparison, which will be discussed in detail latter. The second one an third one are data result from two SQL database tables, for example, source_table1 and test_table1. SQL worksheet contains SQL query used to get data.

In the worksheet of comparison, the comparison is done by the following formula. For example, for cell A1 column:

=IF(source_table1!A1=test_table1!A1, source_table1!A1, "Diff (" & source_table1!A1 & " : " & test_table1!A1 & ")")

For the data cell A2:

=IF(source_table1!A2=test_table1!A2, "YES", "Diff (" & source_table1!A2 & " : " & test_table1!A2 & ")")

The formula of A2 comparison applies to all other data cells. Just copy the formula and paste to all the cells you want to compare. Excel is smart enough to adjust cell coordinates to get the comparison results. The following is an example of comparison result:

If you have thousands of raws of data in Excel, you can use Excel's Filter feature to narrow down the differences easily. From menu Data|Filter|AutoFilter to turn on the filter:

On the header filters, you can configure filter to whatever levels you want. Here are two examples:

As you can see, by using Excel it makes much easy to compare two tables and find out the differences.

Import Data Issues

The above method works fine for most of cases. I had a problem to get data from SQL to Excel last week when I was working on one SQL database table. I had spent for 2 hours+ finally I found the problem. Some rows contain double quotes in their raw data. For example, there was " being used in a table as inch unit. This unbalanced quote caused the Excel to import rows from either clipboard or csv file.

Excel treats " as string data type mark. In order to make importing to work, it has to be escaped. I used VIM to resolve the issue. Just simply search for " by typing /" in VIM, it will mark and find those quotes. Then I used VIM's Replace feature to escape all the single quote by the command(replacing " with ""):


After the normalization of data, I got the expected data from SQL query result to my Excel.

Excel Being too Smart

This issue is beyond the main topic of comparing data between SQL database tables. However, it is worth to leave a note here.

Here is the story of what I do to import or copy data from SQL query result to Excel. For the first data source, I copy the result of SQL query into clipboard. I think that by default, the delimiter used to separate columns is a tab char. I use Paste Special feature from Excel to paste pure text. Then Excel will prompt me to select what delimiter is and preview data in a grid. I like it very much. However, when I repeat the same steps to copy the data result from the second data source, the Excel does not prompt me, nor provide preview. It just copy data to columns. It is too smart to remember the delimiter.

However, I would like to preview data before pasting. I could not find a way to disable this smart feature. Finally I found an alterative way. I pasted the data to VIM first. Then replace the tab to semi-command (make sure it is not used in the raw data):


Then copy the whole text to Excel. This change will fool the Excel to prompt the preview as expected.