Perl concatenation for SQL query

Question

I'm trying to transfer the content of a CSV file into a table in PostgreSQL using Perl.

I'm able to update my table successfully, but the terminal returns an error:

Use of uninitialized value in concatenation (.) or string
Syntax error near ","
INSERT INTO test VALUES (, '', '', '', '',, )

Here is the code where it fails :

for (my $i=0 ; $i<=50; $i++){
$dbh ->do("INSERT INTO test VALUES ('$LastName[$i]', '$Street[$i]', $Balance_account[$i])") ;

If more information is needed just ask them. Sorry for the bad English.

-- Thomas

Pradeep · Accepted Answer · 2017-03-10 12:15:48Z

3

Use placeholders,

for (my $i=0 ; $i<=50; $i++){
  $dbh->do("INSERT INTO test VALUES (?,?,?)",
    undef,
    $LastName[$i], $Street[$i], $Balance_account[$i]
  );
}

Ideally, you should prepare the query and execute for each set of values, something like this:

my $sth = $dbh->prepare('INSERT INTO test VALUES (?,?,?)');

for (my $i=0 ; $i<=50; $i++){
  $sth->execute($LastName[$i], $Street[$i], $Balance_account[$i]);
}

edited Mar 10, 2017 at 12:15

answered Mar 8, 2017 at 11:31

Pradeep

3,14119 silver badges21 bronze badges

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

Comments

Tim Biegeleisen · Accepted Answer · 2017-03-08 10:51:36Z

0

My guess is that your error is being caused by not specifying the column names in your insert, while simultaneously having the wrong number/types of columns. I would expect the following Perl code to not error out:

for (my $i=0 ; $i<=50; $i++){
$dbh -> do("INSERT INTO test (lastname, street, balance) VALUES ('$LastName[$i]', '$Street[$i]', $Balance_account[$i])");

Here is what a working query might look like:

INSERT INTO test (lastname, street, balance)
VALUES
    ('Skeet', '100 Thatcher Street', 100.50);

It is generally considered bad practice to not include column names while doing an insert, because even if you get it right, it could easily break later on.

answered Mar 8, 2017 at 10:51

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Collectives™ on Stack Overflow

Perl concatenation for SQL query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related