Remote Data Connectivity
From Recital Documentation Wiki
Revision as of 15:09, 27 August 2010 by Yvonnemilne (Talk | contribs)
Contents
- 1 Remote Data Connectivity
- 1.1 Overview of Remote Data Connectivity Functions
- 1.2 Overview of Recital Data Objects (RDO)
- 1.3 Using Recital Client/Server
- 1.4 Using Recital with MySQL
- 1.5 Using Recital with PostgreSQL
- 1.6 Using Recital with Oracle
- 1.7 Using Recital with DB2
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
- sqlconnect() - connect 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]]])
- sqlstringconnect() - connect to a data source using a gateway connection string
numeric = sqlconnect([shared as logical,] connectionstring as character [, shared as logical])
Disconnecting from a Data Source
- sqldisconnect() - disconnect 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
- sqlcommit() - commit a transaction
numeric = sqlcommit(connection as numeric)
- sqlrollback() - rollback a transaction
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)
- sqlsetprop() - set property settings for a connection
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])
- sqlcolumns() - store column information to a cursor
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
- rdo_connect() - connect to a data source
numeric = rdo_connect(servertype as character, hostname_or_ip as character, account as character, password as character [, database as character])
- rdo_select_db() - set the active database for a data source connection
numeric = rdo_select_db(database as character [, connection as numeric])
Disconnecting from a Data Source
- rdo_close() - close a data source opened with rdo_connect()
numeric = rdo_close(connection as numeric)
Sending SQL Queries and Statements
- rdo_exec() - execute a non-SELECT statement
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])
- rdo_unbuffered_query() - return a result set object for a SELECT query
object = rdo_unbuffered_query(query as string [, connection as numeric])
- rdo_fetch_array() - return the results of a query as an associative array
array = rdo_fetch_array(query as string)
- rdo_fetch_assoc() - return the results of a query as an associative array
array = rdo_fetch_assoc(query as string)
- rdo_real_escape_string() - escape special characters in a string for use in an SQL statement
string = rdo_real_escape_string(query as string [, connection as numeric])
- rdo_affected_rows() - return the number of rows affected by the last operation
numeric = rdo_affected_rows()
- rdo_info() - return information about the last query
string = rdo_info()
Processing Result Sets
- rdo_fetch_row() - return the next row from a result set as an object
object = rdo_fetch_row(resultset as object)
- rdo_fetch_object() - return the next row from a result set as an object
object = rdo_fetch_object(resultset as object)
- rdo_data_seek() - move the internal row pointer
numeric = rdo_data_seek(resultset as object, row as numeric)
- rdo_field_seek() - jump to a specified field in a result set
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)
- rdo_fetch_lengths() - return the length of the contents of each field in a result set
object = rdo_fetch_lengths(resultset as object)
- rdo_num_fields() - return the number of fields in a result set
numeric = rdo_num_fields(resultset as object)
- rdo_num_rows() - return the number of rows in a result set
numeric = rdo_num_rows(resultset as object)
Field Information
- rdo_field_flags() - return the flags of a field in a result set
string = rdo_field_flags(resultset as object, column as numeric)
- rdo_field_len() - return the length of a field in a result set
numeric = rdo_field_len(resultset as object, column as numeric)
- rdo_field_name() - return the name of a field in a result set
string = rdo_field_name(resultset as object, column as numeric)
- rdo_field_type() - return the type of a field in a result set
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
- rdo_free_object() - free memory used by a result set object
numeric = rdo_free_object(resultset as object)
- rdo_free_result() - free memory used by a result set object
numeric = rdo_free_result(resultset as object)
Getting Error Information
- rdo_errno() - return the number of the last error
numeric = rdo_errno()
- rdo_error() - return the error description of the last Recital error
string = rdo_errno()
Getting Environment Information
- rdo_get_client_info() - return information about the client
string = rdo_get_client_info()
- rdo_get_host_info() - return information about the connection
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)
- rdo_thread_id() - return the current thread ID
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.