How to fix Z SQL Method test problem

I believe that error is related to the fact that you are using Chinese field names in your 'item' table. The ZMySQLDA database adapter does not provide a way to to set the MySQL character set, so the only solution is to add a line to the _begin method in ZMySQLDA/db.py: self.db.query("set character set utf8") I've done that for you, so your queries should work now.

http://www.eng.ox.ac.uk/Plone/developing-for-plone/connecting-to-mysql

Conversation
Posted On: 31 May 2010 10:16 AM

Hello,

> Thanks a lot it fixed well.

On behalf of Sean, you are welcome.

> Could you please let me know where the file "ZMySQLDA/db.py" located in?

The file was:
/home/kyholdings/webapps/xsara/zinstance/products/ZMySQLDA/db.py

Here:
def _begin(self, *ignored):
self.db.query("set character set utf8")
self.db.query("BEGIN")
self.db.store_result()

Note, you can search for all files named "db.py" in "~/webapps/" using:

find ~/webapps/ -name 'db.py'

> By the way, I want to learn how to display the list result as a page also need show a special form as my design. Could you please let me know how to do it?

Zope includes a way to take database results, give you a list, and let you put them into your web page templates. Since you can edit the HTML, you can make forms or lists; anything you need.

The documentation about databases is here:
http://docs.zope.org/zope2/zope2book/RelationalDatabases.html

There is a lot of information, but it should help you understand how to take information out of the database and show it on the web page, or in a form, or anywhere else.

Hope that helps!

Regards,

Ryan S.
WebFaction Support
--
WebFaction - Smarter web hosting
http://webfaction.com - http://twitter.com/webfaction
kyholdings   
Posted On: 31 May 2010 04:23 AM

Hi,

Thanks a lot it fixed well.
Could you please let me know where the file "ZMySQLDA/db.py" located in?

By the way, I want to learn how to display the list result as a page also need show a special form as my design. Could you please let me know how to do it?

Thanks
Alex
Sean F   
Posted On: 30 May 2010 03:54 PM

Hi Alex,

> It is really difficult to ask question by mailing llist.
> I'd rather want to get anwser from yours.
> Please talk with your staff and help me to find the solution early.

I just tried the following steps via http://admin.kyholdings.webfactional.com/manage

> add Z SQL Methord named list_all_item
> Connection Id: MYSQL_database_connection
> SELECT * FROM `item`
> Click add and test
> show
> list_all_item Input Data
> This query requires no input.
> click Submit Query

I did not get a '502 Bad Gateway' error. Instead, I got an error that said:

"Duplicate column name, ????" 

I believe that error is related to the fact that you are using Chinese field
names in your 'item' table.

The ZMySQLDA database adapter does not provide a way to to set the MySQL
character set, so the only solution is to add a line to the _begin method in
ZMySQLDA/db.py:

self.db.query("set character set utf8")

I've done that for you, so your queries should work now.

Please let us know if you have any further questions.

Regards,

Sean F.
WebFaction Support
--
WebFaction - Smarter web hosting
http://webfaction.com - http://twitter.com/webfaction

kyholdings   
Posted On: 30 May 2010 07:10 AM

Hi

It is really difficult to ask question by mailing llist.
I'd rather want to get anwser from yours.
Please talk with your staff and help me to find the solution early.

Thanks
Alex
David L   
Posted On: 29 May 2010 03:22 PM

Hi,

> Not yet my friend.
> Could you please let me know how to contact the Zope mailing lists?

The official Zope mailing lists are located here: http://www.zope.org/Resources/MailingLists


Hope this helps :)


Regards,

David L.
WebFaction Support
--
WebFaction - Smarter web hosting
http://webfaction.com - http://twitter.com/webfaction
Alex Zhang   
Posted On: 29 May 2010 02:42 PM

Not yet my friend.
Could you please let me know how to contact the Zope mailing lists?



2010/5/29 WebFaction Support <support@webfaction.com>

> Hi,
>
> > When I login to the phpMyAdmin, search function works well
>
> From what I can tell (and going on the basis that phpMyAdmin is working
> correctly) it looks like the problem is coming from within Zope itself.
>
> I'm not familiar enough with the internals of Zope to find out where the
> problem is occurring, but the Zope developers should know.
>
> Have you tried contact the Zope user mailing lists?
>
>
> Regards,
>
> David L.
> WebFaction Support
> --
> WebFaction - Smarter web hosting
> http://webfaction.com - http://twitter.com/webfaction
>
> Ticket Details
> ===================
> Ticket ID: BXI-287981
> Department: Support
> Priority: Urgent
> Status: Waiting for reply from customer
>
> You can check the status of or reply to this Ticket online at:
> https://help.webfaction.com/index.php?_m=tickets&_a=viewticket&ticketid=384808
> Please do let us know if we can assist you any further,
>
> WebFaction
>
>
>


-- 
Yours Sincerely
Alex Zhang
KY HOLDINGS LIMITED
10/F INT'L BLDG(E) 369 SHUANGXI WEST RD. JINHUA ZHEJIANG CHINA
TEL: +86 579 8239 0958; 8239 8500
FAX: +86 579 823 8501
E-mail: buyerclub.cn@gmail.com
http://freeplone3.openia.net/expander
http://kyholdings.objectis.net
http://www.kyholdings.com
MSN: zjsources@hotmail.com
Skype:buyerclub.cn

