Refactoring Foxhound: The Series

Refactoring: verb; to change a computer program's code to make it amenable to change, improve its readability, or simplify its structure, while preserving its existing functionality (more...).

Foxhound: noun; a database monitor and schema troubleshooter for SQL Anywhere (more...).

Foxhound is of interest to this blog, not (just) because it's a tool dedicated to SQL Anywhere, but because it's written in SQL... at least, 61,000 lines of it is. That's 98% of the total. The other 2% is written in C.

The time has come for me to fix some bugs in Foxhound and make some enhancements, and while I'm at it I'm going to do some refactoring: every time I have to open a module to make some changes, I'm also going to look around for other improvements to make.

And while I'm doing that, I'm also going to look for code samples and programming techniques that are interesting enough to publish here... hence this post, the first one in "Refactoring Foxhound: The Series".

Refactoring Foxhound: Calling C

The only reason any part of Foxhound is written in C is because it can't be done in SQL.

The piece of C shown here lets you call the Windows API function GetEnvironmentVariable from SQL via the "external function call" mechanism.

Here is the SQL code that shows how to define a SQL function called get_environment_variable that in turn calls a function of the same name inside demodll.dll... and inside that dll resides the C code (shown later) that actually calls GetEnvironmentVariable.

CREATE PROCEDURE get_environment_variable (
   IN    name               VARCHAR ( 255 ),
   OUT   value              VARCHAR ( 32766 ),
   OUT   return_code        INTEGER,    -- OK if > 0
   OUT   diagnostic_code    INTEGER,
   OUT   diagnostic_string  VARCHAR ( 255 ) )
EXTERNAL NAME 'get_environment_variable@C:\\temp\\demodll.dll';

BEGIN
DECLARE @name              VARCHAR ( 255 );
DECLARE @value             VARCHAR ( 32766 );
DECLARE @return_code       INTEGER;
DECLARE @diagnostic_code   INTEGER;
DECLARE @diagnostic_string VARCHAR ( 255 );

SET @name = 'SQLANY11';

CALL get_environment_variable ( 
   @name, 
   @value, 
   @return_code, 
   @diagnostic_code, 
   @diagnostic_string );

SELECT @name, 
       @value,  
       @return_code,  
       @diagnostic_code,  
       @diagnostic_string;

END;

In this case the SQL function receives one input parameter, the name of the environment variable, and returns four output parameters: the value of the environment variable, a return code and two diagnostic values in case there was a problem.

For example, if you call get_environment_variable with name = 'SQLANY11' it will return value = 'C:\Program Files\SQL Anywhere 11' and return_code = a non-zero value.

If you call it with an unknown variable name, such as 'UNKNOWN', the return value will be empty, the return_code will be zero, and the diagnostic_string will contain 'GetEnvironmentVariable failed'.

Here's the actual demodll.cpp code used to compile demodll.dll via Visual C++ 2005 Express Edition, with line numbers and some explanations.

Note: This is not a tutorial in C++, nor is it a complete explanation of how to write external C functions for SQL Anywhere. For more information on the latter topic see the SQL Anywhere External Function API section in the Help, as well as the sample code in C:\Documents and Settings\All Users\Documents\SQL Anywhere 11\Samples\SQLAnywhere\ExternalProcedures.

Instead, the explanations below are my attempt to draw your attention to some tricky and troublesome bits...

  1 // *********************************************************
  2 // Copyright 1994-2008 iAnywhere Solutions, Inc.  All rights 
  3 // reserved.
  4 // This sample code is provided AS IS, without warranty or  
  5 // liability of any kind.
  6 //
  7 // You may use, reproduce, modify and distribute this sample  
  8 // code without limitation, on the condition that you retain 
  9 // the foregoing copyright notice and disclaimer as to the  
 10 // original iAnywhere code.
 11 // *********************************************************
 12 

Lines 1 through 11 is the standard Copyright notice that iAnywhere Solutions wants you to include if you publish any of the sample code that came with SQL Anywhere.

 13 #if defined( WIN32 )
 14    #include <windows.h>
 15 #endif
 16 
 17 #include <stdarg.h>
 18 #include <stdlib.h>
 19 #include <stdio.h>
 20 #include <string.h>
 21 #if defined( _MSC_VER )
 22    #include <tchar.h>
 23 #endif
 24 
 25 #include "extfnapi.h"
 26 #include "Shlobj.h"
 27 
 28 #if !defined( _unused )
 29 #define _unused( i ) ( (i) = (i) )
 30 #endif
 31 
 32 #if !defined( TRUE )
 33 #define TRUE 1
 34 #endif
 35 #if !defined( FALSE )
 36 #define FALSE 0
 37 #endif
 38 
 39 #if defined( WIN32 )
 40     #define _UINT32_ENTRY unsigned int FAR __stdcall
 41     #define _VOID_ENTRY   void FAR __stdcall
 42 #else
 43     #define _UINT32_ENTRY unsigned int 
 44     #define _VOID_ENTRY   void 
 45 #endif
 46 
 47 #define int64  __int64
 48 #define uint64 unsigned __int64
 49 
 50 #if defined( WIN32 )
 51 int __stdcall LibMain(
 52     HANDLE inst,
 53     ULONG reasoncalled,
 54     LPVOID reserved )
 55 /*********************/
 56 {
 57     _unused( inst );
 58     _unused( reasoncalled );
 59     _unused( reserved );
 60     return( 1 );
 61 }
 62 #endif
 63 
 64 __declspec(dllexport) _UINT32_ENTRY extfn_use_new_api( void )
 65 {
 66     return( EXTFN_API_VERSION );
 67 }
 68 

