Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: problem getting ROWID with OCIAttrGet()

Re: problem getting ROWID with OCIAttrGet()

From: Franck Gendre <fgendre_at_sinfor.fr>
Date: 1998/06/08
Message-ID: <357e9514.4955325@172.16.24.78> 








You make a mistake on call to OCIAttrGet
You must write :



ub4 ridlen;


OCIAttrGet(stmthp, OCI_HTYPE_STMT, rowId, &ridlen,
OCI_ATTR_ROWID, errhp);



The criteria for update is essential to get a valid ROWID



I give you a example that I wrote to help you to use ROWID :




Good luck



Franck



On Fri, 5 Jun 1998 16:29:27 -0600, "Heidi K. Barg" <heidi_at_stepforward.org> wrote:



>I'm trying to do an 'insert' and then a 'select for update' based on the

>rowid of the newly inserted row.  It crashes at OCIAttrGet().

>

>sqlText = "insert into test1 (a) values (empty_clob())"

>sqlText2 = "select a from test1 where rowid = ?? for update"

>OCIRowid *rowId;

>

>OCIDescriptorAlloc(envhp, &rowId, OCI_DTYPE_ROWID, 0, 0);

>

>OCIStmtPrepare(stmthp, errhp, sqlText, strlen(sqlText), OCI_NTV_SYNTAX,

>OCI_DEFAULT);


>OCIStmtExecute(svchp, stmthp, errhp, 1, 0, 0, 0, OCI_DEFAULT);

>

>OCIAttrGet(stmthp, OCI_HTYPE_STMT, &rowId, sizeof(OCIRowid *),

>OCI_ATTR_ROWID, errhp);

>

>Note:  If I don't include the OCIAttrGet and base the 'select for update' on

>other criteria it works fine.

>

>thanks!

>heidi

>

>heidi_at_stepforward.org

>

>



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





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



//#pragma comment(lib, "c:\\orant\\oci80\\lib\\msvc\\oci.lib")



void trace(OCIRowid *rowid)


{


  int c,i;


  unsigned char* pRowid = (unsigned char*)rowid;
  printf ("ROWID: ");


  for (i = 0; i < 23; i++)


    {


    c = (int) pRowid[i];


    printf("%02hX ", c);


    }


 printf("\n");


}



void main()


{


int rowFetch = 0;


sword status;


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 field1[20],field2[20];


ub4 ridlen;


text *mySql = (text *) "SELECT empno, ename FROM EMP"; //  FOR UPDATE



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



printf("OCIInitialize\n");

checkerr(p_err, OCIInitialize((ub4) OCI_THREADED/* OCI_OBJECT */,
                 (dvoid *) 0, (dvoid * (*) ()) 0,
                 (dvoid * (*) ()) 0, (void (*) ()) 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, "OUN8", 4));


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 *) field1, (sb4) 19,
                                SQLT_CHR, (dvoid *) 0,
                                (ub2 *)0,(ub2 *)0, OCI_DEFAULT));

checkerr(p_err, OCIDefineByPos(p_sql, &p_define2, p_err, 2,
                                (dvoid *) field2, (sb4) 19,
                                SQLT_CHR, (dvoid *) 0,
                                (ub2 *)0,(ub2 *)0, OCI_DEFAULT));





while (1)


  {


  /* Impression du contenu de la table EMP pour comparer en fin de programme */

  printf("Contenu table emp  A V A N T\n");
  printf("~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n");
  printf("        OCIStmtExecute\n");
  checkerr(p_err, OCIStmtExecute(p_svc, p_sql, p_err, (ub4) 1,
                                  (ub4) 0, (OCISnapshot *)
                                  NULL, (OCISnapshot *) NULL,
                                  (ub4) OCI_DEFAULT));





  status = OCI_SUCCESS;


  while ((status == OCI_SUCCESS) || (status == OCI_SUCCESS_WITH_INFO))
    {

    printf("           Employee Name ----> %s , %s\n", field1,field2);
    memset((void *) field1, (int)'\0' , (size_t) 20);
    memset((void *) field2, (int)'\0' , (size_t) 20);
    status = OCIStmtFetch(p_sql, p_err, (ub4) 1,  
                                  (ub2) OCI_FETCH_NEXT, (ub4) OCI_DEFAULT);


    }


  }



/* Deplacement sur le troisième enregistrement, pour cela on relance la requete */

printf("Deplacement sur le troisieme enregistrement\n");
printf("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n");
printf("        OCIStmtExecute\n");

checkerr(p_err, OCIDescriptorAlloc((dvoid *)p_env,
                                (dvoid **) &rowid,OCI_DTYPE_ROWID,
                                (size_t) 0, (dvoid **) 0));

checkerr(p_err, OCIAttrSet ((dvoid *)p_sql, (ub4)OCI_HTYPE_STMT,
                                (dvoid *) &rowFetch, (ub4) 0,
                                (ub4)OCI_ATTR_PREFETCH_ROWS, (OCIError *) p_err));





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



checkerr(p_err, OCIStmtPrepare(p_sql, p_err, mySql,

                                (ub4) strlen(mySql),
                                OCI_NTV_SYNTAX, OCI_DEFAULT));

