Mason & DBI driving me nuts

[jfroebe]

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>

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>

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

[EvanCarroll]

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)

[doom]

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.)

[EvanCarroll]

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.

[johnnywang]

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"?

[EvanCarroll]

Amazon doesn't use Mason externally, they use it internally to "prototype." Many books have clairfied this.

[johnnywang]

what errors are you getting? that might give us more clue.

[jfroebe]

I wasn't getting any errors.. which was the most frustrating bit. :(

[johnnywang]

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.

[jZed]

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?

[jfroebe]

That is the syntax of bind_columns.

[jZed]

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.

[philcrow]

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

[jfroebe]

umm.. exactly where in the book? I see very little other than "use Apache::DBI" and references to a global dbh.

[jfroebe]

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.