Hello, all tables that have customs fields stop working and show errors. (v2023.7)
Customs fields stop working
-
- User
- Posts: 519
Helle error are:
C:\xampp\htdocs\vendor\doctrine\dbal\src\Driver\API\MySQL\ExceptionConverter.php(86): An exception occurred while executing a query: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'separator, '' AS lockstatussd, datediff(now(), recordadded) AS dayscount, '' ...' at line 1
i was testing and install a previous 2023 version and project work fine, also testing and using last version AGAIN, i delete all customs fields and work fine, also on one table i just delete the custom field, and create a field on the table and work fine, the problems is when the table has custom fields.
thanks
-
- User
- Posts: 519
SQL expression of my custom field it is a blank field, doesn't have any statement
-
- User
- Posts: 519
here is more simple:
this is the error i got on this table:
C:\xampp\htdocs\vendor\doctrine\dbal\src\Driver\API\MySQL\ExceptionConverter.php(86): An exception occurred while executing a query: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'lock FROM contacts_paperwork_tbl WHERE FIND_IN_SET('301', employeeid) LIMIT 20' at line 1
on this table i have 8 fields:
id
documentname
file
type
uploaddate
employeeid
cname
Status
and my custom field created on phpmaker is :
lock
this lock field just has this row_rendered event"
if ($this->Status->CurrentValue == "Approved") {
$this->lock->ViewValue = '<i class="fas fa-lock" style="color: #dc3545"></i>';
}Else
{$this->lock->ViewValue = '<i class="fas fa-unlock" style="color: #198754"></i>';}
}
the only way this work now is if i delete the custom field "lock" or if i add a field "lock" to the table and don't use this as a custom field
-
- User
- Posts: 9394
You may check the generated code of the field in models/<table>.php, if the expression is empty,
''
should be used, e.g. you should find (in this example the custom field name is "SubTotal")$this->SubTotal = new DbField( $this, // Table 'x_SubTotal', // Variable name 'SubTotal', // Name '\'\'', // Expression '\'\'', // Basic search expression 201, // Type 65530, // Size ... );
Also enable Debug to check the complete SQL.
-
- User
- Posts: 519
code look like this:
// lock
$this->lock = new DbField(
$this, // Table
'x_lock', // Variable name
'lock', // Name
'\'\'', // Expression
'\'\'', // Basic search expression
201, // Type
65530, // Size
-1, // Date/Time format
false, // Is upload field
'\'\'', // Virtual expression
false, // Is virtual
false, // Force selection
false, // Is Virtual search
'FORMATTED TEXT', // View Tag
'TEXT' // Edit Tag
);
-
- User
- Posts: 519
using debug:
C:\xampp\htdocs\vendor\doctrine\dbal\src\Driver\API\MySQL\ExceptionConverter.php(86): An exception occurred while executing a query: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'lock FROM contacts_paperwork_tbl WHERE FIND_IN_SET('301', employeeid) LIMIT 20' at line 1
and:
Debug
#0 C:\xampp\htdocs\vendor\doctrine\dbal\src\Connection.php(1908): Doctrine\DBAL\Driver\API\MySQL\ExceptionConverter->convert(Object(Doctrine\DBAL\Driver\PDO\Exception), Object(Doctrine\DBAL\Query))
#1 C:\xampp\htdocs\vendor\doctrine\dbal\src\Connection.php(1851): Doctrine\DBAL\Connection->handleDriverException(Object(Doctrine\DBAL\Driver\PDO\Exception), Object(Doctrine\DBAL\Query))
#2 C:\xampp\htdocs\vendor\doctrine\dbal\src\Connection.php(1070): Doctrine\DBAL\Connection->convertExceptionDuringQuery(Object(Doctrine\DBAL\Driver\PDO\Exception), 'SELECT *, '' AS...', Array, Array)
#3 C:\xampp\htdocs\vendor\doctrine\dbal\src\Query\QueryBuilder.php(336): Doctrine\DBAL\Connection->executeQuery('SELECT *, '' AS...', Array, Array, NULL)
#4 C:\xampp\htdocs\vendor\doctrine\dbal\src\Query\QueryBuilder.php(372): Doctrine\DBAL\Query\QueryBuilder->executeQuery()
#5 C:\xampp\htdocs\models\ContactsPaperworkTblList.php(2237): Doctrine\DBAL\Query\QueryBuilder->execute()
#6 C:\xampp\htdocs\models\ContactsPaperworkTblList.php(922): PHPMaker2023\HRFILECENTER7FINAL2023risen\ContactsPaperworkTblList->loadRecordset(0, 20)
#7 C:\xampp\htdocs\controllers\ControllerBase.php(50): PHPMaker2023\HRFILECENTER7FINAL2023risen\ContactsPaperworkTblList->run()
...
-
- User
- Posts: 519
CREATE TABLE contacts_paperwork_tbl(
id int,
documentname varchar(255),
file varchar(255),
type varchar(255),
employeeid varchar(255)
cname varchar(255)
Status varchar(255)
);
sql log:
[2022-11-30T21:52:01.871650-05:00] log.DEBUG: SELECT COUNT(*) FROM contacts_requirements_tpl {"params":[],"types":[],"executionMS":0.001024007797241211} []
[2022-11-30T21:52:01.873359-05:00] log.DEBUG: SELECT Docname AS `lf`, Docname AS `df`, '' AS `df2`, '' AS `df3`, '' AS `df4` FROM contacts_requirements_tpl {"params":[],"types":[],"executionMS":0.000202178955078125} []
[2022-11-30T21:52:01.874988-05:00] log.DEBUG: SELECT COUNT(*) FROM contacts_requirements_tpl {"params":[],"types":[],"executionMS":0.000186920166015625} []
[2022-11-30T21:52:01.875241-05:00] log.DEBUG: SELECT Docname AS `lf`, Docname AS `df`, '' AS `df2`, '' AS `df3`, '' AS `df4` FROM contacts_requirements_tpl {"params":[],"types":[],"executionMS":0.0001678466796875} []
[2022-11-30T21:52:01.877858-05:00] log.DEBUG: SELECT * FROM employeescontractors WHERE contact_id = 301 {"params":[],"types":[],"executionMS":0.0004291534423828125} []
[2022-11-30T21:52:01.890517-05:00] log.DEBUG: SELECT DISTINCT CredentialsName AS `lf`, CredentialsName AS `df`, '' AS `df2`, '' AS `df3`, '' AS `df4` FROM credentials_tbl WHERE CredentialsName = 'RBT' {"params":[],"types":[],"executionMS":0.00024080276489257812} []
[2022-11-30T21:52:01.892543-05:00] log.DEBUG: SELECT COUNT(*) FROM contacts_paperwork_tbl WHERE FIND_IN_SET('301', employeeid) {"params":[],"types":[],"executionMS":0.0001609325408935547} []
[2022-11-30T21:52:01.901058-05:00] log.DEBUG: SELECT *, '' AS lock FROM contacts_paperwork_tbl WHERE FIND_IN_SET('301', employeeid) LIMIT 20 {"params":[],"types":[],"executionMS":0.0022590160369873047} []
...
-
- User
- Posts: 519
ok i just change lock to loock, and work, let me see other tables, let you know now
-
- User
- Posts: 519
wowww thanks for your help, on the other table was not "lock" was a field name "separator", i just change to "septor" and work,
thanks for help me