Ending a PRO*C statement : summary & thanks

Thank you to everyone who sent suggestions for solving the problem of 'how to end a sql statement using PRO*C.'

This is a summary of responses :

1. The most popular answer was to use

   VARCHAR stmt[100] in place of char stmt[100];    if the latter were to be used it was recommended to initialise    the statement to 100 blank spaces *not* null characters.

   The form can be

         strcpy (stmt.arr, "SELECT A FROM B");
         stmt.len = strlen (stmt.arr);
         EXEC SQL PREPARE P FROM :stmt;

   or
         stmt.len = sprintf(stmt.arr, "SELECT A FROM B");
         EXEC SQL PREPARE PSELECT FROM :stmt;

2. A sensible piece of advice was to reset the size of the buffer after

   each use:

    memset (PSELECT, '/0', 100);
or memset (PSELECT, 100, '/0'); (not sure of the correct order)

    where PSELECT is the character array used in the prepare statement

          100 is the size of the buffer     and '/0' what the array should be reset to.

3. A couple of people suggested checking the command from within ORACLE.

   This is sensible advice, although from experience it will not always    point you in the right direction - the problem is not usually that the    command is wrong , but the buffer does not hold what you think it    does.

4. Reporting error messages should be obligatory :

   void <function name> ()
   {

      EXEC SQL WHENEVER SQLERROR GOTO errprt;

             code

      return;
      errprt :
      printf ("Error in <function name> %70s", sqlca.sqlerrm.sqlerrmc);
      return;

   Although in the problem highlighted, if it is a problem with the    syntax of the command rather than 'not ending properly' the error    message will be quite different!

Thanks again for all your help,

David Yarwood
MSC9342_at_UK.AC.ED.GEOVAX Received on Wed Aug 11 1993 - 12:41:38 CEST