Sunday, February 11, 2007

In-ter-oper-abili-what?

What the heck does that even mean - interoperability? Wikipedia says "Interoperability is connecting people, data and diverse systems."... never would have guessed that one...

A little over a year ago, I embarked on a journey that would help me define Interoperability in data systems -- more precisely, in database environments.

If someone asked you how well you thought a Microsoft FoxPro and a Microsoft SQL database would interact (or interoperate), what would you think? Hmmmmm. I've come to know Microsoft for their ease of integration and interoperability between common ASP.Net applications and the shrink-wrap office environment. That side of the moon is dead-nuts on but come over to my world and lets talk about VFP-to-SQL integration.

If you didn't already know, FoxPro has been around forever -- not always as a Microsoft product. They have been (or do I mean 'had' been) doing a lot of catch up to make VFP work well with the new edge of asp.net applications -- either from a front-end to SQL or a back-end to asp.net. Basically -- they did a 80% job from my view.

Now... don't get too mad at all of this. I think VFP is a great platform... it just has a ceiling. You could say the same about my beloved SQL. It has a ceiling too... it's just a hell of a lot higher, that's all. So I can interoperate between VFP front/back ends and asp.net (front) and SQL (back) ends... basically.

Now let's interject some reality and start a list of gotchas that might be considered indigenous to the environment. Here we go (subject to be added to as the days go on):

  • Case Sensitivity: VFP is primarily case-sensitive when doing db work ('selects' for example). SQL is not case sensitive when doing the same query-style operations.
  • VFP uses an implied wildcard in it's queries versus SQL who is explicit.
  • VFPOLEDB (used for connecting to a FP db from asp.net [or else]) will sometimes execute using VFP syntax, and sometimes using SQL syntax. For example, if you use a 'like' in your FP command window sql select statement... it will work. Use it from VFPOLEDB and it just looks at you funny. No error messages... just roll over and play dead. So -- no 'standardizing' on syntax... actually you can standardize. One for FP; one for SQL; and one for VFPOLEDB.
  • VFPOLEDB does not support 64-bit operating systems. I call that interoperable-NOT. Sure -- I can stick my IIS in 32-bit mode... sure am glad I got all that shiny new memory...
  • VFP considers a date-specific search to be 'anytime on that date' versus SQL that uses date-time. And by the way - it's a real pain to force SQL into 'no, I meant the whole day' mode. So if I ask my FP db to give me a total for the last 10 days (something like date()-10) it will give me all of the data from the first date in the range (all 24 hours). If I do the same in SQL, it will give me the last 240 hours worth of data.
  • more to come...

OK -- this might seem like a bitch session to you, but it is not. FoxPro has done a great job for me and is a solid performer as long as you stay in the envelope. I am simply trying to document real-life interoperability issues that you would never think about on day one. You're too busy telling your CEO "sure -- it's all Microsoft... no problem". Or maybe the classic "T-SQL is T-SQL... no problem.". I don't think so...

Hey -- let me know if you have any other items to stick on the list. I think I've bumped my head on the bulk of them already and headed for the home stretch.

Have an integrated day!

Dan Ribar