Re: [sqlite] would someone check my SQL..

[Witold Czarnecki]

You may try:
SELECT NULL FROM sqlite_master WHERE tbl_name = 'table';
- Original Message - 
From: "Asko Kauppi" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Thursday, February 10, 2005 5:08 PM
Subject: Re: [sqlite] would someone check my SQL..


Is it true that in SQLite one has no way to check (at CREATE TABLE 
statement itself) whether the table already is there?  I got this from 
a person more accustomed to MySQL:

  >in mysql, the CREATE TABLE command has a 'IF NOT EXISTS' option, but 
as i
  >said, it's a non-standard extension.
  >
  >you could do a "SELECT * FROM table LIMIT 1" and check only if 
there's an
  >error to verify the existence of the table.

Sure, I can do that but.. shouldn't there be a less elaborate way?
-ak

Re: [sqlite] would someone check my SQL..

[Asko Kauppi]

Is it true that in SQLite one has no way to check (at CREATE TABLE 
statement itself) whether the table already is there?  I got this from 
a person more accustomed to MySQL:

  >in mysql, the CREATE TABLE command has a 'IF NOT EXISTS' option, but 
as i
  >said, it's a non-standard extension.
  >
  >you could do a "SELECT * FROM table LIMIT 1" and check only if 
there's an
  >error to verify the existence of the table.

Sure, I can do that but.. shouldn't there be a less elaborate way?
-ak

[sqlite] would someone check my SQL..

[Asko Kauppi]

I'm crafting a storage module based on SQLite internally.  Think about 
it as 'ini files, but with a database'.  Since this is my first touch 
to SQL, and the number of statements I need is rediculously small, I'd 
like some kind soul to 'proof-read' these and give comments.

Each SQL block is a single statement, and I precompile them with a 
certain 'section' name baked into the code. Here goes..

Table creation:
Loc_PrepareSql( db, ,
"CREATE TABLE '%q' ( key TEXT UNIQUE ON CONFLICT 
REPLACE,"
   " val VARIANT );", section );

	Q #1: What happens if the table already exists?  Should I check for 
that?  How?

Value setting & getting:
Loc_PrepareSql( db, >sql_set,
"UPDATE '%q' SET val=?2 WHERE key='?1';", section );
Loc_PrepareSql( db, >sql_remove_key,
"DELETE FROM '%q' WHERE key='?1';", section );
Loc_PrepareSql( db, >sql_get,
"SELECT val FROM '%q' WHERE key='?1';", section );
Key enumeration (returns 1..N keys):
Loc_PrepareSql( db, >sql_all_keys,
"SELECT key FROM '%q';", section );
Key enumeration with a prefix (ie. "subtable.b."):
Loc_PrepareSql( db, >sql_like_keys,
"SELECT key FROM '%q' WHERE key= LIKE '?1.%';", 
section );

Similar, returns 1..N key + value pairs:
Loc_PrepareSql( db, >sql_like_dump,
"SELECT key,val FROM '%q' WHERE key= LIKE '?1.%';", 
section );

Loc_PrepareSql( db, >sql_transaction_begin, "BEGIN;", NULL );
Loc_PrepareSql( db, >sql_transaction_commit, "COMMIT;", NULL );
Thanks for help, feedback, and encouragement. :)
-ak