Mason & DBI driving me nuts

jfroebe has asked for the wisdom of the Perl Monks concerning the following question:

Hi,

I'm new to Mason and am having trouble printing a simple html table pulling data from a table on a database. I know this has to do with scope but there has to be an easier way other than putting the entire dbms call (including printing the table) within a perl code section.

One thing I have noticed is the apparent lack of any useful information of using Mason with DBI connections.

Any clues?

thanks

<%once>
    use DBI;
</%once>

<%shared>
    my $serverName;
    my $dumpdate;

    my $dbh = DBI->connect("dbi:Sybase:server=mydb", 'info', 'infopwd'
+, { RaiseError => 1, PrintError => 1 } );
    $dbh->do("use sybase_dba");

    my $query = "select serverName, min(dumpdate) from dbLastBackup gr
+oup by serverName";

    my $sth = $dbh->prepare($query);
    $sth->execute;
    $sth->bind_columns(undef, \$serverName, \$dumpdate);
</%shared>

<table>
    <th>Server Name</th><th>backup date</th>

    <% while ($sth->fetch) { %>
        <tr><td><% $serverName %></td><td><% $dumpdate %></td></tr>
    <% } %>
</table>
[download]

Update: This is how you do it:

<%shared>
    my %serverDump;

    my $dbh = DBI->connect("dbi:Sybase:server=mydb", 'info', 'infopwd'
+, { RaiseError => 1, PrintError => 1 } );
    $dbh->do("use sybase_dba");

    my $query = "select serverName, min(dumpdate) from dbLastBackup gr
+oup by serverName";

    my $sth = $dbh->prepare($query);
    $sth->execute or die "Error: unable to run query! " . $dbh->errstr
+;
</%shared>

<TABLE Border=1>
% while (my $row = $sth->fetchrow_arrayref ) {
<TR>
<TD><% $row->[0] %></TD><TD><% $row->[1] %></TD>
</TR>
% }
</TABLE>
[download]

Note, don't forget to uncomment "PerlSetVar MasonErrorMode fatal". For some reason, no errors were being sent to the browser. :(

Jason L. Froebe

Team Sybase member

No one has seen what you have seen, and until that happens, we're all going to think that you're nuts. - Jack O'Neil, Stargate SG-1

Comment on Mason & DBI driving me nuts Select or Download Code

