The problem is that this:
$sth = $dbh->prepare("SELECT * FROM ?");
$sth->execute("test") && say "OK";
translates to
SELECT * FROM "test";
rather than
SELECT * FROM test;
This isn't a problem for Postgres but MySQL doesn't accept quoted table
names.
On Fri, Apr 9, 2021 at 1:25 PM mailing lists via beginners <
beginners@perl.org> wrote:
> I'm using:
>
> CentOS Linux release 7.9.2009 (Core)
> perl 5.16.3
> perl-DBD-MySQL-4.023
> perl-DBI-1.627
> On Friday, April 9, 2021, 2:19:13 PM GMT+2, mailing lists via beginners <
> beginners@perl.org> wrote:
>
>
> without using the variable $table it also fails with the same error
>
>
> $sth = $dbh->prepare("SELECT * FROM ?");
> $sth->execute("test") && say "OK";
>
>
>
> On Friday, April 9, 2021, 2:12:01 PM GMT+2, mailing lists via beginners <
> beginners@perl.org> wrote:
>
>
> thanks Andrew
>
> I need to insert millions of rows so I need to have a good performance
> using placeholders
>
>
> On Friday, April 9, 2021, 1:57:25 PM GMT+2, Andrew Solomon <
> and...@geekuni.com> wrote:
>
>
> The ? is for quoting field values rather than entering table or field
> names. "SELECT * FROM $table" is probably what you want.
>
> On Fri, Apr 9, 2021 at 12:49 PM mailing lists via beginners <
> beginners@perl.org> wrote:
>
> Hello,
>
> I am having a problem with a very simple task and I don't see where the
> fault is.
>
> The first query works with problem but the second one fails with this
> error:
>
> ./test.pl
> OK 1
> DBD::mysql::st execute failed: You have an error in your SQL syntax; check
> the manual that corresponds to your MySQL server version for the right
> syntax to use near ''test'' at line 1 at ./test.pl line 28.
> END
>
>
> Does anyone know where the problem is?
>
>
> 1 #!/usr/bin/perl
> 2 use strict;
> 3 use warnings;
> 4 use 5.010;
> 5 use DBI;
> 6
> 7 my %db = (
> 8 host => 'mysql.local',
> 9 user => 'app',
> 10 password => '12345678',
> 11 db => 'test_db',
> 12 );
> 13
> 14 my $dbh = DBI->connect(
> 15
> 'dbi:mysql:database='.$db{'db'}.';host='.$db{'host'}.';port=3306',
> 16 $db{'user'},
> 17 $db{'password'},
> 18 { AutoCommit => 1, },
> 19 )
> 20 or die "Can't connect to database: $DBI::errstr\n";
> 21
> 22
> 23 my $sth = $dbh->prepare("SELECT * FROM test");
> 24 $sth->execute() && say "OK 1";
> 25
> 26 my $table="test";
> 27 $sth = $dbh->prepare("SELECT * FROM ?");
> 28 $sth->execute($table) && say "OK 2"
> 29
> 30 say "END";
>
>
>
>
>
>
> --
> Andrew Solomon
> Director, Geekuni <https://geekuni.com/>
> P: +44 7931 946 062
> E: and...@geekuni.com
>
--
Andrew Solomon
Director, Geekuni <https://geekuni.com/>
P: +44 7931 946 062
E: and...@geekuni.com
