Files over HTTP3:Connected Data

[kino]

Using the Curl API, you can connect directly to a database server or a set of files extracted from a database. You access data through a BasicConnection or a FileConnection. A FileConnection creates a connection to a set of files in comma separated value (CSV) format. A BasicConnection creates a connection to a server-side database management system via a servlet and a simple XML protocol over HTTP. We'll discuss each of these scenarios in this Curl Cue.


Both BasicConnection and FileConnection are subsets of a Connection class that provide access to relational data organized as a collection of databases, each of which contains a collection of tables. Each table, in turn, can be accessed as a Curl RecordSet object.


Using a File Connection
The FileConnection class is easiest for simple applications. You can have a single table or a group of related tables in which specific data can be queried for use within the Curl application. In this scenario, CSV-formatted descriptor files are used to describe the data location and structure. Since this metadata is described in files, is easy to modify and does not need to be embedded in the Curl code.


A FileConnection accesses data structured as a collection of files that play the following roles:

•A database descriptor file

•Table descriptor files

•One or more CSV data files


Database Descriptor File
A database descriptor file describes the databases available on the connection, and the tables in each database. You provide the Url of this file to the constructor when you create the FileConnection. This file is used to locate other descriptor files that describe the databases and tables available through the connection. The database descriptor file contains an initial header line, in which you can put labels that help you remember the meaning of the fields in the following lines. For this example, we created the file connection.csv which contains the following information:

connection.csv


•Database: the name of a database.

•Table: the name of a table in the database.

•Location: the name of the file that contains data stored in this table.

•FieldsLocation: the location and name of the table descriptor file for the table named in the Table field.

•HeaderLines: the number of header lines in the data storage file named in the Location field.

•Encoding: The character encoding for the data storage file.


This file can have multiple records. Each record in the database descriptor file represents a table and specifies the location of the data and the table descriptor files.


Table Descriptor File
The table descriptor file is named in the ''FieldsLocation'' of the database descriptor file and describes the fields of the corresponding table. Here is an example of a table descriptor file:

inventory_fields.csv


•name: the name of a field in the table.

•caption: a caption for the field.

•domain: a string that describes the domain for the field.

•default: the default value for this field.

•modifiable?: specifies whether this field is modifiable.

•nullable?: specifies whether the value of this field can be null.


For example, this table descriptor file specifies the data fields of our example CSV data file. Like the database descriptor file, a table descriptor file begins with a header line that contains labels for the fields in the file.


CSV Data Files
And finally, let's discuss our data. The CSV file stores all the data we will be converting to a RecordSet. Remember that you can access more than one CSV data file, as long as each one is described in the database descriptor file
inventory_fields.csv

Code:

{curl 6.0, 7.0 applet}

{import * from CURL.DATA-ACCESS.CONNECTED}
{value
let con:FileConnection = {FileConnection {url "connection.csv"}}
let rs:RecordSet = {con.create-record-set "sample", "inventory"}
{RecordGrid record-source = rs,
width = 6in,
height = 2in
}
}

It is important to note that FileConnection is defined in the CURL.DATA-ACCESS.CONNECTED package. Therefore, we need to explicitly import this package.




For more information regarding file connections, please refer to the following section in the Curl Documentation:Curl Developer's Guide > Data Management and Display > Managing Data from External Sources > Using RecordSets with Data Files



Using a Basic Connection
The BasicConnection class allows you to directly connect to a database server and create RecordSets that are used in a Curl application. To use the BasicConnection object you need to create a servlet on the server that implements the server side of the BasicConnection protocol.


Once this infrastructure is set up, you create a connected record set in two steps.

1.Create a BasicConnection object,

2.Call the method Connection.create-record-set


The url provided to the BasicConnection constructor identifies the servlet that implements the server side of the protocol.

Code:

{import * from CURL.DATA-ACCESS.BASE}
{import * from CURL.DATA-ACCESS.CONNECTED}
{let connection:BasicConnection =
{BasicConnection {url http://localhost:8080/test-server/TestServlet}}
}

In this case, TestServlet is the servlet located on the data server. Once the connection is established, the create-record-set method will directly create a RecordSet using data from the server.

Code:

{RecordGrid
record-source = {connection.create-record-set sample, inventory}
}

Once the RecordSet is created, the API for accessing the data is the same as with a FileConnection.



Use a ConnectedRecordSet just as you use any other RecordSet. If it is modifiable, and if the database server allows it, committing changes to the ConnectedRecordSet updates the data on the database server.