Informix Guide to SQL: Tutorial

Informix Guide to SQL: Tutorial
Chapter 5: Programming with SQL

Home Contents Index Master Index New Book

Embedding Data Definition Statements

Data definition statements, the SQL statements that create databases and modify the definitions of tables, are not usually put into programs. The reason is that they are rarely performed. A database is created once, but it is queried and updated many times.

The creation of a database and its tables is generally done interactively, using DB-Access or the SQL Editor. These tools can also be driven from a file of statements, so that the creation of a database can be done with one operating-system command.

Embedding Grant and Revoke Privileges

One task related to data definition is performed repeatedly: the granting and revoking of privileges. The reasons for this are discussed in Chapter 11, "Granting and Limiting Access to Your Database." Because privileges must be granted and revoked frequently, and possibly by users who are not skilled in SQL, it can be useful to package the GRANT and REVOKE statements in programs to give them a simpler, more convenient user interface.

The GRANT and REVOKE statements are especially good candidates for dynamic SQL. Each statement takes the following parameters:

A list of one or more privileges
A table name
The name of a user

You probably need to supply at least some of these values based on program input (from the user, command-line parameters, or a file) but none can be supplied in the form of a host variable. The syntax of these statements does not allow host variables at any point.

The only alternative is to assemble the parts of a statement into a character string and to prepare and execute the assembled statement. Program input can be incorporated into the prepared statement as characters.

The following INFORMIX-ESQL/C function assembles a GRANT statement from parameters, and then prepares and executes it:

char priv_to_grant[100];

char table_name[20];

char user_id[20];



table_grant(priv_to_grant, table_name, user_id)

char *priv_to_grant;

char *table_name;

char *user_id;

{

	EXEC SQL BEGIN DECLARE SECTION;

	char grant_stmt[200];

	EXEC SQL END DECLARE SECTION;



	sprintf(grant_stmt, " GRANT %s ON %s TO %s",

		priv_to_grant, table_name, user_id);

	PREPARE the_grant FROM :grant_stmt;

	if(SQLCODE == 0)

		EXEC SQL EXECUTE the_grant;

	else

		printf("Sorry, got error # %d attempting %s", 

			SQLCODE, grant_stmt);



	EXEC SQL FREE the_grant;

}

The function's opening statement, shown in the following example, specifies its name and its three parameters. The three parameters specify the privileges to grant, the name of the table on which to grant privileges, and the ID of the user to receive them:

table_grant(priv_to_grant, table_name, user_id)

char *priv_to_grant;

char *table_name;

char *user_id;

The function uses the statements in the following example to define a local variable, grant_stmt, which is used to assemble and hold the GRANT statement:

EXEC SQL BEGIN DECLARE SECTION;

	char grant_stmt[200];

EXEC SQL END DECLARE SECTION;

As the following example illustrates, the GRANT statement is created by concatenating the constant parts of the statement and the function parameters:

sprintf(grant_stmt, " GRANT %s ON %s TO %s",priv_to_grant, table_name, user_id);

This statement concatenates the following six character strings:

'GRANT'
The parameter that specifies the privileges to be granted
'ON'
The parameter that specifies the table name
'TO'
The parameter that specifies the user.

The result is a complete GRANT statement composed partly of program input. The PREPARE statement passes the assembled statement text to the database server for parsing.

If the database server returns an error code in SQLCODE following the PREPARE statement, the function displays an error message. If the database server approves the form of the statement, it sets a zero return code. This action does not guarantee that the statement is executed properly; it means only that the statement has correct syntax. It might refer to a nonexistent table or contain many other kinds of errors that can be detected only during execution. The following portion of the example checks that the_grant was prepared successfully before executing it:

if(SQLCODE == 0)

	EXEC SQL EXECUTE the_grant;

else

	printf("Sorry, got error # %d attempting %s", SQLCODE, grant_stmt);

If the preparation is successful, SQLCODE = = 0, the next step executes the prepared statement.

Informix Guide to SQL: TutorialChapter 5: Programming with SQL Home Contents Index Master Index New Book

Embedding Data Definition Statements

Embedding Grant and Revoke Privileges

Informix Guide to SQL: Tutorial
Chapter 5: Programming with SQL

Home Contents Index Master Index New Book