| INFORMIX-ESQL/C Programmer's Manual Working with Simple Large Objects |
|
|
The dispcat_pic program, annotated on the following pages, uses the ESQL/C loc_t locator structure to retrieve two simple-large-object columns. The program retrieves the cat_descr TEXT simple-large-object column and the cat_picture BYTE column from the catalog table of the stores7 demonstration database. For information on how to create the demonstration database, see "Demonstration Databases" on page 5 in the Introduction.
The dispcat_pic program allows you to select a database from the command line in case you created the stores7 database under a different name. If no database name is given, dispcat_pic opens the stores7 database. For example, the following command runs the dispcat_pic executable and specifies the mystores database:
The program prompts the user for a catalog_num value and performs a SELECT statement to read the description column from the stock table and the catalog_num, cat_descr, and cat_picture columns from the catalog table. If the database server finds the catalog number and the cat_picture column is not null, it writes the cat_picture column to a .gif file.
If the SELECT statement succeeds, the program displays the catalog_num, cat_descr, and description columns. Since these columns store text, they can be displayed on any ESQL/C platform. The program also allows the user to enter another catalog_num value or terminate the program.
When the catalog table is created as part of the stores7 demonstration database, the cat_picture column for all rows is set to null. The ESQL/C demonstration directory provides five graphic images. Use the blobload utility to load simple-large-object images into the cat_picture column of the catalog table.
To display these simple-large-object images from the dispcat_pic program, you must load the images to the catalog table.
Informix provides the five cat_picture images in the Graphics Interchange Format files, which have the .gif file extension.
ESQL/C provides the images in .gif files to provide them in a standard format that can be displayed on all platforms or translated into other formats with filter programs that other vendors supply. The right column of Figure 7-22 shows the names of the .gif files for the simple-large-object images.
Figure 7-22
| Image | Graphics Interchange Format (.gif Files) |
|---|---|
| Baseball glove | cn_10001.gif |
| Bicycle crankset | cn_10027.gif |
| Bicycle helmet | cn_10031.gif |
| Golf balls | cn_10046.gif |
| Running shoe | cn_10049.gif |
The numeric portion of the image filename is the catalog_num value for the row of the catalog table to which the image is to be updated. For example, cn_10027.gif should be updated to the cat_picture column of the row where 10027 is the value of catalog_num.
The blobload utility is an ESQL/C program that is provided as part of the ESQL/C demonstration files. It uses a command-line syntax to load a byte image into a specified table and column of a database.
You must run the blobload program once for each image file that you want to update. For example, the following command loads the contents of the cn_10027.gif file into the cat_picture column of the row for catalog_num 10027. The catalog_num column is the key column in the catalog table.
Use the same command to update each of the four remaining image files, substituting the filename (-f option) and corresponding catalog_num value (-k option) of the image file that you want to load.
The #include <stdio.h> statement includes the stdio.h header file from the /usr/include directory on UNIX and from the include subdirectory for Microsoft Visual C++ on Windows. The stdio.h file enables dispcat_pic to use the standard C I/O library. The program also includes the ESQL/C header files sqltypes.h and locator.h (lines 10 and 11). The locator.h file contains the definition of the locator structure and the constants that you need to work with this structure.
Use the WARNNOTIFY and NOWARNNOTIFY constants (lines 12 and 13) with the exp_chk2() exception-handling function. Calls to exp_chk2() specify one of these constants as the second argument to indicate whether or not to display SQLSTATE and SQLCODE information for warnings (WARNNOTIFY or NOWARNNOTIFY). See lines 171 to 177 for more information about the exp_chk2() function.
The program uses BUFFSZ (line 15) to specify the size of arrays that store input from the user. Line 16 defines errno, an external integer where system calls store an error number.
These lines define global host variables needed for the program. The cat_num variable holds the catalog_num column value of the catalog table. Lines 19 and 20 specify the locator structure as the data type for host variables that receive data for the cat_descr and cat_picture simple-large-object columns of the catalog table. The locator structure is the host variable for a simple-large-object column that is retrieved from or stored to the database. The locator structure has a loc_t typedef. The program uses the locator structure to specify simple-large-object size and location.
Line 22 defines a single global C variable. The cpfl character array stores the name of a file. This named file is the location for the simple-large-object .gif image of cat_picture that the database server writes.
The main() function is the point at which program execution begins. The first argument, argc, is an integer that gives the number of arguments submitted on the command line. The second argument, argv[], is a pointer to an array of character strings that contain the command-line arguments. The dispcat_pic program expects only the argv[1] argument, which is optional, to specify the name of the database to access. If argv[1] is not present, the program opens the stores7 database.
Lines 27 to 29 define the C variables that are local in scope to the main() function. The ans[BUFFSZ] array is the buffer that receives input from the user, namely the catalog number for the associated cat_picture column. Line 28 defines a 4-byte integer (ret) for the value that exp_chk2() returns and declares exp_chk2() as a function that returns a long. The db_msg[BUFFSZ + 1] character array holds the form of the CONNECT statement used to open the database. If an error occurs while the CONNECT executes, the string in db_msg is passed into the exp_chk2() function to identify the cause of the error.
Lines 30 to 33 define the ESQL/C host variables that are local to the main() function. A host variable receives data that is fetched from a table and supplies data that is written to a table. The db_name[20] character array is a host variable that stores the database name if the user specifies one on the command line. The description variable holds the value that the user entered, which is to be stored in the column of the stock table.
These lines interpret the command-line arguments and open the database. Line 35 checks whether more than two arguments are entered on the command line. If so, dispcat_pic displays a message to show the arguments that it expects and then it terminates. Line 42 assigns the default database name of stores7 to the db_name host variable. The program opens this database if the user does not enter a command-line argument.
The program then tests whether the number of command-line arguments is equal to 2. If so, dispcat_pic assumes that the second argument, argv[1], is the name of the database that the user wants to open. Line 44 uses the strcpy() function to copy the name of the database from the argv[1] command line into the db_name host variable. The program then executes the CONNECT statement (line 45) to establish a connection to the default database server and open the specified database (in db_name).
The program reproduces the CONNECT statement in the db_msg[] array (line 46). It does so for the sake of the exp_chk2() call on line 47, which takes as its argument the name of a statement. Line 47 calls the exp_chk2() function to check on the outcome. This call to exp_chk2() specifies the NOWARNNOTIFY argument to prevent the display of warnings that CONNECT generates.
After CONNECT successfully opens the database, it stores information about the database server in the sqlca.sqlwarn array. Because the dispcat_pic program handles simple-large-object data types that are not supported on INFORMIX-SE, line 52 checks the type of database server. If the sqlwarn3 element of sqlca.sqlwarn is set to W, the database server is any server other than INFORMIX-SE. Otherwise, the program notifies the user that it cannot continue and exits. The program has established the validity of the database server and now displays the name of the database that is opened (line 59).
The while(1) on line 61 begins the main processing loop in dispcat_pic. Line 63 prompts the user to enter a catalog number for the cat_picture column that the user wants to see. Line 64 calls getans() to receive the catalog number that the user inputs. The arguments for getans() are the address in which the input is stored, ans[], and the maximum length of the input that is expected, including the null terminator. If the input is unacceptable, getans() returns 0 and line 65 returns control to the while at the top of the loop in line 61, which causes the prompt for the catalog number to be redisplayed. For a more detailed explanation of getans(), see Guide to the inpfuncs.c File.Line 67 calls the ESQL/C library function rstol() to convert the character input string to a long data type to match the data type of the catalog_num column. If rstol() returns a nonzero value, the conversion fails and lines 69 to 72 display a message to the user, close the connection, and exit. Line 74 creates the name of the .gif file to which the program writes the simple-large-object image. The file name consists of the constat pic_, the catalog number that the user entered, and the extension .gif. The file is created in the directory from which the program is run.
These lines define the simple-large-object location for the TEXT cat_descr column of the catalog table, as follows:
If the select is successful, ESQL/C returns the address of the allocated buffer in loc_buffer. Line 81 sets the loc_oflags file-open mode flags to 0 because the program retrieves the simple-large-object information into memory rather than a file.
These lines prepare the locator structure to retrieve the BYTE column cat_picture of the catalog table. Line 85 moves LOCFNAME to loc_loctype to tell ESQL/C to locate the data for cat_descr in a named file. Line 86 moves the address of the cpfl filename into loc_fname. Line 87 moves the LOC_WONLY value into the loc_oflags file-open mode flags to tell ESQL/C to open the file in write-only mode. Finally, line 88 sets loc_size to -1 to tell ESQL/C to send the BYTE data in a single transfer rather than break the value into smaller pieces and use multiple transfers.
These lines define a SELECT statement to retrieve the catalog_num, cat_descr, and cat_picture columns from the catalog table and the description column from the stock table for the catalog number that the user entered. The INTO clause of the SELECT statement identifies the host variables that contain the selected values. The two loc_t host variables, cat_descr and cat_picture, are listed in this clause for the TEXT and BYTE values.
The exp_chk2() function checks whether the SELECT statement was able to find the stock_num and manu_code for the selected row in the catalog table and in the stock table. The catalog table should not contain a row that does not have a corresponding row in the stock table. Lines 98 to 103 handle a NOT FOUND condition. If the exp_chk2() function returns 100, the row was not found; lines 98 to 100 display a message to that effect. The more_to_do() function (line 101) asks whether the user wants to continue. If the user answers n for no, a break terminates the main processing loop and control transfers to line 131 to close the database before the program terminates.
If a runtime error occurs during the select, the program closes the current connection, notifies the user, and exits with a status of 1.
If cat_picture.loc_indicator contains-1 (line 111), the cat_picture column contains a null and the program informs the user (line 112). Execution then continues to line 113 to display the other returned column values.
These lines display the other columns that the SELECT statement returned. Line 116 displays the catalog number that is being processed and the description column from the stock table. Line 122 calls prdesc() to display the cat_descr column. For a detailed description of prdesc(), see Guide to the prdesc.c File.
The more_to_do() function then asks whether the user wants to enter more catalog numbers. If not, more_to_do() returns 0 and the program performs a break to terminate the main processing loop, close the database, and terminate the program.
The closing brace on line 130 terminates the main processing loop, which began with the while(1) on line 61. If the user wants to enter another catalog number, control returns to line 61.
When a break statement (line 124) terminates the main processing loop that the while(1) on line 61 began, control transfers to line 131, which closes the database and the connection to the default database server. The closing brace on line 133 terminates the main() function on line 23 and terminates the program.
Several of the ESQL/C simple-large-object demonstration programs call the prdesc() function. To avoid having the function in each program, the function is put in its own source file. Each program that calls prdesc() includes the prdesc.c source file. Since prdesc() does not contain any ESQL/C statements, the program can include it with the C #include preprocessor statement (instead of the ESQL/C include directive). For a description of this function, see Guide to the prdesc.c File.
Several of the ESQL/C demonstration programs also call the more_to_do() and getans() functions. These functions are also broken out into a separate C source file and included in the appropriate demonstration program. Neither of these functions contain ESQL/C, so the program can use the C #include preprocessor statement to include the files. For a description of these functions, see Guide to the inpfuncs.c File.
The exp_chk2() function examines the SQLSTATE status variable to determine the outcome of an SQL statement. Because many demonstration programs use exception checking, the exp_chk2() function and its supporting functions are broken out into a separate exp_chk.ec source file. The dispcat_pic program must use the ESQL/C include directive to include this file because the exception-handling functions use ESQL/C statements. For a description of the exp_chk.ec source file, see Guide to the exp_chk.ec File.
Tip: In a production environment, functions such as prdesc(), more_to_do(), getans(), and exp_chk2() would be put into C libraries and included on the command line of the ESQL/C program at compile time.
Guide to the prdesc.c File
The prdesc.c file contains the prdesc() function. This function sets the pointer p to the address that is provided in the loc_buffer field of the locator structure to access the simple large object. The function then reads the text from the buffer 80 bytes at a time up to the size specified in loc_size. This function is used in several of the simple-large-object demonstration programs so it is in a separate file and included in the appropriate source files.
Lines 2 to 20 make up the prdesc() function, which displays the cat_descr column of the catalog table. Line 4 defines size, a long integer that prdesc() initializes with the value in cat_descr.loc_size. Line 5 defines shdesc[81], an array into which prdesc() temporarily moves 80-byte chunks of the cat_descr text for output. Line 5 also defines *p, a pointer that marks the current position in the buffer as it is being displayed.
In loc_size, the database server returns the size of the buffer that it allocates for a simple large object. Line 6 moves cat_descr.loc_size to size. Line 7 displays the string "Description for:" as a header for the cat_descr text. Line 8 sets the p pointer to the buffer address that the database server returned in cat_descr.loc_size.
Line 10 begins the loop that displays the cat_descr text to the user. The while() repeats the loop until size is less than 80. Line 11 begins the body of the loop. The ESQL/C ldchar() library function copies 80 bytes from the current position in the buffer, which p addresses, to shdesc[] and removes any trailing blanks. Line 13 prints the contents of shdesc[]. Line 14 subtracts 80 from size to account for the portion of the buffer that was just printed. Line 15, the last in the loop, adds 80 to p to move it past the portion of the buffer that was just displayed.
The process of displaying cat_descr.loc_size 80 bytes at a time continues until fewer than 80 characters are left to be displayed (size < 80). Line 17 copies the remainder of the buffer into shdesc[] for the length of size. Line 18 appends a null to shdesc[size] to mark the end of the array and line 19 displays shdesc[].
The inpfuncs.c file contains the following two functions:
Because these functions are used in several ESQL/C demonstration programs, they are in a separate file and included in the appropriate demonstration source files.
Line 4 includes the UNIX ctype.h header file. This header file provides the definitions of the islower() and tolower() macros used in the definition of the LCASE() macro (defined on line 6). The program only defines the LCASE macro if it has not yet been defined in the program.
The BUFSIZE constant (line 12) defines the size of the character buffer used in the getans() function. Lines 13 to 32 constitute the getans() function. The getans() function uses the getchar() standard library function to accept input from the user. Lines 14 and 15 define the arguments for getans(), the address of the buffer (ans) where it copies the input, and the maximum number of characters (len) that the calling function expects. Line 17 defines buf[], an input buffer array. The int variable c (line 18) receives the character that getchar() returned. The second integer defined on line 18, n, is used to subscript the buf[] input buffer.
Line 19 calls getchar() to receive input from the user until a \n newline character is encountered or until the maximum input is received; that is, n is not less than BUFFSZ. Line 20 moves the input character c into the current position in buf[]. Line 21 places a null terminator at the end of the input, buf[n].
Lines 22 to 26 check whether the number of characters received, n, is less than the number of characters expected, len. If not, line 24 displays a message to the user and line 25 returns 0 to the calling function to indicate that an error occurred. Line 27 checks whether one or more characters were entered. If the expected number of characters, len, is less than or equal to 1, line 28 moves only a single character to the address that the ans calling function gives. If only one character is expected, getans() does not append a null terminator to the input. If the length of the input is greater than 1, line 30 copies the user's input to the address that the calling function (ans) supplies. Line 31 returns 1 to the calling function to indicate successful completion.
The more_to_do () function displays "More? (y/n)..." to ask whether the user wants to continue program execution. The more_to_do() function does not have any input arguments. Line 38 defines a one-character field, ans, to receive the user's response. The condition expressed on line 43 causes the question to be redisplayed until the user answers y(yes) or n(no). The LCASE macro converts the user's answer to lowercase letters for the comparison. Line 42 calls getans() to accept the user's input. Once the user answers yes or no, control passes to line 44, which returns 1 for yes and 0 for no to the calling function.