Table/View Name change

[ryno]

Hi Eugenio

Is there an easy way to change the name of a table or view? I created a view with a "-" and not a "_", example view-name and not view_name, and now SQL queries are broken in the WHERE clause if the table or view name is not also in quotes. It only takes the first part of the name, view in the example above, and then cannot find it.

I have looked under "DB Synchro" but you cannot change the table name their. Searching the forum did not help either. The only other way I can think of is to uninstall the table/view, change the name in the database and re-install the table/view in DaDaBik, but then you will have to recreate the permissions and forms, etc.

Thus far I only get an SQL error if I use this table/view as a reference in the "Master/details view" of another form. The table/view itself works fine.

[08] Error: during query execution. SELECT COUNT(*) FROM `equipment-projects` LEFT JOIN `equipmodel` AS `equipmodel__1` ON `equipment-projects`.`id_model` = `equipmodel__1`.`id_model` LEFT JOIN `equipmake` AS `equipmake__1` ON `equipment-projects`.`id_make` = `equipmake__1`.`id_make` LEFT JOIN `equiptype` AS `equiptype__1` ON `equipment-projects`.`id_type` = `equiptype__1`.`id_type` LEFT JOIN `equipmentstatus` AS `equipmentstatus__1` ON `equipment-projects`.`id_status` = `equipmentstatus__1`.`id_status` LEFT JOIN `supplierpo` AS `supplierpo__1` ON `equipment-projects`.`PO_Date` = `supplierpo__1`.`id_supplierpo` LEFT JOIN `supplier` AS `supplier__1` ON `equipment-projects`.`id_supplier` = `supplier__1`.`id_supplier` LEFT JOIN `supplierpo` AS `supplierpo__2` ON `equipment-projects`.`id_supplierpo` = `supplierpo__2`.`id_supplierpo` LEFT JOIN `location` AS `location__1` ON `equipment-projects`.`id_location` = `location__1`.`id_location` LEFT JOIN `customer` AS `customer__1` ON `equipment-projects`.`id_customer` = `customer__1`.`id_customer` LEFT JOIN `fnbinvoice` AS `fnbinvoice__1` ON `equipment-projects`.`FNBInvoice_ID` = `fnbinvoice__1`.`FNBInvoice_ID` LEFT JOIN `sitedetail` AS `sitedetail__1` ON `equipment-projects`.`idSiteDetail` = `sitedetail__1`.`idSiteDetail` LEFT JOIN `jobcard` AS `jobcard__1` ON `equipment-projects`.`id_jobcard` = `jobcard__1`.`id_jobcard` WHERE equipment-projects.idSiteDetail = '28'
The DBMS server said: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'equipment' in 'where clause'

According to documentation, "-" is not an illegal character in MySQL, if used it needs to be included in quotes. If I copy this SQL query into MySQL Workbench and add the quotes, then it works fine.

I hope you can help.

 

[eugenio]

This is definitely a bug, the table name should be quoted, here is a quick fix, you haven't done enough tests but it should work: in index.php you have for two times this code line:

$where_clause = $items_table_name.".".$items_table_fk_field_name." = '".escape($details_row[$where_field])."'";

change it with:

$where_clause = $quote.$items_table_name.$quote.".".$quote.$items_table_fk_field_name.$quote." = '".escape($details_row[$where_field])."'";

Best,

 

[ryno]

Hi Eugenio

Thank you very much, that works great. I made the 2 changes in index.php and everything seems to be fine.

I test some more and let you know if I find any other issues.

Just a question, I see 8.1 is available, if I upgrade then changes like these that I made to index.php and some other files will be overwritten? I guess it is only the custom_functions.php file that might not be overwritten. I have added some debug code that copies debug information to a text file for fault finding.

Thanks
Ryno

 

[eugenio]

hello,
thanks for having reported the bug!

Yes, except from the code you wrote in custom_functions.php, in the language custom files and the upload folder; you loose everything else.

Best,