PRO*C problem

From: Haris Kusumo <hkusumo_at_socs.uts.EDU.AU>
Date: 12 Aug 1993 19:12:30 +1000
Message-ID: <24d1hu$446_at_saturn.socs.uts.edu.au>

Hi, I have problem with PRO*C.
I just wondering if you guys can help me or give me some direction. As I understand ORACLE 7 support dynamic database label. This is my program listing.

---

File: fred.pc

---

EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA.H; #include "oracle_lib.h"

main ()
{

        if (! _db_connect ("l1", "scott", "tiger", "saturn", "sid1"))


        {

                printf ("error l1\n");
                return;
        }

        if (! _db_connect ("l2", "scott", "tiger", "bison", "bison7"))


        {

                printf ("error l2\n");
                return;
        }

        if (! _exec_statement ("l1", "CREATE TABLE FRED (NAME CHAR(2))"))


        {

                printf ("error l1 create table\n");
                return;
        }

        if (! _exec_statement ("l2", "CREATE TABLE FRED (NAME CHAR(2))"))


        {

                printf ("error l2 create table\n");
                return;
        }

        if (! _exec_statement ("l1", "INSERT INTO FRED VALUES ('hi')"))


        {

                printf ("error l1 insert\n");
                return;
        }

        printf ("finish!\n");

}

---

File: oracle_lib.pc

---

EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA.H; #include "oracle_lib.h"

int _db_connect (label, login, password, server, db)

char*   label;
char*   login;
char*   password;
char*   server;
char*   db;
{
        EXEC SQL BEGIN DECLARE SECTION;
                VARCHAR _label          [128];
                VARCHAR _login          [31];
                VARCHAR _password       [31];
                VARCHAR _host           [128];
        EXEC SQL END DECLARE SECTION;

        strcpy (_login.arr, login);
        _login.len = strlen (_login.arr);
        strcpy (_password.arr, password);
        _password.len = strlen (_password.arr);
        strcpy (_host.arr, "t:");
        strcat (_host.arr, server);
        strcat (_host.arr, ":");
        strcat (_host.arr, db);
        _host.len = strlen (_host.arr);

        EXEC SQL WHENEVER SQLERROR GOTO errrpt;

        if (label == (char *) NULL)


        {

                EXEC SQL CONNECT :_login IDENTIFIED BY :_password
                        USING :_host;
        }
        else


        {

                strcpy (_label.arr, label);
                _label.len = strlen (_label.arr);

                EXEC SQL CONNECT :_login IDENTIFIED BY :_password
                        AT :_label USING :_host;
        }

        return (1);

errrpt:
        return (0);

}

int _exec_statement(char* label, char* statement) {

        EXEC SQL BEGIN DECLARE SECTION;
                VARCHAR _statement      [301];
                VARCHAR _label          [128];
        EXEC SQL END DECLARE SECTION;

        strcpy (_statement.arr, statement);
        _statement.len = strlen (_statement.arr);

        EXEC SQL WHENEVER SQLERROR GOTO errrpt;

        if (label != (char *) NULL)


        {

                strcpy (_label.arr, label);
                _label.len = strlen (_label.arr);
                EXEC SQL AT :_label DECLARE sql_stmt STATEMENT;
                EXEC SQL PREPARE sql_stmt FROM :_statement;
                EXEC SQL EXECUTE sql_stmt;
        }
        else


        {

                EXEC SQL DECLARE sql_stmt1 STATEMENT;
                EXEC SQL PREPARE sql_stmt1 FROM :_statement;
                EXEC SQL EXECUTE sql_stmt1;
        }
        return (1);

errrpt:
        EXEC SQL ROLLBACK WORK;
        return (0);

}

---

The program works fine until it reach this statement:

        if (! _exec_statement ("l2", "CREATE TABLE FRED (NAME CHAR(2))"))


        {

                printf ("error l2 create table\n");
                return;
        }

it seems that it doesn't know where the l2 is pointing to, when it tries to create table fred.

I'll be appreciated if you can send me e-mail instead of putting the solution on net-news, because I don't read net-news every days.

my e-mail address: hkusumo_at_socs.uts.edu.au

Thanks in advance. Received on Thu Aug 12 1993 - 11:12:30 CEST