1127

insert method error in MariaDB server

Showing 1 to 10 of 12 comments.

Comments for “insert method error in MariaDB server”
 

Posted by investobd on Wednesday 9th February 2022 at 18:35 GMT

Hello Trongate,
when i insert data array its show this error (Fatal error: Uncaught PDOException: 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 'group, parent_id) VALUES ('Paper roc', '19506', 'Paper roc', 'A', '445')' at line 1 in D:\xampp\htdocs\investo\engine\Model.php:69 Stack trace: #0 D:\xampp\htdocs\investo\engine\Model.php(69): PDOStatement->execute() #1 D:\xampp\htdocs\investo\engine\Model.php(417): Model->prepare_and_execute('INSERT INTO ins...', Array) #2 D:\xampp\htdocs\investo\modules\instruments\controllers\Instruments.php(146): Model->insert(Array, 'instruments') #3 D:\xampp\htdocs\investo\engine\Core.php(209): Instruments->submit('') #4 D:\xampp\htdocs\investo\engine\Core.php(32): Core->serve_controller() #5 D:\xampp\htdocs\investo\public\index.php(5): Core->__construct() #6 {main} thrown in D:\xampp\htdocs\investo\engine\Model.php on line 69).
any idea why this error and how to solve this.

investobd

User Level: Guest

Date Joined: 10/12/2021

Posted by djnordeen on Wednesday 9th February 2022 at 20:47 GMT

Hello,
Can you post your code to insert?
Use the following tags for code
 
Put your code here

Also, what is the database structure that you are trying to insert into, if possble.
Thanks, Dan
Early Adopter

djnordeen

User Level: Early Adopter

Date Joined: 20/08/2021

Posted by investobd on Thursday 10th February 2022 at 10:13 GMT

Hello Trongate, Good day.
I am trying every way in XAMPP + MariaDB. The insert method show this fatal error, Other method working good.

investobd

User Level: Guest

Date Joined: 10/12/2021

Posted by djnordeen on Thursday 10th February 2022 at 12:42 GMT

Have you turned debug to true?
https://trongate.io/docs_m/information/how-to-use-debug-mode

This will show you your query.
Without knowing the table structure and query hard to know.
Early Adopter

djnordeen

User Level: Early Adopter

Date Joined: 20/08/2021

Posted by D3mon on Thursday 10th February 2022 at 13:01 GMT

Based only on the error message, I'd take a wild guess and say that the column named 'group' (or any of your column names) is using a MariaDB reserved word. This is best avoided as it can cause all sorts of problems down the track.

See: https://mariadb.com/kb/en/reserved-words/ for more info.

Assuming this is an Trongate-generated query, the system could be improved by enclosing each column name in backticks (`) to avoid similar issues moving forward.

This comment was edited by D3mon on Thursday 10th February 2022 at 13:06 GMT

Level One Member

D3mon

User Level: Level One Member

Date Joined: 6/07/2021

Posted by mirandakimel on Thursday 10th February 2022 at 13:19 GMT

I can't test it right now, but this should work:

trongate > engine > Model.php: ~ line 403

replace:
        
        $sql.= implode(", ", array_keys($data)).')';


with:
        
        $sql.= '`'.implode("`, `", array_keys($data)).'`)';

This comment was edited by mirandakimel on Thursday 10th February 2022 at 13:30 GMT

mirandakimel

User Level: Guest

Date Joined: 8/07/2021

Posted by investobd on Thursday 10th February 2022 at 14:08 GMT

Thanks D3mon,
Its work perfectly.

investobd

User Level: Guest

Date Joined: 10/12/2021

Posted by D3mon on Thursday 10th February 2022 at 14:17 GMT

Awesome! Smash that star button on my last post for the win! 👍
Level One Member

D3mon

User Level: Level One Member

Date Joined: 6/07/2021

Posted by DaFa on Thursday 10th February 2022 at 21:27 GMT

Well spotted D3mon! I often wrap column names with backticks and this is a great example as to why.

The solution should be put up as a pull request so DC can add it to the framework. As you may know, any changes in the engine folder will be overwritten on future updates.

Cheers,
Si
Founding Member

DaFa

User Level: Founding Member

Date Joined: 30/11/2018

Posted by D3mon on Thursday 10th February 2022 at 22:40 GMT

Ah Ok. I've posted it on Github as an issue (#79) as I'm not fully skilled in Git yet. 🙂
Level One Member

D3mon

User Level: Level One Member

Date Joined: 6/07/2021

×