6.4 LABORATORY - USING C FOR DATABASE CALLS

---

Purpose:

To access a database using a simple C program.

Overview:

The program listing in Figure X.8 can be used to access the Postgres database. It uses a database access library called 'libpq'. This library of functions allows SQL database queries. The results of the query can then be easily retrieved.

In this example the program begins with an attempt to connect to the database using the function 'PQconnectdb', and the status of the connection is then checked using 'PQstatus'. An SQL query is passed to the database using the 'PQexec' command, and the results are returned into the 'PGresult' structured called 'res' in this example. The results can be checked using 'PQresultStatus', and retrieved using the 'PQgetvalue' function. The 'PQntuples' function returns the number of matching results.

After each query the results structure should be released using 'PQclear', and when all database access is complete the connection to the database should be terminated with 'PQfinish'.

#include <stdio.h>

#include <stdlib.h>

#include <pgsql/libpq-fe.h>

int main(){

char grade[3];

char query_string[256];

PGconn *conn;

PGresult *res;

conn = PQconnectdb("dbname=test");

if(PQstatus(conn) != CONNECTION_BAD){

printf("Enter a grade: ");

scanf("%2s", grade);

sprintf(query_string,

"SELECT name FROM grades WHERE grade = '%s'", grade);

res = PQexec(conn, query_string);

if(PQresultStatus(res) == PGRES_TUPLES_OK){

int i;

for(i = 0; i < PQntuples(res); i++)

printf("name = %s \n",

PQgetvalue(res, i, 0));

} else {

printf("The query failed \n");

}

PQclear(res);

PQfinish(conn);

} else {

printf("Could not open the database \n");

}

return 0;

}

Figure X.8 - C Program for Database Access (dbtest.c)

all: dbtest

CC = gcc

CFLAGS = -Wall

LIBS = -lpq

dbtest: dbtest.c

$(CC) $(CFLAGS) dbtest.c -o dbtest $(LIBS)

Figure X.9 - Makefile for Database Program

Pre-Lab:

1. Examine the database program in the Overview section.

In-Lab:

1. Enter the program and makefile given in the Overview section. Use 'make' to compile the program, and run it to verify that it does access the database.

2. Write a program that allows jobs to be entered into the customer information database created in the previous laboratory.

Submit (individually):

1. The C program to access the customer information database.