`getdata` (Get the results of a query)

The getdata transaction retrieves the results of a query. More precisely, getdata retrieves the values in the result table defined by a query transaction.

Query string

The query string in the HTTP header must contain the following parameters:

api

Specify the name of the API transaction.

apiversion

Specify the version of the API that should handle the requested transaction.

For the most up-to-date version, use apiversion=3.

uid

Specify a valid 1010data user name.

pswd

Specify a valid password for the 1010data user name provided to the uid parameter.

Note: The pswd value should be the encrypted password returned from the login transaction.

sid

The session ID for the current API session.

This value is returned by the login transaction and must be provided to every transaction (except login).

kill (optional)

Valid values for kill are as follows:

yes: Terminate the existing session and start a new one.
no: Do not terminate the existing session.
If no session exists, a new one will be created. If a session does exist, an error is returned.

Note: If kill=no and a session is active, the system returns an error.
possess: Log in and possess the existing session.
auth: Authenticate with the system but do not kill, possess, or start any session.

The default is yes.

XML input to server

The specifications must contain the following elements:

<cols>

The column name(s) of the column(s) that should be retrieved for the query results. Column names should be contained in <col> elements, as shown below:

<cols>
  <col>column1</col>
  <col>column2</col>
</cols>

<rows> (optional)

The row(s) to be retrieved

<rows> must include the attribute mode, which indicates the way in which rows to be returned are selected:

mode="1" allows a relative selection (e.g., next 10 rows)
mode="2" allows an absolute selection (e.g., rows 10-20)

For mode="1", the <rows> element has the form:

<rows mode="1">
  <next>number of rows</next>
</rows>

Note: <next> is required if mode="1".

For mode="2", the form is:

<rows mode="2">
  <from>row number</from>
  <to>row number</to>
</rows>

Note: <from> and <to> are required if mode="2".

If not specified, all rows are retrieved.

format

Specifies the format in which data will be returned. Data may be returned as XML or as a delimited data.

<format> must include the type attribute and may include the values attribute.

The type attribute indicates the format type ("xml" or "csv").

If type="xml", the <format> element has no contents.

If type="csv", it may contain the following elements:

<sep> - Character with which to separate columns (optional)
<linesep> - Character with which to separate rows (optional)

