To run a compiled native C function in an external environment instead of within the database server, the stored procedure or function is defined with the EXTERNAL NAME clause followed by the LANGUAGE attribute specifying one of C_ESQL32, C_ESQL64, C_ODBC32, or C_ODBC64.
Unlike the Perl, PHP, and Java external environments, you do not install any source code or compiled objects in the database. As a result, you do not need to execute any INSTALL statements before using the ESQL and ODBC external environments.
#include <windows.h> #include <stdio.h> #include <stdlib.h> #include <string.h> #include "extfnapi.h" BOOL APIENTRY DllMain( HMODULE hModule, DWORD ul_reason_for_call, LPVOID lpReserved ) { return TRUE; } // Note: extfn_use_new_api used only for // execution in the database server extern "C" __declspec( dllexport ) a_sql_uint32 extfn_use_new_api( void ) { return( EXTFN_API_VERSION ); } extern "C" __declspec( dllexport ) void SimpleCFunction( an_extfn_api *api, void *arg_handle ) { short result; an_extfn_value arg; an_extfn_value retval; int * intptr; int i, j, k; j = 1000; k = 0; for( i = 1; i <= 4; i++ ) { result = api->get_value( arg_handle, i, &arg ); if( result == 0 || arg.data == NULL ) break; if( arg.type & DT_TYPES != DT_INT ) break; intptr = (int *) arg.data; k += *intptr * j; j = j / 10; } retval.type = DT_INT; retval.data = (void*)&k; retval.piece_len = retval.len.total_len = (a_sql_uint32) sizeof( int ); api->set_value( arg_handle, 0, &retval, 0 ); return; }
When compiled into a dynamic link library or shared object, this function can be called from an external environment. An executable image called dbexternc12 is started by the database server and this executable image loads the dynamic link library or shared object for you.
Note that 32-bit or 64-bit versions of the database server can be used and either version can start 32-bit or 64-bit versions of dbexternc12. This is one of the advantages of using the external environment. Note that once dbexternc12 is started by the database server, it does not terminate until the connection has been terminated or a STOP EXTERNAL ENVIRONMENT statement (with the correct environment name) is executed. Each connection that does an external environment call will get its own copy of dbexternc12.
CREATE FUNCTION SimpleCDemo( IN arg1 INT, IN arg2 INT, IN arg3 INT, IN arg4 INT ) RETURNS INT EXTERNAL NAME 'SimpleCFunction@c:\\c\\extdemo.dll' LANGUAGE C_ODBC32;
This is almost identical to the way a compiled native function is described when it is to be loaded into the database server's address space. The one difference is the use of the LANGUAGE C_ODBC32 clause. This clause indicates that SimpleCDemo is a function running in an external environment and that it is using 32-bit ODBC calls. The language specification of C_ESQL32, C_ESQL64, C_ODBC32, or C_ODBC64 tells the database server whether the external C function issues 32-bit or 64-bit ODBC, ESQL, or a_v4_extfn API calls when making server-side requests.
When the native function uses none of the ODBC, ESQL, or SQL Anywhere C API calls to make server-side requests, then either C_ODBC32 or C_ESQL32 can be used for 32-bit applications and either C_ODBC64 or C_ESQL64 can be used for 64-bit applications. This is the case in the external C function shown above. It does not use any of these APIs.
SELECT SimpleCDemo(1,2,3,4);
#include <windows.h> #include <stdio.h> #include "odbc.h" #include "extfnapi.h" BOOL APIENTRY DllMain( HMODULE hModule, DWORD ul_reason_for_call, LPVOID lpReserved ) { return TRUE; } extern "C" __declspec( dllexport ) void ServerSideFunction( an_extfn_api *api, void *arg_handle ) { short result; an_extfn_value arg; an_extfn_value retval; SQLRETURN ret; ret = -1; // set up the return value struct retval.type = DT_INT; retval.data = (void*) &ret; retval.piece_len = retval.len.total_len = (a_sql_uint32) sizeof( int ); result = api->get_value( arg_handle, EXTFN_CONNECTION_HANDLE_ARG_NUM, &arg ); if( result == 0 || arg.data == NULL ) { api->set_value( arg_handle, 0, &retval, 0 ); return; } HDBC dbc = (HDBC)arg.data; HSTMT stmt = SQL_NULL_HSTMT; ret = SQLAllocHandle( SQL_HANDLE_STMT, dbc, &stmt ); if( ret != SQL_SUCCESS ) return; ret = SQLExecDirect( stmt, (SQLCHAR *) "INSERT INTO odbcTab " "SELECT table_id, table_name " "FROM SYS.SYSTAB", SQL_NTS ); if( ret == SQL_SUCCESS ) { SQLExecDirect( stmt, (SQLCHAR *) "COMMIT", SQL_NTS ); } SQLFreeHandle( SQL_HANDLE_STMT, stmt ); api->set_value( arg_handle, 0, &retval, 0 ); return; }
cl extodbc.cpp /LD /Ic:\sa12\sdk\include odbc32.lib
CREATE TABLE odbcTab(c1 int, c2 char(128)); CREATE FUNCTION ServerSideODBC( ) RETURNS INT EXTERNAL NAME 'ServerSideFunction@extodbc.dll' LANGUAGE C_ODBC32; SELECT ServerSideODBC(); // The following statement should return two identical rows SELECT COUNT(*) FROM odbcTab UNION ALL SELECT COUNT(*) FROM SYS.SYSTAB;
#include <windows.h> #include <stdio.h> #include "sqlca.h" #include "sqlda.h" #include "extfnapi.h" BOOL APIENTRY DllMain( HMODULE hModule, DWORD ul_reason_for_call, LPVOID lpReserved ) { return TRUE; } EXEC SQL INCLUDE SQLCA; static SQLCA *_sqlc; EXEC SQL SET SQLCA "_sqlc"; EXEC SQL WHENEVER SQLERROR { ret = _sqlc->sqlcode; }; extern "C" __declspec( dllexport ) void ServerSideFunction( an_extfn_api *api, void *arg_handle ) { short result; an_extfn_value arg; an_extfn_value retval; EXEC SQL BEGIN DECLARE SECTION; char *stmt_text = "INSERT INTO esqlTab " "SELECT table_id, table_name " "FROM SYS.SYSTAB"; char *stmt_commit = "COMMIT"; EXEC SQL END DECLARE SECTION; int ret = -1; // set up the return value struct retval.type = DT_INT; retval.data = (void*) &ret; retval.piece_len = retval.len.total_len = (a_sql_uint32) sizeof( int ); result = api->get_value( arg_handle, EXTFN_CONNECTION_HANDLE_ARG_NUM, &arg ); if( result == 0 || arg.data == NULL ) { api->set_value( arg_handle, 0, &retval, 0 ); return; } ret = 0; _sqlc = (SQLCA *)arg.data; EXEC SQL EXECUTE IMMEDIATE :stmt_text; EXEC SQL EXECUTE IMMEDIATE :stmt_commit; api->set_value( arg_handle, 0, &retval, 0 ); }
sqlpp extesql.sqc extesql.cpp cl extesql.cpp /LD /Ic:\sa12\sdk\include c:\sa12\sdk\lib\x86\dblibtm.lib
CREATE TABLE esqlTab(c1 int, c2 char(128)); CREATE FUNCTION ServerSideESQL( ) RETURNS INT EXTERNAL NAME 'ServerSideFunction@extesql.dll' LANGUAGE C_ESQL32; SELECT ServerSideESQL(); // The following statement should return two identical rows SELECT COUNT(*) FROM esqlTab UNION ALL SELECT COUNT(*) FROM SYS.SYSTAB;
include <windows.h> #include "sacapidll.h" #include "extfnapi.h" BOOL APIENTRY DllMain( HMODULE hModule, DWORD ul_reason_for_call, LPVOID lpReserved ) { return TRUE; } extern "C" __declspec( dllexport ) void ServerSideFunction( an_extfn_api *extapi, void *arg_handle ) { short result; an_extfn_value arg; an_extfn_value retval; unsigned offset; char *cmd; SQLAnywhereInterface capi; a_sqlany_connection * sqlany_conn; unsigned int max_api_ver; result = extapi->get_value( arg_handle, EXTFN_CONNECTION_HANDLE_ARG_NUM, &arg ); if( result == 0 || arg.data == NULL ) { return; } if( !sqlany_initialize_interface( &capi, NULL ) ) { return; } if( !capi.sqlany_init( "MyApp", SQLANY_CURRENT_API_VERSION, &max_api_ver ) ) { sqlany_finalize_interface( &capi ); return; } sqlany_conn = sqlany_make_connection( arg.data ); // processing code goes here capi.sqlany_fini(); sqlany_finalize_interface( &capi ); return; }
cl /LD /Tp extcapi.c /Tp c:\sa12\SDK\C\sacapidll.c /Ic:\sa12\SDK\Include c:\sa12\SDK\Lib\X86\dbcapi.lib
CREATE FUNCTION ServerSideC() RETURNS INT EXTERNAL NAME 'ServerSideFunction@extcapi.dll' LANGUAGE C_ESQL32; SELECT ServerSideC();
The LANGUAGE attribute in the above example specifies C_ESQL32. For 64-bit applications, you would use C_ESQL64. You must use the embedded SQL language attribute since the SQL Anywhere C API is built on the same layer (library) as ESQL.
START EXTERNAL ENVIRONMENT C_ESQL32
Another case where preloading dbexternc12 is useful is when you want to debug your external function. You can use the debugger to attach to the running dbexternc12 process and set breakpoints in your external function.
STOP EXTERNAL ENVIRONMENT C_ESQL32
To return a result set from an external function, the compiled native function must use the native function call interface.
an_extfn_result_set_info rs_info; int columns = capi.sqlany_num_cols( sqlany_stmt ); an_extfn_result_set_column_info *col_info = (an_extfn_result_set_column_info *) malloc( columns * sizeof(an_extfn_result_set_column_info) ); an_extfn_result_set_column_data *col_data = (an_extfn_result_set_column_data *) malloc( columns * sizeof(an_extfn_result_set_column_data) ); rs_info.number_of_columns = columns; rs_info.column_infos = col_info; rs_info.column_data_values = col_data;
a_sqlany_column_info info; for( int i = 0; i < columns; i++ ) { if( sqlany_get_column_info( sqlany_stmt, i, &info ) ) { // set up a column description col_info[i].column_name = info.name; col_info[i].column_type = info.native_type; switch( info.native_type ) { case DT_DATE: // DATE is converted to string by C API case DT_TIME: // TIME is converted to string by C API case DT_TIMESTAMP: // TIMESTAMP is converted to string by C API case DT_DECIMAL: // DECIMAL is converted to string by C API col_info[i].column_type = DT_FIXCHAR; break; case DT_FLOAT: // FLOAT is converted to double by C API col_info[i].column_type = DT_DOUBLE; break; case DT_BIT: // BIT is converted to tinyint by C API col_info[i].column_type = DT_TINYINT; break; } col_info[i].column_width = info.max_size; col_info[i].column_index = i + 1; // column indices are origin 1 col_info[i].column_can_be_null = info.nullable; } } // send the result set description if( extapi->set_value( arg_handle, EXTFN_RESULT_SET_ARG_NUM, (an_extfn_value *)&rs_info, EXTFN_RESULT_SET_DESCRIBE ) == 0 ) { // failed free( col_info ); free( col_data ); return; }
a_sqlany_data_value *value = (a_sqlany_data_value *) malloc( columns * sizeof(a_sqlany_data_value) ); while( capi.sqlany_fetch_next( sqlany_stmt ) ) { for( int i = 0; i < columns; i++ ) { if( capi.sqlany_get_column( sqlany_stmt, i, &value[i] ) ) { col_data[i].column_index = i + 1; col_data[i].column_data = value[i].buffer; col_data[i].data_length = (a_sql_uint32)*(value[i].length); col_data[i].append = 0; if( *(value[i].is_null) ) { // Received a NULL value col_data[i].column_data = NULL; } } } if( extapi->set_value( arg_handle, EXTFN_RESULT_SET_ARG_NUM, (an_extfn_value *)&rs_info, EXTFN_RESULT_SET_NEW_ROW_FLUSH ) == 0 ) { // failed free( value ); free( col_data ); free( col_data ); extapi->set_value( arg_handle, 0, &retval, 0 ); return; } }