simpcode

MySQL Backup Batch Script With 7Zip And Email Alert

2011-12-10T08:43:00.001+08:00

This is yet another MySQL Backup batch script with the following features:

Dumps each database into separate folder and script.
Allows to set database(s) excluded from backup.
Each script is compressed (7zip) and encrypted with a password.
Keeps the last n copies of backups.
Email alert on backup errors.

It requires the following tools:

MySQL bin utilities - mysql.exe and mysqldump.exe.
7zip compression tool.
blat emailer tool.

Note: It can backup any networked MySQL databases (either Windows or Linux platforms), as long as the MySQL bin utilities can access them.

This script is easy to configure and highly customizable. You only need to change the values in the "Begin/End Settings" sections. Please refer the script header remarks for more information and other options.

How to use:

1. Create a batch file named as "weizh-mysql-backup.bat" with the following contents:

@ECHO OFF

REM =====================================================================================
REM Copyright 2011 Weizh Chang
REM 
REM This program is free software: you can redistribute it and/or modify
REM it under the terms of the GNU General Public License as published by
REM the Free Software Foundation, either version 3 of the License, or
REM (at your option) any later version.
REM
REM This program is distributed in the hope that it will be useful,
REM but WITHOUT ANY WARRANTY; without even the implied warranty of
REM MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
REM GNU General Public License for more details.
REM
REM You should have received a copy of the GNU General Public License
REM along with this program.  If not, see <http://www.gnu.org/licenses/>.
REM =====================================================================================

REM =====================================================================================
REM Program    : Weizh MySQL Backup Batch Script
REM File       : weizh-mysql-backup.bat
REM Version    : 1.0.0.0
REM URL        : simpcode.blogspot.com
REM Description:
REM
REM Please change the settings in the Begin/End Settings sections below.
REM
REM This script requires:
REM - MySQL bin utilities - mysql.exe and mysqldump.exe.
REM - 7zip compression tool (www.7-zip.org).
REM - blat emailer tool (www.blat.net).
REM
REM Features:
REM - Dumps each database into separate folder and script.
REM - Allows to set database(s) excluded from backup.
REM - Each script is compressed (7zip) and encrypted with a password.
REM - Keeps the last n copies of backups.
REM - Email alert on backup errors.
REM =====================================================================================


@ECHO ON


REM ================ Begin MySQL Settings ====================

SET mySQLBinDir=C:\Program Files\MySQL\MySQL Server 5.0\bin\

SET host=192.168.100.2
SET uid=admin
SET pwd=adminPassw0rd
SET port=3306

SET backupDir=D:\MySQL_Backup\
SET backupPrefix=MySQL_3306_
SET backupCopy=3

REM ==== Set mysqldump switch (optional)
SET sqldumpSwitch=--single-transaction

REM ==== Set database excluded from backup (comma separated, without quotes and spaces)
SET excludeDb=information_schema,performance_schema,test

REM ==== Set the 7zip exe directory and password
SET zipDir=C:\Program Files\7-Zip\
SET zipPwd=yourZipPassw0rd

REM ================ End MySQL Settings ======================

REM ================ Begin Email Settings ====================

REM ==== Set all values to empty if not using email alert
SET emailServer=mail.example.com
SET emailPort=25
SET emailUid=admin@example.com
SET emailPwd=adminPassw0rd
SET sendFrom=admin@example.com
SET sendTry=5
SET subject=MySQL Backup Alert

REM ==== Set receiver email (comma separated, without quotes and spaces)
SET sendTo=alex@example.com

REM ==== Set the blat exe directory
SET emailDir=C:\blat\

REM ================ End Email Settings ======================


REM ================ Begin Backup ============================

ECHO Performing backup...

REM ==== Set date and time
For /F "tokens=2-4 delims=/ " %%a IN ('DATE /t') DO (SET mydate=%%c-%%a-%%b)
SET mytime=%TIME:~0,8%
SET mytime=%mytime: =0%

REM ==== Set command variable
SET doShowDb="%mySQLBinDir%mysql" -B -N -e "SHOW DATABASES" -h %host% -u %uid% -p%pwd% -P %port%
SET doDump="%mySQLBinDir%mysqldump" %sqldumpSwitch% -h %host% -u %uid% -p%pwd% -P %port%
SET doZip="%zipDir%7z.exe" a -t7z -mx9 -mhe -p%zipPwd%
SET doZipTest="%zipDir%7z.exe" -r -p%zipPwd% t

REM ==== Loop and backup each database
SETLOCAL ENABLEDELAYEDEXPANSION

SET errorDbTemp=

REM ==== Test the connection
!doShowDb!
IF !ERRORLEVEL! NEQ 0 (
    SET errorDbTemp=MySQL Connection Error
    GOTO EndLoop
)

FOR /F "tokens=*" %%G IN ('"!doShowDb!"') DO (
    
    SET exclude=0
    SET errorFound=0

    IF NOT "%excludeDb%"=="" (
        FOR %%S IN (%excludeDb%) DO (
            IF /I "%%S"=="%%G" SET exclude=1
        )
    )

    IF !exclude!==0 (
        REM ==== Set backup directory and file for this db
        IF NOT EXIST "%backupDir%%%G" MD "%backupDir%%%G"
        For /F "tokens=2-4 delims=/ " %%a IN ('DATE /t') DO (SET backupDate=%%c-%%a-%%b)
        SET backupTime=!TIME:~0,8!
        SET backupTime=!backupTime: =0!
        SET backupFile=%backupDir%%%G\%backupPrefix%%%G_!backupDate:-=!_!backupTime::=!

        REM ==== Perform MySQLDump
        !doDump! --databases %%G > "!backupFile!.sql"

        IF !ERRORLEVEL! NEQ 0 (SET errorFound=1)

        IF NOT EXIST "!backupFile!.sql" (
            SET errorFound=1
        ) ELSE (
            REM ==== Zip then delete the sql file
            !doZip! "!backupFile!.7z" "!backupFile!.sql"

            IF !ERRORLEVEL! NEQ 0 (SET errorFound=1)

            REM ==== Test the zip integrity
            !doZipTest! "!backupFile!.7z"

            IF !ERRORLEVEL! NEQ 0 (SET errorFound=1)
 
            DEL /Q "!backupFile!.sql"
        )

        IF !errorFound!==1 (
            REM ==== If errors, clean up and append the db name to a temp variable
            IF EXIST "!backupFile!.*" DEL /Q "!backupFile!.*"
            SET errorDbTemp=!errorDbTemp!%%G;
        ) ELSE (
            REM ==== Delete old zip copies, sort by file name descending
            IF EXIST "%backupDir%%%G\%backupPrefix%%%G_*.7z" (
                SET zipCount=0

                FOR /F "tokens=*" %%U in ('DIR /A:-D-H /O:-N /B "%backupDir%%%G\%backupPrefix%%%G_*.7z"') DO (
                    SET /A zipCount+=1
                    IF !zipCount! GTR %backupCopy% DEL /Q "%backupDir%%%G\%%U"
                )
            )
        )
    )
)

:EndLoop

ENDLOCAL & SET errorDb=%errorDbTemp%


IF NOT "%errorDb%"=="" GOTO SendError

GOTO Finish

REM ================ End Backup ==============================


REM ================ Begin Send Error Email Alert ============
:SendError

IF "%emailServer%"=="" (
    ECHO Backup error on %errorDb%
    GOTO Finish
)

ECHO Sending error email alert

REM ==== Set sendTo with surrounding quotes
SET sendTo="%sendTo%"

REM ==== Set email temp file
SET tmpErrorFile="%~dp0mysql_backup_error-%RANDOM%.tmp"

