LISTING 1

//command props
CONSTANT Long CS_PARENT_HANDLE = 9130

//boolean constants
CONSTANT Long CS_TRUE = 1
CONSTANT Long CS_FALSE = 0

//connection options
CONSTANT Long CS_OPT_SHOWPLAN = 5013
CONSTANT Long CS_OPT_NOEXEC = 5014

//Result types from ct_results().
CONSTANT Long CS_ROW_RESULT = 4040
CONSTANT Long CS_CURSOR_RESULT = 4041
CONSTANT Long CS_PARAM_RESULT = 4042
CONSTANT Long CS_STATUS_RESULT = 4043
CONSTANT Long CS_MSG_RESULT = 4044
CONSTANT Long CS_COMPUTE_RESULT = 4045
CONSTANT Long CS_CMD_DONE = 4046
CONSTANT Long CS_CMD_SUCCEED = 4047
CONSTANT Long CS_CMD_FAIL = 4048
CONSTANT Long CS_ROWFMT_RESULT = 4049
CONSTANT Long CS_COMPUTEFMT_RESULT = 4050
CONSTANT Long CS_DESCRIBE_RESULT = 4051

//ct_diag() and cs_diag() type flags.
CONSTANT Long CS_SERVERMSG_TYPE = 4701
CONSTANT Long CS_ALLMSG_TYPE = 4702
CONSTANT Long SQLCA_TYPE = 4703

//Flags for getting result info using ct_res_info().
CONSTANT Long CS_ROW_COUNT = 800
CONSTANT Long CS_CMD_NUMBER = 801
CONSTANT Long CS_NUM_COMPUTES = 802
CONSTANT Long CS_NUMDATA = 803
CONSTANT Long CS_ORDERBY_COLS = 804
CONSTANT Long CS_NUMORDERCOLS = 805
CONSTANT Long CS_MSGTYPE = 806
CONSTANT Long CS_BROWSE_INFO = 807
CONSTANT Long CS_TRANS_STATE = 808
CONSTANT Long CS_NO_COUNT = -1

//Define basic default types.
CONSTANT Long CS_NULLTERM = -9
CONSTANT Long CS_UNUSED = -99999

//ct_command() command types.
CONSTANT Long CS_LANG_CMD = 148

//Define the core return codes.
CONSTANT Long CS_SUCCEED = 1
CONSTANT Long CS_FAIL = 0
CONSTANT Long CS_ROW_FAIL = -203

//datatypes we support.
CONSTANT Long CS_ILLEGAL_TYPE = -1
CONSTANT Long CS_CHAR_TYPE = 0
CONSTANT Long CS_BINARY_TYPE = 1
CONSTANT Long CS_LONGCHAR_TYPE = 2
CONSTANT Long CS_LONGBINARY_TYPE = 3
CONSTANT Long CS_TEXT_TYPE = 4
CONSTANT Long CS_IMAGE_TYPE = 5
CONSTANT Long CS_TINYINT_TYPE = 6
CONSTANT Long CS_SMALLINT_TYPE = 7
CONSTANT Long CS_INT_TYPE = 8
CONSTANT Long CS_REAL_TYPE = 9
CONSTANT Long CS_FLOAT_TYPE = 10
CONSTANT Long CS_BIT_TYPE = 11
CONSTANT Long CS_DATETIME_TYPE = 12
CONSTANT Long CS_DATETIME4_TYPE = 13
CONSTANT Long CS_MONEY_TYPE = 14
CONSTANT Long CS_MONEY4_TYPE = 15
CONSTANT Long CS_NUMERIC_TYPE = 16
CONSTANT Long CS_DECIMAL_TYPE = 17
CONSTANT Long CS_VARCHAR_TYPE = 18
CONSTANT Long CS_VARBINARY_TYPE = 19
CONSTANT Long CS_LONG_TYPE = 20
CONSTANT Long CS_SENSITIVITY_TYPE = 21
CONSTANT Long CS_BOUNDARY_TYPE = 22
CONSTANT Long CS_VOID_TYPE = 23
CONSTANT Long CS_USHORT_TYPE = 24
CONSTANT Long CS_UNICHAR_TYPE = 25
CONSTANT Long CS_BLOB_TYPE = 26
CONSTANT Long CS_DATE_TYPE = 27
CONSTANT Long CS_TIME_TYPE = 28

