CData Cloud offers access to CockroachDB across several standard services and protocols, in a cloud-hosted solution. Any application that can connect to a MySQL or SQL Server database can connect to CockroachDB through CData Cloud.
CData Cloud allows you to standardize and configure connections to CockroachDB as though it were any other OData endpoint, or standard SQL Server/MySQL database.
This page provides a guide to Establishing a Connection to CockroachDB in CData Cloud, as well as information on the available resources, and a reference to the available connection properties.
Establishing a Connection shows how to authenticate to CockroachDB and configure any necessary connection properties to create a database in CData Cloud
Accessing data from CockroachDB through the available standard services and CData Cloud administration is documented in further details in the CData Cloud Documentation.
Connect to CockroachDB by selecting the corresponding icon in the Database tab. Required properties are listed under Settings. The Advanced tab lists connection properties that are not typically required.
Set the following to connect to data.
You can use the following properties to gain more control over the data returned from CockroachDB:
By default, the Cloud attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.
To specify another certificate, see the SSLServerCert property for the available formats to do so.
The CockroachDB Cloud also supports setting client certificates. Set the following to connect using a client certificate.
Set the following properties:
The Cloud maps types from the data source to the corresponding data type available in the schema. The table below documents these mappings.
| CockroachDB | CData Schema |
| abstime | string |
| aclitem | string |
| bigint | long |
| bigserial | long |
| bit varying | string |
| bit | string |
| boolean | bool |
| box | string |
| bytea | binary |
| char | string |
| character varying | string |
| character | string |
| cid | string |
| cidr | string |
| circle | string |
| date | date |
| daterange | string |
| double precision | float |
| gtsvector | string |
| inet | string |
| int2vector | string |
| int4range | string |
| int8range | string |
| integer | int |
| json | string |
| jsonb | binary |
| line | string |
| lseg | string |
| macaddr8 | string |
| macaddr | string |
| money | decimal |
| name | string |
| numeric | decimal |
| numrange | string |
| oid | string |
| oidvector | string |
| path | string |
| pg_dependencies | string |
| pg_lsn | string |
| pg_ndistinct | string |
| pg_node_tree | string |
| point | string |
| polygon | string |
| real | float |
| refcursor | string |
| regclass | string |
| regconfig | string |
| regdictionary | string |
| regnamespace | string |
| regoper | string |
| regoperator | string |
| regproc | string |
| regprocedure | string |
| regrole | string |
| regtype | string |
| reltime | string |
| serial | int |
| smallint | int |
| smallserial | int |
| smgr | string |
| text | string |
| tid | string |
| time with time zone | string |
| time without time zone | time |
| timestamp with time zone | datetime |
| timestamp without time zone | datetime |
| tinterval | string |
| tsquery | string |
| tsrange | string |
| tstzrange | string |
| tsvector | string |
| txid_snapshot | string |
| uuid | string |
| xid | string |
| xml | string |
The connection string properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure in the connection string for this provider. Click the links for further details.
For more information on establishing a connection, see Establishing a Connection.
| Property | Description |
| Server | The host name or IP address of the server. |
| Port | The port number of the CockroachDB server. |
| Database | The name of the Cockroach database. |
| User | The CockroachDB user account used to authenticate. |
| Password | The password used to authenticate the user. |
| UseSSL | Whether SSL is enabled. |
| Cluster | The name of the CockroachDB cluster. |
| Property | Description |
| SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
| Property | Description |
| Verbosity | The verbosity level that determines the amount of detail included in the log file. |
| Property | Description |
| BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC. |
| Property | Description |
| AllowPreparedStatement | Allow the preparation of a query statement before its execution. |
| FetchResultSetMetadata | This field sets whether the provider retrieves metadata pertaining to the schema and table name for resultset columns returned by the server. |
| MaxRows | Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses. |
| Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
| TimeZone | The time zone for the current session. |
This section provides a complete list of the Authentication properties you can configure in the connection string for this provider.
| Property | Description |
| Server | The host name or IP address of the server. |
| Port | The port number of the CockroachDB server. |
| Database | The name of the Cockroach database. |
| User | The CockroachDB user account used to authenticate. |
| Password | The password used to authenticate the user. |
| UseSSL | Whether SSL is enabled. |
| Cluster | The name of the CockroachDB cluster. |
The host name or IP address of the server.
string
"EMPTYSTRING"
The host name or IP of the server hosting the CockroachDB Database. If not set, the default value "localhost" is used.
The name of the Cockroach database.
string
""
The database to connect to when connecting to the CockroachDB Server. If a database is not provided, the default database will be used.
The CockroachDB user account used to authenticate.
string
""
Together with Password, this field is used to authenticate against the CockroachDB server.
The password used to authenticate the user.
string
""
The User and Password are together used to authenticate with the server.
Whether SSL is enabled.
bool
false
This field sets whether the Cloud will attempt to negotiate TLS/SSL connections to the server. By default, the Cloud checks the server's certificate against the system's trusted certificate store. To specify another certificate, set SSLServerCert.
The name of the CockroachDB cluster.
string
""
The cluster to connect to when connecting to the CockroachDB Server. Not specified by default.
This section provides a complete list of the SSL properties you can configure in the connection string for this provider.
| Property | Description |
| SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
The certificate to be accepted from the server when connecting using TLS/SSL.
string
""
If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.
This property can take the following forms:
| Description | Example |
| A full PEM Certificate (example shortened for brevity) | -----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE----- |
| A path to a local file containing the certificate | C:\cert.cer |
| The public key (example shortened for brevity) | -----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY----- |
| The MD5 Thumbprint (hex values can also be either space or colon separated) | ecadbdda5a1529c58a1e9e09828d70e4 |
| The SHA1 Thumbprint (hex values can also be either space or colon separated) | 34a929226ae0819f2ec14b4a3d904f801cbb150d |
If not specified, any certificate trusted by the machine is accepted.
Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.
This section provides a complete list of the Logging properties you can configure in the connection string for this provider.
| Property | Description |
| Verbosity | The verbosity level that determines the amount of detail included in the log file. |
The verbosity level that determines the amount of detail included in the log file.
string
"1"
The verbosity level determines the amount of detail that the Cloud reports to the Logfile. Verbosity levels from 1 to 5 are supported. These are detailed in the Logging page.
This section provides a complete list of the Schema properties you can configure in the connection string for this provider.
| Property | Description |
| BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC. |
This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.
string
""
Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.
This section provides a complete list of the Miscellaneous properties you can configure in the connection string for this provider.
| Property | Description |
| AllowPreparedStatement | Allow the preparation of a query statement before its execution. |
| FetchResultSetMetadata | This field sets whether the provider retrieves metadata pertaining to the schema and table name for resultset columns returned by the server. |
| MaxRows | Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses. |
| Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
| TimeZone | The time zone for the current session. |
Allow the preparation of a query statement before its execution.
bool
true
If the AllowPreparedStatement property is set to false, statements are parsed each time they are executed. Setting this property to false can be useful if you are executing many different queries only once.
If you are executing the same query repeatedly, you will generally see better performance by leaving this property at the default, true. Preparing the query avoids recompiling the same query over and over. However, prepared statements also require the Cloud to keep the connection active and open while the statement is prepared.
This field sets whether the provider retrieves metadata pertaining to the schema and table name for resultset columns returned by the server.
bool
false
By default, the Cloud will not request that the server provides detailed information about resultset columns like the table name or schema name. It requires issuing additional metadata queries via Cloud , and it may affect query performance essentially in some scenarios. Consider setting this property to True when you need such detailed descriptive information for the resultset columns.
Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
int
-1
Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
The value in seconds until the timeout error is thrown, canceling the operation.
int
30
If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.
If Timeout expires and the operation is not yet complete, the Cloud throws an exception.
The time zone for the current session.
string
""
If the TimeZone property is not set, the Cloud uses the client system time zone. Setting this property can be useful when you need the server to convert "time with time zone" and "timestamp with timezone" values to a time zone other than the client's system time zone.