On 2015/11/23 11:00 PM, chromedout64 at yahoo.com wrote:
> Maybe there's a technical reason that this functionality wasn't added to
> SQLite. Does anyone know?
I am not sure exactly what you intend with this.
Do you mean to Insert into a table a row with the default values? In
that case you can use SQLite's:
"INSERT INTO t DEFAULT VALUES;
If you mean for any specific item to gain its default value upon
inserting (but not the other fields), you can simply omit it from the
Insert statement.
If you mean to Insert any other literal value, you can simply do so with
either specifying in the SQL or adding named hooks and binding them.
If you mean to specify the default value for a single column among other
columns but do not wish to remove it from the inserted fields (as a lazy
programmer would), then I believe you are stuck - you have to omit it
from the field list.
Do you mean perhaps to use a specified value during table-creation as
the default? I have used functions with success (see example sql below),
so a user-defined function can be pressed into service for some
interesting control. However, if you are going to be creating tables in
your code (I mean, why else would you need literals?), then you could
just use string substitution:
"CREATE TABLE t (Val TEXT DEFAULT ?1);" can - with equal amounts of
effort - simply be:
"CREATE TABLE t (Val TEXT DEFAULT " + MyVar + ");".
If none of the above, then I am sorry for not following what you
intended - maybe explain more?
Quick script demonstrating some of it:
(The date function can easily be substituted for any user-defined function)
-- Processing SQL in: E:\Documents\SQLiteAutoScript.sql
-- Using SQLite version 3.8.11.1 on SQLitespeed version 2.0.1.23
-- Script Items: 6 Parameter Count: 0
-- 2015-11-24 19:50:05.296 | [Info] Script Initialized,
Started executing...
--
================================================================================================
CREATE TABLE t (
ID INTEGER PRIMARY KEY,
Val TEXT DEFAULT 'New Item',
Updated NUMERIC DEFAULT (datetime('now'))
);
INSERT INTO t DEFAULT VALUES;
INSERT INTO t (Val) VALUES ('Second Inserted Item');
SELECT * FROM t;
-- ID | Val | Updated
-- ------------ | ---------------------- | ---------------------
-- 1 | New Item | 2015-11-24 17:50:05
-- 2 | Second Inserted Item | 2015-11-24 17:50:05
DROP TABLE t;
-- Script Stats: Total Script Execution Time: 0d 00h 00m
and 00.030s
-- Total Script Query Time: 0d 00h 00m
and 00.014s
-- Total Database Rows Changed: 2
-- Total Virtual-Machine Steps: 210
-- Last executed Item Index: 6
-- Last Script Error:
--
------------------------------------------------------------------------------------------------
-- 2015-11-24 19:50:05.311 | [Success] Script Success.
-- 2015-11-24 19:50:05.311 | [Success] Transaction Rolled back.
