Apples and Oranges, Part II: A Linux DBMS Comparison - page 6

The Database Design

The nice thing about programming PostgreSQL in C is that you can use Embedded SQL. (Well, at least I like it.) It's not very well-documented, but the ESQL preprocessor ecpg works and generates code interfacing to PostgreSQL just fine. SQL's fixed-width way of thinking sometimes got in my way; but, otherwise, it wasn't hard to develop the client.

Did I say "not very well-documented"? That's an understatement. The otherwise complete HTML documentation of PostgreSQL is sorely lacking in this area. My knowledge of ESQL from books was rudimentary, and the online documentation didn't help much, so I had to work out all by myself exactly how C variables are cast into NUMERIC values by ecpg--among other things. Furthermore, the ESQL preprocessor is not very verbose and always seems to bail out completely whenever it encounters any trivial error. This will be a hassle for anyone working on projects with long turn-around times.

(Side note: I am fortunate enough to never have written any COBOL, but I know what it's about, and SQL is very reminiscent of it. The NUMERIC syntax is one example, but then, so is all of SQL's. Not that I would appreciate it any more if the syntax was C-like.)

I encountered some minor weirdnesses when programming the client with PostgreSQL. For example, while it is documented as being possible, ecpg would not accept a FOR READ ONLY clause when declaring a cursor. The ORDER BY clause isn't even implemented. The problems I've encountered are mostly related to the ecpg preprocessor. Postgres has a C API (after all, ESQL needs to be compiled into something), which is probably excellent, but I didn't use it (that's life). When there's ESQL, I want to use ESQL.

This is the list_books() function from postgres-client.pgc:

void list_books(void)
{
  EXEC SQL BEGIN DECLARE SECTION;
  int article_no;
  char author_first_names[30];
  char author_last_names[30];
  char title[30];
  char isbn[14];
  int wholesale_price;
  int retail_price;
  int copies_available;
  EXEC SQL END DECLARE SECTION;

  EXEC SQL DECLARE book_cursor CURSOR FOR
    SELECT ARTICLE_NO, AUTHOR_FIRST_NAMES, AUTHOR_LAST_NAMES,
           TITLE, ISBN, WHOLESALE_PRICE, RETAIL_PRICE,
	       COPIES_AVAILABLE FROM BOOK;

  EXEC SQL OPEN book_cursor;

  while (1)
	{
	  EXEC SQL FETCH NEXT FROM book_cursor
		INTO :article_no, :author_first_names, :author_last_names,
		:title, :isbn, :wholesale_price, :retail_price,
		:copies_available;
	  if (sqlca.sqlcode == 100) /* 100 == NOT FOUND */
		break; /* bail out */

	  printf("\nArticle no. %d\n", article_no);
	  printf("%s, %s:\n", author_last_names, author_first_names);
	  printf("    %s (%s)\n", title, isbn);
	  printf("Bought at %d; selling at %d; %d copies available\n\n",
			 wholesale_price, retail_price, copies_available);
	};

  EXEC SQL CLOSE book_cursor;
}

The code is pretty straightforward. It declares a couple of host variables, wrapping the declaration in a BEGIN/END DECLARE SECTION construct, opens a SELECT query with a cursor, and then fetches the rows one by one into the host variables. The cursor is then closed.

I have used the older, deprecated sqlcode variable instead of the more modern sqlstate, because it is far easier to check for a NOT FOUND state this way.