602SQL Documentation Index  
<­Folders for External Functions Called from SQL UpRoutines Stored on the Server    

An Example of Calling an External Function

The following function deletes a value from the Windows registry on the 602SQL Server. It is required to make 3 functions from the system library ADVAPI32.DLL accessible to accomplish this task.

function RegOpenKeyEx(IN hKey INT, INOUT lpSubKey CHAR(100),
  IN ulOptions INT, IN samDesired INT, OUT phkResult INT) returns int;
external name 'RegOpenKeyExA@advapi32.dll';

function RegCloseKey(IN hKey INT) returns int;
external name 'RegCloseKey@advapi32.dll';

function RegDeleteValue(IN hKey INT, INOUT lpValueName CHAR(100)) 
  returns int;
external name 'RegDeleteValueA@advapi32.dll';

The RegOpenKeyEx and RegDeleteValue functions are in the ADVAPI32.DLL library with the letter A at the end of their names. This indicates that the string parameters use 8-bit characters. The ADVAPI32.DLL library must be accessible from the 602SQL Server. This library is located in the Windows system folder, but it is not safe to allow external library access to the Windows system folder. Therefore, it is recommended to do one of the following:

  1. Copy this library to another folder.
  2. Specify the full path to this library from the above folder in the three functions mentioned.
  3. Add the above folder to the external library access folder list for 602SQL Server

The following function will delete a registry value:

function Delete_value_in_reg_LM(IN key_name CHAR(100), 
  IN value_name CHAR(100)) 
  returns int;
BEGIN
  declare HKEY_LOCAL_MACHINE int default -2147483646;
  declare KEY_ALL_ACCESS int default 983103;
  declare hSubKey int;
  declare res int;
  SET res = RegOpenKeyEx(HKEY_LOCAL_MACHINE, key_name, 0, 
    KEY_ALL_ACCESS, hSubKey);
  if res=0 then
    SET res=RegDeleteValue(hSubKey, value_name);
    call RegCloseKey(hSubKey);
  end if;
  return res;
END

This version can only be used to delete values from the LOCAL_MACHINE registry hive.

<­Folders for External Functions Called from SQL UpRoutines Stored on the Server