checking if a table exists

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

checking if a table exists

mikeegg1
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($$) {
    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;
}

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: checking if a table exists

David Raymond
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:[hidden email]] 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’;

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;
}

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: checking if a table exists

Igor Korot
Hi,

On Fri, Sep 22, 2017 at 4:04 PM, David Raymond <[hidden email]> 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:[hidden email]] 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: checking if a table exists

mikeegg1
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 <[hidden email]> wrote:
>
> Hi,
>
> On Fri, Sep 22, 2017 at 4:04 PM, David Raymond <[hidden email]> 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:[hidden email]] 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: checking if a table exists

Igor Korot
Hi,

On Fri, Sep 22, 2017 at 4:42 PM, mikeegg1 <[hidden email]> 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 <[hidden email]> wrote:
>>
>> Hi,
>>
>> On Fri, Sep 22, 2017 at 4:04 PM, David Raymond <[hidden email]> 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:[hidden email]] 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
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: checking if a table exists

Brian Curley
This is for a bash function, but you can adapt it for perl as you need to.

PRAGMA table_info() will poll the schema for you, and you can grep for your
table name:

function sq3_info () {

   #  Debug/verbose mode...
   if [ "${1}" == "-v" ]; then
      set -x
   fi

   not_sqlite="Error: file is encrypted or is not a database"  2>&1

   if [ -e ${2:-${1}} ] ;then
         #  Invalid first argument...
            #  STFU
         if [ "${1}" != "-v" ] \
            && [ "$(echo ${1} |cut -b 1 )" == "-" ]; then
               printf "\n    Error:  %s \n" "Invalid arg1... "
               return
         #  File exists, but...it's either zero byte or not a SQLite
file...
            #  GTFO
         elif [ -z "$(head -1 ${2:-${1}} |grep 'Lite format' )" ] \
            || [ "$(sqlite3 ${2:-${1}} .tab 2>/dev/null )" ==
"${not_sqlite}" ] ;then
               printf "\n    Error:  %s \n" "${not_sqlite}"
               return
         fi
         #  Hey, look...a real file!
            #  sed is :
            #     a) inserting additional column...only on Line 1
            #     b) inserting table name values...only on Line 2+
         hdr=$(for i in $(sqlite3 ${2:-${1}} '.tables' |tr -d [:cntrl:] )
                  do sqlite3 -header ${2:-${1}} "pragma table_info(${i});" \
                     |sed '1 s/^/table_nm|/g;1!s/^/'${i}'|/g;'
                  done
                  )
         #  Print hdr content...
            #  Line 1 only
         printf "%s\n" ${hdr} |awk 'NR == 1 { print };'
            #  Line 2 onward...minus repeats column headers and sorted by
object and field order...
         printf "%s\n" ${hdr} |awk 'NR != 1 { print };' |sort -t'|' -k1,1
-k2,2n |grep -v "^table_nm"
   elif [ ! -e ${1} ]; then
      printf "\n    Error:  %s \n"  "No SQLite file found..."
   fi

   #  Turn off debug/verbose mode...IF it was us that did it...
   if [ "${1}" == "-v" ] \
      && [ ! -z "$(set |grep xtrace )" ]; then
         set +x
   fi

}



Regards.

Brian P Curley
  cell: 845 548-4377


On Fri, Sep 22, 2017 at 5:01 PM, Igor Korot <[hidden email]> wrote:

> Hi,
>
> On Fri, Sep 22, 2017 at 4:42 PM, mikeegg1 <[hidden email]> 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 <[hidden email]> wrote:
> >>
> >> Hi,
> >>
> >> On Fri, Sep 22, 2017 at 4:04 PM, David Raymond <
> [hidden email]> 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-bounces@
> 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
> >>> [hidden email]
> >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>> _______________________________________________
> >>> sqlite-users mailing list
> >>> [hidden email]
> >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >> _______________________________________________
> >> sqlite-users mailing list
> >> [hidden email]
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: checking if a table exists

John McMahon-2
In reply to this post by mikeegg1


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
       [hidden email]


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users