Loading 32-bit Versus 64-bit DLLs

It's only been five years since someone asked for it... I'm finally getting around to making Foxhound run on a 64-bit SQL Anywhere engine. The bottleneck's always been the teeny tiny custom DLL that Foxhound uses to read the Windows registry and a few other tasks. The 64-bit engine can only use a 64-bit DLL so two versions (32-bit and 64-bit) of the DLL are now required.

So why the delay creating a 64-bit DLL? Let's just say wrestling with C code is not my favorite task, and I assumed migrating to 64-bits was going to be a big job.

Ha ha! Not so big! Microsoft Visual Studio 2008 lets you create a second 64-bit DLL with about ten mouseclicks in the GUI... six clicks if you get them right the first time. Plus, zero C code changes... even the C headers delivered with SQL Anywhere work with both 32-bit and 64-bit code.

But... the SQL code's another matter. The CREATE PROCEDURE ... EXTERNAL NAME clause has to specify which DLL is to be used, and Foxhound doesn't know which SQL Anywhere engine is being used until the database starts up. In fact, the user can switch back and forth, using different SQL Anywhere engines on the same database.

Question: How do I determine which DLL to load at runtime?

Answer: You can't, not from SQL code.

Other Answer: Try calling each version of the DLL, see which one works.

Here's the test code that was used to verify the Other Answer:

BEGIN

DECLARE @bits               VARCHAR ( 10 );
DECLARE @command_line       VARCHAR ( 32766 );
DECLARE @return_code        INTEGER; 
DECLARE @diagnostic_code    INTEGER;
DECLARE @diagnostic_string  VARCHAR ( 255 );

SET @bits = 'unknown';  -- until proven otherwise

-- Try the 32-bit dll.

BEGIN
   DROP PROCEDURE rroad_get_command_line;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE PROCEDURE rroad_get_command_line ( 
  OUT   command_line       VARCHAR ( 32766 ), 
  OUT   return_code        INTEGER, 
  OUT   diagnostic_code    INTEGER, 
  OUT   diagnostic_string  VARCHAR ( 255 ) ) 
EXTERNAL NAME 'get_command_line@C:\\projects\\foxhound\\rroad1.dll';

BEGIN

   CALL rroad_get_command_line (
      @command_line,
      @return_code,
      @diagnostic_code,
      @diagnostic_string );

   SET @bits = '32-bit';  -- call worked

   EXCEPTION WHEN OTHERS THEN  -- ignore exception

END;

IF @bits = 'unknown' THEN

   -- Try the 64-bit dll.

   BEGIN
      DROP PROCEDURE rroad_get_command_line;
      EXCEPTION WHEN OTHERS THEN
   END;

   CREATE PROCEDURE rroad_get_command_line ( 
     OUT   command_line       VARCHAR ( 32766 ), 
     OUT   return_code        INTEGER, 
     OUT   diagnostic_code    INTEGER, 
     OUT   diagnostic_string  VARCHAR ( 255 ) ) 
   EXTERNAL NAME 'get_command_line@C:\\projects\\foxhound\\rroad164.dll';

   BEGIN

      CALL rroad_get_command_line (
         @command_line,
         @return_code,
         @diagnostic_code,
         @diagnostic_string );

      SET @bits = '64-bit';  -- call worked

      EXCEPTION WHEN OTHERS THEN  -- ignore exception

   END;

END IF;

SELECT @bits;

END;