Database I/O Manager (DbIo) Plugin [Support Thread]

[lat9]

I'm running ZC 1.5.6c on PHP7.3.

... and which version of DbIo? There were changes for ProductAttribRaw in v1.6.0 (but use v1.6.1).

[lat9]

... and which version of DbIo? There were changes for ProductAttribRaw in v1.6.0 (but use v1.6.1).

Just noticed that I've got changes staged for DbIo v1.6.2's version of that handler that address the issue reported.

[lat9]

I've just submitted v1.6.2 of DbIo for the Zen Cart plugins moderators for review; I'll post back here when it's available for download.

This release contains changes associated with the following GitHub issues:

#164: Correct duplicate records generated by the Products handler on multi-lingual stores.
#166: ProductsAttribsRaw: Correct MySQL error on 'Import (check)' adding new download.
#167: ProductsAttribsBasic: Correct MySQL error.
#169: Correct PHP 7.4 deprecated use of curly brackets for array functions.

[lat9]

v1.6.2 is now available for download: https://www.zen-cart.com/downloads.php?do=file&id=2091

[gohealth]

Just noticed that I've got changes staged for DbIo v1.6.2's version of that handler that address the issue reported.

Excellent. I am running 1.6.1, so will download 1.6.2 to address the issues with ProductAttribRaw. Thanks!

[gohealth]

I've just submitted v1.6.2 of DbIo for the Zen Cart plugins moderators for review; I'll post back here when it's available for download.

This release contains changes associated with the following GitHub issues:

#164: Correct duplicate records generated by the Products handler on multi-lingual stores.
#166: ProductsAttribsRaw: Correct MySQL error on 'Import (check)' adding new download.
#167: ProductsAttribsBasic: Correct MySQL error.
#169: Correct PHP 7.4 deprecated use of curly brackets for array functions.

Thanks, this didn't seem to fix my issue. The issue is on the IMPORT (FULL), not the Check. The IMPORT (Check) however does show what I believe to be the issue during the IMPORT - a DELETE FROM products_attributes_download WHERE products_attributes_id = LIMIT 1

I have installed v1.6.2 of addon, running on ZC 1.5.6c, PHP7.3. When ADDING new ProductsAttribsRaw, I still get the following on IMPORT (Check):
2020-06-08 11:40:35: importBuildSqlQuery for products_attributes:
INSERT INTO products_attributes (`products_id`, `options_id`, `options_values_id`, `options_values_price`, `price_prefix`, `products_options_sort_order`, `product_attribute_is_free`, `products_attributes_weight`, `products_attributes_weight_prefix`, `attributes_display_only`, `attributes_default`, `attributes_discounted`, `attributes_image`, `attributes_price_base_included`, `attributes_price_onetime`, `attributes_price_factor`, `attributes_price_factor_offset`, `attributes_price_factor_onetime`, `attributes_price_factor_onetime_offset`, `attributes_qty_prices`, `attributes_qty_prices_onetime`, `attributes_price_words`, `attributes_price_words_free`, `attributes_price_letters`, `attributes_price_letters_free`, `attributes_required`)
VALUES (33, 4, 4, 41.95, '', 0, 1, 0.1, '', 0, 0, 1, '', 1, 0, 0, 0, 0, 0, '', '', 0, 0, 0, 0, 0)
2020-06-08 11:40:35: importUpdateRecordKey, removing record: DELETE FROM products_attributes_download WHERE products_attributes_id = LIMIT 1

The INSERT looks good. The key issue is with the DELETE FROM entry that is created. I'm not sure why on INSERT it is doing a delete from this table?

On Import (FULL), this is the error that is displayed on the browser (ie, not in the log - No log file is actually created. The message gets displayed on browser page /config/dbio_manager.php?action=file):
1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 1' at line 1
in:
[DELETE FROM products_attributes_download WHERE products_attributes_id = LIMIT 1]
If you were entering information, press the BACK button in your browser and re-check the information you had entered to be sure you left no blank fields.
Thanks!

[lat9]

Thanks for the detailed report (I appreciate it). I'll give this a deeper look later today.

[lat9]

Thanks for the detailed report (I appreciate it). I'll give this a deeper look later today.

Days are getting long down here in Florida. I've got a correction staged on GitHub for the v1.6.3 release of DbIo.

Find this method in /admin/includes/classes/dbio/DbIoProductsAttribsRawHandler.php:

    protected function importUpdateRecordKey($table_name, $table_fields, $record_key_value)
    {
        $proceed_with_update = true;
        if ($table_name == TABLE_PRODUCTS_ATTRIBUTES_DOWNLOAD) {
            if (empty($table_fields['products_attributes_filename']['value'])) {
                $proceed_with_update = false;

                // -----
                // If the current DbIo operation is an import-check, simply output a debug message containing
                // the record-deletion SQL.  Otherwise, actually run the SQL, removing that record.
                //
                $sql = 
                    "DELETE FROM " . TABLE_PRODUCTS_ATTRIBUTES_DOWNLOAD . " WHERE products_attributes_id = " . $this->key_fields['products_attributes_id'] . " LIMIT 1";
                if ($this->operation == 'check') {
                    $this->debugMessage("importUpdateRecordKey, removing record: $sql", self::DBIO_STATUS);
                } else {
                    $GLOBALS['db']->Execute($sql);
                }
            } elseif ($this->import_is_insert) {
                $table_fields['products_attributes_id'] = array(
                    'value' => $record_key_value,
                    'type' => 'integer',
                );
            }
        }
        return ($proceed_with_update) ? $table_fields : false;
    }

... and add the highlighted clause:

    protected function importUpdateRecordKey($table_name, $table_fields, $record_key_value)
    {
        $proceed_with_update = true;
        if ($table_name == TABLE_PRODUCTS_ATTRIBUTES_DOWNLOAD) {
            if (!$this->import_is_insert && empty($table_fields['products_attributes_filename']['value'])) {
                $proceed_with_update = false;

                // -----
                // If the current DbIo operation is an import-check, simply output a debug message containing
                // the record-deletion SQL.  Otherwise, actually run the SQL, removing that record.
                //
                $sql = 
                    "DELETE FROM " . TABLE_PRODUCTS_ATTRIBUTES_DOWNLOAD . " WHERE products_attributes_id = " . $this->key_fields['products_attributes_id'] . " LIMIT 1";
                if ($this->operation == 'check') {
                    $this->debugMessage("importUpdateRecordKey, removing record: $sql", self::DBIO_STATUS);
                } else {
                    $GLOBALS['db']->Execute($sql);
                }
            } elseif ($this->import_is_insert) {
                $table_fields['products_attributes_id'] = array(
                    'value' => $record_key_value,
                    'type' => 'integer',
                );
            }
        }
        return ($proceed_with_update) ? $table_fields : false;
    }

[Hasher]

Hey Lat9

I wanted to change categories on products so I downloaded the product file and edited and re-imported. It didn't update changes. I suspect this is because they are existing products and DBio isn't making the new categories . Is there an Dbio command to delete products ?, then I could re-import them as (ADD) new and create the categories .

Thanks for any help!
Paul

[lat9]

Hey Lat9

I wanted to change categories on products so I downloaded the product file and edited and re-imported. It didn't update changes. I suspect this is because they are existing products and DBio isn't making the new categories . Is there an Dbio command to delete products ?, then I could re-import them as (ADD) new and create the categories .

Thanks for any help!
Paul

Paul, here's the link to the details for the ProductsHandler: https://github.com/lat9/dbio/blob/ma...uctshandler.md

That handler provides both an ADD and REMOVE command.