"listDatabases" (JSON Action)
JSON DB "listDatabases" action retrieves all databases on the server
The "listDatabases" action retrieves databases on the server. You can optionally filter which databases are returned by specifying a complete or partial database name. By default, the results are sorted in ascending order.
Request examples
Minimal
{
"api":"db",
"action": "listDatabases",
"authToken": "replaceWithAuthTokenFromCreateSession"
}{
"api": "db",
"action": "listDatabases",
"params": {
"partialDatabaseName": "ctr",
"skipRecords": 0,
"maxRecords": 20,
"transactionId": null
},
"responseOptions": {
"binaryFormat": "hex",
"dataFormat": "objects",
"numberFormat": "string"
},
"authToken": "replaceWithAuthTokenFromCreateSession",
"apiVersion": "1.0",
"requestId": "00000010",
"debug": "max"
}
Minimal
{
"result": {
"dataFormat": "objects",
"fields": [
{
"name": "databaseName",
"type": "varchar",
"length": 8192,
"scale": null,
"defaultValue": null,
"nullable": false,
"primaryKey": 0,
"autoValue": "none"
},
{
"name": "path",
"type": "varchar",
"length": 8192,
"scale": null,
"defaultValue": null,
"nullable": false,
"primaryKey": 0,
"autoValue": "none"
},
{
"name": "uid",
"type": "bigint",
"length": null,
"scale": null,
"defaultValue": null,
"nullable": false,
"primaryKey": 0,
"autoValue": "none"
}
],
"data": [
{
"databaseName": "ctreeSQL",
"path": ".\\ctreeSQL.dbs\\SQL_SYS",
"uid": 999
},
{
"databaseName": "faircom",
"path": ".\\faircom.dbs\\SQL_SYS",
"uid": 1001
}
]
},
"requestId": "1",
"debugInfo": {
"request": {
"api": "db",
"action": "listDatabases",
"requestId": "1",
"debug": "max",
"authToken": "replaceWithAuthTokenFromCreateSession"
},
"serverSuppliedValues": {
"databaseName": null,
"ownerName": null
},
"errorData": {
"errorData": null
},
"warnings": []
},
"errorCode": 0,
"errorMessage": "",
"authToken": "replaceWithAuthTokenFromCreateSession"
}
{
"authToken": "replaceWithAuthTokenFromCreateSession",
"result": {
"dataFormat": "objects",
"fields": [
{
"name": "databaseName",
"type": "varchar",
"length": 8192,
"scale": null,
"defaultValue": null,
"nullable": false,
"primaryKey": 0,
"autoValue": "none"
},
{
"name": "path",
"type": "varchar",
"length": 8192,
"scale": null,
"defaultValue": null,
"nullable": false,
"primaryKey": 0,
"autoValue": "none"
},
{
"name": "uid",
"type": "bigint",
"length": null,
"scale": null,
"defaultValue": null,
"nullable": false,
"primaryKey": 0,
"autoValue": "none"
}
],
"data": [
{
"databaseName": "ctreeSQL",
"path": ".\\ctreeSQL.dbs\\SQL_SYS",
"uid": 999
}
]
},
"requestId": "00000010",
"debugInfo": {
"request": {
"authToken": "replaceWithAuthTokenFromCreateSession",
"api": "db",
"action": "listDatabases",
"params": {
"partialDatabaseName": "ctr",
"skipRecords": 0,
"maxRecords": 20,
"transactionId": null
},
"apiVersion": "1.0",
"requestId": "00000010",
"responseOptions": {
"binaryFormat": "hex",
"dataFormat": "objects",
"numberFormat": "string"
},
"debug": "max"
},
"serverSuppliedValues": {
"databaseName": null,
"ownerName": null
},
"errorData": {
"errorData": null
},
"warnings": []
},
"errorCode": 0,
"errorMessage": ""
}
Use the listDatabases API action to retrieve all databases on the server
The "params" property is an object that contains an action's request parameters as defined by a set of properties. Each action defines its own required and optional properties. See System limits for a comprehensive overview of property requirements and limitations.
"params" properties summaryElement | Description | Default | Type | Limits (inclusive) |
|---|---|---|---|---|
(optional) specifies the maximum number of records to return from an action. |
| integer |
| |
(optional) filters the returned databases by applying partial matches to the beginning of database names |
| string | 0 to 64 bytes | |
(optional) specifies the number of records to skip over in the results before returning records. |
| integer |
| |
is a server-generated string that is used to identify a transaction when the "createTransaction" action is called. |
| string | 0 to 255 bytes |
The "maxRecords" is an optional integer from -1 to 65535 that sets the maximum number of records to return from an action. It is used in query actions to paginate the query results when returning records directly. The default for most actions is to return 20 records.
Important
-1 returns all records and should be used very carefully because a large query can return so many records that it eventually times out and wastes server and client resources.
When present,
"maxRecords"works in conjunction with the"skipRecords"(and, when applicable,"reverseOrder") properties to paginate results."maxRecords"returns an error when"returnCursor"istrue.Note
This does not apply to the actions where
"returnCursor"is not present.When returning a cursor, retrieve records using the
"getRecordsFromCursor"action.A value of
0for"maxRecords"is not useful because it causes the action to return no records."maxRecords"is ignored by the"getRecordsByIds"action.
The "partialDatabaseName" property filters the returned databases by applying partial matches to the beginning of database names. It is an optional string from 0 to 64 bytes. It defaults to an empty string.
The
"listDatabases"action returns databases with names that match the beginning of this string.A zero-length string matches all database names.
Since it defaults to an empty string you can omit the
"partialDatabaseName"property to return all databases.
The "skipRecords" property is an optional integer from 0 to 9223372036854775807. It is used in query actions to paginate the query results when returning records directly. It defaults to 0.
It specifies the number of records to skip over in the results before returning records.
It works with
"maxRecords"and"reverseOrder".It is ignored when
"returnCursor"istrue.Note
This does not apply to the actions where
"returnCursor"is not present.
The "transactionId" is an optional string that the server generates and returns during a "createTransaction" action. The generated ID represents a transaction. In requests, it defaults to an empty string.
When a client wants an action to be controlled by a transaction, the
"transactionId"must be included in the action request.A
"transactionId"is valid and can be applied to multiple actions until it is either committed using"commitTransaction"or rolled back using"rollbackTransaction".A zero-length string means the
"transactionId"is invalid.Do not assume that
"transactionId"is a number embedded in a string.
"result" properties summaryProperty | Description | Type | Limits (inclusive) | |||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
specifies an array or object that the server returns, such as records returned by a query. It is an empty array when no results are available. | array | Its contents are determined by the action | ||||||||||||||||||||||
specifies the format of the data in the | string |
| ||||||||||||||||||||||
specifies an array of objects set by the server, where each object is the definition of a field in a table defining the details of each field returned by a query. | array | |||||||||||||||||||||||
| specifies when and how the server automatically sets the field value. | string |
| |||||||||||||||||||||
| specifies the default value of a field. | string | 0 to 65,500 bytes | |||||||||||||||||||||
| specifies the length of a field's value in a record. | integer |
| |||||||||||||||||||||
| specifies the new name of the field. | string | 0 to 64 bytes | |||||||||||||||||||||
| allows a field to contain a NULL value when | Boolean |
| |||||||||||||||||||||
| adds a field to the specified ordinal position of the table's primary key when > 0. | integer |
| |||||||||||||||||||||
| for | integer |
| |||||||||||||||||||||
| specifies the type of field. | string |
|
This optional property indicates when and how the server automatically sets the field value.
Only one of these values is allowed per field.
"none"indicates that the server does not automatically set the field's value."incrementOnInsert"indicates that the server automatically increments a field’s value each time the server inserts a new record. It applies to fields that are of the type of decimal or one of the integer types, such as"bigint". Only one field per table can have this attribute. The server returns an error when assigning this attribute to multiple fields. The JSON DB API automatically creates the"id"field as an"incrementOnInsert"field. If you apply this attribute to another field, it becomes the only automatically incremented field in the table. If you want that field to be the primary key, assign"primaryKey": 1to it."timestampOnInsert"indicates that the server automatically sets a field’s value to the current date and time of an insert. It applies only to fields with a type of"timestamp".Note
If you attempt to insert a record and specify a timestamp for a field that has
"autoValue"set to"timestampOnInsert", the timestamp you specified is ignored, and the current date and time are assigned to the field."timestampOnUpdate"indicates that the server automatically sets a field’s value to the current date and time of an update. This setting applies only to timestamp fields."timestampOnUpdateAndInsert"indicates that the server automatically sets a field’s value to the current date and time of an insert and an update. It applies only to fields with a type of"timestamp"."changeId"indicates the server uses the field for optimistic locking. The server automatically sets the field's value to the internal transaction number used during the last update of the record. This value changes each time the server updates the record. A table may only have one change tracking field. The field type must be"bigint".The JSON DB API automatically creates a
"changeid"field with change-tracking functionality.Change tracking is optional in the CTDB and ISAM APIs. The application must create a 64-bit integer field and assign change-tracking functionality to it.
Request Example
"fields": [
{
"name": "signed_int32",
"type": "integer",
"autoValue": "incrementOnInsert"
}
]
Response Example
{
"result": {
"dataFormat": "objects",
"data": [
{
"changeIdField": "changeId",
"createRecByteIndex": false,
"databaseName": "ctreeSQL",
"fieldDelimiterValue": 0,
"fields": [
{
"autoValue": "incrementOnInsert",
"defaultValue": null,
"length": null,
"name": "signed_int32",
"nullable": true,
"primaryKey": 0,
"scale": null,
"type": "integer"
}
]
}
]
},
"errorCode": 0,
"errorMessage": "",
"authToken": "replaceWithAuthTokenFromCreateSession"
}
The "data" property is an array of objects or arrays that contain information about the database. The "dataFormat" property specifies whether the content is arrays or objects. The initiating action defines the data contents. In results messages, when no results are available, it is empty. For path formats, see "path".
Examples
arrays
"data": [ ["test1", ".\\test1.dbs\\SQL_SYS", 1003] ]
objects
"data":
[
{
"databaseName": "test7",
"path": ".\\test7.dbs\\SQL_SYS",
"uid": 1015
}
]
The "dataFormat" property is a case-insensitive string enum that defines the format of the "data" property. The default format is an array of arrays. The alternative is an array of objects. The default for "dataFormat" can be changed during a "createSession" action by assigning a different value to the "dataFormat" property in "defaultResponseOptions".
"dataFormat" property:Two of those versions occur in a request, and another occurs in a response. They all indicate how data is formatted.
"dataFormat"in the request in"responseOptions"determines how the"data"property in the response is formatted.Possible values include:
"arrays"This is the default and causes the server to return results as an array of arrays, which is the most efficient.
"objects"This returns results as an array of objects. This is less efficient but is simpler to generate, read, and troubleshoot.
"dataFormat"in the request in the"params"object notifies the server how the"sourceData"property is formatted in the request. This version is rarely used because of the default"autoDetect"behavior.Possible values include:
"arrays"This causes the server to return results as an array of arrays, which is the most efficient.
"objects"This returns results as an array of objects. This is less efficient but is simpler to generate, read, and troubleshoot.
"autoDetect"This is the default, and the server automatically detects the format of the data in the
"sourceData"property.
"dataFormat"in the response shows the client how the server formatted the"data"property.Possible values include:
"arrays"This is the default and causes the server to return results as an array of arrays, which is the most efficient.
"objects"This returns results as an array of objects. This is less efficient but is simpler to generate, read, and troubleshoot.
Example response
{
"result": {
"dataFormat": "objects"
}
}
The "fields" property is an array of objects. It is required when creating a table. Each object in the array defines a field by specifying its properties.
Example
"fields": [
{
"autoValue": "none",
"name": "name",
"type": "varchar",
"length": 50,
"scale": null,
"defaultValue": null,
"nullable": false
}
]