Hi, On Fri, Sep 22, 2017 at 4:42 PM, mikeegg1 <mikee...@mac.com> wrote: > 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.
Try following query: SELECT 1 FROM sqlite_master WHERE type = 'table' and name = ?;"; And then bind the actual variable to the query. If the execution return a row the table exists. Thank you. > > Mike > >> On Sep 22, 2017, at 15:24, Igor Korot <ikoro...@gmail.com> wrote: >> >> Hi, >> >> On Fri, Sep 22, 2017 at 4:04 PM, David Raymond <david.raym...@tomtom.com> >> 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:sqlite-users-boun...@mailinglists.sqlite.org] 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 >>> sqlite-users@mailinglists.sqlite.org >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>> _______________________________________________ >>> sqlite-users mailing list >>> sqlite-users@mailinglists.sqlite.org >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users