Showing posts with label Scripts. Show all posts
Showing posts with label Scripts. Show all posts

Sunday, December 11, 2011

File Checking - Perl to the rescue

One of my coworkers posted about an issue with one of the data files we receive from outside world. We have a batch program that processes these files and posts transactions into our system. The issue was that a file had one record that an extra byte and any amount of checks didn't reveal the "bad" row. After different checks, he decided to download to PC, take it into TextPad and turned on visual spaces to find which row had that extra space.  Very tedious, but it works.

As you may know, Perl is a very good scripting tool for such purposes. I've created a sample perl script that does basic checks from file size to record size. If it finds any discrepancy, it prints the line #s and records. (See sample output below). For those interested, I'm attaching a copy (text version of the script) here for reference.

(If you are uploading to unix, you need to chmod +x to make it executable).

Please let me know, if you want more information. Feel free to change the script as needed (but please send me a copy, so I can keep mine updated).

Sample Usage:

/tmp/chkfile.pl FIN_09022011_131353.txt 259

1st parameter is the file name and the second parm is expected record size.

Sample Output:
$/home/svaradar/dev/perl
$ /tmp/chkfile.pl FIN_999905_BILLPAY_09022011_131353.txt 259
Name of the file              : FIN_09022011_131353.txt
File Size                     : 1554 bytes
Record Size expected          : 259
Total # of lines in file      : 6
File appears to be a DOS file. (contains carriage returns)
All rows match!

After creating a bad record (I just "fixed" one of the record to change it to 260 chars):
$ /tmp/chkfile.pl FIN_09022011_131353.txt 259
Name of the file              : FIN_09022011_131353.txt
File Size                     : 1555 bytes
Record Size expected          : 259
Total # of lines in file      : 6
File appears to be a DOS file. (contains carriage returns)

Following rows were unmatched:
+4 -  Size: 260 - << P0001CHK0000000000000374.4600099999990001400001111              011000015                                                                                     Sample Record                                                                                     <CR>>>


<CR>- The script translates CTRL-M to printable <CR>; otherwise it would have inserted just a blank line in the output!



Below file contains the perl script in PDF file format:

chkfile.pl

Monday, June 20, 2011

Dot... and Dotty

As a developer, one of the things I constantly do is documentation. This is not exactly a favorite task of mine, but I know it's a necessary evil. My programs are typically commented generously. Where necessary I create separate technical documents as well.

In order to make this mundane task a little more interesting, I always try to find tools to help with documentation. For e.g., I found Cppdoc, to document C++ programs, (similar to Javadoc), couple of years ago. While researching on this, I stumbled on Dotty. I found a great tool called GraphViz, but  I didn't do much with it at the time.

Recently when I had a need to create a flowchart for a work flow, again I looked for some tools. I could have used Visio and created some diagrams, but I wanted to automate the process of creating diagrams based on some (text) input. While researching, I re-discovered Dot and Dotty!

DOT and Dotty were originally developed by AT&T Bell Labs. DOT is a plain text graph description language. It's a definition language like CSS.  We can simply type up the information about a graph in human readable Text format, then feed it to a tool to generate a visual graph in various graphic formats. Dotty is a graph editor. AT&T actually bundled dotty and other tools as Graphviz. Graphviz is a collection of tools that interpret Dot structures and generate Graphs in various layouts.

In Dot language, Graphs can be defined as directed (DiGraph) or undirected (Graph). It also defines various elements in a graph such as nodes, arrows/lines or edges, labels etc. The syntax for the language includes various attributes of these elements as well. Thus by combining these elements and their attributes, one can build complex graphs/charts. Examples of such diagrams include, database schema diagrams, network nodes and connections, flow charts etc. The tools are capable of generating the diagrams in various graphic formats including JPG, PNG and SVG. A diagram is created in SVG format can be read by visio and other tools, thus sharing is easier.

The specification for DOT language is simple:

For e.g.,
graph {
a--b--c
}
Will generate



Each circle there is a node and the lines (with or without arrows) are called edges. Each of these have several attributes which can be used to control the presentation and to some extent actual layout.

Below is a simple directional graph (lines with arrows):

digraph{A->B->C->A}



Following line creates a yellow colored oval node:

n[style=filled color="red" fillcolor="yellow"]

The same node as a rectangle:
n[shape=rectangle style=filled color="red" fillcolor="yellow"]



There are several other attributes that can be used to build really complex diagrams. See here for syntax. References listed at the bottom have more details.

The strength of Dot is that the language is small and text-based. Thus, we can write scripts or programs to generate such a text description of a graph. One such application would be to generate graphical calling tree for programs.

Dotty and other tools in Graphviz bundle all have similar command line syntax. A typical usage would be,

 

dot -O -Tpng <example.dot>

I found a great use for this tool, recently. At work, we have sort of a workflow system. The system keeps track of various types of Cases generated by the system or created by the users. Each case type is defined by it's own workflow. To track various steps  in the workflow, we used a Treeview structure in Powerbuilder.While I was doing some research on these flows, I had a need to document the flows in a flowchart. As always, I didn't want to just open Visio and draw a few. I wanted to script it, so it can be generated by text and better yet, automatically from the database with the information we have about the flows!

Since it's so easy to create graphs from the text files, we may even be able to build graphs dynamically, thus could represent state diagrams of running processes.

There are various resources available for DOT and dotty. Dotty is originally written in C. There is a Java API (Grappa) available that wraps around Dotty functions. Apart from Dot, there are also other languages available for representing graphs in text format.

References:

http://www.graphviz.org/Documentation.php

http://wapedia.mobi/en/DOT_language

http://code.google.com/apis/chart/image/docs/gallery/graphviz.html

Friday, March 11, 2011

SQLPLUS - Spooling in HTML format

