Column 'column_name' not found

[Former Member]

I have a database view which returns syscolum and systable columns.

ALTER VIEW "DBA"."vBaseColumnDescriptions"
as 
select
(select user_name from SYS.SYSUSERPERM where user_id = SYS.SYSTABLE.creator) as creator,
    sys.SYSCOLUMN.column_name as cname,
    sys.SYSTABLE.table_name as tname,
    (select domain_name from SYS.SYSDOMAIN where domain_id = SYS.SYSCOLUMN.domain_id) as coltype,
    SYS.SYSCOLUMN.nulls,
    SYS.SYSCOLUMN.width as length,
    SYS.SYSCOLUMN.scale as syslength,
    SYS.SYSCOLUMN.pkey as in_primary_key,
    SYS.SYSCOLUMN.column_id as colno,
    SYS.SYSCOLUMN."default" AS defaut_value,
    sys.SYSCOLUMN.remarks as remarks
from SYS.SYSCOLUMN join SYS.SYSTABLE ON sys.SYSCOLUMN.table_id = sys.SYSTABLE.table_id

Problem is when I query this view from Visual Basic 6.0 application using an rdoResultset query is SELECT tname, remarks, cname, LENGTH AS width, coltype ,default_value FROM vBaseColumnDescriptions WHERE cname = 'SURNAME' ORDER BY tname

called rsResults like:

         strColName = rsResults!cName & ""
         strColName = rsResults!REMARKS & ""

rsResults!REMARKS raises an error: System Error 40002:S0002: [Sybase][ODBC Driver][SQL Anywhere]Column 'column_name' not found.

I can get information on the data column such as rsResults.rdoResultset("remarks").name and others, but the "value" raises the error.

The view and above query in Visual Basic 6.0 worked with Sybase ASA 9, but not with Sybase ASA 12.

[Breck_Carter]

Are you sure the SQLSTATE was 40002?

Caveat Emptor: This is a WAG...

Since the view looks OK, and runs OK in SQL Anywhere 12, the error message may be coming from somewhere else in the "call stack" between your VB code and the desired result.

To investigate this, try changing this line of code in the view

sys.SYSCOLUMN.column_name as cname,

to this

'xxx' as cname,

and run it again. You SHOULD get a completely different result, perhaps a completely different error message.

HOWEVER, if you get the same message, then there is some other reference to column_name happening.

[Former Member]

Hi Breck,

Yes the code was definitely 40002. I have discovered also, that the error message is the same for rsResults!Default_value.

Interestingly if I alter the view to: "convert(varchar(100),sys.SYSCOLUMN.remarks) as remarks" I don't get the error. It seems that the "long varchar" datatype in Sybase12 is handled differently by VB6 code than Sybase9.

[VolkerBarth]

IIRC, VB6 might have problems with reading long data from result sets (I guess, when over 4K, but that's a wild guess).

I don't think there is a change on the ASA9/SA12 side (unless you are using UNICODE now). Is the length of the (longest) remark in SA 12 different from that in ASA9?

[Breck_Carter]

So, dead chickens still work 🙂

40002 isn't mentioned in the V12 or V16 Help, must be a SQLSTEALTH value, and S0002 matches a zillion errors... but who cares? You can find the message in the alphabetic list.

FWIW the SYSTABLE and SYSCOLUMN tables don't exist in SQL Anywhere 12, they are views on top of SYSTAB and SYSTABCOL. Personally, when dealing with the catalog, I prefer working with the original source so to speak. Except inside Foxhound, of course, that has to deal with the catalogs from V5.5 through to 12 (and 16 coming)... so if you have to deal with legacy servers all bets are off.

Come to think of it, maybe SYSCOLUMN being a view rather than a table plays a role here. Here is what the two versions of SYSCOLUMN look like in Foxhound-speak (there are some interesting differences)... version 9 first, then 12:

-- SYS.SYSCOLUMN (table_id 2) in ddd9 - May 31 2013 9:33:08AM - Print - Foxhound © 2012 RisingRoad
CREATE TABLE SYS.SYSCOLUMN ( -- 1,431 rows, 172k total = 72k table + 0 ext + 100k index, 124 bytes per row
   table_id       /* PK FK     */ UNSIGNED INT NOT NULL,
   column_id      /* PK        */ UNSIGNED INT NOT NULL,
   pkey                           CHAR ( 1 ) NOT NULL,
   domain_id      /*    FK     */ SMALLINT NOT NULL,
   nulls                          CHAR ( 1 ) NOT NULL,
   width                          SMALLINT NOT NULL,
   scale                          SMALLINT NOT NULL,
   unused                         INTEGER NOT NULL,
   max_identity                   BIGINT NOT NULL,
   column_name                    CHAR ( 128 ) NOT NULL,
   remarks                        LONG VARCHAR NULL,
   "default"                      LONG VARCHAR NULL,
   unused2                        LONG VARCHAR NULL,
   user_type      /*    FK     */ SMALLINT NULL,
   format_str                     CHAR ( 128 ) NULL,
   column_type                    CHAR ( 1 ) NOT NULL,
   remote_name                    VARCHAR ( 128 ) NULL,
   remote_type                    UNSIGNED INT NULL,
   CONSTRAINT PRIMARY KEY ( -- 36k
      table_id,
      column_id )
 );
-- Parents of SYS.SYSCOLUMN
-- SYS.SYSDOMAIN 
-- SYS.SYSTABLE 
-- SYS.SYSUSERTYPE
-- Children
-- SYS.SYSARTICLECOL 
-- SYS.SYSCOLPERM 
-- SYS.SYSCOLSTAT 
-- SYS.SYSCONSTRAINT 
-- SYS.SYSFKCOL 
-- SYS.SYSIXCOL
ALTER TABLE SYS.SYSCOLUMN ADD CONSTRAINT SYSDOMAIN NOT NULL FOREIGN KEY ( -- 24k
      domain_id )
   REFERENCES SYS.SYSDOMAIN (
      domain_id )
   ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE SYS.SYSCOLUMN ADD CONSTRAINT SYSTABLE NOT NULL FOREIGN KEY ( -- 24k
      table_id )
   REFERENCES SYS.SYSTABLE (
      table_id )
   ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE SYS.SYSCOLUMN ADD CONSTRAINT SYSUSERTYPE FOREIGN KEY ( -- 16k
      user_type )
   REFERENCES SYS.SYSUSERTYPE (
      type_id )
   ON UPDATE RESTRICT ON DELETE RESTRICT;

-- SYS.SYSCOLUMN (table_id 580) in ddd12 - May 31 2013 9:17:51AM - Print - Foxhound © 2012 RisingRoad
-- CREATE VIEW SYS.SYSCOLUMN (
--    table_id,      -- UNSIGNED INT   
--    column_id,     -- UNSIGNED INT   
--    pkey,          -- CHAR ( 1 )   
--    domain_id,     -- SMALLINT   
--    nulls,         -- CHAR ( 1 )   
--    width,         -- BIGINT   
--    scale,         -- SMALLINT   
--    object_id,     -- UNSIGNED BIGINT   
--    max_identity,  -- BIGINT   
--    column_name,   -- CHAR ( 128 )   
--    remarks,       -- LONG VARCHAR   
--    "default",     -- LONG VARCHAR   
--    user_type,     -- SMALLINT   
--    column_type )  -- CHAR ( 1 )

create view SYS.SYSCOLUMN
  as select b.table_id,
    b.column_id,
    if c.sequence is null then 'N' else 'Y' endif as pkey,
    b.domain_id,
    b.nulls,
    b.width,
    b.scale,
    b.object_id,
    b.max_identity,
    b.column_name,
    r.remarks,
    b."default",
    b.user_type,
    b.column_type
    from SYS.SYSTABCOL as b
      left outer join SYS.ISYSREMARK as r on(b.object_id = r.object_id)
      left outer join SYS.ISYSIDXCOL as c on(b.table_id = c.table_id and b.column_id = c.column_id and c.index_id = 0)
;