Difference between revisions of "Remote Data Connectivity"

From Recital Documentation Wiki
Jump to: navigation, search
(→‎Examples)
Line 243: Line 243:
 
====Examples====
 
====Examples====
 
<code lang="recital">
 
<code lang="recital">
 +
// Error trapping routine
 +
function errorfunc
 +
parameter p_line
 +
echo p_line, "\n"
 +
// rdo_error() - return the error description of the last Recital error
 +
echo "Error Message: " + rdo_error(), "\n"
 +
// rdo_errno() - return the number of the last error
 +
echo "Error Number : " + rdo_errno(), "\n"
 +
endfunc
 +
 +
// rdo_connect() - connect to a data source
 +
echo time() + " RDO_CONNECT() with database", "\n"
 +
conn = rdo_connect("recital","?","?","?", "southwind")
 +
if conn > -1
 +
// rdo_close() - close a data source opened with rdo_connect()
 +
echo time() + " RDO_CLOSE()", "\n"
 +
rdo_close(conn)
 +
else
 +
echo "rdo_connect failed", "\n"
 +
errorfunc(message(1))
 +
endif
 +
 +
echo time() + " RDO_CONNECT()", "\n"
 +
conn = rdo_connect("recital","?","?","?")
 +
if conn > -1
 +
// rdo_list_dbs() - list the available databases
 +
echo time() + " RDO_LIST_DBS()", "\n"
 +
dbs = rdo_list_dbs()
 +
// rdo_select_db() - set the active database
 +
echo time() + " RDO_SELECT_DB()", "\n"
 +
foreach dbs as currdb
 +
rdo_select_db(currdb, conn)
 +
// rdo_query() - return a result set object for a SELECT query
 +
echo time() + " RDO_QUERY()", "\n"
 +
res = rdo_query("select database() as db from sysresultset")
 +
// rdo_result() - return the value of a field in a result set
 +
echo time() + " RDO_RESULT()", "\n"
 +
echo "Current database: " + rdo_result(res,0,"db"), "\n"
 +
endfor
 +
rdo_close(conn)
 +
else
 +
echo "rdo_connect failed", "\n"
 +
errorfunc(message(1))
 +
endif
 +
 +
// rdo_connect() - connect to a data source
 +
echo time() + " RDO_CONNECT() with database", "\n"
 +
conn = rdo_connect("recital","?","?","?", "southwind")
 +
if conn > -1
 +
// rdo_exec() - execute a non-SELECT statement
 +
echo time() + " RDO_EXEC()", "\n"
 +
if rdo_exec("copy database southwind to rdoreg if exists",conn) > 0
 +
echo "rdo_exec failed", "\n"
 +
errorfunc(message(1))
 +
rdo_close(conn)
 +
return
 +
else
 +
// rdo_exec() - execute a non-SELECT statement
 +
echo time() + " RDO_EXEC()", "\n"
 +
if rdo_exec("update orders set employeeid = 2 where employeeid = 1") > 0
 +
echo "rdo_exec failed", "\n"
 +
errorfunc(message(1))
 +
endif
 +
 +
// rdo_affected_rows() - return the number of rows affected
 +
echo time() + " RDO_AFFECTED_ROWS()", "\n"
 +
echo "Affected rows: " + tostring(rdo_affected_rows()), "\n"
 +
 +
endif
 +
rdo_close(conn)
 +
else
 +
echo "rdo_connect failed", "\n"
 +
errorfunc(message(1))
 +
return
 +
endif
 +
 +
// rdo_connect() - connect to a data source
 +
echo time() + " RDO_CONNECT() with database", "\n"
 +
conn = rdo_connect("recital","?","?","?", "rdoreg")
 +
if conn = -1
 +
echo "rdo_connect failed", "\n"
 +
errorfunc(message(1))
 +
return
 +
else
 +
 +
// rdo_unbuffered_query() - return a result set for a SELECT query
 +
echo time() + " RDO_UNBUFFERED_QUERY()", "\n"
 +
res1 = rdo_unbuffered_query("select * from shippers")
 +
 +
// rdo_info() - return information about the last query
 +
