The SQLEXEC command in FoxPro allows you to sends SQL statements to a database where they can be executed, returning one or more result sets. You might make a call like this in your application:

SQLEXEC(lnConnHand,'SELECT * FROM Customer')

Of course, using SQLEXEC in this way isn’t a great architectural choice, although serviceable. It is difficult to manage code in this way and difficult to support varying back end databases. I would never code customer applications in this way. Instead, I would load an external SQL file and execute that instead.

SQLEXEC(lnConnHand,FILETOSTR("c:sqlserver2005script1.sql"))

This approach not only decouples the proprietary SQL code from my application, but it also allows me to test and review the scripts in the native application (and likely get syntax coloring and intellisense). For example, script1.sql might contain TSQL code that I can review (and test) in SSMS. Even better, if I have a lone client running SQL Server 2000, I can use a different script if a new command (like INTERSECT, for example) is not available. This approach will prove even more valuable as some clients upgrade to SQL Server 2008. I will be able to take advantage of some of the new, faster features of SQL Server 2008 while still supporting older versions.

Using Metadata

To take this a step further, I have built metadata to manage these scripts. Consider an application that supports 4 different back-end databases: Visual FoxPro, SQL Server 2000, SQL Server 2005, and MySQL. Create the following table and store all SQL for each back-end database in the appropriate memo field:

CREATE TABLE sql_meta FREE (;
  query_id character(15),;
  sql_vfp m,;
  sql_ss2005 m,;
  sql_ss2000 m,;
  sql_mysql m)

Include this table in your project. In your beforebuild event in your project hook, import or update the sql scripts from your development folders into this table when you build your application.

Now, when you want to execute a script, your code would look a little like this:

*-- create the query/sql management object and execute the code
oMQ = CREATEOBJECT( "manage_query" ) 
SQLEXEC(lnConnHand, oMQ.get_sql('SQL_ss2005','CUSTOMER'))
 
*-- ok, so not very bullet-proof but you get the idea:
DEFINE CLASS manage_query AS Custom
 
  PROCEDURE get_sql
    LPARAMETERS tcBackEnd, tcQueryId	
    SELECT sql_meta 
    LOCATE FOR query_id = tcQueryId 
    RETURN sql_meta.&tcBackEnd
  ENDPROC 
 
ENDDEFINE

I’m a big fan of writing readable code. In fact, it is a priority in my development tasks. I’m also a big fan of making testing easier. This approach allows me to view and test each SQL script outside of my business and data access objects. It also makes for more readable and manageable code.

I’ve left out some examples on how you could use parameters or SQLPREPARE using this approach. I’ll be sure to make this a topic in a future entry.