Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2007
    Posts
    2

    Unanswered: Translate Column Data Type

    I am new to Informix and I am trying to create an output that is a listing of columns in a table and their respective attributes (i.e. column data type, data length...) I am retrieving this data from syscolumns but find the datatype is a numeric code. How do I translate this numeric code to a verbal description like the one displayed when you do column INFO on a table? Thanks in advance for any help.

  2. #2
    Join Date
    May 2004
    Location
    New York
    Posts
    248
    run dbschema on the table then do a select from syscolumns on the same table and match up values from one output to the other

  3. #3
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  4. #4
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    Hi,

    Put this in a script and you'll get the output you want. I use this in a shell script so I can query a table from the command line by invoking the script with the tablename as argument...
    Code:
    dbaccess database 2>/dev/null<<SQL!                                          
                                                                                    
    SELECT c.colno, c.colname[1,20] column,                            
        CASE coltype                                                                
            WHEN 0 THEN 'char(' || TRIM(CAST (c.collength AS CHAR(5))) || ')'       
            WHEN 1 THEN 'smallint'                                                  
            WHEN 2 THEN 'integer'                                                   
            WHEN 3 THEN 'float'                                                     
            WHEN 4 THEN 'smallfloat'                                                
            WHEN 5 THEN 'decimal(' ||                                               
                TRIM(CAST(TRUNC(c.collength/256) AS VARCHAR(8)) || ',' ||           
                CAST(c.collength - TRUNC(c.collength/256)*256 AS VARCHAR(8))) || ')'
            WHEN 6 THEN 'serial'                                                    
            WHEN 7 THEN 'date'                                                      
            WHEN 8 THEN 'money(' ||                                                 
                TRIM(CAST(TRUNC(c.collength/256) AS VARCHAR(8)) || ',' ||           
                CAST(c.collength - TRUNC(c.collength/256)*256 AS VARCHAR(8))) || ')'
            WHEN 9 THEN 'null'                                                      
            WHEN 10 THEN 'datetime'                                                 
            WHEN 11 THEN 'byte'                                                     
            WHEN 12 THEN 'text'                                                     
            WHEN 13 THEN 'varchar(' || TRIM(CAST(c.collength AS CHAR(5))) || ')'    
            WHEN 14 THEN 'interval'                                                 
            WHEN 15 THEN 'nchar(' || TRIM(CAST(c.collength AS CHAR(5))) || ')'      
            WHEN 16 THEN 'nvarchar(' || TRIM(CAST(c.collength AS CHAR(5))) || ')'   
            WHEN 17 THEN 'int8'                                                     
            WHEN 18 THEN 'serial8'                                                  
            WHEN 19 THEN 'set'                                                      
            WHEN 20 THEN 'multiset'                                                 
            WHEN 21 THEN 'list'                                                     
            WHEN 22 THEN 'row'                                                      
            WHEN 23 THEN 'collection'                                               
            WHEN 24 THEN 'rowdef'                                                   
            WHEN 256 THEN 'char(' || TRIM(CAST(c.collength AS CHAR(5))) ||          
                ') not null'                                                        
            WHEN 257 THEN 'smallint not null'                                       
            WHEN 258 THEN 'integer not null'                                        
            WHEN 259 THEN 'float not null'                                          
            WHEN 260 THEN 'smallfloat not null'                                     
            WHEN 261 THEN 'decimal('||                                              
                TRIM(CAST(TRUNC(c.collength/256) AS VARCHAR(8)) || ',' ||           
                CAST(c.collength - TRUNC(c.collength/256)*256 AS VARCHAR(8))) ||    
                ') not null'                                                        
            WHEN 262 THEN 'serial not null'                                         
            WHEN 263 THEN 'date not null'                                           
            WHEN 264 THEN 'money(' ||                                               
                TRIM(CAST(TRUNC(c.collength/256) AS VARCHAR(8)) || ',' ||           
                CAST(c.collength - TRUNC(c.collength/256)*256 AS VARCHAR(8))) ||    
                ') not null'                                                        
            WHEN 265 THEN 'null not null'                                           
            WHEN 266 THEN 'datetime not null'                                       
            WHEN 267 THEN 'byte not null'                                           
            WHEN 268 THEN 'text not null'                                           
            WHEN 269 THEN 'varchar(' || TRIM(CAST(c.collength AS CHAR(5))) ||       
                ') not null'                                                        
            WHEN 270 THEN 'interval not null'                                       
            WHEN 271 THEN 'nchar(' || TRIM(CAST(c.collength AS CHAR(5))) ||         
                ') not null'                                                        
            WHEN 272 THEN 'nvarchar(' || TRIM(CAST(c.collength AS CHAR(5))) ||      
                ') not null'                                                        
            WHEN 273 THEN 'int8 not null'                                           
            WHEN 274 THEN 'serial8 not null'                                        
            WHEN 275 THEN 'set not null'                                            
            WHEN 276 THEN 'multiset not null'                                       
            WHEN 277 THEN 'list not null'                                           
            WHEN 278 THEN 'row not null'                                            
            WHEN 279 THEN 'collection not null'                                     
            WHEN 280 THEN 'rowdef not null'                                         
            ELSE CAST(coltype AS CHAR(10))                                          
        END datatype                                                                
    FROM systables t, syscolumns c                                                  
    WHERE tabname = "${1}"                                                  
    AND t.tabid = c.tabid                                                           
    ORDER BY 1                                                                      
                                                                                    
    SQL!
    Regards
    Last edited by Tyveleyn; 06-22-07 at 20:15.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •