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 <[email protected]> wrote:
> Hi,
>
> On Fri, Sep 22, 2017 at 4:42 PM, mikeegg1 <[email protected]> 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 <[email protected]> wrote:
> >>
> >> 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: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
> >>> [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
> >
> > _______________________________________________
> > 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
