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

I can't find any Site::Point on CPAN.

You won't - it is a module specific to this application. Although, it is quite a general representation of a point on the globe so it might make a useful CPAN addition.

I don't know which DB you are using.

Sorry - I should have included that it is MariaDB.
Spatial datatypes are something that varies quite a bit between databases. Even between MySQL and MariaDB!

The specification for that "POINT Datatype" is not clear.

Exactly!

I had tried substituting just the numeric values through placeholders like so ST_GeomFromText('POINT( ? ? )') which didn't work. I don't recall the error. I shall look tomorrow. I hadn't tried ST_GeomFromText(?,4326) as you suggest. Thanks - I'll try that tomorrow too...

Replies are listed 'Best First'.
Re^3: DBI placeholders for spatial data
by hippo (Bishop) on Jun 26, 2021 at 08:37 UTC
    Sorry - I should have included that it is MariaDB.

    It would also be helpful to know which version of MariaDB you are connecting to and also which DBD driver (and version) you are using to make that connection.


    🦛

      10.2.39-MariaDB

      I was using DBD::mysql until yesterday when I switched this project to DBD::MariaDB. I'm still using the MySQL driver for other projects that don't require spatial data.

      However, I cannot find DBD::MariaDB listed amongst the installed modules so I am struggling to find the version of that! DBD::mysql is version 4.050

      edit:

      Running cpan -D DBD::MariaDB tells me that the module is not installed!

      However, DBI->connect("DBD:MariaDB:database=xxx", $user, $pass); connects without problem.

      Further edit

      foreach my $driver(DBI->available_drivers) { print "$driver\n"; }
      Produces:
      DBM ExampleP File Gofer Mem Pg Proxy SQLite SQLite2 Sponge mysql
      So I guess that DBI->connect("DBD:MariaDB:database=... is using some form of default driver.

        However, DBI->connect("DBD:MariaDB:database=xxx", $user, $pass); connects without problem.

        [...]

        So I guess that DBI->connect("DBD:MariaDB:database=... is using some form of default driver.

        It works only by accident, or because DBI is quite relaxed regarding the data source argument to connect(). The DBI documentation clearly states:

        connect

        $dbh = DBI->connect($data_source, $username, $password) or die $DBI::errstr; $dbh = DBI->connect($data_source, $username, $password, \%attr) or die $DBI::errstr;
        [...]

        The $data_source value must begin with "dbi:driver_name:". The driver_name specifies the driver that will be used to make the connection. (Letter case is significant.)

        [...]

        Alexander

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

        That's odd.

        $ cpan -D DBD::MariaDB DBD::MariaDB ---------------------------------------------------------------------- +--- (no description) P/PA/PALI/DBD-MariaDB-1.21.tar.gz /Users/1nickt/.perlbrew/libs/perl-5.32.1@meta/lib/perl5/darwin-2le +vel/DBD/MariaDB.pm Installed: 1.21 CPAN: 1.21 up to date Pali (PALI) pali@cpan.org
        $ perl -MDBD::MariaDB -E 'say $DBD::MariaDB::VERSION' 1.21


        The way forward always starts with a minimal test.
Re^3: DBI placeholders for spatial data
by LanX (Sage) on Jun 26, 2021 at 11:23 UTC
    >    ST_GeomFromText('POINT( ? ? )')

    To make my argument clearer: this 'POINT( ? ? )' might look like an SQL function but it's a string which is parsed by ST_GeomFromText() !

    Please note the missing commas, too. Function arguments in SQL are comma separated.

    The MariaDB documentation calls the argument a WKT with T for Text!

    WKT is not SQL, pretty much like SQL is not Perl

    Your string passed here to ST_GeomFromText() will always include literal question marks in your example.

    There is nothing like ? placeholder handling inside strings in SQL!

    The error you get will be along the line "too many arguments for placeholders" because the two ? inside a string are just ignored.

    Cheers Rolf
    (addicted to the Perl Programming Language :)
    Wikisyntax for the Monastery

    ) The Well-Known Text (WKT) representation of Geometry is designed to exchange geometry data in ASCII form

    update