VFP SQL verus DO WHILE

Filed Under (Visual FoxPro, work.BLOG) by WildFire on 30-09-2004

172 bugs-to-squash+features-to-code down... 1,161,017 to go. I'll break from the task for a while and blog a little.

I visited a client yesterday confident that I had tweaked the ILS program to make things faster. I ended up realizing 'on site' that things got slower by 1560 folds.

Bad. Too bad really.

Because of some server folder/file-security-related issues, I have to separate the directories that are used for the ILS module (the main library system) and SASM (the book+author+subject+callnum+isbn searching) module that is being used by the students/faculty members in a given library.

Because the tables are normalized (UGH I still have an unfinished discussion blog about normalization), I have to use some VFP SQL queries to gather all the data needed from three separate tables. VFP SQL ((sans the JOIN.... yup using that makes the querying of data turtle-fast (no offense to the turtles)) is still slow.

(More than 30 seconds of query-ing is slow by Foxpro standards (we're so used to Rushmore's legendary lightning speed technology)).

For those who are not used to Visual Foxpro, VFP has it's own 'native' SQL functions which can be used without relying on ADODB, OLEDB, ODBC and whatever ODODODODO-database-related-technologies proliferating around.

So to tighten up things a little bit I decided to make a separate 'gatherer module' which will be executed on the server/admin part and will be thrown to the student/users PC used for searching instead of slowing down things every time the user 'SASMs'.

I tried using the VIEWS approach on this one (which apparently uses VFP SQL commands too) but have to withdraw at the last minute since there are some incompatibility issues with some table formats that I'm using and I don't want to completely dive in without testing things further.

So I decided to just create a different 'buffer table' that will hold the fields gathered from these three databases... using the native VFP SQL command. Here's the code:

cDATABASE = 'BOOKSASM'
set safety OFF
select BOOKS.ACCESSNO, BOOKS.CALLNUM, BOOKS.TITLE, BOOKS.ISBN, ;
BOOKS.BORROWED, min(AUTHORS.NAME) as [NAME], ;
LOCATION.LOCATION ;
from BOOKS, AUTHORS, LOCATION ;
into dbf &cDATABASE ;
group by BOOKS.ACCESSNO, BOOKS.CALLNUM, BOOKS.TITLE, ;
BOOKS.ISBN, BOOKS.BORROWED, LOCATION.LOCATION ;
where ((BOOKS.ACCESSNO == AUTHORS.ACCESSNO) or ;
BOOKS.NUM_AUTHOR == 0) ;
and BOOKS.LIBSECTION == LOCATION.ID
set safety ON

select BOOKSASM
if !file('BOOKSASM.cdx')
index on alltrim(ACCESSNO) tag CBOOIDNO
index on upper(alltrim(CALLNUM)) tag CBOOCALL
index on upper(alltrim(TITLE)) tag CBOOTITL
index on alltrim(LOCATION) tag CBOOLIBS
index on alltrim(ISBN) tag CBOOISBN
index on upper(alltrim(NAME)) tag CBOONAME
endif

close databases
close all

Guess how long it took for that piece of code to process 2224 records in BOOKS.dbf, 2856 records in AUTHORS.dbf and 15 records in LOCATION.dbf?

Well long enough for me to visit another office and install a newly updated AVRCOM module, discuss that module with the user, return and discuss (with the librarians this time) another set of topics from their lovelife to the difference between computer science and other courses with the 'IT' letters in it to topics which made me retrieve the baby pictures I have in my wallet.

52 minutes... for that code... for that number of records.

And I did not even include the instances where a conflict with the screensaver crashed the query process that I have to start things all over again, and that I copied the files outside the arms of a burdened Novell Netware 6 server and transferred it to the local drive for faster processing.

52 whoopin' minutes!

So when I got home. I re-configured the code using a reliable old school approach... DO WHILE/FOR LOOPS + SET FILTER TO.

Here...

select BOOKS	
nCountTotal = reccount()
nCounter = 0
set filter to
set order to CBOOIDNO
go top
do while not eof()
nCounter = nCounter + 1

cAccessNo = BOOKS.ACCESSNO
cCallNum = BOOKS.CALLNUM
cBookTitle = BOOKS.TITLE
cISBN = BOOKS.ISBN
lBorrowed = BOOKS.BORROWED
cLibSection = BOOKS.LIBSECTION

select AUTHORS
set filter to (AUTHORS.ACCESSNO == cAccessNo)
go top
cAuthor = AUTHORS.NAME
set filter to
cLocation = ''

select LOCATION
set filter to
set order to CLOCIDNO
go top
seek cLibSection
if found()
cLocation = LOCATION.LOCATION
endif

select BOOKSASM
append blank
replace BOOKSASM.ACCESSNO with cAccessNo
replace BOOKSASM.CALLNUM with cCallNum
replace BOOKSASM.TITLE with cBookTitle
replace BOOKSASM.ISBN with cISBN
replace BOOKSASM.BORROWED with lBorrowed
replace BOOKSASM.NAME with cAuthor
replace BOOKSASM.LOCATION with cLocation

.cStatusCounter.value = str(nCounter) + '/' + str(nCountTotal)

select BOOKS
skip
enddo

Now guess how long this code processed those 2224*2856*15 (Yes * not + : figure that out).

Less than 15 seconds.

I was even tempted to write an exact time counter after an initial test run but things were so fast that writing that part was not needed anymore.

OK... let me write a code to grab the exact time... wait.

TimeStart: 20:48:46
TimeEnd: 20:48:54

It is even less than 10 seconds!

So tell me... what am I missing here?

(Well aside from the fact that VisualFoxpro is fast and it rocks... everyone knows that already... including VB-lovin' humans [GRIN])

GEEKS and POEMS

Filed Under (Random.links) by WildFire on 28-09-2004

If the instances of grumbles and pounds from the love-of-your-life is already more than the usual amount of beeps you can handle because you're spending so much time infront of your monitor coding for world peace... stop, smell the roses and make her some poem.

Need some guidelines? Here's how a software engineer creates one.

And while you're there, do check the COUCH POTATO agreement too.

Repairing Tables and Manual Garbage Collection

Filed Under (SoftDev (non-VFP), Visual FoxPro) by WildFire on 28-09-2004

Wikis-warp: Directions how to repair tables after getting the 'Not a table' error messages and Manual Garbage Collection.

CHESTYSOFT has this simple GUI front end for registering DLL and OCX files with REGSVR32.

Hmm... 'chesty soft'... me likes how it sounds.

Meet Microsoft’s Ken Levy

Filed Under (Visual FoxPro) by WildFire on 26-09-2004

This article about Ken Levy, although quite old already (December 2001), still is a good read and explains a lot of things about Ken Levy and the Fox.

Stanislav Petrov Nuclear War and Computer Glitches

Filed Under (Random.links) by WildFire on 26-09-2004

Eleven years ago on this day... September 26, Stanislav Petrov saved the world against a possible nuclear war which could've been ignited by a computer glitch.

Here are some related links: Stanislav Petrov Averts a Worldwide Nuclear War | Armageddon Almost Not Averted.

VFP9Focus

Filed Under (Random.links, Visual FoxPro) by WildFire on 25-09-2004

PDF-link/download-warp: VFP9Focus.pdf from CoDe Magazine.

Bill Gates and Petals Around The Rose. (You have encountered that puzzle already... right?)

Walking on Cloud 9.0 of Visual FoxPro

Filed Under (Visual FoxPro, work.BLOG) by WildFire on 20-09-2004

Darn. I can't work without my costume.

We visited a new client this afternoon. We'll visit an old client tomorrow. On Wednesday we will visit another new client and another new one on Thursday or Friday depending on the schedule we will set after Wednesday's visit.

Let's not even talk about Saturday.

This is something I haven't anticipated when I dived into the freelance world. That there are instances I would be spending more time visiting clients than coding stuff in front of the PC.

Not that I'm regretting things... it is just that it's so time consuming. Plus... the travel alone renders the late night after a visit too tiring to code.

Anyway... here are some foxpro-warps: Walking on Cloud 9.0 of Visual FoxPro and The Kit Box: Knock, Knock! Who's There? (via Alex Feldstein and Andrew MacNeill's blogs)

Nifty screensavers

Filed Under (GFX, Random.links) by WildFire on 19-09-2004

For almost three years I've been using SereneScreen.com's Aquarium ScreenSaver. Needless to say, it is one of the best screensavers.

Yesterday, I found this Machine Code Screensaver from Improbable Software (through surfing Sofotex.com). It has the same 'geekie-Matrix-ie' feel but I like it better than the other Matrix-look-alike screensavers.

You can customize the pictures the screensaver shows in hex. All you need is to convert it to BMP format. The software recommends grey scaled 256-color bitmaps (8 bit), but you can actually use higher resolutions as long as it is greyscaled.

I also tried using colored bitmaps. The screensaver still displays the image but the quality is better when it is dark and grey.

Also from the same site, you'll find some fractal-powered screensaver. If you're into Fractals, they have this Optimal Mandelbrot Screensaver which elegantly depicts the fusion of art and mathematics.

But well of course the effects are enhanced by computers, the fusion of mathematics and machines.

Monster Pickup

Filed Under (Random.links) by WildFire on 18-09-2004

... now if I can just save some bucks for something like this.

Random HUMPIE JUMPIE

Filed Under (Random.links, Random.scribbles) by WildFire on 16-09-2004

Before i'll continue the Normalization post I have discussed earlier... allow me to post some random humpie-jumpie links first.

I'm making my alter-ego blog for me tonight.

From GEEK to CHEF. At first I thought it was a joke but then April 1 is months away.

One of the founders of Pyra, the company responsible for Blogger which of course has changed many lives online and offline, decided to quit the tech world to become a chef.

Meg Hourihan summarized it in three words: 'Follow your heart'. Mushy but true most of the time.

I was viewing some pictures from FooCamp this morning. It's interesting to see what those minds behind some of the great things IT has to offer are doing. And I remember that every time I see pictures from geek/developer camps, events and conferences you don't often see a human smoking.

Yup you see them doing some silly stuff like arranging mascots in sex-related positions or presenting in stage while naked in a bath tub or jumping around the stage and howling 'developers... developers... developers...' more than 10 times.

Yet... seldom (if not never) do you see a picture of someone smoking a cigar.

Which brings me to my next point... that programmers/developers are indeed logical. (Oh come on don't make me explain that further.)

... and of course programmers/developers are good role models. [- insert BIG GRIN here -]

What Steve Wozniak learned from failure. Trust me... it has nothing to do with porcupines.

Illusion linkin'-time: Here's one from Edward H. Adelson... Checkershadow Illusion. Want more..?

Here's more.

Illusions don't impress you? How about the relevance of a brilliant movie entitled Blade Runner?

Blade Runner was selected a number of times as one of the best sci-fi movies of all time.

Don't fret too much jimmo... Matrix is in that list.

Depthcore, the abstract digital artgroup where I belong, released the Verve Pack. Funky artworks... the next pack will be this 15th of October.

Expect at least an artwork from me in that release.

A different digital abstract artgroup also released their art pack... Affekted's Simplicity Pack.

You see in the 'underground' digital art community we do help each other.

Can we say the same thing in the software development world? Probably yes... if it does not involve individuals from opposing platform camps.

I haven't watched TV for quite some months and I'm not sure if this cool batch of Fido Dido commercials have been released already.

OK... enough humpie-jumpie for now.

Database Normalization Part 001

Filed Under (Visual FoxPro, work.BLOG) by WildFire on 15-09-2004

This is a screenshot of the prototype of a new project of ours.

To a give you a short overview it is a database program that monitors the children records in a certain place where parents can 'safely' leave their children so they can shop with comfort and peace of mind.

These children will then unite with the other 'temporarily abandoned' children in that place to battle the forces of evil and squash the invading twelve-headed-iodine-squirting aliens while their parents comfortably shop for shoes and perfumes.

(How I wish someone would come up with the idea for something like this where instead of children, the husbands are the main clients while their lovely wives do the shopping thing... (I would even wholeheartedly create a database program for free if that business pops up))

Some of these children obviously do not win in this enormous battle so a database program is needed to monitor who came back 'alive' and who came back 'replaced'. The TimeIn and TimeOut records of these brave kids are necessary to compute the wormhole coordinates use for their battle portals.

Let us leave those alien battle-invasion details for now and proceed.

If you can remember I posted this yesterday:

If you are to choose between the second level of data normalization or an easier usable interface, what would you prioritize?

Going back to the prototype screenshot above, you see a very simple interface. There are textboxes and combo boxes for the regular information this application needs: ChildIDNO, ChildName, Nickname, Birthdate, FatherName, MotherName, Guardian, ParentsAddress, ParentsPhoneNumber to name a few.

Easy flowing, right?

Probably yes... IF you're creating something and don't give an aliens arse after you get paid. If you're one of those irresponsible 'database dudes' out there who deserve to be devoured by sex-cravin'-tentacle-infested aliens.

If you choose the easy path you'll get a lame database that looks like this (let's just select the important fields):

_KIDS DATABASE
ChildIDNO ChildName Father Mother Etc...
0000001 X FatherofX+Y MotherofX+Y  
0000002 Y (X's bro) FatherofX+Y MotherofX+Y  
0000003 Z FatherofZ MotherofZ  

That is even the picture in the best case scenario. Re-entering the parent data again for another kid with the same parents would yield possible errors... extra spaces and more open for typo-related errors.

For example: Foo, John R.
Can be entered as: Foo, John
  Foo, John R
  Foo, John _R. (an extra space)
  Foo, Jonh R. (and countless versions of typos)

If Child_001 and Child_002 are even encoded to the database a number of days apart, the possibility of an error to occur increases. (Let's say after a month when the second child, learning that one of his sibling was abducted, pledges to go on in a quest to rescue his big brother from aliens.)

(By the way... one of the target readers for these blogs of mine are the ComSci/IT students of our country who needs 'database enlightenment'; for those who have been experts of this field already, you are free to move your arse out of here (for now) and read other more advanced stuff instead... probably about Source Control HowTo or How windows exploit the hyperthreading technology.)

Now if you'll look back to the fields above, ParentID, FatherName and MotherName, ParentAddress, ParentsPhone and the other Parents-related information are redundant. A definite no no in the area of database structure planning.

So the first 'level' of improvement on this is to create a separate database for Kids and a different database for Parents. Something like this:

_KIDS DATABASE
ChildIDNO ChildName Etc...    
0000001 X      
0000002 Y (X's bro)      
0000003 Z      
_PARENTS DATABASE
ChildIDNO Father Mother Etc...  
0000001 FatherofX MotherofX    
0000002 FatherofX MotherofX    
0000003 FatherofZ MotherofZ    

The KIDS database holds fields that are related to... well kids and the PARENTS database for PARENTS-related information such as contact number and the likes.

Now... is that enough improvement and normalization already..?

While it is better than the first table it is not that good enough yet. You can still see redundant records in the PARENTS table.

You can improve this further by creating another field PARENTS_ID on the KIDS table linked to the same field in the PARENTS table.

_KIDS DATABASE
ChildIDNO ChildName Parents_ID Etc...  
0000001 X 0000001    
0000002 Y (X's bro) 0000001    
0000003 Z 0000002    
_PARENTS DATABASE
ChildIDNO Parents_ID Father Mother Etc...
0000001 0000001 FatherofX MotherofX  
0000003 0000002 FatherofZ MotherofZ  

Now is this fine already..?

Better than the first and second approaches but you can still improve things more.

Also... notice that Child_002 disappeared in this scheme.

But I'll end things here for now and we'll continue this series on another post which will include the solution.

I also have to tackle yet the issue I posted above, the issue between Data Normalization and a more usable interface. That too on the continuation of this post.

Normalization versus Easier Usability

Filed Under (work.BLOG) by WildFire on 14-09-2004

If you are to choose between the second level of data normalization or an easier usable interface, what would you prioritize?

Programmer’s Day

Filed Under (Random.links, Visual FoxPro) by WildFire on 12-09-2004

Today is the 256th day of the year... Programmer's Day for leap years.

Programmer's Day is a whimsical "holiday" on the 256th day of the year celebrated mostly by computer programmers. The reason for this is that 256 is equal to 2 to the power of 8, which is equal to the number of values that it is possible to represent in a byte of data. Traditions include drinking, acting silly, coding silly programs, mini computer games, playing with old computers, etc.

Programmer's Day usually falls on September 13th; on leap years, it is September 12th, due to the addition of February 29th into the calendar.

Source: Wikipedia.org

Ten years ago... it was also on this date that Netscape Navigator was released.

Steve Gillmore: Allchin's last stand? Looks like these articles derived from Longhorn's component split up will never end, eh?

VFUG September 2004 Newsletter is out.

Coding for World Peace

Filed Under (GFX, Random.links) by WildFire on 12-09-2004

Inspired by Dave Barry, GapingVoid, Garfield, Neopoleon and a late night online conversation a couple of mid-nights ago with Avatar... I'm coming up with this new thing here in this site.

If you have CuriousLabs Poser 4, the toon is Edgar. The monitors and the lcd monitor mounts are from Ergotron... future 'must-have' in this technolust list of mine.

A hi-res version of the strip above is available here.

How To Write Unmaintainable Code Coding Obfuscation

Filed Under (SoftDev (non-VFP)) by WildFire on 12-09-2004

How To Write Unmaintainable Code Coding Obfuscation.

FoxproAdvisor RSS Feeds

Filed Under (Visual FoxPro) by WildFire on 11-09-2004

Foxpro Advisor Magazine now offers rss feeds. They just released Issue 2004 Week 37 too.

Now if only we can roll back to those times when everything in the 'net is virtually free...

If you’re not into a software-related career…

Filed Under (Random.links, work.BLOG) by WildFire on 09-09-2004

I spent more than half of this Blessed Mother Mary's day (or make that yesterday since it is already 4AM (again)), in bed... sleeping.

Slept up to 12Noon, fired up and tweaked some codes for a couple of hours, took my lunch at 3PM then went off to dream away (Some dream episodes just hunger for sequels). Woke up around 6 in the evening and wondered how come time travels so fast.

I should've went to the office today to work on a freon-filtered room and tinker with Fedora Core 2 in between coding, which I have just finished downloading a couple of days ago.

I have burned the .iso images last night and was able to convince qs to 'lend' me her 'not-so-old' Pentium II 166MHz IBM Aptiva computer.

That computer currently runs on Windows XP SP2 so I don't see any reason it wouldn't run in a Linux-powered OS.

Yup XP SP2 can run in that 166Mhz/64EDO RAM machine. Although the prior Windows XP SP1 runs faster. SP2 tends to slow down almost everything.

Stress on the 'everything' and not the 'almost'.

But then of course, the comparison here does not involve trojan/spyware-infested Windows XP SP1 machines.

Anyway, I was reading Stephen Sawyer's post on why he is pursuing a different career after spending years in software development. It makes you think of a lot of things especially if you're into software development.

EvenMore(DeepThinking++)... if you have slept too much for one day.

If you're not into a software development related career, what do you think you would be doing right now?

What if it needs to be a job that shouldn't be computer-related... what would that be?

Will you survive?

Feel free to transmit your binary thoughts here and let us hear some neuron decibel. (Note: You can post as GUEST... no need to register).

Not Just Usability…

Filed Under (Random.links) by WildFire on 07-09-2004

Here are some infotech-related warps before I'll proceed to my late night programming related tasks.

GMane. Nope it's not related with GMail but the idea is as slick as well... or even better. Info-overload level... stratosphere.

Joel Spolsky: It's Not Just Usability. Indeed... one needs charm too. Software applications are not exempted to charm pre-requisites you know.

OSS torpedoed: Royal Navy will run on Windows for Warships. Giving a new dimension to the object known as the 'Blue Screen of Death'... or is it 'Scream'? Well... who cares there's not much difference really.

Planet Mozilla and Internet Explorer blogs. What else can you ask for? Those are direct links to their feeds by the way.

Still don't have an RSS Aggregator? Time to download RSSBandit which recently upgraded to v1.2.0.117.

What else do you want? How about blogs from John Cormack (the brains behind IDSoftware and Doom) and Quentin Tarantino?

It's a fake Quentin blog though. But who cares? Even Tarantino's publicist was impressed.

Henry Sy and Micro-ISVs

Filed Under (Random.scribbles, SoftDev (non-VFP)) by WildFire on 06-09-2004

Inspiring. (link via Beakman's blogs)

This article talks about Henry Sy from his daughter's point of view. Henry Sy, for those who don't know him, is one of the most successful entrepreneurs here in our country.

He started from 'humble beginnings'.

Her daughter in this article talks about his father's 14 Principles which he and his family applies in the way they tackle things. These principles are not only applicable in business but also in education, software development and day to day life.

It is also good to note that Henry Sy came from China and went here to the Philippines at age 12 to seek greener pastures.

Something which is quite different these days. Now, most Filipinos are yearning to jump into the next boat of opportunity that drives them outside the bounds of this country.

But not all of us wants to jump in that ship. Some of us share the same views Herdy Yumul points out in this article. (Also a link from Beakman)

Besides, I believe the business of software development is one area our developing country could compete with the other far developed giants.

Speaking of 'business' and 'software development', I found some good links related to Micro-ISVs while reading Wesner Moise's blog that tackles this matter.

He also gave independent and small software marketing related links such as: Shareware-Marketing.net - StartUpJournal.com - StartupSkills.com - SoftwareMarketingResource.com and SharewarePromotions.com.

These sites include RSS feeds so my RSSBandit aggregator adds more pounds... as well as the tendency to be 'info-overloaded'.

I am already subscribed to Eric Sink and Joel Spolsky's feeds which also shells out insightful software-related marketing views.

But then of course, if you're following Microsoft's moves keenly you'll learn a lot of strengths and sometimes weaknesses from their visions, moves and 'workarounds'.