• query parameters are not handed through correctly for the paging links (this error also existed prior to version 3.5)

• the result set warning turns on with max_rows set to 0 (unlimited), it needs to test for that

On current master the lint job on TravisCI is not successful. This might be because of newer package versions used.
I activated weekly builds so in future we could notice this earlier.

See https://travis-ci.org/github/zopefoundation/Products.ZSQLMethods/jobs/707796714

I am trying to get ZSQLmethods working on Zope4, but I can't find a DB adapter that works.
SQLAlchemyDB and ZpsycopgDA will not install correctly.

Any suggestion or has anyone got something that has been fixed?

I have upgrade my existing zope2 to zope4 (4.1.3, python 3.6.9, linux). Locally it works fine. On the server it gives an exception for every page with a ZSQL method in the web view. Other pages have no problem . It is this error:

Error Type: Unauthorized
Error Value: Your user account does not have the required permission. Access to 'sqlQuery' of (OrderedFolder at /mysite/news_folder) denied. Your user account, Anonymous User, exists at /acl_users. Access requires Use_Database_Methods_Permission, granted to the following roles: ['Authenticated', 'Manager']. Your roles in this context are ['Anonymous'].

Traceback (innermost last):
Module ZPublisher.WSGIPublisher, line 156, in transaction_pubevents
Module ZPublisher.WSGIPublisher, line 338, in publish_module
Module ZPublisher.WSGIPublisher, line 256, in publish
Module ZPublisher.mapply, line 85, in mapply
Module ZPublisher.WSGIPublisher, line 62, in call_object
Module OFS.DTMLMethod, line 177, in call

• <DTMLMethod at /mysite/index_html>
• URL: http://mysite.nl/index_html/manage_main
• Physical Path: /mysite/index_html
  Module DocumentTemplate.DT_String, line 528, in call
  Module DocumentTemplate._DocumentTemplate, line 145, in render_blocks
  Module DocumentTemplate.DocumentTemplate, line 167, in render_blocks
  Module DocumentTemplate._DocumentTemplate, line 376, in getitem
  Module DocumentTemplate._DocumentTemplate, line 399, in getitem
  Module OFS.DTMLMethod, line 168, in call
• <DTMLMethod at /mysite/index.html>
• URL: http://mysite.nl/index.html/manage_main
• Physical Path: /mysite/index.html
  Module DocumentTemplate.DT_String, line 528, in call
  Module DocumentTemplate._DocumentTemplate, line 145, in render_blocks
  Module DocumentTemplate.DocumentTemplate, line 246, in render_blocks
  Module DocumentTemplate.DT_In, line 462, in renderwb
  Module DocumentTemplate.DT_Util, line 228, in eval
• traceback_info: news_folder
  Module , line 1, in
  Module AccessControl.ImplPython, line 632, in DTMLValidate
  Module AccessControl.ImplPython, line 484, in validate
  Module AccessControl.ImplPython, line 846, in raiseVerbose

The inherited implementation of manage_DAVget in webdav.Resource.Resource returns the results of a call to PrincipiaSearchSource. This is incorrect for ZSQL methods.

Override manage_DAVget to return the results of a call to document_src, which will correctly render the specially formatted tags at the top that describe the parameters, bindings and the title.

BUG/PROBLEM REPORT

What I did:

I added a ZSQLMethod in ZMI with this statement (I use a Oracle DB):

select 'a<xyz>b' VALUE from dual

and clicked on the "Test" tab.

What I expect to happen:

That the Value a<xyz>b is shown.

What actually happened:

In the browser i see the value ab (instead of a<xyz>b). Both in the query and in the result table:

What version of Python and Zope/Addons I am using:

Zope-5.9
Python-3.9.5
Products.ZSQLMethods-4.0

Additional Info

It seems that the "html_quote" is missing in some DTML reports. I will provide a PR with a fix and tests...

I'm currently porting a Zope App from Zope 2.13 to Zope 4.1.1.
There is a simple query working in Zope 2.13 against the same DB (MariaDB 10.3) which causes an exception in Zope 4.1.1

select   *
from     counter
<dtml-sqlgroup where>
         <dtml-sqltest public optional type="nb">
</dtml-sqlgroup>
order by url

It gives an ProgrammingError: (1064, '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 '"'Y'"\norder by url LIMIT 1000' at line 4')
what becomes clear when seeing the generated SQL in Server Log:

select   *
from     counter
where
public = b"'Y'"
order by url LIMIT 1000

Hardcoding where instead of <dtml-sqlgroup> makes no difference while
public = <dtml-var public type='nb'>
works well.

https://github.com/zopefoundation/Products.ZSQLMethods/blob/master/src/Shared/DC/ZRDB/tests/test_results.py#L142

This test wants to use the built-in hash function to assert that the r class is hashable. This works under Python 2. Under Python 3 this happens:

Error in test test_record_hash (Shared.DC.ZRDB.tests.test_results.TestResults)
Traceback (most recent call last):
  File "/usr/local/Cellar/python3/3.6.1/Frameworks/Python.framework/Versions/3.6/lib/python3.6/unittest/case.py", line 59, in testPartExecutor
    yield
  File "/usr/local/Cellar/python3/3.6.1/Frameworks/Python.framework/Versions/3.6/lib/python3.6/unittest/case.py", line 601, in run
    testMethod()
  File "/Users/jens/src/Products.ZSQLMethods/src/Shared/DC/ZRDB/tests/test_results.py", line 145, in test_record_hash
    self.assert_(isinstance(hash(row), int))
TypeError: unhashable type: 'r'

I'm not sure what that test is trying to prove. Is it bogus?

(This is the last failing test on my local Python 3 compatibility branch)

#5 Shows that we seem to have a bad coverage in this package, so we should compute the coverage on the Travis runs and show them here using coveralls.io as in many other of the ZF repos.

When I test a SQL Method in Zope 4.1.1 ZMI I see the Rendered SQL query code if the query was successful.
That is very helpful.
That Rendered SQL query code is not shown in error case. There it would be even more helpful.

https://bugs.launchpad.net/products.zsqlmethods/+bug/143917

I want to fix this bug. (Any hints?)
Where I should start? Which path should I take?

It seems that

I tried to see if it is covered by tests but https://coveralls.io/github/zopefoundation/Products.ZSQLMethods just hast src/Products.

Hello,
i can create a connection to a Mysql Database, i can do some queries with the Test connection.
However, i can not create a Z SQL method: Neither add, add end edit, add and Test works.
It fails with some weird insuficient privileges. afterwards, the listing of the object in the zope administration interface shows that the zsql method is broken.

Is the zsql method working for zope 4.1, plone 5.3, python3.6?

I add the stack trace and the information on the system below.

Thanks for the help!

stack trace:

019-10-06 10:38:08,052 ERROR   [Zope.SiteErrorLog:251][waitress] 1570351088.05237980.47088826335173173 http://localhost:8080/Plone2/portal_skins/custom/ppp/manage_main
Traceback (innermost last):
  Module ZPublisher.WSGIPublisher, line 155, in transaction_pubevents
  Module ZPublisher.WSGIPublisher, line 337, in publish_module
  Module ZPublisher.WSGIPublisher, line 255, in publish
  Module ZPublisher.mapply, line 85, in mapply
  Module ZPublisher.WSGIPublisher, line 61, in call_object
  Module Shared.DC.Scripts.Bindings, line 335, in __call__
  Module Shared.DC.Scripts.Bindings, line 372, in _bindAndExec
  Module App.special_dtml, line 214, in _exec
  Module DocumentTemplate._DocumentTemplate, line 145, in render_blocks
  Module DocumentTemplate._DocumentTemplate, line 167, in render_blocks_
  Module DocumentTemplate._DocumentTemplate, line 376, in __getitem__
  Module DocumentTemplate._DocumentTemplate, line 400, in getitem
  Module App.Management, line 182, in manage_page_header
  Module Shared.DC.Scripts.Bindings, line 335, in __call__
  Module Shared.DC.Scripts.Bindings, line 372, in _bindAndExec
  Module App.special_dtml, line 214, in _exec
  Module DocumentTemplate._DocumentTemplate, line 145, in render_blocks
  Module DocumentTemplate._DocumentTemplate, line 237, in render_blocks_
  Module DocumentTemplate._DocumentTemplate, line 215, in render_blocks_
  Module DocumentTemplate._DocumentTemplate, line 376, in __getitem__
  Module DocumentTemplate._DocumentTemplate, line 388, in getitem
  Module DocumentTemplate._DocumentTemplate, line 306, in __getitem__
  Module Shared.DC.Scripts.Bindings, line 203, in __getattr__
