1
MySQL.Async.fetchAll('INSERT INTO position (id, x, y, z) VALUES (@id, @x, @y, @z)', 
{['@id'] = identifier, ['@x'] = coords.x, ['@y'] = coords.z, ['@z'] = coords.z})

Hi. I'm learning to programm and I've written the simple command above for my FiveM server, but for some reason it returns the code below, when it should be all right. I've checked and everything is ok with the id and the coords, so the problem must be, as indicated, the syntax.

[MySQL] [testeServerClient] An error happens on MySQL for query "INSERT INTO position (id, x, y, z) VALUES (@id, 0, 0, 0)": ER_PARSE_ERROR: 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 'position (id, x, y, z) VALUES (@id, 0, 0, 0)' at line 1

Does anyone know how to fix it? My MariaDB version is 10.4.11-MariaDB . Thanks!

Egor Skriptunoff
  • 23,359
  • 2
  • 34
  • 64

3 Answers3

0

the right syntax to use near 'position (id, x, y, z) VALUES (@id, 0, 0, 0)' at line 1

It seems to me it's not passing an ID in values. Nor should you, if the id column is set to auto_increment you shouldn't even include it in your insert.

The correct syntax for inserting a new row into a table, if id column has auto_increment turned on is:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Otherwise, it seems to be a problem other than your query, since its getting 0 0 0 values for the x y z columns no value for id - it still shows up as @id which is invalid since the id column is, I'm hoping. an INT column.

t1f
  • 3,021
  • 3
  • 31
  • 61
  • Hi! Thanks for the help! I don't understand much about databases, but I have learned that the ID should auto-increment, however, this is not a common ID. It should return me a already generated ID, which it does, after my tests, so this one shouldn't auto-increment. Should I add another ID row that auto-increments? This is what the table looks like: https://imgur.com/a/myByk8F (I don't know how to check the code in the website) –  Mar 25 '20 at 20:13
  • 1
    @Almeidowski Hi there. Yup, a good practice is to have an id column of type INT set as a primary key and with auto_increment on. Since you use this current id column for other identification purposes then your current query is correct and should work. The problem remains that whatever language you're using there isn't retrieving an actual id value for the current id column, as evident by the error message. It still shows up like `@id` which I'm guessing is incorrect. So, it's not a mather of the sql you're using but how you pass the parameter value for the id column, there's an error somwhere. – t1f Mar 25 '20 at 20:52
  • @Almeidowski However since I'm not familiar / don't recognize the code you posted (aside from the SQL code which should be fine) I can't help you out in that regard.. – t1f Mar 25 '20 at 20:53
  • Ok, so this is embarassing. Apparently I didn't have nothing to get the player's id, I had it on an old code and I thought I had it on this one too. Problem is, I'm now getting the same error, but with the correct values. For example: https://imgur.com/a/QsClpFr –  Mar 26 '20 at 12:30
0

position is a keyword.

Plan A, Put backtics around it everywhere.

Plan B: Use a different name for the table.

Reference: https://mariadb.com/kb/en/reserved-words/ I don't see it in MySQL's keyword list.

Note: When it says "near..." it is almost always pointing at or immediately after the offending token.

Rick James
  • 135,179
  • 13
  • 127
  • 222
0

I believe the problem is that you are using MySQL.Async.fetchAll for an insert query instead of using MySQL.Sync.execute or MySQL.Async.execute. Try using MySQL.Sync.execute unless you need a callback.

SK73106
  • 201
  • 1
  • 9