| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: HELP: Dynamic SQL/Pro*C++ core dumps
A copy of this was sent to Ken Dougan <kdougan_at_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 within this routine.
> */
> EXEC SQL WHENEVER SQLERROR CONTINUE;
>
> // Roll back any pending changes and disconnect from Oracle.
> EXEC SQL ROLLBACK RELEASE;
> exit(1);
>}
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
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. Received on Mon Feb 08 1999 - 18:36:22 CST
 |
 |