echo time() + "RDO_INFO()", "\n"
 +
echo "Last Query: " + rdo_info(conn), "\n"
 +
 +
// rdo_fetch_array() - return the results of a query as an array
 +
echo time() + " RDO_FETCH_ARRAY()", "\n"
 +
// rdo_real_escape_string() - escape special characters
 +
echo time() + " RDO_REAL_ESCAPE_STRING()", "\n"
 +
res2 =rdo_fetch_array(rdo_real_escape_string("select * from customers"))
 +
 +
// rdo_num_rows() - return the number of rows in a result set
 +
echo time() + " RDO_NUM_ROWS()", "\n"
 +
echo "Number of rows: " + tostring(rdo_num_rows(res2)), "\n"
 +
 +
// rdo_fetch_row() - return the next row from a result set as an object
 +
echo time() + " RDO_FETCH_ROW()", "\n"
 +
for i = 0 to rdo_num_rows(res2)-1
 +
        arr1 = rdo_fetch_row(res2)
 +
        echo "Customer ID: " + arr1.customerid, "\n"
 +
  next
 +
 +
// rdo_fetch_assoc() - return the results of a query as an array
 +
echo time() + " RDO_ASSOC()", "\n"
 +
res3 = rdo_unbuffered_query("select * from orders")
 +
 +
// rdo_num_rows() - return the number of rows in a result set
 +
echo time() + " RDO_NUM_ROWS()", "\n"
 +
echo "Number of rows: " + tostring(rdo_num_rows(res3)), "\n"
 +
 +
// rdo_fetch_object() - return the next row from a result set as an object
 +
echo time() + " RDO_FETCH_OBJECT()", "\n"
 +
for i = 0 to rdo_num_rows(res3)-1
 +
        arr2 = rdo_fetch_object(res3)
 +
        echo "Order ID: " + arr2.orderid, "\n"
 +
  next
 +
 +
// rdo_data_seek() - move the internal row pointer
 +
echo time() + " RDO_DATA_SEEK()", "\n"
 +
rdo_data_seek(res3,1)
 +
        arr3 = rdo_fetch_object(res3)
 +
        echo "Order ID: " + arr3.orderid, "\n"
 +
 +
// rdo_num_fields() - return the number of fields in a result set
 +
echo time() + " RDO_NUM_FIELDS()", "\n"
 +
echo "Number of fields: " + tostring(rdo_num_fields(res3)), "\n"
 +
 +
// rdo_fetch_field() - return an object with field information
 +
echo time() + " RDO_FETCH_FIELD()", "\n"
 +
for i = 0 to rdo_num_fields(res3)-1
 +
arr4 = rdo_fetch_field(res3, i)
 +
foreach arr4 as key => value
 +
echo "key=" + key + " value=" + value, "\n"
 +
endfor
 +
next
 +
 +
// rdo_fetch_lengths() - return the length of the contents of each field
 +
echo time() + " RDO_FETCH_LENGTHS()", "\n"
 +
arr5 = rdo_fetch_lengths(res3)
 +
foreach arr5 as field => length
 +
echo "field=" + field + " length=" + length, "\n"
 +
endfor
 +
 +
// rdo_field_seek() - jump to a specified field in a result set
 +
echo time() + " RDO_FIELD_SEEK()", "\n"
 +
rdo_field_seek(res3, 2)
 +
 +
// rdo_fetch_field() - return an object with field information
 +
echo time() + " RDO_FETCH_FIELD()", "\n"
 +
arr6 = rdo_fetch_field(res3)
 +
foreach arr6 as key => value
 +
echo "key=" + key + " value=" + value, "\n"
 +
endfor
 +
 +
// rdo_field_name() - return the name of a field in a result set
 +
echo time() + " RDO_FIELD_NAME()", "\n"
 +
echo "Name: " + rdo_field_name(res3,2), "\n"
 +
 +
// rdo_field_type() - return the type of a field in a result set
 +
echo time() + " RDO_FIELD_TYPE()", "\n"
 +
echo "Type: " + rdo_field_type(res3,2), "\n"
 +
 +
// rdo_field_len() - return the length of a field in a result set
 +