(
ECHO Dear Database Admin,
ECHO.
ECHO There were errors while performing the MySQL Backup on the following databases:
ECHO.
ECHO Date: %mydate%      Time: %mytime%
ECHO Host: %host%      Port: %port%
ECHO.
ECHO -----------------------------------------------------------------
ECHO %errorDb%
ECHO -----------------------------------------------------------------
ECHO.
ECHO.
ECHO Regards,
ECHO MySQL Server Admin
) >%tmpErrorFile%


REM ==== Send error email alert
"%emailDir%blat.exe" %tmpErrorFile% -server %emailServer%:%emailPort% -f %emailUid% -u %emailUid% -pw %emailPwd% -from %sendFrom% -to %sendTo% -subject "%subject%" -try %sendTry%

REM ==== Cleanup
IF EXIST %tmpErrorFile% DEL /Q %tmpErrorFile%

REM ================ End Send Error Email Alert ==============


:Finish
ECHO Backup process completed

2. Set values in the Begin/End MySQL Settings and Begin/End Email Settings sections. Put this file into any folder you like.

3. You may use the Windows Task Scheduler to run this script routinely or at anytime you prefer.

SQL Server 2008 R2 Express Installation And Setup

2011-12-02T11:17:00.000+08:00

This post provides a basic guideline about installing SQL Server 2008 R2 Express together with the SQL Server Management Studio.

1. If you do not have Windows Power Shell installed, please install it from here.

On that page, find the Download information > Windows Management Framework Core (WinRM 2.0 and Windows PowerShell 2.0), then select Download the Windows Management Framework Core for Windows XXX package now, whereby XXX is your Windows edition (thanks to this site for the link).
Note: Power Shell is only needed by SQL Server Management Studio. You may skip this step if you do not want to install the studio.

2. Download and install "SQL Server 2008 R2 Express With Tools" from here.

Note: To do a clean install, you may first want to remove any existing SQL Server 2008 (or R2) database and utilities from the Control Panel > Add/Remove Programs.

3. When prompted, select "New installation or add features to an existing installation". Follow the instructions.

Instance Configuration > Named instance: You may want to rename it to "SQLEXPRESS2008R2" or just use the default.
Server Configuration > Service Accounts: Enter any existing Windows account name and password which has sufficient rights (e.g. the admin account).
Database Engine Configuration > Authentication mode: Select "Windows authentication mode" (this can be changed later).
Restart the computer if needed.

4. Go to Start > Microsoft SQL Server 2008 R2 > Configuration Tools > SQL Server Configuration Manager:

SQL Server Services > SQL Server: Make sure it is running.
SQL Server Network Configuration > Protocols for xxxx: You can enable and set the remote network connection settings here.

5. Go to Start > Microsoft SQL Server 2008 R2 > SQL Server Management Studio > Connect to server:

Server name: serverName\instanceName (e.g. myserver\SQLEXPRESS2008R2)
Authentication: Windows Authentication

If everything is correct, you should be able to connect to the database now.

Setup Security and Authentication using SQL Server Management Studio

1. Enable "SQL Server Authentication Mode":

In Object Explorer > Right-click the server to Properties > Security > Server authentication: Select "SQL Server and Windows Authentication mode".
Once set, you may need to restart the service from "SQL Server Configuration Manager".

2. Enable "sa" login:

By default the "sa" account is disabled. To enable:
In Object Explorer > Security > Logins > sa > Right-click to Properties:
- General: Set the password.
- Status > Login: Select "Enabled".

3. Add new SQL Server authentication:

In Object Explorer > Security > Logins > Right-click to New Login > Select "SQL Server authentication": From here, you can set the login name, password, default database and other information.

Additional Resources:
Introduction to SQL Server Express 2008 R2

Assemblies Merging With Post-build Event

2011-11-13T12:18:00.000+08:00

ILMerge is a useful utility to merge multiple .NET assemblies into a single assembly. By using the "Post-build" event command line, we can automate the merging process when building our projects.

There is a better method by using MSBuild. Please refer Assemblies Merging With ILMerge and MSBuild (TODO).

For my projects, I use a ILMergeHelper batch file to run the ILMerge process. By using this script, all files inside the bin output are copied over to a sub folder "ReleaseMerged". All merging process are done within this sub folder, as such, nothing in the bin output folder will be affected.

Syntax:

ILMergeHelper.bat <targetDir> <targetFileName> <solutionName> <inputAssemblies> [cleanAssemblies]

Arguments:

targetDir - The merged output directory.
targetFileName - The merged output file name.
solutionName - The project solution name.
inputAssemblies - The assemblies to be merged. Must enclose with double-quotes, separate each assembly name with a space within the quotes. Supports wildcards.
cleanAssemblies - Optional. The files to be deleted after the merging completed. If not set, will be same as inputAssemblies. Must enclose with double-quotes, separate each assembly name with a space within the quotes. Supports wildcards.

Note: Please see example below.

Setup ILMerge and ILMergeHelper (only set once):

1. Download and install the ILMerge utility.

Note: You may copy the "ILMerge.exe" and put it into any custom folder, as long as you change to the correct path in your post-build command line.

2. Create a batch file named as "ILMergeHelper.bat" with the following contents:

@ECHO OFF

REM #############################################################################################################
REM #### ILMergeHelper.bat
REM #### A utility to perform ILMerge using the Post-build event from the Visual Studio project
REM #### Author: Weizh Chang
REM ####
REM #### Usage: ILMergeHelper.bat <targetDir> <targetFileName> <solutionName> <inputAssemblies> [cleanAssemblies]
REM ####
REM #### Example (set in Post-build event): 
REM #### ILMergeHelper.bat $(TargetDir) $(TargetFileName) $(SolutionName) "support*.dll" "support*.dll"
REM #############################################################################################################

@ECHO ON

REM #### Set variables from arguments

SET targetDir=%1
SET targetFileName=%2
SET solutionName=%3

SET inputAssemblies=%4
SET inputAssemblies=%inputAssemblies:"=%

SET cleanAssemblies=%5
IF (%cleanAssemblies%)==() SET cleanAssemblies="%inputAssemblies%"
SET cleanAssemblies=%cleanAssemblies:"=%

SET mergedDir=%targetDir%..\ReleaseMerged\

REM #### Set the merged file name
SET mergedFileName=%targetFileName%

REM #### Remove existing merged directory
IF EXIST %mergedDir% RD /S /Q %mergedDir%

REM #### Copy all contents from the build target folder to the merged folder
XCOPY %targetDir%* %mergedDir% /i /E /Y /Q
CD /D "%mergedDir%"

REM #### Perform ILMerge
%~dp0ILMerge /lib:%mergedDir% /wildcards /allowDup /ndebug /out:%mergedDir%%mergedFileName% %targetFileName% %inputAssemblies%

REM #### Delete all assemblies that have been merged
DEL /Q %cleanAssemblies%

3. Put this file into the same folder as ILMerge.exe (e.g. C:\Program Files\Microsoft\ILMerge\).

4. Open your project's Properties > Build Events > Post-build event, and enter the ILMergeHelper command. For example:

"C:\Program Files\Microsoft\ILMerge\ILMergeHelper.bat" $(TargetDir) $(TargetFileName) $(SolutionName) "support*.dll MySql*.dll" "support*.* MySql*.* test.xml"

Note:
The above example merges all assemblies named support*.dll and MySql*.dll, then delete all files with names support*.*, MySql*.* and test.xml.
This example uses macros $(TargetDir) and $(TargetFileName) as the merged output directory and file name. You may change them to other values you preferred.

5. Build the project. The merged assembly is created inside the "ReleaseMerged" folder.

Additional Resources:
Assemblies Merging With ILMerge and MSBuild (TODO)

MySQL Table Name Starts With 'test'

2011-11-06T14:32:00.000+08:00

By default, the `mysql.db` table contains rows that permit access by any user to the `test` database and other databases with names that start with `test`.

