Problem inserting data into table

From: Hemant Shah <shah_at_typhoon.xnet.com>
Date: Wed, 30 Jul 2003 20:36:47 +0000 (UTC)
Message-ID: <bg9a8u$k5c$1_at_flood.xnet.com>

Folks,

  I just started working on Oracle. I am learning and porting our applicaiton   from DB2 to Oracle. I have worked for several years with DB2.

  I wrote a simple program that inserts data in to a test table I created.   My application and test program are written in C using "EXEC SQL" calls.   When I try to insert data I get ORA-00913 error. I have checked my code and   the number of parameters matches the columns in the table. I am running   the test program on Sun running Solaris 8 and Oracle 9.2.

  $ uname -a
  SunOS uplyapp06 5.8 Generic_108528-19 sun4u sparc SUNW,Ultra-Enterprise

  Here is how my table is defined:

  CREATE TABLE LIDPOWN.MYTABLE

      (EMPNO  INTEGER NOT NULL,
       LNAME VARCHAR2 (35) NOT NULL, 
       REC   VARCHAR2(1024) NOT NULL)

   TABLESPACE LIDP_DATA01
   PCTFREE 10 PCTUSED 70
   INITRANS 1 MAXTRANS 255
   LOGGING
;

  Here is my sample program:

/********************************************************************

* Sample program

********************************************************************/

#include <stdio.h>

#include <stdlib.h>
#include <string.h>

void ConnectToDatabase(void);
void AddRecord(void);
void RollBack(void);
void Commit(void);
void DisconnectFromDatabase(void);
int CheckForSQLError(void);

EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION;    int EmpNo;

   static struct Record
{

      short RecLength;
      char  Record[1024];

   } Record;

   static struct LastName
{

      short LNameLen;
      char  LName[35];

   } LastName;

   char *UserIdPasswd = "user/passwd";

EXEC SQL END DECLARE SECTION; void Commit(void)
{

   EXEC SQL COMMIT WORK;
   CheckForSQLError();
   atexit(RollBack);
}

void RollBack(void)
{

   EXEC SQL ROLLBACK;
   CheckForSQLError();
}

void AddRecord(void)
{

   printf("Enter EmpNo: ");
   fscanf(stdin, "%d", &EmpNo);
   getchar();
   printf("Enter LName: ");
   fgets(LastName.LName, 35, stdin);
   LastName.LName[strlen(LastName.LName)-1] = 0;    LastName.LNameLen = strlen(LastName.LName);    printf("Enter Record: ");
   fgets(Record.Record, 1024, stdin);
   Record.Record[strlen(Record.Record)-1] = 0;    Record.RecLength = strlen(Record.Record);

   EXEC SQL INSERT INTO lidpown.mytable
   (empno,
   lname,
   rec)

      VALUES
   (:EmpNo,
   :LastName,
   :Record);

   CheckForSQLError();
}

void ConnectToDatabase(void)
{

   printf("**** Connecting to the Server. ****\n");    EXEC SQL CONNECT :UserIdPasswd;
   CheckForSQLError();
}

int CheckForSQLError(void)
{

   char ErrBuf[2048];
   int MsgLength;
   int BufLen = 2048;

   if (SQLCODE == 0)
{

      return(SQLCODE);
   }
   sqlglm(ErrBuf, &BufLen, &MsgLength);

   printf("%s\n", ErrBuf);

   return(SQLCODE);
}

void DisconnectFromDatabase(void)
{

   printf("**** Disconnecting from database ****\n"); }

int main(void)
{

   ConnectToDatabase();
   AddRecord();
   DisconnectFromDatabase();
}

I compile the program as follows:

$ proc PREFETCH=10 DEF_SQLCODE=YES MODE=ANSI CODE=ANSI_C AUTO_CONNECT=YES insert.pc

$ cc -g -o insert insert.c -L $ORACLE_HOME/lib -l clntsh -lposix4 -lm

When I execute the program I get SQLCODE -913.

I am able to insert data via sqlplus.

Similarly when I try to read data from the table using cursor I get ORA-01007: variable not in select list error.

I declare cursor as follows:

EXEC SQL BEGIN DECLARE SECTION;    int EmpNo;
   int MyEmpNo;

   static struct Record
{

      short RecLength;
      char  Record[1024];

   } Record;

   char *UserIdPasswd = "user/passwd";

EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE EmpNoNext CURSOR FOR

         SELECT empno, rec FROM lidpown.mytable WHERE empno >= :MyEmpNo
         ORDER BY empno ASC;

EXEC SQL OPEN EmpNoNext;

EXEC SQL FETCH EmpNoNext

         INTO :EmpNo, :Record;

EXEC SQL CLOSE EmpNoNext;

What am I doing wrong?

Thanks in advance.

-

Hemant Shah                           /"\  ASCII ribbon campaign
E-mail: NoJunkMailshah_at_xnet.com       \ /  --------------------- 
                                       X     against HTML mail
TO REPLY, REMOVE NoJunkMail           / \      and postings      
FROM MY E-MAIL ADDRESS.           
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind,                Above opinions are mine only.
it's backed up on tape somewhere.      Others can have their own.

Received on Wed Jul 30 2003 - 22:36:47 CEST