PHsPeed forums
Refined search stops after editing DBQuery SQL sentence even with NO changes
-
Hi,
I found that refined search stops after editing DBQuery SQL sentence even with NO changes.
Looking for the cause I found that there are also some other unexpected consequences of editing a DBQuery SQL sentence, even if I made no changes at all:
STEPS TO REPRODUCE:
- Using the wizard, create a new Grid app, choose "Use DBQuery".
- In the new app select the DBGrid component, and set refinedsearch > rsactive property to true
- Open the DBGrid fields property. In my case they are qualified with the table name like "my table.myfield" Set any field for the refined search.
Run the app: everything works as expected.
Now for causing the error:
- Select the DBQuery component and edit the SQL property. No need to change anything, just open it and save it
- Go check the DBQuery fields: they lost the table prefix
- Go check the DBGrid fields: the names in the available list also lost the prefix
- Run the app: the grid shows no data, IDE reports:
PHsPeed exception report.
2024-07-22 10:14:57 | [c:\phspeedv33\xampp\htdocs\_libs\php\spdbtable.php] [251]::sqlstate[hy000]: general error: 1 no such column: medicion_fkpaciente
(this is the name of the first dataset column)
To be able to see the DBGrid data again, I need to enter the grid fields, remove all "Selected fields" and then choose again from the available field list. But they still don't have the table name prefix
- After removing and adding all fields, set again a refined search field
- Run the app: the grid again shows data, but the refined search shows no field name nor the list of values. IDE reports:
2024-07-22 10:19:34 | [c:\phspeedv33\xampp\htdocs\_libs\php\spdbtable.php] [251]::sqlstate[hy000]: general error: 1 near ":integer": syntax error
The error is in the __assign function from the DBGrid class (myapp_dbgrid_1.php)
public function __assign($app) {
$this->AddRefinedSearch('.fkPaciente :Integer','',''); // << HERE: the field name starts with a dot, and incluyes a colon and datatype
$cid='grid_medicion_datasource_1';
$cmp=$app->findComponentByName($cid);
$this->datasource = $cmp;
}
Hope that's clear enough!
Regards, -
-
The issue is located and fixed. The problem lies into the fact that a DBQuery does not have a tablename property. A DBQuery could use joins, or complex queries which does not have a strict table name. When you create a grid in the Wizzard, then it is based on a table, and the query was setup like a DBTable, which is incorrect. That is fixed.
For users who are wondering what the difference is between a DBTable and DBQuery, if you are using a grid based on a single table, then use DBTable. Especially when you require a Crud. For complex queries, use DBQuery, which allows you to set up the query manually. You can create a Crud if the query is relatively simple, using only one table.
