Wednesday, December 19, 2012

Calling GetOpenFileName() From SQL

Question: How do I call the Windows API function GetOpenFileName() to display an Open File dialog box from inside a SQL Anywhere stored procedure?

Nanny Answer: You can't! You mustn't! You shouldn't!

Libertine Answer: OK, calling GetOpenFileName from inside a database running on a multi-user network server is a Really Bad Idea... if it even works, the dialog box will pop up on the computer that's running the database, which is different from the computer running the client application, so the user never sees it.

But... if you're developing a standalone application using a local database, it might be perfectly OK to give the user full access to everything on that one single computer: application, database, files, features, everything.

Full Answer

Here's what the SQL code looks like...
CALL get_open_file_name ( @browse_file_spec );
... and here's the output (yes, you can display the Windows Open dialog box from a web page using SQL Anywhere, as long as the browser and the database are running on the same computer):


Here's an overview of the steps involved:

"Show me the code!"

Step 1: Create a folder for the C project: C:\projects\C

Step 2: Create the C source code file: get_open_file_name.cpp
#include <windows.h>
#include <Commdlg.h>
#include "extfnapi.h"

extern "C" __declspec ( dllexport ) a_sql_uint32 extfn_use_new_api ( void ) 
{
   return( EXTFN_API_VERSION );
}

__declspec ( dllexport ) void FAR __stdcall get_open_file_name ( an_extfn_api *api, void *arg_handle ) 
{
   an_extfn_value  api_file_spec;

   char *          file_spec; 
   OPENFILENAME    ofn;               // common dialog box structure
   char            szFile [ 260 ];    // buffer for file name
   BOOL            ok;

   file_spec  = ( char * ) malloc ( 260 );
   strcpy_s ( file_spec, 260, "" );

   ZeroMemory ( &ofn, sizeof ( ofn ) );

   ofn.lStructSize     = sizeof ( ofn );
   ofn.hwndOwner       = NULL;
   ofn.lpstrFile       = szFile;

   // Set lpstrFile [ 0 ] to '\0' so that GetOpenFileName does not 
   // use the contents of szFile to initialize itself.

   ofn.lpstrFile [ 0 ] = '\0';

   ofn.nMaxFile        = sizeof ( szFile );
   ofn.lpstrFilter     = "All\0*.*\0Text\0*.TXT\0";
   ofn.nFilterIndex    = 1;
   ofn.lpstrFileTitle  = NULL;
   ofn.nMaxFileTitle   = 0;
   ofn.lpstrInitialDir = NULL;
   ofn.Flags           = OFN_PATHMUSTEXIST | OFN_FILEMUSTEXIST;

   ok = GetOpenFileName ( &ofn );

   if( ok == TRUE ) {
      strcpy_s ( file_spec, 260, ofn.lpstrFile );
   }

   api_file_spec.type      = DT_VARCHAR;
   api_file_spec.data      = file_spec;
   api_file_spec.piece_len = ( a_sql_uint32 )( strlen ( file_spec ) );
   api_file_spec.len.total_len = ( a_sql_uint32 )( strlen ( file_spec ) );
   api -> set_value ( arg_handle, 1, &api_file_spec, 0 );

   free ( file_spec );

}
  • The includes on lines 1 and 2 refer to standard Windows headers files that are required when calling GetOpenFileName, as documented here.

  • The include on line 3 and the function on lines 5 through 8 are required when using the SQL Anywhere external call interface.

  • Everything about the function header on line 10 is a standard requirement for any C function to be called from SQL Anywhere... everything, that is, except the function name "get_open_file_name". In particular, the parameter list is always "( an_extfn_api *api, void *arg_handle )" which bears no obvious relationship to the actual function parameters.

  • One line of code like line 12 is required for every argument that appears in the CALL from SQL. The type is always "an_extfn_value", and by convention the name is "api_...".

  • The local variables declared on lines 14 through 17 are used in the body of the function, and the code on lines 19 and 20 allocates memory and initializes the file specification that is to be filled in by the call to GetOpenFileName.

  • The code on lines 22 through 41 was copied with very little change from an example in Using Common Dialog Boxes.

  • If the call to GetOpenFileName was successful, the code on lines 43 through 45 copies the return value into the local variable file_spec.

  • The code on lines 47 through 51 is standard fare for returning a local string to the SQL CALL via an OUT parameter.

  • The code on line 53 releases the memory allocated on line 19.

  • You can find more examples of external C functions by searching this blog on "EXTERNAL NAME".
Step 3: Create a "module definition file": get_open_file_name.def
EXPORTS extfn_use_new_api
EXPORTS get_open_file_name
Step 4: Create a "DLL Project" in Visual Studio.
All Programs 
    Microsoft Visual Studio 2008
      Microsoft Visual Studio 2008
        File - New - Project From Existing Code...
          What type of project? Visual C++
          Next
          Project file location: C:\projects\C
          Project name: get_open_file_name
          Next
          check: Use Visual Studio
          Project type: Dynamically linked library (DLL) project
          Finish


Step 5: Change "Debug" to "Release" on the toolbar.


Step 6: Specify the location of SQL Anywhere *.h include files...
Solution Explorer  
     select get_open_file_name 
       right mouse - Properties  
         Configuration Properties 
           C/C++
             General
               Additional Include Directories: ...

   For SQL Anywhere 10: "C:\Program Files\SQL Anywhere 10\h"
   For SQL Anywhere 11: "C:\Program Files\SQL Anywhere 11\SDK\Include"
   For SQL Anywhere 12: "C:\Program Files\SQL Anywhere 12\SDK\Include"




Step 7: Specify the location of get_open_file_name.def file...
Solution Explorer  
     select get_open_file_name 
       right mouse - Properties  
         Configuration Properties 
           Linker
             Input
               Module Definition File: get_open_file_name.def


Step 8: Build the dll...
Shut down any SQL Anywhere engine used for unit testing.
  Build 
    Build Solution
      - or -
    Rebuild Solution


Step 9: Run a simple test in ISQL...
CREATE PROCEDURE get_open_file_name ( 
   OUT file_spec VARCHAR ( 260 ) )
   EXTERNAL NAME 'get_open_file_name@C:\\projects\\C\\Release\\get_open_file_name';

BEGIN

DECLARE @file_spec  VARCHAR ( 260 );

CALL get_open_file_name ( @file_spec );

MESSAGE STRING ( '***** get_open_file_name: "', @file_spec, '"' ) TO CLIENT; 

END;




Step 10: Create the web service plus the supporting objects (table, procedures)...
CREATE SERVICE demo
   TYPE 'RAW' AUTHORIZATION OFF USER DBA
   AS CALL demo ( :action, :file_spec );

CREATE TABLE persistent_storage (
   file_spec VARCHAR ( 260 ) NOT NULL );

INSERT persistent_storage VALUES ( '[Provide a file specification]' );
COMMIT;

CREATE PROCEDURE get_open_file_name ( 
   OUT file_spec VARCHAR ( 260 ) )
   EXTERNAL NAME 'get_open_file_name@C:\\projects\\C\\Release\\get_open_file_name';

CREATE PROCEDURE demo (
   IN @input_action     LONG VARCHAR,
   IN @input_file_spec  LONG VARCHAR )
   RESULT ( html_string LONG VARCHAR )
BEGIN

DECLARE @browse_file_spec  VARCHAR ( 260 );
DECLARE @file_spec         VARCHAR ( 260 );

SELECT file_spec
  INTO @file_spec
  FROM persistent_storage;

CASE 

   WHEN @input_action = 'browse' THEN
      CALL get_open_file_name ( @browse_file_spec );
      MESSAGE STRING ( '@browse_file_spec = "', @browse_file_spec, '"' ) TO CONSOLE;
      IF TRIM ( COALESCE ( @browse_file_spec, '' ) ) <> '' THEN
         SET @file_spec = TRIM ( @browse_file_spec );
      END IF;

   WHEN @input_action = 'save' THEN
      SET @file_spec = TRIM ( COALESCE ( @input_file_spec, '' ) );
      IF @file_spec = '' THEN
         SET @file_spec = '[Provide a file specification]';
      END IF;
      UPDATE persistent_storage
         SET file_spec = @file_spec;
      COMMIT;

   WHEN @input_action = 'refresh' THEN
      -- no further action

   ELSE -- no further action

END CASE;

CALL dbo.sa_set_http_header( 'Content-Type', 'text/html' );

SELECT STRING ( 
   '',
   '',
   ' ',
   '',
   '',

   '
', '', '

', '', '

', '', '

', '', '

', '', '' ); END;
  • The CREATE SERVICE on lines 1 through 3 defines a web service that accepts two parameters in the URL and is implemented as a CALL to a stored procedure.

  • The code on lines 5 through 9 creates and initializes some local persistant storage for the web service.

  • The CREATE PROCEDURE on lines 11 through 13 maps a SQL stored procedure to the C function in the DLL.

  • The procedure on lines 15 through 96 implements a simple web page with one input text field and three buttons, one of which calls get_open_file_name().

  • You can find more examples of web services by searching this blog on "CREATE SERVICE".
Step 11: Launch the 32-bit version of SQL Anywhere with the HTTP web server turned on...
"%SQLANY12%\bin32\dbspawn.exe"^
  -f^
  "%SQLANY12%\bin32\dbeng12.exe"^
  -xs http(port=80)^
  ddd12.db 
Step 12: Launch the web service in your favorite browser...
http://localhost/demo



No comments: