CODEWITHSUNDEEP
perlsql-server-2005
user1363308
user1363308

Reputation: 968

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

#!/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.

Upvotes: 0

Views: 309

Answers (2)

Borodin
Borodin

Reputation: 126722

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);

Upvotes: 3

ikegami
ikegami

Reputation: 385764

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.

Upvotes: 1

Related Questions