Re: [sqlite] checking if a table exists
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
li...@jspect.fastmail.fm
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] checking if a table exists
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 "\nError: %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 "\nError: %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 "\nError: %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 <ikoro...@gmail.com> wrote:
> 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(, of values to bind>)
> >>> cur.execute("select count(*) from sqlite_master where type = 'table'
> and name = ?;", (,))
> >>>
> >>>
> >>> -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 ta
Re: [sqlite] checking if a table exists
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(, >> values to bind>)
>>> cur.execute("select count(*) from sqlite_master where type = 'table' and
>>> name = ?;", (,))
>>>
>>>
>>> -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
Re: [sqlite] checking if a table exists
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 <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(, > values to bind>)
>> cur.execute("select count(*) from sqlite_master where type = 'table' and
>> name = ?;", (,))
>>
>>
>> -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
Re: [sqlite] checking if a table exists
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(, values to bind>)
> cur.execute("select count(*) from sqlite_master where type = 'table' and name
> = ?;", (,))
>
>
> -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
Re: [sqlite] checking if a table exists
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(, )
cur.execute("select count(*) from sqlite_master where type = 'table' and name =
?;", (,))
-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’;
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
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] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