If not specified, the column separator is a comma, and the line separator is a newline ("
").

The values attribute indicates whether the data values should be "raw" or "formatted".

Raw values are a simple representations of the data, while formatted values are the way the data appears in a table view in the user interface. Examples of raw values are 1234567, 7.87083333333333, and 19950101, while the corresponding formatted values might be 1,234,567, 7.87, and 01/01/95.

If type="csv" and values="formatted", <format> is optional. If not specified, XML data with raw formatting is returned.

XML response from server

A successful response from the server contains the following elements:

<rc>: The return code generated by the transaction. For a list of return codes, see Return codes.
<msg>: The message returned by the system. Specific messages correspond to specific return codes.

Query string example

https://www2.1010data.com/gw.k?api=getdata&apiversion=3&uid=$UID&pswd=$PSWD&sid=$SID

XML examples of input and response for the `getdata` transaction

The examples in this section assume a query transaction has already been successfully executed. The input for the query transaction assumed in these examples is as follows:

<in>
    <name>pub.doc.retail.salesdetail</name>
    <ops>
        <link table2="pub.doc.retail.product" col="sku" col2="sku" suffix="_sm"/>
        <sel value="transid=1400190251"/>
    </ops>
</in>

XML input example 1: relative row specification with data returned in XML

This example assumes the query transaction has already run.

<in>
    <cols>
        <col>transid</col>
        <col>trans_date</col>
        <col>sku</col>
        <col>description_sm</col>
        <col>qty</col>
        <col>xsales</col>
        <col>dept</col>
        <col>deptdesc_sm</col>
    </cols>
    <rows mode="1">
        <next>10</next>
    </rows>
    <format type="xml"/>
</in>

The input above provides the following response from the server:

<out>
    <rc>0</rc>
    <msg>getdata successful</msg>
    <table>
        <cols>
            <th name="transid" type="i" format="type:nocommas;width:6;dec:0" fixed="1">Trans
            ID</th>
            <th name="trans_date" type="i" fixed="0">Date</th>
            <th name="sku" type="i" format="type:nocommas" fixed="0">SKU</th>
            <th name="description_sm" type="a" fixed="0">Description</th>
            <th name="qty" type="f" format="type:num;width:5;dec:2" fixed="0">Qty/
            Wgt</th>
            <th name="xsales" type="f" fixed="0">Extended
            Sales</th>
            <th name="dept" type="i" fixed="0">Department</th>
            <th name="deptdesc_sm" type="a" fixed="0">Dept
            Desc</th>
        </cols>
        <data>
            <tr>
                <td>1400190251</td>
                <td>20140101</td>
                <td>242846</td>
                <td>PRIVATE LABEL</td>
                <td>1</td>
                <td>2.31</td>
                <td>20</td>
                <td>DAIRY DELI</td>
            </tr>
            <tr>
                <td>1400190251</td>
                <td>20140101</td>
                <td>105755</td>
                <td>CHAMP SLICED HAM 4X6</td>
                <td>1</td>
                <td>3.75</td>
                <td>47</td>
                <td>MEAT DELI</td>
            </tr>
            <tr>
                <td>1400190251</td>
                <td>20140101</td>
                <td>449508</td>
                <td>PRIVATE LABEL</td>
                <td>1</td>
                <td>1.37</td>
                <td>13</td>
                <td>BAKERY</td>
            </tr>
            <tr>
                <td>1400190251</td>
                <td>20140101</td>
                <td>247560</td>
                <td>BANANAS - WIC</td>
                <td>2.2</td>
                <td>1.34</td>
                <td>36</td>
                <td>PRODUCE</td>
            </tr>
            <tr>
                <td>1400190251</td>
                <td>20140101</td>
                <td>118708</td>
                <td>FSHR IMITATION CHEESE SINGLE 1</td>
                <td>1</td>
                <td>1.13</td>
                <td>20</td>
                <td>DAIRY DELI</td>
            </tr>
            <tr>
                <td>1400190251</td>
                <td>20140101</td>
                <td>405883</td>
                <td>PRIVATE LABEL</td>
                <td>2</td>
                <td>3.15</td>
                <td>55</td>
                <td>DAIRY</td>
            </tr>
        </data>
    </table>
</out>

XML input example 2: absolute row specification; comma-separated; raw values

This example differs in two ways from the first. The input for the transaction specifies the same columns to be returned, but also uses mode="2" and provides an absolute range for which rows should be returned. This example also stipulates that the data be returned in a CSV file.

<in>
    <cols>
        <col>transid</col>
        <col>trans_date</col>
        <col>sku</col>
        <col>description_sm</col>
        <col>qty</col>
        <col>xsales</col>
        <col>dept</col>
        <col>deptdesc_sm</col>
    </cols>
    <rows mode="2">
        <from>1</from>
        <to>10</to>
    </rows>
    <format type="csv" values="raw">
        <sep>,
        </sep>
    </format>
</in>

The output produced from the above input is as follows.

<out>
    <rc>0</rc>
    <msg>getdata successful</msg>
    <csv>
        <![CDATA["Trans
        ID","Date","SKU","Description","Qty/
        Wgt","Extended
        Sales","Department","Dept
        Desc"
        1400190251,20140101,242846,"PRIVATE LABEL",1,2.31,20,"DAIRY DELI"
        1400190251,20140101,105755,"CHAMP SLICED HAM 4X6",1,3.75,47,"MEAT DELI"
        1400190251,20140101,449508,"PRIVATE LABEL",1,1.37,13,"BAKERY"
        1400190251,20140101,247560,"BANANAS - WIC",2.2,1.34,36,"PRODUCE"
        1400190251,20140101,118708,"FSHR IMITATION CHEESE SINGLE 1",1,1.13,20,"DAIRY DELI"
        1400190251,20140101,405883,"PRIVATE LABEL",2,3.15,55,"DAIRY"]]>
    </csv>
</out>

XML input example 3: absolute row specification; comma-separated; formatted values

<in>
    <cols>
        <col>transid</col>
        <col>trans_date</col>
        <col>sku</col>
        <col>description_sm</col>
        <col>qty</col>
        <col>xsales</col>
        <col>dept</col>
        <col>deptdesc_sm</col>
    </cols>
    <rows mode="2">
        <from>1</from>
        <to>10</to>
    </rows>
    <format type="csv" values="formatted">
        <sep>,
        </sep>
    </format>
</in>

The input above specifies that formatted values be returned. The response from the server is as follows:

<out>
    <rc>0</rc>
    <msg>getdata successful</msg>
    <csv>
        <![CDATA["Trans
        ID","Date","SKU","Description","Qty/
        Wgt","Extended
        Sales","Department","Dept
        Desc"
        1400190251,01/01/14,242846,PRIVATE LABEL, 1.00,2.31,20,DAIRY DELI
        1400190251,01/01/14,105755,CHAMP SLICED HAM 4X6, 1.00,3.75,47,MEAT DELI
        1400190251,01/01/14,449508,PRIVATE LABEL, 1.00,1.37,13,BAKERY
        1400190251,01/01/14,247560,BANANAS - WIC, 2.20,1.34,36,PRODUCE
        1400190251,01/01/14,118708,FSHR IMITATION CHEESE SINGLE 1, 1.00,1.13,20,DAIRY DELI
        1400190251,01/01/14,405883,PRIVATE LABEL, 2.00,3.15,55,DAIRY]]>
    </csv>
</out>

getdata (Get the results of a query)