SqlGetAltColInfo%

Gets the column ID, datatype, maximum length, type of aggregate function, and aggregate type name of a specific column in the current set of results.

Syntax

SqlGetAltColInfo% ( sqlconn%, altcolumndata, computeid%, altcolumn% )

where

sqlconn%

Is a SQL Server connection. The value of sqlconn% is returned by SqlOpen%.

altcolumndata

Is a user-defined datatype (structure) defined as AltColumnData. It contains the following elements:

colid%

Is an integer value of the column identification number that the aggregate of the compute column applies to.

datatype%

Is the datatype of the compute column. See SqlAltType% for the list of datatype token values. If either the computeid% or the altcolumn% is invalid, this value is - 1.

maxlen&

The maximum length, in bytes, that the data in the column can be.

aggtype%

The type of aggregate operator of the compute column. See SqlAltOpt% for the list of aggregate function types.

aggopname$

A string containing the name of the aggregate operator used by SQL Server ¾ for example, SUM, AVG, and MAX.

computeid%

Is the compute ID. A SELECT statement can have multiple COMPUTE clauses, each of which can have a different number of aggregate operators and aggregate targets. The computeid% is returned by SqlNextRow% or SqlGetRow%.

altcolumn%

Is the number of the column. The first column returned is number 1.

Returns

SUCCEED (1) or FAIL (0). The column ID, datatype, maximum length, type of aggregate function, and name of the aggregate operator type are returned by the altcolumndata parameter.

Remarks

SqlGetAltColInfo% performs the actions of several other Visual Basic functions and puts the results of the functions in the corresponding elements of the altcolumndata parameter. SqlGetAltColInfo% is the equivalent of calling SqlAltColId%, SqlAltLen%, SqlAltOp%, and SqlAltType%. To access an element returned by altcolumndata, you must use the point (.) notation:

altcolumndata.elementname 

For example, the following statement gets the column name returned by altcolumndata :

AggregateOperatorName$ = AltColumnData.AggOpName$

Example

Dim AltColDetails As AltColumnData
'Put commands into the command buffer.
cmd$ = "SELECT title_id, price, advance FROM titles"
cmd$ = cmd$  " COMPUTE SUM(price), MAX(advance)"
Result% = SqlCmd%(SqlConn%, cmd$)
'Send the command to SQL Server and start execution.
Result% = SqlExec%(SqlConn%)
Result% = SqlResults%(SqlConn%)

'Get the column ID, datatype, and name of the aggregate operator from   
 compute columns and print the results.
IF Result% = SUCCEED THEN
   DO UNTIL SqlNextRow%(SqlConn%) = NOMOREROWS
      IF Result% = REGROW THEN
         PRINT "regular row returned."
         PRINT
      ELSE
         GetAltColInfo% = SqlGetAltColInfo%(SqlConn%, AltColDetails, 
                        ComputeId%, AltColumn%)
         IF GetAltColInfo% = SUCCEED THEN
            AltColumnID% = AltColDetails.ColID%
            ColDatatype% = AltColDetails.Datatype%
            AggregateOperator$ = AltColDetails.AggOpName$
            PRINT "Column ID of compute column = "  AltColumnID%
            PRINT "Datatype of column = "  ColData type%
            PRINT "Aggregate Operator of column = "  AggregateOperator$
            PRINT
         END IF
      END IF
   LOOP

See Also

SqlAltColId%, SqlAltLen%, SqlAltOp%, SqlAltType%, SqlGetColumnInfo%