1.0 beta2: fatal error on save

Archived from the Xataface Users forum.

ststoddard — Mon Apr 28, 2008 2:04 pm

Hi, I get the following strange error when trying to save a form for a new table I set up:

Fatal error: 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 ‘AND fill\_method LIKE CONCAT(‘%’,’active rain’,’%’) AND location RLIKE CONCAT’ at line 1SELECT COUNT(*) FROM J\_Containers WHERE location\_code LIKE CONCAT(‘%’,’test’,’%’) AND container LIKE CONCAT(‘%’,’1’,’%’) AND shape LIKE CONCAT(‘%’,’box’,’%’) AND material LIKE CONCAT(‘%’,’cement’,’%’) AND use LIKE CONCAT(‘%’,’pet use’,’%’) AND dim1 = ‘44’ AND dim3 = ‘55’ AND fill LIKE CONCAT(‘%’,’0.5’,’%’) AND AND fill\_method LIKE CONCAT(‘%’,’active rain’,’%’) AND location RLIKE CONCAT(‘[[::]]') AND ANDOn line 126 of file /var/www/private/xataface-1b2/Dataface/QueryTool.php in function printStackTrace() On line 525 of file /var/www/private/xataface-1b2/Dataface/QueryTool.php in function Dataface\_QueryTool(J\_Containers,Resource id #12,array(,,,,1,1705948876\_1209415935,new,J\_Containers,new,0,0,30,browse,-action=new&-table=J\_Containers) On in /var/www/private/xataface-1b2/Dataface/QueryTool.php on line 126

Any insights?

---

ststoddard — Mon Apr 28, 2008 2:34 pm

Sorry, false alarm, I think that I fixed it. Changed a field name in my database and didn’t make the change in the fields.ini file.

---

ststoddard — Mon Apr 28, 2008 3:52 pm

No, this is weird. I seemed to have tracked the problem to an enum field with ‘0’ in the list of options. Whenever I select something for that field and something in the subsequent select box, I would get the fatal error. Removing the select option and making the field varchar seems to be working.

Again, the error is:

Fatal error: 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 ‘AND fill\_method LIKE CONCAT(‘%’,’manual’,’%’) AND location RLIKE CONCAT(‘[[:’ at line 1SELECT COUNT(*) FROM J\_Containers WHERE location\_code LIKE CONCAT(‘%’,’test’,’%’) AND container LIKE CONCAT(‘%’,’2’,’%’) AND shape LIKE CONCAT(‘%’,’drum’,’%’) AND material LIKE CONCAT(‘%’,’plastic’,’%’) AND use LIKE CONCAT(‘%’,’household’,’%’) AND color LIKE CONCAT(‘%’,’blue’,’%’) AND dim1 = ‘44’ AND dim3 = ‘44’ AND fill LIKE CONCAT(‘%’,’0.5’,’%’) AND AND fill\_method LIKE CONCAT(‘%’,’manual’,’%’) AND location RLIKE CONCAT(‘[[::]]') AND AND `larvicide` LIKE CONCAT('%','na','%') ANDOn line 126 of file /var/www/private/xataface-1b2/Dataface/QueryTool.php in function printStackTrace() On line 525 of file /var/www/private/xataface-1b2/Dataface/QueryTool.php in function Dataface\_QueryTool(J\_Containers,Resource id #12,array(,,,,1,873552123\_120941 in /var/www/private/xataface-1b2/Dataface/QueryTool.php on line 126

---

ststoddard — Tue Apr 29, 2008 7:24 am

Sorry to be a pest about this, but this weird little error is making my app completely useless for data entry.

I still can’t sort out what the heck is going on. If I go into QueryTool.php and enter a line for ‘print $sql;’ before line 126, then the query that results is truncated – ends with AND. The next field is should be trying to select was an enum field with 0 as the first value. I changed this to ‘none’ and it worked. But I have other tables with enum(‘0’,’1’,’2’) and it all works fine!

Here is the fields.ini (I killed tabs thinking that was part of the problem):

Code: Select all

`;[;tab:c]
;label = Caracterisiticas del contenedor

;[;tab:l]
;label = Inmaduros

[id]

[location_code]
;tab = c
widget:label = “Codigo de casa”
widget:atts:size = 12
widget:focus = 1

[container]
;tab = c
widget:label = Contenedor
widget:atts:size = 4

[shape]
;tab = c
widget:label = Forma del contenedor
widget:type = select
vocabulary = shapes

[shape_other]
;tab = c
widget:label = Forma del contenedor
widget:question = “Escribir la forma si se selccionó arriba ‘otro’”

[material]
;tab = c
widget:label = Material del contenedor
widget:type = select
vocabulary = materials

[material_other]
;tab = c
widget:label = Material del contenedor
widget:question = “Escribir la forma si se selccionó arriba ‘otro’”

[use]
;tab = c
widget:label = Funcion
widget:type = select
vocabulary = uses

[use_other]
;tab = c
widget:label = Funcion
widget:question = “Escribir la forma si se selccionó arriba ‘otro’”

[color]
;tab = c

[dim1]
;tab = c
widget:label = “Diametro o largo”
widget:atts:size = 4

[dim2]
;tab = c
widget:label = Ancho
widget:atts:size = 4

[dim3]
;tab = c
widget:label = Alto
widget:atts:size = 4

[fill]
;tab = c
widget:label = “Proporcion llenado”
widget:atts:size = 4
widget:description = “Indicar el nivel de llenar como proporcion: 0.5 = 50%, 0.1 = 10%”

[solar_exposure]
;tab = c
widget:label = Exposicion solar
widget:atts:size = 3

[lid]
;tab = c
widget:label = Tiene tapa
widget:type = checkbox

[fill_method]
;tab = c
widget:label = Llenar
widget:type = select
vocabulary = fill

[frequency]
;tab = c
widget:label = Frecuencia
widget:type = select
vocabulary = frequency

[location]
;tab = c
widget:label = Locacion
widget:type = checkbox
vocabulary = locations

[organic_material]
;tab = c
widget:label = Material organica

[unused]
;tab = c
widget:label = Inservible
widget:type = checkbox

[roof]
;tab = c
widget:label = Techo
widget:type = checkbox

[larvicide]
;tab = c
widget:label = Larvicida
widget:type = select
vocabulary = larvicides
widget:description = “Indica si había larvicida; dejar sin seleccionar si no.”
[larvae]
widget:label = Larvas
;tab=c
[stages]
;tab=c
widget:label = estadios
widget:type = checkbox
vocabulary = stages

[pupae]
;tab = c
widget:label = Pupas
widget:atts:size = 4
validators:numeric = 1

[culex]
;tab = c
widget:label = Culex
widget:question = Habia culex?
widget:type = checkbox

[eggs]
;tab = l
widget:label = Huevos
widget:type = grid
transient = 1
relationship = eggs
order = 27
widget:columns = “date,eggs,comments”

[wings]
;tab = l
widget:label = Alas
widget:type = grid
transient = 1
relationship = wings
order = 28
widget:columns = “sex,wing_length”

[entered]
;tab = l
timestamp = insert
widget:type = hidden

[entered_by]
;tab = l
widget:type = hidden`

[/code]

And the table definition. The suspected fields are solar_exposure and organic_material.

Code: Select all

CREATE TABLE `J_Containers` (   `id` int(11) NOT NULL auto_increment,   `location_code` varchar(20) collate latin1_spanish_ci NOT NULL,   `container` varchar(5) collate latin1_spanish_ci NOT NULL,   `shape` varchar(48) collate latin1_spanish_ci NOT NULL,   `shape_other` varchar(120) collate latin1_spanish_ci default NULL,   `material` varchar(48) collate latin1_spanish_ci NOT NULL,   `material_other` varchar(120) collate latin1_spanish_ci default NULL,   `use` varchar(48) collate latin1_spanish_ci NOT NULL,   `use_other` varchar(120) collate latin1_spanish_ci default NULL,   `color` varchar(20) collate latin1_spanish_ci NOT NULL,   `dim1` int(11) NOT NULL,   `dim2` int(11) default NULL,   `dim3` int(11) NOT NULL,   `fill` decimal(6,2) default NULL,   `solar_exposure` varchar(4) collate latin1_spanish_ci default NULL,   `lid` tinyint(1) default '0',   `fill_method` varchar(32) collate latin1_spanish_ci default NULL,   `frequency` varchar(32) collate latin1_spanish_ci default NULL,   `location` varchar(20) collate latin1_spanish_ci default NULL,   `organic_material` enum('none','1','2','3') collate latin1_spanish_ci default NULL,   `unused` tinyint(1) default NULL,   `roof` tinyint(1) default NULL,   `larvicide` varchar(32) collate latin1_spanish_ci default NULL,   `larvae` enum('none','1-10','10-50','50-100','100-1000','>1000') collate latin1_spanish_ci default 'none',   `stages` varchar(128) collate latin1_spanish_ci default NULL,   `pupae` smallint(6) default NULL,   `culex` tinyint(1) default NULL,   `entered_by` varchar(32) collate latin1_spanish_ci default NULL,   `entered` datetime default NULL,   PRIMARY KEY  (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci COMMENT='Container data for egg surveys' AUTO_INCREMENT=34 ;

[/code]

---

ststoddard — Tue Apr 29, 2008 7:44 am

And the valuelists.ini

Code: Select all

`[shapes]
bottle = botella
box = caja
bucket = balde
can = lata
coconut = coco
cup = vaso
dish = plato
drum = bidon
hole = “hueco en el suelo”
jar = frasco
jug = jarra
other = otro
pan = sarten
part = repuesto
pitcher = jarra2
pot = olla
stump = “hueco en tronco de arbol”
tank = tanque
tire = llanta
toilet = inodoro
well = pozo

[materials]
brick = ladrillo
cement = cemento
earth = tierra
glass = vidrio
metal = metal
other = otro
plastic = plastico
porcelain = porcelana
rubber = caucho
wood = madera

[uses]
drainage = drenaje
household = “uso cotidiano”
pet = “plato de mascota”
pet = “vivero de animal”
plants = florero
no_use = inservible
other = otro

[fill]
manual = potable
active_rain = lluvia
passive_rain = “lluvia natural”
underground = pozo

[frequency]
daily = diario
every_2_or_3 = “cada 2 o 3 dias”
weekly = semanal

[locations]
inside = intra
outside = peri

[larvicides]
na = ninguno
abate = abate
pyriproxifen = pyriproxifen

[stages]
first = primero
second = segundo
third = tercero
fourth = cuarto
pupa = pupa`

---

shannah — Wed Apr 30, 2008 9:49 am

I believe that this is the same issue as

http://bugs.weblite.ca/view.php?id=149