Problem in select for update.

From: <ugtech_at_my-deja.com>
Date: Wed, 13 Oct 1999 04:49:16 GMT
Message-ID: <7u1309$9h$1@nnrp1.deja.com>

hi,

Can anyone tell me why only alternate rows are getting updated in the following code.
I am using Rowid to update the selected row.

Please help.
Thanks a lot.
Geeta.

---

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

static dvoid checkerr (OCIError *p_err, sword status);

dvoid main()
{

   OCIEnv	*p_env;
   OCIError	*p_err;
   OCISvcCtx	*p_svc;
   OCIStmt	*p_sql;
   OCIStmt	*p_sql2 = (OCIStmt *) 0;
   OCIBind *p_Bind1 = (OCIBind *) 0;

   OCIBind *p_Bind2 = (OCIBind *) 0;
   OCIBind *p_Bind3 = (OCIBind *) 0;
   OCIDefine *p_define1 = (OCIDefine *) 0;    OCIDefine *p_define2 = (OCIDefine *) 0;    OCIRowid *rowid = (OCIRowid *) 0;
   text field2[20];
   ub4 ridlen=0;
   int i=0;
   text mySql[200];
   strcpy(mySql, "SELECT ename FROM EMP FOR UPDATE");

   memset((dvoid *) field2, (int)'\0' , (size_t) 20);

   printf("OCIInitialize\n");
   checkerr(p_err, OCIInitialize((ub4) OCI_OBJECT,

		 (dvoid *) 0, (dvoid * (*) ()) 0,
		 (dvoid * (*) ()) 0, (dvoid (*) ()) 0));

   printf("OCIEnvInit\n");
   checkerr(p_err, OCIEnvInit(&p_env, (ub4) OCI_DEFAULT,

(size_t) 0, (dvoid **)0));

   printf("OCIHandleAlloc\n");
   checkerr(p_err, OCIHandleAlloc(p_env, &p_err, OCI_HTYPE_ERROR,

(size_t) 0, (dvoid **) 0));

   printf("OCIHandleAlloc\n");
   checkerr(p_err, OCIHandleAlloc(p_env, &p_svc, OCI_HTYPE_SVCCTX,

(size_t) 0, (dvoid **) 0));

   printf("OCIHandleAlloc\n");
   checkerr(p_err, OCIHandleAlloc(p_env, &p_sql,

                                OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));

   checkerr(p_err, OCIHandleAlloc(p_env, &p_sql2,

                                OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));

   printf("OCILogon\n\n");
   checkerr(p_err, OCILogon(p_env, p_err, &p_svc, "scott", 5, "tiger", 5, "V8", 2));

   printf("\n*************************************************\n");
   printf("                  RowID Sample \n");
   printf("*************************************************\n");

   printf("	OCIStmtPrepare\n");
   printf("	   %s\n",mySql);
   checkerr(p_err, OCIStmtPrepare(p_sql, p_err, mySql,


				(ub4) strlen(mySql),


				OCI_NTV_SYNTAX, OCI_DEFAULT));

   checkerr(p_err, OCIDefineByPos(p_sql, &p_define1, p_err, 1,


				(dvoid *) field2, (sb4) 21,


				SQLT_STR, (dvoid *) 0,


				(ub2 *)0,(ub2 *)0, OCI_DEFAULT));



   printf("	OCIStmtExecute\n");
   checkerr(p_err, OCIStmtExecute(p_svc, p_sql, p_err, (ub4) 0,


				(ub4) 0, (OCISnapshot *)


			  	NULL, (OCISnapshot *) NULL,


				(ub4) OCI_DEFAULT));


   checkerr(p_err, OCIDescriptorAlloc((dvoid *)p_env,


				(dvoid **) &rowid,OCI_DTYPE_ROWID,


                (size_t) 0, (dvoid **) 0));

   while(i<6)
   {

      printf(" OCIAttrGet\n");

      checkerr(p_err, OCIStmtFetch(p_sql, p_err, 1, OCI_FETCH_NEXT, OCI_DEFAULT));

      printf("	   Employee Name ----> %s\n", field2);
      printf("	OCIDescriptor\n");
      checkerr(p_err, OCIAttrGet ((dvoid *)p_sql, OCI_HTYPE_STMT,


				(dvoid *) rowid, (ub4 *) &ridlen,


				OCI_ATTR_ROWID, (OCIError *) p_err));

      printf("	OCIAttrGet\n");
      strcpy(mySql,"UPDATE EMP SET ename = 'MyUpdate' WHERE ROWID
= :1");
      printf("	   %s\n",mySql);
      printf("	OCIStmtStmtPrepare\n");
      checkerr(p_err, OCIStmtPrepare(p_sql2, p_err, mySql,


				(ub4) strlen(mySql), OCI_NTV_SYNTAX,

OCI_DEFAULT));

      printf("	OCIBindByPos 1\n");
      checkerr(p_err, OCIBindByPos(p_sql2, &p_Bind1, p_err, 1,


				(dvoid *) &rowid, (sb4) 0,


				SQLT_RDD, 0, 0, 0, 0, 0, OCI_DEFAULT));

      printf("	OCIStmtExecute\n");
      checkerr(p_err, OCIStmtExecute(p_svc, p_sql2, p_err,


				(ub4) 1, (ub4) 0, (OCISnapshot *) NULL,


				(OCISnapshot *) NULL, (ub4)


OCI_DEFAULT));
      printf("After OCIStmtExecute\n");
      i=i+1;

   }
   checkerr(p_err, OCITransCommit(p_svc,p_err, 0));    return;
   }

   static dvoid checkerr( OCIError *errhp,sword status)    {

      text errbuf[512];
      ub4 errcode;
      switch (status)
		{
		case OCI_SUCCESS:
			break;
		case OCI_SUCCESS_WITH_INFO:
			printf("Error - OCI_SUCCESS_WITH_INFO\n");
			break;
		case OCI_NEED_DATA:
			printf("Error - OCI_NEED_DATA\n");
			break;
		case OCI_NO_DATA:
			printf("Error - OCI_NO_DATA\n");
			break;
		case OCI_ERROR:
			OCIErrorGet ((dvoid *) errhp, (ub4) 1, (text *)
NULL, &errcode,
					errbuf, (ub4) sizeof(errbuf),
(ub4) OCI_HTYPE_ERROR);
			printf("Error - %s\n", errbuf);
			break;
		case OCI_INVALID_HANDLE:
			printf("Error - OCI_INVALID_HANDLE\n");
			break;
		case OCI_STILL_EXECUTING:
			printf("Error - OCI_STILL_EXECUTE\n");
			break;
		case OCI_CONTINUE:
			printf("Error - OCI_CONTINUE\n");
			break;
		default:
			break;
		}

   }

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Oct 12 1999 - 23:49:16 CDT