www.hkvforums.com

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

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

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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