Update Sqlite References

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks

Given the following snippet which adds a record into a simple Sqlite Database perfectly working

my $Tag1="hallo";
my $Tag2="bye";

my $dbh = DBI->connect( "dbi:SQLite:$DbFullPath", '', '', { sqlite_uni
+code => 1 }  ) || die "Cannot connect: $DBI::errstr";
$dbh->do('INSERT INTO Data (Tag1, Tag2) VALUES (?, ?)',
undef,
standardize($Tag1), standardize($Tag1));
$dbh->disconnect;

sub standardize{
 my $string=shift;
 #manipulating string
 $string=~ s/\'/\'\'/g;
 $string=~ s/^\s+|\s+$//g;
 $string=~ s/\t/ /g;
 return $string;
}
[download]

Why is the following (updatin an entry) not working? (Error message "syntax error")

my $ID_read=1;
my $dbh = DBI->connect( "dbi:SQLite:$DbFullPath", '', '', { sqlite_uni
+code => 1 }  ) || die "Cannot connect: $DBI::errstr";
$dbh->do ('UPDATE Data SET Tag1=?, Tag2=? WHERE ID=$ID_read VALUES (?,
+ ?)',
undef,
standardize($Tag1), standardize($Tag1));
$dbh->disconnect;
[download]

Comment on Update Sqlite References Select or Download Code

Replies are listed 'Best First'.
Re: Update Sqlite References by 1nickt (Canon) on Jan 05, 2017 at 20:19 UTC
You don't need VALUES in the latter case of UPDATE. Try something like: `$dbh->do('UPDATE Data SET Tag1=?, Tag2=? WHERE ID=$ID_read', undef, standardize($Tag1), standardize($Tag2) );` [download] But consider using a placeholder for the WHERE clause also, and pass `$ID_read` as a bind variable. Edit: I see you found that was the problem, cool! Hope this helps! The way forward always starts with a minimal test.	[reply] [d/l] [select]
Re^2: Update Sqlite References by kennethk (Abbot) on Jan 05, 2017 at 20:24 UTC
As an add on, the tutorials on SQL on w3schools (see http://www.w3schools.com/sql/sql_update.asp) may be helpful in getting up to speed. Note that it's got a platform bias built in so much of their CSS/Javascript isn't portable, but it's good for introduction. #11929 First ask yourself `How would I do this without a computer?' Then have the computer do it the same way.	[reply]
Re: Update Sqlite References by Anonymous Monk on Jan 05, 2017 at 20:25 UTC
I guess the problem was in WHERE ID=$ID_read. Using the following is okay: `my $sth = $dbh->prepare( 'update Data set Tag1=?, Tag2=? where ID=?' ); $sth->execute(StandardizeString($Tag1),StandardizeString($Tag2), $ID +_read);` [download]	[reply] [d/l]
Re^2: Update Sqlite References by NetWallah (Canon) on Jan 06, 2017 at 05:37 UTC
Yes - the reason your original code: `$dbh->do ('UPDATE Data SET Tag1=?, Tag2=? WHERE ID=$ID_read VALUES (?, + ?)',` [download] did not work is that $ID will NOT get interpolated, since the string is in single quotes. It could be fixed by: `$dbh->do ("UPDATE Data SET Tag1=?, Tag2=? WHERE ID=$ID_read VALUES (?, + ?)", # = + =` [download] ...it is unhealthy to remain near things that are in the process of blowing up. man page for WARP, by Larry Wall	[reply] [d/l] [select]