Solved.
The problem was that I was addressing 'single_value_field' without the
context of the derived table.
Changed:
JOIN
table2 ON (table2.id = single_value_field)
To:
JOIN
table2 ON (table2.id = *new_table_alias*.single_value_field)
On Monday, May 13, 2019 at 6:14:08 PM UTC+3, Gilad Hoshmand wrote:
>
> 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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/web2py/0a849aa9-95fa-4e1a-9d1c-33f13e75a548%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
