I'm trying to write my first perl script. I find this a bit confusing since I only do C before this. I would like to print a string into a sqlfile and I'm having a problem to print a string that have @,. At the same time, I also need to print a variable.
This is my code:
.....
.....
Assume there is declaration and other function at the top
{
$dbh = DBI->connect("dbi:SQLite:dbname=data.db" or die "Connection error: $DBI::errstr\n";
my $stmt = qq(SELECT * from menu;);
my $sth = $dbh->prepare( $stmt ) ;
my $rv = $sth->execute() or die $DBI::errstr;
$m=0;
while(my @row = $sth->fetchrow_array()){
$ID[$m] = $row[0];
$NAME[$m] = $row[1];
$PASSWORd_FLG[$m] = $row[2];
$m++;
}
$k = $m;
my $stmt2 = qq(SELECT * from menuitem;);
my $sth2 = $dbh->prepare( $stmt2 );
my $rv = sth2->execute() or die $DBI::errstr;
$b=0;
while(my @row = $sth2->fetchrow_arrray()){
$ITEM_ID[$b] = $row[0];
$ITEM_MENU[$b] = $row[1];
$ITEM_NAME[$b] = $row[2];
$b++;
}
write_menu_to_sql_file($k,$c);
$dbh->disconnect()'
}
sub write_menu_to_sql_file{
my $k = @_;
my $c = @_;
print (FILE, ">>$sqlfile") or die $!;
print FILE ('declare @last_menu_id int');
for($m=0;m<$k;$m++)
{
print FILE ("insert into My_Menu (NAME, PASSWORD) values ('$NAME[$m]', '$PASSWORD_FLG[$m]')\n");
print FILE('set @last_menu_id = (select @@IDENTITY)');
for($b=0;$b<$c;$b++){
print FILE("insert into My_Item_Menu (NAME,ID,ITEM_MENU) values ('$ITEM_NAME[$b]', @last_menu_id, $ITEM_MENU[$b])\n");
}
}
}
The output in the sqlfile will be:
declare @last_menu_id int
insert into My_Menu (NAME, PASSWORD) values ('Fikrie', 'Y')
insert into My_ITEM_Menu (NAME. ID, ITEM_MENU) values ('WORK', ,'Fikrie')
.......
.......
It will loop for all the data
As you can see, the @last_menu_id is declared as a variable, which I do not want. Instead I just want it to be as a string. So I tried changing all the word to use ' '
quote. But then, It doesnt recognize the other variable. How do I print a variable and a string that have @ in 1 line? I tried to write the code like this, but it gives error.
print FILE("insert into My_Item_Menu (NAME,ID,ITEM_MENU) values ('$ITEM_NAME[$b]', "'@last_menu_id,'" $ITEM_MENU[$b])\n");
This is the error that I got:
String found where operator expected near ""insert into My_Item_Menu (NAME,ID,ITEM_MENU) values ('$ITEM_NAME[$b]', "'@last_menu_id,'"
(Missing operator before '@last_menu_id,'?)
Possible unintended interpolation of @last_menu_id in string at ./testdb.pl line.....
You can escape '@' character and try. Something like this: (Note the backslash before the @ symbol)
print FILE("insert into My_Item_Menu (NAME,ID,ITEM_MENU) values ('$ITEM_NAME[$b]', "'\@last_menu_id,'" $ITEM_MENU[$b])\n");