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

I'm starting with Mojolicious.

I need to make a html form that can be used to retrieve records from a mysql database. The database is on a server and I have to connect to and retrieve a database handler. For now, I'm able to display the form and check the values send. I still have to talk to the database but I'm stuck with doing the connection. Here is my code 

use Mojolicious::Lite;
use Mojolicious::Plugin::Database;
use Data::Dumper;

# Route with placeholder
get '/invtot' => sub {
  my $c   = shift;
  # my $foo = $c->param('foo');
  # $c->render(text => "Hello from $foo.");
  $c->render("invtot");

post '/invtot' => sub {
    my $c = shift;
    my $p = $c->req->body_params->to_hash;
    print Dumper $p;
}  
};

sub startup {
    my $self = shift;
    print "startup\n";
    $self->plugin('database', {
        dsn      => 'DBI:mysql:host=mysql....',
        username => 'd......r',
        password => '.....',
        options  => { 'pg_enable_utf8' => 1, AutoCommit => 1, PrintErr
+or =>0, RaiseError => 1 },
        helper   => 'db',
        });
}
 
# Start the Mojolicious command system
app->start;
__DATA__

@@ invtot.html.ep
<!DOCTYPE html>
<html>
  <head><title>Paper Journals from the library</title></head>
  <body>
    <h1>Paper Journals from the library</h1>
    <p>Select Title, ISSN, or Domain. Domains are 'Earth Sciences','Ma
+thematics','Life Sciences','Chemistry','Physics','Environmental Scien
+ces</p>
<form name='fSearch' method='post' action=invtot>
<table width='50%' border='0'>
 <tr> 
 <td valign='top'> <select name='ZLField'>
  <option value='jrn.ti' selected>Title</option>
  <option value='dom'>Domain</option></select></td>
  <td valign='top'><select name='ZLMode'>
  <option value='1' selected>Begin with</option>
  <option value=2>Anywhere in</option>
  <option>Exact matching</option>
  </select>
  </td>
  <td valign='top'> 
  <input type='text' name='ztSearch' size='40' maxlength='100'>
  </td>
  </tr>
   </table>
  <table border='0'>
  <tr><td valign='top'>
Or enter some words from the title in any order. Use \" to search a ph
+rase, and * for truncation. Choose AND to have all the words, OR to h
+ave any of them.
  <input type='text' name='ztwords' size='40' maxlength='100'>
  <input type='radio' name='ccbool' value='AND' checked>AND
  <input type='radio' name='ccbool' value='OR'>OR
  <input type='radio' name='ccabo' value='ABO'>ABO     
  </td>
  </tr><tr>
  <td> 
  <br><input type='submit' name='Search' value='Send' id='mySubmit'>
      <input type="reset" name="Submit2" value="Reset" id="myReset">
      </td>
      </tr>
   </table>
    </form>
  </body>
</html>

[download]

The startup function is taken directly from the documentation of Mojolicious::Plugin::Database but it is not called. And even if it were, how do I retrieve a $dbh I could prepare my sql queries with ?

Update

After reading the doc for different plugins, I have this

use Mojolicious::Lite;
use Mojolicious::Plugin::Database;
use Data::Dumper;

plugin 'Database' => {
        dsn      => 'dbi:MySQL:host=mysql...:dbname=d...',
        username => 'd...',
        password => '....',
        options  => { 'pg_enable_utf8' => 1, AutoCommit => 1, PrintErr
+or =>0, RaiseError => 1 },
        helper   => 'db',
        };

# Route with placeholder
get '/invtot' => sub {
  my $c   = shift;
  # my $foo = $c->param('foo');
  # $c->render(text => "Hello from $foo.");
  $c->render("invtot");

post '/invtot' => sub {
    my $c = shift;
    my $p = $c->req->body_params->to_hash;
    print Dumper $p;
    print Dumper $c->app->attr('_dbh_db')
}  
};


# Start the Mojolicious command system
app->start;
__DATA__
#as above

[download]

But the page is not even load...

Edit2 With a corrected syntax, the page load.... But I'm still stuck with retrieving the $dbh

frazap

Replies are listed 'Best First'.
Re: How can I get a database handler with Mojolicious::Plugin::Database
by Veltro (Hermit) on Apr 03, 2019 at 08:17 UTC

    Hi frazap

    I thought that the way how they solved it in this example was pretty neat:

    https://github.com/tempire/MojoExample/blob/master/lib/MojoFull.pm

    They set up a helper 'db' inside of the startup method that handles the connection.

    Hope this helps,

    Veltro

    edit And this is how I set up my connection:

        my $schema = <YourSchemaLocation>::Schema->connect(
        "DBI:mysql:database=<DatabaseName>;host=localhost",
        "<LoginName>",
        "<Password>",
        {
            'RaiseError' => 1,
            'quote_char' => '`', # Needed in case you use reserved sym
+bols such as: 'group' is a reserved symbol
            'name_sep'   => '.'
        }
    ) ;
    # In case you want debugging
    # $schema->storage->debug( 1 ) ;

    [download]

    Replace <YourSchemaLocation> with your schema, <DatabaseName> with your database, <LoginName> and <Password> with the MySQL credentials that you use.

    edit 2: Two more links that could be of interest for you:

[reply]
[d/l]
[select]
      Hi Veltro

      Thanks for the suggestion. Eventually, the database handler was found with $c->app->_dbh_db(); Strange that the doc of the module does not describe this...

      I'm that code, which works 
      use Mojolicious::Lite;
use Mojolicious::Plugin::Database;
use Data::Dumper;

plugin 'Database' => {
        dsn      => 'dbi:mysql:host=m...h:dbname=d....',
        username => 'd....r',
        password => '.....',
        options  => { 'pg_enable_utf8' => 1, AutoCommit => 1, PrintErr
+or =>0, RaiseError => 1 },
        helper   => 'db',
        };

get '/invtot' => sub {
  my $c   = shift;
  $c->render("invtot");
};

post '/invtot' => sub {
    my $c = shift;
    my $p = $c->req->body_params->to_hash;
    print Dumper $p;
    print Dumper $c->app->_dbh_db();
    my $dbh = $c->app->_dbh_db();
    my $st = $dbh->prepare("SELECT * FROM jrn WHERE ti like ? ORDER BY
+ tri ASC") or die $dbh->errstr ;
    if (length $p->{'ztSearch'} > 0) {
        $st->execute($p->{'ztSearch'} . '%');
        my $ar;
       while ($ar = $st->fetchrow_arrayref) {
           my @values = map {defined $_ ? ($_) : ('') } @$ar;
           print join(" ", @values), "\n";
   
         }
       $st->finish;
   }

};  

app->start;
__DATA__
#as above

      [download]

      Now, I would prefer to have my statements prepared as the start of the application, one time after the connection is made, not at each page displayed. What's the best way to do this?

      frazap

[reply]
[d/l]
[select]
        the database handler was found with $c->app->_dbh_db();

        The underscore often implies that the developer of the module meant these as 'private' (or internal) functions (and therefor may not be documented). I believe that that is also the case here. I looked at the source and there is definitely a helper created by the name 'db'. Try to use the method from the previous link that I gave you: A Simple Mojolicious/DBI Example

[reply]

        If you are content with preparing them on the first invocation, you can use either ->prepare_cached or use the state keyword:

        post '/invtot' => sub {
    state $st = $dbh->prepare(...);
    $st->execute(...);
};

        [download]
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom