Difference between revisions of "Remote Data Connectivity"
From Recital Documentation Wiki
Yvonnemilne (Talk | contribs) (→Using Recital with Oracle) |
Yvonnemilne (Talk | contribs) (→Using Recital with Oracle) |
||
Line 481: | Line 481: | ||
===Using Recital with PostgreSQL=== | ===Using Recital with PostgreSQL=== | ||
===Using Recital with Oracle=== | ===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. | * 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. | * 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=== | ===Using Recital with DB2=== |
Revision as of 15:27, 25 August 2010
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
- sqlcancel() - request that an executing SQL statement be cancelled
- sqlcolumns() - store column information to a cursor
- sqlcommit() - commit a transaction
- sqlconnect() - connect to a data source
- sqldisconnect() - disconnect from a data source
- sqlexec() - send an SQL statement to a data source
- sqlgetprop() - query property settings for a connection or the environment
- sqlmoreresult() - check if more results sets are available and if so, copy next results set to a cursor
- sqlprepare() - prepare an SQL statement that will be executed by the SQLEXEC() function
- sqlrollback() - rollback a transaction
- sqlsetprop() - set property settings for a connection
- sqlstringconnect() - connect to a data source using a gateway connection string
- sqltables() - store data source table names to a table
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.