Go Ahead…Think Outside The Box

I know, I know, my next tech post was supposed to be about connecting to FirebirdSQL database tables via an ODBC connection. I promise it is coming, but in the meantime, I had an epiphany the other day and I wanted to share it.

The elite FoxPro programmers out there will probably think this is a silly thing to write about and something that I probably should have known all along, but I really think what I learned by accident points to something bigger that everyone should take the time and think about. But before I get to the moral, perhaps I should tell the story first.

At the FoxForward 2007 convention, I asked a group of seasoned FoxPro programmers about all the articles I had read on the web pro and con of using Remote Views or SQL Pass through. Without a single hesitation on anyone’s part they all said in unison that SQL Pass through was the only way to go. My reason for asking the question was because I’d never seen a definitive answer to the question and frankly, I was curious to know if there was actually a preferred way of accessing remote data.

This left me with a problem. You see, I guess I’m lazy because I really like designing my screens visually and working with visual objects. Because I like to use grids in certain situations, I was having lots of problems with SQL Passthrough. I could never figure out how to use the Data Environment to open and manage my tables because every time I wanted to use a cursor that I created on the fly using passthrough in a grid, the init of the grid would happen BEFORE the query ran. I would get errors unless I performed the query in the BEFOREOPEN tables event, but when I did this, I would have to manually open and close the tables and make sure that all of the properties were set right.

Because FoxPro is so flexible, I figured no problem, just create Remote Views, parameterize them and use them in grids making sure that no data was loaded to the view until after I set the parameters and requeried the view. This worked and handled most of the situations that I ran across. However, this created a new problem because it required me to have lots and lots of predefined remote views when sometimes all I really wanted was a short list of stuff to display for the user. “Oh, well” I thought, “I guess you just have to find what works and stick with it.”

But still it nagged at me. If all the programmers at FoxForward said pass through was the way to go, then why was I having so much trouble achieving what I wanted. Either I was missing something or they were just blowing smoke. I suspected that the former was the correct answer.

Anyway, fast forward to the other night when I was working once again with a grid that I wanted to set up using data generated from a query, but in this case, I just wanted to access local data run a query on it and have the results displayed in a grid. My age-old problem re-emerged because everytime the grid instantiated, it generated an error because the ControlSource wasn’t found. I figured I’d deal with that later and went ahead and wrote my refresh/requery code to re-issue the SQL statement whenever the user changed something on the screen. I knew that grids will go blank if you issue the Requery command so I knew that I needed to issue the following series of commands:

1. thisform.lockscreen = .t.
2. thisform.stdgrid1.controlsource = ”
3. requery(‘gridview’)
4. thisform.stdgrid1.controlsource = ‘gridview’
5. thisform.lockscreen = .f.
6. thisform.refresh()

And while looking at this code, it suddenly dawned on me that I’d been staring the solution to my problem right in the face and never seen it before now. I could create my grid visually using the raw table, set up all of the click,dblclick and other code that I wanted to attach to the grid. Then, clear the ControlSource and make it blank. Once the form and grid was instantiated, I could issue whatever SQL passthrough commmands I wanted to create a cursor and then assign the cursor to the ControlSource of the grid!

I got even more excited after I tried it and it worked perfectly. The only issue was making sure that the fields I requested in my SQL command matched the columns I’d predefined in the grid. I wanted to share it with someone but when I turned to my wife and started to explain it, and her eyes glazed over, and I knew that I was going to have to put this on my blog even if people laughed at me about how silly I had been to not know all this stuff to begin with.

But the point about not knowing this previously is this: All this time I had been programming in Visual Foxpro and my assumption was that once I put something in a box on a property, it was written in stone. The flexibility of FoxPro gave me another way to accomplish what I wanted to do and cludgy as it seemed even to me, it worked, so why go back and figure out another, better way to do. At the same time, it is sometimes hard for programmers to admit they just figured something out, but it is more important to think about the programmers who are just starting out with Foxpro and make sure they don’t have to work out all of the same problems all by themselves.

Ultimately while feeling stupid, I began to wonder how many programmers out there have done exactly the same thing: Accept that what we put in a box should always stay there? How many programmers have accepted the status quo of how they do things and never examined possible alternatives? I realize that many of us work in production mode, sling the code, send the bill, and look for a new client. But my small problem led to a huge realization and I wanted to share it because who knows who else might need the same insight.

Moral Of The Story:

Moving out of our comfort zone can sometimes lead to living in an even MORE comfortable zone.

Go ahead…Think outside the box.