Re: [Help-glpk] Least significant digit error in "table" statement

[glpk xypron]

Hello Marc,

I was able to reproduce the error with Access 2010 and
GLPK 4.45. The string returned by the ODBC library is indead
'E-2'.

I set up the same table in MySQL

create table tbl (
  FromCode Text(10),
  ToCode Text(10),
  TransitTime Double,
  Primary Key (FromCode(10), ToCode(10))
  );
  
Insert into tbl values ("A", "B", 1.91176470588235E-02);

The error did not occur.

In the current implementation of glpk-4.45/src/glpsql.c
the ODBC driver is requested to convert all values to
string (SQL_CHAR). Afterwards the value is converted
to double in function db_iodbc_read if it is numeric.

There seems to be a bug in the Access implementation
for number conversion.

When requesting the Access driver to convert the value
in question to double (SQL_DOUBLE) your example works
correctly.

Please, find the patch below.

Could you, please, give me some feedback after a more
extensive testing.

Best regards

Xypron


--- c:\Program Files\GLPK\glpk-4.45\src\glpsql.c        2010-12-05 
10:00:00.000000000 +0100
+++ glpsql.c    2011-02-21 21:47:40.519123900 +0100
@@ -840,8 +840,13 @@
             &nullable);
          sql->isnumeric[i] = is_numeric(sql->coltype[i]);
          /* bind columns to program vars, converting all types to CHAR*/
-         dl_SQLBindCol(sql->hstmt, i, SQL_CHAR, sql->data[i],
-            SQL_FDLEN_MAX, &(sql->outlen[i]));
+         if (sql->isnumeric[i])
+         {  dl_SQLBindCol(sql->hstmt, i, SQL_DOUBLE, sql->data[i],
+               SQL_FDLEN_MAX, &(sql->outlen[i]));
+         } else 
+         {  dl_SQLBindCol(sql->hstmt, i, SQL_CHAR, sql->data[i],
+               SQL_FDLEN_MAX, &(sql->outlen[i]));
+         }
          for (j = sql->nf; j >= 1; j--)
          {  if (strcmp(mpl_tab_get_name(dca, j), sql->colname[i]) == 0)
             break;
@@ -893,23 +898,17 @@
          len = sql->outlen[i];
          if (len != SQL_NULL_DATA)
          {
-            if (len > SQL_FDLEN_MAX)
-               len = SQL_FDLEN_MAX;
-            else if (len < 0)
-               len = 0;
-            strncpy(buf, (const char *) sql->data[i], len);
-            buf[len] = 0x00;
-            if (0 != (sql->isnumeric[i]))
-            {  strspx(buf); /* remove spaces*/
-               if (str2num(buf, &num) != 0)
-               {  xprintf("'%s' cannot be converted to a number.\n",
-                     buf);
-                  return 1;
-               }
-               mpl_tab_set_num(dca, sql->ref[i], num);
+            if (sql->isnumeric[i])
+            {  mpl_tab_set_num(dca, sql->ref[i], *((const double *) 
sql->data[i]));
             }
             else
-            {  mpl_tab_set_str(dca, sql->ref[i], strtrim(buf));
+            {  if (len > SQL_FDLEN_MAX)
+                  len = SQL_FDLEN_MAX;
+               else if (len < 0)
+                  len = 0;
+               strncpy(buf, (const char *) sql->data[i], len);
+               buf[len] = 0x00;
+               mpl_tab_set_str(dca, sql->ref[i], strtrim(buf));
             }
          }
       }


-------- Original-Nachricht --------
> Datum: Mon, 21 Feb 2011 03:15:10 -0600
> Betreff: [Help-glpk] Least significant digit error in "table" statement

> I have a simple "table" statement that fails with the message: 'E-2'
> cannot be converted to a number.
> 
> One of the input numbers (for the field TransitTime) is
> 0.0191176470588235.  When I strip away the least significant digit to make it
> 0.019117647058823, the "table" statement works fine.
> 
> The test code is below; the Access database and model file are attached.
> 
> param DB_LOCATION symbolic := "C:\TestOfTable.mdb";
> #param ACCESS_DRIVER symbolic := "Driver={Microsoft Access Driver
> (*.mdb)}";
> param ACCESS_DRIVER symbolic := "Driver={Microsoft Access Driver (*.mdb,
> *.accdb)}";
> 
> set OD_R dimen 2;
> param TransitTimeHours     {OD_R};
> 
> table R IN 'ODBC'
>    ACCESS_DRIVER & ';DBQ=' & DB_LOCATION & ';'
>   'SELECT FromCode, ToCode, TransitTime '
>   'FROM tbl'  :
>   OD_R                 <- [FromCode,ToCode],
>   TransitTimeHours      ~ TransitTime;
> 
> display card(OD_R);
> 
> end;
> 
> ________________________________
> This e-mail and any attachments may be confidential or legally privileged.
> If you received this message in error or are not the intended recipient,
> you should destroy the e-mail message and any attachments or copies, and you
> are prohibited from retaining, distributing, disclosing or using any
> information contained herein. Please inform us of the erroneous delivery by
> return e-mail. Thank you for your cooperation.

-- 
Empfehlen Sie GMX DSL Ihren Freunden und Bekannten und wir
belohnen Sie mit bis zu 50,- Euro! https://freundschaftswerbung.gmx.de