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

Rick, the ***ProductsAttribsRaw ***handler provides a means to update products' attributes, not their model-numbers. That's what the ***Products ***handler does.

TNX Cindy,

I had a suspicion that was the case but hoped that in the offchance that field might have been editable.

Hi Lat9

I know that the v_dbio_command command is ADD for a new product . Is UPDATE the command to update a product ?

Thanks in advance

Hasher:

Hi Lat9

I know that the v_dbio_command command is ADD for a new product . Is UPDATE the command to update a product ?

Thanks in advance
Hey, Hasher; no v_dbio_command is used for a product update action, just leave that column empty.

I am looking for a plugin that can help me make mas product price changes. Can DbIo help me make mass price changes?

jodean:

I am looking for a plugin that can help me make mas product price changes. Can DbIo help me make mass price changes?

yes, it can

I used this plugin to make price changes and it worked flawlessly. This plugin should be made part of the ZenCart release. The only thing I would suggest would be to note in the log if it is an record update verses duplicate.

Hi all,
Have started uploading ProductAttribRaw and having some issues. The 'Import (Check Only)' returns the following as GREEN and set to load all the records.

Numerous records like this:

2020-06-07 15:03:18: 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, 8, 41.95, '', 0, 0, 0.1, '', 0, 0, 1, '', 1, 0, 0, 0, 0, 0, '', '', 0, 0, 0, 0, 0)
2020-06-07 15:03:18: importUpdateRecordKey, removing record: DELETE FROM products_attributes_download WHERE products_attributes_id = LIMIT 1

.... Plus another 10 records or so to be INSERT.

However, once I run the Import (Full), I get the error:
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.

Assuming it needs a value for the WHERE clause? Why is this not getting picked up after the INSERT?

Am I missing something?

Thanks

Hi all,
I have started uploading using ProductAttribRaw and having some issues. The 'Import (Check Only)' returns the following as GREEN and ready to load all the records.

Numerous records like this are in the log file post the 'Check':

2020-06-07 15:03:18: 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, 8, 41.95, '', 0, 0, 0.1, '', 0, 0, 1, '', 1, 0, 0, 0, 0, 0, '', '', 0, 0, 0, 0, 0)
2020-06-07 15:03:18: importUpdateRecordKey, removing record: DELETE FROM products_attributes_download WHERE products_attributes_id = LIMIT 1

.... Plus another 10 records or so to be INSERT.

However, once I run the Import (Full), I get the error:

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.

Assuming it needs a value for the WHERE clause? Why is this not getting picked up after the INSERT? Or why is it assuming there is a value in products_attributes_download?

Am I missing something?

Thanks

gohealth:

Hi all,
I have started uploading using ProductAttribRaw and having some issues. The 'Import (Check Only)' returns the following as GREEN and ready to load all the records.
I'm running ZC 1.5.6c on PHP7.3.

gohealth:

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.

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.

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

lat9:

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!

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.

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!

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 ([B]!$this->import_is_insert && [/B]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;
    }

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

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
Paul, here's the link to the details for the ProductsHandler: https://github.com/lat9/dbio/blob/master/pages/usage_notes/productshandler.md

That handler provides both an ADD and REMOVE command.