note
	description: "Implementation of a SQL Statement"
	legal: "See notice at end of class."
	status: "See notice at end of class."
	names: SQL_statement, query
	author: "$Author$"
	date: "$Date$"
	revision: "$Revision$"
	history: "$History: oci_statement.e $"

class
	OCI_STATEMENT

inherit
	OCI_CHILD_HANDLE
		redefine
			free
		end
	
	OCI_DEFINITIONS
		export {NONE} all
		undefine
			is_equal
		end
		
create
	make, make_by_handle

feature -- Access

	sql_text: STRING
			-- Current SQL text

	statement_type: INTEGER
			-- Type of SQL statement (see OCI_CONST)
		require
			prepared: is_prepared
		do
			Result := stmt_type
		end
		
	function_code: INTEGER
			-- Function code of the SQL command associated with the statement
		require
			prepared: is_prepared
		do
			Result := int16_attr (Oci_attr_sqlfncode, error_handler)
		end
		
	column_count: INTEGER
			-- The number of columns in the select-list for the statement
		require
			described: is_described
		do
			Result := column_list.count
		end
	
	column (index: INTEGER): OCI_COLUMN_PARAM
			-- `index'th column in select-list
		require
			described: is_described
			valid_index: index > 0 and index <= column_count
		do
			Result := column_list @ index
		end
		
	index_of_column (name: STRING): INTEGER
			-- Index of the column with given name; 0 means not found
		require
			described: is_described
			name_not_empty: name /= Void and name.count /= 0
		local
			i: INTEGER
		do
			from
				i := column_list.lower
			until
				i > column_list.upper or Result /= 0
			loop
				if column_list.item (i).name.is_equal (name) then
					Result := i
				end
				i := i + 1
			end
		end
		
	index_of_variable (name: STRING): INTEGER
			-- Index of the bind-variable with given name; 0 means not found
		require
			name_not_empty: name /= Void and name.count /= 0
		local
			i: INTEGER
		do
			from
				i := 1
			until
				i > binds.count or Result /= 0
			loop
				if (binds @ i).name.is_equal (name) then
					Result := i
				end
				i := i + 1
			end
		end
		
	numbers_as_strings: BOOLEAN
		-- Treat NUMBER columns as strings ?

	dates_as_strings: BOOLEAN
		-- Treat DATE columns as strings ?
		
	auto_fetch_first_row: BOOLEAN
		-- Automatically fetch the first row on `execute' ?
	
	column_value (name: STRING): ANY
			-- Value of the column `name' in the current row
		require
			is_query: is_prepared and then is_query
			described: is_described
			executed: is_executed
			not_eof: not eof
			row_fetched: rows_fetched > 0
			column_exists: index_of_column (name) > 0
		local
			index: INTEGER
		do
			index := index_of_column (name)
			Result := defines.item (index).value
		end
		
	is_column_defined (name: STRING): BOOLEAN
			-- Does column `name' exist in select-list and is of a valid type ?
		require
			is_query: is_prepared and then is_query
			described: is_described
			defined: is_defined
		local
			index: INTEGER
			def: OCI_DEFINE
		do
			index := index_of_column (name)
			if index /= 0 then
				def := defines.item (index)
				Result := def /= Void
			else
				Result := False
			end
		end
		
	is_column_null (name: STRING): BOOLEAN
			-- Is value of the column `name' in the current row NULL ?
		require
			is_query: is_prepared and then is_query
			described: is_described
			executed: is_executed
			not_eof: not eof
			row_fetched: rows_fetched > 0
			column_defined: is_column_defined (name)
		local
			index: INTEGER
		do
			index := index_of_column (name)
			Result := defines.item (index).is_null
		end
		
	variable_value (name: STRING): ANY
			-- Value of bind-variable `name'
		require
			is_query: is_prepared and then is_query
			variable_exists: index_of_variable (name) > 0
		local
			index: INTEGER
		do
			index := index_of_variable (name)
			Result := (binds @ index).value
		end
		
	row_count: INTEGER
			-- The number of rows processed so far
		require
			executed: is_executed
		do
			Result := int_attr (Oci_attr_row_count, error_handler)
		end
		
	current_row: ARRAY [ANY]
			-- Entire current row (the last row fetched) as an ARRAY.
			-- Database NULLs represented as Void.
			-- Note: items get overriden every time a row is fetched (by `execute' or `fetch_next').
		require
			is_query: is_prepared and then is_query
			described: is_described
			executed: is_executed
			not_eof: not eof
			row_fetched: rows_fetched > 0
		do
				-- Make sure `row_data' is filled with actual column values
			if not row_data_filled then
				fill_row_data
			end
			Result := row_data
		ensure
			current_row_exists: Result /= Void
			correct_number_of_items: Result.count = column_count
		end
		
feature -- Status report

	failed: BOOLEAN
		-- Has last operation failed ?

	is_prepared: BOOLEAN
		-- Has statement been prepared?

	is_executed: BOOLEAN
		-- Has statement been executed?
	
	is_described: BOOLEAN
		-- Has statement been described ?
	
	is_defined: BOOLEAN
		-- Has statement been defined ?
	
	eof: BOOLEAN
		-- Has end of data been reached ?
	
	rows_fetched: INTEGER
		-- Number of rows already fetched
	
	num_dml_errors: INTEGER
			-- The number of errors in the DML operation
		require
			executed: is_executed
			is_dml: is_prepared and then is_dml
		do
			Result := int_attr (Oci_attr_num_dml_errors, error_handler)
		end
		
	parse_error_offset: INTEGER
			-- The parse error offset for the statement
		require
			prepared: is_prepared
			--? described: is_described
		do
			Result := int16_attr (Oci_attr_parse_error_offset, error_handler)
		end
		
	is_valid: BOOLEAN
			-- Is `Current' statement of a known type ?
		do
			Result := known_statement_type (statement_type)
		end
		
	is_query: BOOLEAN
			-- Is `Current' statement a query ?
		do
			Result := is_query_type (statement_type)
		end

	is_dml: BOOLEAN
			-- Is `Current' statement a DML statement ?
		do
			Result := is_dml_type (statement_type)
		end

	is_ddl: BOOLEAN
			-- Is `Current' statement a DDL statement ?
		do
			Result := is_ddl_type (statement_type)
		end

	is_pl_sql: BOOLEAN
			-- Is `Current' statement a PL/SQL statement ?
		do
			Result := is_pl_sql_type (statement_type)
		end

feature -- Status setting

	free
			-- Free the handle and reset status flags
		do
			Precursor
			column_list := Void
			binds := Void
			defines := Void
			row_data := Void
			rows_fetched := 0
			is_prepared := False
			is_described := False
			is_defined := False
			is_executed := False
			eof := False
			failed := False
		end

feature -- Basic operations

	prepare (text: STRING)
			-- Prepare a SQL statement
		local
			status: INTEGER_16
			area: WEL_STRING
		do
			is_described := False
			is_defined := False
			is_executed := False
			eof := False
			column_list := Void
			binds := Void
			defines := Void
			if text /= Void then				
				sql_text := text.twin
			end
			create area.make (sql_text)
			status := oci_stmt_prepare (handle, error_handler.handle, area.item, text.count,
					Oci_ntv_syntax, Oci_default)
			failed := status = Oci_error
			error_handler.check_error (status)
			is_prepared := not failed
			if is_prepared then
				stmt_type := int16_attr (Oci_attr_stmt_type, error_handler)
				create binds.make (0)
			end
		ensure
			success_unless_failed: (not failed) implies is_prepared
			not_described: not is_described
			not_defined: not is_defined
			not_executed: not is_executed
			not_eof: not eof
		end

	set_prefetch_rows (rows: INTEGER)
			-- Set the number of top level rows to be prefetched
		require
			valid_argument: rows > 0
		do
			set_int_attr (Oci_attr_prefetch_rows, rows, error_handler)
		end
		
	declare_string_variable (name: STRING; size: INTEGER)
			-- Declare a STRING bind-variable
		require
			prepared: is_prepared
			new_variable: index_of_variable (name) = 0
		local
			var: OCI_BIND_STRING
		do
			create var.make (size)
			binds.extend (var)
		ensure
			one_more_variable: binds.count = old binds.count + 1
		end
		
	declare_integer_variable (name: STRING)
			-- Declare an INTEGER bind-variable
		require
			prepared: is_prepared
			new_variable: index_of_variable (name) = 0
		local
			var: OCI_BIND_INTEGER
		do
			create var.make
			binds.extend (var)
		ensure
			one_more_variable: binds.count = old binds.count + 1
		end
		
	declare_double_variable (name: STRING)
			-- Declare a DOUBLE bind-variable
		require
			prepared: is_prepared
			new_variable: index_of_variable (name) = 0
		local
			var: OCI_BIND_DOUBLE
		do
			create var.make
			binds.extend (var)
		ensure
			one_more_variable: binds.count = old binds.count + 1
		end
		
	declare_date_time_variable (name: STRING)
			-- Declare a DATE_TIME bind-variable
		require
			prepared: is_prepared
			new_variable: index_of_variable (name) = 0
		local
			var: OCI_BIND_DATE_TIME
		do
			create var.make
			binds.extend (var)
		ensure
			one_more_variable: binds.count = old binds.count + 1
		end
		
	declare_cursor_variable (name: STRING)
			-- Declare a CURSOR bind-variable
		require
			prepared: is_prepared
			new_variable: index_of_variable (name) = 0
		local
			var: OCI_BIND_CURSOR
		do
			create var.make
			binds.extend (var)
		ensure
			one_more_variable: binds.count = old binds.count + 1
		end
		
	assign_variable (name: STRING; value: ANY)
			-- Assign a bind-variable
		require
			prepared: is_prepared
			variable_exists: index_of_variable (name) > 0
		local
			index: INTEGER
		do
			index := index_of_variable (name)
			(binds @ index).set_value (value)
		ensure
			assigned: (value /= Void and (variable_value (name) /= Void and then
				variable_value (name).is_equal (value))) or
				(value = Void and variable_value (name) = Void)
		end
		
	reset_variables
			-- Reset values of all bind-variables to default values
		require
			prepared: is_prepared
		do
			-- To do
		end

	remove_variables
			-- Remove all bind-variables
		require
			prepared: is_prepared
		do
			binds.wipe_out
		end
		
	set_numbers_as_strings (value: BOOLEAN)
			-- Treat NUMBER columns as strings ?
		do
			numbers_as_strings := value
		ensure
			numbers_as_strings = value
		end

	set_dates_as_strings (value: BOOLEAN)
			-- Treat DATE columns as strings ?
		do
			dates_as_strings := value
		ensure
			dates_as_strings = value
		end

	set_auto_fetch_first_row (value: BOOLEAN)
			-- Automatically fetch the first row on `execute' ?
		do
			auto_fetch_first_row := value
		ensure
			auto_fetch_first_row = value
		end

	execute (context: OCI_SERVICE_CONTEXT)
			-- Execute a prepared SQL statement in given `context'
		require
			valid_statement: is_prepared and then is_valid
		local
			iters: INTEGER
			status: INTEGER_16
		do
			row_data_filled := False
			if is_query and then not (is_defined and auto_fetch_first_row) then
					-- Defines haven't been done yet - can't fetch data
				iters := 0
			else
					-- For a query this will force fetching first row; must be 1 for other SQL
				iters := 1
			end
			rows_fetched := 0
			status := oci_stmt_execute (context.handle, handle, error_handler.handle, iters, 0, 
				default_pointer, default_pointer, Oci_default)
			failed := status = Oci_error
			error_handler.check_error (status)
			if not failed then
				is_described := True
				is_executed := True
				eof := status = Oci_no_data or else not is_query
				if is_query then
					build_column_list
					if not is_defined then
						define
						if auto_fetch_first_row then
								-- Try to fetch the first row now
							fetch_next
						end
					else
						if auto_fetch_first_row then
								-- The first row is already fetched by `oci_stmt_execute' above
							rows_fetched := 1
						end
					end
				end
			end
		ensure
			success_unless_failed: (not failed) implies (is_described and is_executed)
			first_row_fetched: (is_query and not failed and auto_fetch_first_row) implies rows_fetched = 1
		end
		
	describe (context: OCI_SERVICE_CONTEXT)
			-- Describe a prepared SQL statement in given `context' without executing
		require
			valid_statement: is_prepared and then is_valid
		local
			status: INTEGER_16
		do
			row_data_filled := False
			status := oci_stmt_execute (context.handle, handle, error_handler.handle, 1, 0, 
					default_pointer, default_pointer, Oci_describe_only)
			failed := status = Oci_error
			error_handler.check_error (status)
			if not failed then
				is_described := status = Oci_success
				if is_query and is_described then
					build_column_list
				end
			end
		ensure
			success_unless_failed: (not failed) implies is_described
		end
		
	define
			-- Define output variables based on the column list
		require
			described: is_described
			is_query: is_query
		local
			i: INTEGER
			param: OCI_COLUMN_PARAM
			def: OCI_DEFINE
		do
			create defines.make (1, column_list.upper)
			from
				i := 1
			until
				i > column_list.upper
			loop
				param := column_list @ i
				def := define_column (param)
				if def /= Void then
					def.define_by_pos (Current, error_handler, i)
				end
				defines.put (def, i)
				i := i + 1
			end
			is_defined := True
			create row_data.make (1, column_count)
		ensure
			defined: is_defined
			synchronized_with_column_list: defines /= Void and then 
				(defines.lower = column_list.lower and defines.upper = column_list.upper)
		end
		
	fetch_next
			-- Fetch next row from a query
		require
			is_query: is_prepared and then is_query
			executed: is_executed
			defined: is_defined
			has_data: not eof
		local
			status: INTEGER_16
		do
			row_data_filled := False
			status := oci_stmt_fetch (handle, error_handler.handle, 1, Oci_fetch_next, Oci_default)
			failed := status = Oci_error
			error_handler.check_error (status)
			eof := status = Oci_no_data
			rows_fetched := rows_fetched + 1
		end
		
	cancel_fetch
			-- Cancel the cursor
		require
			is_query: is_prepared and then is_query
			executed: is_executed
		local
			status: INTEGER_16
		do
			row_data_filled := False
			status := oci_stmt_fetch (handle, error_handler.handle, 0, Oci_fetch_next, Oci_default)
			failed := status = Oci_error
			error_handler.check_error (status)
		end

feature {OCI_HANDLE} -- Implementation

	handle_type: INTEGER
			-- Handle type
		do
			Result := Oci_htype_stmt
		end

feature {NONE} -- Implementation

	stmt_type: INTEGER_16
		-- Statement type

	column_list: ARRAY [OCI_COLUMN_PARAM]
		-- the list of column descriptors for a query
		
	binds: ARRAYED_LIST [OCI_BIND]
		-- bind-variables

	defines: ARRAY [OCI_DEFINE]
		-- define-variables, one per column

	row_data: ARRAY [ANY]
		-- Content of the last row fetched; valid only if `row_data_filled'

	row_data_filled: BOOLEAN
		-- Does `row_data' contain actual field values ?

	build_column_list
			-- Build the column list (when query is being described)
		require
			is_query: is_prepared and then statement_type = Oci_stmt_select
		local
			param: OCI_COLUMN_PARAM
			i: INTEGER
		do
			create column_list.make (1, int_attr (Oci_attr_param_count, error_handler))
			from
				i := 1
			until
				i > column_list.upper
			loop
				create param.make (Current, i, error_handler)
				column_list.put (param, i)
				i := i + 1
			end
		ensure
			column_list_exists: column_list /= Void
		end
		
	define_column (col: OCI_COLUMN_PARAM): OCI_DEFINE
			-- Create a define-variable associated with a column `col'; Void if unsupported datatype
		do
			inspect
				col.data_type
			when Sqlt_chr, Sqlt_str, Sqlt_vcs, Sqlt_afc, Sqlt_avc then
				create {OCI_DEFINE_STRING} Result.make (col.data_size)
			when Sqlt_bin then
					-- RAWs are converted to hex strings (2 characters per byte)
				create {OCI_DEFINE_STRING} Result.make (col.data_size * 2)
			when Sqlt_int, Sqlt_uin then
				if numbers_as_strings then
					create {OCI_DEFINE_STRING} Result.make (Max_number_string_length)
				else
					create {OCI_DEFINE_INTEGER} Result.make
				end
			when Sqlt_flt, Sqlt_num, Sqlt_pdn, Sqlt_vnu then
				if numbers_as_strings then
					create {OCI_DEFINE_STRING} Result.make (Max_number_string_length)
				else
					create {OCI_DEFINE_DOUBLE} Result.make
				end
			when Sqlt_dat, Sqlt_date, Sqlt_time, Sqlt_time_tz, Sqlt_timestamp, Sqlt_timestamp_tz,
					Sqlt_timestamp_ltz then
				if dates_as_strings then
					if col.data_type = Sqlt_timestamp_tz then
							-- Allow for additional zone info (e.g. " +10:00")
						create {OCI_DEFINE_STRING} Result.make (Max_date_string_length + 7)
					else
						create {OCI_DEFINE_STRING} Result.make (Max_date_string_length)
					end
				else
					create {OCI_DEFINE_DATE_TIME} Result.make
				end
			when Sqlt_interval_ym then
				create {OCI_DEFINE_STRING} Result.make (Year_to_month_length)
			when Sqlt_interval_ds then
				create {OCI_DEFINE_STRING} Result.make (Day_to_second_length)
			when Sqlt_lng then
				create {OCI_DEFINE_STRING} Result.make (Long_initial_size)
			when Sqlt_lab, Sqlt_osl then
				create {OCI_DEFINE_STRING} Result.make (255)
			when Sqlt_rid, Sqlt_rdd then
				create {OCI_DEFINE_STRING} Result.make (Rowid_length)
			when Sqlt_cur then
					-- Nested query
				create {OCI_DEFINE_CURSOR} Result.make
			else
				Result := Void
			end
		end
		
	fill_row_data
			-- Fill `current_row' with actual data from the query
		require
			is_query: is_prepared and then is_query
			described: is_described
			executed: is_executed
			not_eof: not eof
			current_row_exists: row_data /= Void
			correct_number_of_items: row_data.count = column_count
		local
			value: ANY
			i: INTEGER
			def: OCI_DEFINE
		do
			from
				i := 1
			until
				i > row_data.upper
			loop
				def := defines.item (i)
				if def /= Void and then not def.is_null then
					value := def.value
				else
					value := Void
				end
				row_data.put (value, i)
				i := i + 1
			end
			row_data_filled := True
		ensure
			filled: row_data_filled
		end
		
	Unknown_statement_type_error: STRING = "Error: Unknown statement type"
	
	Max_number_string_length: INTEGER = 40
		
	Max_date_string_length: INTEGER = 40
	
	Long_initial_size: INTEGER = 32000
	
	Year_to_month_length: INTEGER = 8
	
	Day_to_second_length: INTEGER = 30
		
	Rowid_length: INTEGER = 18
		
	known_statement_type (type: INTEGER): BOOLEAN
			-- Is `type' a known statement type ?
		do
			Result :=
				type = Oci_stmt_select or 
				type = Oci_stmt_update or
				type = Oci_stmt_delete or
				type = Oci_stmt_insert or
				type = Oci_stmt_create or
				type = Oci_stmt_drop or
				type = Oci_stmt_alter or
				type = Oci_stmt_begin or
				type = Oci_stmt_declare or
					-- Sometimes the statement type is 0.
				type = 0
		end
		
	is_query_type (type: INTEGER): BOOLEAN
			-- Is `type' a query statement type ?
		do
			Result := type = Oci_stmt_select
		end

	is_dml_type (type: INTEGER): BOOLEAN
			-- Is `type' a DML statement type ?
		do
			Result :=
				type = Oci_stmt_update or
				type = Oci_stmt_delete or
				type = Oci_stmt_insert
		end

	is_ddl_type (type: INTEGER): BOOLEAN
			-- Is `type' a DDL statement type ?
		do
			Result :=
				type = Oci_stmt_create or
				type = Oci_stmt_drop or
				type = Oci_stmt_alter
		end

	is_pl_sql_type (type: INTEGER): BOOLEAN
			-- Is `type' a PL/SQL statement type ?
		do
			Result :=
				type = Oci_stmt_begin or
				type = Oci_stmt_declare
		end

feature {NONE} -- Externals

	oci_stmt_prepare (stmtp: POINTER; errhp: POINTER; stmt: POINTER; stmt_len: INTEGER;
			language: INTEGER; mode: INTEGER): INTEGER_16
		external
			"C (void *, void *, char *, int, int, int): short | %"oci.h%""
		alias
			"OCIStmtPrepare"
		end
		
	oci_stmt_execute (svchp: POINTER; stmtp: POINTER; errhp: POINTER; iters: INTEGER;
			rowoff: INTEGER; snap_in: POINTER; snap_out: POINTER; mode: INTEGER): INTEGER_16
		external
			"C (void *, void *, void *, int, int, void *, void *, int): short | %"oci.h%""
		alias
			"OCIStmtExecute"
		end
		
	oci_stmt_fetch (stmtp: POINTER; errhp: POINTER; nrows: INTEGER; orientation: INTEGER_16; 
			mode: INTEGER): INTEGER_16
		external
			"C (void *, void *, int, short, int): short | %"oci.h%""
		alias
			"OCIStmtFetch"
		end
		
invariant
	one_based_column_list: column_list /= Void implies column_list.lower = 1
	column_list_exists_if_described: (is_described and then is_query) implies (column_list /= Void
			and then column_list.count = column_count)
	binds_exist_if_prepared: is_prepared implies binds /= Void
	defines_exist_if_defined: is_defined implies (defines /= Void and then defines.count =
			column_count)
	described_when_executed: is_executed implies is_described
	always_eof_unless_is_query: (is_executed and then not is_query) implies eof
	row_data_filled: row_data_filled implies (row_data /= Void and row_data.count = column_count)

note
	copyright:	"Copyright (c) 1984-2006, Eiffel Software and others"
	license:	"Eiffel Forum License v2 (see http://www.eiffel.com/licensing/forum.txt)"
	source: "[
			 Eiffel Software
			 356 Storke Road, Goleta, CA 93117 USA
			 Telephone 805-685-1006, Fax 805-685-6869
			 Website http://www.eiffel.com
			 Customer support http://support.eiffel.com
		]"




end -- class OCI_STATEMENT