I believe that there are a couple of faults in the sqlpatch.php this is used to apply sql patches in admin.
The first is the production of the error message Query incomplete: missing closing semicolon when using multi line sql statements.
In order to process more complex sql patches where you refer to more than one table on the database or the same table multiple times it is necessary top split the statement over multiple lines as stated in the help on the page. You can see this by clicking the details button in the install sql patched page.
INSERT INTO tablename
(col1, col2, col3, col4)
SELECT col_a, col_b, col_3, col_4
FROM table2;
Above will produce 3 errors followed by a success statement.
The second fault is the #NEXT_X_ROWS_AS_ONE_COMMAND: has no effect.
If you turn on debug you can see that the value is overridden as soon as the next statement is processed.
Statement
Code:
#NEXT_X_ROWS_AS_ONE_COMMAND:2
SET @Default_Insurance = "20.00";
UPDATE configuration SET configuration_value = @Default_Insurance WHERE configuration_key = 'MODULE_SHIPPING_RM1STSMLPARCEL_MAX_ORDERVALUE' ;
debug output
#NEXT_X_ROWS_AS_ONE_COMMAND:2 SET @Default_Insurance = "20.00"; UPDATE configuration SET configuration_value = @Default_Insurance WHERE configuration_key = 'MODULE_SHIPPING_RM1STSMLPARCEL_MAX_ORDERVALUE' ;
#NEXT_X_ROWS_AS_ONE_COMMAND:2
SET @Default_Insurance = "20.00";
About to execute.
Debug info:
$ line=SET @Default_Insurance = "20.00";
$ complete_line=1
$ keep_together=1
SQL=SET @Default_Insurance = "20.00";
UPDATE configuration SET configuration_value = @Default_Insurance WHERE configuration_key = 'MODULE_SHIPPING_RM1STSMLPARCEL_MAX_ORDERVALUE' ;
About to execute.
Debug info:
$ line=UPDATE configuration SET configuration_value = @Default_Insurance WHERE configuration_key = 'MODULE_SHIPPING_RM1STSMLPARCEL_MAX_ORDERVALUE' ;
$ complete_line=1
$ keep_together=1
SQL=UPDATE configuration SET configuration_value = @Default_Insurance WHERE configuration_key = 'MODULE_SHIPPING_RM1STSMLPARCEL_MAX_ORDERVALUE' ;
Keep_together is set to 1 for every transaction.
As it turns out it does not matter as it appears that the SQL statements are run with memory of the previous statements so as in their first example
#NEXT_X_ROWS_AS_ONE_COMMAND:4
SET @t1=0;
SELECT (@t1:=configuration_value) as t1
FROM configuration
WHERE configuration_key = 'KEY_NAME_HERE';
UPDATE product_type_layout SET configuration_value = @t1 WHERE configuration_key = 'KEY_NAME_TO_CHECK_HERE';
DELETE FROM configuration WHERE configuration_key = 'KEY_NAME_HERE';
If you leave out the #NEXT_X_ROWS_AS_ONE_COMMAND:4 the statement will still be process correctly
Proposed solutions:
1) Remove the check on missing semi colon and all reference to #NEXT_X_ROWS_AS_ONE_COMMAND: from the help page
Remove check from line 283;
PHP Code:
else {
$messageStack->add(ERROR_LINE_INCOMPLETE, 'error');
}
OR
2) If you want to keep the missing semi colon check repurpose the #NEXT_X_ROWS_AS_ONE_COMMAND: to be the number of line that the single SQL statement is on.
This will require correcting the processing between lines 71 and 81
currently
PHP Code:
foreach ($lines as $line) {
if ($_GET['debug'] == 'ON') {
echo $line . '<br>';
}
$line = trim($line);
$line = str_replace('`', '', $line); //remove backquotes
$line = $saveline . $line;
$keep_together = 1; // count of number of lines to treat as a single command
new
Code:
$keep_together = 1; // count of number of lines to treat as a single command
foreach ($lines as $line) {
if ($_GET['debug'] == 'ON') {
echo $line . '<br>';
}
$line = trim($line);
$line = str_replace('`', '', $line); //remove backquotes
$line = $saveline . $line;
Remove existing processing for #NEXT_X_ROWS_AS_ONE_COMMAND:
line 276
PHP Code:
$lines_to_keep_together_counter++;
if ($lines_to_keep_together_counter == $keep_together) { // if all grouped rows have been loaded, go to execute.
$complete_line = true;
$lines_to_keep_together_counter = 0;
} else {
$complete_line = false;
}
replace with
PHP Code:
$complete_line = true;
$lines_to_keep_together_counter = 0; // to reset the counter if set incorrectly
Add processing in before missing semicolon check line 283
PHP Code:
else {
$lines_to_keep_together_counter++;
if ($lines_to_keep_together_counter == $keep_together) { // if all grouped rows have been loaded, go to execute.
$lines_to_keep_together_counter = 0;
$messageStack->add(ERROR_LINE_INCOMPLETE, 'error');
}
}
Additionally modify documentation in the sqlpatch.php language file accessed by pressing the Details button.
Finally I might suggest changing the button called "Details" to "Help"
admin>includes>languages>english.php line 282
Code:
define('IMAGE_DETAILS', 'Details');
Might be better to add new button as this is used in orders and geo_zones as well
Hope that all make sense. There was more discussion on https://www.zen-cart.com/showthread....semicolon-quot
Bookmarks