To disable this, after a fresh MySQL installation, please run the following command:

DELETE FROM mysql.db WHERE Db LIKE 'test%';

Capture The DataGridView CheckBox Column Click Event

2011-11-06T14:29:00.000+08:00

The .Net DataGridView CheckBox Column control can be a bit tricky to use. Recently I've encountered a problem where I need to do some calculations when a user checks or unchecks the CheckBox. However, the events triggered always seems to return the previous value. E.g. When the CheckBox is checked (true), the value is captured as uncheck (false), and vice-versa.

After some digging around, I found that using both "CellContentClick" and "CellContentDoubleClick" events can do the trick.

For example, we have a grid named "myGrid" with columns "colTick" (the CheckBox) and "colAmt" (an amount field). Bind this grid to the following 2 events:


private void myGrid_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
    GetTotalSelection(e.ColumnIndex, e.RowIndex);
}

private void myGrid_CellContentDoubleClick(object sender, DataGridViewCellEventArgs e)
{
    GetTotalSelection(e.ColumnIndex, e.RowIndex);
}

Then we process the checked row values in the "GetTotalSelection" method, accumulating the total selected amount into a private variable "_totalSelAmt":


private decimal _totalSelAmt = 0;
....

/// <summary>
/// Calculates total amount selected.
/// </summary>
private void GetTotalSelection(int colIndex, int rowIndex)
{
    if (colIndex < 0 || rowIndex < 0)
        return;

    if (myGrid.Columns[colIndex].Name != colTick.Name)
        return;

    myGrid.EndEdit();

    bool tick      = Convert.ToBoolean(myGrid[colIndex, rowIndex].Value);     // Gets the current row check value.
    decimal selAmt = Convert.ToDecimal(myGrid[colAmt.Name, rowIndex].Value);  // Gets the current row amount.

    if (tick)
        _totalSelAmt += selAmt;
    else
        _totalSelAmt -= selAmt;
}

Now, every time a user checks or unchecks the CheckBox, the variable "_totalSelAmt" will be updated with the latest total selected amount.

Online Syntax Highlighting Using ToHtml.com

2011-10-04T15:08:00.000+08:00

Just want to share a source code syntax highlighting tool I discovered recently that is easy to use and supports many different languages, even the rare ones.

The steps are rather straight forward. Just go to ToHtml.com, paste your code inside the "Source Code" panel. Then choose your language type and hit "Highlight". In a short while your code will be formatted into the "HTML code" panel, which you can just copy and paste directly into your web site or blog. In addition, you can also select the style you want, and preview the formatted output.

There is no worries on any special characters like > or <, it will take care for you as well.

Originally I was trying to use SyntaxHighlighter, but after spending much time troubleshooting, it just won't work on this blog. I'm not sure where I did wrong, maybe I will give it another shot when I have more time to spare. However, for now, I think this online tool already can fulfill my needs. Cheers :)

Update MySQL Table Field With Value Derived From Another Table

2011-10-04T14:16:00.002+08:00

If you need to update certain field in a table with value derived from another table (e.g. performing a sum), you could do this using a JOIN statement in the following way (which I referred from this post):

UPDATE table1 a
JOIN
   ( SELECT batch_no, SUM(sale_amt) AS sum_sale_amt
     FROM table2
     WHERE source = 'SALE'
     GROUP BY batch_no
   ) b ON 
   b.batch_no = a.batch_no
SET a.total_sale_amt = b.sum_sale_amt
WHERE a.cancel <> 1;

In the above example, we sum the `sale_amt` from `table2` where the `source` is 'SALE', grouped by `batch_no`, into an alias `sum_sale_amt`. Then we update this sum into `table1`'s field `total_sale_amt` based on the `batch_no`. This update only applies to `table1` records which is not cancelled.

This query can be run multiple-times to update/fix any inconsistency error when saving from the derived values.

Subversion: Post-Commit Email Notification

2011-10-02T15:29:00.002+08:00

A Subversion repository has a post-commit "hook" which is invoked on every SVN commit. This can be used to send email notifications to all team members involved.

The following is my two-script method inspired by Andrew Farley's SVN Post-Commit Automatic Email.
With this method, we can set different emails for each repositories, and we only have to keep the main script in one place for easy maintenance.

This script is easy to configure and highly customizable. As a batch script, it can be used by Subversion on Windows.
Blat is required for sending email. You only need to change the values in the "Begin/End Settings" section. Please refer the script header remarks for more information and other options.

How to use:

1. Create a batch file named as "weizh-post-commit-email.bat" with the following contents:

ECHO OFF

REM =====================================================================================
REM Copyright 2011 Weizh Chang
REM 
REM This program is free software: you can redistribute it and/or modify
REM it under the terms of the GNU General Public License as published by
REM the Free Software Foundation, either version 3 of the License, or
REM (at your option) any later version.
REM
REM This program is distributed in the hope that it will be useful,
REM but WITHOUT ANY WARRANTY; without even the implied warranty of
REM MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
REM GNU General Public License for more details.
REM
REM You should have received a copy of the GNU General Public License
REM along with this program.  If not, see <http://www.gnu.org/licenses/>.
REM
REM
REM This script is inspired by 'SVN Post-Commit Automatic Email' by Andrew Farley
REM (andrewfarley.com/sysadmin/svn-commit-automatic-email).
REM =====================================================================================

REM =====================================================================================
REM Program    : Weizh SVN Post-Commit Email Batch Script
REM File       : weizh-post-commit-email.bat
REM Version    : 1.0.0.0
REM URL        : simpcode.blogspot.com
REM Description:
REM
REM Please change the settings in the Begin/End Settings section below.
REM
REM This script requires:
REM - post-commit.bat (the caller).
REM - blat emailer tool (www.blat.net).
REM - usermap (optional - see below).
REM
REM Features:
REM - Sends SVN post commit email notification.
REM - Allows multiple common email recipients for all repositories.
REM - Allows multiple custom email recipients for each repository.
REM - Option to include the SVN Diff in the email.
REM - Option to skip sending email using the 'no email' tag in the log.
REM - Option to use usermap (see below).
REM
REM
REM usermap:
REM This script is able to read the authorized svn users from conf\authz, and map to
REM the users' emails in a 'usermap' file. These emails will be used as part of 
REM the receiver emails. This feature can be disabled by changing the 
REM Settings' usermap to empty.
REM
REM The following example shows how a 'usermap' file content may look like:
REM
REM harry=name=Harry Smith
REM harry=email=harry@example.com
REM
REM sally=name=Sally Anderson
REM sally=email=sally@example.com,sally@example2.com
REM =====================================================================================

ECHO ON

SET repos=%1
SET rev=%2
SET reposName=%3
SET sendTo=%4
SET sendToCc=%5


REM ================ Begin Settings ====================

REM ==== Set email settings
SET emailServer=mail.example.com
SET emailPort=25
SET emailUid=admin@example.com
SET emailPwd=adminPassw0rd
SET sendFrom=admin@example.com
SET sendTry=5

REM ==== Set common receiver/cc email (comma separated, without quotes and spaces)
SET sendToCommon=
SET sendToCcCommon=michelle@example.com,alex@example.com

REM ==== Set the blat exe directory
SET emailDir=C:\blat\

REM ==== Set the SVN bin directory
SET svnDir=C:\svnserve\bin\

REM ==== Set the SVN Diff line limit (-1 to not show any)
SET limitDiff=-1

REM ==== Set the usermap file location (leave empty if not using usermap)
SET usermap=D:\svn_repos\scripts\usermap

REM ==== Set no email tag. If this tag exists at the beginning of a line in the log,
REM ==== no email will be sent (leave empty if not using this feature)
SET noEmailTag=[noemail]

REM ================ End Settings ======================


REM ==== Remove surrounding quotes
SET repos=%repos:"=%
SET rev=%rev:"=%
SET reposName=%reposName:"=%
SET sendTo=%sendTo:"=%
SET sendToCc=%sendToCc:"=%

REM ==== Set reposName to repos if empty
IF "%reposName%"=="" SET reposName=%repos%

REM ==== Get svn author
FOR /F "tokens=*" %%R IN ('"%svnDir%svnlook.exe" author -r %rev% %repos%') DO SET author=%%R
SET authorName=

REM ==== Set email temp file
SET tmpFile=%~dp0svn-email-%rev%-%RANDOM%.tmp


REM ================ Begin Read Usermap ================

REM ==== Skip if usermap not exist
IF "%usermap%"=="" GOTO SendEmail
IF NOT EXIST %usermap% GOTO SendEmail

SETLOCAL ENABLEDELAYEDEXPANSION

REM ==== Set authz file
SET reposAuthz=%repos:/=\%\conf\authz

SET sendToMap=
SET authorName=

FOR /F "tokens=1,2,3 delims==" %%A IN (%usermap%) DO (
    IF NOT "%%%A:~0,1%"=="#" (
        IF "%%B"=="email" (
            IF NOT "%%C"=="" (
                SET count=0
                REM ==== If user exists in authz, append the emails to sendToMap
                FOR /F "tokens=1 delims==" %%U IN ('FINDSTR /I /B "%%A.*\=.*r" %reposAuthz%') DO (
                    REM ==== Remove any spaces
                    FOR /F "tokens=1 delims= " %%Y IN ("%%U") DO (
                        IF "%%Y"=="%%A" (
                            IF !count!==0 (
                                IF NOT "!sendToMap!"=="" SET sendToMap=!sendToMap!,
                                SET sendToMap=!sendToMap!%%C
                                SET count=1
                            )
                        )
                    )
                )
            )
        ) ELSE (
            IF "%%B"=="name" (
                IF "%%A"=="%author%" SET authorName=%%C
            )
        )
    )
)

SET mapReturns=^
  SET sendToMap=%sendToMap%^&^
  SET authorName=%authorName%

ENDLOCAL & %mapReturns%

REM ==== Append to the existing sendTo
IF NOT "%sendToMap%"=="" (
    IF NOT "%sendTo%"=="" SET sendTo=%sendTo%,
)
SET sendTo=%sendTo%%sendToMap%

REM ================ End Read Usermap ==================


:SendEmail

REM ==== Append any common emails
IF NOT "%sendToCommon%"=="" (
    IF NOT "%sendTo%"=="" SET sendTo=%sendTo%,
)
SET sendTo=%sendTo%%sendToCommon%

IF NOT "%sendToCcCommon%"=="" (
    IF NOT "%sendToCc%"=="" SET sendToCc=%sendToCc%,
)
SET sendToCc=%sendToCc%%sendToCcCommon%

REM ==== Exit if sendTo is empty
IF "%sendTo%"=="" GOTO Finish

REM ==== Set sendTo with surrounding quotes
SET sendTo="%sendTo%"

REM ==== Set sendToCc with argument switch and surrounding quotes
IF NOT "%sendToCc%"=="" SET sendToCc=-cc "%sendToCc%"

REM ==== Get svn date and time
FOR /F "tokens=1,2" %%R IN ('"%svnDir%svnlook.exe" date -r %rev% %repos%') DO (
    SET svnDate=%%R
    SET svnTime=%%S
)


REM ==== Set email subject
SET subject=[%reposName%] SVNCommit (%author%) Rev: %rev%

REM ==== Set email body

(
ECHO Dear Developers,
ECHO.

IF NOT "%authorName%"=="" (
    ECHO There is an SVN Commit on  [ %reposName% ]  by %authorName% ^(%author%^).
) ELSE (
    ECHO There is an SVN Commit on  [ %reposName% ]  by %author%.
)

ECHO Please SVN Update your working copy.
ECHO.
ECHO.
ECHO -------------------- SVN Commit Notification --------------------
ECHO Repository:    %reposName%
ECHO Revision:      %rev%
ECHO Author:        %author%
ECHO Date:          %svnDate%      Time: %svnTime%
ECHO.
ECHO -----------------------------------------------------------------
ECHO Log Message:
ECHO -----------------------------------------------------------------
) >%tmpFile%

REM ==== Get svn log
FOR /F "tokens=*" %%R IN ('"%svnDir%svnlook.exe" log -r %rev% %repos%') DO ECHO %%R >>%tmpFile%

REM ==== If the 'no email tag' exists in the log, exit without sending email
IF NOT "%noEmailTag%"=="" (
    FOR /F "tokens=*" %%E IN ('FINDSTR /I /B /C:"%noEmailTag%" %tmpFile%') DO GOTO Finish
)

(
ECHO.
ECHO -----------------------------------------------------------------
ECHO Changes:
ECHO -----------------------------------------------------------------
) >>%tmpFile%

REM ==== Get svn changed
FOR /F "tokens=*" %%R IN ('"%svnDir%svnlook.exe" changed -r %rev% %repos%') DO ECHO %%R >>%tmpFile%

IF NOT "%limitDiff%"=="-1" (
(
ECHO.
ECHO -----------------------------------------------------------------
ECHO Diff: ^(only first %limitDiff% lines shown^)
ECHO -----------------------------------------------------------------
) >>%tmpFile%

REM ==== Get svn diff
FOR /F "tokens=*" %%R IN ('"%svnDir%svnlook.exe" diff -r %rev% %repos% ^| head --lines=%limitDiff%') DO ECHO %%R >>%tmpFile%
)

(
ECHO.
ECHO.
ECHO Regards,
ECHO SVN Server Admin
) >>%tmpFile%


REM ==== Send email
"%emailDir%blat.exe" %tmpFile% -server %emailServer%:%emailPort% -f %emailUid% -u %emailUid% -pw %emailPwd% -from %sendFrom% -to %sendTo% %sendToCc% -subject "%subject%" -try %sendTry%


:Finish
REM ==== Cleanup
IF EXIST %tmpFile% DEL /Q %tmpFile%

2. Set values in the Begin/End Settings section. Put this file inside a folder accessible by all repositories. For instance, If your repository root folder is "D:\svn_repos\", you may put it inside "D:\svn_repos\scripts\".

3. Create another batch file named as "post-commit.bat" with the following contents:

SET repos=%1
SET rev=%2


REM ==== Set repository name for display only, if empty will be same as repos
SET reposName=

REM ==== Set receiver/cc email (comma separated, without quotes and spaces)
SET sendTo=
SET sendToCc=


D:\svn_repos\scripts\weizh-post-commit-email.bat "%repos%" "%rev%" "%reposName%" "%sendTo%" "%sendToCc%"

4. Set the repository's name and receiver/cc emails if any. Change the "D:\svn_repos\scripts" to your path. Put this file inside each repository's "hooks" folder.

Whenever there is a commit, an email will be sent to the relevant recipients with contents such as below:


Dear Developers,

There is an SVN Commit on  [ XXXX ]  by Harry Smith (harry).
Please SVN Update your working copy.


-------------------- SVN Commit Notification --------------------
Repository:    XXXX
Revision:      139
Author:        harry
Date:          2011-10-01      Time: 19:53:53

-----------------------------------------------------------------
Log Message:
-----------------------------------------------------------------
Added and modified on some stuff.

-----------------------------------------------------------------
Changes:
-----------------------------------------------------------------
U  Trunk/Foo/
U  Trunk/Foo/Foo.csproj
A  Trunk/Foo/Helper.cs
U  Trunk/Foo/Customer.cs
U  Trunk/Foo/Properties/AssemblyInfo.cs
D  Trunk/Foo/Test.txt


