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:
- Write a C function that calls GetOpenFileName(). Here are the docs for that...
- Use Visual Studio 2008 to build a 32-bit DLL containing that C function.
- Write a CREATE PROCEDURE ... EXTERNAL NAME statement to create a SQL Anywhere function that calls the C function.
- Write a CREATE SERVICE statement to create a SQL Anywhere web service that can be called from a browser.
- Write a stored procedure that calls the EXTERNAL function and is called by the SERVICE to display the HTML.
"Show me the code!"
Step 1: Create a folder for the C project: C:\projects\CStep 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".
EXPORTS extfn_use_new_api EXPORTS get_open_file_nameStep 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".
"%SQLANY12%\bin32\dbspawn.exe"^ -f^ "%SQLANY12%\bin32\dbeng12.exe"^ -xs http(port=80)^ ddd12.dbStep 12: Launch the web service in your favorite browser...
http://localhost/demo
No comments:
Post a Comment