in reply to Re^5: DBI placeholders for spatial data
in thread DBI placeholders for spatial data

I think this is the important part of the documentation

As a convenience, if the $data_source parameter is undefined or empty, the DBI will substitute the value of the environment variable DBI_DSN. If just the driver_name part is empty (i.e., the $data_source prefix is "dbi::"), the environment variable DBI_DRIVER is used. If neither variable is set, then connect dies.

I guess that the environment variable DBI_DRIVER is set to mysql as that is the RDBMS installed on the server.

Replies are listed 'Best First'.
Re^7: DBI placeholders for spatial data
by afoken (Canon) on Jun 27, 2021 at 00:06 UTC

    I think this is the important part of the documentation

    As a convenience, if the $data_source parameter is undefined or empty, the DBI will substitute the value of the environment variable DBI_DSN. If just the driver_name part is empty (i.e., the $data_source prefix is "dbi::"), the environment variable DBI_DRIVER is used. If neither variable is set, then connect dies.

    The first sentence does not apply, because $data_source is neither undefined nor empty. The second sentence does not apply, because $data_source does not start with "dbi::". The third sentence does not apply, because neither the first nor the second sentence applied.

    I guess that the environment variable DBI_DRIVER is set to mysql as that is the RDBMS installed on the server.

    Guess? How about printing the environment instead of guessing? Run env or perl -e 'print "$_=$ENV{$_}\n" for keys %ENV' from a shell for a list of all environment variables; perl -e 'print "$_=$ENV{$_}\n" for sort grep /^DBI_/,keys %ENV' to just list those starting with DBI_. From a CGI context, run something like this:

    #!/bin/bash echo -en "Content-Type: text/plain\r\n\r\n" env

    Or this:

    #!/usr/bin/perl -T use strict; use warnings; use Data::Dumper; print "Content-Type: text/plain\r\n\r\n", Data::Dumper->new([\%ENV],['*ENV'])->Sortkeys(1)->Useqq(1)->Du +mp();

    Why should installing MySQL set a DBI-specific environment variable? MySQL does not do so out of the box, and I would be rather surprised if any of the major Linux or *BSD distributions would do so. It does not make any sense, simply because you may have more than one database on your system.

    According to your logic, installing the SQLite library should automatically set $ENV{'DBI_DRIVER'}='SQLite', system-wide. Installing PostgreSQL should set it to 'Pg'. Installing MariaDB should set it to 'MariaDB'. Which one wins if I install PostgreSQL for the main work and SQLite to read some application database like, for example, Firefox bookmarks? The database installed first? The database installed last? Given that DBD::MariaDB does not only support MariaDB, but also MySQL, and it fixes a lot of problems of DBD::mysql, why should installing MySQL set $ENV{'DBI_DRIVER'}='mysql' instead of $ENV{'DBI_DRIVER'}='MariaDB'?

    Let's assume your idea was real. Installing MySQL sets $ENV{'DBI_DRIVER'}='mysql'. Assume a very bare system, just enough to get MySQL running, plus Perl and DBI. Perl scripts would rely on $ENV{'DBI_DRIVER'}. Now, SQLite is installed as a dependency to support Firefox or any other application using SQLite database files. Following the same logic, installing SQLite forcefully sets $ENV{'DBI_DRIVER'}='SQLite'. Now all existing perl scripts would break just because you installed some completely independent software that has nothing to do with MySQL, or Perl, or DBI. It just installed another relational database. Wouldn't that be great? Imgine the hordes of users assembling in front of the home of whoever came up with that great idea, to celebrate it with torches, pitchforks, and some barrels containing tar and feathers.


    Update:

    You seem to trigger a bug in DBI->connect(), see Re^8: DBI placeholders for spatial data.

    Alexander

    --
    Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
      Guess? How about printing the environment instead of guessing?

      I guess because the DBI environment variables are not available to me on the shared hosting!

      There is a script in my cgi-bin which lists all the CGI environment variables and there is nothing helpful in there. When I connect by SSH and run perl -e 'foreach $k(keys %ENV){print "$k - $ENV{$k}\n";}' I get environment variables for SSH and for local PERL and other directories but nothing relating to DBI

      So, as it is hidden from me by the server admins, I can only guess...

        I guess because the DBI environment variables are not available to me on the shared hosting!

        There is a script in my cgi-bin which lists all the CGI environment variables and there is nothing helpful in there. When I connect by SSH and run perl -e 'foreach $k(keys %ENV){print "$k - $ENV{$k}\n";}' I get environment variables for SSH and for local PERL and other directories but nothing relating to DBI

        Of course you won't see the web server environment variables when running code from an SSH shell.

        So, as it is hidden from me by the server admins, I can only guess...

        Nonsense. When you run from the web server, i.e. a CGI or something running via mod_perl, you will see exactly the same variables as Perl. There is no way to hide anything. If DBI can see an environment variable, so can any other perl code started by the web server.

        Depending on how your shared hosting is configured, your CGIs may actually be run via mod_perl, using ModPerl::Registry. Loading Apache::DBI from the webserver may also happen. That changes how DBI->connect() works, as documented in Apache::DBI. The documentation also gives a hint how to detect that situation: $ENV{'MOD_PERL'} is set and $INC{'Apache/DBI.pm'} should exist.

        Alexander

        --
        Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