zExceptions.unauthorized.Unauthorized: You are not allowed to access 'title_or_id' in this context

system information:
Plone 5.2.0 (5207)
CMF 2.4.0
Zope 4.1.1
Python 3.6.9 |Anaconda, Inc.| (default, Jul 30 2019, 13:42:17) [GCC 4.2.1 Compatible Clang 4.0.1 (tags/RELEASE_401/final)]
PIL 6.1.0 (Pillow)
WSGI: On
Server: waitress 1.3.0

I get this when trying to add a method in ZMI:

Resource not found

Sorry, the requested resource does not exist.
Check the URL and try again.

Resource: manage_manage_addZSQLMethod POST

BUG/PROBLEM REPORT / FEATURE REQUEST

What I did:

• Added a ZSQLMethod fullname_sql with
  select 'Willi Langenenberger' FULLNAME from dual

• Tested it with the "Test" tab

• Filled in the "Add Search Interface" form

What I expect to happen:

• The Search Interface to be added

What actually happened:

• Boom:

What version of Python and Zope/Addons I am using:

Zope-5.9
Python-3.9.5
Products.ZSQLMethods-4.0

Additional Info:

• Error message in Log:

ValueError: The input searchable object, <em>fullname_sql</em>,
                has not been tested.  Until it has been tested,
                it's output schema is unknown, and a report
                cannot be generated.  Before creating a report
                from this query, you must try out the query.  To
                try out the query, <a href="https://.../fullname_sql">click here</a>.

• Full Traceback:

2023-12-30 21:09:37,860 ERROR   [waitress:435][waitress-0]
 Exception while serving /VirtualHostBase/https/bach.wu.ac.at:443/VirtualHostRoot/_vh_zope4/_vh_prod2/wlang/manage_addProduct/ZSQLMethods/manage_addZSearch
