Beefy Boxes and Bandwidth Generously Provided by pair Networks
Come for the quick hacks, stay for the epiphanies.
 
PerlMonks  

Perl XAMPP DB Connect issue (Windows/Strawberry?)

by Polyglot (Chaplain)
on Dec 31, 2022 at 12:41 UTC ( [id://11149218] : perlquestion . print w/replies, xml ) Need Help??

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

A friend has asked me to install my scripts on his Windows 10 laptop. Last time I tried such a thing, I ended up going with a VM. But that is inconvenient for multiple reasons, which need not concern this question. So this time I have tried again to use XAMPP, and, amazingly, everything seems to be working--except that I cannot connect to the DB with Perl.

Naturally, I've changed/edited the shebang line to accommodate XAMPP's requirements. I also changed the use DBD::mysql; to use DB; in accordance with XAMPP's setup. After struggling through the process of installing modules not included with XAMPP, such as Regexp::Assemble and Roman (which involved installation of 'chocolatey' through PowerShell to enable the installation of make to be able to install from untarred CPAN tarballs), I finally had a Perl script which accepted all of the modules in use.

And the included phpMyAdmin sees the database and can work with it just fine, proving that MariaDB is running without issues.

Making the connection to the DB with Perl is where what I suppose to be the final problem has cropped up. The DB just does not respond as I would have expected. Even after checking authentication issues for user/password for the database, the error message remains the same.

(From the Apache log file)
[Sat Dec 31 19:14:39.401041 2022] [cgi:error] [pid 9360:tid 1864] [client 127.0.0.1:61019] AH01215: Can't locate object method "connect" + via package "DBI" at ../htdocs/mydir/my_mysql.pl line 621.: C:/xampp/cgi-bin/myscript.cg +i
Notably, no error appears for not finding the package, the error is for not finding its "connect" method. My code for that portion which connects to the database looks like this:
use DB; our $database = 'script_db'; our $db_user_name = 'script_user'; our $db_password = 'my_secure_pw'; our $dsn = "DBI:mysql:$database:localhost"; # . . . {snip} #FOLLOWING LINE PRODUCES ERROR WITH XAMPP my $dbh = DBI->connect($dsn, $db_user_name, $db_password, { mysql_enable_utf8 => 1 }) or die "Can't connect to the DB: $DBI::errstr\n"; $dbh->{PrintError} = 1; $dbh->{RaiseError} = 1; $quest = $dbh->prepare($statement, { RaiseError => 1}) or die "Can +not prepare statement! $DBI::errstr\n"; $quest->execute();
I have tried a number of things including looking at the DB.pm package supplied with my copy of XAMPP (version 3.3.0). The DB.pm package is not in a directory named "DB", rather, it stands alone in the /lib/ subdirectory of XAMPP's perl installation. The word "connect" does not exist anywhere in the DB.pm file, and, as there appear to be no other files associated with the package--that's all, folks!

What am I and/or my XAMPP installation missing?

Blessings,

~Polyglot~

Replies are listed 'Best First'.
Re: Perl XAMPP DB Connect issue (Windows/Strawberry?)
by soonix (Canon) on Dec 31, 2022 at 12:59 UTC
    Hmm. What do you think, where should Perl find DBI->connect if you didn't use DBI; ?
      When this script ran perfectly on Mac or Linux, I was not using "DBI" either. It was "DBD::mysql" then.

      I haven't used use DBI; in a long time, to be honest -- but all the while the "DBI->connect" worked just fine in my code.

      Blessings,

      ~Polyglot~

        I see DBD/mysql.pm includes "use DBI;", so it wasn't needed explicitly in your code in that case. However, even the DBD::mysql examples start out with "use DBI;", and the connect method induces loading the corresponding DBD module (instead of useing it explicitly). This way - at least for simple SQL - you could switch and mix database drivers by simply changing the connect string...

        ⇒ You might need to install DBI and/or DBD::mysql, because at least one of these two seems not to be in XAMPP's default - (see also)

Re: Perl XAMPP DB Connect issue (Windows/Strawberry?)
by LanX (Saint) on Dec 31, 2022 at 13:31 UTC
      Well, if it's only a debugger, it sure fooled me. They might choose a less ambiguous piece of namespace next time. What package does XAMPP expect one to use for database connections?

      Blessings,

      ~Polyglot~

        I currently don't have XAMPP installed, and on the XAMPP website they don't mention which Perl version and modules are installed by default, but the Stackoverflow post that I referenced in my previous post lets me assume it is DBI. (I had noticed the use DB; in your OP, too, but, like you, had assumed it might be another database layer in XAMPP) -- what does it say if you try to use DBI; instead?
Re: Perl XAMPP DB Connect issue (Windows/Strawberry?)
by Anonymous Monk on Dec 31, 2022 at 20:22 UTC
    Hi.

    Have you tried a 'use libs' for the DB.pm? Just a thought.

    J.C.
      Well, I believe Rolf nailed it in identifying that "DB.pm" package as a debugger. It seems to have nothing to do with databases. In looking through its code I saw much about identifying files and nothing about tables. Most of it was so abstract as to be above my level, and the explicit parts related to debugging or error messaging had not surprised me because, well, don't all good modules have that sort of thing?

      In any case, "DB.pm" is definitely not an equivalent for DBI/DBD.

      Blessings,

      ~Polyglot~

      Or 'unshift'ing to the @INC
Re: Perl XAMPP DB Connect issue (Windows/Strawberry?)
by Polyglot (Chaplain) on Jan 01, 2023 at 01:12 UTC
    I'm still struggling with this. Now the error message is that it cannot find "mysql.h"--a file that exists nowhere on the machine. This is the error I get from trying to execute "perl Makefile.PL" at the command line in the DBD-mysql-4.050 directory. I'm given options for providing the appropriate directory to the path, but I have not found such a file to begin with. XAMPP comes with its own "mysql.exe" that is compiled for Windows. So, it seems XAMPP only works with PHP, not Perl, when it comes to using its MariaDB. Frustrating.

    By the way, XAMPP's version of Perl is: 5.32.1, said to be "built for MSWin32-x64-multi-thread" and "Copyright 1987-2021, Larry Wall".

    Blessings,

    ~Polyglot~

      Now the error message is that it cannot find "mysql.h"--a file that exists nowhere on the machine

      That file and the rest of the mysql library ships with Strawberry Perl 5.32.1.
      The 'mysql.h' file is in Strawberry's c/include/mysql50716 folder, the static library ('libmysql.a') is in Strawberry's c/lib folder and the shared library ('libmysql__.dll) is in Strawberry's c/bin folder.

      If you're using Strawberry Perl, I recommend building against that mysql library, rather than the one that xampp provided.
      After all, the mysql library that ships with the Strawberry Perl distro is the one against which Strawberry's DBD::mysql was built.

      OTOH, if you're using XAMPP's perl, then you should build against the mysql library that they provided.

      HTH.
      (I haven't read this thread thoroughly, and I don't do databases. I concede that this post might therefore be of no help at all.)

      Update:
      By the way, XAMPP's version of Perl is: 5.32.1, said to be "built for MSWin32-x64-multi-thread"

      If you want to check that this version of perl is compatible with Strawberry Perl 5.32.1, just run perl -V:archname using your Strawberry Perl 5.32.1.
      If it reports 'MSWin32-x64-multi-thread', then they're compatible. Else, they are incompatible.

      Cheers,
      Rob
        Well, you were right about the "mysql.h" file. I found it just where you said it would be.

        Now the next error in this wild goose chase is that there is no "mysqlclient.lib" and I am supposed to supply the directory where that can be found before the "make" will process. Having searched the entire hard disk (which took awhile), it is nowhere to be found.

        It seems this module is pretty needy, and assumes its dependencies will be supplied by the unsuspecting client, rather than the source. I have no idea when the requirements will end, as they appear one at a time...or even if I will be able to meet all of them.

        Blessings,

        ~Polyglot~

      My options are nearly exhausted at this point. I have made some discoveries, some good, some not.

      I downloaded Strawberry Perl, which is supposed to have DBD::mysql and all that is necessary, and found it to apparently have the identical list of libraries that came with XAMPP. So that's the bad news--XAMPP is not working out of the box with what, by all accounts online, appears to be the best possible option for getting Perl going on Windows.

      The good news is that I found the DBD::mysql library tucked into a directory where I would not have expected it. It was in "C:\xampp\perl\vendor\lib\auto\".

      But more bad news is coming. The file is not compatible for some reason. An error message leading back to DynaLoader.pm appears saying "Can't load 'C:/xampp/perl/vendor/lib/auto/DBD/mysql/mysql.xs.dll' for module DBD::mysql: load_file: The specified module could not be found at C:/xampp/perl/lib/DynaLoader.pm line 193."

      I tried a "hail Mary"-style push of the path for the DBD module to DynaLoader's "@dl_library_path" array...to no avail, of course.

      It looks like this is where the whole project dies. Is there an easy way to inline some php to handle the DB connections that I cannot do with Perl?

      I'd really hate to go back to having to run a linux VM on windows just to get perl to use a database--it takes a whopping amount of space to do it this way, not to mention the added layers of complexity with accessing it via the VM.

      Blessings,

      ~Polyglot~

        Is the Strawberry Perl c\bin directory in your path? If not then external DLLs cannot be located by the system and nothing that depends on them will work. In this case the file you refer to, mysql.xs.dll depends on libmysql__.dll. If that file is not in the path then the bindings will not work. The .xs.dll file is also the compiled XS bindings, not the mysql DLL.

        If you have downloaded a portable version of Strawberry perl, which from memory is what Chocolatey distributes, then you need to run the portableshell.bat script to set up the paths, or manually prepend them to your path. This script is at the top level of the Strawberry perl distribution.

        (Edited a few minutes after posting to clarify reference to .xs.dll file.)

        The file is not compatible for some reason.

        If that file is present, then this usually happens when perl.exe has been changed but the current *.xs.dll have not been rebuilt with this new perl.exe. Or when multiple perl.exe exist in your computer and the one you are using now is not the one used to compile/create the xs.dll file.