Number update problem

alexm · Post by **alexm** » Fri 07 Mar 2008 10:23

I have a table with some values:

create table test_number(id number(10) primary key,
value number);

and a simple test code:

var
data1: Double;
data2: Double;
begin
data1 := 17.8893827617777/10.2937219385664;
OraQuery1.Refresh;
OraQuery1.First;
OraQuery1.Edit;
OraQuery1.FieldByName('VALUE').AsFloat := data1;
OraQuery1.Post;
OraSession1.Commit;
OraQuery1.Refresh;
OraQuery1.First;
data2 := OraQuery1.FieldByName('VALUE').AsFloat;
if (data1 = data2) then Edit1.Text := 'Equal!'
else Edit1.Text := 'Not equal!';
end;

Then I run this code I see 'Not equal!' message in Edit1 field. Why value in data1 is not equal to value in data2?

SQL*Plus gives me a value=1.73789255903187 from updated column.

Tested with Oracle 10.2.0.3, Delphi7, ODAC 6.25.2.14.

Plash · Post by **Plash** » Tue 11 Mar 2008 09:14

This is normal behaviour because Double variable holds a value approximately with limited precision. You can treat Double values
as equal, if their difference is small enough. For example:

if abs(data1 - data2) < 1E-10 then
Edit1.Text := 'Equal!'

alexm · Post by **alexm** » Tue 11 Mar 2008 18:46

Of couse, I know about double types arithmetics rules, but if I will write this code on PL/SQL it will give me 'Equal' result. If I put some values in table, I should give it back with no changes, is not it?
I think Delphi, OCI and Oracle Server have differences in Double presentation, am I right?

alexmcc · Post by **alexmcc** » Fri 14 Mar 2008 08:12

And?
Do you have different support for ODAC purchased users? I have found nothing...

Plash · Post by **Plash** » Fri 14 Mar 2008 16:02

alexm wrote:Of couse, I know about double types arithmetics rules, but if I will write this code on PL/SQL it will give me 'Equal' result. If I put some values in table, I should give it back with no changes, is not it?
I think Delphi, OCI and Oracle Server have differences in Double presentation, am I right?

Conversion of Double value to NUMBER and back can lead to some difference, because Delphi Double and Oracle NUMBER types use different format.

alexmcc · Post by **alexmcc** » Thu 17 Apr 2008 18:25

Plash wrote:
Conversion of Double value to NUMBER and back can lead to some difference, because Delphi Double and Oracle NUMBER types use different format.

And what is about BINARY_FLOAT or BINARY_DOUBLE?

Plash · Post by **Plash** » Fri 18 Apr 2008 08:09

Theoretically, BINARY_DOUBLE type can be converted to Delphi Double without any changes. But actually this depends on Oracle client and server.

alexmcc · Post by **alexmcc** » Wed 17 Jun 2009 14:50

I have created table with DOUBLE_PRECISION column:

CREATE TABLE SCOTT.TEST_BINARY
(
ID NUMBER(10),
VALUE BINARY_DOUBLE
);

and this is a simple test code:

procedure TForm1.Button1Click(Sender: TObject);
var
data1: Double;
data2: Double;
begin
data1 := 177./3.456789;
OraQuery1.Refresh;
OraQuery1.First;
OraQuery1.Edit;
OraQuery1.FieldByName('VALUE').AsFloat := data1;
OraQuery1.Post;
OraSession1.Commit;
OraQuery1.Refresh;
OraQuery1.First;
data2 := OraQuery1.FieldByName('VALUE').AsFloat;
if (data1 = data2) then Edit1.Text := 'Equal!'
else Edit1.Text := 'Not equal!';
end;

end.

Then I run this code I see 'Not equal!' message in Edit1 field.

Then I have executed this code in SQL*Plus:

update test_binary set value=177.0/3.456789;
commit;

and run this code I see 'Equal!' message in Edit1 field:

procedure TForm1.Button1Click(Sender: TObject);
var
data1: Double;
data2: Double;
begin
data1 := 177./3.456789;
OraQuery1.Refresh;
OraQuery1.First;
//OraQuery1.Edit;
//OraQuery1.FieldByName('VALUE').AsFloat := data1;
//OraQuery1.Post;
//OraSession1.Commit;
//OraQuery1.Refresh;
//OraQuery1.First;
data2 := OraQuery1.FieldByName('VALUE').AsFloat;
if (data1 = data2) then Edit1.Text := 'Equal!'
else Edit1.Text := 'Not equal!';
end;

end.

I think you have a bug then write down value to the database.

Any suggestions?

Plash · Post by **Plash** » Fri 19 Jun 2009 08:27

This is the normal case for floating point numbers.

There can be some loss of accuracy when the parameter value is converted from Delphi Double type to the Oracle internal format.

alexmcc · Post by **alexmcc** » Wed 24 Jun 2009 08:03

This is the NOT normal case for floating point numbers in this case.
What is about IEEE-754 Floating Point Standard? It's specified how floating-points numbers encoding into bits.

I think problem is in Delphi Double type, not in ODAC. Maybe it have double extended internal precision (Double = Extended)?

Plash · Post by **Plash** » Fri 26 Jun 2009 08:37

ODAC passes the value to Oracle client, and gets it back as is, without any conversion.

So the loss of accuracy occurs because of an Oracle problem.

alexmcc · Post by **alexmcc** » Fri 18 Sep 2009 10:38

Plash wrote:ODAC passes the value to Oracle client, and gets it back as is, without any conversion.

So the loss of accuracy occurs because of an Oracle problem.

That's not an Oracle problem. I have a good test now.

I have Windows 2003 Server with Oracle Database and Client 10.2.0.4 installed. All test programs runs in this computer and use same database and client.
I have created the table and have inserted a row:

create table test_binary(
id number(10) primary key,
value binary_double);

insert into test_binary values(1,0);
commit;

I have created a C++ code with (BDS 2006 and ODAC 6.80.0.5 (production)). I use two OraQuery objects for data manipulation:

OraQuery1 (select * from test_binary where id = :p1)
OraQuery2 (update test_binary set value = :p2 where id = :p1)

This is my code:

Code: Select all

 double data1;
 double data2;
 data1 = (double)17.8893827617777/(double)10.2937219385664;
 OraQuery2 -> ParamByName("P1") -> AsInteger = 1;
 OraQuery2 -> ParamByName("P2") -> AsFloat = data1;
 OraQuery2 -> Execute();
 OraQuery1 -> ParamByName("P1") -> AsInteger = 1;
 OraQuery1 -> Open();
 OraQuery1 -> First();
 data2 = OraQuery1 -> FieldByName("VALUE") -> AsFloat;
 Edit4 -> Text = data1 - data2;
 // if (abs(data1 - data2)  Text = "Equal!";
 else
  Edit3 -> Text = "Not equal!";

 Edit5 -> Text = DBL_EPSILON;
 OraQuery1 -> Close();

 OraSession1 -> Commit();

It gives me "Not equal!" result in Edit3.

I have udertaken some debug steps and have found what value in data1 is equals to 3F FB CE 68 6D 90 0E F0 (or 1.7378925590318737). But in table this value stored as 3F FB CE 68 6D 90 0E DF (or 1.7378925590318700). I use Oracle function DUMP for testing and IEEE-754 calculators from here: http://babbage.cs.qc.edu/IEEE-754/64bit.html. And this value back to client with no changes.

One more strange thing.

If I use SQL*Plus for calcule this value as:

update test_binary set value = 17.8893827617777/10.2937219385664;
commit;

and take it back with this code:

Code: Select all

 double data1;
 double data2;
 data1 = (double)17.8893827617777/(double)10.2937219385664;
 OraQuery1 -> ParamByName("P1") -> AsInteger = 1;
 OraQuery1 -> Open();
 OraQuery1 -> First();
 data2 = OraQuery1 -> FieldByName("VALUE") -> AsFloat;
 // if (abs(data1 - data2)  Text = "Equal!";
 else
  Edit7 -> Text = "Not equal!";

 OraQuery1 -> Close();

it gives me "Equal!" result in Edit7.

I have very strange thing for you.
I have written the same fragment with MSVC 8.0 and OCCI. Result is equal and debugging (and Oracle DUMP function to) shows what I write down to table value 3F FB CE 68 6D 90 0E F0 (or 1.7378925590318737) and take it back with no changes, I have "Equal" message.
This is my code:

Code: Select all

#define db_user_name "scott"
#define db_password "tiger"
#define db_conn_str "home"


int _tmain(int argc, _TCHAR* argv[])
{
    Environment* env;  
    Connection* conn;
try
    {
    /*
    Create environment for OCCI.
    */
    env = Environment::createEnvironment(Environment::OBJECT);  
    /*
    Connect to db
    */
    conn = env->createConnection(db_user_name, db_password, db_conn_str);
	if (!conn) return -1;
	/*
	 Calculate result
	*/
	double test_data = (double)17.8893827617777/(double)10.2937219385664;
	/*
	 Put it to BDouble
	*/
	BDouble val;
	val.value = test_data;
	val.isNull = false;
	/*
	 Write to table
	*/
	Statement* update_stmt = conn->createStatement("update test_binary set value = :1 where id = :2");
	update_stmt -> setAutoCommit(false);
	update_stmt -> setBDouble(1, val);
	update_stmt -> setInt(2, 1);
	update_stmt -> executeUpdate();
	conn -> commit();
	conn -> terminateStatement(update_stmt);
    /*
    Get it back
    */
    Statement* stmt = conn->createStatement("select value from test_binary where id = :1");
	stmt -> setInt(1, 1);
    /*
    Execute query
    */
    ResultSet *rs = stmt->executeQuery();
    BDouble res;
    while (rs->next())
          {
		   /* get it as BDouble */
		   res = rs->getBDouble(1);
		   cout closeResultSet(rs);
    env->terminateConnection(conn);
    Environment::terminateEnvironment(env);
    }
  catch(SQLException &sqlExcp)
    {
    cerr  rollback();
    }
  return 0;
}

A bit late I will write this test with BDS 2006 and pure OCI and you can not refer to a difference in compilers

.
And if the result will be similar, you will be necessary to find a new explanation to this fact.

alexmcc · Post by **alexmcc** » Mon 21 Sep 2009 12:47

I have written the test with BDS 2006 and pure OCI.
This test gives me "Equal!" result and debugging (and Oracle DUMP function too) shows what I write down to table value 3F FB CE 68 6D 90 0E F0 (or 1.7378925590318737) and take it back with no changes.

Any suggestions?

Test Source:

Code: Select all

#include 
#include 
#include 
#include 
#include 
#include 
#include 

#pragma hdrstop

//---------------------------------------------------------------------------

#pragma argsused
/*------------------------ Global Variables -------------------------------*/

static boolean logged_on = FALSE;
static OCIError *errhp; 

/*========================== UTILITY FUNCTIONS ======================*/
/*
 * These functions are generic functions that can be used in any
 * OCI program.
 */

/* ----------------------------------------------------------------- */
/* Initialize environment, allocate handles                          */
/* ----------------------------------------------------------------- */
sword init_handles(OCIEnv **envhp, OCISvcCtx **svchp, OCIError **errhp, 
				   OCIServer **srvhp, OCISession ** authp, ub4 init_mode)
{
  (void) printf("Environment setup ....\n");

  /* Initialize the OCI Process */
  if (OCIInitialize(init_mode, (dvoid *)0,
                    (dvoid * (*)(dvoid *, size_t)) 0,
                    (dvoid * (*)(dvoid *, dvoid *, size_t))0,
                    (void (*)(dvoid *, dvoid *)) 0 ))
  {
    (void) printf("FAILED: OCIInitialize()\n");
    return OCI_ERROR;
  }

  /* Inititialize the OCI Environment */
  if (OCIEnvInit((OCIEnv **) envhp, (ub4) OCI_DEFAULT,
                 (size_t) 0, (dvoid **) 0 ))
  {
    (void) printf("FAILED: OCIEnvInit()\n");
    return OCI_ERROR;
  }

  /* Allocate a service handle */
  if (OCIHandleAlloc((dvoid *) *envhp, (dvoid **) svchp,
                     (ub4) OCI_HTYPE_SVCCTX, (size_t) 0, (dvoid **) 0))
  {
    (void) printf("FAILED: OCIHandleAlloc() on svchp\n");
    return OCI_ERROR;
  }

  /* Allocate an error handle */
  if (OCIHandleAlloc((dvoid *) *envhp, (dvoid **) errhp,
                     (ub4) OCI_HTYPE_ERROR, (size_t) 0, (dvoid **) 0))
  {
    (void) printf("FAILED: OCIHandleAlloc() on errhp\n");
    return OCI_ERROR;
  }

  /* Allocate a server handle */
  if (OCIHandleAlloc((dvoid *) *envhp, (dvoid **) srvhp,
                     (ub4) OCI_HTYPE_SERVER, (size_t) 0, (dvoid **) 0))
  {
    (void) printf("FAILED: OCIHandleAlloc() on srvhp\n");
    return OCI_ERROR;
  }

  /* Allocate a authentication handle */
  if (OCIHandleAlloc((dvoid *) *envhp, (dvoid **) authp,
                     (ub4) OCI_HTYPE_SESSION, (size_t) 0, (dvoid **) 0))
  {
    (void) printf("FAILED: OCIHandleAlloc() on authp\n");
    return OCI_ERROR;
  }

  return OCI_SUCCESS;
}

/* ----------------------------------------------------------------- */
/* Attach to server with a given mode.                               */
/* ----------------------------------------------------------------- */
sword attach_server(ub4 mode, OCIServer *srvhp, OCIError *errhp, OCISvcCtx *svchp, text * connectstr)
{
  if (OCIServerAttach(srvhp, errhp, (text *) connectstr,
                     (sb4) strlen((char *)connectstr), (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIServerAttach()\n");
    return OCI_ERROR;
  }

  /* Set the server handle in the service handle */
  if (OCIAttrSet((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX,
                 (dvoid *) srvhp, (ub4) 0, (ub4) OCI_ATTR_SERVER, errhp))
  {
    (void) printf("FAILED: OCIAttrSet() server attribute\n");
    return OCI_ERROR;
  }

  return OCI_SUCCESS;
}
/* ----------------------------------------------------------------- */
/* Logon to the database using given username, password & credentials*/
/* ----------------------------------------------------------------- */
sword log_on(OCISession *authp, OCIError *errhp, OCISvcCtx *svchp, text *uid, text *pwd, ub4 credt, ub4 mode)
{
  /* Set attributes in the authentication handle */
  if (OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION,
                 (dvoid *) uid, (ub4) strlen((char *) uid),
                 (ub4) OCI_ATTR_USERNAME, errhp))
  {
    (void) printf("FAILED: OCIAttrSet() userid\n");
    return OCI_ERROR;
  }
  if (OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION,
                 (dvoid *) pwd, (ub4) strlen((char *) pwd),
                 (ub4) OCI_ATTR_PASSWORD, errhp))
  {
    (void) printf("FAILED: OCIAttrSet() passwd\n");
    return OCI_ERROR;
  }

  (void) printf("Logging on as %s  ....\n", uid);

  if (OCISessionBegin(svchp, errhp, authp, credt, mode))
  {
    (void) printf("FAILED: OCIAttrSet() passwd\n");
    return OCI_ERROR;
  }

  (void) printf("%s logged on.\n", uid);

  /* Set the authentication handle in the Service handle */
  if (OCIAttrSet((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX,
                 (dvoid *) authp, (ub4) 0, (ub4) OCI_ATTR_SESSION, errhp))
  {
    (void) printf("FAILED: OCIAttrSet() session\n");
    return OCI_ERROR;
  }

  return OCI_SUCCESS;
}

/* ----------------------------------------------------------------- */
/*  Free the specified handles                                       */
/* ----------------------------------------------------------------- */
void free_handles(OCIEnv *envhp, OCISvcCtx *svchp, OCIServer *srvhp, OCIError *errhp, OCISession *authp, OCIStmt *stmthp)
{
  (void) printf("Freeing handles ...\n");

  if (srvhp)
    (void) OCIHandleFree((dvoid *) srvhp, (ub4) OCI_HTYPE_SERVER);
  if (svchp)
    (void) OCIHandleFree((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX);
  if (errhp)
    (void) OCIHandleFree((dvoid *) errhp, (ub4) OCI_HTYPE_ERROR);
  if (authp)
    (void) OCIHandleFree((dvoid *) authp, (ub4) OCI_HTYPE_SESSION);
  if (stmthp)
    (void) OCIHandleFree((dvoid *) stmthp, (ub4) OCI_HTYPE_STMT);
  if (envhp)
    (void) OCIHandleFree((dvoid *) envhp, (ub4) OCI_HTYPE_ENV);

  return;
}

/* ----------------------------------------------------------------- */
/* Print the error message                                           */
/* ----------------------------------------------------------------- */
void report_error(OCIError *errhp)
{
  text  msgbuf[512];
  sb4   errcode = 0;

  (void) OCIErrorGet((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode,
                       msgbuf, (ub4) sizeof(msgbuf), (ub4) OCI_HTYPE_ERROR);
  (void) printf("ERROR CODE = %d\n", errcode);
  (void) printf("%.*s\n", 512, msgbuf);
  return;
}

/*-------------------------------------------------------------------*/
/* Logout and detach from the server                                 */
/*-------------------------------------------------------------------*/
void logout_detach_server(OCISvcCtx *svchp, OCIServer *srvhp, OCIError *errhp, OCISession *authp, text *userid)
{
  if (OCISessionEnd(svchp, errhp, authp, (ub4) 0))
  {
    (void) printf("FAILED: OCISessionEnd()\n");
    report_error(errhp);
  }

  (void) printf("%s Logged off.\n", userid);

  if (OCIServerDetach(srvhp, errhp, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCISessionEnd()\n");
    report_error(errhp);
  }

  (void) printf("Detached from server.\n");

  return;
}

/*---------------------------------------------------------------------*/
/* Finish demo and clean up                                            */
/*---------------------------------------------------------------------*/
sword finish(boolean loggedon, OCIEnv *envhp, OCISvcCtx *svchp, OCIServer *srvhp, 
			 OCIError *errhp, OCISession *authp, OCIStmt *stmthp, text *userid)
{

  if (loggedon)
    logout_detach_server(svchp, srvhp, errhp, authp, userid);

  free_handles(envhp, svchp, srvhp, errhp, authp, stmthp);

  return OCI_SUCCESS;
}

/*===================== END OF UTILITY FUNCTIONS ======================*/

#pragma argsused
int main(int argc, char* argv[])
{
  text *username = (text *)"scott";
  text *password = (text *)"tiger";
  text *connectstr = (text *)"home";

  OCIEnv *envhp;
  OCIServer *srvhp;
  OCISvcCtx *svchp;
  OCISession *authp;
  // not used
  OCIStmt *stmthp = (OCIStmt *) NULL; 

  /* Initialize the Environment and allocate handles */
  if (init_handles(&envhp, &svchp, &errhp, &srvhp, &authp, (ub4)OCI_DEFAULT))
  {
    (void) printf("FAILED: init_handles()\n");
    return finish(logged_on, envhp, svchp, srvhp, errhp, authp,
                       stmthp, username);
  }

  /* Attach to the database server */
  if (attach_server((ub4) OCI_DEFAULT, srvhp, errhp, svchp, connectstr))
  {
    (void) printf("FAILED: attach_server()\n");
    return finish(logged_on, envhp, svchp, srvhp, errhp, authp,
                       stmthp, username);
  }

  /* Logon to the server and begin a session */
  if (log_on(authp, errhp, svchp, username, password,
             (ub4) OCI_CRED_RDBMS, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: log_on()\n");
    return finish(logged_on, envhp, svchp, srvhp, errhp, authp,
                       stmthp, username);
  }
  logged_on = TRUE; 

  /* Statements  for update and select */
  OCIStmt *updateHandle = (OCIStmt *) NULL; 
  text *updatestmt =(text *)"update test_binary set value = :va where id = :id";
  OCIStmt *selectHandle = (OCIStmt *) NULL;
  text *selectstmt =(text *)"begin select value into :va from test_binary where id = :id; end;";

  /* Allocate update statement handle */
  if (OCIHandleAlloc((dvoid *)envhp, (dvoid **) &updateHandle,
                     (ub4)OCI_HTYPE_STMT, (CONST size_t) 0, (dvoid **) 0))
  {
    (void) printf("FAILED: alloc update statement handle\n");
    return finish(logged_on, envhp, svchp, srvhp, errhp, authp,
                       updateHandle, username);
  }

  /* Allocate select statement handle */
  if (OCIHandleAlloc((dvoid *)envhp, (dvoid **) &selectHandle,
                     (ub4)OCI_HTYPE_STMT, (CONST size_t) 0, (dvoid **) 0))
  {
    (void) printf("FAILED: alloc select statement handle\n");
    return finish(logged_on, envhp, svchp, srvhp, errhp, authp,
                       selectHandle, username);
  }

  /* prepare the update statement */
  if (OCIStmtPrepare(updateHandle, errhp, updatestmt, (ub4)strlen((char *)updatestmt),
                    (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIStmtPrepare() update\n");
    return finish(logged_on, envhp, svchp, srvhp, errhp, authp,
                       updateHandle, username);
  } 

  OCIBind *bind_id = (OCIBind *)0;
  OCIBind *bind_va = (OCIBind *)0;
  /* calculate test data */
  int id = 1;
  double test_data = (double)17.8893827617777/(double)10.2937219385664;

  if (OCIBindByName(updateHandle,&bind_id,errhp,(text *)":id",strlen(":id"),(ub1*)&id,
	  (sword)sizeof(id),SQLT_INT,(dvoid *)0,(ub2)0,(ub2)0,(ub4)0,(ub4)0,OCI_DEFAULT)) {
	  (void) printf("FAILED: OCIBindByName() update - id\n");
      return finish(logged_on, envhp, svchp, srvhp, errhp, authp,
                       updateHandle, username);
  }

  if (OCIBindByName(updateHandle,&bind_va,errhp,(text *)":va",strlen(":va"),(ub1*)&test_data,
	  (sword)sizeof(test_data),SQLT_BDOUBLE,(dvoid *)0,(ub2)0,(ub2)0,(ub4)0,(ub4)0,OCI_DEFAULT)) {
	  (void) printf("FAILED: OCIBindByName() update - va\n");
      return finish(logged_on, envhp, svchp, srvhp, errhp, authp,
                       updateHandle, username);
  }

  /* update, write value to table */
  if (OCIStmtExecute(svchp, updateHandle, errhp, (ub4) 1, (ub4) 0,
                    (CONST OCISnapshot*) 0, (OCISnapshot*) 0,
                    (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIStmtExecute() update\n");
	report_error(errhp);
    return finish(logged_on, envhp, svchp, srvhp, errhp, authp,
                       updateHandle, username);
  }

  /* Commit the changes */
  (void) OCITransCommit(svchp, errhp, (ub4) 0);

  /* get test data back, execute select */

  /* prepare the select statement */
  if (OCIStmtPrepare(selectHandle, errhp, selectstmt, (ub4)strlen((char *)selectstmt),
                    (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIStmtPrepare() select\n");
    return finish(logged_on, envhp, svchp, srvhp, errhp, authp,
                       selectHandle, username);
  }

  /* bind, id = 1 */
  OCIBind *bind_iid = (OCIBind *)0;
  /* for result */
  OCIBind *bind_vva = (OCIBind *)0;
  double res_value = 0;
  if (OCIBindByName(selectHandle,&bind_iid,errhp,(text *)":id",strlen(":id"),(ub1*)&id,
	  (sword)sizeof(id),SQLT_INT,(dvoid *)0,(ub2)0,(ub2)0,(ub4)0,(ub4)0,OCI_DEFAULT)) {
	  (void) printf("FAILED: OCIBindByName() insert - id\n");
      return finish(logged_on, envhp, svchp, srvhp, errhp, authp,
                       updateHandle, username);
  }

  if (OCIBindByName(selectHandle,&bind_vva,errhp,(text *)":va",strlen(":va"),(ub1*)&res_value,
	  (sword)sizeof(res_value),SQLT_BDOUBLE,(dvoid *)0,(ub2)0,(ub2)0,(ub4)0,(ub4)0,OCI_DEFAULT)) {
	  (void) printf("FAILED: OCIBindByName() select - va\n");
      return finish(logged_on, envhp, svchp, srvhp, errhp, authp,
                       updateHandle, username);
  }

  if (OCIStmtExecute(svchp, selectHandle, errhp, (ub4) 1, (ub4) 0,
                    (CONST OCISnapshot*) 0, (OCISnapshot*) 0,
                    (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIStmtExecute() select\n");
	report_error(errhp);
    return finish(logged_on, envhp, svchp, srvhp, errhp, authp,
                       updateHandle, username);
  }

  /* test on equal */
  if (test_data == res_value)
			  printf("Equal\n");
		   else
			   printf("Not Equal\n");

  /* Deallocate select statement handle */
  if (OCIHandleFree((dvoid *) selectHandle, (ub4) OCI_HTYPE_STMT)) {
	(void) printf("FAILED: dealloc select statement handle\n");
    return finish(logged_on, envhp, svchp, srvhp, errhp, authp,
                       selectHandle, username);  
  }

  /* Dellocate update statement handle */
  if (OCIHandleFree((dvoid *) updateHandle, (ub4) OCI_HTYPE_STMT)) {
	(void) printf("FAILED: dealloc update statement handle\n");
    return finish(logged_on, envhp, svchp, srvhp, errhp, authp,
                       updateHandle, username);  
  }

  /* clean up */
  return finish(logged_on, envhp, svchp, srvhp, errhp, authp,
                     stmthp, username); 
  
  return 0;
}

alexmcc · Post by **alexmcc** » Fri 25 Sep 2009 15:48

Waiting...

I have changed the code (code using BDS 2006 and ODAC) in the previous message - it's was wrong, becouse I have copied it from wrong place.
Now it looks like:

Code: Select all

 double data1;
 double data2;
 data1 = (double)17.8893827617777/(double)10.2937219385664;
 OraQuery2 -> ParamByName("P1") -> AsInteger = 1;
 OraQuery2 -> ParamByName("P2") -> AsFloat = data1;
 OraQuery2 -> Execute();
 OraQuery1 -> ParamByName("P1") -> AsInteger = 1;
 OraQuery1 -> Open();
 OraQuery1 -> First();
 data2 = OraQuery1 -> FieldByName("VALUE") -> AsFloat;
 Edit4 -> Text = data1 - data2;
 // if (abs(data1 - data2)  Text = "Equal!";
 else
  Edit3 -> Text = "Not equal!";

 Edit5 -> Text = DBL_EPSILON;
 OraQuery1 -> Close();

 OraSession1 -> Commit();

Plash · Post by **Plash** » Mon 28 Sep 2009 10:24

This difference that you bind the parameter as SQLT_BDOUBLE while ODAC binds it as SQLT_FLT.

You can use the following code to bind the parameter as BINARY_DOUBLE:

Code: Select all

type
  TOraParam_ = class(TOraParam);
...
  OraQuery.Params[0].AsFloat := d;
  TOraParam_(OraQuery.Params[0]).SubDataType := dtBDouble;

This code has no effect for the current ODAC version. But we will fix this problem in the next ODAC build. The BINARY_DOUBLE parameter will be used by default when you edit SELECT query with a BINARY_DOUBLE field.

Devart Forums

Number update problem

Number update problem

again...