//Cancel types.
CONSTANT Long CS_CANCEL_ALL = 6001

//Action flags used.
CONSTANT Long CS_GET = 33
CONSTANT Long CS_SET = 34
CONSTANT Long CS_CLEAR = 35
CONSTANT Long CS_INIT = 36
CONSTANT Long CS_STATUS = 37

CONSTANT Long CS_FMT_UNUSED = 0
CONSTANT Long CS_FMT_NULLTERM	= 1
CONSTANT Long CS_FMT_PADBLANK = 4

LISTING 2

FUNCTION Long ct_cmd_alloc(Long connection, REF Long cmd) LIBRARY "libct.dll"
FUNCTION Long ct_command(Long cmd, Long ltype, String Buf, Long buflen, Long option)
LIBRARY "libct.dll" alias for "ct_command"
FUNCTION Long ct_send(Long cmd) LIBRARY "libct.dll"
FUNCTION Long ct_results(Long cmd, Ref Long resulttype) LIBRARY "libct.dll"
FUNCTION Long ct_fetch(Long cmd, Long ltype, Long offset, Long option, Ref Long count) LIBRARY "libct.dll"
FUNCTION Long ct_res_info(Long cmd, Long operation, Ref Long buf, Long buflen, Ref Long outlen) LIBRARY "libct.dll"
FUNCTION Long ct_cmd_drop(Long cmd) LIBRARY "libct.dll"
FUNCTION Long ct_describe(Long cmd, Long item, REF CS_DATAFMT datafmt) LIBRARY "libct.dll" alias for "ct_describe"
FUNCTION Long ct_cancel(Long connection, Long cmd, Long ltype) LIBRARY "libct.dll"
FUNCTION Long ct_con_props(Long connection, Long action, Long property, REF Long buffer, LONG buflen, REF Long outlen) LIBRARY "libct.dll"
FUNCTION Long cs_dt_crack(Long context, Long datetype, REF blob buf, REF CS_DATEREC d) LIBRARY "libcs.dll" alias for "cs_dt_crack"
FUNCTION Long ct_bind(Long cmd, Long item, CS_DATAFMT datafmt, Ref integer buf, Ref Long outputlen, Ref Long indicator) LIBRARY "libct.dll" alias for "ct_bind"
FUNCTION Long ct_diag(Long connection, Long operation, Long ltype, Long index, Ref cs_servermsg buffer) LIBRARY "libct.dll" alias for "ct_diag"
FUNCTION Long ct_options(Long connection, Long action, Long option, Ref Long param, Long paramlen, Long outlen) LIBRARY "libct.dll"

LISTING 3

CHOOSE CASE ai_datatype
	CASE CS_MONEY_TYPE, CS_MONEY4_TYPE, CS_DECIMAL_TYPE, CS_FLOAT_TYPE, CS_NUMERIC_TYPE
			RETURN 'decimal'
	CASE CS_TINYINT_TYPE, CS_SMALLINT_TYPE
			RETURN 'integer'
	CASE CS_REAL_TYPE
			RETURN 'real'
	CASE CS_LONG_TYPE, CS_BIT_TYPE, CS_INT_TYPE
			RETURN 'long'
	CASE CS_CHAR_TYPE, CS_LONGCHAR_TYPE, CS_TEXT_TYPE, CS_VARCHAR_TYPE
			RETURN 'string'
	CASE CS_DATE_TYPE
			RETURN 'date'
	CASE CS_DATETIME_TYPE, CS_DATETIME4_TYPE
			RETURN 'datetime'
	CASE CS_TIME_TYPE
			RETURN 'time'
	CASE CS_BINARY_TYPE, CS_LONGBINARY_TYPE, CS_VARBINARY_TYPE
			RETURN 'binary'
	CASE ELSE
			RETURN 'string'
END CHOOSE

LISTING 4

//Allocate command structure
ct_cmd_alloc(SQLCA.DBHandle(), ll_command)

//Tie an SQL statement (ls_sql) to the command structure allocated above.
ct_command(ll_command, CS_LANG_CMD, ls_sql, CS_NULLTERM, CS_UNUSED)

//Send the command to the database
ct_send(ll_command)

//Loop through all the result sets
DO WHILE ct_results(ll_command, ll_resulttype) = CS_SUCCEED
	CHOOSE CASE ll_resulttype
		CASE CS_ROW_RESULT
			//Rows returned from and SQL Select statement
		CASE CS_CURSOR_RESULT
			//Cursor results
		CASE CS_PARAM_RESULT
			//Called when a stored procedure has an output variable
		CASE CS_STATUS_RESULT
			//Return status. E.g. result of compiling a Stored Procedure
		CASE CS_MSG_RESULT
		CASE CS_COMPUTE_RESULT
			//Rows returned from an SQL Compute statement
		CASE CS_CMD_DONE
			//Results are done.
		CASE CS_CMD_SUCCEED
			//No special result returned just a success indicator
		CASE CS_CMD_FAIL
			//Database error - error executing sent SQL.
		CASE CS_ROWFMT_RESULT
		CASE CS_COMPUTEFMT_RESULT
		CASE CS_DESCRIBE_RESULT
	END CHOOSE
LOOP

//Drop/Destroy the command structure
ct_cmd_drop(ll_command)

LISTING 5

type cs_datafmt from structure
	character		name[132]
	long		namelen
	long		datatype
	long		format
	long		maxlength
	long		scale
	long		precision
	long		status
	long		count
	long		usertype
	long		cs_local
end type

type cs_daterec from structure
	long		dateyear
	long		datemonth
	long		datedmonth
	long		datedyear
	long		datedweek
	long		datehour
	long		dateminute
	long		datesecond
	long		datemsecond
	long		datetzone
end type

LISTING 6

//get column count
ct_res_info(ll_command, CS_NUMDATA, ll_colcount, 1000, ll_outlen)

//Loop through each column so we can bind it to a program variable
FOR ll_index = 1 TO ll_colcount
	//get column information (size, type etc...)
	ct_describe(ll_command, ll_index, datafmt[ll_index])

	//of_GetDataType contains code found in listing 3
	ls_datatype[ll_index] = of_GetDataType(datafmt[ll_index].datatype)

	//Clear all binding for column
	ct_bind(ll_command, ll_index, ll_null, ll_null, ll_null, ll_null)

	//Determine how to bind
	CHOOSE CASE ls_datatype[ll_index]
		CASE 'decimal'
			ct_bind(blob variable)
		CASE 'integer'
			ct_bind(integer variable)
		CASE 'real'
			ct_bind(real variable)
		CASE 'long'
			ct_bind(long variable)
		CASE 'string'
			ct_bind(blob variable)
		CASE 'date', 'datetime', 'time'
			ct_bind(blob variable)
	END CHOOSE
NEXT

//Fetch first row
ct_fetch(ll_command, CS_UNUSED, CS_UNUSED, CS_UNUSED, ll_rows)

//loop through all rows
DO WHILE ll_return = CS_SUCCEED
	...
	Process variable from ct_bind
	...
	//fetch next row
	ct_fetch(ll_command, CS_UNUSED, CS_UNUSED, CS_UNUSED, ll_rows)
LOOP

LISTING 7

type sqlerrm from structure
	long		sqlerrml
	character	sqlerrmc[256]
end type

type strsqlca from structure
	character	sqlcaid[8]
	long		sqlcabc
	long		sqlcode
	sqlerrm		strsqlerrm
	character	sqlerrp[8]
	long		sqlerrd
	character	sqlwarn[8]
	character	sqlext[8]
end type

type cs_servermsg from structure
	ULong		msgnumber
	long		state
	long		severity
	character		text[1024]
	long		textlen
	character		svrname[132]
	long		svrnlen
	character		proc[132]
	long		proclen
	long		line
	long		status
	character		sqlstate[8]
	long		sqlstatelen
end type

LISTING 8

//Get message count
ll_return = ct_diag_status(atr.DBHandle(), CS_STATUS, CS_SERVERMSG_TYPE, CS_UNUSED, ll_servercount)

IF ll_servercount > 0 THEN
	FOR ll_index = 1 TO ll_servercount
		ll_return = ct_diag(atr.DBHandle(), CS_GET, CS_SERVERMSG_TYPE, ll_index, server_msg)
	NEXT
END IF

//Clear the structures of existing error messages
ct_diag(atr.DBHandle(), CS_CLEAR, CS_SERVERMSG_TYPE, CS_UNUSED, server_msg)