Re^7: DBI placeholders for spatial data
by erix (Prior) on Jun 26, 2021 at 23:58 UTC

    But... did you notice that your original dsn example had:

    DBD:MariaDB:

    instead of

    dbi:MariaDB:

    ?

      I just wanted to demonstrate that DBI can't connect with DBI->connect('DBD:MariaDB:...',...), but it can and does, if environment variables are set. I think that should not happen.


      DBI's connect method is pure perl. It contains some legacy stuff, but I don't really want to analyse every single line of it. So I just cleared the environment and ran a test:

      /tmp>env - perl -MDBI -e 'DBI->connect("DBD:MariaDB","","",{PrintError +=>1})' Can't connect to data source 'DBD:MariaDB' because I can't work out wh +at driver to use (it doesn't seem to contain a 'dbi:driver:' prefix a +nd the DBI_DRIVER env var is not set) at -e line 1.

      There is a hint hiding in the error message. The relevant part of the connect() documentation is this, at the very end:

      For compatibility with old DBI scripts, the driver can be specified by passing its name as the fourth argument to connect (instead of \%attr):

      $dbh = DBI->connect($data_source, $user, $pass, $driver);

      In this "old-style" form of connect, the $data_source should not start with "dbi:driver_name:". (If it does, the embedded driver_name will be ignored). Also note that in this older form of connect, the $dbh->{AutoCommit} attribute is undefined, the $dbh->{PrintError} attribute is off, and the old DBI_DBNAME environment variable is checked if DBI_DSN is not defined. Beware that this "old-style" connect will soon be withdrawn in a future version of DBI.

      connect() assumes an old-style call, despite being called with an unblessed hash reference as fourth argument, not a string. The old-style call should have a driver name as fourth argument. This smells like a bug.

      What happens if $ENV{'DBI_DRIVER'} is set?

      /tmp>env - DBI_DRIVER=NON::SENSE perl -MDBI -e 'DBI->connect("DBD:Mari +aDB","","",{PrintError=>1})' install_driver(NON::SENSE) failed: Can't locate DBD/NON/SENSE.pm in @I +NC (you may need to install the DBD::NON::SENSE module) (@INC contain +s: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/ven +dor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/per +l5 .) at (eval 5) line 3. Perhaps a module that DBD::NON::SENSE requires hasn't been fully insta +lled at -e line 1.

      Again, DBI->connect() treats a new-style call with \%attr, but an invalid $data_source, as an old-style call.

      What happens to the attributes?

      /tmp>env - perl -MDBI -e 'print DBI->connect("dbi:SQLite:foo","","",{P +rintError=>1})->{PrintError}' 1 /tmp>env - DBI_DRIVER=SQLite perl -MDBI -e 'print DBI->connect("DBD:Ma +riaDB:foo","","",{PrintError=>1})->{PrintError}' 1 /tmp>env - DBI_DRIVER=SQLite perl -MDBI -e 'print DBI->connect("DBD:Ma +riaDB:foo","","",{})->{PrintError}' 1

      PrintError is on, even by default. For "old-style" connects, it should be off. See the quoted documentation above.

      What happens for a real old-style call?

      /tmp>env - perl -MDBI -e 'print DBI->connect("DBD:MariaDB:foo","",""," +SQLite")->{PrintError}' DBI->connect using 'old-style' syntax is deprecated and will be an err +or in future versions at -e line 1. /tmp>env - perl -MDBI -e 'print defined DBI->connect("DBD:MariaDB:foo" +,"","","SQLite")->{PrintError}' DBI->connect using 'old-style' syntax is deprecated and will be an err +or in future versions at -e line 1. 1 /tmp>env - perl -MDBI -e 'print DBI->connect("DBD:MariaDB:foo","",""," +SQLite")->{PrintError}+0' DBI->connect using 'old-style' syntax is deprecated and will be an err +or in future versions at -e line 1. 0

      We get the expected deprecation warning, and PrintError is off (defined and empty).

      What happens without \%attr? This:

      /tmp>env - perl -MDBI -e 'print DBI->connect("DBD:MariaDB:foo","","")' Can't connect to data source 'DBD:MariaDB:foo' because I can't work ou +t what driver to use (it doesn't seem to contain a 'dbi:driver:' pref +ix and the DBI_DRIVER env var is not set) at -e line 1.

      This is acceptable, as there is no fourth argument containing either a driver name or \%attr. So this could be an old-style call. The first argument lacks a "dbi:" prefix, so it must be an old-style call. (Or a confused coder.)


      So, an invalid modern-style $data_source confuses connect() to use the fall-back to old-style driver name handling, but at the same time to respect the \%attr parameter and to omit the deprecation warning. I think this is a bug.

      Bod's system obviously behaves the same, and someone must have set up DBI environment variables that hide the problem.

      My DBI version:

      /tmp>perl -MDBI -e 'print $DBI::VERSION' 1.636

      I'm a little bit behind (CPAN currently has 1.643), but the relevant parts in DBI.pm are connect() and installl_driver(). The former has some changes related to the password, and keeps a copy of the original $data_source for an error message, but has no other changes. The latter is unchanged. So I think this bug still exists. Unfortunately, I don't have a spare system at hand to test the current version of DBI.

      Alexander

      --
      Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)