Query Language commands may be embedded in a C program with the routine mscall. The routine takes the database directory and the Query Language command as its arguments.
mscall (database_directory, query_language_statement);
Both the database directory and the Query Language statement should be strings. The strings may be built by the application program. The routine returns zero (0) if the statement succeeds, and a non-zero value if it fails. (A failure may be caused by a syntax error in the Query Language command, or an invalid attribute or table name.)
The header file mscc.h must be included in all C applications using mscall. It contains definitions of constants and data types used by all the Host Language Interface routines, including mscall. The header file is listed below.
/***********************************************************************
* (c) Copyright Empress Software Inc. 1983, 2006
***********************************************************************/
#ifndef __MSCC_H
#define __MSCC_H
#ifdef __cplusplus
extern "C" {
#endif
#include <stdio.h>
#include "c.h"
#include <misc/public.h>
#include <usrmp/msdsqldt.h>
#include <usrfns/dtparvar.hx>
#include <api/mrapi.hx>
#include <api/msapi.hx>
#include <api/mxapi.hx>
#include <misc/init.hx>
#include <misc/mscc_com.h>
#include <misc/mxe.h>
#include <misc/mxtrig.h>
#ifdef __cplusplus
}
#endif
#endif /* __MSCC_H */
Note that, the mscc.h file differs from version to version. For an exact reference, the file is located in the following Empress directory:
There are two ways to organize a program using mscall.
The routine msclean allows you to put a procedure on the stack to be called immediately before the program exits.
Programs using mscall should be compiled using empcc, rather than cc. This ensures that the appropriate Empress library routines are included when the program is compiled. In all other respects, empcc is identical to cc.
The database name in the following examples is a logical database named repairs. The physical location of the database could be anywhere on the system.
Example 1
The following program, names.c, selects names from the personnel table.
#include <mscc.h>
#define DATABASE "repairs"
msmain ()
{
mscall (DATABASE, "select name from personnel");
}
Example 2
The program phone.c looks up a telephone number given a single employee name.
#include <mscc.h>
#define DATABASE "repairs"
#define BUFSIZE 512
msmain (argc, argv)
int argc;
char *argv[];
{
char *name, command[BUFSIZE];
name = argv[1];
sprintf (command,"
select phone from personnel dump where name =\
'%s'", name);
mscall (DATABASE, command);
}
Example 3
The program newphone.c checks that only one name is given:
#include <mscc.h>
#define DATABASE "repairs"
#define BUFSIZE 512
msmain (argc, argv)
int argc;
char *argv[];
{
char command[BUFSIZE], *name;
if (argc != 2)
{
fprintf (stderr, "Usage: phone \
employee_name\n");
msexit (1);
}
name = argv[1];
sprintf (command,
"select phone from personnel\
dump where name =\
'%s'", name);
mscall (DATABASE, command);
}
Example 4
An interactive version of the phone program, getphone.c:
#include <mscc.h>
#define DATABASE "repairs"
#define NOTHING ""
#define BUFSIZE 512
msmain()
{
char name[BUFSIZE], command[BUFSIZE];
printf ("\nFIND phone number GIVEN employee's\name.\n");
printf ("To stop, enter \".q\"\n\n");
for ( ; ; )
{
do
{
printf ("Employee name: ");
gets (name);
if (strcmp (name, ".q") == 0 )
msexit (0);
} while (strcmp (name, NOTHING) == 0 );
printf ("\n");
sprintf (command,
"select phone from\personnel dump\
where name = '%s', name);
mscall (DATABASE, command);
printf ("\n");
}
}
Example 5
The program employees.c selects the names and phone numbers of all employees in the personnel table, counts their number and prints suitable headings:
#include <mscc.h>
#define DATABASE "repairs"
msmain()
{
printf ("Current Employees\n\n");
mscall (DATABASE, "select name, phone from\personnel");
printf ("Total Number of Employees = ");
fflush (stdout);
mscall (DATABASE, "select count from personnel\dump");
printf ("\n");
}
Example 6
The C program loans.c prints the details of loans outstanding for each given employee:
#include <mscc.h>
#define DATABASE "repairs"
#define BUFSIZE 512
msmain (argc, argv)
int argc;
char *argv[];
{
char command[BUFSIZE], newcommand[BUFSIZE],*name;
int i;
if (argc < 2 )
{
print f("USAGE: %s employee's name\n", argv[0]);
exit (1);
}
for (i = 1 ; i < argc ; i++ )
{
name = argv[i];
printf ("Loans currently outstanding to\
%s:\n\n", name);
fflush (stdout);
sprintf (command,
"select date, amount from loans where \
name = '%s'", name);
mscall (DATABASE, command);
printf ("\n");
printf ("Total amount owing for %s: ", name);
fflush (stdout);
sprintf (command,
"select sum (amount) from loans dump \
where name = '%s'", name);
mscall (DATABASE, command);
printf ("\n\n");
}
name = argv[1];
sprintf (command,
"select sum (amount) from loans dump where \
name = '%s'" name);
for (i = 2 ; i < argc ; i++ )
{
name = argv[i];
sprintf (newcommand, " or name = '%s'", name);
strcat (command, newcommand);
}
printf ("\n\ntotal amount outstanding over ");
printf ("all of the above:");
fflush (stdout);
mscall (DATABASE, command);
}
This produces exactly the same results as the corresponding shell program.
Example 7
The program menu.c shows a simple menu-driven program for invoking Empress Query Language commands via the Standard C Interface. It enables you to select:
#include <mscc.h>
#define DATABASE "repairs"
#define YES '1'
#define NO '0'
#define BUFSIZE 512
#define NAMESIZE 32 /* max length of ATTR names +1 */
#define NOTHING ""
#define NUMBER "n"
#define PHONE "p"
#define CREDIT "c"
#define RECORD "r"
#define LOANS "l"
#define QUIT "q"
char name[BUFSIZE];
char clause[BUFSIZE];
msmain ()
{
char showmenu;
char command[BUFSIZE],
attribute[NAMESIZE];
char table[NAMESIZE],
query[BUFSIZE];
printmenu ();
for ( ; ; ) /* loop through repeated queries */
{
strcpy (attribute, NOTHING);
strcpy (clause, NOTHING);
strcpy (table, "personnel");
showmenu = NO;
printf ("\nCommand: ");
gets (command);
if (strcmp (command, NUMBER) == 0)
{
strcpy (attribute, "number");
findname ();
}
else
if (strcmp (command, PHONE) == 0)
{
strcpy (attribute, "phone");
findname ();
}
else
if (strcmp (command, CREDIT) == 0)
{
strcpy (attribute, "credit_limit");
findname ();
}
else
if (strcmp (command, RECORD) == 0)
{
/* do nothing yet */
}
else
if (strcmp (command, LOANS) == 0)
{
strcpy (table, "loans");
}
else
if (strcmp (command, QUIT) == 0 )
{
msexit (0); /* normal termination */
}
else
{
showmenu = YES;
briefmenu ();
}
if (showmenu == NO) /* continue with query */
{
sprintf (query, "select %s from %s %s",
attribute, table, clause);
mscall (DATABASE, query);
}
}
}
printmenu ()
{
printf ("\n\n To select:\n\n");
printf ("An employee's personnel number %s\n", NUMBER);
printf ("An employee's phone number %s\n", PHONE);
printf ("An employee's credit limit %s\n", CREDIT);
printf ("All personnel records %s\n", RECORD);
printf ("All loans outstanding %s\n", LOANS);
printf ("To leave the program %s\n\n", QUIT);
}
briefmenu ()
{
printf ("\nPersonnel number (%s), phone (%s), ",
NUMBER, PHONE);
printf ("credit limit (%s), all records (%s), \n", CREDIT,
RECORD);
printf ("all loans (%s), or quit (%s)\n",
LOANS, QUIT);
}
findname ()
{
do
{
printf ("Enter employee name: ");
gets (name);
} while (STRCMP (name, NOTHING) == 0);
sprintf (clause, "where name = '%s'", name);
}
To manipulate attribute values in more complex ways than the Query Language permits, use the routines msgvinit and msgval to obtain pointers to attribute values in a dump file produced via a Query Language command. The routine msgvline returns the current record number. msgpline returns the current line number of the dump file.
The routine msgvinit takes as its argument a pointer to an Empress dump file returned by fopen, and initializes retrievals from that file. It must always be called before values are retrieved.
The routine msgval takes no arguments. It returns an attribute value as a string, (char *)0 on end-of-file, and (char *)-1 on end-of-line (which is normally the end of a record, unless the dump file has been produced by a LIST DUMP command).
Successive calls to msgval return successive attribute values, stepping though the dump file attribute-by-attribute, record-by-record, until all values have been retrieved. Thus, to retrieve all the records from the loans table and print the amounts plus interest, for example, requires a loop which runs until msgval returns (char *)0, with a call to msgval for every attribute value expected, and a check for the end of the line when all values from one record have been found.
msgvline takes no arguments, and returns the current line number in the dump file as a LONGINTEGER. This will give a count of the number of records seen if the file was produced by a regular DUMP command, and a count of the number of attributes seen if the file was produced by a LIST DUMP command.
msgpline takes no arguments, and returns the current file record number in the the dump as a LONGINTEGER regardless of whether or not DUMP or LIST DUMP commands where used.
Example 1
The program statement.c calculates interest on loans and prints a simple monthly statement for the named employees. A useful routine, dollcvt, which turns a dollar attribute value into a float value, is included in this example.
#include <mscc.h>
#define DATABASE "repairs"
#define TEMP "msdump"
#define RATE 0.02
#define BUFSIZE 128
extern double dollcvt ();
msmain (argc, argv)
int argc;
char *argv[];
{
FILE *file;
int loan_no, counter;
char *name, *value;
char command[BUFSIZE];
double amount, interest, total, sum, limit;
for (counter = 1; counter < argc; counter++)
{
name = argv[counter];
sprintf (command, "select credit_limit from \
personnel dump into '%s' where \
name = '%s'", TEMP, name);
mscall (DATABASE, command);
file = fopen (TEMP, "r");
msgvinit (file);
value = msgval ();
fclose (file);
if (value == (char *)0)
{
printf ("\n\n***** Cannot find any");
printf ("record of employee '%s' in the database *****\n",
name);
}
else
{
limit = dollcvt (value);
sprintf (command, "select date, amount from loans dump\
into '%s' where name = '%s'",
TEMP, name);
mscall (DATABASE, command);
printf ("\n\n\n");
printf ("Monthly Statement for %s\n", name);
printf ("*********************************\n\n");
printf ("Date Amount Interest Total\n\n");
file = fopen (TEMP, "r");msgvinit (file);
value = msgval ();/* get first date */
if (value == (char *)0)
{
printf ("No loans outstanding\n\n");
printf ("Credit limit = $%-8.2f\n",limit);
continue;
}
loan_no = sum = 0;
for ( ; value != (char *)0 ; )
{
printf ("%-7s", value); /* print date */
value = msgval (); /* get amount */
printf (" $%-9s", value); /* print amount */
amount = dollcvt (value);
interest = amount * RATE;
total = amount + interest;
printf ("$%-8.2f $%-8.2f\n", interest, total);
sum = sum + total;
value = msgval (); /* check end-of-line */
if (value == (char *)-1)
{
loan_no++;
value = msgval (); /* get next date */
}
}
fclose (file);
printf ("\nNumber of loans = %d \n", loan_no);
printf ("Credit limit = $%-8.2f\n", limit);
printf ("Total Debts = $%-8.2f\n", sum);
if (sum >= limit)
{
printf ("***** Please note: \ your credit limit is");
printf ("exceeded *****\n");
}
}
}
unlink (TEMP);
}
double dollcvt (string)
char *string;
{
extern double atof ();
char c, buffer [BUFSIZE], *buf_ptr;
for (buf_ptr = buffer ;
(c = *string++) != '\0' ;
)
if (c != '$' &&
c != '*' &&
c != ' ' &&
c != ',')
*buf_ptr++ = c;
*buf_ptr = '\0';
return (atof (buffer));
}