Am 24.07.2006 um 15:09 schrieb Martin Jenkins:
This is all good when the fields(columns) are known in advance,
but how
would I attempt doing this without knowing the name of the fields?
You need to recast your problem into one where the field names ARE
known. ;) Just issue a "pragma table_info(table_name)" as shown
below in an sqlite shell session. You'll do this via your VB wrapper.
SQLite version 3.3.4
Enter ".help" for instructions
sqlite> pragma table_info(servers);
0|svrId|INTEGER|0||1
1|svrName|TEXT|0||0
2|lastDate|TEXT|0||0
3|lastTime|TEXT|0||0
4|uname|TEXT|0||0
5|passwd|TEXT|0||0
6|cost|TEXT|0||0
If you want to see the info sqlite holds about the table then:
sqlite> select * from sqlite_master where name="servers";
table|servers|servers|2|CREATE TABLE servers(svrId INTEGER PRIMARY
KEY, svrName TEXT,
lastDate TEXT, lastTime TEXT, uname TEXT, passwd TEXT, cost TEXT)
If you want to see the SQL that was originally used to create the
table then:
sqlite> select sql from sqlite_master where name="servers";
CREATE TABLE servers(svrId INTEGER PRIMARY KEY, svrName TEXT,
lastDate TEXT,
> lastTime TEXT, uname TEXT, passwd TEXT, cost TEXT)
Again, this should work through your wrapper. Add your extra column
names before the closing brace and Robert is your mother's
husband's brother. ;)
Alternatively, if you don't care about column types, indices, etc.
you can also do this:
Assume table t1 is the unknown table you need to extend:
CREATE TEMP table t1_copy AS SELECT * FROM t1;
DROP TABLE t1;
CREATE TABLE t1 AS SELECT t1_copy.*, NULL AS foo FROM t1_copy;
This will reproduce the data including an empty new column named
'foo' in t1.
Note, though, that this will _not_ recreate the table one to one,
namely you'll loose and indices on the table as well as column type
declarations.
(However, for this simple scenario, you could as well use ALTER
TABLE...)
YMMV,
</jum>
