Combo default value set to "" throws SQL error

Talk about foreign keys

Moderator: alexandrleonenko

mposse
Junior
Junior
Posts: 20
Joined: Nov 2nd, '10, 03:45

Combo default value set to "" throws SQL error

Postby mposse » Nov 12th, '10, 01:50

Hi There
I have a combo that is not required, so the user could leave it on --Select--. The combo is loaded from the database using an aux table, so I am using a foreign key field to load the combo.
Problem is that when it is sent, it passes the value as '' instead of 0 and since the field is numeric, it can complete the insert statement.

INSERT INTO ordenes (tmpDateStart, accounts_accId, optype_id, users_userId, contacts_conId, tmpDateEnd, fpId, tmpNotas) VALUES ('2010-11-11', '15', '1', '11', '', '2010-11-11', '3', 'test')

I have set the "default"=> to "", 0 and "0" but the form still renders:

<option value="">-- seleccionar --</option></select>

Is there a way to get

<option value="0">-- seleccionar --</option></select>

instead?

administrator
Site Admin
Site Admin
Posts: 6003
Joined: Jan 7th, '09, 23:18
Contact:

Re: Combo default value set to "" throws SQL error

Postby administrator » Nov 14th, '10, 08:51

Find AddRow() method in datagridclass.php

if($this->IsDate($fldName)){
...
}else if($this->IsPassword($fldName)){
...
}else if($this->IsMoney($fldName)){
...
}else if($this->IsText($fldName)) {
...
}else{
if(trim($fldValue) != ""){
if($this->dbHandler->phptype == "oci8") $sql .= $fldValue;
else $sql .= "'".$fldValue."'";
}else if($this->IsFieldRequired($fldName)){
$sql .= '0';
}else{
$sql .= 'NULL';
}
}

and simply check why don't you get $sql .= '0';
How did you define your field in database as integer or varchar?

mposse
Junior
Junior
Posts: 20
Joined: Nov 2nd, '10, 03:45

Re: Combo default value set to "" throws SQL error

Postby mposse » Nov 16th, '10, 21:32

From your code, I would assume the return value should be NULL
The field is type enum, required siy (not required, integer).

Code: Select all

"contacts_conId"   =>array("header"=>"Contacto", "type"=>"enum", "req_type"=>"si", "width"=>"210px", "title"=>"", "readonly"=>"false", "default"=>"0", "source"=>$fill_from_array, "unique"=>"false", "unique_condition"=>"", "visible"=>"true", "on_js_event"=>"", "post_addition"=>""),


when the field is rendered, the "select" option's value is ""

Code: Select all

<select class='default_dg_select' name='siycontacts_conId' id='siycontacts_conId' title='Contacto'    ><option value=''>-- seleccionar --</option></select>


All that considered, when you pass it through the function AddRow(), the field should fall into the option

Code: Select all

}else{
    $sql .= 'NULL';
}


and return something like

Code: Select all

INSERT INTO ordenes (tmpDateStart, accounts_accId, optype_id, users_userId, contacts_conId, tmpDateEnd, fpId, tmpNotas) VALUES ('2010-11-11', '15', '1', '11', 'NULL', '2010-11-11', '3', 'test')


But I get only

Code: Select all

INSERT INTO ordenes (tmpDateStart, accounts_accId, optype_id, users_userId, contacts_conId, tmpDateEnd, fpId, tmpNotas) VALUES ('2010-11-11', '15', '1', '11', '', '2010-11-11', '3', 'test')


Am I missing something?

administrator
Site Admin
Site Admin
Posts: 6003
Joined: Jan 7th, '09, 23:18
Contact:

Re: Combo default value set to "" throws SQL error

Postby administrator » Nov 17th, '10, 09:12

the field should fall into the option


Did you check where it falls?

Another way: add to your $fill_from_array as a first option something like this: "NULL"=>"-- select --"

mposse
Junior
Junior
Posts: 20
Joined: Nov 2nd, '10, 03:45

Re: Combo default value set to "" throws SQL error

Postby mposse » Nov 27th, '10, 17:16

Hi Admin
Yes, Indeed, I checked it falls in the right place... from what I can understand if the field we are discussing is "siy" and the combo has no selection (value = "") then it should replace it for NULL, yet it is not doing it and I can't figure the reason.

administrator
Site Admin
Site Admin
Posts: 6003
Joined: Jan 7th, '09, 23:18
Contact:

Re: Combo default value set to "" throws SQL error

Postby administrator » Nov 28th, '10, 11:41

In AddRow method, in following conditions

Code: Select all

if($this->IsDate($fldName)){
...
}else if($this->IsPassword($fldName)){
...
}else if($this->IsMoney($fldName)){
...
}else if($this->IsText($fldName)) {
...
}else{

}


where your field value fails? Di you debug this?
You have to find the exactly place in condition where the code fails with your NULL-valued field.

mposse
Junior
Junior
Posts: 20
Joined: Nov 2nd, '10, 03:45

Re: Combo default value set to "" throws SQL error

Postby mposse » Dec 2nd, '10, 03:26

Hi Admin
I fixed (sort of) my problem altering the database to use varchar instead of integer for the foreign key fields, but I still think there is a bug somewhere.

Basically, when you set a field to be of type "foreign_key" and you set the "req_type" option as "si", the field is treated as text instead of integer (the "i" in "si" means integer, right?)
When you go through the addrow() function, it enters the isText() validation and obtains a value for $field_type. I would assume here is where you determine that the field is an INT type and isText() should return false ( I couldn't find where in the class you determine that i = INT) but it is returning true instead.
The function isText() returns true to addRow() and the field value is added as '' to the INSERT statement, throwing an error when executing the sql against the database.
The error is that the value is not an int (which is what the db is expecting) and the INSERT doesn't happen.

Now, I am sure there is a place on the class where you ask if the second letter of the req_type field is i, then you return "INT" as the field type. If you tell me where that is, I can follow the code to see why it is not returning it.

Please note that this is only if the field is "si" and there is no value selected, if the field is "ri" or there is a value selected, the insert works as expected.

Regards,

Martin

administrator
Site Admin
Site Admin
Posts: 6003
Joined: Jan 7th, '09, 23:18
Contact:

Re: Combo default value set to "" throws SQL error

Postby administrator » Dec 2nd, '10, 08:23

In the next version this issue was fixed. If you define "req_type"=>"si" and don't select value, it writes ...., 0, ...
But! you field must be define as int in database table.

saanobhaai
Junior
Junior
Posts: 18
Joined: Nov 9th, '10, 18:03

Re: Combo default value set to "" throws SQL error

Postby saanobhaai » Dec 29th, '10, 22:13

I ran into this same problem today, and though I upgraded to 6.41 I am still seeing it. I've tried defining my field ("qa_processor") as both int and tinyint (what I really want).

From what I can tell, mposse's analysis is correct: isText() isn't returning false when it should. isText() calls GetFieldInfo(); if I insert

Code: Select all

if ($field == "qa_processor") { print("<pre>qa_processor: "); print_r($fields[$this->GetFieldOffset($field)][$parameter]); print("</pre>"); }
after line 5143 in datagrid.class.php, I see in my results:
qa_processor:
Notice: Undefined offset: -1 in C:\WCs\welikia.net\SDR\PHPDG_641\datagrid.class.php on line 5144
when the grid is in edit mode. This implies that at the time GetFieldInfo() is called,

Code: Select all

$this->dataSet->tableInfo()

does not contain info about all the fields. I can't trace it farther back into all the PEAR code.

For now, like mposse, I will simply define my field as a varchar, but this is not a good solution longterm.

administrator
Site Admin
Site Admin
Posts: 6003
Joined: Jan 7th, '09, 23:18
Contact:

Re: Combo default value set to "" throws SQL error

Postby administrator » Dec 30th, '10, 16:39

How did you define this field in datagrid page?


Return to “Field type: "foreign key"”