Archive for the 'Zope' Category

Retrieve or return an output parameter from a Mysql stored procedure in Zope

Published
by
Greg
on April 2, 2006
in Databases, Programming and Zope
. 0 Comments

Zope 2.7.6 - Mysql 5.0.7. I have always thought that output parameters from stored procedures were not useable in Zope’s ZSQL methods, but a few minutes ago is suddenly occured to me that I could do it by using the “sql_delimiter” function in Zope. To be quick, put something like this in a ZSQL method:

call sp_test(@y)
dtml-var sql_delimiter
select @y as thecount

(put the <> around dtml-var sql_delimiter)

This works perfectly! I simply return a count in a simple sproc, but it’s exciting because this makes it even more useable! I LOVE ZOPE! (and now the MySQL has stored procedures, I LOVE MYSQL!)

Sample code if you want it:

— Here is the stored procedure —-
— This simply returns the parameter vout
— as a count.

DELIMITER $$

DROP PROCEDURE IF EXISTS `fb`.`sp_test`$$
CREATE PROCEDURE `fb`.`sp_test` (
out vout int
)
BEGIN
select count(*) into vout from blogs;
END$$

DELIMITER ;

— Here is the ZSQL Method —

call sp_test(@y)
dtml-var sql_delimiter
select @y as thecount

(put the <> around dtml-var)

Now you should note… for some reason, in MySQL Query Browser you can’t issue the commands:
call sp_test(@y);
select @y as thecount;

It wont work. But you can do that at he command line client just fine.

Also, you cannot do this AND return a recordset in the same procedure. It works from the command line client, but not in the ZSQL method. So, you will be limited to only return parameters this way.

Get ZMySQLDA to retrieve MySQL Stored Procedures

Published
by
Greg
on July 6, 2005
in Databases, Programming and Zope
. 0 Comments

Quick, before I forget!!

Tired of “can’t return a result set in the given context” errors when calling stored procedures in Mysql 5?

The ultra fast how-to on returning records from a multi-column multi-record stored procedure in Mysql 5 using Zope/Python!

In the ZMySQLDA, edit the db.py file and add the lines below in the _parse_connection_string function:


 #added by Greg Fischer retheoff at gmaildot com on 07/05/2005
 # Wanted an option to use 'client_flag' in connection string
 # to utilize MULTI_RESULTS and thereby retrieving full
 # recordsets from  Mysql 5.x
 # There is most likely a better way to implement this,
 # but this solved my isssue for now.  (what if you have
 # more than 1 client_flag?)
 if not items: return kwargs
 kwargs['client_flag'], items = int(items[0]), items[1:]
 # End addition


Like so…


def _parse_connection_string(self, connection):
kwargs = {'conv': self.conv}
items = split(connection)
self._use_TM = None
if not items: return kwargs
lockreq, items = items[0], items[1:]
if lockreq[0] == "*":
    self._mysql_lock = lockreq[1:]
    db_host, items = items[0], items[1:]
    self._use_TM = 1
else:
    self._mysql_lock = None
    db_host = lockreq
if '@' in db_host:
    db, host = split(db_host,'@',1)
    kwargs['db'] = db
    if ':' in host:
        host, port = split(host,':',1)
        kwargs['port'] = int(port)
    kwargs['host'] = host
else:
    kwargs['db'] = db_host
if kwargs['db'] and kwargs['db'][0] in ('+', '-'):
    self._try_transactions = kwargs['db'][0]
    kwargs['db'] = kwargs['db'][1:]
else:
    self._try_transactions = None
if not kwargs['db']:
    del kwargs['db']
if not items: return kwargs
kwargs['user'], items = items[0], items[1:]
if not items: return kwargs
kwargs['passwd'], items = items[0], items[1:]

#added by Greg Fischer retheoff at gmaildot com on 07/05/2005
# Wanted an option to use 'client_flag' in connection string
# to utilize MULTI_RESULTS and thereby retrieving full
# recordsets from  Mysql 5.x
# There is most likely a better way to implement this,
# but this solved my isssue for now.  (what if you have
# more than 1 client_flag?)
if not items: return kwargs
kwargs['client_flag'], items = int(items[0]), items[1:]
# End addition

if not items: return kwargs
kwargs['unix_socket'], items = items[0], items[1:]
return kwargs

