Re: [sqlite] to find table exists or not
On Thu, Dec 6, 2012 at 6:49 PM, Staffan Tylen wrote: > SELECT CASE WHEN EXISTS (...) END > > On Thu, Dec 6, 2012 at 5:47 PM, Durga D wrote: > >> I have situation in which I want to read particular record if table exists. >> Based on that record information, I have to execute some logic on other >> tables of the same database. If record doesn't exist I need to create new >> table. SELECT * FROM ATable WHERE EXISTS (SELECT * FROM sqlite_master where type = 'table' and name = '') Can also make it correlated if the name of the table whose existence you are checking depends on the value of a column in each record of ATable: SELECT * FROM ATable WHERE EXISTS (SELECT * FROM sqlite_master where type = 'table' and name = ATable.TableName) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] to find table exists or not
On 12/6/2012 11:47 AM, Durga D wrote:> I have situation in which I want to read particular record if table exists. Based on that record information, I have to execute some logic on other tables of the same database. If record doesn't exist I need to create new table. Prepare a statement to read that record from the table. If sqlite3_prepare fails, then the table doesn't exist. If it succeeds, run the statement as you normally would. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] to find table exists or not
SELECT CASE WHEN EXISTS (...) END Staffan On Thu, Dec 6, 2012 at 5:47 PM, Durga D wrote: > I have situation in which I want to read particular record if table exists. > Based on that record information, I have to execute some logic on other > tables of the same database. If record doesn't exist I need to create new > table. > > thanks for sharing your ideas. > > > On Thu, Dec 6, 2012 at 5:26 PM, Dave McKee wrote: > > > I can see situations in which you might want to create a new table with a > > name that wasn't already taken: at which point simple knowledge would let > > you reconsider your choice of table name without affecting the db in any > > way, success or fail. > > > > > > On Thu, Dec 6, 2012 at 10:57 AM, Hick Gunter wrote: > > > > > Both applications can be done without external logic using > > > > > > CREATE TABLE IF NOT EXISTS ... > > > (detect that the table is missing AND create it) > > > > > > and (if required) > > > > > > INSERT OR IGNORE INTO ... > > > (set missing options to default while keeping pre-set values) > > > > > > -Ursprüngliche Nachricht- > > > Von: Stephen Chrzanowski [mailto:pontia...@gmail.com] > > > Gesendet: Donnerstag, 06. Dezember 2012 11:21 > > > An: General Discussion of SQLite Database > > > Betreff: Re: [sqlite] to find table exists or not > > > > > > I can think of two reasons why you wouldn't want to blindly delete the > > > table, but verify that it exists. > > > > > > - First run of the program that creates the database from square one. > If > > > the table exists, skip over the create routine and continue. I > routinely > > > do this for creating an Options database (Program preferences, etc) > > instead > > > of relying on ini files (In Windows) since they're limited in volume of > > > data, and the kind of data that can be stored within. > > > > > > - If using a temporary table validating that the table exists to begin > > with > > > before you start throwing data at it. > > > > > > The wrapper I use contains a routine that returns a Boolean if the > table > > > exists or not, so I don't have to rely on constructing SQL statements. > > > > > > On Wed, Dec 5, 2012 at 9:57 AM, Hick Gunter wrote: > > > > > > > Why do you only want to see if the table is there? > > > > > > > > You can always do > > > > > > > > DROP TABLE IF EXISTS ... > > > > CREATE TABLE ... > > > > > > > > to replace the definition or > > > > > > > > CREATE TABLE IF NOT EXISTS ... > > > > > > > > to keep the old definition. > > > > > > > > -Ursprüngliche Nachricht- > > > > Von: Durga D [mailto:durga.d...@gmail.com] > > > > Gesendet: Mittwoch, 05. Dezember 2012 15:33 > > > > An: General Discussion of SQLite Database > > > > Betreff: [sqlite] to find table exists or not > > > > > > > > Hi all, > > > > > > > > > > > > I just want to find whether table exists or not in a database. > > > > > > > > > > > > Is it correct query? > > > > > > > > > > > > select distinct tbl_name from sqlite_master where tbl_name = 'abc'; > > > > > > > > > > > > Is there any other better way to find whether table exists or not. > > > > > > > > > > > > Thanks in advance. > > > > > > > > > > > > Regards, > > > > ___ > > > > sqlite-users mailing list > > > > sqlite-users@sqlite.org > > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > > > > > > > > > > -- > > > > Gunter Hick > > > > Software Engineer > > > > Scientific Games International GmbH > > > > Klitschgasse 2 - 4, A - 1130 Vienna, Austria > > > > FN 157284 a, HG Wien > > > > Tel: +43 1 80100 0 > > > > E-Mail: h...@scigames.at > > > > > > > > This e-mail is confidential and may well also be legally privileged. > If > > > > you have received it in error, you are on notice as to its status and >
Re: [sqlite] to find table exists or not
I have situation in which I want to read particular record if table exists. Based on that record information, I have to execute some logic on other tables of the same database. If record doesn't exist I need to create new table. thanks for sharing your ideas. On Thu, Dec 6, 2012 at 5:26 PM, Dave McKee wrote: > I can see situations in which you might want to create a new table with a > name that wasn't already taken: at which point simple knowledge would let > you reconsider your choice of table name without affecting the db in any > way, success or fail. > > > On Thu, Dec 6, 2012 at 10:57 AM, Hick Gunter wrote: > > > Both applications can be done without external logic using > > > > CREATE TABLE IF NOT EXISTS ... > > (detect that the table is missing AND create it) > > > > and (if required) > > > > INSERT OR IGNORE INTO ... > > (set missing options to default while keeping pre-set values) > > > > -Ursprüngliche Nachricht- > > Von: Stephen Chrzanowski [mailto:pontia...@gmail.com] > > Gesendet: Donnerstag, 06. Dezember 2012 11:21 > > An: General Discussion of SQLite Database > > Betreff: Re: [sqlite] to find table exists or not > > > > I can think of two reasons why you wouldn't want to blindly delete the > > table, but verify that it exists. > > > > - First run of the program that creates the database from square one. If > > the table exists, skip over the create routine and continue. I routinely > > do this for creating an Options database (Program preferences, etc) > instead > > of relying on ini files (In Windows) since they're limited in volume of > > data, and the kind of data that can be stored within. > > > > - If using a temporary table validating that the table exists to begin > with > > before you start throwing data at it. > > > > The wrapper I use contains a routine that returns a Boolean if the table > > exists or not, so I don't have to rely on constructing SQL statements. > > > > On Wed, Dec 5, 2012 at 9:57 AM, Hick Gunter wrote: > > > > > Why do you only want to see if the table is there? > > > > > > You can always do > > > > > > DROP TABLE IF EXISTS ... > > > CREATE TABLE ... > > > > > > to replace the definition or > > > > > > CREATE TABLE IF NOT EXISTS ... > > > > > > to keep the old definition. > > > > > > -Ursprüngliche Nachricht- > > > Von: Durga D [mailto:durga.d...@gmail.com] > > > Gesendet: Mittwoch, 05. Dezember 2012 15:33 > > > An: General Discussion of SQLite Database > > > Betreff: [sqlite] to find table exists or not > > > > > > Hi all, > > > > > > > > > I just want to find whether table exists or not in a database. > > > > > > > > > Is it correct query? > > > > > > > > > select distinct tbl_name from sqlite_master where tbl_name = 'abc'; > > > > > > > > > Is there any other better way to find whether table exists or not. > > > > > > > > > Thanks in advance. > > > > > > > > > Regards, > > > ___ > > > sqlite-users mailing list > > > sqlite-users@sqlite.org > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > > > > -- > > > Gunter Hick > > > Software Engineer > > > Scientific Games International GmbH > > > Klitschgasse 2 - 4, A - 1130 Vienna, Austria > > > FN 157284 a, HG Wien > > > Tel: +43 1 80100 0 > > > E-Mail: h...@scigames.at > > > > > > This e-mail is confidential and may well also be legally privileged. If > > > you have received it in error, you are on notice as to its status and > > > accordingly please notify us immediately by reply e-mail and then > delete > > > this message from your system. Please do not copy it or use it for any > > > purposes, or disclose its contents to any person as to do so could be a > > > breach of confidence. Thank you for your cooperation. > > > ___ > > > sqlite-users mailing list > > > sqlite-users@sqlite.org > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > __
Re: [sqlite] to find table exists or not
I can see situations in which you might want to create a new table with a name that wasn't already taken: at which point simple knowledge would let you reconsider your choice of table name without affecting the db in any way, success or fail. On Thu, Dec 6, 2012 at 10:57 AM, Hick Gunter wrote: > Both applications can be done without external logic using > > CREATE TABLE IF NOT EXISTS ... > (detect that the table is missing AND create it) > > and (if required) > > INSERT OR IGNORE INTO ... > (set missing options to default while keeping pre-set values) > > -Ursprüngliche Nachricht- > Von: Stephen Chrzanowski [mailto:pontia...@gmail.com] > Gesendet: Donnerstag, 06. Dezember 2012 11:21 > An: General Discussion of SQLite Database > Betreff: Re: [sqlite] to find table exists or not > > I can think of two reasons why you wouldn't want to blindly delete the > table, but verify that it exists. > > - First run of the program that creates the database from square one. If > the table exists, skip over the create routine and continue. I routinely > do this for creating an Options database (Program preferences, etc) instead > of relying on ini files (In Windows) since they're limited in volume of > data, and the kind of data that can be stored within. > > - If using a temporary table validating that the table exists to begin with > before you start throwing data at it. > > The wrapper I use contains a routine that returns a Boolean if the table > exists or not, so I don't have to rely on constructing SQL statements. > > On Wed, Dec 5, 2012 at 9:57 AM, Hick Gunter wrote: > > > Why do you only want to see if the table is there? > > > > You can always do > > > > DROP TABLE IF EXISTS ... > > CREATE TABLE ... > > > > to replace the definition or > > > > CREATE TABLE IF NOT EXISTS ... > > > > to keep the old definition. > > > > -----Ursprüngliche Nachricht- > > Von: Durga D [mailto:durga.d...@gmail.com] > > Gesendet: Mittwoch, 05. Dezember 2012 15:33 > > An: General Discussion of SQLite Database > > Betreff: [sqlite] to find table exists or not > > > > Hi all, > > > > > > I just want to find whether table exists or not in a database. > > > > > > Is it correct query? > > > > > > select distinct tbl_name from sqlite_master where tbl_name = 'abc'; > > > > > > Is there any other better way to find whether table exists or not. > > > > > > Thanks in advance. > > > > > > Regards, > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > -- > > Gunter Hick > > Software Engineer > > Scientific Games International GmbH > > Klitschgasse 2 - 4, A - 1130 Vienna, Austria > > FN 157284 a, HG Wien > > Tel: +43 1 80100 0 > > E-Mail: h...@scigames.at > > > > This e-mail is confidential and may well also be legally privileged. If > > you have received it in error, you are on notice as to its status and > > accordingly please notify us immediately by reply e-mail and then delete > > this message from your system. Please do not copy it or use it for any > > purposes, or disclose its contents to any person as to do so could be a > > breach of confidence. Thank you for your cooperation. > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > -- > Gunter Hick > Software Engineer > Scientific Games International GmbH > Klitschgasse 2 – 4, A - 1130 Vienna, Austria > FN 157284 a, HG Wien > Tel: +43 1 80100 0 > E-Mail: h...@scigames.at > > This e-mail is confidential and may well also be legally privileged. If > you have received it in error, you are on notice as to its status and > accordingly please notify us immediately by reply e-mail and then delete > this message from your system. Please do not copy it or use it for any > purposes, or disclose its contents to any person as to do so could be a > breach of confidence. Thank you for your cooperation. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] to find table exists or not
Both applications can be done without external logic using CREATE TABLE IF NOT EXISTS ... (detect that the table is missing AND create it) and (if required) INSERT OR IGNORE INTO ... (set missing options to default while keeping pre-set values) -Ursprüngliche Nachricht- Von: Stephen Chrzanowski [mailto:pontia...@gmail.com] Gesendet: Donnerstag, 06. Dezember 2012 11:21 An: General Discussion of SQLite Database Betreff: Re: [sqlite] to find table exists or not I can think of two reasons why you wouldn't want to blindly delete the table, but verify that it exists. - First run of the program that creates the database from square one. If the table exists, skip over the create routine and continue. I routinely do this for creating an Options database (Program preferences, etc) instead of relying on ini files (In Windows) since they're limited in volume of data, and the kind of data that can be stored within. - If using a temporary table validating that the table exists to begin with before you start throwing data at it. The wrapper I use contains a routine that returns a Boolean if the table exists or not, so I don't have to rely on constructing SQL statements. On Wed, Dec 5, 2012 at 9:57 AM, Hick Gunter wrote: > Why do you only want to see if the table is there? > > You can always do > > DROP TABLE IF EXISTS ... > CREATE TABLE ... > > to replace the definition or > > CREATE TABLE IF NOT EXISTS ... > > to keep the old definition. > > -Ursprüngliche Nachricht- > Von: Durga D [mailto:durga.d...@gmail.com] > Gesendet: Mittwoch, 05. Dezember 2012 15:33 > An: General Discussion of SQLite Database > Betreff: [sqlite] to find table exists or not > > Hi all, > > > I just want to find whether table exists or not in a database. > > > Is it correct query? > > > select distinct tbl_name from sqlite_master where tbl_name = 'abc'; > > > Is there any other better way to find whether table exists or not. > > > Thanks in advance. > > > Regards, > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > -- > Gunter Hick > Software Engineer > Scientific Games International GmbH > Klitschgasse 2 - 4, A - 1130 Vienna, Austria > FN 157284 a, HG Wien > Tel: +43 1 80100 0 > E-Mail: h...@scigames.at > > This e-mail is confidential and may well also be legally privileged. If > you have received it in error, you are on notice as to its status and > accordingly please notify us immediately by reply e-mail and then delete > this message from your system. Please do not copy it or use it for any > purposes, or disclose its contents to any person as to do so could be a > breach of confidence. Thank you for your cooperation. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] to find table exists or not
I can think of two reasons why you wouldn't want to blindly delete the table, but verify that it exists. - First run of the program that creates the database from square one. If the table exists, skip over the create routine and continue. I routinely do this for creating an Options database (Program preferences, etc) instead of relying on ini files (In Windows) since they're limited in volume of data, and the kind of data that can be stored within. - If using a temporary table validating that the table exists to begin with before you start throwing data at it. The wrapper I use contains a routine that returns a Boolean if the table exists or not, so I don't have to rely on constructing SQL statements. On Wed, Dec 5, 2012 at 9:57 AM, Hick Gunter wrote: > Why do you only want to see if the table is there? > > You can always do > > DROP TABLE IF EXISTS ... > CREATE TABLE ... > > to replace the definition or > > CREATE TABLE IF NOT EXISTS ... > > to keep the old definition. > > -Ursprüngliche Nachricht- > Von: Durga D [mailto:durga.d...@gmail.com] > Gesendet: Mittwoch, 05. Dezember 2012 15:33 > An: General Discussion of SQLite Database > Betreff: [sqlite] to find table exists or not > > Hi all, > > > I just want to find whether table exists or not in a database. > > > Is it correct query? > > > select distinct tbl_name from sqlite_master where tbl_name = 'abc'; > > > Is there any other better way to find whether table exists or not. > > > Thanks in advance. > > > Regards, > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > -- > Gunter Hick > Software Engineer > Scientific Games International GmbH > Klitschgasse 2 – 4, A - 1130 Vienna, Austria > FN 157284 a, HG Wien > Tel: +43 1 80100 0 > E-Mail: h...@scigames.at > > This e-mail is confidential and may well also be legally privileged. If > you have received it in error, you are on notice as to its status and > accordingly please notify us immediately by reply e-mail and then delete > this message from your system. Please do not copy it or use it for any > purposes, or disclose its contents to any person as to do so could be a > breach of confidence. Thank you for your cooperation. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] to find table exists or not
Why do you only want to see if the table is there? You can always do DROP TABLE IF EXISTS ... CREATE TABLE ... to replace the definition or CREATE TABLE IF NOT EXISTS ... to keep the old definition. -Ursprüngliche Nachricht- Von: Durga D [mailto:durga.d...@gmail.com] Gesendet: Mittwoch, 05. Dezember 2012 15:33 An: General Discussion of SQLite Database Betreff: [sqlite] to find table exists or not Hi all, I just want to find whether table exists or not in a database. Is it correct query? select distinct tbl_name from sqlite_master where tbl_name = 'abc'; Is there any other better way to find whether table exists or not. Thanks in advance. Regards, ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] to find table exists or not
On 5 Dec 2012, at 2:33pm, Durga D wrote: > I just want to find whether table exists or not in a database. > > > Is it correct query? > > > select distinct tbl_name from sqlite_master where tbl_name = 'abc'; This may return zero lines or many. Because indexes and other pieces of information for a table can have the same value in the 'tbl_name' column. So you can do this, but don't test to see if it returns 1 line, test to see if it returns zero lines. Or you can do SELECT name FROM sqlite_master WHERE tbl_name = 'abc' AND type='table' > Is there any other better way to find whether table exists or not. You can do PRAGMA table_info(table-name) and see what comes back. Can't check it now, but I think it gives an error if the table doesn't exist. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] to find table exists or not
Hi all, I just want to find whether table exists or not in a database. Is it correct query? select distinct tbl_name from sqlite_master where tbl_name = 'abc'; Is there any other better way to find whether table exists or not. Thanks in advance. Regards, ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