Regards,
SVN Server Admin

Delete rows in DataTable using "foreach"

2011-09-25T14:22:00.001+08:00

If you try to delete multiple rows in a DataTable with certain conditions using the foreach loop, you may get the following error:
"Collection was modified; enumeration operation might not execute."
For such cases, use the following method:


DataRow[] deleteRows = myDataTable.Select("mycondition = true");

foreach (DataRow deleteRow in deleteRows)
{
    deleteRow.Delete();
    deleteFound = true;
}

Subversion: Client: Installation and Guidelines

2009-08-27T19:43:00.005+08:00

This is the third section of the Subversion topic. The first and second sections can be found here and here.

Note: This topic is more like a walk-through than a complete guidelines. And is basically based on the great documentations found on the TortoiseSVN website and various sources. You can refer the site for more explanation if needed.

Client: Installation and Guidelines

Part 1: Installation

Install TortoiseSVN (e.g. TortoiseSVN-1.4.X.XXXX-win32-svn-1.4.X.msi).

After installed, right click > Settings > General >
Tick Use "_svn" instead of ".svn" directories
Tick Set fildates to the "last commit times"

Part 2: Importing Data Into A Repository and Checking Out From A Repository

Before importing, please organize the project folder and take away unused files that are not needed to build the project.
Right-click on the (top-level) project folder, select TortoiseSVN > Import. All the contents of the folder will be imported into the repository, under version control.
The project folder used for importing is unversioned and cannot be used for "Check Out" (See "Check Out A Working Copy" below). If the project folder (source tree) is also used as the working copy, you have to either:

After the folder is imported into the repository, delete the folder, then do "Check Out" to a new folder with the same name, or
"Check Out" to a different folder, or
"Check Out" to an empty folder, then copy the content to the folder. Select TortoiseSVN > Add to add the files needed for version control. Then TortoiseSVN > Commit.

Check Out A Working Copy (client):

To obtain a working copy from a repository. Only can check out into an empty folder.
Right-click on an empty folder > SVN Checkout
Can be perfomed on a sub-directory path instead of the whole repository path. Then the client only need to check out on the part that needed (e.g. trunk only).
The "Check Out" folder contents are now under version control.

If certain folders/files need to be ignored, select TortoiseSVN > Add to ignore list, or TortoiseSVN > Properties, Add "svn:ignore", put the ignore pattern. E.g.:

[Bb][Aa][Cc][Kk][Uu][Pp] [Uu][Nn][Uu][Ss][Ee] [Uu][Nn][Uu][Ss][Ee][Dd] *.[Bb][Aa][Kk] *.[Tt][Bb][Kk] *.[Ee][Xx][Ee] *.[Ee][Xx][Ee][1_]

Part 3: Daily Use Guide

All TortoiseSVN commands are accessed with right-clicking on a file or folder. The versioned folder contents will be indicated with icon overlays. TortoiseSVN also provide right-drag functions.
Checking The Working Copy:

Right-click > TortoiseSVN > Check for modifications (To see changes in the client)
Click "Check Repository" to see changes in the server

Update The Working Copy:

Right-click > SVN Update (update the client)
Default update is from the repository's HEAD revision.
To update from a different revision (not recommended), select TortoiseSVN > Update to revision.
Note: SVN will never overwrite unversioned files.

Commit Changes To The Repository:

Select any file/folder, right-click > SVN Commit > Select the changed files to be commited.
Only commit if the working copy is up-to-date and there are no conflicts.
Optionally, write a log message to describe the changes.
Optionally, files/folders can be ignored, refer here

If there are conflicts during update or commit, either:

Double-click/right-click on the file to launch the diff/merge tool to show the changes (only for non-binary files), or
Manual checking and resolving the problem, or
TortoiseSVN > Resolve, to commit overwrite the repository, or
TortoiseSVN > Revert, to update overwrite the client
Refer: here

To Add/Delete/Rename on version controlled files/folders, etiher:

Modify as usual in Windows Explorer, or
Right-click > TortoiseSVN > Add/Delete/Rename

To undo changes in client:

TortoiseSVN > Revert, to update overwrite the client.

Reference: File Version Used:
Subversion (svn): 1.4.5
TortoiseSVN: 1.4.8.12137 (win32)
Cygwin: 1.5.25-14
Putty, Puttygen, Pageant: 0.60

Subversion: Server: Setting Up Svnserve With SSH

2008-08-16T16:28:00.013+08:00

This is the second section of the Subversion topic. The first section can be found here.

Note: This topic is more like a walk-through than a complete guidelines. And is basically based on the great documentations found on the TortoiseSVN website and various sources. You can refer the site for more explanation if needed.

Server: Setting Up Svnserve With SSH

Part 1: Server Setup

Login as Administrator.
Install SVN on the server (as described in Setting Up Svnserve).

Close the svnserve service if it is running.

Create a new user account named “svnuser” (or any other name) with a password. Check that the user permissions are sufficient to read and write your SVN repository directory on the server.
Install Cygwin SSH daemon as described here.

