SQL Pass-Through Facility Specifics for Informix

Key Information

For general information about this feature, see About SQL Procedure Interactions . Informix examples are available.

Here are the SQL pass-through facility specifics for the Informix interface.

The dbms-name is informix.
The CONNECT statement is optional when you are connecting to an Informix database if the DBDATASRC environment variable has been set. When you omit a CONNECT statement, an implicit connection is performed when the first EXECUTE statement or CONNECTION TO component is passed to the DBMS.
You can connect to only one Informix database at a time. However, you can specify multiple CONNECT statements if they all connect to the same Informix database. If you use multiple connections, you must use an alias to identify the different connections. If you omit an alias, informix is automatically used.
The CONNECT statement database-connection-arguments are identical to its connection-options. (See connection-options.)
If you use quotation marks in your Informix pass-through statements, your DELIMIDENT= environment variable must be set to DELIMIDENT=YES, or your statements are rejected by Informix.

Stored Procedures and the SQL Pass-Through Facility

The SQL pass-through facility recognizes two types of stored procedures in Informix that perform only database functions. The methods for executing the two types of stored procedures are different.

Procedures that return no values to the calling application:
Stored procedures that do not return values can be executed directly by using the Informix SQL EXECUTE statement. Stored procedure execution is initiated with the Informix EXECUTE PROCEDURE statement. The following example executes the stored procedure make_table. The stored procedure has no input parameters and returns no values.
```
execute (execute procedure make_table())
   by informix;
```
Procedures that return values to the calling application:

Stored procedures that return values must be executed by using the PROC SQL SELECT statement with a CONNECTION TO component. This example executes the stored procedure read_address, which has one parameter, "Putnum".
The values that read_address returns serve as the contents of a virtual table for the PROC SQL SELECT statement.
```
select * from connection to informix
        (execute procedure read_address ("Putnum"));
```
For example, when you try to execute a stored procedure that returns values from a PROC SQL EXECUTE statement, you receive this error message:
```
execute (execute procedure read_address
   ("Putnum")) by informix;

ERROR: Informix EXECUTE Error: Procedure
   (read_address) returns too many values.
```

Command Restrictions for the SQL Pass-Through Facility

Informix SQL contains extensions to the ANSI-89 standards. Some of these extensions, such as LOAD FROM and UNLOAD TO, are restricted from use by any applications other than the Informix DB-Access product. Specifying these extensions in the PROC SQL EXECUTE statement generates this error:

-201
A syntax error has occurred

Examples

This example connects to Informix by using data source testdsn:

proc sql;
   connect to informix
   (user=SCOTT password=TIGER server=testdsn);

You can use the DBDATASRC environment variable to set the default data source.

This next example grants UPDATE and INSERT authority to user gomez on the Informix ORDERS table. Because the CONNECT statement is omitted, an implicit connection is made. The connection uses a default value of informix as the connection alias and default values for the SERVER= argument.

proc sql;
   execute (grant update, insert on ORDERS to gomez) by informix;
quit;

This example connects to Informix and drops (removes) the table TempData from the database. The alias Temp5 that is specified in the CONNECT statement is used in the EXECUTE statement's BY clause.

proc sql;
   connect to informix as temp5
   (server=testdsn);
   execute (drop table tempdata) by temp5;
   disconnect from temp5;
quit;

This example sends an SQL query, shown with highlighting, to the database for processing. The results from the SQL query serve as a virtual table for the PROC SQL FROM clause. In this example DBCON is a connection alias.

proc sql;
connect to informix as dbcon
   (user=testuser using=testpass
    server=testdsn);

select *
   from connection to dbcon
      (select empid, lastname, firstname,
      hiredate, salary
          from employees
          where hiredate>='31JAN88');

disconnect from dbcon;
quit;

This next example gives the previous query a name and stores it as the PROC SQL view Samples.Hires88. The CREATE VIEW statement appears in highlighting.

libname samples 'SAS-library';

proc sql;
connect to informix as mycon
   (user=testuser using=testpass
     server=testdsn);

create view samples.hires88 as
  select *
    from connection to mycon
      (select empid, lastname, firstname,
       hiredate, salary from employees
       where hiredate>='31JAN88');

disconnect from mycon;
quit;

This example connects to Informix and executes the stored procedure testproc. The select * clause displays the results from the stored procedure.

proc sql;
   connect to informix as mydb
      (server=testdsn);
   select * from connection to mydb
      (execute procedure testproc('123456'));
   disconnect from mydb;
quit;