echo time() + " RDO_FIELD_LEN()", "\n"
 +
echo "Length: " + tostring(rdo_field_len(res3,2)), "\n"
 +
 +
// rdo_field_flags() - return the flags of a field in a result set
 +
echo time() + " RDO_FIELD_FLAGS()", "\n"
 +
echo "Flags: " + rdo_field_flags(res3,2), "\n"
 +
 +
// rdo_field_table() - return the name of the table where a specified field is located
 +
echo time() + " RDO_FIELD_TABLE()", "\n"
 +
echo "Table: " + rdo_field_table(res3,2), "\n"
 +
 +
// rdo_free_object() - free memory used by a result set object
 +
echo time() + " RDO_FREE_OBJECT()", "\n"
 +
echo "Object "+iif(rdo_free_object(res1)=0,"released","could not be released"), "\n"
 +
 +
// rdo_free_result() - free memory used by a result set object
 +
echo time() + " RDO_FREE_OBJECT()", "\n"
 +
echo "Object "+iif(rdo_free_object(res2)=0,"released","could not be released"), "\n"
 +
 +
// rdo_get_client_info() - return information about the client
 +
echo time() + " RDO_GET_CLIENT_INFO()", "\n"
 +
echo "Client: " + rdo_get_client_info(), "\n"
 +
 +
// rdo_get_host_info() - return information about the connection
 +
echo time() + " RDO_GET_HOST_INFO()", "\n"
 +
echo "Host: " + rdo_get_host_info(conn), "\n"
 +
 +
// rdo_ping() - check whether a server connection is active
 +
echo time() + " RDO_PING()", "\n"
 +
echo "Connection "+iif(rdo_ping(conn),"is active","is not active"), "\n"
 +
 +
// rdo_stat() - return the current system status of Recital
 +
echo time() + " RDO_STAT()", "\n"
 +
echo "Connection "+iif(rdo_stat(conn)>0,"is active","is not active"), "\n"
 +
 +
// rdo_thread_id() - return the current thread ID
 +
echo time() + " RDO_THREAD_ID()", "\n"
 +
echo "Current thread ID: " + tostring(rdo_thread_id()), "\n"
 +
 +
// rdo_close() - close a data source opened with rdo_connect()
 +
echo time() + " RDO_CLOSE()", "\n"
 +
rdo_close(conn)
 +
endif
 +
// rdo_connect() - connect to a data source
 +
echo time() + " RDO_CONNECT()", "\n"
 +
conn = rdo_connect("recital","?","?","?")
 +
if conn > -1
 +
// rdo_exec() - execute a non-SELECT statement
 +
echo time() + " RDO_EXEC()", "\n"
 +
if rdo_exec("drop database rdoreg",conn) > 0
 +
echo "rdo_exec failed", "\n"
 +
errorfunc(message(1))
 +
endif
 +
 +
// rdo_close() - close a data source opened with rdo_connect()
 +
echo time() + " RDO_CLOSE()", "\n"
 +
rdo_close(conn)
 +
else
 +
echo "rdo_connect failed", "\n"
 +
errorfunc(message(1))
 +
endif
 
</code>
 
</code>
  

Revision as of 16:21, 2 August 2010

Remote Data Connectivity

Overview of Remote Data Connectivity Functions

Overview of Recital Data Objects (RDO)

The RDO functions can be used for data access for both remote third-party/Recital and local Recital data sources. The functions allow a connection to be made to the data, SQL queries to be sent and the results to be loaded into a result set object for further processing.

Each MySQL compatible RDO function also has a MySQL-named equivalent for ease of use, e.g. rdo_connect() and mysql_connect() are synonyms.

Connecting to a Data Source

numeric = rdo_connect(servertype as character, hostname_or_ip as character, account as character, 
  password as character [, database as character])

numeric = rdo_select_db(database as character [, connection as numeric])

Disconnecting from a Data Source

numeric = rdo_close(connection as numeric)

Sending SQL Queries and Statements

numeric = rdo_exec(statement as string [, connection as numeric])
  • rdo_query() - return a result set object for a SELECT query 
object = rdo_query(query as string [, connection as numeric])

