In this article Barry Mavin, CEO and Chief Software Architect for Recital provides details on how to use the Recital Universal .NET Data Provider with the Recital Database Server.
Overview
A data provider in the .NET Framework serves as a bridge between an application and a data source. A data provider is used to retrieve data from a data source and to reconcile changes to that data back to the data source.
Each .NET Framework data provider has a DataAdapter object: the .NET Framework Data Provider for OLE DB is the OleDbDataAdapter object, the .NET Framework Data Provider for SQL Server is the SqlDataAdapter object, the .NET Framework Data Provider for ODBC is the OdbcDataAdapter object, and the .NET Framework Data Provider for the Recital Database Server is the RecitalDataAdapter object.
The Recital Universal .NET Data Provider can access any data sources supported by the Recital Database Server. It is not restricted to only access Recital data. It can be used to access server-side ODBC, JDBC and OLE DB data sources also.
Core classes of the Data Provider
The Connection, Command, DataReader, and DataAdapter objects represent the core elements of the .NET Framework data provider model. The Recital Universal .NET Data Provider is plug compatible with the .NET Framework Data Provider for SQL Server. All SQL Server classes are prefixed with "Sql" e.g. SqlDataAdaptor. To use the Recital Universal Data Adaptor, simply change the "Sql" prefix to "Recital" e.g. RecitalDataAdaptor.
The following table describes these objects.
Object | Description |
---|---|
RecitalConnection | Establishes a connection to a specific data source. |
RecitalCommand | Executes a command against a data source. |
RecitalDataReader | Reads a forward-only, read-only stream of data from a data source. |
RecitalDataAdapter | Populates a DataSet and resolves updates with the data source. |
Along with the core classes listed in the preceding table, a .NET Framework data provider also contains the classes listed in the following table.
Object | Description |
---|---|
RecitalTransaction | Enables you to enlist commands in transactions at the data source. |
RecitalCommandBuilder | A helper object that will automatically generate command properties of a DataAdapter or will derive parameter information from a stored procedure and populate the Parameters collection of a Command object. |
RecitalParameter | Defines input, output, and return value parameters for commands and stored procedures. |
The Recital Universal .NET Data Provider provides connectivity to the Recital Database Server running on any supported platform (Windows, Linux, Unix, OpenVMS) using the RecitalConnection object. The Recital Universal .NET Data Provider supports a connection string format that is similar to the SQL Server connection string format.
The basic format of a connection string consists of a series of keyword/value pairs separated by semicolons. The equal sign (=) connects each keyword and its value.
The following table lists the valid names for keyword values within the ConnectionString property of the RecitalConnection class.
Name | Default | Description |
---|---|---|
Data Source -or- Server -or- Servername -or- Nodename |
The name or network address of the instance of the Recital Database Server which to connect to. | |
Directory | The target directory on the remote server where data to be accessed resides. This is ignored when a Database is specified. | |
Encrypt -or- Encryption |
false | When true, DES3 encryption is used for all data sent between the client and server. |
Initial Catalog -or- Database |
The name of the database on the remote server. | |
Password -or- Pwd |
The password used to authenticate access to the remote server. | |
User ID -or- uid -or- User -or- Username |
The user name used to authenticate access to the remote server. | |
Connection Pooling -or- Pool |
false | Enable connection pooling to the server. This provides for one connection to be shared. |
Logging | false | Provides for the ability to log all server requests for debugging purposes |
Rowid | true | When Rowid is true (the default) a column will be post-fixed to each SELECT query that is a unique row identifier. This is used to provide optimised UPDATE and DELETE operations. If you use the RecitalSqlGrid, RecitalSqlForm, or RecitalSqlGridForm components then this column is not visible but is used to handle updates to the underlying data source. |
Logfile | The name of the logfile for logging | |
Gateway |
Opens an SQL gateway(Connection) to a foreign SQL data source on the remote server.
The gateway can be specified in several formats: |
Populating a DataSet from a DataAdaptor
The ADO.NET DataSet is a memory-resident representation of data that provides a consistent relational programming model independent of the data source. The DataSet represents a complete set of data including tables, constraints, and relationships among the tables. Because the DataSet is independent of the data source, a DataSet can include data local to the application, as well as data from multiple data sources. Interaction with existing data sources is controlled through the DataAdapter.
A DataAdapter is used to retrieve data from a data source and populate tables within a DataSet. The DataAdapter also resolves changes made to the DataSet back to the data source. The DataAdapter uses the Connection object of the .NET Framework data provider to connect to a data source and Command objects to retrieve data from and resolve changes to the data source.
The SelectCommand property of the DataAdapter is a Command object that retrieves data from the data source. The InsertCommand, UpdateCommand, and DeleteCommand properties of the DataAdapter are Command objects that manage updates to the data in the data source according to modifications made to the data in the DataSet.
The Fill method of the DataAdapter is used to populate a DataSet with the results of the SelectCommand of the DataAdapter. Fill takes as its arguments a DataSet to be populated, and a DataTable object, or the name of the DataTable to be filled with the rows returned from the SelectCommand.
The Fill method uses the DataReader object implicitly to return the column names and types used to create the tables in the DataSet, as well as the data to populate the rows of the tables in the DataSet. Tables and columns are only created if they do not already exist; otherwise Fill uses the existing DataSet schema.
Examples in C#:
//////////////////////////////////////////////////////////////////////// // include the references below using System.Data; using Recital.Data; //////////////////////////////////////////////////////////////////////// // The following code example creates an instance of a DataAdapter that // uses a Connection to the Recital Database Server Southwind database // and populates a DataTable in a DataSet with the list of customers. // The SQL statement and Connection arguments passed to the DataAdapter // constructor are used to create the SelectCommand property of the DataAdapter. public DataSet SelectCustomers() { RecitalConnection swindConn = new RecitalConnection("Data Source=localhost;Initial Catalog=southwind"); RecitalCommand selectCMD = new RecitalCommand("SELECT CustomerID, CompanyName FROM Customers", swindConn); selectCMD.CommandTimeout = 30; RecitalDataAdapter custDA = new RecitalDataAdapter(); custDA.SelectCommand = selectCMD; swindConn.Open(); DataSet custDS = new DataSet(); custDA.Fill(custDS, "Customers"); swindConn.Close(); return custDS; } //////////////////////////////////////////////////////////////////////// // The following example uses the RecitalCommand, RecitalDataAdapter and // RecitalConnection, to select records from a database, and populate a // DataSet with the selected rows. The filled DataSet is then returned. // To accomplish this, the method is passed an initialized DataSet, a // connection string, and a query string that is a SQL SELECT statement public DataSet SelectRecitalRows(DataSet dataset, string connection, string query) { RecitalConnection conn = new RecitalConnection(connection); SqlDataAdapter adapter = new RecitalDataAdapter(); adapter.SelectCommand = new RecitalCommand(query, conn); adapter.Fill(dataset); return dataset; }
FOREACH array_expression AS value statements... ENDFORThe first form loops over the array given by array_expression. On each loop, the value of the current element is assigned to value and the internal array pointer is advanced by one (so on the next loop, you'll be looking at the next element).
FOREACH array_expression AS key => value statements... ENDFOR
The second form does the same thing, except that the current element's key will be assigned to the variable key on each loop. This form works only on associative arrays and objects.
Privacy Policy
We respect and are committed to protecting your privacy.
We require that you provide some personal information, to allow us to provide the services we do to the users of recitalsoftware.com, and to improve recitalsoftware.com. The type of information we collect, how we use it, and what choices you have, are detailed in this policy.
Information Collection and Use
We collect the information you provide when you register for an account or complete an information request form.
We use this information to satisfy your requests for further information, to customize our responses and our future communication with you, and to contact you, regarding development and events in the projects or areas of recitalsofware.com that you have expressed interest in, or in recitalsoftware.com, in general.
We make every effort to allow you to opt-in and opt-out of receiving messages from recitalsoftware.com. However, if you are receiving messages from us and cannot find a way to unsubscribe, please contact us at This email address is being protected from spambots. You need JavaScript enabled to view it..
Information Sharing
We will not release your personal information to anyone by any method, including selling, renting, or sharing, unless:
- you grant us permission
- we are required to do so by law
We will not share personal identification information data, either for single individuals or groups, with any parties, including those affiliated with recitalsoftware.com, such as members or sponsors.
How We Use Cookies
This website uses cookies. A cookie is a small amount of text data, sent from our webserver to your browser, and stored on your device. The cookie is sent back to the webserver each time the browser connects to this site. We use cookies to personalize the site and to streamline your interaction with the site.
It may be possible to configure your browser to refuse cookies, or to ask you to accept each time a cookie is offered. If you choose not to accept cookies, areas of this site may have reduced functionality or performance.
Software on our servers, or third party web statics services, may store your IP address, and other information passed on by your browser (such as browser version, operating system, screen size, language, etc). recitalsoftware.com and/or third party services will aggregate this information to provide usages statistics for this website. We use this information to optimize the design, structure, and performance of this site. In particular, we use Google Analytics to provide usage statistics. For more information, read the Google Analytics Privacy Policy.
Data Security
recitalsoftware.com is also committed to the security of your personal information. We train those who work on recitalsoftware.com on this privacy policy. On our site, we use SSL (Secure Sockets Layer) to protect your personal information, by encrypting your information when you send it to recitalsoftware.com.
Public Forum Content
recitalsoftware.com makes available to its users communication forums, such as mail lists, blogs, and others. Be aware that any information or messages you share in these forums becomes public information immediately. Exercise caution in determining whether to disclose any of your personal information. recitalsoftware.com reserves the right to act as necessary to preserve the integrity of the site and its forums, including removing any and all posts deemed vulgar or inappropriate.
Children's Online Privacy
Regarding children under the age of 13, recitalsoftware.com does not knowingly:
- accept personal information from them
- allow them to become registered members of our web site
Updates to this Privacy Policy
We may update this policy. We will contact you if we make any substantial changes in how we use your personal information.
This privacy policy was last updated on July 1, 2010.
Contact Information
If you have any questions about this privacy policy itself, or on how we use personal information on recitalsoftware.com, please contact us at This email address is being protected from spambots. You need JavaScript enabled to view it..
echo "Hello world\n"
After split brain has been detected, one node will always have the resource in a StandAlone connection state. The other might either also be in the StandAlone state (if both nodes detected the split brain simultaneously), or in WFConnection (if the peer tore down the connection before the other node had a chance to detect split brain).
At this point, unless you configured DRBD to automatically recover from split brain, you must manually intervene by selecting one node whose modifications will be discarded (this node is referred to as the split brain victim). This intervention is made with the following commands:
# drbdadm secondary resource
# drbdadm disconnect resource
# drbdadm -- --discard-my-data connect resource
On the other node (the split brain survivor), if its connection state is also StandAlone, you would enter:
# drbdadm connect resource
You may omit this step if the node is already in the WFConnection state; it will then reconnect automatically.
If all else fails and the machines are still in a split-brain condition then on the secondary (backup) machine issue:
drbdadm invalidate resource
http://msdn.microsoft.com/en-us/library/cc351024(VS.85).aspx
Binary distributions for Unison can be found here.
The user manual can be found here.
This article discusses the features in Recital that allow data to be imported and exported between platforms in Microsoft® ADO XML Format.
Overview
Extensible Markup Language, XML, is widely regarded as a lingua franca for the interchange of data. XML's text-based, platform-independent format and its integration of data and the schema to define and describe that data, make it the ideal import/export medium. Recital software provides the functionality to output the data from Recital - and other supported table formats such as FoxPro and FoxBASE - into XML file format and to import XML data into those tables' formats. Such import/export operations provide the means to exchange data with third-party applications and can also facilitate the transfer of data between Recital installations on binary-incompatible platforms.
The features examined in this article are available in Recital Terminal Developer and in the Recital Mirage and Recital Database Servers on all Recital supported platforms. Both the Recital/4GL and Recital/SQL provide XML import and export capabilities. The XML files discussed are in Microsoft® ADO XML format.
Microsoft® ActiveX® Data Objects XML Format
The ADO XML format is primarily designed for ADO Recordset persistence and ADO XML files created by Recital can be used in this way and loaded directly into ADO Recordsets. The format can, though also be used for more generic data transfer. An ADO XML file is self-contained, consisting of two sections: a schema section followed by a data section. The schema conforms to the W3C XML-Data specification and defines the data structure.
For additional information on the Microsoft® ActiveX® Data Objects XML Format, please see Appendix 1.
NOTE: The Recital XMLFORMAT setting should always be in its default setting of ADO for ADO XML Format operations.
set xmlformat to ADO
SQL
Recital/SQL offers the ability to export data into XML files using the SELECT and FETCH statements and import from XML using the CREATE TABLE and INSERT statements.
SQL: Exporting
The SELECT...SAVE AS XML statement allows the complete result set from a SELECT statement to be saved as an XML file. This could be a complete table:
open database southwind SELECT * from orders SAVE AS XML orders.xml
or a more complex multi-table query:
open database southwind SELECT orders.orderid, orders.customerid,; employees.employeeid, employees.lastname, employees.firstname,; orders.orderdate, orders.freight, orders.requireddate,; orders.shippeddate, orders.shipvia, orders.shipname,; orders.shipaddress, orders.shipcity,; orders.shipregion, orders.shippostalcode, orders.shipcountry,; customers.companyname, customers.address, customers.city,; customers.region, customers.postalcode, customers.country; FROM orders INNER JOIN customers; ON customers.customerid = orders.customerid,; orders INNER JOIN employees; ON orders.employeeid = employees.employeeid; SAVE AS XML orderinfo
The resulting XML file can then be further processed within the same or a different Recital environment or transferred to a third party product.
<x-ml xmlns:z="#RowsetSchema" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3- 00AA00C14882"> <s:schema id="RowsetSchema"> <s:elementtype rs:updatable="true" content="eltOnly" name="row"> <s:attributetype rs:basecolumn="orderid" rs:basetable="orders.dbf" rs:write="true" rs:nullable="true" rs:number="1" name="orderid"> <s:datatype rs:fixedlength="true" rs:precision="14" rs:scale="0" dt:maxlength="10" rs:dbtype="numeric" dt:type="number"> </s:datatype></s:attributetype> <s:attributetype rs:basecolumn="customerid" rs:basetable="orders.dbf" rs:write="true" rs:nullable="true" rs:number="2" name="customerid"> <s:datatype rs:fixedlength="true" dt:maxlength="5" rs:dbtype="str" dt:type="string"> </s:datatype></s:attributetype> <s:attributetype rs:basecolumn="employeeid" rs:basetable="orders.dbf" rs:write="true" rs:nullable="false" rs:number="3" name="employeeid"> <s:datatype rs:fixedlength="true" rs:precision="20" rs:scale="0" dt:maxlength="10" rs:dbtype="numeric" dt:type="number"> </s:datatype></s:attributetype> <s:attributetype rs:basecolumn="lastname" rs:basetable="orders.dbf" rs:write="true" rs:nullable="false" rs:number="4" name="lastname"> <s:datatype rs:fixedlength="true" dt:maxlength="20" rs:dbtype="str" dt:type="string"> </s:datatype></s:attributetype> <s:attributetype rs:basecolumn="firstname" rs:basetable="orders.dbf" rs:write="true" rs:nullable="false" rs:number="5" name="firstname"> <s:datatype rs:fixedlength="true" dt:maxlength="10" rs:dbtype="str" dt:type="string"> </s:datatype></s:attributetype> <s:attributetype rs:basecolumn="orderdate" rs:basetable="orders.dbf" rs:write="true" rs:nullable="true" rs:number="6" name="orderdate"> <s:datatype rs:fixedlength="true" dt:maxlength="10" rs:dbtype="Date" dt:type="Date"> </s:datatype></s:attributetype> <s:attributetype name="freight" ...
Click image to display full size
Fig 1: Microsoft® Office Excel 2003: orderinfo.xml.
For data accessed through a Recital Database Gateway, such as Oracle, MySQL or PostgreSQL, the FETCH command can be used to save a cursor results set into an XML file:
// Connect to MySQL Database 'mydata' via Recital Database Gateway nStatHand=SQLSTRINGCONNECT("mys@mysql1:user1/pass1-mydata",.T.) if nStatHand < 1 dialog box [Could not connect] else DECLARE cursor1 CURSOR FOR; SELECT account_no, last_name, first_name FROM example OPEN cursor1 FETCH cursor1 INTO XML exa1.xml SQLDISCONNECT(nStatHand) endif
SQL: Importing
The CREATE TABLE statement allows a new table to be created based on the structure defined in an XML file. The data from the XML file can optionally be loaded into this new table if the LOAD keyword is included. For example, a new 'orderinfo' table can be created and populated with data from the orderinfo.xml file created by the SELECT...SAVE AS XML statement shown earlier:
open database southwind SELECT orders.orderid, orders.customerid,; employees.employeeid, employees.lastname, employees.firstname,; orders.orderdate, orders.freight, orders.requireddate,; orders.shippeddate, orders.shipvia, orders.shipname,; orders.shipaddress, orders.shipcity,; orders.shipregion, orders.shippostalcode, orders.shipcountry,; customers.companyname, customers.address, customers.city,; customers.region, customers.postalcode, customers.country; FROM orders INNER JOIN customers; ON customers.customerid = orders.customerid,; orders INNER JOIN employees; ON orders.employeeid = employees.employeeid; SAVE AS XML orderinfo CREATE TABLE orderinfo FROM XML orderinfo LOAD
The INSERT statement can be used to load data when the table structure already exists. Taking our earlier orderinfo.xml file again, the data can be loaded using INSERT:
open database southwind; SELECT orders.orderid, orders.customerid,; employees.employeeid, employees.lastname, employees.firstname,; orders.orderdate, orders.freight, orders.requireddate,; orders.shippeddate, orders.shipvia, orders.shipname,; orders.shipaddress, orders.shipcity,;; orders.shipregion, orders.shippostalcode, orders.shipcountry,; customers.companyname, customers.address, customers.city,; customers.region, customers.postalcode, customers.country; FROM orders INNER JOIN customers; ON customers.customerid = orders.customerid,; orders INNER JOIN employees; ON orders.employeeid = employees.employeeid; SAVE AS XML orderinfo CREATE TABLE orderinfo FROM XML orderinfo INSERT INTO orderinfo FROM XML orderinfo
The examples above show the export and import in a single piece of code. To transfer data between binary-incompatible platforms, the export phase using SELECT...SAVE AS XML would be carried out on the source platform, the resulting XML file would be transferred to the target platform, then the import phase using CREATE TABLE...LOAD or CREATE TABLE + INSERT would be run on the target platform.
Recital/4GL
The Recital/4GL offers the ability to export data into XML files using the COPY TO ... TYPE XML command and import from XML using the XMLFIRST() and XMLNEXT() functions.
Recital/4GL: Exporting
The COPY TO command can be used to export data from Recital and other natively supported tables out to a wide range of formats. This includes exporting to an XML file. The '.xml' file extension is added automatically. The COPY TO command can be used to export an entire table:
open database southwind use orders copy to orders type xml
or, using the FIELDS clause and the FOR or WHILE clauses, restrict the field list and export only those records which match a particular condition:
open database southwind use orders copy to orders type xml fields orderid for year(orderdate) = 1996
Only the orderid field from those records which match the condition is exported:
<x-ml xmlns:z="#RowsetSchema" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3- 00AA00C14882"> <s:schema id="RowsetSchema"> <s:elementtype rs:updatable="true" content="eltOnly" name="row"> <s:attributetype rs:basecolumn="ORDERID" rs:basetable="ORDERS" rs:write="true" rs:nullable="true" rs:number="1" name="ORDERID"> <s:datatype rs:fixedlength="true" rs:precision="10" rs:scale="0" dt:maxlength="10" rs:dbtype="numeric" dt:type="number"> </s:datatype></s:attributetype> </s:elementtype> </s:schema> <rs:data> <z:row orderid="10248"> <z:row orderid="10249"> <z:row orderid="10250"> <z:row ...
Recital/4GL: Importing
Data from an XML file can be extracted one record at a time using the XMLFIRST() and XMLNEXT() functions. XMLFIRST() reads the first record from an XML file and loads information from the file into a series of memory variables and arrays. The record data is loaded into a one-dimensional array which is created automatically. Each element in the array contains the data for its corresponding field in string format. The field names are loaded into another automatically-created array. The XMLNEXT() function works in a similar way to deal with all the subsequent records in the XML file. The XMLCOUNT() function can be used, as in the example below, to determine how many data records the XML file has.
The Recital/4GL includes a vast range of functions for manipulation and conversion of arrays and their individual elements. In the example program below, the XMLFIRST() and XMLNEXT() functions are used to sequentially extract each record from an XML file, whose name is passed to the program as a parameter. Once loaded into an array, the data is converted to the correct Recital data type then appended into a table. The table name is also passed as a parameter.
procedure replaceit append blank for i = 1 to numfields if type(field(i)) = "N" replace &(field(i)) with val(data[&i]) elseif type(field(i)) = "D" replace &(field(i)) with stod(data[&i]) elseif type(field(i)) = "T" replace &(field(i)) with ctot(data[&i]) elseif type(field(i)) = "L" replace &(field(i)) with iif(data[&i]="T",.T.,.F.) elseif type(field(i)) = "Y" replace &(field(i)) with val(data[&i]) else replace &(field(i)) with data[&i] endif next return procedure starthere parameters cTable, cFile numfields=xmlfirst(cFile,targ,trans,where,fldnames,data) if numfields < 1 dialog box [No records in XML file] else use &cTable replaceit() endif numrecs = xmlcount(cFile) if numrecs > 1 numleft = numrecs -1 for i = 1 to numleft xmlnext(trans,where,fldnames,data) replaceit() next endif return
Alternative Import/Export Methods
Other features exist in Recital to facilitate the import and export of data:
RDDs
The RDDs, Replaceable Database Drivers, are available on Windows, Linux and all supported 32-bit UNIX platforms. They allow for the use and creation of database tables and indexes in FoxPro, dBase and Clipper formats. The file format is the same across all the platforms that support the RDDs, allowing the tables and indexes to be transferred as required. The formats are also supported by a wide range of third-party products as well as their originating database systems. For more information on the RDDs, please see the online documentation on Xbase migration and the SET FILETYPE command.
BUILD/INSTALL
These are Recital/4GL commands for the export (BUILD) and import (INSTALL) of Recital tables and their associated memo, dictionary and multiple index files in ASCII format to allow them to be transferred across binary incompatible platforms. For more information, please see the online documentation on Recital/4GL commands.
COPY Commands
The COPY TO, COPY STRUCTURE, COPY STRUCTURE EXTENDED and CREATE FROM commands can all be used to enable data to be transferred between different formats and different platforms. For more information, please see the online documentation on Recital/4GL commands.
Appendix 1: Microsoft® ActiveX® Data Objects XML Format
For detailed information on the Microsoft® ActiveX® Data Objects XML Format, please consult the following Microsoft documentation:
Link |
APPEND FROM <table-name>Before when appending into a shared Recital table each new row was locked along with the table header, then unlocked after it was inserted. This operation has now been enhanced to lock the table once, complete inserting all the rows from the table and then unlock the table. The performance of this operation has been increased by using this method. All the database and table constraints are still enforced.