Integer SQL Values Defaulting With String Value

Colorado4Wheeler commented

2017-05-31 14:14:44 +00:00

(Migrated from github.com)

Trying to build a little test project I have added just two fields, a string and an integer. No matter what value I put in the "Data Default" or in the XML "default=" area, the SQL always attempts to use a string value as the default and causes an error when trying to install.

I've tried as many variations as I can think of and it always tries to quote the default, throwing this error.

The SQL being executed is:

CREATE TABLE IF NOT EXISTS #__ranalyzer_site (
id INT(11) NOT NULL AUTO_INCREMENT,
asset_id INT(10) unsigned NOT NULL DEFAULT 0 COMMENT 'FK to the #__assets table.',
name VARCHAR NOT NULL DEFAULT '',
user_id INT(10) NOT NULL DEFAULT '0',
params text NOT NULL DEFAULT '',
published TINYINT(3) NOT NULL DEFAULT 1,
created_by INT(10) unsigned NOT NULL DEFAULT 0,
modified_by INT(10) unsigned NOT NULL DEFAULT 0,
created DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
modified DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
checked_out int(11) unsigned NOT NULL DEFAULT 0,
checked_out_time DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
version INT(10) unsigned NOT NULL DEFAULT 1,
hits INT(10) unsigned NOT NULL DEFAULT 0,
ordering INT(11) NOT NULL DEFAULT 0,
PRIMARY KEY (id),
KEY idx_checkout (checked_out),
KEY idx_createdby (created_by),
KEY idx_modifiedby (modified_by),
KEY idx_state (published)
) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

And the message I get:

JInstaller: :Install: Error SQL 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 'NOT NULL DEFAULT '', user_id INT(10) NOT NULL DEFAULT '0', params text NOT' at line 4
Extension Install: SQL error processing query: DB function failed with error number 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 'NOT NULL DEFAULT '', user_id INT(10) NOT NULL DEFAULT '0', params text NOT' at line 4

Trying to build a little test project I have added just two fields, a string and an integer. No matter what value I put in the "Data Default" or in the XML "default=" area, the SQL always attempts to use a string value as the default and causes an error when trying to install. I've tried as many variations as I can think of and it always tries to quote the default, throwing this error. ![screen shot 2017-05-31 at 8 14 15 am](https://cloud.githubusercontent.com/assets/20329385/26636196/32d1c704-45d9-11e7-9bd2-8963ded52704.png) The SQL being executed is: > CREATE TABLE IF NOT EXISTS `#__ranalyzer_site` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `asset_id` INT(10) unsigned NOT NULL DEFAULT 0 COMMENT 'FK to the #__assets table.', `name` VARCHAR NOT NULL DEFAULT '', `user_id` INT(10) NOT NULL DEFAULT '0', `params` text NOT NULL DEFAULT '', `published` TINYINT(3) NOT NULL DEFAULT 1, `created_by` INT(10) unsigned NOT NULL DEFAULT 0, `modified_by` INT(10) unsigned NOT NULL DEFAULT 0, `created` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00', `modified` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00', `checked_out` int(11) unsigned NOT NULL DEFAULT 0, `checked_out_time` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00', `version` INT(10) unsigned NOT NULL DEFAULT 1, `hits` INT(10) unsigned NOT NULL DEFAULT 0, `ordering` INT(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), KEY `idx_checkout` (`checked_out`), KEY `idx_createdby` (`created_by`), KEY `idx_modifiedby` (`modified_by`), KEY `idx_state` (`published`) ) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; And the message I get: >JInstaller: :Install: Error SQL 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 'NOT NULL DEFAULT '', `user_id` INT(10) NOT NULL DEFAULT '0', `params` text NOT' at line 4 Extension Install: SQL error processing query: DB function failed with error number 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 'NOT NULL DEFAULT '', `user_id` INT(10) NOT NULL DEFAULT '0', `params` text NOT' at line 4

Llewellyn commented

2017-06-01 09:45:25 +00:00

I think the hidden field has a few challenges, you can see how the code is compiled at two places in the compiler.

In c_Fields.php on line 1642
In e_Interpretation.php on line 4826

The default gets wrapped with single quotes, and this usually does not cause an error. We could change that implementation to just load an int instead.

Take a look at the code and let me know what you think will be a better approach.

I think the **hidden** field has a few challenges, you can see how the code is compiled at two places in the compiler. In [c_Fields.php](https://github.com/vdm-io/Joomla-Component-Builder/blob/master/admin/helpers/compiler/c_Fields.php#L1642) on line 1642 In [e_Interpretation.php](https://github.com/vdm-io/Joomla-Component-Builder/blob/master/admin/helpers/compiler/e_Interpretation.php#L4826) on line 4826 The default gets wrapped with single quotes, and this usually does not cause an error. We could change that implementation to just load an int instead. Take a look at the code and let me know what you think will be a better approach.

Colorado4Wheeler commented

2017-06-01 13:05:51 +00:00

(Migrated from github.com)

It's not just hidden fields, I've just verified that even if I change the field type to integer it does the same thing.

ro-ot commented

2017-06-01 21:20:03 +00:00

(Migrated from github.com)

The default gets wrapped with single quotes, and this usually does not cause an error. We could change that implementation to just load an int instead.

So yes it is not just hidden field, @Llewellynvdm said that the default gets wrapped with single quotes, did you check the code he gave links to?

You will see from line 4852 to line 4876 the defaults for all fields are being constructed. So you will see that at this time the defaults are wrapped with single quotes for all types... as @Llewellynvdm mentioned:

We could change that implementation to just load an int instead.

So lets do something like this on line 4865:

elseif ($default == 0 || $default)
{
	if (is_numeric($default))
	{
		$default = $data['null_switch']." DEFAULT ".$default;
	}
	else
	{
		$default = $data['null_switch']." DEFAULT '".$default."'";
	}
}

> The default gets wrapped with single quotes, and this usually does not cause an error. We could change that implementation to just load an int instead. So yes it is not just hidden field, @Llewellynvdm said that the default gets wrapped with single quotes, did you check the code he gave links to? You will see from line [4852](https://github.com/vdm-io/Joomla-Component-Builder/blob/master/admin/helpers/compiler/e_Interpretation.php#L4852) to line [4876](https://github.com/vdm-io/Joomla-Component-Builder/blob/master/admin/helpers/compiler/e_Interpretation.php#L4876) the defaults for all fields are being constructed. So you will see that at this time the defaults are wrapped with single quotes for all types... as @Llewellynvdm mentioned: > We could change that implementation to just load an int instead. So lets do something like this on line [4865](https://github.com/vdm-io/Joomla-Component-Builder/blob/master/admin/helpers/compiler/e_Interpretation.php#L4865): ``` elseif ($default == 0 || $default) { if (is_numeric($default)) { $default = $data['null_switch']." DEFAULT ".$default; } else { $default = $data['null_switch']." DEFAULT '".$default."'"; } } ```

Llewellyn commented

2017-06-03 01:07:52 +00:00

Yes we can change it to behave more as expected, will be in next push to staging branch. So you can test it.

Llewellyn commented

2017-06-05 20:40:50 +00:00

@Colorado4Wheeler please watch this video and give me some feedback.

Here is the pull request and the package to test is this one.

@Colorado4Wheeler please watch [this video](https://youtu.be/cxryVA5Cni8) and give me some feedback. Here is the [pull request](https://github.com/vdm-io/Joomla-Component-Builder/pull/98) and the package to test is [this one](https://github.com/vdm-io/Joomla-Component-Builder/pull/98/commits).

Integer SQL Values Defaulting With String Value #91