Hi all,
I built a join query which breaks up a field that has '|' char as separator
of values to multiple rows with single values (ie. field value = '1|2|3' ,
rows will be multiplied with single_value_field = 1 , single_value_field =
2 etc..)
Filters some rows ('Having ... ' )
Then join with a table that I need to get values from.
I tested it on mysql 5.7 and it works fine.
I'm using db.executesql(...) to execute the query because I find it hard to
make with the DAL but I'm getting an error.
The query is:
SELECT
*
FROM
(SELECT * , SUBSTRING_INDEX(SUBSTRING_INDEX(field1, '|', numbers.n),
'|', - 1) AS single_value_field
FROM
(SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT
4 UNION ALL SELECT 5) numbers
INNER JOIN table1 ON CHAR_LENGTH(field1) - CHAR_LENGTH(REPLACE(field1,
'|', '')) >= numbers.n - 1
HAVING (field1 IS NOT NULL
AND field1 != '||'
AND single_value_field IS NOT NULL
AND single_value_field != '')) new_table_alias
JOIN
table2 ON (table2.id = single_value_field)
WHERE (table2.field2 = '' OR table2.field2 is null)
My Error arises on db.executesql(...) only when I include the last Join
clause:
JOIN
table2 ON (table2.id = single_value_field)
If it is omitted - no problems.
Full error(private bits renamed as in query):
<class 'gluon.contrib.pymysql.err.Error'> (<type
'exceptions.AssertionError'>, AssertionError('Protocol error, expecting
EOF',))Version
web2py™ Version 2.14.6-stable+timestamp.2016.05.10.00.21.47Traceback
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
Traceback (most recent call last):
File "/path/gluon/restricted.py", line 227, in restricted
exec ccode in environment
File "
<https://192.168.16.230:1234/admin/edit/StoreDot/controllers/automated_reports.py>/path/controllers/myController.py"
<https://192.168.16.230:1234/admin/edit/StoreDot/controllers/automated_reports.py>,
line 93, in <module>
File "/path/gluon/globals.py", line 417, in <lambda>
self._caller = lambda f: f()
File "/path/controllers/myController.py"
<https://192.168.16.230:1234/admin/edit/StoreDot/controllers/automated_reports.py>,
line 15, in index
rows = db.executesql(query);
File "/path/gluon/packages/dal/pydal/base.py", line 1019, in executesql
adapter.execute(query)
File "/path/gluon/packages/dal/pydal/adapters/base.py", line 1388, in execute
return self.log_execute(*a, **b)
File "/path/gluon/packages/dal/pydal/adapters/base.py", line 1382, in
log_execute
ret = self.get_cursor().execute(command, *a[1:], **b)
File "/path/gluon/contrib/pymysql/cursors.py", line 117, in execute
self.errorhandler(self, exc, value)
File "/path/gluon/contrib/pymysql/connections.py", line 200, in
defaulterrorhandler
raise Error(errorclass, errorvalue)
Error: (<type 'exceptions.AssertionError'>, AssertionError('Protocol error,
expecting EOF',))
Thank you all!
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/web2py/0e82148c-2e3c-4371-86c4-8a8ada59425a%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
