DBI question

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

Replies are listed 'Best First'.
Re: DBI question by runrig (Abbot) on Jun 01, 2005 at 19:40 UTC
You may want to do something like (just guessing since I don't know exactly what you want to do): `... chomp @lines; ...etc. my $sth = $dbh->prepare("insert into foo (col1, col2) values (?,?)"); for my $i (0..$#lines) { $sth->execute($i, $lines[$i]); }` [download]	[reply] [d/l]
Re: DBI question by injunjoel (Priest) on Jun 01, 2005 at 20:04 UTC
Greetings all, There is a way to use placeholders in a `$dbh->do` statement. Here is the documentation on it but just to give you something to work with here is a small example: `# Connect to the database. my $dbh = DBI->connect("DBI:mysql:database=test;host=localhost", "joe","joe's password", {'RaiseError'=>1}) \|\| die "unable to + connect to the database"; # Now the interesting part. $dbh->do("INSERT INTO foo VALUES(1,?)",undef, "Tim");` [download] This is because, according to the docs, you have the option of running a `$dbh->do()` with this syntax `# From the docs $rows = $dbh->do($statement, \%attr, @bind_values)` [download] Given that you don't have any attributes you just leave that part `undef` and then follow it with your values you want to bind your placeholders to. The placeholders take care of the quoting for you so there is no need for the `$dbh->quote()`. -InjunJoel "I do not feel obliged to believe that the same God who endowed us with sense, reason and intellect has intended us to forego their use." -Galileo	[reply] [d/l] [select]
Re: DBI question by derby (Abbot) on Jun 01, 2005 at 19:42 UTC
You look to be on the right path but not knowing the format of the switchshow command nor the schema of your table, it's a bit difficult. At a high level, you might try something along the lines of: `#!/usr/bin/perl -w use DBI; use Net::Telnet (); $t = new Net::Telnet (Timeout => 10,); $user = "admin"; $passwd = ""; $t->open("Sparta"); $t->login($user, $passwd); @lines = $t->cmd("switchshow"); my $dbh = DBI->connect( ... ); my $sth = $dbh->prepare( q{INSERT INTO table (field_a, field_b) VALUES (?, ?)}); foreach my $line ( @lines ) { #$sth->execute(1, $dbh->quote($line) ); $sth->execute( 1, $line ) } $dbh->commit; $dbh->disconnect;` [download] -derby updated Thanks to gmax and runrig for pointing out that quote and placeholders should not be mixed.	[reply] [d/l]
Re^2: DBI question by runrig (Abbot) on Jun 01, 2005 at 19:46 UTC
From the DBI docs: The quote() method should not be used with "Placeholders and Bind Values".	[reply]
Re: DBI question by Anonymous Monk on Jun 01, 2005 at 19:55 UTC
Thanks for all the help the output is thus `switchName: sugartown switchType: 12.4 switchState: Online switchMode: Native switchRole: Subordinate switchDomain: 1 switchId: fffc01 switchWwn: 10:00:00:60:69:90:59:21 zoning: ON (Production_Config) switchBeacon: OFF` [download]	[reply] [d/l]
Re^2: DBI question by xtype (Deacon) on Jun 01, 2005 at 23:37 UTC
You didn't say what you column names were! `my %switch = map { split /:\s+/ } @lines; my @cols = sort keys %switch; my $rows = $dbh->do( "insert into foo (".join(',', @cols).") values (".join(',', ('?') +x@cols).")", undef, @switch{@cols} );` [download] ;^) -xtype	[reply] [d/l]
Re: DBI question by Anonymous Monk on Jun 01, 2005 at 19:41 UTC
Hope I understood your question rigth..but here it goes: `foreach $tmpline (@lines) { $tmpline=~s/[\012\015]//; $dbh->do("INSERT INTO foo VALUES (1, "$tmpline")"); }` [download] if you could provide the format the info is being returned in would help. I assume its in list format ("\n" at end of each)	[reply] [d/l]
Re^2: DBI question by cool_jr256 (Acolyte) on Jun 01, 2005 at 19:46 UTC
As a side note to this, you could have the database handle the incrementation of your first field in the query. Hence you'd only need to do this: `"INSERT INTO foo VALUES('$tmpline')"` [download] Cheers....	[reply] [d/l]