Skip to Content

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

  1. #include <windows.h>
  2. #include <Commdlg.h>
  3. #include “extfnapi.h”
  4. extern “C” __declspec ( dllexport ) a_sql_uint32 extfn_use_new_api ( void )
  5. {
  6.    return( EXTFN_API_VERSION );
  7. }
  8. __declspec ( dllexport ) void FAR __stdcall get_open_file_name ( an_extfn_api *api, void *arg_handle )
  9. {
  10.    an_extfn_value  api_file_spec;
  11.    char *          file_spec;
  12.    OPENFILENAME    ofn;               // common dialog box structure
  13.    char            szFile [ 260 ];    // buffer for file name
  14.    BOOL            ok;
  15.    file_spec  = ( char * ) malloc ( 260 );
  16.    strcpy_s ( file_spec, 260, “” );
  17.    ZeroMemory ( &ofn, sizeof ( ofn ) );
  18.    ofn.lStructSize     = sizeof ( ofn );
  19.    ofn.hwndOwner       = NULL;
  20.    ofn.lpstrFile       = szFile;
  21.    // Set lpstrFile [ 0 ] to ‘\0’ so that GetOpenFileName does not
  22.    // use the contents of szFile to initialize itself.
  23.    ofn.lpstrFile [ 0 ] = ‘\0’;
  24.    ofn.nMaxFile        = sizeof ( szFile );
  25.    ofn.lpstrFilter     = “All\0*.*\0Text\0*.TXT\0”;
  26.    ofn.nFilterIndex    = 1;
  27.    ofn.lpstrFileTitle  = NULL;
  28.    ofn.nMaxFileTitle   = 0;
  29.    ofn.lpstrInitialDir = NULL;
  30.    ofn.Flags           = OFN_PATHMUSTEXIST | OFN_FILEMUSTEXIST;
  31.    ok = GetOpenFileName ( &ofn );
  32.    if( ok == TRUE ) {
  33.       strcpy_s ( file_spec, 260, ofn.lpstrFile );
  34.    }
  35.    api_file_spec.type      = DT_VARCHAR;
  36.    api_file_spec.data      = file_spec;
  37.    api_file_spec.piece_len = ( a_sql_uint32 )( strlen ( file_spec ) );
  38.    api_file_spec.len.total_len = ( a_sql_uint32 )( strlen ( file_spec ) );
  39.    api -> set_value ( arg_handle, 1, &api_file_spec, 0 );
  40.    free ( file_spec );
  41. }
  • 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)…

  1. CREATE SERVICE demo
  2.    TYPE ‘RAW’ AUTHORIZATION OFF USER DBA
  3.    AS CALL demo ( :action, :file_spec );
  4.  
  5. CREATE TABLE persistent_storage (
  6.    file_spec VARCHAR ( 260 ) NOT NULL );
  7.  
  8. INSERT persistent_storage VALUES ( ‘[Provide a file specification]’ );
  9. COMMIT;
  10.  
  11. CREATE PROCEDURE get_open_file_name (
  12.    OUT file_spec VARCHAR ( 260 ) )
  13.    EXTERNAL NAME ‘get_open_file_name@C:\\projects\\C\\Release\\get_open_file_name’;
  14.  
  15. CREATE PROCEDURE demo (
  16.    IN @input_action     LONG VARCHAR,
  17.    IN @input_file_spec  LONG VARCHAR )
  18.    RESULT ( html_string LONG VARCHAR )
  19. BEGIN
  20.  
  21. DECLARE @browse_file_spec  VARCHAR ( 260 );
  22. DECLARE @file_spec         VARCHAR ( 260 );
  23.  
  24. SELECT file_spec
  25.   INTO @file_spec
  26.   FROM persistent_storage;
  27.  
  28. CASE
  29.  
  30.    WHEN @input_action = ‘browse’ THEN
  31.       CALL get_open_file_name ( @browse_file_spec );
  32.       MESSAGE STRING ( ‘@browse_file_spec = “‘, @browse_file_spec, ‘”‘ ) TO CONSOLE;
  33.       IF TRIM ( COALESCE ( @browse_file_spec, ” ) ) <> ” THEN
  34.          SET @file_spec = TRIM ( @browse_file_spec );
  35.       END IF;
  36.  
  37.    WHEN @input_action = ‘save’ THEN
  38.       SET @file_spec = TRIM ( COALESCE ( @input_file_spec, ” ) );
  39.       IF @file_spec = ” THEN
  40.          SET @file_spec = ‘[Provide a file specification]’;
  41.       END IF;
  42.       UPDATE persistent_storage
  43.          SET file_spec = @file_spec;
  44.       COMMIT;
  45.  
  46.    WHEN @input_action = ‘refresh’ THEN
  47.       — no further action
  48.  
  49.    ELSE — no further action
  50.  
  51. END CASE;
  52.  
  53. CALL dbo.sa_set_http_header( ‘Content-Type’, ‘text/html’ );
  54.  
  55. SELECT STRING (
  56.    ‘<HTML>’,
  57.    ‘<HEAD>’,
  58.    ‘<script Language=”JavaScript”> ‘,
  59.     ‘function browse() { ‘,
  60.       ‘document.f.action = “demo?action=browse”; ‘,
  61.       ‘document.f.submit(); ‘,
  62.       ‘return false; ‘,
  63.    ‘} ‘,
  64.     ‘function save() { ‘,
  65.       ‘var file_spec = document.getElementById ( “file_spec” ); ‘,
  66.       ‘document.f.action = “demo?action=save&file_spec=” + file_spec.value; ‘,
  67.       ‘document.f.submit(); ‘,
  68.       ‘return false; ‘,
  69.    ‘} ‘,
  70.     ‘function refresh() { ‘,
  71.       ‘document.f.action = “demo?action=refresh”; ‘,
  72.       ‘document.f.submit(); ‘,
  73.       ‘return false; ‘,
  74.    ‘} ‘,
  75.     ‘</script> ‘,
  76.    ‘</HEAD>’,
  77.    ‘<BODY>’,
  78.     ‘<FORM NAME=”f” METHOD=”POST”> ‘,
  79.    ‘<INPUT TYPE=”TEXT” ID=”file_spec” SIZE=”160″ VALUE=”‘,
  80.       HTML_ENCODE ( @file_spec ),
  81.       ‘”>’,
  82.    ‘<P>’,
  83.    ‘<INPUT TYPE=”SUBMIT” ONCLICK=”return browse();” VALUE=”Browse”>’,
  84.    ‘<P>’,
  85.    ‘<INPUT TYPE=”SUBMIT” ONCLICK=”return save();” VALUE=”Save”>’,
  86.    ‘<P>’,
  87.    ‘<INPUT TYPE=”SUBMIT” ONCLICK=”return refresh();” VALUE=”Refresh”>’,
  88.    ‘</FORM>’,
  89.    ‘</BODY>’,
  90.    ‘</HTML>’ );
  91. 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

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply