Client 3 - Processing SQL Commands

   

Client 3 ”Processing SQL Commands

Now let's turn our attention to the task of executing SQL commands and interpreting the results. To start with, I'll show you how to execute simple SQL statements in an ecpg application:

 1  2 /* client3a.pgc */  3  4 EXEC SQL INCLUDE sqlca;  5  6 #include <stdio.h>  7  8 int main( )  9 { 10 11   EXEC SQL WHENEVER SQLERROR   SQLPRINT; 12   EXEC SQL WHENEVER SQLWARNING SQLPRINT; 13   EXEC SQL WHENEVER NOT FOUND  SQLPRINT; 14 15   EXEC SQL CONNECT TO movies; 16 17   EXEC SQL 18     INSERT INTO tapes 19       VALUES 20       ( 21         'GG-44278', 22         'Strangers On A Train', 23         '1 hour 3 minutes' 24       ); 25 26   EXEC SQL 27     DELETE FROM tapes WHERE tape_id = 'GG-44278'; 28 29   EXEC SQL 30     DELETE FROM tapes WHERE tape_id IS NULL; 31 32   EXEC SQL DISCONNECT; 33 34     return( 0 ); 35 } 

You can see from this example that executing simple SQL statements with ecpg is easy ”you just insert the text of the statement after EXEC SQL . I've used the EXEC SQL WHENEVER statement that you saw in the previous section to show how easy it can be to handle errors. The DELETE command on lines 29 and 30 will produce an error message; and at the beginning of the program, I told ecpg to SQLPRINT whenever a NOT FOUND condition occurs.

The three SQL statements executed in client3a.pgc are considered simple for two reasons:

  • They don't require any data to be provided at runtime (the values involved are hard-coded).

  • No data is returned to the client application (other than error conditions).

Things get a bit more complex if you need to provide (or process) data at runtime. The first thing that changes when you need to provide C data to ecpg is that you have to tell the ecpg preprocessor about the variables in your code. You may remember from earlier in this chapter that I used the EXEC SQL BEGIN DECLARE SECTION and EXEC SQL END DECLARE SECTION directives. These ecpg directives tell the preprocessor that it should pay close attention to the variable declarations in between because you will use those variables when interacting with ecpg. A quick example should make this a little clearer:

 1 /* client3b.pgc */  2  3 EXEC SQL INCLUDE sqlca;  4  5 #include <stdio.h>  6  7 int main( int argc, char * argv[] )  8 {  9   EXEC SQL BEGIN DECLARE SECTION; 10     char  * tape_id  = argc > 1 ? argv[1] : NULL; 11     char  * title    = argc > 2 ? argv[2] : NULL; 12     char  * duration = argc > 3 ? argv[3] : NULL; 13   EXEC SQL END DECLARE SECTION; 14 15   EXEC SQL WHENEVER SQLERROR   SQLPRINT; 16   EXEC SQL WHENEVER SQLWARNING SQLPRINT; 17   EXEC SQL WHENEVER NOT FOUND  SQLPRINT; 18 19   EXEC SQL CONNECT TO movies; 20 21   EXEC SQL 22     INSERT INTO tapes 23       VALUES 24       ( 25         :tape_id, 26         :title, 27         :duration 28       ); 29 30   EXEC SQL DISCONNECT; 31 32   return( 0 ); 33 } 

At line 9, I've included an EXEC SQL BEGIN DECLARE SECTION directive. This tells the ecpg preprocessor that I will declare one or more variables ”the variable declarations end with an EXEC SQL END DECLARE SECTION directive. Once I have told ecpg about my variables, I can use them in future EXEC SQL commands.

At lines 25, 26, and 27, I've told ecpg that it should find the values that I want to insert in the tape_id , title , and duration variables. When you want ecpg to substitute a C variable within a SQL statement, you prefix the variable name with a colon ( : ).

When you run this program, you should provide three strings on the command line (enclose each string in double quotes). For example:

 $ ./client3b "SP-00001" "Young Einstein" "91 minutes" 

If you run this program with fewer than three command-line arguments, it will crash because one (or more) of the substitution variables will be set to NULL . To handle NULL values correctly, you must pair each substitution variable with an indicator variable . An indicator variable is a value that determines whether the related substitution variable is NULL . Indicator variables can be any of the following types: unsigned short , unsigned int , unsigned long , unsigned long long , short , int , long , or long long . As you'll see a little later, you should avoid using the unsigned variants because PostgreSQL uses negative values to return useful information to your application.

You match the substitution variable to its indicator by appending a colon and then the indicator name to the substitution variable name. I've rewritten client3b.pgc a bit (now client3c.pgc ) to handle NULL values better:

 1 /* client3c.pgc */  2  3 EXEC SQL INCLUDE sqlca;  4  5 EXEC SQL WHENEVER SQLERROR   SQLPRINT;  6 EXEC SQL WHENEVER SQLWARNING SQLPRINT;  7 EXEC SQL WHENEVER NOT FOUND  SQLPRINT;  8  9 #include <stdio.h> 10 11 int main( int argc, char * argv[] ) 12 { 13   EXEC SQL BEGIN DECLARE SECTION; 14 15     char  * tape_id  = argc > 1 ? argv[1] : "ignored"; 16     char  * title    = argc > 2 ? argv[2] : "ignored"; 17     char  * duration = argc > 3 ? argv[3] : "ignored"; 18 19     short   tape_id_ind  = argc > 1 ? 0 : -1; 20     short   title_ind    = argc > 2 ? 0 : -1; 21     short   duration_ind = argc > 3 ? 0 : -1; 22 23   EXEC SQL END DECLARE SECTION; 24 25   EXEC SQL CONNECT TO movies; 26 27   EXEC SQL INSERT INTO tapes 28     VALUES 29     ( 30       :tape_id  :tape_id_ind, 31       :title    :title_ind, 32       :duration :duration_ind 33     ); 34 35   EXEC SQL DISCONNECT; 36 37   return( 0 ); 38 } 

You can see that at lines 19, 20, and 21, I've created three indicator variables ”one for each substitution variable. If I want to tell the ecpg library that a column value should be set to NULL , I set its corresponding indicator variable to a negative number ( means NOT NULL , any other value means NULL ). Notice that if an indicator variable is set to indicate a NULL value, the matching substitution variable is completely ignored.

Indicator variables are also used when you request data from the database. The following client application ( client3d.pgc ) requests a single row from the tapes table and displays the values:

 1 /* client3d.pgc */  2  3 EXEC SQL INCLUDE sqlca;  4  5 EXEC SQL WHENEVER SQLERROR   SQLPRINT;  6 EXEC SQL WHENEVER SQLWARNING SQLPRINT;  7 EXEC SQL WHENEVER NOT FOUND  SQLPRINT;  8  9 #include <stdio.h> 10 11 int main( int argc, char * argv[] ) 12 { 13   EXEC SQL BEGIN DECLARE SECTION; 14 15     char  * desired_tape = argv[1]; 16 17     char    tape_id[8+1]; 18     varchar title[80+1]; 19     varchar duration[30+1]; 20 21     short   duration_ind; 22 23   EXEC SQL END DECLARE SECTION; 24 25   EXEC SQL CONNECT TO movies; 26 27   EXEC SQL 28     SELECT * INTO 29       :tape_id, 30       :title, 31       :duration :duration_ind 32     FROM tapes 33     WHERE 34       tape_id = :desired_tape; 35 36   printf( "tape_id  = %s\n", tape_id ); 37   printf( "title    = %s\n", title.arr ); 38   printf( "duration = %s\n", duration_ind < 0 39                                ? "null" : duration.arr ); 40 41   EXEC SQL DISCONNECT; 42 43   return( 0 ); 44 } 

At line 21, I've declared a single indicator ”I don't need an indicator variable for tape_id or title because those columns are declared as NOT NULL . In the SELECT command that starts at line 27, I've asked ecpg to return the value of the tape_id column into the tape_id variable, the title column into the title variable, and the duration column into the duration variable and duration_ind indicator. If you SELECT a row where the duration column is NULL , the duration_ind variable will be set to a negative number.

Take a close look at the definitions of the title and duration variables ”each is defined as an array of type varchar . varchar has special meaning to the ecpg preprocessor. Whenever the preprocessor sees a variable defined as varchar (within the declaration section), it translates the variable into a structure. The title variable is defined as varchar title[80+1]; the ecpg preprocessor will translate that definition into

 struct varchar_title { int len; char arr[80+1]; } title; 

When you SELECT a column into a varchar variable, ecpg will set the len member to the length of the data actually retrieved (the array is also null- terminated if the null character will fit).

You might be wondering what happens if the data that you ask for won't fit into the space that you have allocated. This is the second use for an indicator variable. Whenever ecpg has to truncate a value, it sets the indicator to the number of bytes actually retrieved.

So, when you retrieve a value from the database, an indicator variable can hold any of the values shown in Table 11.3.

Table 11.3. Indicator Variable Values

Indicator Value

Meaning

indicator < 0

Value was NULL

indicator = 0

Value was NOT NULL and fit into the associated substitution variable without being truncated

indicator > 0

Value was NOT NULL , but was truncated

ecpg Data Types

I mentioned the varchar data type earlier, but what other data types are understood by ecpg? The ecpg preprocessor needs to know some basic information about each of the data types that you use. When you interact with a database using ecpg, the ecpg library can convert between the C data types used in your application and the PostgreSQL data types stored in the database. When you supply data to the database, the ecpg library will convert from your C data type into the format required by the database. When you retrieve data from the database, the ecpg library will convert from PostgreSQL format into the format required by your application.

The ecpg library includes implicit support for the C data types shown here:

  • unsigned

  • unsigned short

  • unsigned int

  • unsigned long

  • unsigned long int

  • unsigned long long

  • unsigned long long int

  • unsigned char

  • short

  • short int

  • int

  • long

  • long int

  • long long

  • long long int

  • bool

  • float

  • double

  • char

  • varchar

  • struct

  • union

  • enum

Note that the char and varchar data types will be null-terminated if the null character will fit within the allotted space. If the null terminator will not fit, the indicator variable will not reflect the fact that the string was truncated.

Sometimes, we C programmers find that it's a good idea to introduce artificial data types. For example, if your application must deal with account numbers , you might introduce an acct_no data type that is defined in terms of one of the basic C data types:

 typedef unsigned int acct_no; 

You can use the contrived data type with ecpg, but you must use the EXEC SQL TYPE directive. Here's a code snippet that shows how you might use EXEC SQL TYPE :

 EXEC SQL TYPE acct_no IS unsigned int; typedef unsigned int acct_no; EXEC SQL BEGIN DECLARE SECTION;     acct_no payroll_acct; EXEC SQL END DECLARE SECTION; EXEC SQL     SELECT payroll_acct     INTO :payroll_acct     FROM employees     WHERE employee_id = 133; 

Notice that you must tell both ecpg and the C compiler what an acct_no is (in other words, you need both the EXEC SQL TYPE and the typedef ). In later releases of ecpg ( newer than 7.2), you can use the -c flag to tell the ecpg preprocessor to generate the typedef s for you.

In the preceding list you saw that the ecpg preprocessor supports the struct data type. When you ask ecpg to retrieve data into a struct , it will place each result column in a separate member of the structure. Let's modify client3d.pgc to SELECT into a structure:

 1 /* client3e.pgc */  2  3 EXEC SQL INCLUDE sqlca;  4  5 EXEC SQL WHENEVER SQLERROR   SQLPRINT;  6 EXEC SQL WHENEVER SQLWARNING SQLPRINT;  7 EXEC SQL WHENEVER NOT FOUND  SQLPRINT;  8  9 #include <stdio.h> 10 11 int main( int argc, char * argv[] ) 12 { 13   EXEC SQL BEGIN DECLARE SECTION; 14 15     char  * desired_tape = argv[1]; 16 17     struct 18     { 19         char    tape_id[8+1]; 20         varchar title[80+1]; 21         varchar duration[30+1]; 22     } tape; 23 24     struct 25     { 26         short   tape_id_ind; 27         short   title_ind; 28         short   duration_ind; 29     } tape_ind; 30 31   EXEC SQL END DECLARE SECTION; 32 33   EXEC SQL CONNECT TO movies; 34 35   EXEC SQL 36     SELECT * INTO 37       :tape :tape_ind 38     FROM tapes 39     WHERE 40       tape_id = :desired_tape; 41 42   printf( "tape_id  = %s\n", tape_ind.tape_id_ind < 0 43                               ? "null" : tape.tape_id ); 44   printf( "title    = %s\n", tape_ind.title_ind < 0 45                               ? "null" : tape.title.arr ); 46   printf( "duration = %s\n", tape_ind.duration_ind < 0 47                                ? "null" : tape.duration.arr ); 48 49   EXEC SQL DISCONNECT; 50 51   return( 0 ); 52 } 

At lines 17 “22, I've defined a structure to hold a single row from the tapes table. At lines 24 “29, I've defined a structure that holds the indicator variables for a tapes row.

When I SELECT a row from the tapes table, I've asked ecpg to place the resulting data into the tape structure and to set the indicators in the tape_ind structure.

If the data that you retrieve into a structure cannot be matched up with the structure members , you will receive a runtime error. For example, if you SELECT four columns of data into a structure that contains three members, you will receive an ECPG_TOO_FEW_ARGUMENTS error (at runtime). Likewise, if your indicator structure doesn't match the data returned by the query, you may get an ECPG_MISSING_INDICATOR error if you run into a NULL value.

To wrap up this discussion of ecpg data types, I should mention that you can ask ecpg to retrieve multiple rows into an array of substitution (and indicator) variables. I've modified the previous client application to show you how to use arrays with ecpg:

 1 /* client3f.pgc */  2  3 #include <stdio.h>  4  5 EXEC SQL INCLUDE sqlca;  6  7 EXEC SQL WHENEVER SQLERROR   SQLPRINT;  8 EXEC SQL WHENEVER SQLWARNING SQLPRINT;  9 EXEC SQL WHENEVER NOT FOUND  SQLPRINT; 10 11 EXEC SQL TYPE tape IS 12     struct tape 13     { 14         char    tape_id[8+1]; 15         varchar title[80+1]; 16         varchar duration[10+1]; 17     }; 18 19 EXEC SQL TYPE ind IS 20     struct ind 21     { 22         short   id_ind; 23         short   title_ind; 24         short   duration_ind; 25     }; 26 27 int main( ) 28 { 29   EXEC SQL BEGIN DECLARE SECTION; 30 31     tape    tapes[5]; 32     ind     inds[5]; 33 34   EXEC SQL END DECLARE SECTION; 35 36   int       r; 37 38   EXEC SQL CONNECT TO movies; 39 40   EXEC SQL 41     SELECT * INTO :tapes:inds 42     FROM tapes 43     LIMIT 5; 44 45   for( r = 0; r < 5; r++ ) 46   { 47     printf( "tape_id  = %s\n", inds[r].id_ind < 0 48                               ? "null" : tapes[r].tape_id ); 49     printf( "title    = %s\n", inds[r].title_ind < 0 50                               ? "null" : tapes[r].title.arr ); 51     printf( "duration = %s\n\n",inds[r].duration_ind < 0 52                                ? "null" : tapes[r].duration.arr ); 53   } 54 55   EXEC SQL DISCONNECT; 56 57   return( 0 ); 58 } 

At line 30 and 31, I've defined an array of five tape structures and five indicator structures. [4] When I SELECT data into these variables, the ecpg library will place the first row in the first array element, the second row in the second array element, and so on. Likewise, the indicators for the first row will be placed in the first member of the inds array, the second set of indicators will be placed in the second member, and so on. In this example, I've allocated enough space to hold five rows and I've limited the query to return no more than five rows. If you try to retrieve more rows than will fit into the space you've allocated, ecpg will trigger an ECPG_TOO_MANY_MATCHES error.

[4] In this example, I've taken advantage of the -c flag to let the ecpg preprocessor generate structure typedef s for me. The -c flag offers more than mere convenience ”it lets you include varchar members in a structure. Without the -c flag, you can't include varchar members with a structure; the ecpg preprocessor can't handle it.

   


PostgreSQL
PostgreSQL (2nd Edition)
ISBN: 0672327562
EAN: 2147483647
Year: 2005
Pages: 220
Authors: Korry Douglas

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net