Lines 12 through 67 are "standard stuff" necessary for writing a C dll that's going to be called from SQL Anywhere... mess around with this code at your peril!

 69 //----------------------------------------------------------
 70 // get_environment_variable
 71 //----------------------------------------------------------
 72 
 73 __declspec(dllexport) _VOID_ENTRY get_environment_variable ( 
 74    an_extfn_api *api, void *arg_handle ) {
 75 

Lines 73 and 74 show the standard entry point for your externally-callable C function. You pick the function name, but no matter what arguments you pass in the SQL CALL, the C routine only ever sees these two parameters: an_extfn_api *api and void *arg_handle.

 76    // For information about GetEnvironmentVariable see: 
 77    // http://msdn2.microsoft.com/en-us/library/ms683188.aspx
 78 
 79    an_extfn_value  api_name;
 80    an_extfn_value  api_value;
 81    an_extfn_value  api_return_code;
 82    an_extfn_value  api_diagnostic_code;
 83    an_extfn_value  api_diagnostic_string;
 84 

Lines 79 through 83 show how you must provide special SQL-Anywhere-specific declarations for each of the arguments that are passed in the SQL CALL.

 85    char *          name; 
 86    char *          value; 
 87    DWORD           return_code; 
 88    DWORD           diagnostic_code; 
 89    char *          diagnostic_string;
 90 
 91    if ( !api -> get_value ( arg_handle, 1, &api_name ) 
 92          || api_name.data == NULL ) {
 93       return;
 94    }
 95 

Lines 85 through 89 show five local declarations corresponding to the five arguments passed in the SQL CALL.

Lines 91 through 94 check for an invalid call and return immediately.

 96    name = (char *) api_name.data;
 97 
 98    value = (char *) malloc( 32766 );
 99    strcpy_s ( value, 32766, "" );
100 
101    return_code        = 0;     
102    diagnostic_code    = 0;     
103    diagnostic_string  = (char *) malloc( 255 );
104    strcpy_s ( diagnostic_string, 255, "" );
105 

Lines 96 through 104 initialize the five local variables. The first variable receives a pointer to the environment variable name parameter, and the other four are set to default return values.

106    return_code = GetEnvironmentVariable ( 
107       (LPCTSTR) name,
108       (LPTSTR) value,
109       32767 );
110 

Lines 106 through 109 call the actual Windows API function, passing name and returning values in return_code and value.

111    if ( return_code <= 0 ) {
112       diagnostic_code = 1;
113       strcpy_s ( diagnostic_string, 255, 
114          "GetEnvironmentVariable failed" );
115    }
116 

Lines 11 through 115 handle a failed call to GetEnvironmentVariable.

117    // CHECK THE ARGUMENT NUMBERS IN THE SET_VALUE CALLS...
118 

Line 117 is an exhortation to the maintenance programmer: The five arguments passed in the SQL CALL are numbered 1, 2, 3, 4, 5 and those numbers are hard-coded in the code that follows. Getting one of those numbers wrong can wreak havoc...

119    api_value.type = DT_VARCHAR;
120    api_value.data = value;
121    api_value.piece_len 
122       = ( a_sql_uint32 )( strlen ( value ) );
123    api_value.len.total_len 
124       = ( a_sql_uint32 )( strlen ( value ) );
125    api -> set_value ( 
126       arg_handle, 2, &api_value, 0 );
127 
128    api_return_code.type = DT_INT;
129    api_return_code.data = &return_code;
130    api -> set_value (  
131       arg_handle, 3, &api_return_code, FALSE );
132 
133    api_diagnostic_code.type = DT_INT;
134    api_diagnostic_code.data = &diagnostic_code;
135    api -> set_value (  
136       arg_handle, 4, &api_diagnostic_code, FALSE );
137 
138    api_diagnostic_string.type = DT_VARCHAR;
139    api_diagnostic_string.data = diagnostic_string;
140    api_diagnostic_string.piece_len 
141       = ( a_sql_uint32 )( strlen ( diagnostic_string ) );
142    api_diagnostic_string.len.total_len 
143       = ( a_sql_uint32 )( strlen ( diagnostic_string ) );
144    api -> set_value (  
145       arg_handle, 5, &api_diagnostic_string, 0 );
146 

The four sections of code above, starting at lines 119, 128, 133 and 138 respectively, show the special SQL-Anywhere-specific code that must be written to return data to the four output parameters in the SQL CALL.

147    free ( value );
148    free ( diagnostic_string );
149 
150 } // get_environment_variable

Lines 147 and 148 perform some cleanup before return.

Here are a few tips about using Visual C++:

To specify the location of SQL Anywhere *.h include files:

   Solution Explorer - project Properties
      Configuration Properties 
      C/C++ 
      General
      Additional Include Directories...
         "C:\Program Files\SQL Anywhere 10\h"
            ... or ...
         "C:\Program Files\SQL Anywhere 11\SDK\Include"

To specify which DLL functions can be named 
in CREATE PROCEDURE ... EXTERNAL statements:

   Solution Explorer - project Properties
      Configuration Properties 
      Linker
      Input
      Module Definition File...  demodll.def

The demodll.def file contains these two lines of text:

EXPORTS extfn_use_new_api
EXPORTS get_environment_variable