Traceback (most recent call last):
  File "/opt/wu-wien/zope/buildouts/z5-dev-wl/eggs/waitress-2.1.2-py3.9.egg/waitress/channel.py", line 428, in service
    task.service()
  File "/opt/wu-wien/zope/buildouts/z5-dev-wl/eggs/waitress-2.1.2-py3.9.egg/waitress/task.py", line 168, in service
    self.execute()
  File "/opt/wu-wien/zope/buildouts/z5-dev-wl/eggs/waitress-2.1.2-py3.9.egg/waitress/task.py", line 434, in execute
    app_iter = self.channel.server.application(environ, start_response)
  File "/opt/wu-wien/zope/buildouts/z5-dev-wl/eggs/Paste-3.7.1-py3.9.egg/paste/translogger.py", line 77, in __call__
    return self.application(environ, replacement_start_response)
  File "/opt/wu-wien/zope/buildouts/z5-dev-wl/eggs/Zope-5.9-py3.9.egg/ZPublisher/httpexceptions.py", line 30, in __call__
    return self.application(environ, start_response)
  File "/opt/wu-wien/zope/buildouts/z5-dev-wl/eggs/Zope-5.9-py3.9.egg/ZPublisher/WSGIPublisher.py", line 391, in publish_module
    response = _publish(request, new_mod_info)
  File "/opt/wu-wien/zope/buildouts/z5-dev-wl/eggs/Zope-5.9-py3.9.egg/ZPublisher/WSGIPublisher.py", line 285, in publish
    result = mapply(obj,
  File "/opt/wu-wien/zope/buildouts/z5-dev-wl/eggs/Zope-5.9-py3.9.egg/ZPublisher/mapply.py", line 98, in mapply
    return debug(object, args, context)
  File "/opt/wu-wien/zope/buildouts/z5-dev-wl/eggs/Zope-5.9-py3.9.egg/ZPublisher/WSGIPublisher.py", line 68, in call_object
    return obj(*args)
  File "/opt/wu-wien/zope/buildouts/z5-dev-wl/develop-source/Products.ZSQLMethods/src/Shared/DC/ZRDB/Search.py", line 63, in manage_addZSearch
    raise ValueError(
ValueError: The input searchable object, <em>fullname_sql</em>,
                has not been tested.  Until it has been tested,
                it's output schema is unknown, and a report
                cannot be generated.  Before creating a report
                from this query, you must try out the query.  To
                try out the query, <a href="https://.../fullname_sql">click here</a>.

In the Zope 4 ZMI the "Test" tab (manage_testForm) is all jumbled. Fix it for the new Zope 4 ZMI.

According to https://stackoverflow.com/a/40877934/8531312 it could be replaced by six.reraise.

@hannosch In commit f328208, which was meant to be for PEP-8 compliance, you also changed the base class for Shared.DC.ZRDB.Aqueduct.Args to object where it did not have one before. This causes all ZSQL Methods created under Zope 2.13 and earlier to break:

2019-03-29 09:45:26 ERROR ZODB.Connection Couldn't load state for Products.ZSQLMethods.SQL.SQL 0x0358ec
Traceback (most recent call last):
  File "/opt/zope/.eggs/ZODB-5.5.1-py2.7.egg/ZODB/Connection.py", line 795, in setstate
    self._reader.setGhostState(obj, p)
  File "/opt/zope/.eggs/ZODB-5.5.1-py2.7.egg/ZODB/serialize.py", line 633, in setGhostState
    state = self.getState(pickle)
  File "/opt/zope/.eggs/ZODB-5.5.1-py2.7.egg/ZODB/serialize.py", line 626, in getState
    return unpickler.load()
TypeError: ('__init__() takes exactly 3 arguments (1 given)', <class 'Shared.DC.ZRDB.Aqueduct.Args'>, ())

Question: Was there a specific reason for this change? If not, I'd like to revert it unless you have a better suggestion. This is a blocker for Zope 4.

BUG/PROBLEM REPORT (OR OTHER COMMON ISSUE)

What I did:

I tried to use Z Search Interface via ZMI.

What I expect to happen:

I expected to get a generated template with a search form.

What actually happened:

Traceback (innermost last):
  Module ZPublisher.WSGIPublisher, line 162, in transaction_pubevents
  Module ZPublisher.WSGIPublisher, line 359, in publish_module
  Module ZPublisher.WSGIPublisher, line 262, in publish
  Module ZPublisher.mapply, line 85, in mapply
  Module ZPublisher.WSGIPublisher, line 63, in call_object
  Module Shared.DC.ZRDB.Search, line 57, in manage_addZSearch
TypeError: object of type 'map' has no len()

Looks like this code path was not tested for Python 3.

What version of Python and Zope/Addons I am using:

Zope 4.5.0
Python 3.7.4
Products.ZSQLMethods 3.11

Additional Info

This is the first time I tried to use this function - I did this in order to support the following question on the Plone forum:
https://community.plone.org/t/error-when-i-want-create-zsearchinterface/12985

The broken code has no coverage