I'm not able to execute insert command inside perl script?

Question

#!/usr/bin/perl

use DBI;     

$fund=103;
$mobile_number1="7700009896";
$city_address="hello word";

$sql_query3=(qq{ exec "INSERT INTO [192.168.14.28].CommunicationLog.dbo.sms_processedfeeds (pf_Fund,pf_trtype,pf_acno,pf_ihno,pf_Mobile,pf_msgtrtype,pf_msg,pf_entdt) VALUES  ($fund,'CALL',0,NULL,'$mobile_number1','CALL','$city_address',Getdate())"});

my $sql_sms = $dbh->prepare($sql_query3);
$sql_sms->execute();

I'm getting the following error:

DBD::ODBC::db prepare failed: [unixODBC][FreeTDS][SQL Server]The identifier that starts with 'INSERT INTO [192.168.14.28].CommunicationLog.dbo.sms_processedfeeds (pf_Fund,pf_trtype,pf_acno,pf_ihno,pf_Mobile,pf_msgtrtype,pf' is too long. Maximum length is 128. (SQL-42000)
[unixODBC][FreeTDS][SQL Server]Statement(s) could not be prepared. (SQL-42000) at Mobile_verification.pl line 8.
Can't call method "execute" on an undefined value at Mobile_verification.pl line 9.

I don't know exec, but the message says the first argument should be an identifier, not a SQL query. — ikegami
– ikegami, Commented Dec 28, 2012 at 16:46

ikegami · Accepted Answer · 2012-12-29 09:21:53Z

You don't need exec and the nested quotes in the statement. Use this instead

my $mobile_number1_lit = $dbh->quote($mobile_number1);
my $city_address_lit   = $dbh->quote($city_address);
$sql_query3 = <<END_SQL;
INSERT INTO [192.168.14.28].CommunicationLog.dbo.sms_processedfeeds (pf_Fund, pf_trtype, pf_acno, pf_ihno, pf_Mobile, pf_msgtrtype, pf_msg, pf_entdt)
VALUES ($fund, 'CALL', 0, NULL, $mobile_number1_lit, 'CALL', $city_address_lit, Getdate())
END_SQL

my $sql_sms = $dbh->prepare($sql_query3);
$sql_sms->execute;

or, preferably, use placeholders in the prepare and pass the parameters to execute, like this

$sql_query3 = <<'END_SQL';
INSERT INTO [192.168.14.28].CommunicationLog.dbo.sms_processedfeeds (pf_Fund, pf_trtype, pf_acno, pf_ihno, pf_Mobile, pf_msgtrtype, pf_msg, pf_entdt)
VALUES (?, 'CALL', 0, NULL, ?, 'CALL', ?, Getdate())
END_SQL

my $sql_sms = $dbh->prepare($sql_query3);
$sql_sms->execute($fun, $mobile_number1, $city_address);

ikegami · Accepted Answer · 2012-12-28 22:11:51Z

I'm not familiar with exec, but the message says the first argument should be an identifier, not a SQL query. If you meant to use the exec SQL command, you're misusing it.

But you say you want to perform an INSERT, so maybe you didn't mean to use EXECUTE at all. An INSERT would look like:

 my $stmt = "
    INSERT INTO [192.168.14.28].CommunicationLog.dbo.sms_processedfeeds (
              pf_Fund,
              pf_trtype,
              pf_acno,
              pf_ihno,
              pf_Mobile,
              pf_msgtrtype,
              pf_msg,
              pf_entdt
           ) VALUES (
              ?,?,?,?,?,?,?,Getdate()
           )
";
my $sth = dbh->prepare($stmt);
$sth->execute(
   $fund,
   'CALL',
   0,
   undef,
   '$mobile_number1',
   'CALL',
   $city_address,
);

Note: You could replace prepare+execute with $dbh->do($stmt, undef, ...data...);

Note: I'm assuming [192.168.14.28].CommunicationLog.dbo.sms_processedfeeds is a valid table designation.

Collectives™ on Stack Overflow

I'm not able to execute insert command inside perl script?

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