Wednesday, December 3, 2008

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

2 comments:

Anonymous said...

Yeah, the beauties of SA's external C API :)

But wouldn't it be easier to call something like
SET VarName > VarValue.txt
with xp_cmdshell?
(At least it should be possible with "100% pure SQL").

Regards
Volker

Breck Carter said...

Yes, indeed... if GetEnvironmentVariable was the original reason for writing a C DLL, I might have used the xp_cmdshell route (at least, I like to *think* so :)

I chose GetEnvironmentVariable for the blog posting because the actual code is fairly simple, the intent being to expose the "beauties" of the API.

The original reason for the DLL was to obtain a list of all the User and System ODBC entries... the API code ain't the ugly part of *that* function :)