object = rdo_unbuffered_query(query as string [, connection as numeric])

array = rdo_fetch_array(query as string)

array = rdo_fetch_assoc(query as string)

string = rdo_real_escape_string(query as string [, connection as numeric])

numeric = rdo_affected_rows()
  • rdo_info() - return information about the last query 
string = rdo_info()

Processing Result Sets

Navigating the Result Set
object = rdo_fetch_row(resultset as object)

object = rdo_fetch_object(resultset as object)

numeric = rdo_data_seek(resultset as object, row as numeric)

numeric = rdo_field_seek(resultset as object, column as numeric)
  • rdo_result() - return the value of a field in a result set 
string = rdo_result(resultset as object, row as numeric, column as string)
Result Set Information
  • rdo_fetch_field() - return an object containing information about a field from a result set 
object = rdo_fetch_field(resultset as object, field as numeric)

object = rdo_fetch_lengths(resultset as object)

numeric = rdo_num_fields(resultset as object)

numeric = rdo_num_rows(resultset as object)
Field Information
string = rdo_field_flags(resultset as object, column as numeric)

numeric = rdo_field_len(resultset as object, column as numeric)

string = rdo_field_name(resultset as object, column as numeric)

string = rdo_field_type(resultset as object, column as numeric)
  • rdo_field_table() - return the name of the table where a specified field is located 
string = rdo_field_table(resultset as object, column as numeric)
Freeing Memory
numeric = rdo_free_object(resultset as object)

numeric = rdo_free_result(resultset as object)

Getting Error Information

numeric = rdo_errno()
  • rdo_error() - return the error description of the last Recital error 
string = rdo_errno()

Getting Environment Information

string = rdo_get_client_info()

string = rdo_get_host_info(connection as numeric)
  • rdo_list_dbs() - list the available databases for the current or specified connection 
object = rdo_list_dbs([connection as numeric])
  • rdo_ping() - check whether a server connection is active 
logical = rdo_ping(connection as numeric)
  • rdo_stat() - return the current system status of Recital 
numeric = rdo_stat(connection as numeric)

numeric = rdo_thread_id([connection as numeric])

Examples

// Error trapping routine
function errorfunc
	parameter p_line
	echo p_line, "\n"
	// rdo_error() - return the error description of the last Recital error 
	echo "Error Message: " + rdo_error(), "\n"
	// rdo_errno() - return the number of the last error 
	echo "Error Number : " + rdo_errno(), "\n"
endfunc
 
// rdo_connect() - connect to a data source 
echo time() + " RDO_CONNECT() with database", "\n"
conn = rdo_connect("recital","?","?","?", "southwind")
if conn > -1
	// rdo_close() - close a data source opened with rdo_connect() 
	echo time() + " RDO_CLOSE()", "\n"
	rdo_close(conn)
else
	echo "rdo_connect failed", "\n"
	errorfunc(message(1))
endif
 
echo time() + " RDO_CONNECT()", "\n"
conn = rdo_connect("recital","?","?","?")
if conn > -1
	// rdo_list_dbs() - list the available databases
	echo time() + " RDO_LIST_DBS()", "\n"
	dbs = rdo_list_dbs()
	// rdo_select_db() - set the active database
	echo time() + " RDO_SELECT_DB()", "\n"
	foreach dbs as currdb
		rdo_select_db(currdb, conn)
		// rdo_query() - return a result set object for a SELECT query 
		echo time() + " RDO_QUERY()", "\n"
		res = rdo_query("select database() as db from sysresultset")
		// rdo_result() - return the value of a field in a result set 
		echo time() + " RDO_RESULT()", "\n"
		echo "Current database: " + rdo_result(res,0,"db"), "\n"
	endfor
	rdo_close(conn)
else
	echo "rdo_connect failed", "\n"
	errorfunc(message(1))
endif
 
