On 23/09/2017 05:36, mikeegg1 wrote:
I’m using the statement:
select count(*) from sqlite_master where type = 'table' and name = ‘$NAME’;
This statement works fine in the sqlite3 shell. This statement does not work in
my API.
Is there a PRAGMA I need to issue so I can check for table existence?
TIA
Mike
PERL code to check for table existence:
#> sub tableexists($$) {
prototyping depricated practice carried over from perl 4
sub tableexists {
my $dbh = shift;
my $name = shift;
my $tableexists = 0;
#> $dbh->do("pragma writable_schema = 'on';");
# neither recommended nor needed
#> my $sql = "select count(*) from sqlite_master where type =
'table' and name = '$name';";
my $sql = "select count(*) from sqlite_master where type = 'table' and
name = ?;";
my $stmt = $dbh->prepare($sql);
#> $stmt->execute or die "$0: verifying table name failed:
$DBI::errstr";
$stmt->execute($name)
or die "$0: verifying table name failed: $DBI::errstr";
while(my @row = $stmt->fetchrow_array) {
$tableexists = $row[0];
}
$stmt->finish;
#> $dbh->do("pragma writable_schema = 'off';");
return $tableexists;
}
Alternatively (not tested)
sub tableexists {
my $dbh = shift;
my $name = shift;
my $sql = "select count(*) from sqlite_master where type = 'table'
and name = '$name';";
# stmt only executed once, $name only evaluated once
my ($tableexists) = $dbh->selectrow_array($sql);
# selectrow returns 1 row, the stmt returns 1 element in list context
return $tableexists;
}
my $check_table = tableexists($dbh, $name);
John
--
Regards
John McMahon
li...@jspect.fastmail.fm
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
