Relational Data Sources
This section describes the usage of DSL APIs for creating, altering or deleting a relational data source.
Creating a Relational Data Source with Native Connection Properties
Native properties may be specified using "SET NATIVE PROPERTIES" in the DSL syntax with name value pairs in JSON format. The JSON format can be specified as key value pairs and the keys represent the definition_name taken from model.SYS_DATASOURCE_ATTRIBUTE_DEFS table. The definition_name may be specified in a case-insensitive manner.
Considerations
Listed below are some points to consider while working with Relational Data sources using native properties syntax:
1. Collision between native properties with standard DSL expanded form will throw error. For example if "urlIP" is specified as a property in SET NATIVE PROPERTIES syntax and also specified in "CONNECT USING HOST 'xxx'" syntax, error will be displayed indicating this.
2. Warning when properties are used when a DSL expanded syntax is available for a property/definition. For example, if "urlIP" is specified as a property in SET NATIVE PROPERTIES syntax and not specified using a standard DSL syntax as "CONNECT USING HOST", a warning will be logged in the cs_server.log indicating the syntax that can be used for this particular property. This is done to encourage users to use DSL syntax whenever possible. However, if there is no corresponding DSL syntax for a property, then no warning is logged. Refer to the section
Examples for an understanding of how to create or alter a data source.
Examples
curl -d "[\"CREATE DATASOURCE /shared/examples/relationalDS BASED ON ADAPTER 'PostgreSQL 9.1' SET NATIVE PROPERTIES {\\\"urlIP\\\":\\\"localhost\\\", \\\"urlPort\\\":9408, \\\"urlDatabaseName\\\":\\\"orders\\\", \\\"login\\\":\\\"tutorial\\\", \\\"password\\\":\\\"password\\\"} SET ANNOTATION 'this is a Postgres datasource created using DSL api native properties'\"]" -u "admin:admin" -X POST "http://localhost:9400/rest/execute/v1/actions/dsl/invoke" -H "Content-Type:application/json"
Creating a datasource with DSL keywords
Considerations
Listed below are some points to consider while working with Relational Data sources using DSL keywords:
1. When connection properties are not specified during creation, introspection is not done on the datasource as its a required information for introspection. Datasource is created without introspection. When the datasource is altered later to add connection properties, full introspection of the datasource is done.
2. When no authentication properties are specified during creation, an error will be displayed during introspection, but the datasource will be created successfully.
3. For QUOTED_STRING ticket_cache, an explicit NULL keyword or 'NULL' (as a quoted string) can be specified. If null is invalid for some reason during introspection, an error will be displayed.
Note: If an unexpected input is given or if a resource is missing, an error is displayed.
Examples
Create a Postgres datasource ds1
curl -d "[\"CREATE DATASOURCE /shared/examples/ds1 BASED ON ADAPTER 'PostgreSQL 9.1'
CONNECT USING host 'localhost' port 5432 DATABASE_NAME 'orders'
AUTHENTICATE IN BASIC MODE USING LOGIN 'tutorial' PASSWORD 'password' PASS_THROUGH_LOGIN_TRUE
SET ANNOTATION 'this is a datasource created using DSL api'\"]"
-u "admin:admin" -X POST "http://localhost:9400/rest/execute/v1/actions/dsl/invoke" -H "Content-Type:application/json"
Create an Oracle datasource oracleds
curl -d "[\"CREATE DATASOURCE /shared/examples/oracleds BASED ON ADAPTER 'Oracle 11g (Thin Driver)'
CONNECT USING host 'xxx.xx.x.xx' port 1521 DATABASE_NAME 'xxxxx'
AUTHENTICATE IN BASIC MODE USING LOGIN 'user' PASSWORD 'password'
SET ANNOTATION 'this is an oracle datasource created using DSL api'\"]"
-u "admin:admin" -X POST "http://localhost:9400/rest/execute/v1/actions/dsl/invoke" -H "Content-Type:application/json"
Create a Postgres datasource ds1 with space in its name
curl -d "[\"CREATE DATASOURCE /shared/examples/\\\"ds 1\\\" BASED ON ADAPTER 'PostgreSQL 9.1'
CONNECT USING host 'localhost' port 5432 DATABASE_NAME 'orders'
AUTHENTICATE IN BASIC MODE USING LOGIN 'tutorial' PASSWORD 'password' PASS_THROUGH_LOGIN_TRUE
SET ANNOTATION 'this is a datasource created using DSL api'\"]"
-u "admin:admin" -X POST "http://localhost:9400/rest/execute/v1/actions/dsl/invoke" -H "Content-Type:application/json"