// rdo_connect() - connect to a data source 
echo time() + " RDO_CONNECT() with database", "\n"
conn = rdo_connect("recital","?","?","?", "southwind")
if conn > -1
	// rdo_exec() - execute a non-SELECT statement 
	echo time() + " RDO_EXEC()", "\n"
	if rdo_exec("copy database southwind to rdoreg if exists",conn) > 0
		echo "rdo_exec failed", "\n"
		errorfunc(message(1))
		rdo_close(conn)
		return
	else
		// rdo_exec() - execute a non-SELECT statement 
		echo time() + " RDO_EXEC()", "\n"
		if rdo_exec("update orders set employeeid = 2 where employeeid = 1") > 0
			echo "rdo_exec failed", "\n"
			errorfunc(message(1))
		endif
 
		// rdo_affected_rows() - return the number of rows affected
		echo time() + " RDO_AFFECTED_ROWS()", "\n"
		echo "Affected rows: " + tostring(rdo_affected_rows()), "\n"
 
	endif
	rdo_close(conn)
else
	echo "rdo_connect failed", "\n"
	errorfunc(message(1))
	return
endif
 
// rdo_connect() - connect to a data source 
echo time() + " RDO_CONNECT() with database", "\n"
conn = rdo_connect("recital","?","?","?", "rdoreg")
if conn = -1
	echo "rdo_connect failed", "\n"
	errorfunc(message(1))
	return
