Problems creating more than one table in ESQL program

[Claus-Thomas Buhl]

I am hereby reposting what I submitted to this list 3 weeks ago.

The appended ESQL program creates 2 tables X and Y.
During creation of the second table the following error occurs:

Duplicate table name:Y(22) Errorcode = -6000, ISAM = 0

This is strange since the table Y does not exist.

Furthermore, dropping multiple tables or creating/dropping
multiple synonyms also doesn't work.

We are using SAP DB 7.3.00.24 on Debian Linux 3.0 (woody).

Any glues?

Regards,

CTB
-- 
_______
\o/|\o/   Claus-Thomas Buhl
   |      Diplom-Informatiker
\_____/   mailto:[EMAIL PROTECTED]

H.E.I. GmbH | Wimpfener Strasse 23 | D-68259 Mannheim
Fon: +49-(0)621-795141 | Fax: +49-(0)621-795161 | mailto:[EMAIL PROTECTED]
http://www.h-e-i.de && http://www.hei.biz && http://www.radpage.com
/* copyright (c) 2002 H.E.I. GmbH Mannheim */

#include <stdio.h>
#include <string.h>
#include <ctype.h>
#include <assert.h>

EXEC SQL BEGIN DECLARE SECTION;

EXEC SQL END DECLARE SECTION;

#define HANDLE_ERROR(s) if (Error()) printf("%s: %s\n",s, ErrorMsg())

int
Error (void)
{
   return (sqlca.sqlcode!=0 ? 1 : 0);
}

#define RESET_ERROR sqlca.sqlcode=0

char *
ErrorMsg (void)
{
    char buffer[1000];

    if (sqlca.sqlcode!=0) {
        sprintf (buffer,"%s(%d) Errorcode = %ld, ISAM = %ld",
        sqlca.sqlerrmc,sqlca.sqlerrml,
        sqlca.sqlcode,sqlca.sqlerrd[1]);
        return (strdup(buffer));
    }
    assert(0);
}

static int connected=0;

int
Connect (char *Host,
         char *Instance,
         char *User,
         char *Passwd)
{
  EXEC SQL BEGIN DECLARE SECTION;
        char *username;
        char *password;
        char *host;
        char *dbn;
  EXEC SQL END DECLARE SECTION;

  RESET_ERROR;

  username = (char *)User; password = (char *)Passwd; host=(char *)Host;
  dbn = (char *) Instance;
  if (host==NULL || host[0]==0) host="localhost";

  EXEC SQL SET SERVERDB :dbn ON :host;
  HANDLE_ERROR("set serverdb");
  if (sqlca.sqlcode!=0) return 1;

  EXEC SQL CONNECT :username IDENTIFIED BY :password
  ISOLATION LEVEL 30;
  HANDLE_ERROR("connect");
  if (sqlca.sqlcode!=0) return 1;

  connected=1;

  return 0;
}

void
Commit (void)
{
  RESET_ERROR;
  EXEC SQL COMMIT;
}

void
RollBack (void)
{
  RESET_ERROR;
  EXEC SQL ROLLBACK;
}


int
Update  (char * sqlstmt)
{
  EXEC SQL BEGIN DECLARE SECTION;
     char * stmt;
  EXEC SQL END DECLARE SECTION;
    char * c;

    RESET_ERROR;

    stmt = sqlstmt;
    EXEC SQL PREPARE stmtupd FROM  :stmt;
    HANDLE_ERROR("prepare");
    if (sqlca.sqlcode!=0){return 1;}

    EXEC SQL DESCRIBE stmtupd;
    HANDLE_ERROR("describe");
    if (sqlca.sqlcode!=0){return 1;}


    EXEC SQL EXECUTE stmtupd USING DESCRIPTOR;
    HANDLE_ERROR("execute");
    if (sqlca.sqlcode!=0 && sqlca.sqlcode!=100) {
      return 1;
    }
    return 0;
}

int
Disconnect  (void)
{
  RESET_ERROR;

  if (!connected) {return 1;}

  EXEC SQL commit work release;
  HANDLE_ERROR("disconnect");
  if (sqlca.sqlcode!=0){return 1;}
  connected=0;

  return 0;
}

int main (int argc, char *argv[])
{
  if (argc!=5) {
    fprintf(stderr, "usage: %s Host Instance User Password\n", argv[0]);
    exit(1);
  }

  if (Connect(argv[1],argv[2],argv[3],argv[4])) { exit(1);}
  if (Update("create table X (f integer)")) { RollBack(); goto disconn;}
  if (Update("create table Y (f integer)")) { RollBack(); goto disconn;}
  Commit();

disconn:
  exit (Disconnect());
}

# copyright (c) 2002 H.E.I. GmbH Mannheim

SAPDB_HOME      = /usr/sapdb/depend
SAPDB_INSTANCE  = PRODDB
SAPDB_HOST      = localhost
SAPDB_LIBS      = -lm -L$(SAPDB_HOME)/lib -lpcr -lsqlca
CPC=cpc

CFLAGS=-g

testcr2:        testcr2.o
        $(CC) -o testcr2 testcr2.o $(SAPDB_LIBS)

testcr2.o:      testcr2.c
        $(CC) $(CFLAGS) -c -I$(SAPDB_HOME)/incl $<

testcr2.c: testcr2.cpc
        $(CPC) -c -d $(SAPDB_INSTANCE) -n $(SAPDB_HOST) -u dba,dba $*

clean:
        rm -f testcr2 *.o testcr2.c