Replies are listed 'Best First'.
Re: Mason & DBI driving me nuts by johnnywang (Priest) on Jul 31, 2006 at 19:33 UTC
what errors are you getting? that might give us more clue.	[reply]
Re^2: Mason & DBI driving me nuts by jfroebe (Parson) on Jul 31, 2006 at 19:47 UTC
I wasn't getting any errors.. which was the most frustrating bit. :( Jason L. Froebe Team Sybase member No one has seen what you have seen, and until that happens, we're all going to think that you're nuts. - Jack O'Neil, Stargate SG-1	[reply]
Re^3: Mason & DBI driving me nuts by johnnywang (Priest) on Jul 31, 2006 at 20:26 UTC
You said "having trouble printing a simple html table", which troubel? It shows an empty page? it shows a partial page (how much)? it hangs? what's in apache's error log? etc. more information.	[reply]
Re^4: Mason & DBI driving me nuts by jfroebe (Parson) on Jul 31, 2006 at 20:30 UTC
Re: Mason & DBI driving me nuts by EvanCarroll (Chaplain) on Aug 01, 2006 at 01:07 UTC
Having developed many Mason, projects I can share some quick words of wisdom. Mason isn't fun for large projects, and large is subjective, if you think of your own undertaking as large, you might want to look into a tool that lends itself to better code, ie an mvc type of tool Learn the ins and outs of Mason - buy the Mason book the whole framework is a cinch to down, and get running If you chose not use an ORM consider using $sth->fetchrow_hashref - for clarity when possible Move the connect string out of the mason root!!! Use Apache::DBI, or when you fail to connect, Mason will stack dump w/ source (default) and spit out your pw Use autocommit=>0 (dbi param, makes you $dbh->commit, and $dbh->rollback - the right way) Make $dbh a PerlGlobal, consider another PerlGlobal ($s) for the session, I also use one more, ($u for the user), it is silly to pass these around to comps or to reinvent the wheel on every invocation - Every modern framework, agrees in putting these into a global (globals are declared with PerlGlobal, and NOT localized/initialized anywhere in your code, only set to different variables) If you plan on doing web development, you will probably move away from Mason at some point, MVC really is an incredibly better way to design. I should have my session code, and root autohandlers on PM if I don't and you want that code, (my mason based framework I used for many years, you can have it) Evan Carroll www.EvanCarroll.com	[reply]
Re^2: Mason & DBI driving me nuts by doom (Deacon) on Aug 01, 2006 at 02:55 UTC
Can you explain what you mean by an "MVC"? Just list a few examples. Myself, I would say that Mason is one way of implementing the model-view separation. (My suspicion is that you're talking about web development frameworks based on object-relational mappers like Class::DBI, in which case, I disagree strongly with your claim that they're the right way to develop "large" projects. They may be quick, they may be fun, but scale, they do not.)	[reply]
Re^3: Mason & DBI driving me nuts by EvanCarroll (Chaplain) on Aug 01, 2006 at 20:17 UTC
MVC is an all around more rigidly structured way of handling the web, it tends to yield better code on the whole, while being easier to divvy out amonst a team. It is more so maintainable and scalable. You have three fine divides, a model - interacts with the database (makes the data accessible in your language of choice), a controller which can access a model and holds all of your programming logic, and a view which simply reads from the context variable - catalyst's stash, rail's flash/other misc custom globals, etc. More than one medium can access the model, the interface to the db in perl, Class::DBI, DBIx::Class custom DBI modules etc, in our implementation, the models are recycled and I use the same modules when inserting data with web crawlers, that i do when users add the data themselves. The controller is often kind of like a server side http/rpc, and can be largely reused, http://foobar.com/1/2 will in most implementations dispatch to the function '1', the arg '2', in Catalyst you can form this 10900498084 different ways, you could in the path '1/2/3/4', send the function 1, the value 2, the function 3 the value 4, etc, these functions are written in the controller logic view is any of the many template languages available to what ever lang you pick. TT, embperl, etc these only deal with presentation logic. The best Mason users, found on the mailing lists, will highlight these points in their methodologies, which largely mimic MVC now too. they often put their model functions in modules, isolated from the .mas files. A dhandler in mason is easily abstracted to be a controller, and the autohandler and seamless calling chain are the powerhouse of the language. If you start doing this too, and then you pick up a true MVC framework, you might come to view Mason as less developed -- I have. Evan Carroll www.EvanCarroll.com	[reply]
Re^4: Mason & DBI driving me nuts by doom (Deacon) on Aug 05, 2006 at 10:48 UTC
Re^2: Mason & DBI driving me nuts by johnnywang (Priest) on Aug 01, 2006 at 05:25 UTC
I think you meant frameworks like CGI::Application, Maple, Catalyst, etc. That's what I usually use. The parallel distinction in the java world is jsp vs. frameworks like Struts. I only use Mason occasionally, but was told that Amazon mainly use Mason, it then must be "good enough"?	[reply]
Re^3: Mason & DBI driving me nuts by EvanCarroll (Chaplain) on Aug 01, 2006 at 20:04 UTC
Amazon doesn't use Mason externally, they use it internally to "prototype." Many books have clairfied this. Evan Carroll www.EvanCarroll.com	[reply]
Re^4: Mason & DBI driving me nuts by doom (Deacon) on Aug 05, 2006 at 10:55 UTC
Re: Mason & DBI driving me nuts by jZed (Prior) on Jul 31, 2006 at 19:06 UTC
Why do you have an undef as the first bind_column? You are binding three columns but only selecting two. What error message or undesired behaviour do you get?	[reply]
Re^2: Mason & DBI driving me nuts by jfroebe (Parson) on Jul 31, 2006 at 19:17 UTC
That is the syntax of bind_columns. Jason L. Froebe Team Sybase member No one has seen what you have seen, and until that happens, we're all going to think that you're nuts. - Jack O'Neil, Stargate SG-1	[reply]
Re^3: Mason & DBI driving me nuts by jZed (Prior) on Jul 31, 2006 at 19:33 UTC
Um, no it is not the syntax of bind_columns. To quote the DBI docs: The list of references should have the same number of elements as the number of columns in the SELECT statement. You have three elements in the bind_columns and only two columns in your SELECT statement. You still haven't told us what the warning, error message, or unexpected behaviour is, so it's hard to guess what is going on. update Oops, reading further in the DBI docs, I see it does allow an ignorable undef as the first param, sorry: For compatibility with old scripts, the first parameter will be ignored if it is undef or a hash reference.	[reply]
Re^4: Mason & DBI driving me nuts by jfroebe (Parson) on Jul 31, 2006 at 19:45 UTC
Re: Mason & DBI driving me nuts by philcrow (Priest) on Jul 31, 2006 at 18:59 UTC
I haven't used Mason in a while, but there is a book I read when I did: http://www.masonbook.com/book/. It covers use of the DBI in Mason and is free on-line. Phil	[reply]
Re^2: Mason & DBI driving me nuts by jfroebe (Parson) on Jul 31, 2006 at 19:18 UTC
umm.. exactly where in the book? I see very little other than "use Apache::DBI" and references to a global dbh. Jason L. Froebe Team Sybase member No one has seen what you have seen, and until that happens, we're all going to think that you're nuts. - Jack O'Neil, Stargate SG-1	[reply]
Re^3: Mason & DBI driving me nuts by jfroebe (Parson) on Aug 12, 2006 at 15:25 UTC
I've been gone a week so I eventually did find the DBI code in the Mason book. The problem is the index being incomplete. Jason L. Froebe Team Sybase member No one has seen what you have seen, and until that happens, we're all going to think that you're nuts. - Jack O'Neil, Stargate SG-1	[reply]