Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses

Re^2: perl mysql - INSERT INTO, 157 columns

by Tux (Abbot)
on May 02, 2014 at 12:35 UTC ( #1084782=note: print w/replies, xml ) Need Help??

in reply to Re: perl mysql - INSERT INTO, 157 columns
in thread perl mysql - INSERT INTO, 157 columns

describe is not present in all databases :(

More portable would be:

my $sth = $dbh->prepare ("select * from foo"); $sth->execute; my @fld = @{$sth->{NAME}}; $sth->finish; $sth = do { local $" = ","; $dbh->prepare ("insert into foo (@fld) values (@{[('?') x scalar @ +fld]})"); };

Enjoy, Have FUN! H.Merijn

Replies are listed 'Best First'.
Re^3: perl mysql - INSERT INTO, 157 columns
by graff (Chancellor) on May 02, 2014 at 16:52 UTC
    Given that the OP specifically mentioned mysql, I would not have posted the approach using "describe" if it didn't work in mysql.

    As for your alternative (for cases where "describe" doesn't work), I would hope that any type of database covered by DBD would at least support "select * from foo limit 1", and this would be a prudent usage for the task at hand, whether or not you actually need to execute the query in order to get field names.

    (BTW, thanks for the reminder about $")

      any type of database covered by DBD would at least support "select * from foo limit 1"
      Unfortunately, MS SQL Server throws an error:
      Incorrect syntax near '1'

      What works, though, is

      SELECT TOP 1 * FROM table
      لսႽ ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ
      One would hope. However, last time I looked (v 11R2), Oracle still didn't support either limit or offset (you can fake it with rownum, but that's ugly).
Re^3: perl mysql - INSERT INTO, 157 columns
by erix (Parson) on May 02, 2014 at 12:53 UTC
    my $sth = $dbh->prepare ("select * from foo");

    Surely that should be

    my $sth = $dbh->prepare ("select * from foo where 1=0 ");

    Or something like that (limit, top, or whatever to not have the db think deeper than necessary...).

      Usually not: the fetch starts only at the first fetch, so no table content is actually fetched at all.

      Some DBD's do not even need the execute. They have the table/field info readily available after the prepare.

      Enjoy, Have FUN! H.Merijn
        Usually not

        Well, maybe so but you really should limit in the case of postgres.

        This is basically your example code running against 9.4devel, with and without a limiting where-clause:

        $ perl ./ # PostgreSQL 9.4devel_HEAD_20140502_2044_0717748 on x86_64-unknown-linux +-gnu, compiled by gcc (GCC) 4.9.0, 64-bit prepare execute finish no where 0.00007 5.52991 0.02707 no where 0.00005 5.42172 0.03283 no where 0.00005 5.42320 0.03247 where 0=1 0.00005 0.00049 0.00000 where 0=1 0.00002 0.00013 0.00000 where 0=1 0.00002 0.00012 0.00000

        (foo has 10M 1-column rows; just a create table foo as select n from generate_series(1, 10000000) as f(n); )

        (What the hell -- let me just dump the test here too, even if it's a bit clunky (disks are cheap and patient):

        use strict; use warnings; use DBI; use Time::HiRes qw/gettimeofday tv_interval/; my $dbh = DBI->connect or die "oops - $!\n"; print $dbh->selectrow_arrayref('select version()')->[0], "\n\n"; my $sql1 = "select * from foo"; my $sql2 = "select * from foo where 0 = 1"; print " prepare execute finish\n"; time_this( $dbh, $sql1, ' no where' ); time_this( $dbh, $sql1, ' no where' ); time_this( $dbh, $sql1, ' no where' ); print "\n"; time_this( $dbh, $sql2, 'where 0=1'); time_this( $dbh, $sql2, 'where 0=1' ); time_this( $dbh, $sql2, 'where 0=1' ); sub time_this { my ($dbh, $sql, $how) = @_; my $t0; $t0 = [gettimeofday]; my $sth = $dbh->prepare( $sql ); print $how, " "; printf(" %7.5f", tv_interval($t0 , [gettimeofday])); $t0 = [gettimeofday]; $sth->execute; printf(" %7.5f", tv_interval($t0 , [gettimeofday])); my @fld = @{$sth->{NAME}}; $t0 = [gettimeofday]; $sth->finish; printf(" %7.5f", tv_interval($t0 , [gettimeofday])); print "\n"; }

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1084782]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (2)
As of 2018-01-19 22:11 GMT
Find Nodes?
    Voting Booth?
    How did you see in the new year?

    Results (223 votes). Check out past polls.