What is the proper data structure for CAP::DBH?

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

I am moving websites to Pair.com from a dedicated server where local is the implied database host. So, I have never had to declare it and connections using CGI::Application::Plugin::DBH have looked like this and worked fine:

$self->dbh_config( 'DBI:mysql:'.
                      $self->config_param('mysql.database'),
                      $self->config_param('mysql.username'),
                      $self->config_param('mysql.password'),
                      {RaiseError => 1} );
[download]

However, Pair requires a host name. It might look like: vdb25b.pair.com and the database: 1945667_w

The docs for CGI::Application::Plugin::DBH give this for the data source

# use the same args as DBI->connect();
  $self->dbh_config($data_source, $username, $auth, \%attr);
[download]

And DBI docs suggest this format for the data structure

$data_source = dbi:DriverName:database=database_name;host=hostname
[download]

So, I have set up the connection to look like this:

my $data_source = 'DBI:mysql:'.
         $self->config_param('mysql.database').':'.
         $self->config_param('mysql.host');

   $self->dbh_config( 
         $data_source,
         $self->config_param('mysql.username'),
         $self->config_param('mysql.password'),
         { RaiseError => 1 }
   );
[download]

There is no error, it just hangs and I have to manually kill it.

In a straight up connection using DBI, this works fine:

   $dbh = DBI->connect( 
         $data_source,
         config_param('mysql.username'),
         config_param('mysql.password'),
         { RaiseError => 1 }
   );
[download]

Question: What is the data source look like when the host must be named when using CAP::DBH?

—Brad
"The important work of moving the world forward does not wait to be done by perfect men." George Eliot

Comment on What is the proper data structure for CAP::DBH? Select or Download Code

Replies are listed 'Best First'.
Re: What is the proper data structure for CAP::DBH? by hv (Prior) on Jan 18, 2023 at 02:44 UTC
You say: DBI docs suggest this format for the data structure `$data_source = dbi:DriverName:database=database_name;host=hostname` So, I have set up the connection to look like this: `my $data_source = 'DBI:mysql:'. $self->config_param('mysql.database').':'. $self->config_param('mysql.host');` [download] However the first part suggests it should rather look like this (note also the semicolon in the string): `my $data_source = 'DBI:mysql:database='. $self->config_param('mysql.database').';host='. $self->config_param('mysql.host');` [download] So maybe you should try that first. In fact I'd probably prefer to construct a string like that with sprintf: `my $data_source = sprintf 'DBI:mysql:database=%s;host=%s', $self->config_param('mysql.database'), $self->config_param('mysql.host');` [download]	[reply] [d/l] [select]
Re^2: What is the proper data structure for CAP::DBH? by bradcathey (Prior) on Jan 18, 2023 at 13:44 UTC
Thanks for the suggestions. Unfortunately, no form of those work. It just hangs. No error message even though I have set: `use CGI::Carp qw(fatalsToBrowser);` I tried with both a semi-colon and colon between the database name and host name but no joy. Just to be sure, Data Dumper showed: `'DBI:mysql:highgatevps_kcmach;vdb2b.pair.com'` [download] I'll reach out to Pair and see if their logs show the issue (nothing in the logs they provide for users). —Brad "The important work of moving the world forward does not wait to be done by perfect men." George Eliot	[reply] [d/l] [select]
Re^3: What is the proper data structure for CAP::DBH? by hippo (Archbishop) on Jan 18, 2023 at 13:53 UTC
`'DBI:mysql:highgatevps_kcmach;vdb2b.pair.com'` The keywords `database` and `host` are still missing. 🦛	[reply] [d/l] [select]
Re^4: What is the proper data structure for CAP::DBH? by bradcathey (Prior) on Jan 18, 2023 at 20:04 UTC
Re^5: What is the proper data structure for CAP::DBH? by kcott (Archbishop) on Jan 18, 2023 at 21:25 UTC
Re: What is the proper data structure for CAP::DBH? by tangent (Parson) on Jan 18, 2023 at 22:26 UTC
You mention that you are able to create a connection using straight DBI. In the docs for CGI::Application::Plugin::DBH it has: You can either pass in an existing DBI database handle, or provide the usual parameters used for DBI->connect() If you haven't tried this already, pass in an existing handle and see if it works - will help narrow the problem. `my $dbh = DBI->connect( ... ); if (not $dbh) { die "Could not connect to DB: ", $DBI::errstr; } $self->dbh_config($dbh);` [download]	[reply] [d/l]
Re^2: What is the proper data structure for CAP::DBH? by bradcathey (Prior) on Jan 20, 2023 at 00:58 UTC
I didn't do this exactly, but it prompted me to abstract the code and strip away everything but the salient stuff and I found the error which was simply a bad path to a `use lib`. Embarrassed but I learned two lessons: abstract the issue, and learn all about `sprintf`. Thanks all for your help. —Brad "The important work of moving the world forward does not wait to be done by perfect men." George Eliot	[reply] [d/l] [select]
Re: What is the proper data structure for CAP::DBH? by LanX (Saint) on Jan 18, 2023 at 21:28 UTC
It seems to me like you have a `<blockquote>` which is never closed in your post, making the thread look weird. `... and worked fine:</p> <blockquote><code> $self->dbh_config( ... ^^^^^^^^^^^^` [download] Could you please fix this by editing your post? edit I just noticed you have a `</blockquote>` inside the end of just that code-block. `... {RaiseError => 1} );</blockquote></code> <p>However ... ^^^^^^^^^^^^^` [download] that's causing the problem. update one of the gods edited it in the meantime Cheers Rolf _{(addicted to the 𐍀𐌴𐍂𐌻 Programming Language :) Wikisyntax for the Monastery}	[reply] [d/l] [select]

edit

update