Wide tables

Adaptive Server Enterprise 12.5 supports tables with more than 255 columns and column sizes in excess of 255 bytes or 255-byte binary data. To accommodate the expanded table limits in Adaptive Server, Open Client/Open Server 12.5 can send and receive wider data and column information for tables with more columns.


Capability

To support wide tables, the client sends a login packet to the server along with a capability packet. Possible ct_capability parameters include:

If the version of the application is set to CS_VERSION_125, Client-Library always sends CS_WIDETABLE capability to the server; the application does not have control of the request capability. However, the application can set CS_NOWIDETABLE response capability before the connection is established to specifically request the server not to enable wide table capabilities.

The syntax of ct_capability is:

CS_RETCODE ct_capability (connection, action, type,
        capability, value)
   CS_CONNECTION              *connection;
   CS_INT                      action;
   CS_INT                      type;
   CS_INT                      capability;
   CS_VOID                     *value;

where the values of type are CS_WIDETABLES or CS_NOWIDETABLES.

If you do not want to enable wide table support, you can send the server a CS_NOWIDETABLE command to disable this feature.

...
CS_BOOL     boolv = CS_TRUE
...
 retcode = ct_capability ( *conn_ptr, CS_SET, CS_CAP_RESPONSE, 
     CS_NOWIDETABLES, &boolv);
...

ct_dynamic() with CS_CURSOR_DECLARE supports the flags CS_PREPARE, CS_EXECUTE, and CS_EXEC_IMMEDIATE to prepare and execute dynamic SQL statements that reference the 1024-column limit of Adaptive Server 12.5.

ct_param() can be used to pass as many as 1024 arguments to a dynamic SQL statement.


Changes in application program

If the column data you are retrieving is in excess of CS_MAX_CHAR (256 characters or 256 binary data), you must edit the CS_DATAFMT structure field datafmt.maxlength definition to the maximum length, in bytes, of the data that you are retrieving. Otherwise you get a truncation error.

If you expect wider columns in the client program, change the column array size in the application program.

For example, if the application expects a column that is 300 bytes wide, then the column should mention CS_CHAR col1[300] at an appropriate place. Assign an appropriate length-of-character datatype, to the maxlength parameter of the CS_DATAFMT structure for RPC applications if the column is more than 255 bytes. The following is recommended for the CS_DATAFMT parameter:

datafmt.datatype = CS_LONGCHAR_TYPE
datafmt.maxlength = sizeof(col1)

The following example is a small ctlib program using the pubs2 database.

  1. Alter the authors table and add a column “comment” declare as a varchar(500):

    1>alter table authors add comment varchar(500) null 
    2>go
    
  2. Update the new column within the table:

    1>update authors set comment = replicate (substring(state,1,1), 500)
    2>go 
    /* This SQL command will update the comment column with a replicate of 500 times the first letter of the state for each row. */
    
  3. Modify the example.h file to set the “new limits” capabilities.

    #define EX_CTLIB_VERSION CS_VERSION_125
    
  4. Update the exutils.h file and reset the MAX_CHAR_BUF to 16384 (16K).

  5. Recompile and link ctlib using 12.5 headers and libraries.

  6. Execute and test on a Adaptive Server version 12.5 Xk page size server.

    If you set CS_VERSION_125, you see the following (only displays the last 2 rows):

    Heather              McBadden                                 95688       CCCCCC
    CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
    CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
    CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
    CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
    CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
    CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
    CCCCCCCCCCCCCC
    Anne                 Ringer                                   84152       UUUUUU
    UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU
    UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU
    UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU
    UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU
    UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU
    UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU
    UUUUUUUUUUUUUU
    
  7. Update the example.h file and reset ctlib to CS_VERSION_120. Recompile and link using OCS-12_5 headers and libraries.

    NoteIf you execute the same program without setting CS_VERSION_125 first, you retrieve only the first 255 bytes of the comment column and cannot retrieve wide columns, even if you are using version 12.5 of Adaptive Server and OCS-12.5 libraries.

    Open Client message:

    Message number: LAYER = (1) ORIGIN = (4) SEVERITY = (1) NUMBER = (132)
    Message String: ct_fetch(): user api layer: internal common library error: The bind of result set item 4 resulted in truncation.
    Error on row 21.
    Heather              McBadden                                 95688       CCCCCC
    CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
    CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
    CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
    

    Open Client message:

    Message number: LAYER = (1) ORIGIN = (4) SEVERITY = (1) NUMBER = (132)
    Message String: ct_fetch(): user api layer: internal common library error: The bind of result set item 4 resulted in truncation.
    Error on row 22.
    Anne                 Ringer                                   84152       UUUUUU
    UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU
    UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU
    UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU
    

Wide-table compatibility

Wide-table support is activated automatically if:

If the Client-Library application’s version string is not set to CS_VERSION_125, and it is linked to an Open Client/Open Server 12.5, the application does not support the extended limits and there is no behavioral change.

If the Open Client/Open Server version 12.5 connects to a pre-12.5 Adaptive Server, the server returns a capability bit of 0, indicating that it does not support wide tables; the connection is still made but there are no behavioral changes.

Likewise, if a pre-12.5 version of Open Client/Open Server connects to an Adaptive Server 12.5, the new limits are not enabled. However, if the Adaptive Server determines that it must send a wide-table format to an older client, the data is truncated and sent.

NoteAdaptive Server 11.0.x and SQL Server return a mask length of 0 for any mask length in excess of 7 bytes. If the connection request receives a capability mask of 0, you see this error message:

ct_connect(): protocol specific layer: external error: “This server does not accept new larger cpability mask, the original cap mask will be used.”

and the extended limits are not enabled.