Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
Clear All
new posts

  • 6.1p DataImport importToDataSource issue in combination with RESTHandler

    Hi Isomorphic,

    I'm currently creating an API to my application and hit some bumps here in DataImport I use in combination with RESTHandler (latest 6.1p, v11.1p_2017-11-08).
    The issues/questions are mainly DataImport-related and the whole testcase is serverside only (FF26 Dev mode in Eclipse).

    employeesUpload.ds.xml:
    Code:
    <DataSource xmlns="lmscompany/ds" xmlns:fmt="lmscompany/fmt" ID="employeesUpload" serverType="sql" tableName="employeeTable" recordName="employee" useAnsiJoins="true">
    <fmt:bundle basename="com.smartgwt.sample.server.listener.DSXMLResources-utf8" encoding="utf-8" />
        <fields>
            <field name="EmployeeId" title="Employee ID" type="integer" primaryKey="true" required="true"/>
    
            <field name="Name" uploadFieldName="Name" title="Name" type="text" length="128">
                <validators>
                    <validator type="isUnique" caseSensitive="true" />
                </validators>
            </field>
    
            <field name="ReportsTo" uploadFieldName="ReportsTo" displayField="ReportsToName" required="true" importStrategy="display"
                foreignKey="employees.EmployeeId" relatedTableAlias="relatedReportsTo">
                <title><fmt:message key="ReportsTo" /></title>
                <validators>
                    <validator type="hasRelatedRecord" errorMessage="Unknown ReportsTo" />
                </validators>
            </field>
            <field name="ReportsToName" includeFrom="employees.Name" includeVia="ReportsTo" />
    
            <field name="Job" uploadFieldName="Job" title="Title" type="text" length="128">
                <validators>
                    <validator type="isOneOf" />
                </validators>
                <valueMap>
                    <value>Developer</value>
                    <value>IT-Infrastructure</value>
                    <value>Other</value>
                </valueMap>
            </field>
    
            <field name="Gender" uploadFieldName="Gender" title="Gender" type="text" length="7">
                <validators>
                    <validator type="isOneOf" />
                </validators>
                <valueMap>
                    <value>male</value>
                    <value>female</value>
                </valueMap>
            </field>
    
            <field name="Salary" uploadFieldName="Salary" title="Salary" type="integer">
            </field>
        </fields>
        <serverObject lookupStyle="new" className="com.smartgwt.sample.server.listener.EmployeesUpload" />
        <operationBindings>
            <operationBinding operationType="custom" operationId="upload" serverMethod="upload" />
        </operationBindings>
    </DataSource>
    DSXMLResources-utf8.properties:
    Code:
    ReportsTo = MyManager
    web.xml addition:
    Code:
        <servlet>
            <servlet-name>Upload</servlet-name>
            <servlet-class>com.smartgwt.sample.server.listener.Upload</servlet-class>
        </servlet>
        <servlet-mapping>
            <servlet-name>Upload</servlet-name>
            <url-pattern>/Upload</url-pattern>
        </servlet-mapping>
    EmployeesUpload.java (this will change for the different issues and I'll start different threads for this):
    Code:
    package com.smartgwt.sample.server.listener;
    
    import java.io.Reader;
    import java.io.StringReader;
    import java.io.StringWriter;
    import java.util.Map;
    
    import javax.servlet.http.HttpServletRequest;
    
    import org.json.simple.JSONObject;
    
    import com.isomorphic.datasource.DSRequest;
    import com.isomorphic.datasource.DSResponse;
    import com.isomorphic.tools.DataImport;
    import com.isomorphic.tools.DataImport.ImportFormat;
    
    public class EmployeesUpload {
        public DSResponse upload(DSRequest dsRequest, HttpServletRequest servletRequest) throws Exception {
            @SuppressWarnings("unchecked")
            Map<String, Object> requestValues = dsRequest.getValues();
            Reader jsonReader = new StringReader(convertToCSV(requestValues));
    
            DataImport dataImporter = new DataImport(ImportFormat.CSV, ";");
            dataImporter.setPopulateDisplayFields(true);
    
            long i = dataImporter.importToDataSource(jsonReader, "employeesUpload");
            if (i == 1)
                return new DSResponse(dsRequest.getDataSource()).setSuccess();
            else
                return new DSResponse(dsRequest.getDataSource()).setFailure("Not successfully imported");
        }
    
        private String convertToCSV(Map<String, Object> requestValues) {
            StringWriter csvHeader = new StringWriter();
            StringWriter csvData = new StringWriter();
            for (Map.Entry<String, Object> entry : requestValues.entrySet()) {
                String cleanKey = entry.getKey().replace(";", "").replace("\"", "");
                String cleanValue = entry.getValue().toString().replace(";", "").replace("\"", "");
                csvHeader.append(cleanKey + ";");
                csvData.append(cleanValue + ";");
            }
            return csvHeader.toString() + "\r\n" + csvData.toString();
        }
    }
    Sent ARC request (Advanced REST client 10.0.8-stable from the Chrome Web store):
    Code:
    POST to http://127.0.0.1:8888/builtinds/sc/RESTHandler
    
    Body:
    <request>
        <dataSource>employeesUpload</dataSource>
        <operationType>custom</operationType>
        <operationId>upload</operationId>
        <data>
            <EmployeeId>100001</EmployeeId>
              <Name>New colleague</Name>
            <ReportsTo>Charles Madigen</ReportsTo>
            <Job>Developer</Job>
            <Gender>male</Gender>
            <Salary>12345</Salary>
          </data>
      </request>
    Response:
    Code:
    <response>
    <status>
    -1
    </status>
    <queueStatus>
    -1
    </queueStatus>
    <data>
    Not successfully imported
    </data>
     </response>
    Server log:
    Code:
    === 2017-11-08 21:18:08,342 [7-31] INFO  RequestContext - URL: '/builtinds/sc/RESTHandler', User-Agent: 'null': Unsupported WITHOUT Accept-Encoding header
    === 2017-11-08 21:18:08,343 [7-31] DEBUG RESTHandler - Defaulting response data format to xml
    === 2017-11-08 21:18:08,343 [7-31] DEBUG RestRequestParser - Parsing xml object: '<request>
        <dataSource>employeesUpload</dataSource>
        <operationType>custom</operationType>
        <operationId>upload</operationId>
        <data>
            <EmployeeId>100001</EmployeeId>
              <Name>New colleague</Name>
            <ReportsTo>Charles Madigen</ReportsTo>
            <Job>Developer</Job>
            <Gender>male</Gender>
            <Salary>12345</Salary>
          </data>
      </request>'
    === 2017-11-08 21:18:08,345 [7-31] DEBUG XML - Parsed XML from (in memory stream): 2ms
    === 2017-11-08 21:18:08,347 [7-31] INFO  RESTHandler - Performing 1 operation(s)
    === 2017-11-08 21:18:08,347 [7-31] DEBUG DeclarativeSecurity - Processing security checks for DataSource null, field null
    === 2017-11-08 21:18:08,347 [7-31] DEBUG DeclarativeSecurity - DataSource employeesUpload is not in the pre-checked list, processing...
    === 2017-11-08 21:18:08,348 [7-31] DEBUG LocaleMessage - Returning Properties from cache for props file com.smartgwt.sample.server.listener.DSXMLResources-utf8_de_DE
    === 2017-11-08 21:18:08,348 [7-31] DEBUG LocaleMessage - Returning Properties from cache for props file com.smartgwt.sample.server.listener.DSXMLResources-utf8_de_DE
    === 2017-11-08 21:18:08,350 [7-31] DEBUG DeclarativeSecurity - Processing security checks for DataSource null, field null
    === 2017-11-08 21:18:08,350 [7-31] DEBUG DeclarativeSecurity - Request is not a client request, ignoring security checks.
    === 2017-11-08 21:18:08,351 [7-31] DEBUG DeclarativeSecurity - Processing security checks for DataSource null, field null
    === 2017-11-08 21:18:08,351 [7-31] DEBUG DeclarativeSecurity - Request is not a client request, ignoring security checks.
    === 2017-11-08 21:18:08,351 [7-31] DEBUG AppBase - [builtinApplication.null] No userTypes defined, allowing anyone access to all operations for this application
    === 2017-11-08 21:18:08,351 [7-31] DEBUG AppBase - [builtinApplication.null] No public zero-argument method named '_null' found, performing generic datasource operation
    === 2017-11-08 21:18:08,351 [7-31] INFO  SQLDataSource - [builtinApplication.null] Performing fetch operation with
        criteria: {EmployeeId:"Charles Madigen"}    values: {EmployeeId:"Charles Madigen"}
    === 2017-11-08 21:18:08,352 [7-31] DEBUG SQLDataSource - [builtinApplication.null] DataSource 51 acquired SQLDriver instance 1994064315 during initialization
    === 2017-11-08 21:18:08,352 [7-31] WARN  SQLWhereClause - [builtinApplication.null] Got non-numeric value 'Charles Madigen' for numeric column 'EmployeeId', creating literal false expression: java.lang.NumberFormatException: For input string: "Charle"
    === 2017-11-08 21:18:08,352 [7-31] INFO  SQLDataSource - [builtinApplication.null] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause
    === 2017-11-08 21:18:08,353 [7-31] INFO  SQLDataSource - [builtinApplication.null] 51: Executing SQL query on 'HSQLDB': SELECT employeeTable.userOrder, employeeTable.Name, employeeTable.EmployeeId, employeeTable.ReportsTo, employeeTable.Job, employeeTable.Email, employeeTable.EmployeeType, employeeTable.EmployeeStatus, employeeTable.Salary, employeeTable.OrgUnit, employeeTable.Gender, employeeTable.MaritalStatus FROM employeeTable WHERE ('0'='1')
    === 2017-11-08 21:18:08,353 [7-31] DEBUG SQLConnectionManager - [builtinApplication.null] Borrowed connection '998793675'
    === 2017-11-08 21:18:08,353 [7-31] INFO  SQLDriver - [builtinApplication.null] Executing SQL query on 'HSQLDB' using connection '998793675': SELECT employeeTable.userOrder, employeeTable.Name, employeeTable.EmployeeId, employeeTable.ReportsTo, employeeTable.Job, employeeTable.Email, employeeTable.EmployeeType, employeeTable.EmployeeStatus, employeeTable.Salary, employeeTable.OrgUnit, employeeTable.Gender, employeeTable.MaritalStatus FROM employeeTable WHERE ('0'='1')
    === 2017-11-08 21:18:08,354 [7-31] INFO  DSResponse - DSResponse: List with 0 items
    === 2017-11-08 21:18:08,354 [7-31] DEBUG SQLDataSource - About to clear SQLDriver state for DS instance 51
    === 2017-11-08 21:18:08,354 [7-31] DEBUG SQLDriver - Freeing SQLDriver dbConnection 998793675 for SQLDriver instance 1994064315
    === 2017-11-08 21:18:08,354 [7-31] DEBUG SQLConnectionManager - About to close connection with hashcode "998793675"
    === 2017-11-08 21:18:08,354 [7-31] DEBUG SQLDataSource - About to clear SQLDriver state for DS instance 51
    === 2017-11-08 21:18:08,354 [7-31] DEBUG SQLDataSource - About to clear SQLDriver state for DS instance 51
    === 2017-11-08 21:18:08,354 [7-31] DEBUG ValidationContext - Adding validation errors at path '/employeesUpload/ReportsTo/ReportsTo': {errorMessage=Unknown ReportsTo}
    === 2017-11-08 21:18:08,355 [7-31] INFO  Validation - Validation error: [
        {
            ReportsTo:{
                errorMessage:"Unknown ReportsTo"
            }
        }
    ]
    === 2017-11-08 21:18:08,355 [7-31] DEBUG DataSourceDMI - Freeing resources in DataSourceDMI...
    === 2017-11-08 21:18:08,355 [7-31] DEBUG DataSourceDMI - Freeing QueueResources in DataSourceDMI
    === 2017-11-08 21:18:08,355 [7-31] DEBUG DataSourceDMI - Freeing resources in DataSourceDMI...
    === 2017-11-08 21:18:08,355 [7-31] DEBUG RPCManager - Content type for RPC transaction: text/html; charset=UTF-8
    === 2017-11-08 21:18:08,355 [7-31] DEBUG RPCManager - DMI response, dropExtraFields: false
    === 2017-11-08 21:18:08,356 [7-31] WARN  RequestContext - Content type has already been set to: text/html; charset=UTF-8 - setting to: text/xml
    === 2017-11-08 21:18:08,356 [7-31] DEBUG DataSourceDMI - Freeing QueueResources in DataSourceDMI
    As you can see, importToDataSource() does not use setPopulateDisplayFields(true), even though it is set. This does work for importDataSourceRecords().

    Best regards
    Blama
    Last edited by Blama; 8th Nov 2017, 13:02. Reason: web.xml change added to thread

  • Blama
    replied
    Hi Isomorphic,

    thanks, this seems to be working for me using v12.0p_2019-02-24.

    Best regards
    Blama

    Leave a comment:


  • Blama
    replied
    Hi Isomorphic,

    thanks a lot. I'll report back once I have the time to change my code.

    Best regards
    Blama

    Leave a comment:


  • Isomorphic
    replied
    ImportDataSourceRecord(Map,...) and ImportToDataSource(Map,...) APIs are added and available in recent nightly builds.

    Leave a comment:


  • Isomorphic
    replied
    So, we are in sync ) Everything you wrote makes perfect sense and your assumption about escaping is correct - in order to use linefeed inside field value (read "inside a data row") you need to escape backslashes with "\\r". Yet it is simple to do, allowing Map would be a proper way and we are doing it, as mentioned earlier. Till then you may escape backslashes or use another import format.

    Leave a comment:


  • Blama
    replied
    FYI: Edited the post. As meta problem it seems that phpBB (or whatever runs the forum) has problems with \\ (backslashes) in data, at least when editing posts :)

    Leave a comment:


  • Blama
    replied
    Hi Isomorphic,

    answering in different order:
    Originally posted by Isomorphic View Post
    Regarding #3.2 (providing record as a Map instead of Reader to DataImport): do you expect that if we implement it, then this problem would automatically go away?
    Yes, expecting it to go away, because there is no escaping whatsoever, but only Java Map and no escaping or CSV/JSON/XML encode on my side and no CSV/JSON/XML decode on your side needed.

    Originally posted by Isomorphic View Post
    Are you talking about linefeeds inside field values? And the way DataImport is not working is that it treats those as new lines, which splits data into rows incorrectly and fails, right? We expect such linefeeds to be escaped. Also, do you get these values from some kind of tool? If so, we'd like to know what it is. Thank you.
    Yes, linefeeds in the field values. Data is self generated. I have a (pretty dumbed down, JSON would be better, but (perhaps) more difficult to generate for websites with forms) mail format that I import:
    Code:
    fieldname: value1;
    fieldname2: value2;
    fieldname3: value3;
    (with the requirement of no ; and : in the data). But for ease of mind, lets just assume it were JSON.
    I then build this from it:
    Code:
    fieldname;fieldname2;fieldname3
    "value1";"value2";"value3"
    and feed this to DataImport as CSV. DataImport is needed, as some of the fields need to go though DataImport's fK-value ->ID conversion. Otherwise I'd just use plain ADD.
    As you can see, I do have "value1" with quotes, which is what you mean by "We expect such linefeeds to be escaped" right?

    So an 1-row import with linefeeds could look like this:
    Code:
    fieldname;fieldname2;fieldname3
    "value1";"Some long data
    in
    field 2";"value3"
    .

    Or do you mean I should generated this instead by "We expect such linefeeds to be escaped"
    Code:
    fieldname;fieldname2;fieldname3
    "value1";"Some long data\r\nin\r\nfield 2";"value3"
    , so that it is always exactly 1 CSV row per data row?

    Then I'd also might have problems with "\" (escape it to "\\"?).
    This would also work for me for now and the change is easy on my side. But the correct way is using Java Map IMHO. Of course I could also build JSON or XML instead of CSV from the data, which is probably better, now that I think about it, but still, Java Map would be the one with basically no code needed.

    Best regards
    Blama
    Last edited by Blama; 18th Oct 2018, 02:12.

    Leave a comment:


  • Isomorphic
    replied
    Just to clarify if we understand #12 correctly. Please answer following questions.

    Are you talking about linefeeds inside field values? And the way DataImport is not working is that it treats those as new lines, which splits data into rows incorrectly and fails, right? We expect such linefeeds to be escaped. Also, do you get these values from some kind of tool? If so, we'd like to know what it is. Thank you.

    Regarding #3.2 (providing record as a Map instead of Reader to DataImport): do you expect that if we implement it, then this problem would automatically go away? Sorry for delay, we *are* going to implement this and will update this thread when we do.

    Leave a comment:


  • Blama
    replied
    Hi Isomorphic,

    #3 point 2 is indeed necessary, more a bug than an enhancement if you want to upload data that includes linefeeds (\r, \n, \r\n). If you do this now like convertToCSV() in #3 this will fail if you have linefeeds (because it seems like a new data row in the CSV). This is the one where you write "in progress" in #10.
    Linefeeds are perfectly fine in data IMHO and must be converted to <br> at display time if you want to respect them in a label. This is also working as expected when entering them in a TextAreaItem. But they don't work if you need to use DataImport.

    Best regards
    Blama

    Leave a comment:


  • Blama
    replied
    Hi Isomorphic,

    thanks. I can see that #3.1 is fixed and also the advice for #3.3 in the SmartGWT docs to look here.

    I'll wait for an update of #3.2 and perhaps #3.4 then.

    Best regards
    Blama

    Leave a comment:


  • Isomorphic
    replied
    Apologies for the delay, we are addressing issues you listed in #3 (same order as in your post):
    • fixed
    • this suggestion is in progress and will be reported here when it's done
    • we've added XML and JSON formats to the DataImport docs with the reference to format descriptions and samples in Smartclient Reference
    • taken into consideration

    Leave a comment:


  • Blama
    replied
    Hi Isomorphic,

    I can see that #1 and #2 are fixed using v11.1p_2017-12-27. Do you have any further information on #3?

    Thank you & Best regards
    Blama

    Leave a comment:


  • Isomorphic
    replied
    Issues #1 and #2 are fixed and are available for download in nightly builds since Dec 15 (today). Issues from #3 are in progress, thank you for your patience.

    Leave a comment:


  • Isomorphic
    replied
    No updates yet, they will be posted here when we have them.

    Leave a comment:


  • Blama
    replied
    Hi Isomorphic,

    do you have an update on this one?

    Thank you & Best regards
    Blama

    Leave a comment:

Working...
X