Now, when you create your database connection object in Zope to Mysql 5, you use a slightly different connection string:
db user passwd client_flag
The client_flag is 131072 as specified in the CLIENT.py of MySQLdb for Python. (it didnt work for me to just use the constant MULTI_RESULTS.)
It will look like this:
adatabase auser apassword 131072

On Linux, this worked great. I cant get it to work on Windows. Probably need a newer version of _mysql.c from the mysql-python developers. (just a guess)

In Python, if you just want to connect, use the client_flag option in your connection string: (watch the wrapped lines)


cn = MySQLdb.Connection(db=adb, host=ahost,
user=auser, passwd=apw, client_flag=131072)

Worked great, again on Linux! But on Windows, I still get the “can’t return a result set in the given context” error unfortunately. If I find out how to get around that, I’ll post more.

This is a QUICK FIX! It is in know way the proper solution and will most likely cause some other problems, but it works and I needed the sprocs on Mysql! So, use at your own risk. And good luck!

Edit 7/06/05 2PM:
This *was* working. I ran in to a problem after creating a more complicated procedure using multiple statements. I then received an error in Zope stating something about releasing a lock. ?? I duno. I’ll test it in Python directly and see if it’s just my corny DA fix.

Edit 7/06/05 5PM:
Ok, it works, but I need to do more testing. You NEED TO USE A DASH in the connection string. Like so:
-adatabase auser apassword 65536

Also notice I am using 65536 as the client_flag. Using this specifies MULTI_STATEMENTS, and implies MULTI_RESULTS.

Configure and compile Python with Zlib

Published
by
Greg
on June 26, 2005
in Linux, Python and Zope
. 2 Comments

I just spent an hour trying to figure this out, AGAIN! To compile Zlib support into your Python interpreter, do this:

1. Install the Zlib-dev package for your distribution. (you probably have zlib already, but not the dev package)
On Ubuntu it’s called ‘zlib1g-dev’, on others it might just be: zlib-dev or zlib-devel. Do a search on your distro package lists to find it.
You should end up with a ‘zlib.h’ file in your system. In mine, Ubuntu, I have /usr/include/zlib.h

2. Get your python sources to compile and run the configure script like so:
./configure –with-zlib=/usr/include

See there that I included the ‘/usr/include’ directory to find zlib.h in? After the make && make install it worked great!

Keep in mind your mileage may vary, but this might help I hope. More importantly, now I can remember how I did this! :)

Why I use Zope and not ASP.net?

Published
by
Greg
on March 2, 2005
in Business, Networking, Programming, Web Design and Zope
. 0 Comments

In 2002, I saw an article on Devshed.com about Zope. What they described intrigued me greatly. The whole “Everything’s an object” idea was fascinating. I proceeded to www.zope.org and downloaded Zope version Soon, I was in heaven as I discovered a new passion.

Before I get into this too much, you should know that I am not a Software Engineer. I am not an educated person. I have no formal training or computer science degree. Everything I have learned regarding programming, I learned on my own using books, the Internet, and practice. The thoughts I express here are strictly based on my own observations, opinions, and desires.

I originally started web design learning HTML and Javascript, of course, but soon realized the importance of server side code. So I ran into ASP and coming from a little experience in VB 6, it was easy to pick up. Then I found Zope. As much as I liked and enjoyed working in ASP/VB, I still found some tasks to be a little cumbersome. After testing Zope, it just made sense. For about 2 years I worked in ASP off and on, then about 2 more years in Zope.

This isn’t a story about why I switched from ASP though, it’s about not using ASP.NET. I purchased VB.NET 2003 in the summer of 2004. For six months I worked with web forms in .NET. After that, I concluded that it was better for me to use Zope. The keywords here are “better for me.” I gave ASP.NET a shot because I wanted to be sure I was working with a good platform for me and my clients. Doing so allowed me to compare what I learned in the previous ASP and in Zope. (From here on out, I will call ASP.NET, .NET.)

.NET is a great platform, and it’s very powerful and capable. It’s also very widely used and has a huge community of support. I really can’t say anything negative about it, except that it is too big and complicated. The things I might say that are drawbacks, are not necessarily negative as all platforms or environments have drawbacks. One drawback in .NET is the size of it. Many times I have found myself drowning in the overload of information and NOT finding what I wanted. Another drawback would be, from a web development standpoint, deployment. Deploying web applications in .NET is a pain. You cannot simply copy your application directory from a test server to the production server without running into path issues, or maybe some odd configuration at the ISP’s IIS server. As a result, when my clients want a simple change, I put them off because I have to do so much more work just to test some code and publish it.

I love the Visual Studio IDE and Intellisense. (That is what it is called, right?) Also, having the full procedural language, and having it separate from the HTML code is nice. Although, I have since learned that I like the integrated DTML mixed right in with the HTML better, but that is really only my preference. (Sounds like another blog to write)

In .NET, the compiled code is a benefit because even as complex and bulky as the .NET framework is, it is quite efficient during runtime. (for those uninformed, in .NET you must compile your code. In the older ASP, you simple edited the text file and ran it.) This compiling of code is partly a blessing for its runtime speed, but during development and deployment, it’s a curse. It just slows you down.

I would suppose in a large scale, corporate application, the compiling of code and separation of code and presentation is a benefit. And I suppose that the .NET framework would work better for web applications with thousands of concurrent users. But, that’s not what I do. That’s not what my clients want or need. My clients have less than 100 employees. (way less) 99% of what they need does not require the full scale, bloated capability of .NET. In fact, actually in my opinion, I believe that everything a small business would need for web applications, can be accomplished using Zope with the same performance as .NET, overall. Though you might find some things are faster in .NET, some are faster in Zope, so *overall* it’s a wash.

Concerning the speed of development, I would have to say Zope is faster, *overall*. There are some things that are faster in .NET as Microsoft has done a decent job helping the developer along with several pre-built objects. The problem is that when you want to customize the pre-built objects you run into problems trying to figure out how to use them in your own customized way, and since you almost always need to customize to suit your clients’ needs, that slows down development time greatly. So in this respect, I would have to say Zope is faster to develop in, but not in all cases as I have mentioned.

One of Zope drawbacks is that in order to work with it and be efficient you have to understand it. You have to *get* it. There are some concepts you have to understand. I suppose that’s no different in learning things like database design or programming in general, so maybe it’s not really a drawback after all. The concepts I am referring to here are really the relationship between DTML documents and DTML methods, and all other objects in the Zope database. But that is the beauty of Zope isn’t it? Zope is an application server that is really a database serving web objects. Everything in that database is just that, an object. How they all relate is the magic. It’s magic because they give you this framework to build upon, you take all the objects, design them and piece them together, and BAM you have an application.

During development, you inevitably run into code that needs debugging. I am split on this one. .NET is pretty good for debugging code. It has nice call stacks listed if you want and of course the IDE helps by allowing you to step through the code and see what your variables contain during runtime. Zope also has tracebacks and error logs allowing you to find an error. Neither platform has improved my development process enough over the other, so I would have to say it’s a tie.

Management. Zope is hands down, no contest, the winner here. .NET, as I stated erlier requires some tinkering to deploy and the code requires compilation when changed. This is a real pain when you want to move fast. The Zope server is easy to manage and quite reliable. The objects you create and develop are easily imported and exported and moved around making deployment a snap. In addition to all this, if you want to make a fast text change on a site, heck even a quick coding change, you simply log into the Zope Management Interface from any web browser on the Internet and make your changes. The changes are immediately available and if you just happen to need to revert from those changes, Zope has versioning built in and you can rollback the changes.

DTML. There are many people out there who would say DTML is no good. I completely disagree, particularly from a small business perspective. That’s a whole other debate and blog, so from my perspective and keeping with the relevance of this article, I will keep this simple. DTML allows you to easily create your application around your HTML and provide dynamic content. It looks like HTML, in fact I would describe it as super-charged HTML that executes on the server. .NET separates your code from your HTML. If you like that, or need it in some cases, then Zope is not for you. (unless you use ZPT, but I don’t) Because DTML is mixed within your page content, it’s quite easy to follow. This in turn speeds my development time.

.NET might have a full-blown development environment giving you a lot of power, but Zope does have Python scripts, and if so desired, external methods and products. Because of this, I wouldn’t say .NET has any additional capability over Zope. I am not a computer science guy though, so someone might argue that this is not true, but from my perspective, and for my clients needs, it is true.

.NET does not have or run an object database or use acquisition. This is what gives Zope its power. And again, someone might argue that Zope’s acquisition is no good, but for me, it rocks! I haven’t had any problems with it. It’s just one of those things in Zope that need an understanding of the concepts of object relationship.

.NET needs Windows. Not that this is bad, but limits options and makes it more expensive. I like options, makes it more useful to my clients. But, you should know, my favorite server setup for Zope is to run it on Windows 2000 or 2003 with Microsoft’s SQL server using the Egenix Zope DA for ODBC. With version 8 of Postgresql, I might be moving towards that though.

Zope connects to any data source I have thrown at it, and it will run on other platforms than Windows. (Linux) On a downside, Zope needs a licensed ODBC adapter on Windows if you use Zope 2.7 or higher, and on Linux you need licensed ODBC if you want to connect to Microsoft SQL server. This is not true when using MySQL or Postgresql on Linux, there are free database adapters available.

Well, let’s recap. I’ll list the pros and cons as I see it and how they fit with my needs.

Zope Pros

  • I love DTML and how there is no separation of code and content.
  • Python scripts, external methods, and products.
  • Easy to manage and deploy. Nice Zope Management Interface useable from any web browser. This also makes quick updates, actually quick.
  • It can do everything I need and ASP.NET provides me with no feature I need over the features of Zope. In other words, for my needs, Zope can do anything .NET can.
  • Easy to learn.
  • Easy to extend.
  • Runs on Windows or Linux.
  • Object database with custom object properties and acquisition.
  • No compiling of code.
  • Extensive database connectivity.

Zope Cons

  • No IDE. This would be nice, but since it’s quite easy to use a decent text editor connecting to FTP or WebDAV, it’s not needed.
  • Debugging could be a little better, but I don’t really have problems with it. Tracebacks and event logs are easy to use.
  • Need a licensed ODBC adapter for running with MS SQL server. You need this with Zope 2.7 and higher, it’s worth every penny, but would be nice if there was newer, free one available.
  • Not as widely used as other technologies. (Zope server use on Netcraft.com has doubled from 2/04 to 2/05 though, so that’s nice)
  • Documentation is a little lacking. (this is getting much better though)

.NET Pros

  • Nice IDE for development. Even the Web Matrix IDE is nice, but I much prefer the Visual Studio one. At about $100 for the Standard version of VB.NET, I can handle it. (but that wont connect to the full SQL server, you need to use MSDE)
  • Nice debugging. This is partly due to the use of Visual Studio though.
  • Great for enterprise application development with many developers.
  • Many pre-built objects for development.
  • Huge user base.
  • Tons of documentation and usage examples.
  • Full VB or C# language available. (or others too)

.NET Cons

  • Too much information, too big. It’s often easy to get lost when trying to find a solution. This is a huge problem for me. When you do actually find the items you are looking for, it’s got pretty good documentation though.
  • Deployment is a pain.
  • Code needs to be compiled. (makes deployment harder as well as other things)
  • Quick updates are a pain, because of the compiling of code.
  • Pre-built objects are nice and are documented fully. Well, not fully, because if you need to do something customized, which you always do, then documentation is lacking. This is a big deal to me because trying to customize something takes way longer than it should. So, this is a con.
  • You must use Windows.
  • No object database or acquisition.

Just to be a little more complete too, here is why I don’t use ASP. (The older ASP)

  • No object database and acquisition.
  • Database operation is a little more of a pain. Not that it is not doable, just more difficult.
  • SSI is available for dropping in code objects, but not anywhere near as nice and easy as using Zope DTML Methods or Python Scripts.
  • While it is easier to deploy than .NET, and updates are quick, and it’s not compiled, there is no easy management interface and it’s not easily done from any web browser. Again, it’s just easier in Zope.
  • The code is sort-of integrated and useable like DTML, but DTML is way easier, particularly when using databases.
  • I wouldn’t say that ASP is exactly extensible.
  • The thing I really don’t like about ASP though, and this is completely based on my own experience, it is slightly unreliable and the code is slightly slow and clunky when running.

I hope this information is helpful to someone out there. This isn’t ment to be disparaging to Microsoft or ASP and ASP.NET, it’s just that Zope seems to fit with my preferences and needs. .NET is great, and I have a couple clients that will continue to use .NET so I’ll keep using it, but if I can help it, I’ll use Zope.

Enjoy!

Greg Fischer
1st Byte Solutions