HELP: Dynamic SQL/Pro*C++ core dumps

HELP: Dynamic SQL/Pro*C++ core dumps

Post by Ken Douga » Tue, 09 Feb 1999 04:00:00



I am new to Oracle Dynamic SQL and I am having trouble getting a
small test program up and running.  (I'm using Oracle 7.3.3 on Solaris
2.5.1 and it compiles and links cleanly after running cleanly with
Pro*C 2.2.3.0.0.)

PROBLEM:  The attached program core dumps while opening the cursor
(i.e. line "EXEC SQL OPEN curs1 USING DESCRIPTOR sqlda_bv;").  It
fails on the line "sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);"
which is part of the OPEN cursor block inserted by the Pro*C++
precompiler.  The debugger gives the following message:

program terminated by signal SEGV (no mapping at the fault address)
dbx: warning: Can't find function symbol for 'main' :
/home/kdougan/oracle2/libs/catalog/dynam_ex1:main.cc stab #940
main:F(0,3);(0,3);(0,24)=*(33,17)
dbx: warning: undefined type number (0,24) at
/home/kdougan/oracle2/libs/catalog/dynam_ex1:main.cc stab #943
argv:p(0,24),
assuming type `int {assumed}'
Current function is main
  633     sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);

All addresses of the above variables are valid and appear to contain
reasonable data. (I've attached the values for each argument.)

If anyone can shed some light on this I would be extremely grateful!!

Thanks.
Ken

=============

(dbx) print sqlctx
sqlctx = 307331U
(dbx) print sqlstm
sqlstm = {
    sqlvsn = 8U
    arrsiz = 3U
    iters  = 1U
    offset = 60U
    selerr = 61308U
    sqlety = 0
    unused = 0
    cud    = 0x930c0
    sqlest = 0x95360 ""
    stmt   = 0x931fd ""
    sqphsv = 0xeffff11c
    sqphsl = 0xeffff128
    sqpind = 0xeffff134
    sqparm = 0xeffff140
    sqparc = 0xeffff14c
    sqhstv = (0xc1870 "", 0xef7eb8e8 "", 0xef7eb8e8 "")
    sqhstl = (0, 26U, 4016510332U)
    sqindv = ((nil), 0xef7ec950, (nil))
    sqharm = (0, 0, 4016510072U)
    sqharc = ((nil), (nil), 0x4994866)

}

(dbx) print sqlfpn
sqlfpn = {
    fillen = 12U
    filnam = "dynam_ex1.pc"

}

CC -c -o ./sparc-solaris25/dynam_ex1.o -g
-I/home/kumayri/sw30/swdev/include
-I/home/burma3/Ora701/app/oracle/product/7.3.3/precomp/public -I. -I./..
-I./../../include -ptrsparc-solaris25 -I/home/rpe/include
-I/home/rpe/support_libs/temp_stl/ospace/std
-I/home/rpe/support_libs/temp_stl -DFUNCPROTO=3 -D_REENTRANT
-D_XOPEN_SOURCE -g -DDEBUG dynam_ex1.cc

CC -o ./sparc-solaris25/dynam_ex1 ./sparc-solaris25/dynam_ex1.o
./sparc-solaris25/Extent.o ./sparc-solaris25/Catalog.o
./sparc-solaris25/DatumRec.o ./sparc-solaris25/main.o
-L./sparc-solaris25 -lappframe -lpreference -llicense -lmessage -lfile
-lutil -lport -lpscmath -L/home/kumayri/sw30/swdev/lib -qoption ld
-zmuldefs -lsdl -lowcomm -lowenv /opt/SUNWspro/SC4.0/lib/libF77.a
/opt/SUNWspro/SC4.0/lib/libV77.a /opt/SUNWspro/SC4.0/lib/libM77.a
-L/home/apps/solaris25/flexlm/v5.12/sun4_u5 -llmgr -llmgr_as -llmgr_pic
-llmgr_s -llmgrd -llmutil -lintl -lsocket -lnsl
-L/home/burma3/Ora701/app/oracle/product/7.3.3/lib -lclntsh -lxa -lsql
-lsqlnet -lncr -lsqlnet -lclient -lcommon -lgeneric -lsqlnet -lncr
-lsqlnet -lclient -lcommon -lgeneric  -lepc -lnlsrtl3 -lc3v6 -lcore3
-lnlsrtl3 -lcore3 -lnlsrtl3 -L../../support_libs/temp_stl/lib -lospace
`cat /home/burma3/Ora701/app/oracle/product/7.3.3/rdbms/lib/sysliblist`
-ptrsparc-solaris25 -L./sparc-solaris25 -L./../../lib/sparc-solaris25
-L/home/rpe/lib/sparc-solaris25  -lstdc++ -lsunmath -lgen -lw -lm  

=============

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

extern "C" {
EXEC SQL INCLUDE sqlca;
EXEC SQL INCLUDE sqlda;
EXEC SQL INCLUDE sqlcpr;

extern void sqlclu( struct SQLDA* );
extern SQLDA *sqlald(int, unsigned int, unsigned int);
extern void sqlnul( unsigned short*, unsigned short*, int* );
extern void sqlprc( unsigned long*, int*, int*);

};

int main( int argc, char** argv )
{
  char vtest[50];
  int _type;
  int v_int;
  long v_long;
  float v_float;
  double v_double;
  int v_bv_int = 1;

  int i;                    /* counter variable */
  int nullok;               /* holder variable for sqlnul() return */
  int prec;                 /* holder variable for sqlprec() */
  int scale;                /* holder variable for sqlprec() */

  SQLDA* sqlda_bv;          /* declare sqlda for bind variables */
  SQLDA* sqlda_sli;         /* declare sqlda for select list items */

  EXEC SQL BEGIN DECLARE SECTION;
  VARCHAR username[21];
  VARCHAR password[21];
  EXEC SQL END DECLARE SECTION;

  strcpy((char*)username.arr, "ken");
  username.len = strlen((const char*)username.arr);
  strcpy((char*)password.arr, "ken");
  password.len = strlen((const char*)password.arr);

  EXEC SQL CONNECT :username IDENTIFIED BY :password;

  /* sqlald(max # of SLI items, max SLI name length, max BV name length)
*/
  /* NB: When allocating for a select decriptor always set param 3 to
'0' */
  sqlda_bv = sqlald(3,10,10);
  sqlda_sli = sqlald(4,10,0);

  /* set max # of BVs */
  sqlda_bv->N=3;
  /* set max # of SLIs */
  sqlda_sli->N=4;

  EXEC SQL PREPARE stmt1 FROM "select i, l, f, d from foo where i =
:id";

  EXEC SQL DECLARE curs1 CURSOR FOR stmt1;

  EXEC SQL DESCRIBE BIND VARIABLES FOR stmt1 INTO sqlda_bv;

  /*  Allocate storage for BVs */
  sqlda_bv->N=sqlda_bv->F;   /*  reset N to the value in F */

  for(i=1;i<sqlda_bv->F+1;i++) {
      /*  Setup the I (Indicator Variable) value */
      sqlda_bv->I[i-1]=(short *)malloc(sizeof(short *));  
      *sqlda_bv->I[i-1]=0;     /*  or set to -1 if NULL value    */

      /*  Setup the T (Datatype) value */
      /* sqlda_bv->T[i-1]=1; */
      sqlda_bv->T[i-1]=3; /*  integer */

      /*  Setup the L (Length) value */
      /* sqlda_bv->L[i-1]=strlen((const char*)v_bv_val); */
      sqlda_bv->L[i-1]= sizeof(int);

      /*  Setup the V (Value/Address) value */
      /* sqlda_bv->V[i-1]=v_bv_val; */
      sqlda_bv->V[i-1]=(char*)v_bv_int;
  }

  EXEC SQL OPEN curs1 USING DESCRIPTOR sqlda_bv;

  EXEC SQL DESCRIBE SELECT LIST FOR stmt1 INTO sqlda_sli;

  /* printf("\nHere's the value:\t%i",sqlca.sqlerrd[2]); */
  /* exit(0); */

  /*  Allocate storage for SLIs */
  sqlda_sli->N=sqlda_sli->F;  /*  reset N to the value in F */

  for(i=1;i<sqlda_sli->F+1;i++)
    {
      /*  Reset the null bit value */
      sqlnul((unsigned short*)&sqlda_sli->T[i-1],
            (unsigned short*)&sqlda_sli->T[i-1], &nullok);

      /*  Setup the I (Indicator Variable) value */
      sqlda_sli->I[i-1]=(short *)malloc(sizeof(short *));  
      if (i == 1) _type = 3;  /*  INTEGER */
      if (i == 2) _type = 8;  /*  LONG */
      if (i == 3) _type = 4;  /*  FLOAT */
      if (i == 4) _type = 2;  /*  NUMBER */
      /*  Setup the L (Length) value and the T (Datatype) value */
      switch( _type )
        {
          /*  VARCHAR2 */
        case  1: sqlda_sli->T[i-1] = 1;
          break;

          /*  NUMBER -- FLOAT into double */
        case  2: sqlprc((unsigned long*)&sqlda_sli->L[i-1],&prec,&scale);
          /* sqlda_sli->L[i-1] = sizeof(int); */
          sqlda_sli->L[i-1] = sizeof(double);
          sqlda_sli->T[i-1] = 4;
          sqlda_sli->V[i-1]=(char*) &v_double;
          break;

          /*  INTEGER */
        case  3:
          /* sqlda_sli->L[i-1] = sizeof(int); */
          sqlda_sli->L[i-1] = sizeof(int);
          sqlda_sli->T[i-1] = 3;
          sqlda_sli->V[i-1]=(char*) &v_int;
          break;

          /*  FLOAT (this is never the case since 4 never occurs */
        case  4: sqlprc((unsigned long*)&sqlda_sli->L[i-1],&prec,&scale);
          sqlda_sli->L[i-1] = sizeof(float);
          sqlda_sli->T[i-1] = 4;
          /*  Store to a variable */
          sqlda_sli->V[i-1]=(char*) &v_float;
          /*  Store to a sqlda */
          /*  sqlda_sli->V[i-1]=(char *)malloc((size_t)sqlda_sli->L[i-1]); */
          break;

          /*  PACKED DECIMAL */
        case  7: break;

          /*  LONG */
        case  8: sqlprc((unsigned long*)&sqlda_sli->L[i-1],&prec,&scale);
          /* sqlda_sli->L[i-1] = sizeof(int); */
          sqlda_sli->L[i-1] = sizeof(long);
          sqlda_sli->T[i-1] = 8;
          sqlda_sli->V[i-1]=(char*) &v_long;
          break;

          /*  LONG RAW */
        case 24: sqlda_sli->L[i-1] = 240;
          break;
        }

      /*  Setup the V (Value/Address) value (check for numbers!!) */
      if (sqlda_sli->T[i-1]!=2 && sqlda_sli->T[i-1]!=4)
        sqlda_sli->V[i-1]=(char *)malloc((size_t)sqlda_sli->L[i-1]);
    }  

  EXEC SQL FETCH curs1 USING DESCRIPTOR sqlda_sli;  

  v_int = *((int*)(sqlda_sli->V[0]));
  printf( "sli int: %i",  *((int*)(sqlda_sli->V[0])) );
  printf( "  v_int: %i",  v_int );
  v_long = *((long*)(sqlda_sli->V[1]));
  printf( "sli long: %l",  *((long*)(sqlda_sli->V[1])) );
  printf( "  v_long: %l",  v_long );
  v_float = *((float*)(sqlda_sli->V[2]));
  printf( "sli float: %l",  *((float*)(sqlda_sli->V[2])) );
  printf( "  v_float: %l",  v_float );
  v_double = *((double*)(sqlda_sli->V[3]));
  printf( "sli double: %f",  *((double*)(sqlda_sli->V[3])) );
  printf( "  v_double: %f",  v_double );

  /*  Deallocate storage for SLIs, BVs, and SQLDAs */

  /*  Handle SLI deallocations */
  for(i=1;i<sqlda_sli->F+1;i++)
    {
      free(sqlda_sli->V[i-1]);
      free(sqlda_sli->I[i-1]);
    }

  /*  Handle BV deallocations */
  for(i=1;i<sqlda_bv->F+1;i++)
    {
      free(sqlda_bv->V[i-1]);
      free(sqlda_bv->I[i-1]);
    }

  /*  Handle sqlda deallocations */
  sqlclu(sqlda_bv);
  sqlclu(sqlda_sli);

  EXEC SQL CLOSE curs1;

  return 0;

}

 
 
 

HELP: Dynamic SQL/Pro*C++ core dumps

Post by Thomas Ky » Tue, 09 Feb 1999 04:00:00



(if that email address didn't require changing)


>I am new to Oracle Dynamic SQL and I am having trouble getting a
>small test program up and running.  (I'm using Oracle 7.3.3 on Solaris
>2.5.1 and it compiles and links cleanly after running cleanly with
>Pro*C 2.2.3.0.0.)

>PROBLEM:  The attached program core dumps while opening the cursor
>(i.e. line "EXEC SQL OPEN curs1 USING DESCRIPTOR sqlda_bv;").  It
>fails on the line "sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);"
>which is part of the OPEN cursor block inserted by the Pro*C++
>precompiler.  The de* gives the following message:

it is the line that reads:

      sqlda_bv->V[i-1]=(char*)v_bv_int;

V[] is an array of pointers to data.  You are setting the first pointer to the
number 1, not to the address of a variable that contains one.  Change it to:

      sqlda_bv->V[i-1]=(char*)&v_bv_int;
                              ^^

and it won't seg fault (it may still not work, haven't looked any further but
you are missing the & for sure)

Thomas Kyte

Oracle Service Industries
Reston, VA   USA

--
http://www.veryComputer.com/;  -- downloadable utilities

----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you,
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.

 
 
 

HELP: Dynamic SQL/Pro*C++ core dumps

Post by Ken Douga » Tue, 09 Feb 1999 04:00:00


By the way, I have the test program running and I thought you might
be interested in the working source.  After you run it though you
can see that a double inserted as 1.1111111111e111 is NOT returned
as inserted.  It actually returns
1111111111111111419130920033087031241842622669656883858001193965689775666308169623874335331159460917210068811776.000000
when it should be
1111111111111111000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000.000000.  
That is,
"if (1.111111111111111e111 == v_double )" is FALSE.

Any suggestions?
Thanks.
Ken

SQL> desc foo;
 Name                            Null?    Type
 ------------------------------- -------- ----
 I                                        NUMBER(4)
 L                                        NUMBER(12)
 F                                        NUMBER
 D                                        NUMBER

=====================================

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

#include <iostream.h>

extern "C" {
EXEC SQL INCLUDE sqlca;
EXEC SQL INCLUDE sqlda;
EXEC SQL INCLUDE sqlcpr;

/* The ORACA=YES option must be specified to enable use of the ORACA */
EXEC ORACLE OPTION (ORACA=YES);

void sql_error(char *msg);
extern void sqlclu( struct SQLDA* );
extern SQLDA *sqlald(int, unsigned int, unsigned int);
extern void sqlnul( unsigned short*, unsigned short*, int* );
extern void sqlprc( unsigned long*, int*, int*);

};

int main( int argc, char** argv ) {
  char vtest[50];
  int _type;
  int v_int;
  long v_long;
  float v_float;
  double v_double;
  int v_bv_int = 1;

  int i;                    /* counter variable */
  int nullok;               /* holder variable for sqlnul() return */
  int prec;                 /* holder variable for sqlprec() */
  int scale;                /* holder variable for sqlprec() */

  SQLDA* sqlda_bv;          /* declare sqlda for bind variables */
  SQLDA* sqlda_sli;         /* declare sqlda for select list items */

  EXEC SQL BEGIN DECLARE SECTION;
  VARCHAR username[21];
  VARCHAR password[21];
  EXEC SQL END DECLARE SECTION;

  strcpy((char*)username.arr, "sdb3");
  username.len = strlen((const char*)username.arr);
  strcpy((char*)password.arr, "sdb3");
  password.len = strlen((const char*)password.arr);

  EXEC SQL WHENEVER SQLERROR DO sql_error("Oracle error");

  EXEC SQL CONNECT :username IDENTIFIED BY :password;

  /* sqlald(max # of SLI items, max SLI name length, max BV name length)
*/
  /* NB: When allocating for a select decriptor always set param 3 to
'0' */
  sqlda_bv = sqlald(3,10,10);
  sqlda_sli = sqlald(4,10,0);

  /* set max # of BVs */
  sqlda_bv->N=3;
  /* set max # of SLIs */
  sqlda_sli->N=4;

  EXEC SQL PREPARE stmt1 FROM "select i, l, f, d from foo where i =
:id";

  EXEC SQL DECLARE curs1 CURSOR FOR stmt1;

  EXEC SQL DESCRIBE BIND VARIABLES FOR stmt1 INTO sqlda_bv;

  /*  Allocate storage for BVs */
  sqlda_bv->N=sqlda_bv->F;   /*  reset N to the value in F */

  for(i=1;i<sqlda_bv->F+1;i++) {
      /*  Setup the I (Indicator Variable) value */
      sqlda_bv->I[i-1]=(short *)malloc(sizeof(short *));  
      *sqlda_bv->I[i-1]=0;     /*  or set to -1 if NULL value    */

      /*  Setup the T (Datatype) value */
      /* sqlda_bv->T[i-1]=1; */
      sqlda_bv->T[i-1]=3; /*  integer */

      /*  Setup the L (Length) value */
      /* sqlda_bv->L[i-1]=strlen((const char*)v_bv_val); */
      sqlda_bv->L[i-1]= sizeof(int);

      /*  Setup the V (Value/Address) value */
      /* sqlda_bv->V[i-1]=v_bv_val; */
      sqlda_bv->V[i-1]=(char*)&v_bv_int;
  }

  EXEC SQL OPEN curs1 USING DESCRIPTOR sqlda_bv;

  EXEC SQL DESCRIBE SELECT LIST FOR stmt1 INTO sqlda_sli;

  /* printf("\nHere's the value:\t%i",sqlca.sqlerrd[2]); */
  /* exit(0); */

  /*  Allocate storage for SLIs */
  sqlda_sli->N=sqlda_sli->F;  /*  reset N to the value in F */

  for(i=1;i<sqlda_sli->F+1;i++)
    {
      /*  Reset the null bit value */
      sqlnul((unsigned short*)&sqlda_sli->T[i-1],
            (unsigned short*)&sqlda_sli->T[i-1], &nullok);

      /*  Setup the I (Indicator Variable) value */
      sqlda_sli->I[i-1]=(short *)malloc(sizeof(short *));  
      if (i == 1) _type = 3;  /*  INTEGER */
      if (i == 2) _type = 8;  /*  LONG */
      if (i == 3) _type = 4;  /*  FLOAT */
      if (i == 4) _type = 2;  /*  NUMBER -- but handle like FLOAT */
      /*  Setup the L (Length) value and the T (Datatype) value */
      switch( _type )
        {
          /*  VARCHAR2 */
        case  1: sqlda_sli->T[i-1] = 1;
          break;

          /*  NUMBER -- FLOAT into double */
        case  2: sqlprc((unsigned long*)&sqlda_sli->L[i-1],&prec,&scale);
          /* sqlda_sli->L[i-1] = sizeof(int); */
          sqlda_sli->L[i-1] = sizeof(double);
          sqlda_sli->T[i-1] = 4;
          sqlda_sli->V[i-1]=(char*) &v_double;
          break;

          /*  INTEGER */
        case  3:
          /* sqlda_sli->L[i-1] = sizeof(int); */
          sqlda_sli->L[i-1] = sizeof(int);
          sqlda_sli->T[i-1] = 3;
          sqlda_sli->V[i-1]=(char*) &v_int;
          break;

          /*  FLOAT (this is never the case since 4 never occurs */
        case  4: sqlprc((unsigned long*)&sqlda_sli->L[i-1],&prec,&scale);
          sqlda_sli->L[i-1] = sizeof(float);
          sqlda_sli->T[i-1] = 4;
          /*  Store to a variable */
          sqlda_sli->V[i-1]=(char*) &v_float;
          /*  Store to a sqlda */
          /*  sqlda_sli->V[i-1]=(char *)malloc((size_t)sqlda_sli->L[i-1]); */
          break;

          /*  PACKED DECIMAL */
        case  7: break;

          /*  LONG */
        case  8: sqlprc((unsigned long*)&sqlda_sli->L[i-1],&prec,&scale);
          /* sqlda_sli->L[i-1] = sizeof(int); */
          sqlda_sli->L[i-1] = sizeof(long);
          /* internally to Oracle a number has a defined precision,
             therefore, NUMBER(p) is a long if p is greater than the
             precision of MAXINT */
          sqlda_sli->T[i-1] = 3;
          sqlda_sli->V[i-1]=(char*) &v_long;
          v_long = 0l;
          break;

          /*  LONG RAW */
        case 24: sqlda_sli->L[i-1] = 240;
          break;
        }

      /*  Setup the V (Value/Address) value (check for numbers!!) */
      if (sqlda_sli->T[i-1]!=2 && sqlda_sli->T[i-1]!=4)
        sqlda_sli->V[i-1]=(char *)malloc((size_t)sqlda_sli->L[i-1]);
    }  

  EXEC SQL FETCH curs1 USING DESCRIPTOR sqlda_sli;  

  printf("insert into foo values (1, 1111111, 1.111e1,
1.111111111111111e111);\n");
  v_int = *((int*)(sqlda_sli->V[0]));
  printf( "sli int: %i \n",  *((int*)(sqlda_sli->V[0])) );
  printf( "  v_int: %i \n",  v_int );
  v_long = *((long*)(sqlda_sli->V[1]));
  printf( "sli long: %d \n",  *((long*)(sqlda_sli->V[1])) );
  printf( "  v_long: %d \n",  v_long );
  v_float = *((float*)(sqlda_sli->V[2]));
  printf( "sli float: %f \n",  *((float*)(sqlda_sli->V[2])) );
  printf( "  v_float: %f \n",  v_float );
  v_double = *((double*)(sqlda_sli->V[3]));
  printf( "sli double: %e \n",  *((double*)(sqlda_sli->V[3])) );
  printf( "  v_double: %e \n",  v_double );
  printf( "sli double: %f \n",  *((double*)(sqlda_sli->V[3])) );
  printf( "  v_double: %f \n",  v_double );
  if (1.111111111111111e111 == v_double ) {
     printf("1.111111111111111e111 == v_double\n");
  } else {
     printf("WARNING:  1.111111111111111e111 != v_double\n");
  }

  /*  Deallocate storage for SLIs, BVs, and SQLDAs */

  /*  Handle SLI deallocations */
  for(i=1;i<sqlda_sli->F+1;i++)
    {
      free(sqlda_sli->V[i-1]);
      free(sqlda_sli->I[i-1]);
    }

  /*  Handle BV deallocations */
  for(i=1;i<sqlda_bv->F+1;i++)
    {
      free(sqlda_bv->V[i-1]);
      free(sqlda_bv->I[i-1]);
    }

  /*  Handle sqlda deallocations */
  sqlclu(sqlda_bv);
  sqlclu(sqlda_sli);

  EXEC SQL CLOSE curs1;

  return 0;

}

void sql_error(char *msg)
{
    cout << endl << msg << endl;
    sqlca.sqlerrm.sqlerrmc[sqlca.sqlerrm.sqlerrml] = '\0';
    oraca.orastxt.orastxtc[oraca.orastxt.orastxtl] = '\0';
    oraca.orasfnm.orasfnmc[oraca.orasfnm.orasfnml] = '\0';
    cout << sqlca.sqlerrm.sqlerrmc << endl;
    cout << "in " << oraca.orastxt.orastxtc << endl;
    cout << "on line " << oraca.oraslnr << " of " <<
oraca.orasfnm.orasfnmc
         << endl << endl;

   /* Disable ORACLE error checking to avoid an infinite loop
    * should another error occur within this routine.
    */
   EXEC SQL WHENEVER SQLERROR CONTINUE;

   // Roll back any pending changes and disconnect from Oracle.
   EXEC SQL ROLLBACK RELEASE;
   exit(1);

}

 
 
 

HELP: Dynamic SQL/Pro*C++ core dumps

Post by Thomas Ky » Wed, 10 Feb 1999 04:00:00


A copy of this was sent to Ken Dougan <kdou...@panther.ab.ca>
(if that email address didn't require changing)

On Mon, 08 Feb 1999 16:07:17 -0700, you wrote:
>By the way, I have the test program running and I thought you might
>be interested in the working source.  After you run it though you
>can see that a double inserted as 1.1111111111e111 is NOT returned
>as inserted.  It actually returns
>1111111111111111419130920033087031241842622669656883858001193965689775666308169623874335331159460917210068811776.000000
>when it should be
>1111111111111111000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000.000000.  
>That is,
>"if (1.111111111111111e111 == v_double )" is FALSE.

it was never
>1111111111111111000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000.000000.  

to begin with...

doubles and floats in C are imperfect numbers.  if you move them out of the
double or float representation -- they lose precision pretty fast.  they are
approximations of numbers -- not really numbers.  

Try this in your code:

    printf( "%lf\n", 1.111111111111111e111 );

I get:

1111111111111111018633350797916390792776052762865862369994096023817381301237868157208136747289357787904677511168.000000

it is approx. 1.111111111111111e111 (and as close as you are going to get).  

By putting the double into the database -- it is converted into a number with 38
digits of precision.  Since the double is an approx. to begin with, by
converting we lose some and by converting again, we lose more.  The problem is
that C is incapable of representing the number 1.111111111111111e111 in a double
or float.

The database doesn't just put 8 raw bytes into the database (think about it if
it did -- what about byte ordering on different systems, the fact that
floats/doubles are machine dependent representations -- there are >1 way to
store a float and so on).  the number stored in the database has 38 digits of
precision, the double 16 (typically) and a float 13 or something like that.
Since they all have different precisions -- we get different approximations (but
they are all approximations)...

>Any suggestions?
>Thanks.
>Ken

>SQL> desc foo;
> Name                            Null?    Type
> ------------------------------- -------- ----
> I                                        NUMBER(4)
> L                                        NUMBER(12)
> F                                        NUMBER
> D                                        NUMBER

>=====================================

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

>#include <iostream.h>

>extern "C" {
>EXEC SQL INCLUDE sqlca;
>EXEC SQL INCLUDE sqlda;
>EXEC SQL INCLUDE sqlcpr;

>/* The ORACA=YES option must be specified to enable use of the ORACA */
>EXEC ORACLE OPTION (ORACA=YES);

>void sql_error(char *msg);
>extern void sqlclu( struct SQLDA* );
>extern SQLDA *sqlald(int, unsigned int, unsigned int);
>extern void sqlnul( unsigned short*, unsigned short*, int* );
>extern void sqlprc( unsigned long*, int*, int*);
>};

>int main( int argc, char** argv ) {
>  char vtest[50];
>  int _type;
>  int v_int;
>  long v_long;
>  float v_float;
>  double v_double;
>  int v_bv_int = 1;

>  int i;                    /* counter variable */
>  int nullok;               /* holder variable for sqlnul() return */
>  int prec;                 /* holder variable for sqlprec() */
>  int scale;                /* holder variable for sqlprec() */

>  SQLDA* sqlda_bv;          /* declare sqlda for bind variables */
>  SQLDA* sqlda_sli;         /* declare sqlda for select list items */

>  EXEC SQL BEGIN DECLARE SECTION;
>  VARCHAR username[21];
>  VARCHAR password[21];
>  EXEC SQL END DECLARE SECTION;

>  strcpy((char*)username.arr, "sdb3");
>  username.len = strlen((const char*)username.arr);
>  strcpy((char*)password.arr, "sdb3");
>  password.len = strlen((const char*)password.arr);

>  EXEC SQL WHENEVER SQLERROR DO sql_error("Oracle error");

>  EXEC SQL CONNECT :username IDENTIFIED BY :password;

>  /* sqlald(max # of SLI items, max SLI name length, max BV name length)
>*/
>  /* NB: When allocating for a select decriptor always set param 3 to
>'0' */
>  sqlda_bv = sqlald(3,10,10);
>  sqlda_sli = sqlald(4,10,0);

>  /* set max # of BVs */
>  sqlda_bv->N=3;
>  /* set max # of SLIs */
>  sqlda_sli->N=4;

>  EXEC SQL PREPARE stmt1 FROM "select i, l, f, d from foo where i =
>:id";

>  EXEC SQL DECLARE curs1 CURSOR FOR stmt1;

>  EXEC SQL DESCRIBE BIND VARIABLES FOR stmt1 INTO sqlda_bv;

>  /*  Allocate storage for BVs */
>  sqlda_bv->N=sqlda_bv->F;   /*  reset N to the value in F */

>  for(i=1;i<sqlda_bv->F+1;i++) {
>      /*  Setup the I (Indicator Variable) value */
>      sqlda_bv->I[i-1]=(short *)malloc(sizeof(short *));  
>      *sqlda_bv->I[i-1]=0;     /*  or set to -1 if NULL value    */

>      /*  Setup the T (Datatype) value */
>      /* sqlda_bv->T[i-1]=1; */
>      sqlda_bv->T[i-1]=3; /*  integer */

>      /*  Setup the L (Length) value */
>      /* sqlda_bv->L[i-1]=strlen((const char*)v_bv_val); */
>      sqlda_bv->L[i-1]= sizeof(int);

>      /*  Setup the V (Value/Address) value */
>      /* sqlda_bv->V[i-1]=v_bv_val; */
>      sqlda_bv->V[i-1]=(char*)&v_bv_int;
>  }

>  EXEC SQL OPEN curs1 USING DESCRIPTOR sqlda_bv;

>  EXEC SQL DESCRIBE SELECT LIST FOR stmt1 INTO sqlda_sli;

>  /* printf("\nHere's the value:\t%i",sqlca.sqlerrd[2]); */
>  /* exit(0); */

>  /*  Allocate storage for SLIs */
>  sqlda_sli->N=sqlda_sli->F;  /*  reset N to the value in F */

>  for(i=1;i<sqlda_sli->F+1;i++)
>    {
>      /*  Reset the null bit value */
>      sqlnul((unsigned short*)&sqlda_sli->T[i-1],
>            (unsigned short*)&sqlda_sli->T[i-1], &nullok);

>      /*  Setup the I (Indicator Variable) value */
>      sqlda_sli->I[i-1]=(short *)malloc(sizeof(short *));  
>      if (i == 1) _type = 3;  /*  INTEGER */
>      if (i == 2) _type = 8;  /*  LONG */
>      if (i == 3) _type = 4;  /*  FLOAT */
>      if (i == 4) _type = 2;  /*  NUMBER -- but handle like FLOAT */
>      /*  Setup the L (Length) value and the T (Datatype) value */
>      switch( _type )
>    {
>      /*  VARCHAR2 */
>    case  1: sqlda_sli->T[i-1] = 1;
>      break;

>      /*  NUMBER -- FLOAT into double */
>    case  2: sqlprc((unsigned long*)&sqlda_sli->L[i-1],&prec,&scale);
>      /* sqlda_sli->L[i-1] = sizeof(int); */
>      sqlda_sli->L[i-1] = sizeof(double);
>      sqlda_sli->T[i-1] = 4;
>      sqlda_sli->V[i-1]=(char*) &v_double;
>      break;

>      /*  INTEGER */
>    case  3:
>      /* sqlda_sli->L[i-1] = sizeof(int); */
>      sqlda_sli->L[i-1] = sizeof(int);
>      sqlda_sli->T[i-1] = 3;
>      sqlda_sli->V[i-1]=(char*) &v_int;
>      break;

>      /*  FLOAT (this is never the case since 4 never occurs */
>    case  4: sqlprc((unsigned long*)&sqlda_sli->L[i-1],&prec,&scale);
>      sqlda_sli->L[i-1] = sizeof(float);
>      sqlda_sli->T[i-1] = 4;
>      /*  Store to a variable */
>      sqlda_sli->V[i-1]=(char*) &v_float;
>      /*  Store to a sqlda */
>      /*  sqlda_sli->V[i-1]=(char *)malloc((size_t)sqlda_sli->L[i-1]); */
>      break;

>      /*  PACKED DECIMAL */
>    case  7: break;

>      /*  LONG */
>    case  8: sqlprc((unsigned long*)&sqlda_sli->L[i-1],&prec,&scale);
>      /* sqlda_sli->L[i-1] = sizeof(int); */
>      sqlda_sli->L[i-1] = sizeof(long);
>          /* internally to Oracle a number has a defined precision,
>             therefore, NUMBER(p) is a long if p is greater than the
>             precision of MAXINT */
>      sqlda_sli->T[i-1] = 3;
>      sqlda_sli->V[i-1]=(char*) &v_long;
>          v_long = 0l;
>      break;

>      /*  LONG RAW */
>    case 24: sqlda_sli->L[i-1] = 240;
>      break;
>    }

>      /*  Setup the V (Value/Address) value (check for numbers!!) */
>      if (sqlda_sli->T[i-1]!=2 && sqlda_sli->T[i-1]!=4)
>    sqlda_sli->V[i-1]=(char *)malloc((size_t)sqlda_sli->L[i-1]);
>    }  

>  EXEC SQL FETCH curs1 USING DESCRIPTOR sqlda_sli;  

>  printf("insert into foo values (1, 1111111, 1.111e1,
>1.111111111111111e111);\n");
>  v_int = *((int*)(sqlda_sli->V[0]));
>  printf( "sli int: %i \n",  *((int*)(sqlda_sli->V[0])) );
>  printf( "  v_int: %i \n",  v_int );
>  v_long = *((long*)(sqlda_sli->V[1]));
>  printf( "sli long: %d \n",  *((long*)(sqlda_sli->V[1])) );
>  printf( "  v_long: %d \n",  v_long );
>  v_float = *((float*)(sqlda_sli->V[2]));
>  printf( "sli float: %f \n",  *((float*)(sqlda_sli->V[2])) );
>  printf( "  v_float: %f \n",  v_float );
>  v_double = *((double*)(sqlda_sli->V[3]));
>  printf( "sli double: %e \n",  *((double*)(sqlda_sli->V[3])) );
>  printf( "  v_double: %e \n",  v_double );
>  printf( "sli double: %f \n",  *((double*)(sqlda_sli->V[3])) );
>  printf( "  v_double: %f \n",  v_double );
>  if (1.111111111111111e111 == v_double ) {
>     printf("1.111111111111111e111 == v_double\n");
>  } else {
>     printf("WARNING:  1.111111111111111e111 != v_double\n");
>  }

>  /*  Deallocate storage for SLIs, BVs, and SQLDAs */

>  /*  Handle SLI deallocations */
>  for(i=1;i<sqlda_sli->F+1;i++)
>    {
>      free(sqlda_sli->V[i-1]);
>      free(sqlda_sli->I[i-1]);
>    }

>  /*  Handle BV deallocations */
>  for(i=1;i<sqlda_bv->F+1;i++)
>    {
>      free(sqlda_bv->V[i-1]);
>      free(sqlda_bv->I[i-1]);
>    }

>  /*  Handle sqlda deallocations */
>  sqlclu(sqlda_bv);
>  sqlclu(sqlda_sli);

>  EXEC SQL CLOSE curs1;

>  return 0;
>}

>void sql_error(char *msg)
>{
>    cout << endl << msg << endl;
>    sqlca.sqlerrm.sqlerrmc[sqlca.sqlerrm.sqlerrml] = '\0';
>    oraca.orastxt.orastxtc[oraca.orastxt.orastxtl] = '\0';
>    oraca.orasfnm.orasfnmc[oraca.orasfnm.orasfnml] = '\0';
>    cout << sqlca.sqlerrm.sqlerrmc << endl;
>    cout << "in " << oraca.orastxt.orastxtc << endl;
>    cout << "on line " << oraca.oraslnr << " of " <<
>oraca.orasfnm.orasfnmc
>         << endl << endl;

>   /* Disable ORACLE error checking to avoid an infinite loop
>    * should another error occur

...

read more »

 
 
 

1. Help with Pro*C core dumps :)

Hi,

I recently installed 7.3.2 and Pro*C/C++ 2.2.2 on Solaris.
my programs seem to compile fine, and as far as the C portion
of the house they run ok.  The moment I hit an embedded SQL
statement, it seems to core dump..

I have tried a simple
EXEC SQL INCLUDE SQLCA
...

main
{
...

EXEC SQL CONNECT :user;      

Using sample1.sql as a template, and it still core_dumps
on the connect.  

Is this a library path issue related to the shared library
runtime load > ?

I would appreciate your pointers as to how to avoid this,
and if there are specific flags I should use
(code= , mode= in pcscfg.h) etc

thanks
Maurice De Vidts

2. Number of fields in a table?

3. Pro*C program core dump when linked with libclntsh

4. Print jobs cause resource loss (pdox 7 3.1)

5. core dump with program compiled with pro*c

6. Problems Grouping in Code

7. core dump with pro*c program

8. Conditional Select statement

9. pro*c core dumps over syntax errors?

10. Dynamic SQL method 4 in Pro *C/C++.

11. Pro*c/C++ 8.03 Dynamic SQL Method 4

12. Help, ORA-07445 and core dump