Customs fields stop working

This public forum is for user-to-user discussions of PHPMaker. Note that this is not support forum.
Post Reply
christ2000
User
Posts: 519

Customs fields stop working

Post by christ2000 »

Hello, all tables that have customs fields stop working and show errors. (v2023.7)


arbei
User
Posts: 9286

Post by arbei »

  1. You need to post error messages.
  2. You may always enable Debug to check detailed error messages.

christ2000
User
Posts: 519

Post by christ2000 »

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


arbei
User
Posts: 9286

Post by arbei »

The error said you have syntax error. You may post the CREATE TABLE statement of your table and the SQL expression of your custom field for testing.


christ2000
User
Posts: 519

Post by christ2000 »

SQL expression of my custom field it is a blank field, doesn't have any statement


arbei
User
Posts: 9286

Post by arbei »

Did you mean your custom field is "lockstatussd" so you have '' AS lockstatussd in the SQL?

You may post the CREATE TABLE statement of your table for testing.


christ2000
User
Posts: 519

Post by christ2000 »

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


arbei
User
Posts: 9286

Post by arbei »

  1. 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
    		...
            );
  2. Also enable Debug to check the complete SQL.


christ2000
User
Posts: 519

Post by christ2000 »

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
);


arbei
User
Posts: 9286

Post by arbei »

It is correct. You better enable Debug to check the complete SQL, simply click Tools -> Advanced Settings, check "Debug" and "Log SQL to file" and generate config.php again. Then check the SQL in the log file.


christ2000
User
Posts: 519

Post by christ2000 »

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()
...

arbei
User
Posts: 9286

Post by arbei »

To find out what exactly the syntax error is you need to see the complete SQL, you may either, as suggested,

  1. Post the CREATE STATEMENT of the table so other users can re-create the table and test, OR
  2. Enable "Log SQL to file" and check the complete SQL in the log file.

christ2000
User
Posts: 519

Post by christ2000 »

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} []
...

arbei
User
Posts: 9286

Post by arbei »

The error is due to that "lock" is a reseved word in MySQL, it appears that PHPMaker tries not to quote the custom field name if it does not contain spaces. You may try to rename them.


christ2000
User
Posts: 519

Post by christ2000 »

ok i just change lock to loock, and work, let me see other tables, let you know now


christ2000
User
Posts: 519

Post by christ2000 »

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


Post Reply