2

I want to insert multiple rows of data in a single column using a single query. This program is for less data. I have another weather monitoring .txt file which had 4000 lines of data. I can insert one data at a time but it becomes tedious for so many data values.

1.     use DBI;
2.     use DBD::mysql;
3.     use warnings;


4.     $connection = ConnectToMySql($database);

5.   # Multiple Data inputs
6.      $myquery = "INSERT INTO data(datatime,battery)
7.           VALUES
8.             (?,?),
9.             ('16.01.2013','6.54'), #data corresponding to date and battery
10.             ('17.01.2013','6.42'),
11.             ('21.01.2013','6.24'),
12.             ('22.01.2013','6.21'),
13.             ('24.01.2013','6.17'),
14.             ('25.01.2013','6.13'),
15.             ('28.01.2013','6.00'),
16.             ('29.01.2013','5.97'),
17.             ('30.01.2013','5.94'),
18.             ('01.02.2013','5.84')";
19.    $statement2 = $connection->prepare($myquery);

20.     $statement2->execute($myquery);

21.    #--- start sub-routine
22.    sub ConnectToMySql {
23.       $database ="xxxx";
24.       $user = "XXXX";
25.       $pass = "XXXX";
26.       $host="XXXX";
27.    my $dbh = DBI->connect("DBI:mysql:$database:$host", $user, $pass);
28.    }

This code is giving me the following errors:

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 '' at line 2 at C:/Users/User/workspace/DataBaseEntry/DataEntry.pl line 20.
DBD::mysql::st execute failed: called with 1 bind variables when 2 are needed at C:/Users/User/workspace/DataBaseEntry/DataEntry.pl line 40.

I cannot identify the problem. Is it the placeholder. What can i do to improve it? I am new to these things. so can you keep it simple. THANKS

ikegami
  • 367,544
  • 15
  • 269
  • 518
sumeet
  • 47
  • 10

2 Answers2

3

You should be passing the data values which should replace the (?, ?) as parameters to execute. Your code as written only passes a single parameter to execute and that parameter is the SQL text of your query.

Try this instead:

$myquery = "INSERT INTO data(datatime,battery) VALUES (?,?)";
my $sth = $connection->prepare($myquery);

$sth->execute('16.01.2013','6.54');
$sth->execute('17.01.2013','6.42');
$sth->execute('21.01.2013','6.24');
$sth->execute('22.01.2013','6.21');
$sth->execute('24.01.2013','6.17');
$sth->execute('25.01.2013','6.13');
$sth->execute('28.01.2013','6.00');
$sth->execute('29.01.2013','5.97');
$sth->execute('30.01.2013','5.94');
$sth->execute('01.02.2013','5.84');
Dave Sherohman
  • 45,363
  • 14
  • 64
  • 102
  • Thank you so much. It worked. If you can be kind enough to tell me whether it is wiser to convert data in txt or in spreadsheet to csv file rather that execute data like this, for large amounts of data, and then upload it in database using a program in perl? – sumeet Feb 04 '13 at 13:40
  • @sumeet: Personally, yes, that is how I would do it. Convert the data to CSV format, then use a Perl program to read the data a line at a time and pass the values from that line into an `execute` call. – Dave Sherohman Feb 05 '13 at 10:57
2
$connection->do(<<'EOT');
  INSERT INTO data (datatime, battery)
  VALUES
    ('17.01.2013', '6.42'),
    ('21.01.2013', '6.24'),
    ('22.01.2013', '6.21'),
    ('24.01.2013', '6.17'),
    ('25.01.2013', '6.13'),
    ('28.01.2013', '6.00'),
    ('29.01.2013', '5.97'),
    ('30.01.2013', '5.94'),
    ('01.02.2013', '5.84')
EOT

I'm not sure what you're trying to do with placeholders here.

Also, you're missing use warnings; use strict;, and you shouldn't use global variables everywhere.

melpomene
  • 84,125
  • 8
  • 85
  • 148