else
 
	// rdo_unbuffered_query() - return a result set for a SELECT query 
	echo time() + " RDO_UNBUFFERED_QUERY()", "\n"
	res1 = rdo_unbuffered_query("select * from shippers")
 
	// rdo_info() - return information about the last query 
	echo time() + "RDO_INFO()", "\n"
	echo "Last Query: " + rdo_info(conn), "\n"
 
	// rdo_fetch_array() - return the results of a query as an array 
	echo time() + " RDO_FETCH_ARRAY()", "\n"
	// rdo_real_escape_string() - escape special characters
	echo time() + " RDO_REAL_ESCAPE_STRING()", "\n"
	res2 =rdo_fetch_array(rdo_real_escape_string("select * from customers"))
 
	// rdo_num_rows() - return the number of rows in a result set 
	echo time() + " RDO_NUM_ROWS()", "\n"
	echo "Number of rows: " + tostring(rdo_num_rows(res2)), "\n"
 
	// rdo_fetch_row() - return the next row from a result set as an object 
	echo time() + " RDO_FETCH_ROW()", "\n"
	for i = 0 to rdo_num_rows(res2)-1
        	arr1 = rdo_fetch_row(res2)
        	echo "Customer ID: " + arr1.customerid, "\n"
   	next
 
	// rdo_fetch_assoc() - return the results of a query as an array 
	echo time() + " RDO_ASSOC()", "\n"
	res3 = rdo_unbuffered_query("select * from orders")
 
	// rdo_num_rows() - return the number of rows in a result set 
	echo time() + " RDO_NUM_ROWS()", "\n"
	echo "Number of rows: " + tostring(rdo_num_rows(res3)), "\n"
 
	// rdo_fetch_object() - return the next row from a result set as an object 
	echo time() + " RDO_FETCH_OBJECT()", "\n"
	for i = 0 to rdo_num_rows(res3)-1
        	arr2 = rdo_fetch_object(res3)
        	echo "Order ID: " + arr2.orderid, "\n"
   	next
 
	// rdo_data_seek() - move the internal row pointer 
	echo time() + " RDO_DATA_SEEK()", "\n"
	rdo_data_seek(res3,1)
        arr3 = rdo_fetch_object(res3)
        echo "Order ID: " + arr3.orderid, "\n"
 
	// rdo_num_fields() - return the number of fields in a result set 
	echo time() + " RDO_NUM_FIELDS()", "\n"
	echo "Number of fields: " + tostring(rdo_num_fields(res3)), "\n"
 
	// rdo_fetch_field() - return an object with field information
	echo time() + " RDO_FETCH_FIELD()", "\n"
	for i = 0 to rdo_num_fields(res3)-1
		arr4 = rdo_fetch_field(res3, i)
		foreach arr4 as key => value
			echo "key=" + key + " value=" + value, "\n"
		endfor
	next
 
	// rdo_fetch_lengths() - return the length of the contents of each field
	echo time() + " RDO_FETCH_LENGTHS()", "\n"
	arr5 = rdo_fetch_lengths(res3)
	foreach arr5 as field => length
		echo "field=" + field + " length=" + length, "\n"
	endfor
 
	// rdo_field_seek() - jump to a specified field in a result set 
	echo time() + " RDO_FIELD_SEEK()", "\n"
	rdo_field_seek(res3, 2)
 
	// rdo_fetch_field() - return an object with field information
	echo time() + " RDO_FETCH_FIELD()", "\n"
	arr6 = rdo_fetch_field(res3)
	foreach arr6 as key => value
		echo "key=" + key + " value=" + value, "\n"
	endfor
 
	// rdo_field_name() - return the name of a field in a result set 
	echo time() + " RDO_FIELD_NAME()", "\n"
	echo "Name: " + rdo_field_name(res3,2), "\n"
 
	// rdo_field_type() - return the type of a field in a result set 
	echo time() + " RDO_FIELD_TYPE()", "\n"
	echo "Type: " + rdo_field_type(res3,2), "\n"
 
	// rdo_field_len() - return the length of a field in a result set 
	echo time() + " RDO_FIELD_LEN()", "\n"
	echo "Length: " + tostring(rdo_field_len(res3,2)), "\n"
 
	// rdo_field_flags() - return the flags of a field in a result set 
	echo time() + " RDO_FIELD_FLAGS()", "\n"
	echo "Flags: " + rdo_field_flags(res3,2), "\n"
 
	// rdo_field_table() - return the name of the table where a specified field is located 
	echo time() + " RDO_FIELD_TABLE()", "\n"
	echo "Table: " + rdo_field_table(res3,2), "\n"
 
	// rdo_free_object() - free memory used by a result set object 
	echo time() + " RDO_FREE_OBJECT()", "\n"
	echo "Object "+iif(rdo_free_object(res1)=0,"released","could not be released"), "\n"
 
	// rdo_free_result() - free memory used by a result set object 
	echo time() + " RDO_FREE_OBJECT()", "\n"
	echo "Object "+iif(rdo_free_object(res2)=0,"released","could not be released"), "\n"
 
	// rdo_get_client_info() - return information about the client 
	echo time() + " RDO_GET_CLIENT_INFO()", "\n"
	echo "Client: " + rdo_get_client_info(), "\n"
 
	// rdo_get_host_info() - return information about the connection 
	echo time() + " RDO_GET_HOST_INFO()", "\n"
	echo "Host: " + rdo_get_host_info(conn), "\n"
 
	// rdo_ping() - check whether a server connection is active 
	echo time() + " RDO_PING()", "\n"
	echo "Connection "+iif(rdo_ping(conn),"is active","is not active"), "\n"
 
	// rdo_stat() - return the current system status of Recital 
	echo time() + " RDO_STAT()", "\n"
	echo "Connection "+iif(rdo_stat(conn)>0,"is active","is not active"), "\n"
 
	// rdo_thread_id() - return the current thread ID 
	echo time() + " RDO_THREAD_ID()", "\n"
	echo "Current thread ID: " + tostring(rdo_thread_id()), "\n"
 
	// rdo_close() - close a data source opened with rdo_connect() 
	echo time() + " RDO_CLOSE()", "\n"
	rdo_close(conn)
endif
// rdo_connect() - connect to a data source 
echo time() + " RDO_CONNECT()", "\n"
conn = rdo_connect("recital","?","?","?")
if conn > -1
	// rdo_exec() - execute a non-SELECT statement 
	echo time() + " RDO_EXEC()", "\n"
	if rdo_exec("drop database rdoreg",conn) > 0
		echo "rdo_exec failed", "\n"
		errorfunc(message(1))
	endif
 
	// rdo_close() - close a data source opened with rdo_connect() 
	echo time() + " RDO_CLOSE()", "\n"
	rdo_close(conn)
else
	echo "rdo_connect failed", "\n"
	errorfunc(message(1))
endif

Using Recital Client/Server

Using Recital with MySQL

Using Recital with PostgreSQL

Using Recital with Oracle

Using Recital with DB2

Retrieved from "https://www.recitalsoftware.com/wiki/index.php?title=Remote_Data_Connectivity&oldid=15303"