John Croson's Blog Home

This script was recently written to automatically process files on our FTP server. They come in daily, in pairs, and dated with todays date, e.g. XXERUP0912.dat.pgp. The script checks for existence, ensures a pair is found, makes sure they are the right and same date, then decrypts them and calls an external program I wrote to inject them into a SQL database for querying via ASP, which I also wrote. If any of the steps fail, it gets logged, and an email notification is sent to a number of folks. I had a bit of an issue with expansion, but found the error of my ways in short order... @ECHO On SETLOCAL ENABLEDELAYEDEXPANSION :: ////////////////////////////////////////////// :: :: Set path variables for key access, passphrase, :: and source our key files. SET PGPPATH=C:\PGP_Keys\ SET PGPPASS=MYPGPPASS :: The "KEY" to a successful import of our keys is running these commands as the user :: that will be running this script. :: pgp +batchmode -ka %PGPPATH%SE...

Most healthcare professionals know at this point that all providers of health care, require NPI (National Provider Identifier) numbers. Without one, it will become increasingly difficult for claims to be paid by commercial payers, and impossible to collect medicare and medicaid payments. Since we are a coding/billing/collection/management agency, we have frequented the NPPES (National Plan & Provider Enumeration System) to lookup NPI information. Unfortunately, there have been brief periods of downtime of the site, causing us to implement our own solution: a backup of the registry. I have a daily cron job that downloads the NPI database, push it into MySQL, giving us an albeit slow, but accurate access to an off-line version of this data. The shell script below performs the retrieval: #!/bin/sh WORKDIR="/srv/htdocs/npi" LOG="$WORKDIR/npi.log" MONTH=`date +%b` LASTMONTH=`date +%b --date='1 month ago'` YEAR=`date +%Y` URL="http://nppesdata.cms.h...

...in this hemisphere that is using R:Base. Although I'm sure if the other person using it reads this, I'll get flamed. I've posted a comment to the "mailing list", and have further convinced myself I'm the only one, next to the list admin, that has subscribed to this list, as my post rendered no comments, and I've not received any messages over a 72 hour period. Since this version (6.5) has few functions as compared to that of some other programming languages I've used, and interacting with the data in the form I've been forced to, I will admit to learning more about fundamental SQL data manipulation, use of Cursor's, SQL functions, etc. I'll have to say overall it's been a satisfying experience, in that it has exercised my brain. Without the luxury of having peer support or newsgroup forums to draw from, I've had to rely solely on the sparse documentation (few examples), and my past coding experience (few examples).

I've been tasked this week to review an R:Base database that is used to parse data from one of our clients, and output it into a usable form for our Misys Tiger server. It's had many little menu items added to it to perform data manipulation over the years, and never really been used to it's full potential. I'm more of a VBA/VB guy, and would rather get this done using Access, but only have a couple of weeks to clean it up before the poor guy that currently has to use this goes on vacation, when I will presumably step in to "scrub and screen" data for interfacing into Misys. Wow, what a clunky piece of stuff. I'm using v6.5, which is quite old, circa 1999 I think. Lots of functions and methods to learn, which is doing to my eyes what pouring salt into would do just quicker... ;-). I'm finding virtually no newsgroup info, and they've removed most legacy application information from their website. *sigh* Guess I'll have to post my questions t...

I had a need to import user data into Joomla with the Community Builder RC1 component installed. After a bit of discovery I found a method. First, I had to export my users from a proprietory Access database to something I could import using phpMyAdmin. I ultimately found that MySQL seems to like tab delimited files better, since phpMyAdmin likes to have a character set surrounding each cell for import purposes, and Excel doesn't export csv that way (at least not to my knowledge). Before any of these changes were attempted, I exported the tables marked for updating. Backups are good juju.... Once that was done, I had to form the data to prepare for import. The first table I imported was jos_users. I used a nice php script found somewhere on the net. Replace $TABLENAME, $FILENAME, $DATABASENAME, $PASSWORD, $USERNAME, and $HOSTNAME with appropriate values. # first get a mysql connection $dbh=mysql_connect ("$HOSTNAME", "$USERNAME", "$PASSWORD") or di...

I needed a method to update a database application across a WAN, and found that I could do this with my Linux firewalls, and Windows batch scripting. The topology is this: Win2k Svr -> Linux FW -> Internet Win2k Svr batch file: @ECHO OFF SET _LOGFILE=mydb.log Net Stop "SQL Anywhere - mydb" >> "%_LOGFILE%" ATTRIB -R "C:\Program Files\mydb Software\mydb\mydb.db" ATTRIB -R "C:\Program Files\mydb Software\mydb\mydb.log" NET USE Z: \\win2ksvr\home\backup\backup\mydb admpass /USER:domain\admin FOR /F "tokens=2,3,4 delims=/ " %%i in ('DATE /T') do set d=%%k%%i%%j FOR /F "tokens=1,2,3 delims=:. " %%i in ('TIME /T') do set t=%%i%%j%%k XCOPY "C:\Program Files\mydb Software\mydb\mydb.log" Z: CALL :REPORT %ERRORLEVEL% "C:\Program Files\mydb Software\mydb\mydb.log" XCOPY "C:\Program Files\mydb Software\mydb\mydb.db" Z: CALL :REPORT %ERRORLEVEL% "C:\Program Files\mydb Softwar...