David L   
Posted On: 29 May 2010 02:39 PM

Hi,

> When I login to the phpMyAdmin, search function works well

From what I can tell (and going on the basis that phpMyAdmin is working
correctly) it looks like the problem is coming from within Zope itself.

I'm not familiar enough with the internals of Zope to find out where the 
problem is occurring, but the Zope developers should know.

Have you tried contact the Zope user mailing lists?


Regards,

David L.
WebFaction Support
--
WebFaction - Smarter web hosting
http://webfaction.com - http://twitter.com/webfaction
kyholdings   
Posted On: 29 May 2010 02:09 PM

When I login to the phpMyAdmin, search function works well 

Attachments a.JPG (168.02 KB)
kyholdings   
Posted On: 29 May 2010 02:03 PM

If I change the language from English to Chinese,
Clik the Submit Query,
show:
Site Error
An error was encountered while publishing this resource. 

ValueError

(Also, the following error occurred while attempting to render the standard error message, please see the event log for full details: 'ascii' codec can't encode characters in position 3352-3355: ordinal not in range(128))Traceback (innermost last): Module ZPublisher.Publish, line 202, in publish_module_standard Module ZPublisher.Publish, line 150, in publish Module plone.app.linkintegrity.monkey, line 21, in zpublisher_exception_hook_wrapper Module Zope2.App.startup, line 221, in zpublisher_exception_hook Module ZPublisher.Publish, line 119, in publish Module ZPublisher.mapply, line 88, in mapply Module ZPublisher.Publish, line 42, in call_object Module Shared.DC.ZRDB.DA, line 343, in manage_test Module Shared.DC.ZRDB.DA, line 321, in manage_test Module Shared.DC.ZRDB.DA, line 515, in __call__ - Module Shared.DC.ZRDB.Results, line 50, in __init__ ValueError: (see above) 
--------------------------------------------------------------------------------

Troubleshooting Suggestions

The URL may be incorrect. 
The parameters passed to this resource may be incorrect. 
A resource that this resource relies on may be encountering an error. 
For more detailed information about the error, please refer to the error log. 

If the error persists please contact the site maintainer. Thank you for your patience. 
kyholdings   
Posted On: 29 May 2010 02:00 PM

What's the mean:headers

I add Z SQL Methord named list_all_item
Connection Id: MYSQL_database_connection
SELECT * FROM `item`
Click add and test
show
list_all_item Input Data
This query requires no input.
click Submit Query
show
502 Bad Gateway

--------------------------------------------------------------------------------

nginx
David L   
Posted On: 29 May 2010 01:49 PM

Hi,

> There are 1780 line on the item table. You mean it is too big?
> But I change the Query Template: SELECT * FROM item
> as
> SELECT * FROM item where (ConNo=169103)
> Also same problem.
> How can I fix the problem?

It's not that your table is too big, but that the headers that Zope is setting
for each response are far too large.

Could you possible provide us with step to recreate the problem?


Regards,

David L.
WebFaction Support
--
WebFaction - Smarter web hosting
http://webfaction.com - http://twitter.com/webfaction
kyholdings   
Posted On: 29 May 2010 01:14 PM

There are 1780 line on the item table. You mean it is too big?
But I change the Query Template: SELECT * FROM item 
as
SELECT * FROM item where (ConNo=169103)
Also same problem.
How can I fix the problem?

Please help me with thanks
David L   
Posted On: 29 May 2010 12:59 PM

Hi,

> immediately 

Ah, OK. 

The problem is that your application is sending very, very large headers 
whenever you run your query.

Here's an extract from your error log:

> upstream sent too big header while reading response header from upstream


Do you know which headers your application is sending?



Regards,

David L.
WebFaction Support
--
WebFaction - Smarter web hosting
http://webfaction.com - http://twitter.com/webfaction
kyholdings   
Posted On: 29 May 2010 12:42 PM

immediately
David L   
Posted On: 29 May 2010 11:25 AM

Hi,

> I have imported dump file to mysql_database.
> There is a tabel named item
> I add Z SQL Method id: list all item
> Connection Id : MYSQL_database_connection
> empty Arguments
> Query Template: SELECT * FROM item
> Click :add and test
> show :This query require no input.
> Click Submit Query
> Show: 502 Bad Gateway
> nginx

> Please help me to fix the problem with thanks

Do you see the nginx error immediately or after a period of waiting?


Regards,

David L.
WebFaction Support
--
WebFaction - Smarter web hosting
http://webfaction.com - http://twitter.com/webfaction
kyholdings   
Posted On: 29 May 2010 11:17 AM

Hi,

I have imported dump file to mysql_database.
There is a tabel named item
I add Z SQL Method id: list all item
Connection Id : MYSQL_database_connection
empty Arguments
Query Template: SELECT * FROM item 
Click :add and test
show :This query require no input.
Click Submit Query
Show: 502 Bad Gateway
nginx

Please help me to fix the problem with thanks
Alex