Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

DBI giving user input by place holders

by valavanp (Curate)
on Jul 11, 2006 at 08:38 UTC ( [id://560359]=perlquestion: print w/replies, xml ) Need Help??

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

In DBI, I want to get the user input and fetch the rows given by user. I am getting only the last row of my table. But when i view it in MYSQL driver it shows all the rows. Can anyone tell where i am wrong. This is the coding which i executed.

use DBI; use strict; $a=<STDIN>; chomp $a; $b=<STDIN>; chomp $b; my $dbname="test"; my $dbh=DBI->connect("DBI:mysql:$dbname","root","", {RaiseError=>1, Au +toCommit=>0}); $sth=$dbh->do("create table rec(name varchar(10), id int(10)"); $sth=$dbh->prepare("insert into rec(name, id) values(?, ?)"); $sth->execute($a, $b); $sth->prepare("select * from rec"); my @row; while (@row=$sth->fetchrow_array) { print "@row\n"; } $sth->finish(); $dbh->disconnect(); exit;

Thanks for the help. Apologize if any errors.

20060711 Janitored by Corion: Added formatting, code tags, as per Writeup Formatting Tips

Replies are listed 'Best First'.
Re: DBI giving user input by place holders
by gellyfish (Monsignor) on Jul 11, 2006 at 08:45 UTC

    Firstly you probably don't want to use the variables $a and $b in your code like that, they have special meaning when used in a sort subroutine (which is why they don't provoke an error from 'strict').

    The code above appears to only insert one row into the table anyway, I'm not actually sure why you think there should be more than one row.

    /J\

      i want to insert more rows in my table. i am not able to do with the above coding. Can you please suggest how to do that.

        Assuming you want the same SQL executed, but with different values, you'll need to issue the $sth->execute(...) multiple times. You can do this either by manually repeating that line in your code, or put the line inside a loop of some sort.

        Hope that helps.

Re: DBI giving user input by place holders
by Herkum (Parson) on Jul 11, 2006 at 13:12 UTC

    You should create the table outside of your script, once the table is created, your done with it. Why try and create the table everytime you run the script?

    Without executing the code, I am also going to assume the script dies when attempt to create the table that already exists which causes problems.

    Create your table manually, and delete the do statement which creates the table from your code. Run that and see what happens.

      As Herkum has said, create your table outside your script (or drop it every time and recreate it). In order to add multiple rows to your table, you'll want the execute statement inside your while loop, passing it new arguments for each iteration of the execute. You'll probably want to check the success of the execute as well.


      No good deed goes unpunished. -- (attributed to) Oscar Wilde

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://560359]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others studying the Monastery: (5)
As of 2024-03-29 11:00 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found