openSource

VBScript for compressing files in a folder and send on FTP

2013-02-28T22:47:00.001-08:00

This is a simple vbscript compression script. works flawlessly on text files even bigger than 15GB.
We have a SSIS data generation package installed on our DB server which generates huge data files for data mining operations for current year, current month. The below scripts are doing the rest; compress the files and FTP to the data mining server location.

'======================================================

' Function : Zip datamart CSV, CTL files to local server for archive purposes.

'======================================================

Function WindowsZip(sFile, sZipFile, szPath, szDate)

Set oZipShell = CreateObject("WScript.Shell")

Set oZipFSO = CreateObject("Scripting.FileSystemObject")

Set LogFile = oZipFSO.CreateTextFile(szPath & "\Logs\log_" & szDate & ".log", true)

LogFile.WriteLine("=======================================")

LogFile.WriteLine(Now & " - Compressing CSV file - Starting...")

LogFile.WriteLine("=======================================")

If Not oZipFSO.FileExists(sZipFile) Then

NewZip(sZipFile)

End If

Set oZipApp = CreateObject("Shell.Application")

Set source = oZipApp.NameSpace(sFile).Items

sZipFileCount = oZipApp.NameSpace(sZipFile).items.Count

oZipApp.NameSpace(sZipFile).Copyhere source

LogFile.WriteLine(sZipFile & " - Compressing -")

'Keep script waiting until Compressing is done

On Error Resume Next

sLoop = 0

Do Until sZipFileCount < oZipApp.NameSpace(sZipFile).Items.Count

Wscript.Sleep(100)

sLoop = sLoop + 1

Loop

On Error GoTo 0

LogFile.WriteLine(sZipFile & " - Successfully compressed -")

LogFile.WriteBlankLines 1

LogFile.Close

Set LogFile = Nothing

Set oZipShell = Nothing

Set oZipFSO = Nothing

End Function

Sub NewZip(sNewZip)

Set oNewZipFSO = CreateObject("Scripting.FileSystemObject")

Set oNewZipFile = oNewZipFSO.CreateTextFile(sNewZip)

oNewZipFile.Write Chr(80) & Chr(75) & Chr(5) & Chr(6) & String(18, 0)

oNewZipFile.Close

Set oNewZipFSO = Nothing

Wscript.Sleep(500)

End Sub

'======================================================

' Function : Upload datamart CSV, CTL files to MTMS FTP server.

'======================================================

Function FTPUpload(sSite, sUsername, sPassword, sLocalFile, sRemotePath, szPath, szDate, szYear, szMonth)

Const OpenAsDefault = -2

Const FailIfNotExist = 0

Const ForReading = 1

Const ForWriting = 2

Set oFTPScriptFSO = CreateObject("Scripting.FileSystemObject")

Set oFTPScriptShell = CreateObject("WScript.Shell")

Set LogFile = oFTPScriptFSO.OpenTextFile(szPath & "\Logs\log_" & szDate & ".log", 8)

LogFile.WriteLine("========================================")

LogFile.WriteLine(Now & " - Sending CSV file on FTP - Starting...")

LogFile.WriteLine("========================================")

sRemotePath = Trim(sRemotePath)

sLocalFile = Trim(sLocalFile)

'Check to ensure that a remote path was

'passed. If it's blank then pass a "\"

If Len(sRemotePath) = 0 Then

sRemotePath = "\"

End If

If InStr(sLocalFile, "*") Then

If InStr(sLocalFile, " ") Then

FTPUpload = "Error: Wildcard uploads do not work if the path contains a " & "space." & vbCRLF

FTPUpload = FTPUpload & "This is a limitation of the Microsoft FTP client."

LogFile.WriteLine(FTPUpload)

Exit Function

End If

ElseIf Len(sLocalFile) = 0 Or Not oFTPScriptFSO.FileExists(sLocalFile) Then

'nothing to upload

FTPUpload = "Error: File Not Found."

LogFile.WriteLine(sLocalFile & FTPUpload)

Exit Function

End If

'--------END Path Checks---------



'build input file for ftp command

sFTPScript = sFTPScript & "USER " & sUsername & vbCRLF

sFTPScript = sFTPScript & sPassword & vbCRLF

sFTPScript = sFTPScript & "prompt " & vbCRLF

sFTPScript = sFTPScript & "cd " & sRemotePath & vbCRLF

sFTPScript = sFTPScript & "binary" & vbCRLF

sFTPScript = sFTPScript & "lcd " & szPath & vbCRLF

sFTPScript = sFTPScript & "mput " & sLocalFile & vbCRLF

sFTPScript = sFTPScript & "quit" & vbCRLF & "quit" & vbCRLF & "quit" & vbCRLF

sFTPTemp = oFTPScriptShell.ExpandEnvironmentStrings("%TEMP%")

sFTPTempFile = sFTPTemp & "\" & oFTPScriptFSO.GetTempName

sFTPResults = sFTPTemp & "\" & oFTPScriptFSO.GetTempName

'Write the input file for the ftp command

'to a temporary file.

Set fFTPScript = oFTPScriptFSO.CreateTextFile(sFTPTempFile, True)

fFTPScript.WriteLine(sFTPScript)

fFTPScript.Close

Set fFTPScript = Nothing

oFTPScriptShell.Run "%comspec% /c FTP -n -s:" & sFTPTempFile & " " & sSite & " > " & sFTPResults, 0, TRUE

Wscript.Sleep 1000

'Check results of transfer.

Set fFTPResults = oFTPScriptFSO.OpenTextFile(sFTPResults, ForReading, FailIfNotExist, OpenAsDefault)

'sResults = fFTPResults.ReadAll

Do Until fFTPResults.AtEndOfStream

sResults = sResults & vbCRLF & fFTPResults.ReadLine

Loop

fFTPResults.Close

oFTPScriptFSO.DeleteFile(sFTPTempFile)

oFTPScriptFSO.DeleteFile (sFTPResults)

If InStr(sResults, "226 Transfer complete.") > 0 Then

FTPUpload = "226 Transfer complete."

ElseIf InStr(sResults, "File not found") > 0 Then

FTPUpload = "Error: File Not Found"

ElseIf InStr(sResults, "cannot log in.") > 0 Then

FTPUpload = "Error: Login Failed."

Else

FTPUpload = sResults & "Error: Unknown."

End If

LogFile.WriteLine(FTPUpload)

LogFile.WriteBlankLines 1

LogFile.Close

Set oFTPScriptFSO = Nothing

Set oFTPScriptShell = Nothing

Set LogFile = Nothing

End Function

Dim dtTemp, szMonth, szDayName, szDay, szYear, inFilename, outFilename, szPath, szDate

dtTemp = Date

'Get the 2 digit month. VBScript does not include a function for this

if (len(CStr(Month(dtTemp))) < 2) then

szMonth = "0" & CStr(Month(dtTemp))

else

szMonth = CStr(Month(dtTemp))

end if

if (len(CStr(Day(dtTemp))) < 2) then

szDay = "0" & CStr(Day(dtTemp))

else

szDay = CStr(Day(dtTemp))

end if

szYear = Year(dtTemp)

szPath = "D:\FILES"

szDate = szYear & szMonth & szDay

inFilename = szPath & "\" & szYear & "\" & szMonth

outFilename = szPath & "\Archive\COMPRESSED" & ".ZIP"

Wscript.Echo WindowsZip(inFilename, outFilename, szPath, szDate)

Wscript.Echo FTPUpload("xx.xx.xx.xx", "userid", "password", outFilename, "REMOTEPATH", szPath, szDate, szYear, szMonth)

Save the above script as compress_ftp.vbs, and run it on task scheduler with cscript. For the total file size around 20GB, this process will take around 1 hour.

VBScript for URL link creation

2012-11-11T23:13:00.000-08:00

We had a need to create URL link of our project on users's desktop using a script. First stage the users had enough rights on their PC's so we included the required icon file also with the script and passed the following VBScript to users to click on it to create the desktop icon link.

' Definie shell object

Set objWSHShell = WScript.CreateObject("Wscript.Shell")

strDesktop = objWSHShell.SpecialFolders("Desktop")

' Copy the icon file to windows

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFileCopy = objFSO.GetFile( objWSHShell.CurrentDirectory&"\icon.ico")

objFileCopy.Copy ("C:\WINDOWS\system32\")

' Define link properties and create link

strShortcutName = "Log In" ' Shortcut Name - Edit for the wanted name

strShortcutPath = "http://thesystem.com/Login.aspx" ' Link URL - Edit to the wanted URL

strIconPath = "C:\WINDOWS\system32\icon.ico"

Set objShortcut = objWSHShell.CreateShortcut(strDesktop & "\" & strShortcutName & ".lnk")

objShortcut.TargetPath=strShortcutPath

objShortcut.IconLocation=strIconPath

objShortcut.Save

' Quit Script

WScript.Quit

But later the user access rights has been limited and were blocked accessing system folder, the link creator didn't created the link with supplied icon file. As the icon file is copied to the system folder. So the updated script would not use supplied icon file, but make use of existing system icon.

' Definie shell object

Set objWSHShell = WScript.CreateObject("Wscript.Shell")

strDesktop = objWSHShell.SpecialFolders("Desktop")

' Define link properties and create link

strShortcutName = "LogIn" ' Shortcut Name - Edit for the wanted name

strShortcutPath = "http://thesystem.com/Login.aspx" ' Link URL - Edit to the wanted URL

strIconPath = "c:\windows\system32\shell32.dll,44"

Set objShortcut = objWSHShell.CreateShortcut(strDesktop & "\" & strShortcutName & ".lnk")

objShortcut.TargetPath=strShortcutPath

objShortcut.IconLocation=strIconPath

objShortcut.Save

' Quit Script

WScript.Quit

Now the icon shown from system.

ASP.NET default Login page and controller modification for using with Web Service or local method

2012-10-05T02:45:00.002-07:00

By default the ASP.NET login page and controls are bound with default methods and its bit uneasy to work with ( at least for some ppl like me). We could make use of same controller and pages with our own mechanism of logging in.

1. Changes on web.config

<location path="Login.aspx">

<system.web>

<authorization>

<allow users="*" />

</authorization>

</system.web>

</location>

<location path="Styles">

<system.web>

<authorization>

<allow users="*" />

</authorization>

</system.web>

</location>

<location path="Images">

<system.web>

<authorization>

<allow users="*" />

</authorization>

</system.web>

</location>

<system.web>

<authorization>

<deny users="?" />

</authorization>

</system.web>

2. Moving files
Move the Login.aspx and related files outside the default folder and delete the folder

3. Update on Login frontend, add the onclick method

<p class="submitButton">

<asp:Button ID="LoginButton" runat="server" onclick="LoginButton_Click" Text="Log In" ValidationGroup="LoginUserValidationGroup"/>

</p>

4. Update on Login backend

protected void LoginButton_Click(object sender, EventArgs e)

{

bool authSuccess = false;

sc.AppCode = WebConfigurationManager.AppSettings["appCode"];

sc.AppPwd = WebConfigurationManager.AppSettings["appPwd"];

sc.ClientIP = "aa";

sc.EndUserId = "aa";

try

{

if (LoginUser.UserName == WebConfigurationManager.AppSettings["userId"]) // web.config user id

{

if (LoginUser.Password == WebConfigurationManager.AppSettings["userPwd"])

{

authSuccess = true;

}

else

{

authSuccess = false;

}

}

else // other user id

{

req.ServiceContext = sc;

req.UserId = LoginUser.UserName;

req.UserPwd = LoginUser.Password;

try

{

response = port.Authenticate(req);

if(response.Status.ToUpper() == "OK")

{

authSuccess = true;

}

else

{

authSuccess = false;

wsStatus.Text = response.Message;

}

}

catch (Exception lex)

{

wsStatus.Text = lex.Message;

authSuccess = false;

}

}

//

if (authSuccess)

{

FormsAuthentication.SetAuthCookie(LoginUser.UserName, false /* createPersistentCookie */);

string continueUrl = Request.QueryString["ReturnUrl"];

if (String.IsNullOrEmpty(continueUrl))

{

continueUrl = "~/";

}

Response.Redirect(continueUrl);

}

else

{

LoginUser.FailureText = "Login Failure!";

}

}

catch (Exception lex)

{

LoginUser.FailureText = lex.Message;

}

}

ASP.NET - Almost complete use of Datagrid

2012-10-05T00:09:00.004-07:00

I was on a need of creating datagrid object on my page with sorting, filtering, updating needs. You could make use of below example for some complex cases (Dropdown, Date selector update columns for relational data table updates, filters, multiple update queries)

<table border="0" cellspacing="0">

<tbody>

<tr>

<th>Owner_Name</th>

<th>OS</th>

<th>Type</th>

<th>Status</th>

<th>Action Type</th>

</tr>

<tr>

<td><asp:dropdownlist appenddatabounditems="true" autopostback="true" datasourceid="dsPopulateOwner_Name" datatextfield="UNAME" datavaluefield="UID" font-size="11px" id="ddlOwner_Name" runat="server" width="130px">

<asp:listitem text="*" value="%"></asp:listitem>

</asp:dropdownlist>

<asp:sqldatasource connectionstring="<%$ ConnectionStrings:Connection %>" id="dsPopulateOwner_Name" runat="server" selectcommand="SELECT

Id as UID, Name as UNAME FROM tblUser"></asp:sqldatasource>

</td>

<td><asp:dropdownlist appenddatabounditems="true" autopostback="true" datasourceid="dsPopulateOS" datavaluefield="OS" font-size="11px" id="ddlOS" runat="server" width="130px">

<asp:listitem text="All" value="%"></asp:listitem>

</asp:dropdownlist>

<asp:sqldatasource connectionstring="<%$ ConnectionStrings:Connection %>" id="dsPopulateOS" runat="server" selectcommand="SELECT

DISTINCT OS FROM tblServer"></asp:sqldatasource></td>

<td><asp:dropdownlist appenddatabounditems="true" autopostback="true" datasourceid="dsPopulateType" datavaluefield="Type" font-size="11px" id="ddlType" runat="server" width="130px">

<asp:listitem text="All" value="%"></asp:listitem>

</asp:dropdownlist>

<asp:sqldatasource connectionstring="<%$ ConnectionStrings:Connection %>" id="dsPopulateType" runat="server" selectcommand="SELECT

DISTINCT Type FROM tblServer"></asp:sqldatasource></td>

<td><asp:dropdownlist appenddatabounditems="true" autopostback="true" datasourceid="dsPopulateStatus" datavaluefield="Status" font-size="11px" id="ddlStatus" runat="server" width="130px">

<asp:listitem text="All" value="%"></asp:listitem>

</asp:dropdownlist>

<asp:sqldatasource connectionstring="<%$ ConnectionStrings:Connection %>" id="dsPopulateStatus" runat="server" selectcommand="SELECT

DISTINCT Status FROM tblServer"></asp:sqldatasource></td>

<td><asp:dropdownlist appenddatabounditems="true" autopostback="true" datasourceid="dsPopulateActionType" datavaluefield="ActionType" font-size="11px" id="ddlActionType" runat="server" width="130px">

<asp:listitem text="All" value="%"></asp:listitem>

</asp:dropdownlist>

<asp:sqldatasource connectionstring="<%$ ConnectionStrings:Connection %>" id="dsPopulateActionType" runat="server" selectcommand="SELECT

DISTINCT ActionType FROM tblServer"></asp:sqldatasource></td>

</tr>

</tbody></table>

<asp:gridview allowsorting="True" autogeneratecolumns="False" cellpadding="3" datakeynames="Id" datasourceid="SqlDataSource1" id="GridView1" pageindex="1" pagesize="100" runat="server" width="95%">

<alternatingrowstyle backcolor="#B6CFA5">

<columns>

<asp:templatefield headertext="Select">

<itemtemplate>

<asp:checkbox id="chkSelect" runat="server">

</asp:checkbox></itemtemplate>

</asp:templatefield>

<asp:boundfield datafield="Id" headertext="ID" readonly="true" sortexpression="Id" visible="true">

<asp:boundfield controlstyle-cssclass="cssWdth" datafield="Hostname" headertext="Hostname" sortexpression="Hostname">

</asp:boundfield>

<asp:boundfield controlstyle-cssclass="cssWdth" datafield="IP_Address" headertext="IP_Address" sortexpression="IP_Address">

</asp:boundfield>

<asp:templatefield headerstyle-horizontalalign="Left" headertext="Owner" sortexpression="Owner">

<edititemtemplate>

<asp:dropdownlist appenddatabounditems="true" autopostback="false" cssclass="cssWdth" datasourceid="dsPopulateOwner_Name" datatextfield="UNAME" datavaluefield="UID" id="ddlEditOwner_Name" runat="server" selectedvalue="<%# Bind("Owner") %>">

</asp:dropdownlist>

</edititemtemplate>

<itemtemplate>

<asp:label id="lblOwner_Name" runat="server" text="<%# Bind("Owner_Name") %>"></asp:label>

</itemtemplate>

</asp:templatefield>

<asp:templatefield headerstyle-horizontalalign="Left" headertext="OS" sortexpression="OS">

<edititemtemplate>

<asp:dropdownlist cssclass="cssWdth" datasourceid="dsPopulateOS" datatextfield="OS" datavaluefield="OS" id="ddlEditOS" runat="server" selectedvalue="<%# Bind("OS") %>">

</asp:dropdownlist>

</edititemtemplate>

<itemtemplate>

<asp:label id="lblOS" runat="server" text="<%# Bind("OS") %>"></asp:label>

</itemtemplate>

</asp:templatefield>

<asp:templatefield headerstyle-horizontalalign="Left" headertext="Type" sortexpression="Type">

<edititemtemplate>

<asp:dropdownlist cssclass="cssWdth" datasourceid="dsPopulateType" datatextfield="Type" datavaluefield="Type" id="ddlEditType" runat="server" selectedvalue="<%# Bind("Type") %>">

</asp:dropdownlist>

</edititemtemplate>

<itemtemplate>

<asp:label id="lblType" runat="server" text="<%# Bind("Type") %>"></asp:label>

</itemtemplate>

</asp:templatefield>

<asp:templatefield headerstyle-horizontalalign="Left" headertext="Status" sortexpression="Status">

<edititemtemplate>

<asp:dropdownlist cssclass="cssWdth" datasourceid="dsPopulateStatus" datatextfield="Status" datavaluefield="Status" id="ddlEditStatus" runat="server" selectedvalue="<%# Bind("Status") %>">

</asp:dropdownlist>

</edititemtemplate>

<itemtemplate>

<asp:label id="lblStatus" runat="server" text="<%# Bind("Status") %>"></asp:label>

</itemtemplate>

</asp:templatefield>

<asp:templatefield headerstyle-horizontalalign="Left" headertext="ActionType" sortexpression="ActionType">

<edititemtemplate>

<asp:dropdownlist appenddatabounditems="true" autopostback="false" cssclass="cssWdth" datasourceid="dsPopulateActionType" datatextfield="ActionType" datavaluefield="ActionType" id="ddlEditActionType" runat="server" selectedvalue="<%# Bind("ActionType") %>">

</asp:dropdownlist>

</edititemtemplate>

<itemtemplate>

<asp:label id="lblActionType" runat="server" text="<%# Bind("ActionType") %>"></asp:label>

</itemtemplate>

</asp:templatefield>

<asp:templatefield headerstyle-horizontalalign="Left" headertext="ActionDate" sortexpression="ActionDate">

<edititemtemplate>

<asp:textbox cssclass="cssWdth" id="TextBox2" runat="server" text="<%# Bind("ActionDate") %>"></asp:textbox>

<ajaxtoolkit:calendarextender format="dd-MM-yyyy" id="CalendarExtender2" runat="server" targetcontrolid="TextBox2">

</ajaxtoolkit:calendarextender></edititemtemplate>

<itemtemplate>

<asp:label id="lblActionDate" runat="server" text="<%# Bind("ActionDate") %>"></asp:label>

</itemtemplate>

</asp:templatefield>

<asp:commandfield showeditbutton="True">

</asp:commandfield></asp:boundfield></columns>

<editrowstyle backcolor="#FFCC99" width="95%">

</editrowstyle></alternatingrowstyle></asp:gridview>

</code><br />

<code><asp:button id="btnSelct" onclick="GetSelected" runat="server" text="Edit selected records">

<asp:button id="btnCancel" postbackurl="~/Default.aspx" runat="server" text="Return">

</asp:button></asp:button></code><br />

<code>

<asp:sqldatasource connectionstring="<%$ ConnectionStrings:Connection %>" filterexpression="Owner_Name like '{0}%' and OS like '{1}%' and Type like '{2}%' and Status like '{3}%' and ActionType like '{4}%'" id="SqlDataSource1" runat="server" selectcommand="SELECT S.ID, S.HostName AS Hostname, S.IP AS IP_Address, U.Name AS Owner_Name, S.Owner, S.OS, S.Type, S.Status, S.ActionType, S.ActionDate

FROM tblServer AS S INNER JOIN tblUser AS U ON S.Owner = U.Id" updatecommand="UPDATE [tblServer]

SET [HostName] = @HostName

,[IP] = @IP_Address

,[Owner] = @Owner

,[OS] = @OS

,[Type] = @Type

,[Status] = @Status

,[ActionType] = @ActionType

,[ActionDate] = @ActionDate

,[ModifiedDate] = CURRENT_TIMESTAMP

,[ModifiedBy] = @ModifiedBy

WHERE [Id] = @Id;

INSERT INTO [tblActivityLog]

([ActionTimelog]

,[ActionCode]

,[ActionUserName]

,[ServerHostname]

,[ServerLocation]

,[ServerOS]

,[ServerType]

,[ServerOwner]

,[ServerStatus]

,[ServerFollowUpDate]

,[ServerActionType]

,[ServerActionDate]

,[UserLogin]

,[UserName]

,[UserType]

,[UserStatus]

,[Notes])

values(

CURRENT_TIMESTAMP

,@ActionCode

,@ModifiedBy

,@HostName

,''

,@OS

,@Type

,@Owner

,@Status

,''

,@ActionType

,@ActionDate

,@ModifiedBy

,@ModifiedBy

,''

,''

,''

);">

<filterparameters>

<asp:controlparameter controlid="ddlOwner_Name" name="Name" propertyname="SelectedItem.Text">

<asp:controlparameter controlid="ddlOS" name="OS" propertyname="SelectedValue">

<asp:controlparameter controlid="ddlType" name="Type" propertyname="SelectedValue">

<asp:controlparameter controlid="ddlStatus" name="Status" propertyname="SelectedValue">

<asp:controlparameter controlid="ddlActionType" name="ActionType" propertyname="SelectedValue">

</asp:controlparameter></asp:controlparameter></asp:controlparameter></asp:controlparameter></asp:controlparameter></filterparameters>

<updateparameters>

<asp:parameter name="Id" type="Int32">

<asp:parameter name="HostName" type="String">

<asp:parameter name="IP_Address" type="String">

<asp:parameter name="Owner" type="Int32">

<asp:parameter name="OS" type="String">

<asp:parameter name="Type" type="String">

<asp:parameter name="Status" type="String">

<asp:parameter name="ActionType" type="String">

<asp:parameter name="ActionDate" type="DateTime">

<asp:parameter defaultvalue="UnKnown" name="ModifiedBy" type="string">

<asp:parameter defaultvalue="UPD" name="ActionCode" type="string">

</asp:parameter></asp:parameter></asp:parameter></asp:parameter></asp:parameter></asp:parameter></asp:parameter></asp:parameter></asp:parameter></asp:parameter></asp:parameter></updateparameters>

</asp:sqldatasource>

MSSQL Split, Pad Zeroes, Get Alphabets, and Substring

2012-08-10T03:32:00.000-07:00

Got a request to create a data table get from some existing data to some required data formatting.
The Source data is like 'C10 L1_B21773197D.xls', 'EV1 L1_B21773602F.xls', 'EV116 L1_B21773659G.xls', etc.
The following were the requirements.

CategoryCode	No	NVARCHAR(10)	C	Contained in legacy data file name Sample legacy data file name : “C10 L1_B21773197D” 1. Alphabets prior to Numeric characters)
FolderNo	No	VARCHAR(10)	C001	Contained in legacy data file name Sample legacy data file name : “C10 L1_B21773197D” 1. convert numeric characters prior to “white space” to a 3 digit number (e.g. “1” --> “001”) 2. Append 3 digit number to Category Code
VolumeNo	No	VARCHAR(10)	C001L01	Contained in legacy data file name Sample legacy data file name : “C10 L1_B21773197D” 1. Extract the 2^nd occurrence of an Alphabets in File Name after “white Space” (It will be value L or V) 2. convert the numeric characters prior to “_”to a 2 digit number (e.g. “1” --> “01”) 3. Append the 2 digit number (step2) to Alphabet (step1) to get volume code like L01 4. Append volume code to Folder No to get Volume No like C001L01
VolumeBarcode	No	VARCHAR(12)	B21773203C	Contained in legacy data file name Sample legacy data file name : “C10 L1_B21773197D” · The 10 Alphanumeric characters after “_”

I created a temporary table with required values and get the desired data on another SELECT.
Follows the SQL statements.

SELECT SUBSTRING([Filename],1,(len([Filename]) - 4)) as TFilename --This line will remove the file extension

,dbo.f_get_alphabets(PARSENAME(REPLACE(SUBSTRING([Filename],1,(len([Filename]) - 4)), ' ', '.'), 2)) as Code -- This is to get only the alphabets from first portion

,dbo.f_get_numbers(PARSENAME(REPLACE(SUBSTRING([Filename],1,(len([Filename]) - 4)), ' ', '.'), 2)) AS TCat --This is to get only the numbers from first portion

,PARSENAME(REPLACE(SUBSTRING([Filename],1,(len([Filename]) - 4)), ' ', '.'), 1) as Vol -- This part is to get the second portion

into #TempLD -- Store data into temp table

FROM [dbo].[SourceTable]

SELECT Code as CategoryCode,

Code + RIGHT('000'+ TCat, 3) as FolderNo, -- Padding Zeroes infront to make it 3 digit number with zeroes in front

Code + RIGHT('000'+ TCat, 3) + LEFT(PARSENAME(REPLACE(Vol, '_', '.'), 2),1)+RIGHT('00'+ RIGHT(PARSENAME(REPLACE(Vol, '_', '.'), 2),1),2) as VolumeNo, -- Padded numbers and Alphabets concatenated to make the desired string

PARSENAME(REPLACE(Vol, '_', '.'), 1) as VolumeBarcode, -- Split the portion behind _ character

from #TempLD

Follow is the function used on the above query. For numbers, the IF condition would be changed as @code between 48 and 57.

CREATE FUNCTION [dbo].[f_get_alphabets](

@string varchar(255)

) RETURNS varchar(255)

BEGIN



DECLARE @newstring varchar(255)

DECLARE @num int

DECLARE @code int

  SELECT @newstring = ''

SELECT @num = 1

WHILE @num < LEN(@string)+1

BEGIN

SET @code = ASCII(SUBSTRING(@string, @num, 1))

--- ascii: 48-57 (numbers 0-9)

--- ascii: 65-90 (uppercase letters A-Z)

--- ascii: 97-122 (lowercase letters a-z)

IF (@code between 65 and 90) or (@code between 97 and 122)

BEGIN

SET @newstring = @newstring + SUBSTRING(@string, @num, 1)

END

  SET @num = @num + 1

END

  RETURN @newstring

END

MySQL Dump cronjob on linux server

2010-09-29T23:40:00.000-07:00

Creating mysql dump for production databases is very important. In case of any failure or malefic attack on DB, The only way to restore to previous state is by having proper mysql dump file. Follows an example mysql dump shell script with auto naming with dumping time stamp. Its important to create the shell scripts in unix mode, if you create on a PC, It'll add some unwanted character encoding, and it'll lead to some erroneous response on the script.

#!/bin/bash
today=`date +%Y%m%d%H%M`
backup="cmtdbbackup-$today.sql"
mysqldump -u user -ppassword mydb > /apps/mysql_dump/$backup

Its important to create the shell script carefully. No spaces, unless its required. in between -u and user name, there is a space, but no space between -p and password. As per the usage of system, we can decide on which interval the cronjob could be set. normally the format of a crontab entry is like the following.

* * * * * /apps/scripts/scheduled_job.sh >> /dev/null 2>&1

Here the first 5 portions are the timing interval markers (Minute (0-59), Hour (2-24), Day of month (1-31), Month (1-12, Jan, Feb, etc), Day of week (0-6) 0 = Sunday, 1 = Monday etc or Sun, Mon etc). next to those is the script to run. The '>> /dev/null 2>&1' part means to send any standard output to /dev/null (the linux trash can) and to redirect standard error (2) to the same place as the standard output (1). Basically it runs the command without any output to a terminal.

If you want to run the script for some specific times only, lets say you want to run it on 4 pm and 8 pm, the timers looks like the following

0 16,20 * * *

if you want to run the script only in week days, the timers will be look like the following.

0 16,20 * * 1,2,3,4,5

If you run more than one script, better give them as seperate jobs.

building SOAP object in perl to call a web service

2010-09-20T21:12:00.000-07:00

Follows an example perl script snippet structure to build SOAP object to call a web service

Use the required library

use SOAP::Lite +trace => 'debug';
Define the SOAP object

my $soap = SOAP::Lite
                   -> uri('http://localhost:1325/WSImpl/')
                   -> on_action( sub { return '"http://www.example.com/ws/MaterialService:updateMaterialIn"' } )
                   -> proxy('http://localhost:1325/WSImpl/MaterialService.asmx')
                   -> on_fault(sub { my($soap, $res) = @_;
            die ref $res ? $res->faultdetail : $soap->transport->status, "\n";
           });
Define the SOAP method

my $method = SOAP::Data->name('UpdateMaterialRequest')
->attr({xmlns => 'http://www.example.com/ws/MaterialService'});
Build the SOAP query data

my $query = SOAP::Data->value(
    SOAP::Data->name(ServiceTags =>
    \SOAP::Data->value(
    SOAP::Data->name(UserName => ''),
    SOAP::Data->name(Pwd => ''))),
    SOAP::Data->name(Details =>
        \SOAP::Data->value(
        SOAP::Data->name(DetailsName => Name )
        )
      )
    );
Make the Call

$result = $soap->call($method => $query);
Read the resultant data

my $statusmes = $result->valueof('//GetResponse/Status');

Perl: Read Text Files and Filter Data

2010-06-18T02:07:00.000-07:00

Perl is the language for text manipulation. Its designed in a way, to handle the data in whatever the format, and process out the data into the desired format. Lets see some text processing examples from an external text file.

Step One:

Open a text file for reading,

#!/usr/bin/perl
open (FILE, 'data.txt');
while () {
chomp;
print $_;
}
close (FILE);
exit;

Round the float

2010-06-07T08:07:00.000-07:00

There are 3 types of rounding a float to integer is applicable in any language. we programmers will come across such situation in every project. the three types are,

round to next bigger integer, with not considering the decimal value.
round to the next smaller integer, with not considering the decimal value.
round the float according to the value of decimal value, the real round of float number.

The rounding methods depend on which scenario we need the round function.

Follows the examples of php rounding methods.

for the case 1, rounding to the next big integer, we use ceil() function

Example :

$rounded_value = ceil($floor_value);

if rounded the following values, the results are like shown

2 = ceil(1.3)
2 = ceil(1.5)
2 = ceil(1.7)


for the case 2, round to the next small integer, we could use floor()
Example:
$rounded_value = floor($floor_value);
if rounded the following values, the results are like shown
1 = floor(1.3)
1 = floor(1.5)
1 = floor(1.7)


for the case 3, round as per the decimal value, we could use round()
Example:
$rounded_value = round($floor_value);
if rounded the following values, the results are like shown
1 = round(1.3)
2 = round(1.5)
2 = round(1.7)

posting code snippets to blogger

2010-05-18T01:14:00.000-07:00

When you try to post any code snippet, which related to web applications, If you directly include them into the post with blockquotes, you could never say whether its going to display as it is. because the browser transforms the html code as per the tags. So, its important to post the code with the correct formats. There is a cool site, which converts code snippets to cope with posting on a blog page.

The interface is like the following.

you have to paste your code into 'Enter Markup' text box, and click process, then the site will return the HTML safe code in the second text box, also you can see the preview of the code.

This is the site name : http://www.simplebits.com/cgi-bin/simplecode.pl

Then you can cut and paste the code which returned on the second box to the required place with block quotes. So the code will be displayed correctly. There are also some advanced methods and styles to post the code snippet to the blogger also available, but for this method, you might not need to do any further change on code.

lightbox Image viewer for blogger

2010-05-17T23:24:00.000-07:00

Its normally very hard with blogger pages when we click on an image. It opens the image on the browser.
When i roam around to find any alternative to show the pictures on a separate shadow pop-up, I found some cool script which uses scriptaculous and prototype. Follows the steps.

Login to your blogger Layout
Click on Edit HTML
Find the </head> tag and replace it with the following

<style> #lightbox{ position: absolute; left: 0; width: 100%; z-index: 100; text-align: center; line-height: 0;} #lightbox img{ width: auto; height: auto;} #lightbox a img{ border: none; } #outerImageContainer{ position: relative; background-color: #fff; width: 250px; height: 250px; margin: 0 auto; } #imageContainer{ padding: 10px; } #loading{ position: absolute; top: 40%; left: 0%; height: 25%; width: 100%; text-align: center; line-height: 0; } #hoverNav{ position: absolute; top: 0; left: 0; height: 100%; width: 100%; z-index: 10; } #imageContainer>#hoverNav{ left: 0;} #hoverNav a{ outline: none;} #prevLink, #nextLink{ width: 49%; height: 100%; background-image: url(data:image/gif;base64,AAAA); /* Trick IE into showing hover */ display: block; } #prevLink { left: 0; float: left;} #nextLink { right: 0; float: right;} #prevLink:hover, #prevLink:visited:hover { background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhC_yJAcOEkSEMz9nY1plUueDm9hAXs6a0pWpd0KLS-LOBCP2tkrwWNSguDFamzeOZI92Xa-8Qfrq0ec54ykqVTr9V69AXDtTjUZzU-zNJee1jiSDalmSIfMPMwV80_ZTV51dEaaPQrdJQ/) left 15% no-repeat; } #nextLink:hover, #nextLink:visited:hover { background: url(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgiv_400Cc-0KEv58lTiWdFIeMMqvQkpFmtgMpfI2uxvEZdi9qAO3rc_Exfvz0cmp63F9T136P5qbcN5xGsnSj4unLgo4CIbrXSz1sRqGnxfeGI5divmuq_bfVej8t9beLZXBua5DsdcXE/) right 15% no-repeat; } #imageDataContainer{ font: 10px Verdana, Helvetica, sans-serif; background-color: #fff; margin: 0 auto; line-height: 1.4em; overflow: auto; width: 100% ; } #imageData{ padding:0 10px; color: #666; } #imageData #imageDetails{ width: 70%; float: left; text-align: left; } #imageData #caption{ font-weight: bold; } #imageData #numberDisplay{ display: block; clear: left; padding-bottom: 1.0em; } #imageData #bottomNavClose{ width: 66px; float: right; padding-bottom: 0.7em; outline: none;} #overlay{ position: absolute; top: 0; left: 0; z-index: 90; width: 100%; height: 500px; background-color: #000; } round-color: #000; } lute; top: 0; left: 0; z-index: 90; width: 100%; height: 500px; background-color: #000; } round-color: #000; } </style> <script src='http://ajax.googleapis.com/ajax/libs/prototype/1.6.0.2/prototype.js' type='text/javascript'/> <script src='http://script.aculo.us/scriptaculous.js' type='text/javascript'/> <script src='http://blogergadgets.googlecode.com/files/lightbox.js' type='text/javascript'/>  </head>

Now you have enabled javascript based lightbox.

To enable this to the images you upload, do the following

Insert Image
Switch the editor to 'Edit HTML'

There is an anchor tag for each of the picture you uploaded.
Insert the following code if you want to open the image as separate one ( there is no next, prev)

rel = "lightbox" title = "Caption of your Image"

Insert the following code, if your image is part of an album (set of images)

rel = "lightbox[album_name]" title = "Caption of your Image"

Sometimes, the image is too bigger for showing on the window. To handle that, Check the 'href' value of the anchor. There you can find something like '/s1600' (if you found '/s1600-h', remove '-h'). s1600 means the x-large or original size of the image. so you can replace it with '/s640', which is suitable within the monitor.

Examples

Single Image

Album Images

Good Perl Tutorial

2009-10-01T23:32:00.000-07:00

I have attached here a nice perl tutorial from Chan Bernard Ki Hong.

Display the list of installed packages on perl

2009-09-30T23:42:00.000-07:00

Lets say that you are using the external vendor's hosted environment or somehow you don't know the inner things on your perl installations. how do you check the installed packages on the server using perl script?

I came through the same scenario when i intended to use CGI::Cookie Package. I was unable to check the package availability. then found the following script on web after some search and filter. when applied on my site, I got the full list of installed packages

use ExtUtils::Installed;
my $instmod = ExtUtils::Installed->new();
foreach my $module ($instmod->modules()) {
my $version = $instmod->version($module) || "Version Not Found";
print "$module — $version
";
}

This piece of code returns the list of packages and their versions on the system.

perl : viewing pdf file out of web folder

2009-08-03T19:25:00.000-07:00

How to view a pdf file on your web page?
If the pdf file parked in a web folder like /cgi-bin/files/example.pdf, you can easily show it in a page using the following code

<embed src="/cgi-bin/files/example.pdf#toolbar=0&navpanes=1&scrollbar=0" width="100%" height="500">

so the page will show the pdf file embedded on the web page like the following

but lets say that, the pdf file is not accessible on web page, that means its parked out of web directory for the sake of security. then how to get the file viewed on the web page?

I did the following thing to achieve showing the pdf file out of web directory for testing.

created a tempfile handler within web directory
use File::Temp qw/ tempfile /;
use File::Copy;
use File::Basename;

my($fh, $fname) = tempfile('TEMP_XXXX', SUFFIX => '.pdf', DIR =>'d:/Inetpub/cgi-bin/temp/');
copied the file to the temp file
copy("d:/files/example.pdf","$fname") or die "Copy failed: $!";
$output_file = "temp/".basename($fname);

embedded the temp file on the web page
< embed src="$output_file#toolbar=0&navpanes=1&scrollbar=0" width="100%" height="500" >

Now the page shows the pdf file out of web folder. one more thing to consider on this is to remove the temp file after usage. by default, the temp file will be removed if application closed. but its better to unlink them after session ends.
this method is not preferred to show secured documents.

cheers!