You have an error in your SQL syntax

ERPNext
, ,
1

ErrorCode: InternalServerError

Description:
INTERNAL SERVER ERROR

Reason:
{“exc”:“["Traceback (most recent call last):\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/app.py\", line 67, in application\n response = frappe.api.handle()\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/api.py\", line 111, in handle\n doctype, **frappe.local.form_dict)})\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/init.py\", line 939, in call\n return fn(*args, **newargs)\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/client.py\", line 34, in get_list\n limit_start=limit_start, limit_page_length=limit_page_length, ignore_permissions=False)\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/init.py\", line 1150, in get_list\n return frappe.model.db_query.DatabaseQuery(doctype).execute(None, *args, **kwargs)\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/model/db_query.py\", line 88, in execute\n result = self.build_and_run()\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/model/db_query.py\", line 112, in build_and_run\n return frappe.db.sql(query, as_dict=not self.as_list, debug=self.debug, update=self.update)\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/database.py\", line 176, in sql\n self._cursor.execute(query)\n File \"/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/cursors.py\", line 170, in execute\n result = self._query(query)\n File \"/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/cursors.py\", line 328, in _query\n conn.query(q)\n File \"/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py\", line 516, in query\n self._affected_rows = self._read_query_result(unbuffered=unbuffered)\n File \"/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py\", line 727, in _read_query_result\n result.read()\n File \"/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py\", line 1066, in read\n first_packet = self.connection._read_packet()\n File \"/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py\", line 683, in _read_packet\n packet.check_error()\n File \"/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/protocol.py\", line 220, in check_error\n err.raise_mysql_exception(self._data)\n File \"/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/err.py\", line 109, in raise_mysql_exception\n raise errorclass(errno, errval)\nProgrammingError: (1064, u’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 \\‘from tabPOS Profile User where tabPOS Profile User.user = \"jgray@test.com\\’ at line 1’)\n"]”}

Trying to get POS profile using RestAPI and am getting a SQL error from the API. I hope is me doing something wrong. The username is email address with the @ sign. Is that legal?

2

I remove the user’s email address and am getting a different error.

ErrorCode: InternalServerError

Description:
INTERNAL SERVER ERROR

Reason:
{“exc”:“["Traceback (most recent call last):\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/app.py\", line 67, in application\n response = frappe.api.handle()\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/api.py\", line 111, in handle\n doctype, **frappe.local.form_dict)})\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/init.py\", line 939, in call\n return fn(*args, **newargs)\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/client.py\", line 34, in get_list\n limit_start=limit_start, limit_page_length=limit_page_length, ignore_permissions=False)\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/init.py\", line 1150, in get_list\n return frappe.model.db_query.DatabaseQuery(doctype).execute(None, *args, **kwargs)\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/model/db_query.py\", line 88, in execute\n result = self.build_and_run()\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/model/db_query.py\", line 112, in build_and_run\n return frappe.db.sql(query, as_dict=not self.as_list, debug=self.debug, update=self.update)\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/database.py\", line 176, in sql\n self._cursor.execute(query)\n File \"/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/cursors.py\", line 170, in execute\n result = self._query(query)\n File \"/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/cursors.py\", line 328, in _query\n conn.query(q)\n File \"/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py\", line 516, in query\n self._affected_rows = self._read_query_result(unbuffered=unbuffered)\n File \"/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py\", line 727, in _read_query_result\n result.read()\n File \"/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py\", line 1066, in read\n first_packet = self.connection._read_packet()\n File \"/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py\", line 683, in _read_packet\n packet.check_error()\n File \"/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/protocol.py\", line 220, in check_error\n err.raise_mysql_exception(self._data)\n File \"/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/err.py\", line 109, in raise_mysql_exception\n raise errorclass(errno, errval)\nProgrammingError: (1064, u\"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 'from tabPOS Profile User \\n\\t\\t\\t order by tabPOS Profile User.modified DESC ’ at line 1\")\n"]”}

3

I think you need to use backtick quotes around the table names

from `tabPOS Profile User`
order by `tabPOS Profile User`.modified DESC

4

Am going through the API am not touching the SQL Server. This would need to fix in the API query? Is this what you are saying?

5

I don’t know enough about dev/API - just commented on what looks like a source of error - but unfortunately I cannot say with any certainty if it is relevant. Maybe one of the proper experts will be more helpful.

6

I hope this will help you.
https://frappe.io/docs/user/en/guides/integration/rest_api

7

Guys,
Am not touching the SQL Server.
The error is in the SQL code the API is calling. Can we look at that code? Anyone ever integrate POS User?

8

just for any future visitors, i had a similar error when using the api.

the issue was that i passed in a field via the api call that wasn’t in the doctype schema/database definition.

so double check that the data that you pass in via the api matches your doctype.