Before step 5 (“ssh-host-config” section), open c:\cygwin\etc\hosts.allow with WordPad, make sure it looks like this (take note the hash #):
#ALL : PARANOID : deny sshd: ALL
Continue to do the “ssh-host-config” section.
Continue to do the “Test the sshd” section.

Also test on “ssh svnuser@localhost”

Skip the other sections unless specially needed.
Type “logout” untill the cygwin window is closed.

Download PuTTY, PuTTYgen and Pageant from here and place the EXEs in c:\cygwin
In Windows, logout as Administrator, then login as svnuser.
Create a key pair (Replace the ‘svnharrykey’ in sample below with your preferred key name):

Open a cygwin window. Will be logged in as svnuser by default.

$ cd /home/svnuser
$ mkdir .ssh
$ ssh-keygen -t rsa -f svnharrykey.key

Enter a passphrase: a secret key for private key encryption

The key pair will be created in c:\cygwin\home\svnuse

If authorized_keys not exist (first time create):$ cp svnharrykey.key.pub /home/svnuser/.ssh/authorized_keysIf authorized_keys already exist:$ cat svnharrykey.key.pub >> /home/svnuser/.ssh/authorized_keys
Double-click on c:\cygwin\puttygen.exe
Goto Conversions > Import Key > Select the svnharrykey.key file.
Enter the passphrase > Save private key > saved as svnharrykey.ppk (saved into the same folder).
Goto c:\cygwin\home\svnuser\.ssh and open “authorized_keys” file with WordPad.

Append the following line (in blue & red) to the top of each authorized key:
command="svnserve -t -r d:/svn_repos/ --tunnel-user=harry",no-port-forwarding,no-agent-forwarding,no-X11-forwarding,no-pty ssh-rsa <ThePublicKey><PublicKeyComment>
Replace the text in red to your svn repository root folder and svn authorized user (as in your svn repository conf\authz file).
Note: If “svnserve” command cannot be found, change it to full path. E.g: command="c:/svnserve/bin/svnserve.exe –t –r d:/svn_repos/…
Note: The user read/write right of the svn repository will follow as set in conf\authz. (conf\passwd will not be used)
Save the file.

Repeat steps above to generate keys for each svn authorized user.

Note: Recommended each svn user should have a different key pair and passphrase.

After complete, copy all the .ppk keys in c:\cygwin\home\svnuser to another folder to be distributed to the clients.
In Windows, logout as svnuser, then login back as Administrator.

Distribute the Putty private key files (e.g. svnharrykey.ppk) created to the authorized clients.
To restrict access to the ssh service:

In Windows, login as Administrator.
Open c:\cygwin\etc\hosts.allow with WordPad.
Put a hash (#) on this line:
#sshd: ALL
Put the following lines:
ALL : PARANOID : deny ALL : PARANOID : RFC931 20 : deny ALL : localhost 127.0.0.1 : allow sshd : 192.168.1. : allow sshd : 218.208.999.99: allow … ALL : ALL : deny
Note:

Replace the IPs in blue to those you allow.
Rules applies in the first-come-first-serve order.
To allow an IP range: sshd : 192.168.1. : allow
To allow a particular IP: sshd : 192.168.1.47: allow
To allow a domain: sshd : .mydomain.com : allow

Save the file. The changes will take effect immediately.

Part 2: Client Setup

Copy or download PuTTY and Pageant (from here) and place the EXEs in any folder (e.g. d:\_svnuse)
Put the Putty private key (e.g. svnharrykey.ppk) into the same folder.
Double-click on putty.exe to create a new session:

Session:

HostName: Username @ Hostname/IP of the server (e.g. svnuser@myhost.com)

Protocol: SSH

Saved Sessions: Any name (e.g. svnsession)

Connection: SSH:

Prefered SSH Protocol version: 2

Go back to Session node, click save. Close the program.

In the same folder, create a batch file named “runpageant.bat”, put the following line (replace with your paths and private key name):
start "Pageant" "d:\_svnuse\pageant.exe" d:\_svnuse\svnharrykey.ppk
Create a shortcut of runpageant.bat and drag it into All Programs > Startup.
When the runpageant.bat is executed, key in the passphrase when prompted.
Accessing svn repository via TortoiseSvn:

Using the address as below:

svn+ssh://svnsession/Repo_ProjectName/Trunk

Reference: File Version Used:
Subversion (svn): 1.4.5
TortoiseSVN: 1.4.8.12137 (win32)
Cygwin: 1.5.25-14
Putty, Puttygen, Pageant: 0.60

Additional Resources:
Subversion: Post-Commit Email Notification

Subversion: Server: Setting up Svnserve

2008-07-17T18:26:00.021+08:00

Subversion (SVN) is a version control system that is gaining wide popularity. I've been using TortoiseSVN as the client, which is easy to use and setup. However, I find that setting up the Subversion server (svnserve based) is not that straight-forward (although not really that difficult as well).

As such, based on my own experience, I've compiled a step-by-step guide to setup the svnserve based Subversion server, that could be useful for future use.

Note: This topic is more like a walk-through than a complete guidelines. And is basically based on the great documentations found on the TortoiseSVN website and various sources. You can refer the site for more explanation if needed.

This topic is separated into 3 sections:

Server: Setting Up Svnserve

Part 1: Installation

Note: The following guide assumes the server's
- svnserve is installed in c:\svnserve\
- root folder for all repositories is in d:\svn_repos

In Windows, login as Administrator.
Install SvnServe (get the latest version from here).

If manual install:

Manually create a folder c:\svnserve\
Unzip and copy all the files/folders into the folder.

Run the SvnServe:

If install as a service (automatically started when Windows starts): sc create svnserve binpath= "c:\svnserve\bin\svnserve.exe --service --root d:\svn_repos\" displayname= "Subversion" depend= tcpip start=auto obj= "NT AUTHORITY\LocalService"
If manual run, create and run a batch file (e.g. svnserver_service.bat):c:\svnserve\bin\svnserve.exe --daemon --root d:\svn_repos\

Install TortoiseSVN (get the latest version from here).

Optionally, after installed, right click > Settings > General
Tick Use "_svn" instead of ".svn" directories
Tick Set fildates to the "last commit times"

Create a root folder for all repositories in d:\svn_repos\.
Create a repository trunk folder for the relavant project inside the root folder. (e.g. d:\svn_repos\Repo_Project1\Trunk)
Right click on the folder and select TortoiseSVN -> Create Repository here.

Choose Native Filesystem (FSFS)
Do NOT choose Berkeley DB, especially use on a network share.
Do NOT modify the contents of the repository folder yourself.

Authentication (modify and save with a Notepad):

Modify the 'svnserve.conf' file in the repository 'conf' folder (e.g. d:\svn_repos\Repo_Project1\Trunk\conf).
Paste the following after the last line:
anon-access = none auth-access = write password-db = passwd authz-db = authz
Modify the 'passwd' file in the same folder to set user name and password.

Put your user-password pair after the last line. E.g.:
harry = harrypassword user1 = user1password

Modify the 'authz' file in the same folder to set the path-based access control:

Put your user-authorize pair after the last line (r = read, w= write). E.g.:
[/] harry = rw user1 = r * =

Test (make sure the svnserve service is running): Anywhere in Windows Explorer, right click > TortoiseSVN > Repo-Browser to view a repository (e.g. svn://localhost/Repo_Project1/Trunk). Type the username and password when prompted.
Optional: To create a backup copy of the repository:

In DOS prompt, type: svnadmin hotcopy path/to/repository path/to/backup --clean-logs

Optional: Setup SvnServe using SSH. Refer the Setting up Svnserve with SSH.

Reference source: http://tortoisesvn.net/docs/release/TortoiseSVN_en/tsvn-serversetup-svnserve.html

Part 2: Repository Layout

The repository basic folder structure can be created by:

Creating an empty folder with the folder structure inside, then TortoiseSVN > Import, or
Using TortoiseSVN repository browser

It is recommended to create separate repositories for 'each' project. This allows independent development and to keep track on the repository's revision number.
The recommended directory structure:

trunk : To hold the "main line" of development
branches : Contain branch copies
tags : Contain tags copies
For details, refer here.

Reference source: http://tortoisesvn.net/docs/release/TortoiseSVN_en/tsvn-dug-import.html

Reference: File Version Used:
Subversion (svn): 1.4.5
TortoiseSVN: 1.4.8.12137 (win32)

Additional Resources:
Subversion: Post-Commit Email Notification

C#: Set And Unset Auto-start For Windows Application

2008-07-15T18:47:00.014+08:00

I found this useful C# class in this MSDN forum that provides an easy way to enable and disable an application auto-run in Windows.

With a little modification, I made it to be easily called by any project. What you need to do is just pass in the 'Key Name' and 'Assembly Location' parameters.

Here is the complete class:

using Microsoft.Win32;

/// <summary>
/// Utility.
/// </summary>
public class Util
{
    private const string RUN_LOCATION = @"Software\Microsoft\Windows\CurrentVersion\Run";

    /// <summary>
    /// Sets the autostart value for the assembly.
    /// </summary>
    /// <param name="keyName">Registry Key Name</param>
    /// <param name="assemblyLocation">Assembly location (e.g. Assembly.GetExecutingAssembly().Location)</param>
    public static void SetAutoStart(string keyName, string assemblyLocation)
    {
        RegistryKey key = Registry.CurrentUser.CreateSubKey(RUN_LOCATION);
        key.SetValue(keyName, assemblyLocation);
    }

    /// <summary>
    /// Returns whether auto start is enabled.
    /// </summary>
    /// <param name="keyName">Registry Key Name</param>
    /// <param name="assemblyLocation">Assembly location (e.g. Assembly.GetExecutingAssembly().Location)</param>
    public static bool IsAutoStartEnabled(string keyName, string assemblyLocation)
    {
        RegistryKey key = Registry.CurrentUser.OpenSubKey(RUN_LOCATION);
        if (key == null)
            return false;

        string value = (string)key.GetValue(keyName);
        if (value == null)
            return false;

        return (value == assemblyLocation);
    }

    /// <summary>
    /// Unsets the autostart value for the assembly.
    /// </summary>
    /// <param name="keyName">Registry Key Name</param>
    public static void UnSetAutoStart(string keyName)
    {
        RegistryKey key = Registry.CurrentUser.CreateSubKey(RUN_LOCATION);
        key.DeleteValue(keyName);
    }
}

Sample Usage:

string keyName = "MyProgramName";
string assemblyLocation = Assembly.GetExecutingAssembly().Location;  // Or the EXE path.

// Set Auto-start.
Util.SetAutoStart(keyName, assemblyLocation);

// Unset Auto-start.
if (Util.IsAutoStartEnabled(keyName, assemblyLocation))
    Util.UnSetAutoStart(keyName);

C#: Get A Unique Random String Using GUID Hash Code

2008-07-12T14:36:00.012+08:00

GUID, also known as Globally Unique Identifier, is an 128-bit integer (16 bytes) that can be used to uniquely identify a record in the database (other than the auto-increase number). It is best used as a primary key as well.

But in some occasion, what you need is not really a GUID, but a random string that is "unique enough" to be used by your system. And you might want to store it as a string in your database (for whatever reason).

In C#, you can do this by using the GUID hash code. Here is the function that returns a "unique enough" string with a desired length:

/// <summary>
/// Gets a unqiue key based on GUID string hash code.
/// </summary>
/// <param name="length">The length of the key returned.</param>
/// <returns>Unique key returned.</returns>
public static string GetUniqueKey(int length)
{
    string guidResult = string.Empty;
    
    while (guidResult.Length < length)
    {
        // Get the GUID.
        guidResult += Guid.NewGuid().ToString().GetHashCode().ToString("x");
    }

    // Make sure length is valid.
    if (length <= 0 || length > guidResult.Length)
        throw new ArgumentException("Length must be between 1 and " + guidResult.Length);

    // Return the first length bytes.
    return guidResult.Substring(0, length);
}

Replace Certain Data In All FoxPro tables

2008-07-12T11:55:00.014+08:00

The data in our database might need to be changed once in a while, due to business requirement or human error. For example, a field named lot_code (to store the Lot Code of a manufacturing plant) might need to be changed from 'LT001' to 'LOT001'. But this code has been widely used within your system and is stored in several tables.

In Visual FoxPro, one of the method to solve this is:

1. Select the table
2. Set to an appropriate index
3. Seek and replace the field data in a loop
4. Repeat steps 1 to 3 for other affected tables

But, with this method, you have to be sure not to miss out any fields and tables, or else your transaction's data or reports might produce incorrect results.

To avoid that, here is another method, which scans through all the tables for the affected field(s) and replaces the old value with a new one:

***********************************************************************************
** Program: changefield.prg
** Purpose: Check and replace selected field value in all tables.
** Usage  : DO changefield WITH cFields, cOldValue, cNewValue, cDataPath, lExecute
***********************************************************************************

LPARA In_cFields, In_cOldValue, In_cNewValue, In_cDataPath, In_lExecute

IF TYPE('In_cFields') <> 'C' .OR. TYPE('In_cOldValue') <> 'C' .OR. TYPE('In_cNewValue') <> 'C'
    =MESSAGEBOX('Location fields information not provided.')
    RETURN
ENDIF

In_cFields   = "," + ALLTR(LOWER(In_cFields)) + ","
In_cOldValue = ALLTR(In_cOldValue)
In_cNewValue = ALLTR(In_cNewValue)

IF TYPE('In_cDataPath') <> 'C'
    In_cDataPath = ''
ELSE
    In_cDataPath = ADDBS(In_cDataPath)
    SET DEFA TO &In_cDataPath
ENDIF

CLOSE DATA

STORE SET("CONSOLE")  TO mOriConsole
STORE SET("CPDIALOG") TO mOriCpDialog
SET CPDIALOG OFF        && Prevent Code Page prompt if no code page (same as hit esc).

IF FILE("_script.PRG")
    ERASE("_script.PRG")
ENDIF

SET PRINTER TO "_script.PRG" ADDITIVE
SET PRINTER ON
SET CONSOLE OFF

lnFiles = ADIR(gaDatabase, '*.dbf')
FOR nCount = 1 TO lnFiles                   && Loop for number of databases.
    lcFile = ALLTR(LOWER(gaDatabase(nCount,1)))
 
    WAIT WINDOWS "Table " + ALLTR(STR(nCount)) + " of " + ALLTR(STR(lnFiles)) + " : " + lcFile NOWAIT

    SELE 0
 
    _mOriError2 = ON('ERROR')
    mErrRetry2  = .F.
    mErrSkip    = .F.
    ON ERROR STORE .T. TO mErrRetry2        && Retry to open file if error (happen when CDX file not found). Open again will remove the CDX reference.
   
    USE &lcFile
 
    IF mErrRetry2
        ON ERROR STORE .T. TO mErrSkip      && Skip the file if still error (happen when file corrupt, etc).
        USE &lcFile
    ENDIF
 
    ON ERROR &_mOriError2
 
    IF !mErrSkip
        ** Get the loc field and prepare the REPL statement.
        PRIVATE lcReplCommand
        lcReplCommand = ''

        FOR mFieldCount = 1 TO AFIELDS(aDbfField)                  && Get field in each dbf file.
            mFieldName  = LOWER(ALLTR(aDbfField(mFieldCount,1)))   && Get field name.
            mFieldType  = UPPER(ALLTR(aDbfField(mFieldCount,2)))   && Get field type.

            IF "," + mFieldName + "," $ In_cFields .AND. mFieldType == 'C'
                IF !EMPTY(lcReplCommand)
                    lcReplCommand = lcReplCommand + CHR(13)
                ENDIF
    
                lcReplCommand = lcReplCommand + ;
                    'REPL ALL ' + mFieldName + ' WITH In_cNewValue FOR ALLTR(' + mFieldName + ') == ALLTR(In_cOldValue)'
            ENDIF
        ENDFOR
        USE
        **

        ** Run each REPL statement for current table.
        IF !EMPTY(ALLTR(lcReplCommand))
            IF In_lExecute
                USE &lcFile
            ELSE
                ? 'USE ' + lcFile
            ENDIF
    
            lnReplTotal = ALINES(aReplCommand, lcReplCommand, .T.)
            FOR lnReplCount = 1 TO lnReplTotal
                mReplCommand = aReplCommand(lnReplCount)
    
                IF In_lExecute
                    &mReplCommand
                ELSE
                    ? mReplCommand
                ENDIF
            ENDFOR
        ENDIF
        USE
        **
    ENDIF
ENDFOR

CLOSE DATA
WAIT CLEAR

RELEASE gaDatabase

SET CONSOLE &mOriConsole
SET PRINTER OFF
SET PRINTER TO

SET CPDIALOG &mOriCpDialog

Explanation:
cFields: Accepts multiple fields, separated by comma. This will be useful if there are several fields which store the same content.

lExecute: If true, it directly replaces the data. If false, it just generates the update script (_script.PRG) without affecting the data. The script will be saved in the cDataPath given.

Here is a sample usage on changing the "lot_code" field from 'LT001' to 'LOT001':
DO changefield WITH 'lot_code', 'LT001', 'LOT001','d:\mysystem\data',.T.

FoxPro To Run External Application

2008-07-01T18:00:00.006+08:00

In Visual FoxPro, the best way to run an external application is by using the ShellExecute() API function.

For the complete explanation on the ShellExecute() function, please refer to this Foxite's article .

To make things easy, I put all the codes into a program PRG. Whenever I need to call an external application, I just run this procedure.

********************************************************************
* Program: runexapp.prg
* Purpose: Procedure to execute an external application, with parameters.
* Usahe  : DO runexapp WITH <cApplication>, <cParameters>
********************************************************************

PARAMETERS In_App, In_Params

IF TYPE("In_Params") <> "C"
    In_Params = ""
ENDIF

** Declare API function (only need to declare once in main.prg).
DECLARE INTEGER ShellExecute IN shell32.DLL ;
    INTEGER hndWin, ;
    STRING cAction, ;
    STRING cFileName, ;
    STRING cParams, ;
    STRING cDir, ;
    INTEGER nShowWin
**

cAction   = "open"
cFileName = In_App
cParams   = In_Params
cPath     = ""

mRtn      = ShellExecute(0,cAction,cFileName,cParams,cPath,1)

** Extra checking.
DO CASE
    CASE mRtn = 2
        mMsg = "Bad Association"
    CASE mRtn = 29
        mMsg = "Failure to load application"
    CASE mRtn = 30
        mMsg = "Application is busy"
    CASE mRtn = 31
        mMsg = "No application association"
    OTHERWISE
        mMsg = ""
ENDCASE

IF !EMPTY(mMsg)
    MESSAGEBOX(mMsg,48,"Error")
ENDIF

CLEAR DLLS ShellExecute
**

To use the procedure, just put a single line in your program:
DO runexapp WITH 'c:\program\someapp.exe'
You can pass the optional parameters as the second arguments if needed.

Visual FoxPro 6 Right-click Shortcut

2008-06-30T19:04:00.012+08:00

Right-click shortcut menu in a TextBox control is a norm in many programming languages. But, if you happen to be working in Visual FoxPro 6, it is not that easy.

Here is the way to make the shortcut I found somewhere (which I've lost the reference), that provides the Cut, Copy and Paste functions. The functions will be enabled or disabled depends on is there any characters selected:

Create a shortcut.prg as the following:

LPARAMETERS In_SelLength,In_Row,In_Col

DEFINE POPUP shortcut SHORTCUT RELATIVE FROM In_Row,In_Col TO In_Row+3.5,In_Col+20

*DEFINE BAR _med_undo OF shortcut PROMPT "\<Undo" ;
*    KEY CTRL+Z, "Ctrl+Z" ;
*    MESSAGE "Undoes the last command or action"

*DEFINE BAR 2 OF shortcut PROMPT "\-"

If In_SelLength > 0
    DEFINE BAR _med_cut OF shortcut PROMPT "Cu\<t" ;
        KEY CTRL+X, "Ctrl+X" ;
        MESSAGE "Removes the selection and places it onto the Clipboard"

    DEFINE BAR _med_copy OF shortcut PROMPT "\<Copy" ;
        KEY CTRL+C, "Ctrl+C" ;
        MESSAGE "Copies the selection onto the Clipboard"
ELSE
    DEFINE BAR _med_cut OF shortcut PROMPT "\Cut" ;
        KEY CTRL+X, "Ctrl+X" ;
        MESSAGE "Removes the selection and places it onto the Clipboard"

    DEFINE BAR _med_copy OF shortcut PROMPT "\Copy" ;
        KEY CTRL+C, "Ctrl+C" ;
        MESSAGE "Copies the selection onto the Clipboard"
ENDIF

IF LEN(_CLIPTEXT) > 0
    DEFINE BAR _med_paste OF shortcut PROMPT "\<Paste" ;
        KEY CTRL+V, "Ctrl+V" ;
        MESSAGE "Pastes the contents of the Clipboard"
ELSE
    DEFINE BAR _med_paste OF shortcut PROMPT "\Paste" ;
        KEY CTRL+V, "Ctrl+V" ;
        MESSAGE "Pastes the contents of the Clipboard"
ENDIF

ACTIVATE POPUP shortcut

Then in your form's TextBox KeyPress event, put this in:

LPARAMETERS nKeyCode, nShiftAltCtrl

IF nKeyCode = 93
    THISFORM.SCALEMODE = 0
    DO shortcut WITH THIS.SELLENGTH,THIS.HEIGHT / 2 + THIS.TOP,THIS.WIDTH / 2 + THIS.LEFT
    THISFORM.SCALEMODE = 3
    THIS.VALUE = STR(THISFORM.SCALEMODE)
ENDIF

In the same TextBox RightClick event, put this in:
DO shortcut WITH THIS.SELLENGTH,MROW(),MCOL()
Now when you run your form, a right-click menu will pop-up:

Fast Replace Fields In All FoxPro Report Formats

2008-06-26T18:43:00.041+08:00

I've always found the Visual FoxPro report format as one of the best reporting tools around. It is easy and fast to develop, and there are seldom connection or linking issues (read: Crystal Report).
But of course, it only binds to the FoxPro database. :)

One of the amazing things is that the report can actually be processed as a data table. You can manipulate the report contents as if working with a table, as long as you know what you're doing.

For instance, if there are certain fields' expression in the report format that need to be changed, and it happens that you also need to change the same thing for 100 over reports, here is a faster way to do:

Create a small procedure that will loop through all the reports, and modify the field expression contents for each:


***************************************************************
* Program: rptchgfield.prg
* Purpose: Replaces old field expression with new expression in all reports.
* Usage  : DO rptchgfield WITH cReportPath, cOldField, cNewField
***************************************************************

LPARA In_lcReportPath, In_lcOldField, In_lcNewField

In_lcReportPath = ADDBS(ALLTR(In_lcReportPath))
In_lcOldField   = ALLTR(UPPER(In_lcOldField))
In_lcNewField   = ALLTR(In_lcNewField)

lnFiles         = ADIR(laDatabase, In_lcReportPath + '*.frx')  && Get all files into an array.

FOR lnCount = 1 TO lnFiles              && Loop for each tables (report format).
    lcFile   = laDatabase(lnCount,1)    && Row, Column = 1, file names.
 
    lcReport = In_lcReportPath + lcFile
  
    USE &lcReport
    GO TOP
    DO WHILE !EOF()
        IF In_lcOldField $ UPPER(expr)     && Check the field expression that matched.
            lcExpress = UPPER(expr)
            lnStart   = AT(In_lcOldField, lcExpress)
            lnReplace = LEN(In_lcOldField)

            REPL expr WITH STUFF(lcExpress,lnStart,lnReplace,In_lcNewField) && Replace with the new expression.
        ENDIF

        SKIP
    ENDDO
ENDFOR

CLOSE DATABASE
RELEASE laDatabase

=MESSAGEBOX("Process Complete.")

Run the procedure with command like:
DO rptchgfield WITH "d:\reports\", "phone1", "off_phone"
After completed, all the reports in d:\reports\ with field expression "phone1" will be converted to "off_phone".

Using MySQL Workbench To Generate Structure Differences

2008-06-25T19:00:00.014+08:00

As part of the database maintenance and deployment processes, there are always cases where you need to get the differences between the current (legacy) structure used by your customer, and the latest structures.

A fast and easy way to do this is by using the MySQL Workbench. The following are the steps (based on version 5.0.22):

1. Get the scripts of the databases that need to be compared.

- I have an example here with two sql scripts, mydb_old.sql and mydb_new.sql. These two have near identical structures (with same database name), except some changes in the mydb_new, where:

customer table: added an address field of type TEXT
supplier table: altered the phone field to length 100

2. Run Workbench. Close any Physical Schemata if any (e.g. the 'mysql' database) : Right click on the icon, then select 'Delete'.

3. Goto File > Import > Reverse Engineer MySQL Create Script ...
- Input File : Select the new data structure script file (mydb_new.sql), click Next;
- If the script has no error, the progress will show 'Execution completed successfully', click Next;
- 'Autoplace objects in new diagram' left untick if not needed. Click Finish.

4. Now the Physical Schemata will show the database structure that was imported.

5. Goto File > Export > Forward Engineer SQL Alter Script ...
- Input File : Select the old data structure script file (mydb_old.sql), click Next;
- After the process is completed, an 'SQL Diff Tree' screen will show the differences between the two database schema.

6. Click Next. Now you will see the update script for your database schema. Review the generated script, save the script to a file.

7. Exit the Workbench program. Do not save if prompt.

Note:
- Both scripts must have the same database name.
- Remember to always 'Import' the new script first, then 'Export' with the old script.