Thunderstone Support Forums

In looking at the SYSCOLUMNS table, I've found that not all tables have entries for their columns. Why is this? Is there a way to automatically update SYSCOLUMNS to include info about all tables in my db?

Thanks,
David Fisher

Are you sure? Give a specific example please.

Have a looksee:

> tsql "select * from SYSCOLUMNS where TBNAME='bookorder'"
Texis Version 03.01.971289085(20001011) Copyright (c) 1988-2000 Thunderstone EPI

NAME TBNAME TYPE IDX NULLABLE SQLTYPE PRECIS LENGTH SCALE RADIX REMARK
------------+------------+------------+----
thumper.alibris.com> tsql -l 1 "select * from bookorder"
Texis Version 03.01.971289085(20001011) Copyright (c) 1988-2000 Thunderstone EPI

Cid Bookid Copies Date Substitute promo_id
------------+------------+------------+----------
3ae125f86 0000000000 13123 NULL



This is one example. We have at least 8 tables like this. Strange, huh?

-David

That is really strange. Texis has never forgotten to add things to the SYStem tables before. The only thing I can think of that would cause this is some kind of odd permissions issue with respect to tsql or vortex. Make sure that all the programs using the DB are setuid to the correct user.

Try: "select * from SYSCOLUMNS order by TBNAME"
and: "select * from SYSTABLES".
and "select * from SYSINDEX"

See if the corruption has extended beyond just SYSCOLUMNS. I would be very leary of the status of this database at this point.

If they are really gone from SYSCOLUMNS then something very odd happened when the tables were created. The entries can be restored with with the addtable command, but you'll probably have to recreate indexes.

How was the table created? copydb had an issue where it would sometimes have problems with SYSCOLUMNS where there were version differences.

It's possible that the tables were created using something like:

create table bookorder as select * from bookordertemp

The SYSINDEX and SYSTABLES tables seem to be correct for bookorder, as for the other tables that are missing from SYSCOLUMNS.

Exactly how can I use addtable to update SYSCOLUMNS? Can I do this without dropping the tables first? Any other implications of doing this?

Thanks,
-David

Run kdbfchk on SYSCOLUMNS to make sure it's not corrupt.

To use addtable, you need to rename bookorder.tbl to something else. Then drop table bookorder. Then rename bookorder.tbl back to it's real name. Then addtable bookorder.tbl . Then rebuild all indices on bookorder (they are automatically dropped when you drop the table).

Thanks Mark & Bart... dropping/adding worked like a charm. I appreciate your help.

Best,
David