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
