I have a form and one its fields is not required. When I try to insert a new record to the table the operation fails if this field is left empty. The correspondent column in the table is set to allow null values. The update operations run flawlessly even with the before mentioned field left empty. Anyone have a clue on this?
Are you using connectors on server side?
Try to enable logging.
Well, I’m not quite sure I am using connectors on server side, here is the code I’m using to retrieve data.
formdata.php
<?
include("./config.php");
require_once("./../../../includes/DHTMLX/dataprocessor/codebase/dhtmlxdataprocessor_debug.js");
require_once("./../../../includes/DHTMLX/connector/php/codebase/data_connector.php");
$res=mysql_connect($hostdb,$userdb,$passdb);
mysql_select_db($namedb);
$data = new JSONDataConnector($res,$dbtype);
$data->enable_log("c:\temp\dhtmlx",true);
$data->render_table("lancamentos","cod_lancamento","dat_lancamento,cod_payee_lancamento,cod_categoria_lancamento,vlr_lancamento,cod_formapagamento_lancamento,nmr_documento,cod_origem_lancamento,dsc_lancamento,cod_tipo_lancamento");
?>and here is the page containing the form:
index.html
<!DOCTYPE html>
<html>
<head>
<meta name = "viewport" content = "initial-scale = 1.0, maximum-scale = 1.0, user-scalable = no">
<link rel="stylesheet" href="./codebase/touchui.css" type="text/css" media="screen" charset="utf-8">
<script src="./codebase/touchui.js" type="text/javascript" charset="utf-8"></script>
</head>
<body>
<script type="text/javascript" charset="utf-8">
dhx.ready(function(){
dhx.i18n.dateFormat = "%Y-%m-%d";
dhx.i18n.setLocale();
dhx.ui({ id: 'app', view: 'layout',
rows: [
{ view: 'layout', type: 'wide',
rows: [
{ view: 'toolbar', type: 'MainBar',
elements: [
{ view: 'label', label: 'Lançamento', id: 'titulo', align: 'center'}
], id: 'toolbar_2'
},
{ view: 'form', scroll: true, id: 'form_2',
elements: [
{ view: 'datepicker', label: 'Data', startOnMonday: true, navigation: true, skipEmptyWeeks: true, calendarDateFormat: '%d/%m/%Y', DateFormat: '%d-%m-%Y', stringResult: true, calendarMonthHeader: '%F %Y', calendarDayHeader: '%d', calendarWeek: '%W', cellAutoHeight: true, id: 'dat_lancamento', labelWidth: '100', align: 'right'},
{ view: 'richselect', label: 'Fornecedor', value: '0', id: 'cod_payee_lancamento', datatype: 'json', url: './data/touch_fornecedor_json.php', labelWidth: '100', align: 'left'},
{ view: 'richselect', label: 'Categoria', value: '2', id: 'cod_categoria_lancamento', datatype: 'json', url: './data/touch_categoria_json.php', labelWidth: '100', align: 'left'},
{ view: 'text', label: 'Valor', value: '0.00', labelPosition: 'left', labelAlign: 'left', id: 'vlr_lancamento', labelWidth: '100', align: 'right'},
{ view: 'toggle',
options: [
{ value: '2', label: 'Débito'},
{ value: '1', label: 'Crédito'}
], label: '', labelPosition: 'right', id: 'cod_tipo_lancamento'
},
{ view: 'richselect', label: 'Forma PG', value: '2', id: 'cod_formapagamento_lancamento', datatype: 'json', url: './data/touch_formapagamento_json.php', labelWidth: '100', align: 'left'},
{ view: 'text', label: 'Documento', labelPosition: 'left', labelAlign: 'left', id: 'nmr_documento', labelWidth: '100', align: 'left'},
{ view: 'richselect', label: 'Origem PG', value: '2', id: 'cod_origem_lancamento', datatype: 'json', url: './data/touch_origem_json.php', labelWidth: '100', align: 'left'},
{ view: 'textarea', label: 'Descrição', id: 'dsc_lancamento'},
]
},
{ view: 'toolbar', type: 'MainBar',
elements: [
{ view: 'button', label: 'Cancelar', id: 'btn_cancel', align: 'left', width: 100, inputWidth: '100'},
{ view: 'button', label: 'Salvar', id: 'btn_save', click:"save_form", width: 100, inputWidth: '100', align: 'right'}
], id: 'toolbar_3'
}
], id: 'layout_2'
}
]
});
});
function save_form(){
dhx.ajax().post("data/formdata.php?action=insert", $$('form_2').getValues());
};
</script>
</body>
</html>By the way, the debug options are not producing any results, may you also help me with this?
Both - js and server side code looks valid, it seems that problem occurs because of some db level constraints
$data->enable_log(“c:\temp\dhtmlx”,true);
Please be sure that this is valid path, and that directory has write access.
There are no constraints associated to this column but I’m thinking if it can be some issue related to data type conversion because the mentioned column (nmr_lancamento) is of integer data type.
Here is the table DDL:
CREATE TABLE `lancamentos` (
`cod_lancamento` int(11) NOT NULL auto_increment,
`dat_lancamento` date NOT NULL,
`cod_payee_lancamento` int(11) NOT NULL,
`cod_categoria_lancamento` int(11) NOT NULL,
`vlr_lancamento` decimal(11,2) NOT NULL,
`cod_formapagamento_lancamento` int(11) default NULL,
`nmr_documento` int(11) default NULL,
`cod_origem_lancamento` int(11) default NULL,
`dsc_lancamento` text character set latin1,
`cod_tipo_lancamento` int(11) NOT NULL,
PRIMARY KEY (`cod_lancamento`)
) ENGINE=MyISAM AUTO_INCREMENT=28 DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;$data->enable_log(“c:\temp\dhtmlx”,true);
I’m running my tests on a Windows Vista machine and this is a valid path with modify permissions set to “Everyone” built-in group.
The only way, how log file can be missed - the file in question is not called at all
Please try to use firebug or dev. tools of the browser and check is call to the server side really occurs and what server returns for it.
I’m still thinking that the error is related to some data type convertion issue, remember that ‘nmr_documento’ is an integer.
When i try to run the INSERT statement found inside the log file, MySQL returns error
1366 - Incorrect integer value: '' for column 'nmr_documento'I wonder if there is a way of telling connector that this column is of integer data type so when it builds the INSERT statement it reflects it, like changing ‘’ (two single quotation marks) for integer columns by NULL.
Generated log
====================================
Log started, 22/09/2011 11:09:24
====================================
DataProcessor object initialized
dat_lancamento => 2011-09-22
cod_payee_lancamento => 1
cod_categoria_lancamento => 2
vlr_lancamento => 3.00
cod_tipo_lancamento => 2
cod_formapagamento_lancamento => 2
nmr_documento =>
cod_origem_lancamento => 2
dsc_lancamento => tee
Row data [dummy_id]
dat_lancamento => 2011-09-22
cod_payee_lancamento => 1
cod_categoria_lancamento => 2
vlr_lancamento => 3.00
cod_tipo_lancamento => 2
cod_formapagamento_lancamento => 2
nmr_documento =>
cod_origem_lancamento => 2
dsc_lancamento => tee
INSERT INTO lancamentos(`dat_lancamento`,`cod_payee_lancamento`,`cod_categoria_lancamento`,`vlr_lancamento`,`cod_formapagamento_lancamento`,`nmr_documento`,`cod_origem_lancamento`,`dsc_lancamento`,`cod_tipo_lancamento`) VALUES ('2011-09-22','1','2','3.00','2','','2','tee','2')
Edit operation finished
0 => action:error; sid:dummy_id; tid:dummy_id;
Done in 0.0292279720306sPS.
The problem I was facing with debug was that I was thinking I just have to point to an existing directory (path) and debug will create the file automatically.
Then I changed
$data->enable_log("c:\temp\dhtmlx",true);by
$data->enable_log("c:\temp\dhtmlx\log.txt",true);and it works.
Default configuration of Mysql allows to use strings for integer column ( they will be automatically resolved as numbers ), but it seems that your Mysql installation was configured in different, more strict way and throws an error for such kind of operation.
Yes, you’re right. My MySQL is configured with strict mode turned on
my.ini (…)
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"When I disable strict mode the script runs without errors but in this case MySQL will convert ‘’ (empty string) into 0 (zero) what may be a valid value but is different than NULL and I need to set NULL to this column. I’m trying to build a small web app for personal use, so changing configurations is not a problem but in corporate environments this is not true and many times NULL have to be NULL not 0 nor ".
I don’t know if it’s possible but will be great if we can have a property that can give us the option of telling connector to set NULL to the related DB column when a field is left empty. It could be implemented to dhx.ui.text or to the connector itself.
Something like:
…
{ view: ‘text’, label: ‘Doc#’, id: ‘nmr_documento’, align: ‘left’, emptyisNull: true},
…
With this property, when the SQL statement is being built, ‘’ (two single quotation marks) could be replaced by NULL.
What do you think about this? Is it possible/useful?