ERPNext.com Frappe Cloud Support Partners Foundation Frappe School

You have an error in your SQL syntax

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?

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"]"}

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

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

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?

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.

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

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?

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.