CREATE PROCEDURE statement [External call]

You can create permanent stored procedures that call external or native procedures written in a variety of programming languages. You can use PROC as a synonym for PROCEDURE.

• OR REPLACE clause

  Specifying CREATE OR REPLACE PROCEDURE creates a new procedure, or replaces an existing procedure with the same name. This clause changes the definition of the procedure, but preserves existing privileges. An error is returned if you attempt to replace a procedure that is already in use.

• parameter

  Parameter names must conform to the rules for other database identifiers such as column names. They must be a valid SQL data type.

  Parameters can be prefixed with one of the keywords IN, OUT, or INOUT. If you do not specify one of these values, then parameters are INOUT by default. The keywords have the following meanings:

  • IN

    The parameter is an expression that provides a value to the procedure.

  • OUT

    The parameter is a variable that could be given a value by the procedure.

  • INOUT

    The parameter is a variable that provides a value to the procedure, and could be given a new value by the procedure.

  You can set the data type explicitly, or specify the %TYPE or %ROWTYPE attribute to set the data type to the data type of another object in the database. Use %TYPE to set it to the data type of a column in a table or view. Use %ROWTYPE to set the data type to a composite data type derived from a row in a table or view.

  When procedures are executed using the CALL statement, not all parameters need to be specified. If a default value is provided in the CREATE PROCEDURE statement, then missing parameters are assigned the default values. If an argument is not provided in the CALL statement, and no default is set, then an error is given.

  SQLSTATE and SQLCODE are special OUT parameters that output the SQLSTATE or SQLCODE value when the procedure ends. The SQLSTATE and SQLCODE special values can be checked immediately after a procedure call to test the return status of the procedure.

  The SQLSTATE and SQLCODE special values are modified by the next SQL statement. Providing SQLSTATE or SQLCODE as procedure arguments allows the return code to be stored in a variable.

  Specifying OR REPLACE (CREATE OR REPLACE PROCEDURE) creates a new procedure, or replaces an existing procedure with the same name. This clause changes the definition of the procedure, but preserves existing privileges. An error is returned if you attempt to replace a procedure that is in use.

  You cannot create TEMPORARY external call procedures.

• RESULT clause

  The RESULT clause declares the number and type of columns in the result set. The parenthesized list following the RESULT keyword defines the result column names and types. This information is returned by the Embedded SQL DESCRIBE or by ODBC SQLDescribeCol when a CALL statement is being described.

  If a RESULT clause is specified, then it must be the first clause of the statement.

  Embedded SQL (LANGUAGE C_ESQL32, LANGUAGE C_ESQL64) or ODBC (LANGUAGE C_ODBC32, LANGUAGE C_ODBC64) external procedures can return 0 or 1 result sets.

  Perl, PHP (LANGUAGE PERL, LANGUAGE PHP), or JavaScript external procedures cannot return result sets. Procedures that call native functions loaded by the database server cannot return result sets.

  CLR or Java (LANGUAGE CLR, LANGUAGE JAVA) external procedures can return 0, 1, or more result sets.

  Some procedures produce more than one result set, with different numbers of columns, depending on how they are executed. For example, the following procedure returns two columns under some circumstances, and one in others.

  CREATE PROCEDURE names( IN formal char(1))
  BEGIN
     IF formal = 'n' THEN
        SELECT GivenName
        FROM GROUPO.Employees
     ELSE
        SELECT Surname, GivenName
        FROM Employees
     END IF
  END;

  Procedures with variable result sets must be written without a RESULT clause, or in Transact-SQL. Their use is subject to the following limitations:

  • Embedded SQL

    You must DESCRIBE the procedure call after the cursor for the result set is opened, but before any rows are returned, to get the proper shape of result set. The CURSOR cursor-name clause on the DESCRIBE statement is required.

  • ODBC, OLE DB, ADO.NET

    Variable result-set procedures can be used by applications using these interfaces. The proper description of the result sets is carried out by the driver or provider.

  • Open Client applications

    Variable result-set procedures can be used by Open Client applications.

  If your procedure returns only one result set, then use a RESULT clause. The presence of this clause prevents ODBC and Open Client applications from re-describing the result set after a cursor is open.

  To handle multiple result sets, ODBC must describe the currently executing cursor, not the procedure's defined result set. Therefore, ODBC does not always describe column names as defined in the RESULT clause of the procedure definition. To avoid this problem, use column aliases in the SELECT statement that generates the result set.

• NO RESULT SET clause

  If a NO RESULT SET clause is specified, then it must be the first clause of the statement.

  Declares that no result set is returned by this procedure. This declaration can lead to a performance improvement.

• DYNAMIC RESULT SETS clause

  If a DYNAMIC RESULT SETS clause is specified, it must be the first clause of the statement.

  Use this clause with LANGUAGE CLR and LANGUAGE JAVA calls. The DYNAMIC RESULT SETS clause is used to specify the number of dynamic result sets that will be returned by the procedure. When a RESULT clause is specified and the DYNAMIC RESULT SETS clause is not specified, it is assumed that the number of dynamic result sets is 1. When neither the RESULT clause nor the DYNAMIC RESULT SETS clause is specified, no result set is expected and an error will result if a result set is generated.

  The C_ESQL32, C_ESQL64, C_ODBC32, and C_ODBC64 external environments can also return result sets (like CLR and JAVA), but they are restricted to only one dynamic result set.

  Procedures that call into Perl, PHP (LANGUAGE PERL, LANGUAGE PHP), or JavaScript external functions cannot return result sets. Procedures that call native functions loaded by the database server cannot return result sets.

• SQL SECURITY clause

  The SQL SECURITY clause defines whether the procedure is executed as the INVOKER (the user who is calling the procedure), or as the DEFINER (the user who owns the procedure). The default is DEFINER. For external calls, this clause establishes the ownership context for unqualified object references in the external environment.

  When SQL SECURITY INVOKER is specified, more memory is used because annotation must be done for each user that calls the procedure. Also, when SQL SECURITY INVOKER is specified, name resolution is done as the invoker as well. Therefore, qualify all object names (tables, procedures, and so on) with their appropriate owner. For example, suppose user1 creates the following procedure:

  CREATE PROCEDURE user1.myProcedure()
     RESULT( columnA INT )
     SQL SECURITY INVOKER
     BEGIN
       SELECT columnA FROM table1;
     END;

  If user2 attempts to run this procedure and a table user2.table1 does not exist, a table lookup error results. Additionally, if a user2.table1 does exist, that table is used instead of the intended user1.table1. To prevent this situation, qualify the table reference in the statement (user1.table1, instead of just table1).

• EXTERNAL NAME native-call clause

  Because native-call can specify multiple sets of operating systems, processors, libraries, and functions, more-precisely specified configurations take precedence over less-precisely defined configurations. For example, Solaris(X86_64):myfunc64@mylib.so takes precedence over Solaris:myfunc64@mylib.so.

  For syntaxes that support system-configuration, if you do not specify system-configuration, then it is assumed that the procedure runs on all system configurations. Unix represents the following Unix-based operating systems: AIX, HPUX, Linux, Mac OSX, and Solaris. The generic term Windows represents all versions of the Windows operating system.

  If you specify Unix for one of the calls, then it is assumed that the other call is for Windows.

  The specific-operating-system and processor-architecture values are those operating systems and processors supported by SQL Anywhere Server.

  The library name (library-file-prefix) is followed by the file extension, which is typically .dll on Windows and .so on Unix. In the absence of the extension, the software appends the platform-specific default file extension for libraries. For example:

  CREATE PROCEDURE mystring( IN instr LONG VARCHAR )
  EXTERNAL NAME 'mystring@mylib.dll;Unix:mystring@mylib.so';

  A simpler way to write the EXTERNAL NAME clause, using platform-specific defaults, is as follows:

  CREATE PROCEDURE mystring( IN instr LONG VARCHAR )
  EXTERNAL NAME 'mystring@mylib';

  When called, the library containing the function is loaded into the address space of the database server. The native function executes as part of the database server. In this case, if the function causes a fault, then the database server shuts down. Because of this behavior, loading and executing functions in an external environment using the LANGUAGE attribute is recommended. If a function causes a fault in an external environment, then the database server continues to run.

• EXTERNAL NAME c-call clause

  To call 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.

  When the LANGUAGE attribute is specified, then the library containing the function is loaded by an external process and the external function executes as part of that external process. In this case, if the function causes a fault, then the database server continues to run.

  The following is a sample procedure definition.

  CREATE PROCEDURE ODBCinsert( 
    IN ProductName CHAR(30),
    IN ProductDescription CHAR(50)
  )
  NO RESULT SET
  EXTERNAL NAME 'ODBCexternalInsert@extodbc.dll'
  LANGUAGE C_ODBC32;

• EXTERNAL NAME clr-call clause

  To call a Microsoft .NET function in an external environment, the procedure interface is defined with an EXTERNAL NAME clause followed by the LANGUAGE CLR attribute.

  A CLR stored procedure or function behaves the same as a SQL stored procedure or function except that the code for the procedure or function is written in a Microsoft .NET language such as Microsoft C# or Microsoft Visual Basic, and the execution of the procedure or function takes place outside the database server (that is, within a separate Microsoft .NET executable).

  CREATE PROCEDURE clr_interface( 
      IN p1 INT, 
      IN p2 UNSIGNED SMALLINT, 
      OUT p3 LONG VARCHAR) 
  NO RESULT SET
  EXTERNAL NAME 'CLRlib.dll::CLRproc.Run( int, ushort, out string )' 
  LANGUAGE CLR;

• EXTERNAL NAME perl-call clause

  To call a Perl function in an external environment, the procedure interface is defined with an EXTERNAL NAME clause followed by the LANGUAGE PERL attribute.

  A Perl stored procedure or function behaves the same as a SQL stored procedure or function except that the code for the procedure or function is written in Perl and the execution of the procedure or function takes place outside the database server (that is, within a Perl executable instance).

  The following is a sample procedure definition.

  CREATE PROCEDURE PerlWriteToConsole( IN str LONG VARCHAR) 
  NO RESULT SET
  EXTERNAL NAME '<file=PerlConsoleExample> 
      WriteToServerConsole( $sa_perl_arg0 )'
  LANGUAGE PERL;

• EXTERNAL NAME php-call clause

  To call a PHP function in an external environment, the procedure interface is defined with an EXTERNAL NAME clause followed by the LANGUAGE PHP attribute.

  A PHP stored procedure or function behaves the same as a SQL stored procedure or function except that the code for the procedure or function is written in PHP and the execution of the procedure or function takes place outside the database server (that is, within a PHP executable instance).

  The following is a sample procedure definition.

  CREATE PROCEDURE PHPPopulateTable() 
  NO RESULT SET
  EXTERNAL NAME '<file=ServerSidePHPExample> ServerSidePHPSub()'
  LANGUAGE PHP;

• EXTERNAL NAME java-call clause

  To call a Java method in an external environment, the procedure interface is defined with an EXTERNAL NAME clause followed by the LANGUAGE JAVA attribute.

  A Java-interfacing stored procedure or function behaves the same as a SQL stored procedure or function except that the code for the procedure or function is written in Java and the execution of the procedure or function takes place outside the database server (that is, within a Java VM).

  The following is a sample procedure definition.

  CREATE PROCEDURE HelloDemo( IN name LONG VARCHAR ) 
  NO RESULT SET
  EXTERNAL NAME 'Hello.main([Ljava/lang/String;)V'
  LANGUAGE JAVA;

  The descriptors for arguments and return values from Java methods have the following meanings:

  Field type	Java data type
  B	byte
  C	char
  D	double
  F	float
  I	int
  J	long
  L class-name;	An instance of the class class-name. The class name must be fully qualified, and any dot in the name must be replaced by a /. For example, java/lang/String.
  S	short
  V	void
  Z	Boolean
  [	Use one for each dimension of an array.

• EXTERNAL NAME js-call clause

  To call a JavaScript function in an external environment, the procedure interface is defined with an EXTERNAL NAME clause followed by the LANGUAGE JS attribute.

  A JavaScript stored procedure or function behaves the same as a SQL stored procedure or function except that the code for the procedure or function is written in JavaScript and the execution of the procedure or function takes place outside the database server (that is, within a Node.js executable instance).

  Specify the return type of the JavaScript function at the beginning of the EXTERNAL NAME string inside angle brackets. Since JavaScript does not allow pass-by-reference for simple variables inside functions, the left bracket character ([) can proceed the S, B, I, U, or D characters to indicate that a one element array is being passed to the JavaScript stored procedure. This syntax is provided to support INOUT and OUT parameters in stored procedures.

  The following is a sample procedure definition.

  CREATE PROCEDURE JSInOutDemo( INOUT num1 INT, OUT num2 INT ) 
    EXTERNAL NAME '<file=JSInOutParam> JSFunctionPlusOne([I[I)'
  LANGUAGE JS;

  The descriptors for arguments and return values from JavaScript methods have the following meanings:

  Field type	JavaScript data type
  S	String
  B	Boolean
  I	Integer
  U	Unsigned integer
  D	Double

Clause order is important for the following clauses, which, when specified, must appear in the order listed here:

• Result-related clauses (RESULT, NO RESULT SET, DYNAMIC RESULT SETS)
• SQL SECURITY
• EXTERNAL NAME

The CREATE PROCEDURE statement creates a procedure in the database. You can create procedures for other users by specifying an owner. A procedure is invoked with a CALL statement.

If a stored procedure returns a result set, it cannot also set output parameters or return a return value.

When referencing a temporary table from multiple procedures, a potential issue can arise if the temporary table definitions are inconsistent and statements referencing the table are cached.

If you specify an EXTERNAL NAME clause when running on Mac OS X 10.11, then you must either specify the full path to the .dylib you need to load, or place the .dylib file in the lib64 directory of the SQL Anywhere install.

You must have the CREATE PROCEDURE and CREATE EXTERNAL REFERENCE system privileges to create external procedures owned by you.

You must have the CREATE ANY PROCEDURE or CREATE ANY OBJECT system privileges, as well as the CREATE EXTERNAL REFERENCE system privilege to create external procedures owned by others.

To replace an existing procedure, you must own the procedure or have one of the following:

• CREATE ANY PROCEDURE and DROP ANY PROCEDURE system privileges.

• CREATE ANY OBJECT and DROP ANY OBJECT system privileges.

• ALTER ANY OBJECT or ALTER ANY PROCEDURE system privileges.

Automatic commit.

• ANSI/ISO SQL Standard

  CREATE PROCEDURE for an external language environment is a core feature of the ANSI/ISO SQL Standard, though some of its components supported in the software are optional SQL Language Features. A subset of these features include:

  • The SQL SECURITY clause is optional Language Feature T324.

  • The ability to pass a LONG VARCHAR, LONG NVARCHAR, or LONG BINARY value to an external procedure is SQL Language Feature T041.

  • The ability to create or modify a schema object within an external procedure, using statements such as CREATE TABLE or DROP TRIGGER, is SQL Language Feature T653.

  • The ability to use a dynamic-SQL statement within an external procedure, including statements such as CONNECT, EXECUTE IMMEDIATE, PREPARE, and DESCRIBE, is SQL Language Feature T654.

  • JAVA external procedures embody SQL Language Feature J621.

  Several clauses of the CREATE PROCEDURE statement are not in the standard. These include:

  • Support for C_ESQL32, C_ESQL64, C_ODBC32, C_ODBC64, CLR, PERL, and PHP in the LANGUAGES clause are not in the standard. The ANSI/ISO SQL Standard supports "C" as an environment-name as optional Language Feature B122.

  • The format of external-call is implementation-defined.

  • The RESULT and NO RESULT SET clauses are not in the standard. The ANSI/ISO SQL Standard uses the RETURNS clause.

  • The optional DEFAULT clause for a specific routine parameter is not in the standard.

  • The optional OR REPLACE clause is not in the standard.

• Transact-SQL

  CREATE PROCEDURE for an external routine is supported by Adaptive Server Enterprise. Adaptive Server Enterprise supports C-language and Java language external routines.