checkerr(p_err, OCIStmtExecute(p_svc, p_sql, p_err, (ub4) 1,
                              (ub4) 0, (OCISnapshot *)
                              NULL, (OCISnapshot *) NULL,
                              (ub4) OCI_DESCRIBE_ONLY));

checkerr(p_err, OCIDefineByPos(p_sql, &p_define1, p_err, 1,
                                (dvoid *) field1, (sb4) 19,
                                SQLT_CHR, (dvoid *) 0,
                                (ub2 *)0,(ub2 *)0, OCI_DEFAULT));

checkerr(p_err, OCIDefineByPos(p_sql, &p_define2, p_err, 2,
                                (dvoid *) field2, (sb4) 19,
                                SQLT_CHR, (dvoid *) 0,
                                (ub2 *)0,(ub2 *)0, OCI_DEFAULT));

checkerr(p_err, OCIStmtExecute(p_svc, p_sql, p_err, (ub4) 1,
                              (ub4) 0, (OCISnapshot *)
                              NULL, (OCISnapshot *) NULL,
                              (ub4) OCI_DEFAULT));

memset((void *) field1, (int)'\0' , (size_t) 20);


memset((void *) field2, (int)'\0' , (size_t) 20);
checkerr(p_err, OCIStmtFetch(p_sql, p_err, (ub4) 1,  

                                (ub2) OCI_FETCH_NEXT, (ub4) OCI_DEFAULT));

printf("        OCIAttrGet\n");
checkerr(p_err, OCIAttrGet ((dvoid *)p_sql, OCI_HTYPE_STMT,
                                (dvoid *) rowid, (ub4 *) &ridlen,
                                OCI_ATTR_ROWID, (OCIError *) p_err));


trace(rowid);


memset((void *) field1, (int)'\0' , (size_t) 20);
memset((void *) field2, (int)'\0' , (size_t) 20);
checkerr(p_err, OCIStmtFetch(p_sql, p_err, (ub4) 1,  
                                (ub2) OCI_FETCH_NEXT, (ub4) OCI_DEFAULT));
printf("           Employee Name ----> %s , %s\n", field1,field2);

printf("        OCIAttrGet\n");
checkerr(p_err, OCIAttrGet ((dvoid *)p_sql, OCI_HTYPE_STMT,
                                (dvoid *) rowid, (ub4 *) &ridlen,
                                OCI_ATTR_ROWID, (OCIError *) p_err));


trace(rowid);


memset((void *) field1, (int)'\0' , (size_t) 20);
memset((void *) field2, (int)'\0' , (size_t) 20);
checkerr(p_err, OCIStmtFetch(p_sql, p_err, (ub4) 1,  
                                (ub2) OCI_FETCH_NEXT, (ub4) OCI_DEFAULT));

printf("        OCIAttrGet\n");
checkerr(p_err, OCIAttrGet ((dvoid *)p_sql, OCI_HTYPE_STMT,
                                (dvoid *) rowid, (ub4 *) &ridlen,
                                OCI_ATTR_ROWID, (OCIError *) p_err));


trace(rowid);


memset((void *) field1, (int)'\0' , (size_t) 20);
memset((void *) field2, (int)'\0' , (size_t) 20);
checkerr(p_err, OCIStmtFetch(p_sql, p_err, (ub4) 1,  
                                (ub2) OCI_FETCH_NEXT, (ub4) OCI_DEFAULT));

memset((void *) field1, (int)'\0' , (size_t) 20);


memset((void *) field2, (int)'\0' , (size_t) 20);
checkerr(p_err, OCIStmtFetch(p_sql, p_err, (ub4) 1,  

                                (ub2) OCI_FETCH_NEXT, (ub4) OCI_DEFAULT));





/* Allocation et recuperation du ROWID sur la ligne courante ? */
/* Mise à jour de l'enregsitrement courant */
printf("Mise a jour\n");


printf("~~~~~~~~~~~\n");


strcpy(mySql,(text *) "UPDATE EMP SET ename = '***' 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));





/* Impression du contenu de la table EMP apres modification */

printf("Contenu table emp  A P R E S\n");
printf("~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n");
printf("        OCIStmtExecute\n");
checkerr(p_err, OCIStmtExecute(p_svc, p_sql, p_err, (ub4) 1,
                                (ub4) 0, (OCISnapshot *)
                                NULL, (OCISnapshot *) NULL,
                                (ub4) OCI_DEFAULT));





status = OCI_SUCCESS;


while (status == OCI_SUCCESS)


  {

  printf("           Employee Name ----> %s , %s\n", field1,field2);
  memset((void *) field1, (int)'\0' , (size_t) 20);
  memset((void *) field2, (int)'\0' , (size_t) 20);
  status = OCIStmtFetch(p_sql, p_err, (ub4) 1,  
                                (ub2) OCI_FETCH_NEXT, (ub4) OCI_DEFAULT);


  }



/* Pas de commit afin de pouvoir relancer le test dans les memes conditions */
//checkerr(p_err, OCITransCommit(p_svc, p_err, OCI_DEFAULT));




return;


}



static void checkerr(errhp, status)


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;
                }




}
Received on Mon Jun 08 1998 - 00:00:00 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US