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
