OdxSetColumnType

OdxSetColumnType assigns a data type to select-list items. The actual data type assigned is te data type of the variable passed in the @variable parameter.

Syntax

OdxSetColumnType @cursor, @position, @variable

@cursor integer input
The cursor number assigned when the cursor was opened.

@position integer input
The ordinal position of the column in the select list, starting with 1.

@variable (any type) input
A variable defined in the current stored procedure. The data type of this variable will set be the data type of the return value.

Options

Example

This example

-- Parse the sql statement
exec @status = OdxParseSql @cursor_num, 'select customer_no,company,contact from customers where company='
if @status <> 0 goto exit_label

-- define the column list
exec @status = OdxSetColumnType @cursor_num, 1, @customer_num
if @status <> 0 goto exit_label
exec @status = OdxSetColumnType @cursor_num, 2, @company
if @status <> 0 goto exit_label
exec @status = OdxSetColumnType @cursor_num, 3, @contact
if @status <> 0 goto exit_label

-- execute the select sql
exec @status = OdxExecuteSql @cursor_num
if @status <> 0 goto exit_label

-- open a second cursor for child table retrieval
exec @status = OdxOpenCursor @instance_num, @child_cursor output
if @status <> 0 goto exit_label

-- select rows from the child table
exec @status = OdxParseSql @child_cursor, 'select customer_no,date_tickler,tickler,initials,action_info from activity where customer_no = @cust_no order by date_tickler desc'
if @status <> 0 goto exit_label

-- define the column list for the child table
exec @status = OdxSetColumnType @child_cursor, 1, @customer_num
if @status <> 0 goto exit_label
exec @status = OdxSetColumnType @child_cursor, 2, @date_tickler
if @status <> 0 goto exit_label
exec @status = OdxSetColumnType @child_cursor, 3, @tickler
if @status <> 0 goto exit_label
exec @status = OdxSetColumnType @child_cursor, 4, @initials
if @status <> 0 goto exit_label
exec @status = OdxSetColumnType @child_cursor, 5, @action_info
if @status <> 0 goto exit_label

-- let's fetch the row from the parent table until the end
exec @status = OdxGetRow @cursor_num
while @status = 0
begin

-- get the column values
exec @status = OdxGetColumn @cursor_num, 1, @customer_num output
if @status <> 0 goto exit_label
exec @status = OdxGetColumn @cursor_num, 2, @company output
if @status <> 0 goto exit_label
exec @status = OdxGetColumn @cursor_num, 3, @contact output
if @status <> 0 goto exit_label

-- display it
exec @counter = OdxGetLastRowCount @cursor_num
print convert( varchar(32), @counter) + ') ' + convert( varchar(32), @customer_num) + ' ' + @company + ' ' + @contact

-- let's retrieve rows from the child table for this particular customer number
exec @status = OdxSetVariable @child_cursor, '@cust_no', @customer_num
if @status <> 0 goto exit_label
exec @status = OdxExecuteSql @child_cursor
if @status <> 0 goto exit_label

-- fetch the child rows
exec @status = OdxGetRow @child_cursor
while @status = 0
begin

-- get the child column values
exec @status = OdxGetColumn @child_cursor, 1, @customer_num output
if @status <> 0 goto exit_label
exec @status = OdxGetColumn @child_cursor, 2, @date_tickler output
if @status <> 0 goto exit_label
exec @status = OdxGetColumn @child_cursor, 3, @tickler output
if @status <> 0 goto exit_label
exec @status = OdxGetColumn @child_cursor, 4, @initials output
if @status <> 0 goto exit_label
exec @status = OdxGetColumn @child_cursor, 5, @action_info output
if @status <> 0 goto exit_label
-- display it
print ' - ' + convert( varchar(32), @customer_num) + ' ' + convert( varchar(32), @date_tickler, 101) + ' ' + @tickler + ' ' + @initials + ' ' + @action_info

exec @status = OdxGetRow @child_cursor
end

exec @status = OdxGetRow @cursor_num
end

if @status = 11032 set @status = 0

exit_label:

if @status <> 0
begin
exec OdxGetErrorMessage @status, @message output
print 'Error occured: ' + @message
end

-- clean up
-- check if we need to close the cursors to avoid memory leak
exec @status = OdxIsOpened @child_cursor
if @status <> 0
exec @status = OdxCloseCursor @child_cursor
exec @status = OdxIsOpened @cursor_num
if @status <> 0
exec @status = OdxCloseCursor @cursor_num

-- check if we need to disconnect to avoid memory leak
exec @status = OdxIsConnected @instance_num
if @status <> 0
exec @status = OdxDisconnectEnv @instance_num

end
go

Top