Remote Data Connectivity

From Recital Documentation Wiki
Revision as of 15:09, 27 August 2010 by Yvonnemilne (Talk | contribs)

Jump to: navigation, search

Remote Data Connectivity

Overview of Remote Data Connectivity Functions

Recital supports a collection of Visual FoxPro compatible remote data connectivity functions, which can be used to handle gateway connections to third party data sources.

Connecting to a Data Source

numeric = sqlconnect(connection as numeric | serverdatasource as character)
numeric = sqlconnect(dsn as character [, user as character [, password as character [, shared as logical]]])

numeric = sqlconnect([shared as logical,] connectionstring as character [, shared as logical])

Disconnecting from a Data Source

numeric = sqldisconnect(connection as numeric)

Sending SQL Queries and Statements

  • sqlprepare() - prepare an SQL statement that will be executed by the SQLEXEC() function 
numeric = sqlprepare(connection as numeric, sqlstatement as character [, cursor as character])
  • sqlexec() - send an SQL statement to a data source 
numeric = sqlexec(connection as numeric [, sqlstatement as character [, cursor as character]])
  • sqlcancel() - request that an executing SQL statement be cancelled 
numeric = sqlcancel(connection as numeric)

SQL Transactions

numeric = sqlcommit(connection as numeric)

numeric = sqlrollback(connection as numeric)

Processing Result Sets

  • sqlmoreresult() - check if more results sets are available and if so, copy next results set to a cursor 
numeric = sqlmoreresults(connection as numeric)

Getting and Setting Properties

  • sqlgetprop() - query property settings for a connection or the environment 
expression = sqlgetprop(connection as numeric, setting as character) 

numeric = sqlsetprop(connection as numeric, setting as character [, value as expression])

Getting Table and Field Information

  • sqltables() - store data source table names to a table 
numeric = sqltables(connection as numeric [, tabletypes as character] [, cursorname as character])

logical | numeric = sqlcolumns(connection as numeric, tablename as character [, format as character] [, cursorname as character])

Examples

nStatHand=sqlstringconnect("recital@localhost:user/password-southwind.tcpip",.T.)
if nStatHand < 1
    messagebox("Cannot make connection", 16, "SQL Connect Error")
else
    messagebox("Connection made", 48, "SQL Connect Message")
    store "00010" to myVar
    sqlexec(nStatHand, "select * from example where account_no = ?myVar", "restab")
    display all
 
    nSetEnd = sqlsetprop(nStatHand,"Transactions",2)
    if nSetEnd = 1
        messagebox("Manual Transactions enabled",0,"Transaction")
    else
        if messagebox("Unable to enable Manual Transactions, continue?",36,"Transaction") = 6
            sqldisconnect(nStatHand)
            return
        endif
    endif
    nRet=sqlexec(nStatHand,"insert into example (account_no, title, last_name, first_name, initial, street, city, state, zip, limit, start_date) values ('00200','Mr','Doe','John','L','1 High Street','Beverly','MA','01916', 12000, {05/12/2010})")
    if sqlgetprop(nStatHand, "Transactions") = 2
        if messagebox("Commit Insert?",36,"Transaction") = 6
            messagebox("sqlcommit() returned " + etos(sqlcommit(nStatHand)),0,"Transaction")
        else
            messagebox("sqlrollback() returned " + etos(sqlrollback(nStatHand)),0,"Transaction")
        endif
    endif
    nSetEnd = sqlsetprop(nStatHand, "Transactions",1)
    if nSetEnd = 1
        messagebox("Automatic Transactions enabled",0,"Transaction")
    else
        messagebox("Unable to enable Automatic Transactions.",0,"Transaction")
    endif
endif
sqldisconnect(nStatHand)

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

Requirements:

  • Make sure the Oracle environment (ORACLE_HOME, ORACLE_SID etc.) is set up before starting the Recital Server. If not, you will see the error ORA-01019. A call to the Oracle environment setup script can be added to the /etc/init.d/recital script if your Recital Server is set to run on startup.
  • The Recital Oracle Gateway requires the Oracle libclntsh.so shared library. If this file is unknown to ld.so.conf, add it using the ldconfig command.

Using Recital with DB2

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