The SELECT service is used to extrapolate data. You can in fact query the cloud system, indicating a specific table, to obtain a list of all the records in it or the list of only the records modified or entered from a specific date/time.


The service in question can be called up via the following URL: https://api.gooseapp.com/select/


The JSON data structure must be sent by POST, below is an example of a call JSON:


{
code: "DEV001",
key: "09mpada9nx4mxl3346055518sf89hcyga74mwmhucgf9j7cg78",
table: "CON_TRO"
}


This call returns a JSON containing all the records found in the CON_TRO table.


The parameters available for the SELECT calls are as follows.

Name

Type

 Mandatory

Description

code


varchar(6)


Yes


Company code
key


varchar(200)


Yes


Secure API Key


table


varchar(7)


Yes


Table name

fields


varchar(255)


No


List of the fields you wish to receive, all table fields are provided by default.

If you wish to receive only a specific list of fields simply indicate them with a comma as separator.

E.g.:  "RECORD_ID,CORAS,CORA2,COCON,COCLI,COFOR"


from


varchar(50)


No


Date / time format "yyyy-mm-dd hh:mm:ss" that allows you to request only the records that have been modified or entered from a certain period onward.

Alternatively, you can use the "latest" keyword to have only the list of records modified or inserted on a given table from the last successful call.


imc


true/false


No


The imc parameter stands for "Include My Call".

This parameter is only considered if the from parameter is also given a value, with a valid date/time format or with "latest" value.

If it is set to true, all records modified or inserted are provided, including those modified or entered by one’s service, otherwise excluded. 
filter


array


No


The filter parameter allows you to apply a filter on the indicated table.

This parameter requires a data sub-structure with the following parameters:
key: Filter key (name of the field you wish to query)
value: Filter value

If given a value, the from and imc parameters will be ignored.


The fields and from parameters are not mandatory, however, they are essential if you wish to perform incremental sync operations, especially from, which may be used in the date/time format as well as with the "latest" keyword.

A call like the following, in fact, allows you to obtain the list of all records, including all data columns, modified since the last call of your service. 

{
 code: "DEV001",
 key: "09mpada9nx4mxl3346055518sf89hcyga74mwmhucgf9j7cg78",
 table: "CON_TRO",
 from: "latest"
}


If, however, you only wish to obtain a list of specific fields:


{
 code: "DEV001",
 key: "09mpada9nx4mxl3346055518sf89hcyga74mwmhucgf9j7cg78",
 table: "CON_TRO",
 from: "latest",
 fields: "RECORD_ID,CORAS"
}


This last call would only bring up the modified records since the last successful call with the RECORD_ID and CORAS fields of the CON_TRO table.

If instead you wish to obtain a specific record:


 {
 code: "DEV001",
 key: "09mpada9nx4mxl3346055518sf89hcyga74mwmhucgf9j7cg78",
 table: "CON_TRO",
 fields: "RECORD_ID,CORAS",
 filter: {
 key: "RECORD_ID",
 value: "0-1"
}
}


This last call would bring up the RECORD_ID and CORAS fields only for records with a "0-1” value in the RECORD_ID field in the CON_TRO table.


If the operation performed is successful you will obtain the following JSON: 

{
 status: "OK",
 result:
 {
  total: 3,
  data: 
  [
   {
    RECORD_ID: "0-1",
    CORAS: "Test 1",
    COTEL: "0113486474"
   },
   {
    RECORD_ID: "0-2",
    CORAS: "Test 2",
    COTEL: "0113486474"
   },
   {
    RECORD_ID: "0-3",
    CORAS: "Test 3",
    COTEL: "0113486474"
   }
  ]
 }
}


The "OK" status indicates that the SELECT operation was successful. The result structure also contains the total fields (total selected rows) and data (return data structure).


If the operation performed is not successful you will obtain the following JSON: 

{
 status: "error",
 error: 
 {
   code: 120,
   description: "Table 'client_goose_DEM001.CON_TRO' doesn't exist'"
 }
}


In case of an error, the status is shown with an "error" value and the error code and a description of the error are indicated in the error structure.

Do you want to ask us something?
Contact us at support@gooseapp.com