Problem with MySQL and decimal format

alsol
alsol Member Posts: 243
edited 2010-12-21 in NAV/Navision Classic Client
Hello,

We are currently developing a Magento - NAV integration. For that we use ADO so we can control everything from NAV. This works very well so far but now a problem with the decimal format came up. In Magento for example prices are stored as decimal(12,4). And when I access these fields I get the following error message:

"This data type is not supported by C/SIDE. You can access data from any of the following data types:
VT_VOID, VT_I2, VT_I4, ... "

It looks that there is no way to format the result I get the same message. Somewhere I read something of using the CAST() function, but that did not work either. Does anyone have an idea what we could do here?

Best regards

Comments

  • ta5
    ta5 Member Posts: 1,164
    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
  • alsol
    alsol Member Posts: 243
    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
  • ta5
    ta5 Member Posts: 1,164
    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
  • DigiTecKid
    DigiTecKid Member Posts: 46
    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), ',', '.'));
  • alsol
    alsol Member Posts: 243
    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
  • rmv_RU
    rmv_RU Member Posts: 119
    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.

Categories