A SQL Datasource is an HTTP endpoint to access SQL data, using a provided JDBC driver. These resources only represent the connection setup and proxy HTTP requests/response to the remote SQL database managed externally.

From a folder view, choose "SQL Datasource" from the create menu. If you don't see it try scrolling down and check that your user is in the admin group and has accepted any outstanding invitations.

Field Description Example
Label The value will be used to compute the local part in the resulting endpoint A label of "TestDB" will have a local part of "testdb"
Comment Free text field used to describe author's intent for creating this
JDBC URL The syntax is specific to the driver being used, but always starts with jdbc: jdbc:mysql://localhost:3306/javatest
Driver class name The value is unique to the driver and provided by the vendor com.mysql.jdbc.Driver
Driver jar file The uploaded driver JAR provided by the vendor, should corespond to the version of the database mysql-connector-java-5.1.29-bin.jar
localhost to modify Indicates that the system can modify the data without further authentication This is useful when access the datasource via pipeline or PURL
Validation qurey A command sent to server prior to every request to ensure communication is open to remote database SELECT 1
Max active Limit the number of active concurrent connections to the remote database 100
Max idle Pool the connections, but don't keep more that this amount inactive. 30
Max wait When the Max active value is reached, wait this many miliseconds for one to become available before giving up 10000

Using SQL Datasource

The endpoint will only process a single command per request. Transactions are not supported. However, replacing the contents of a table is performed within a transaction. Below is a list of request types that are supported by a SQL Datasource /sql.

Description HTTP Method Query parameter Content-Type xor Accept header Example
Query the database using an arbitrary SELECT command GET query "text/csv;header=present", "text/tab-separated-values", or "application/sparql-results+xml"
GET /sql?query=SELECT%20*%20FROM%20%20Book
Accept: text/csv
Query or Modify the database using an arbitrary SQL command POST n/a application/sql
POST /sql
Content-type: application/sql

UPDATE example
 SET field1 = 'updated value'
 WHERE field2 = 'N';
Retrive the contents of a table GET table "text/csv;header=present", "text/tab-separated-values", or "application/sparql-results+xml"
GET /sql?table=example
Accept: text/csv
Replace the contents of a table PUT table "text/csv;header=present", "text/tab-separated-values", or "application/sparql-results+xml"
PUT /sql?table=Book
Content-Type: text/csv;header=present

title,authors
"SQL Examples and Guide",4
"The Joy of SQL",1
"An Introduction to SQL",2
Append the contents to a table POST table "text/csv;header=present", "text/tab-separated-values", or "application/sparql-results+xml"
POST /sql?table=Book
Content-Type: text/csv;header=present

title,authors
"Pitfalls of SQL",1
Drop a table DELETE table n/a
DELETE /sql?table=Book