Inserting Data From Perl To MysQL

Question

I have already connected MySQL to the database but the problem is that I have some problems with inserting Data from Perl to Mysql.

The error that pops out is:

Use of uninitialized value in concatenation (.) or string at ./etl_server_info.pl line 204, (Which is the connection of the database ) 

DBD::mysql::st execute failed: called with 1 bind variables when 0 are needed at ($stmt->execute($sql);)

sub insert_record(){

 my($data,$db_config)=@_;
 my $result = -1; # -1 fail;  0 - succ

  # connect to db
  # connect to MySQL database
  my $dsn = "DBI:mysql:database=".$db_config->{'Top_Data'}.";host=".$db_config->{'127.0.0.1'}.";port=".$db_config->{'3306'};
my $username = $db_config->{'username'};
my $password = $db_config->{'password'};

my %attr = (PrintError=>0,RaiseError=>1 );
my $dbh = DBI->connect($dsn,$username,$password,\%attr) or die $DBI::errstr;

print "We Have Successfully Connected To The Database \n";

  # prepare sql statement
  # execute insert
  my $sql = 'insert into Top(Load_Average, CPU_us, CPU_id, CPU-wa, CPU_hi, CPU_si, CPU_st, Swap_Total, Swap_Free, Swap_Used, Memory_Total, Memeory_Free, Memory_Used, Memory_Buff, Date) values(float,float,float,float,float,float,float,float,varchar,varchar,varchar,varchar,varchar,varchar,date)';




  my $stmt =$dbh->prepare($sql) or die "Its A Fail" . $dbh->errstr();
  $stmt->execute($sql);



  $stmt->finish();
  $dbh->disconnect();
  $result = 0;
  return($result);

insert into sometable (foo, bar, baz) values ( ?, ?, ?) and then $stmt->execute( $foo, $bar, $baz) — k-mx
– k-mx, Commented Aug 13, 2019 at 4:16
Read perldoc DBI. Your code is wrong. You must pass exact values to execute() and use placeholders in statement as I told. You pass statement to execute(), this is mistake — k-mx
– k-mx, Commented Aug 13, 2019 at 5:09

Dave Cross · Accepted Answer · 2019-08-13 08:10:18Z

1

Your use of the $db_config variable looks suspicious to me. Either your config hash is strange, or you're using values instead of keys.

You haven't shown us where $db_config is set up, but I'd guess it looks something like this:

$db_config = {
  name => 'Top_Data',
  host => '127.0.0.1',
  port => 3306,
  username => 'someone',
  password => 'a secret',
};

And then you would use it like this:

my $dsn = "DBI:mysql:database=".$db_config->{name}.";host=".$db_config->{host}.";port=".$db_config->{port};

Notice that I've used the key names (name, host and port) instead of the values (Top_Data, 127.0.0.1 and 3306).

I'll also point out that you can simplify this slightly by using Perl's ability to expand variables inside a double-quoted string.

my $dsn = "DBI:mysql:database=$db_config->{name};host=$db_config->{host};port=$db_config->{port}";

There's another problem later on, with your SQL statement.

my $sql = 'insert into Top(Load_Average, CPU_us, CPU_id, CPU-wa, CPU_hi, 
           CPU_si, CPU_st, Swap_Total, Swap_Free, Swap_Used, Memory_Total, 
           Memeory_Free, Memory_Used, Memory_Buff, Date) 
           values(float,float,float,float,float,float,float,float,
           varchar,varchar,varchar,varchar,varchar,varchar,date)';

The values that you should be inserting are the actual data items. So where you have the strings "float", "varchar" or "date", you should actually have data items (a floating-point number, a string or a date).

Finally, having prepared your statement, you don't need to pass it to the execute() method. You should, however, look at using bind points in your SQL and passing your actual data items to the execute() call

answered Aug 13, 2019 at 8:10

Dave Cross

69.5k3 gold badges55 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dream Member Over a year ago

But how can i put multiple items in one coloum if my data is an array ... so do i change the value into this (?) ..... since the data is many and not a single data ?

Dave Cross Over a year ago

@DreamMember: You prepare your statement once (my $sth = $dbh->prepare($sql)) with ? in place of the actual values. Then you loop over your data and call $sth->execute(@data) for each iteration of the loop - with @data containing one row of data items each time.

Collectives™ on Stack Overflow

Inserting Data From Perl To MysQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related