I am working on converting my system table from one form to another. The
old form was one row per value with a category/key/value (DBLookup) , the
new form is a separate column for each value (PP_VIEWER_SETTINGS). I am
trying to create an insert statement to run when the new table is created,
but when I run it, it does not work nor do I get any errors in SQLite
manager:
/* The OLD one */
CREATE TABLE DBLookup (
Category VARCHAR(32) NOT NULL,
ItemName VARCHAR(128) NOT NULL,
ItemValue VARCHAR(3000) NOT NULL,
PRIMARY KEY(Category, ItemName))
/* The NEW one */
CREATE TABLE PP_VIEWER_SETTINGS
(
VIEWER_SETTINGS_ID INTEGER PRIMARY KEY
AUTOINCREMENT,
COMPANY_NAME VARCHAR( 260) NOT NULL,
DSPNEXTPREVIOUS SMALLINT NOT NULL,
ENABLE_CARTS SMALLINT NOT NULL,
ENABLE_DEBUGINFO SMALLINT NOT NULL,
ENABLE_FAVORITES SMALLINT NOT NULL,
ENABLE_RIGHTCLICK SMALLINT NOT NULL,
ENABLE_SLIDESHOW SMALLINT NOT NULL,
ENABLE_TIMEOUT SMALLINT NOT NULL,
EXIT_KVS SMALLINT NOT NULL,
EXIT_PASSWORD VARCHAR( 20) NOT NULL,
IS_CART_FAVORITES SMALLINT NOT NULL,
IS_LOGIN_REQUIRED SMALLINT NOT NULL,
IMAGE_SIZE INTEGER NOT NULL,
PHONE_NUM_FORMAT VARCHAR( 20) NOT NULL,
THEME_ID INTEGER NOT NULL,
THUMBNAIL_SIZE SMALLINT NOT NULL,
TICKER_MSG VARCHAR( 260) NOT NULL,
TO_AFTER SMALLINT NOT NULL,
TO_STARTS SMALLINT NOT NULL,
TO_TRANSITION_SECS SMALLINT NOT NULL,
SS_COUNT SMALLINT NOT NULL,
SS_DEFAULT_IS_IN_SLIDESHOW SMALLINT NOT NULL,
SS_DISPLAY_SECONDS DOUBLE PRECISION NOT NULL,
SS_ZOOM_FACTOR DOUBLE PRECISION NOT NULL,
USERLAN VARCHAR( 260) NOT NULL
);
/* The insert script */
insert into PP_VIEWER_SETTINGS
( COMPANY_NAME, DSPNEXTPREVIOUS, ENABLE_CARTS, ENABLE_DEBUGINFO,
ENABLE_FAVORITES, ENABLE_RIGHTCLICK, ENABLE_SLIDESHOW,
ENABLE_TIMEOUT, EXIT_KVS, EXIT_PASSWORD, IS_CART_FAVORITES,
IS_LOGIN_REQUIRED, IMAGE_SIZE, PHONE_NUM_FORMAT, THEME_ID,
THUMBNAIL_SIZE, TICKER_MSG, TO_AFTER, TO_STARTS, TO_TRANSITION_SECS,
SS_COUNT, SS_DEFAULT_IS_IN_SLIDESHOW,
SS_DISPLAY_SECONDS, SS_ZOOM_FACTOR, USERLAN)
values (
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "Company"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "DspNextPrevious"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "EnableCarts"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "EnableDebugInfo"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "EnableFavorites"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "EnableRightClick"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "EnableSlideShow"),
1,
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "ExitKvs"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "ExitPassword"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "IsCartFavorites"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "IsLoginRequired"),
900,
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "PhoneNumberFormat"),
0,
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "ThumbnailSize"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "TickerMsg"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "TimeoutAfter"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "TimeoutStarts"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "TransitionSeconds"),
0,
(select ItemValue from dblookup where Category =
"SlideShowSettings" and ItemName = "DefaultIsInSlideShow"),
(select ItemValue from dblookup where Category =
"SlideShowSettings" and ItemName = "DisplaySeconds"),
(select ItemValue from dblookup where Category =
"SlideShowSettings" and ItemName = "ZoomFactor"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "USERLAN"));
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