I used this recently and I thought of sharing this. If you don't know this already, SQL*PLUS has built in support for HTML. All you have to do is add markup option either on command line or inside the script. By the nature of it, it is only useful when you are spooling the output. Useful when you are dumping from multiple tables or generating simple reports. Just be aware that the output file will be larger than the usual text file spool.

Just add below to the script before spooling and the

SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON -
HEAD "<TITLE>E-App Validation Report</TITLE> -
<STYLE type='text/css'> -
<!-- BODY {background: #FFFFFF} --> -
</STYLE>" -
BODY "TEXT='#000FF'" -
TABLE "WIDTH='90%' BORDER='5'"

spool cleanup_eapp_data.html

SQLPlus - spool name with embedded timestamp

To be able to create a spool file, with filename embedded with timestamp:

— spool filename with timestamp
col sysdt noprint new_value sysdt_var
SELECT TO_CHAR(SYSDATE, ‘yyyymmdd_hh24miss’) sysdt FROM DUAL;
spool run_filename_&sysdt_var.Log

Update 12/07/12

This was confusing, as I had the same name for column name and the SQL variable created by new_value. I've renamed the two accordingly. This works.

Also, I posted about spooling to HTML some time back. You can combine the two, of course:

col spoolname new_value spoolname_var

select 'tablist_'||to_char(sysdate, 'yymmdd_hh24miss') || '.html' spoolname from dual;
prompt &spoolname_var

SET MARKUP HTML ON -
HEAD "<TITLE>Session Stats</TITLE> -
<STYLE type='text/css'> -
<!-- BODY {background: #FFFFFF} --> -
</STYLE>" -
BODY "TEXT='#000FF'" -
TABLE "WIDTH='90%' BORDER='5'"

spool &spoolname_var

SELECT username, logon_time, status FROM v$session;
spool OFF;

creates a nice HTML file with a list of all users currently logged on to the database. This is saved in a HTML file with timestamp in it's name!

Monday, November 22, 2010

Script: PB backup – Using PB ORCA to export PBLs

A basic Operating system level unit in Powerbuilder is a library file (.PBL). Each PBL file is a self contained directory in itself, with the objects stored inside in a proprietary binary format. You need Powerbuilder to view and work with this directory structure (System tree, Library painter etc).

Sometimes, it would be nice to be able to work with individual objects as text files at the OS level. This will enable us to use any text utilities to work with the objects as files (like you would a Java or C++ file). For e.g., I have a Perl script to grep through the exported text files. With text files, you can store away these source files in any version control systems (PVCS, SVN etc) easily. And you can easily diff 2 versions of an object by using any text diff tool (Of course, there are tools available to do these from inside PB, but it's just lot easier to work text files). PB allows the source code to be exported to text files with extensions, *.sr? (.srw for windows, .srd for datawindows etc.).

PB ORCA is an API that Powersoft developed early on for 3rd Party CASE tool vendors to work with PB objects. With PB ORCA, you can write programs to work PB libraries and objects from OS command line. Several object level operations available in PB IDE are also available in this API. Incidentally, there is a command line utility available with the same name (pborca.exe) on Google Code. The tool has several commands that correspond to API functions. You can create a ORCA script file (with .orc extension) and run it in batch mode using the tool. See here for a complete list of commands supported by the tool.

Below script uses pborca.exe in a DOS batch file to export all the objects in all the PBLs in a project directory to a corresponding directory structure. The batch file first loops on a list of .pbl files in a directory (passed in), and creates an ORCA script (latax.orc) with export commands. Then it runs the generated ORCA script using pborca.exe to export the object files (.*sr?). When the script finishes, you will have directory with several sub-directories, one for each PBL file exported.

This script uses several advanced features (%~ modifiers, ENABLEDELAYEDEXPANSION) of DOS Batch file. DOS scripts do several things a Unix script does, but it’s almost like an after thought! More on these later.

I use this script for daily backup of my development environment. Often times only a handful of files change on the developer's PC. Once I export the PBLs to a directory, I can diff 2 different versions using a diff tool. (I use Winmerge). The files can be optionally zipped up as well.

Pborca.exe can also handle the reverse (known as bootstrapping). Exported PB object files are only useful for performing file level operations. To be useful in a PB application, they will have to be imported back in to a PBL file again which can then be opened inside Powerbuilder. As of version 9, PB ORCA can be used to bootstrap as well. See here for the idea.

REM 12,2009 Sam V
REM script to export PBLS using pborca.exe (ORCA interface to PB)
REM this copies all the folders from source (typically Z:) to target.
REM then it creates an ORCA script to export each pbl to the subdirectories.
REM Finally it runs the generated orca script in pborca
REM @echo off

echo %0 %1, %2
@REM copy all the directories from the powerbuilder root to the export directory
@REM if a x.PBL is already in x subdirectory, you may not need MKD1 below.

@echo creating the target directory; just copy the entire path with empty sub-directories
xcopy /t /e /I %1 %2

@REM Here we start generating the orca file to be used with <a href="http://pborca.googlecode.com/svn/trunk/install/deploy/pborca.htm">PBORCA</a>.
@echo session begin pborc100.dll > latax.orc

@echo off
@SETLOCAL ENABLEDELAYEDEXPANSION
@FOR /f "usebackq delims=" %%a IN (`dir /s /b %1*.pbl`) DO (
@set a1=%%~dpa
@REM echo a1=%a1%
@call set "a1=!a1:%~1=%~2!"
@REM MKD1
@REM This creates a sub-directory with the same name as PBL file
mkdir "!a1!"
@echo export %%~fa,,,!a1! >> latax.orc)

@echo session end >> latax.orc

@ENDLOCAL

@echo on
@echo run the script in pborca
pborca latax.orc

REM end of script