I could use the “if not exist” syntax, and do in some places, but this is a SELECT. I need to verify the table exists before I attempt to pull data from it to prevent throwing and error.
Mike > On Sep 22, 2017, at 15:24, Igor Korot <[email protected]> wrote: > > Hi, > > On Fri, Sep 22, 2017 at 4:04 PM, David Raymond <[email protected]> > wrote: >> Not familiar with perl, but... >> >> -You should never need to do writable_schema = on for any normal operation. >> You can always read the schema even without that. >> >> -To my untrained eye it looks like you made a "name" variable, but never >> actually bound it to the statement. Usually that would involve some form of >> bind function call or providing it as an argument to the execute function. >> >> For example Python: cur.execute(<statement with ?'s to bind to>, <list of >> values to bind>) >> cur.execute("select count(*) from sqlite_master where type = 'table' and >> name = ?;", (<table name you're checking>,)) >> >> >> -----Original Message----- >> From: sqlite-users [mailto:[email protected]] On >> Behalf Of mikeegg1 >> Sent: Friday, September 22, 2017 3:36 PM >> To: SQLite mailing list >> Subject: [sqlite] checking if a table exists >> >> I’m using the statement: >> >> select count(*) from sqlite_master where type = 'table' and name = ‘$NAME’; > > If you write the query like this it will check for the table called "$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($$) { >> my $dbh = shift; >> my $name = shift; >> my $tableexists = 0; >> >> $dbh->do("pragma writable_schema = 'on';"); >> >> my $sql = "select count(*) from sqlite_master where type = 'table' and >> name = '$name';"; >> my $stmt = $dbh->prepare($sql); >> $stmt->execute 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; >> } > > I think SQLite support "CREATE TABLE IF NOT EXIST...." syntax. > So you don't have to check for the table existence and just execute > such query. > > Thank you. > >> >> _______________________________________________ >> sqlite-users mailing list >> [email protected] >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> _______________________________________________ >> sqlite-users mailing list >> [email protected] >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
