Problem with MySQL and decimal format

Comments

• Options

  ta5 Member Posts: 1,164

  2010-12-15

  Hi
  Can you post a code snippet on how you are accessing the decimal value from mySQL? I'm not sure, but maybe you can first store the value in a NAV variant type.
  
  Regards
  Thomas

  0
• Options

  alsol Member Posts: 243

  2010-12-15

  Hi Thomas,
  
  Please find below the most important part of the code:
  

  OpenConnection();
  OdbcSqlString :=  'SELECT sales_flat_order.entity_id, state, status, sales_flat_order.customer_id, base_grand_total, ' +
                    '       item_id, product_id, order_id, sku, name, qty_ordered, base_price, ' +
                    '       company, firstname, lastname, street, city, postcode, country_id ' +
                    'FROM   sales_flat_order, ' +
                    '       sales_flat_order_address, ' +
                    '       sales_flat_order_item ' +
                    'WHERE  sales_flat_order.entity_id = sales_flat_order_address.parent_id AND ' +
                    '       sales_flat_order.entity_id = sales_flat_order_item.order_id AND ' +
                    '       sales_flat_order.status <> "canceled" AND ' +
                    '       sales_flat_order_address.address_type = "shipping"';
  ExecuteStatement();
  
  WHILE NOT OdbcRecord.EOF DO BEGIN
    //(...)
    SalesLine.Quantity := OdbcRecord.Fields.Item('qty_ordered').Value;
    //(...)
  END;
  
  

  
  Even when I try to read the value with MESSAGE(FORMAT(...), it does not work. So it must be an issue with C/SIDE. Maybe you have an idea?
  
  Best regards,
  Felix

  0
• Options

  ta5 Member Posts: 1,164

  2010-12-15

  Hi Felix
  I have no access to a mySQL DB, so we need a kind of try and error...
  
  Can you try to get the value first in a variable of type variant, eg:
  myVariant := OdbcRecord.Fields.Item('qty_ordered').Value;
  
  If this works, you could try to convert the value then, either with the format or the evaluate command.
  
  Good luck
  
  Thomas

  0
• Options

  DigiTecKid Member Posts: 46

  2010-12-15

  Hello Felix,
  
  I've worked alot with NAV-MySQL integrations. I'm creating an integration with Interspire Shopping Cart now. You can do it in two commands.
  
  Sql := 'SELECT * FROM [CUSTOMVALUE] WHERE [CUSTOM]=' + SingleQuote + FORMAT(CustomID) + SingleQuote;
  ADOrs.Open(Sql, ADOConn, OpenMethod, LockMethod);
  ....
  ADOField := ADOrs.Fields.Item('VALUE');
  EVALUATE(WebSalesLine."Unit Price", CONVERTSTR(FORMAT(ADOField.Value), ',', '.'));
  WebSalesLine.VALIDATE("Unit Price");
  
  You can probably skip the whole ADOField step if you don't need the ADO field and go:
  EVALUATE(WebSalesLine."Unit Price", CONVERTSTR(FORMAT(ADOrs.Fields.Item('VALUE').Value), ',', '.'));

  0
• Options

  alsol Member Posts: 243

  2010-12-20

  Hi,
  
  Thank you for your answers.
  
  I tried both of your ideas, but none of them worked with the Magento database. Maybe they use a special decimal format which is not compatibel with NAV.
  
  However I found a solution for the problem. I changed my SQL statement and the code as follows:
  

  OpenConnection();
  OdbcSqlString :=  'SELECT sales_flat_order.entity_id, state, status, sales_flat_order.customer_id, base_grand_total, ' +
                    '       item_id, product_id, order_id, sku, name, CAST(qty_ordered AS CHAR) AS quantity, base_price, ' +
                    '       company, firstname, lastname, street, city, postcode, country_id ' +
                    'FROM   sales_flat_order, ' +
                    '       sales_flat_order_address, ' +
                    '       sales_flat_order_item ' +
                    'WHERE  sales_flat_order.entity_id = sales_flat_order_address.parent_id AND ' +
                    '       sales_flat_order.entity_id = sales_flat_order_item.order_id AND ' +
                    '       sales_flat_order.status <> "canceled" AND ' +
                    '       sales_flat_order_address.address_type = "shipping"';
  ExecuteStatement();
  
  WHILE NOT OdbcRecord.EOF DO BEGIN
    //(...)
      EVALUATE(SalesLine.Quantity,FORMAT(OdbcRecord.Fields.Item('quantity').Value));
      SalesLine.VALIDATE(Quantity);
    //(...)
  END;
  

  
  I am not sure if this is the best solution but for now it works. Thank you for your input.
  
  Best regards,
  Felix

  0
• Options

  rmv_RU Member Posts: 119

  2010-12-21

  cyberscout wrote:

  Hi,
  
  Thank you for your answers.
  
  I tried both of your ideas, but none of them worked with the Magento database. Maybe they use a special decimal format which is not compatibel with NAV.
  
  However I found a solution for the problem. I changed my SQL statement and the code as follows:
  

  OpenConnection();
  OdbcSqlString :=  'SELECT sales_flat_order.entity_id, state, status, sales_flat_order.customer_id, base_grand_total, ' +
                    '       item_id, product_id, order_id, sku, name, CAST(qty_ordered AS CHAR) AS quantity, base_price, ' +
                    '       company, firstname, lastname, street, city, postcode, country_id ' +
                    'FROM   sales_flat_order, ' +
                    '       sales_flat_order_address, ' +
                    '       sales_flat_order_item ' +
                    'WHERE  sales_flat_order.entity_id = sales_flat_order_address.parent_id AND ' +
                    '       sales_flat_order.entity_id = sales_flat_order_item.order_id AND ' +
                    '       sales_flat_order.status <> "canceled" AND ' +
                    '       sales_flat_order_address.address_type = "shipping"';
  ExecuteStatement();
  
  WHILE NOT OdbcRecord.EOF DO BEGIN
    //(...)
      EVALUATE(SalesLine.Quantity,FORMAT(OdbcRecord.Fields.Item('quantity').Value));
      SalesLine.VALIDATE(Quantity);
    //(...)
  END;
  

  
  I am not sure if this is the best solution but for now it works. Thank you for your input.
  
  Best regards,
  Felix

  cast(FieldName as money) is better way. Also, boolean fields must be converted to integer - cast(FieldName as int).

  Looking for part-time work.
  Nav, T-SQL.

  0