[JBoss JIRA] (TEIID-5919) Salesforce: Insert fails in case of tables with salesforce standard objects
by Steven Hawkins (Jira)
[ https://issues.redhat.com/browse/TEIID-5919?page=com.atlassian.jira.plugi... ]
Steven Hawkins updated TEIID-5919:
----------------------------------
Component/s: Query Engine
Original Estimate: 2 hours
Remaining Estimate: 2 hours
Story Points: 0.5
Sprint: DV Sprint 61
Workaround Description:
Add ddl to the vdb to alter the affected columns to be auto_increment:
alter table contacts alter column name string not null auto_increment;
Workaround: Workaround Exists
Priority: Major (was: Critical)
The importer looks for this case only on key columns currently.
However since the column is marked as non-updatable, we could also look at making this change in the engine as part of the insert validation.
> Salesforce: Insert fails in case of tables with salesforce standard objects
> ---------------------------------------------------------------------------
>
> Key: TEIID-5919
> URL: https://issues.redhat.com/browse/TEIID-5919
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine, Salesforce Connector
> Affects Versions: 13.1
> Reporter: Dmitrii Pogorelov
> Assignee: Steven Hawkins
> Priority: Major
> Fix For: 14.0, 13.0.3, 13.1.1
>
> Original Estimate: 2 hours
> Remaining Estimate: 2 hours
>
> An insert fails in the case of a Salesforce table with standard objects. For example, the Contact table (https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api...) in salesforce contains Name standard object: https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api.... The field can't be null, doesn't support create(), insert() and so on operations but should be filled automatically by salesforce. As a result when running the following query:
> {code:sql}
> INSERT INTO sf34.Contact
> ("LastName", "FirstName", "Salutation", "MailingStreet", "MailingCity", "MailingPostalCode", "MailingCountry")
> VALUES
> ('Hillrichsen', 'Suse', 'Mrs.', 'Frankfurter Allee 29', 'Berlin', '10247','Deutschland') ;;
> {code}
> Teiid generates the following error:
> {code}
> 2020-03-16 16:35:15,105 WARN [org.teiid.PROCESSOR] (Worker3_QueryProcessorQueue50) ij6teT/JNA+c TEIID30020 Processing exception for request ij6teT/JNA+c.18 'TEIID30492 Element sf34.Contact.Name of sf34.Contact is neither nullable nor has a default value. A value must be specified in the insert.'. Originally QueryValidatorException Request.java:343. Enable more detailed logging to see the entire stacktrace.
> {code}
> The problem can be solved by a native call, for example:
> {code:sql}
> SELECT * FROM (call "sf34.native"('create;type=Contact;attributes=LastName,FirstName,Salutation,MailingStreet,MailingCity,MailingPostalCode,MailingCountry','Erich','Honecker','Herr','Magdalenenstr. 99','Berlin','11799','DE')) lpk ;;
> {code}
> but it won't work with dates/timestamps types (no sample request available).
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
6 years
[JBoss JIRA] (TEIID-5918) SQL Server uniqueidentifier field type: Conversion and concat errors, incorrect lower case comparisons
by Steven Hawkins (Jira)
[ https://issues.redhat.com/browse/TEIID-5918?page=com.atlassian.jira.plugi... ]
Steven Hawkins updated TEIID-5918:
----------------------------------
Fix Version/s: 14.0
> SQL Server uniqueidentifier field type: Conversion and concat errors, incorrect lower case comparisons
> ------------------------------------------------------------------------------------------------------
>
> Key: TEIID-5918
> URL: https://issues.redhat.com/browse/TEIID-5918
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 13.1
> Reporter: Dmitrii Pogorelov
> Assignee: Steven Hawkins
> Priority: Blocker
> Fix For: 14.0
>
>
> 1. when running the following queries:
> {code:sql}
> -- Check that the following queries don't fail and return correct results:
> -- Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 test_tables_ms: S0002 Unexpected exception while translating results: Conversion failed when converting from a character string to uniqueidentifier.
> select * from ms.test_uid where test_id != content ;;
> -- Should return all 16 rows
> -- Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 test_tables_ms: S0002 Unexpected exception while translating results: Conversion failed when converting from a character string to uniqueidentifier.
> select * from ms.test_uid t1 join ms.test_uid t2 on t1.test_id != t2.content ;;
> -- Should return 1 row
> -- Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 test_tables_ms: S0002 Unexpected exception while translating results: Conversion failed when converting from a character string to uniqueidentifier.
> select * from ms.test_uid where test_id in ('B279DD00-1B57-4567-ACA8-22D2693124F2', 'a', 'b') ;;
> {code}
> Teiid throws out the following error:
> {code}
> 2020-03-16 14:57:08,314 WARN [org.teiid.CONNECTOR] (Worker6_QueryProcessorQueue68) B9oE6Jwiki7f Connector worker process failed for atomic-request=B9oE6Jwiki7f.23.0.12: org.teiid.translator.TranslatorException: S0002 Unexpected exception while translating results: Conversion failed when converting from a character string to uniqueidentifier. at org.teiid.translator.jdbc.JDBCQueryExecution.next(JDBCQueryExecution.java:348) at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.handleBatch(ConnectorWorkItem.java:475) at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.more(ConnectorWorkItem.java:261) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:228) at com.sun.proxy.$Proxy43.more(Unknown Source) at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:305) at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108) at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:104) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:59) at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:281) at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:124) at org.teiid.dqp.internal.process.ThreadReuseExecutor$2.run(ThreadReuseExecutor.java:212) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting from a character string to uniqueidentifier. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216) at com.microsoft.sqlserver.jdbc.SQLServerResultSet$FetchBuffer.nextRow(SQLServerResultSet.java:4853) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.fetchBufferNext(SQLServerResultSet.java:1781) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:1034) at org.jboss.jca.adapters.jdbc.WrappedResultSet.next(WrappedResultSet.java:2689) at org.teiid.translator.jdbc.JDBCQueryExecution.next(JDBCQueryExecution.java:334) ... 19 more
> {code}
> 2. when running the following queries:
> {code:sql}
> -- Check if concatenation of strings works correctly with uniqueidentifier column.
> -- Should return 4 rows anding with lowercased 'a'
> -- Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 test_tables_ms: 402 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT (g_0."test_id" + 'a') AS c_0 FROM "test_tables"."dbo"."test_uid" g_0 ORDER BY @@version OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY]
> select test_id || 'a' from ms.test_uid ;;
> {code}
> Teiid throws out the following error:
> {code}
> 2020-03-16 14:59:57,516 WARN [org.teiid.CONNECTOR] (Worker8_QueryProcessorQueue80) B9oE6Jwiki7f Connector worker process failed for atomic-request=B9oE6Jwiki7f.27.0.16: org.teiid.translator.jdbc.JDBCExecutionException: 402 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT (g_0."test_id" + 'a') AS c_0 FROM "test_tables"."dbo"."test_uid" g_0 ORDER BY @@version OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY] at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:127) at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:402) at sun.reflect.GeneratedMethodAccessor106.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:228) at com.sun.proxy.$Proxy43.execute(Unknown Source) at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302) at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108) at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:104) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:59) at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:281) at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:124) at org.teiid.dqp.internal.process.ThreadReuseExecutor$2.run(ThreadReuseExecutor.java:212) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The data types uniqueidentifier and varchar are incompatible in the add operator. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285) at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:119) ... 17 more
> {code}
> 3. when running the following queries:
> {code:sql}
> -- Should return 0 rows
> -- FAILURE(result 1 result, lower case comparison)
> select * from ms.test_uid t1 join ms.test_uid t2 on t1.test_id = t2.test_id where t2.test_id = 'b279dd00-1b57-4567-aca8-22d2693124f2' ;;
> -- Should return 0 rows
> -- FAILURE(result 1 result, lower case comparison)
> select * from ms.test_uid where test_id = 'b279dd00-1b57-4567-aca8-22d2693124f2' ;;
> -- Should return 0 rows
> -- CORRECT
> select * from ms.test_uid where cast(cast(test_id as clob) as string) = 'b279dd00-1b57-4567-aca8-22d2693124f2' ;;
> {code}
> Teiid returns incorrect results.
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
6 years
[JBoss JIRA] (TEIID-5919) Salesforce: Insert fails in case of tables with salesforce standard objects
by Steven Hawkins (Jira)
[ https://issues.redhat.com/browse/TEIID-5919?page=com.atlassian.jira.plugi... ]
Steven Hawkins updated TEIID-5919:
----------------------------------
Component/s: Salesforce Connector
(was: Query Engine)
Fix Version/s: 13.1.1
14.0
13.0.3
Priority: Critical (was: Blocker)
> Salesforce: Insert fails in case of tables with salesforce standard objects
> ---------------------------------------------------------------------------
>
> Key: TEIID-5919
> URL: https://issues.redhat.com/browse/TEIID-5919
> Project: Teiid
> Issue Type: Bug
> Components: Salesforce Connector
> Affects Versions: 13.1
> Reporter: Dmitrii Pogorelov
> Assignee: Steven Hawkins
> Priority: Critical
> Fix For: 14.0, 13.0.3, 13.1.1
>
>
> An insert fails in the case of a Salesforce table with standard objects. For example, the Contact table (https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api...) in salesforce contains Name standard object: https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api.... The field can't be null, doesn't support create(), insert() and so on operations but should be filled automatically by salesforce. As a result when running the following query:
> {code:sql}
> INSERT INTO sf34.Contact
> ("LastName", "FirstName", "Salutation", "MailingStreet", "MailingCity", "MailingPostalCode", "MailingCountry")
> VALUES
> ('Hillrichsen', 'Suse', 'Mrs.', 'Frankfurter Allee 29', 'Berlin', '10247','Deutschland') ;;
> {code}
> Teiid generates the following error:
> {code}
> 2020-03-16 16:35:15,105 WARN [org.teiid.PROCESSOR] (Worker3_QueryProcessorQueue50) ij6teT/JNA+c TEIID30020 Processing exception for request ij6teT/JNA+c.18 'TEIID30492 Element sf34.Contact.Name of sf34.Contact is neither nullable nor has a default value. A value must be specified in the insert.'. Originally QueryValidatorException Request.java:343. Enable more detailed logging to see the entire stacktrace.
> {code}
> The problem can be solved by a native call, for example:
> {code:sql}
> SELECT * FROM (call "sf34.native"('create;type=Contact;attributes=LastName,FirstName,Salutation,MailingStreet,MailingCity,MailingPostalCode,MailingCountry','Erich','Honecker','Herr','Magdalenenstr. 99','Berlin','11799','DE')) lpk ;;
> {code}
> but it won't work with dates/timestamps types (no sample request available).
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
6 years
[JBoss JIRA] (TEIID-5919) Salesforce: Insert fails in case of tables with salesforce standard objects
by Dmitrii Pogorelov (Jira)
Dmitrii Pogorelov created TEIID-5919:
----------------------------------------
Summary: Salesforce: Insert fails in case of tables with salesforce standard objects
Key: TEIID-5919
URL: https://issues.redhat.com/browse/TEIID-5919
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 13.1
Reporter: Dmitrii Pogorelov
Assignee: Steven Hawkins
An insert fails in the case of a Salesforce table with standard objects. For example, the Contact table (https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api...) in salesforce contains Name standard object: https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api.... The field can't be null, doesn't support create(), insert() and so on operations but should be filled automatically by salesforce. As a result when running the following query:
{code:sql}
INSERT INTO sf34.Contact
("LastName", "FirstName", "Salutation", "MailingStreet", "MailingCity", "MailingPostalCode", "MailingCountry")
VALUES
('Hillrichsen', 'Suse', 'Mrs.', 'Frankfurter Allee 29', 'Berlin', '10247','Deutschland') ;;
{code}
Teiid generates the following error:
{code}
2020-03-16 16:35:15,105 WARN [org.teiid.PROCESSOR] (Worker3_QueryProcessorQueue50) ij6teT/JNA+c TEIID30020 Processing exception for request ij6teT/JNA+c.18 'TEIID30492 Element sf34.Contact.Name of sf34.Contact is neither nullable nor has a default value. A value must be specified in the insert.'. Originally QueryValidatorException Request.java:343. Enable more detailed logging to see the entire stacktrace.
{code}
The problem can be solved by a native call, for example:
{code:sql}
SELECT * FROM (call "sf34.native"('create;type=Contact;attributes=LastName,FirstName,Salutation,MailingStreet,MailingCity,MailingPostalCode,MailingCountry','Erich','Honecker','Herr','Magdalenenstr. 99','Berlin','11799','DE')) lpk ;;
{code}
but it won't work with dates/timestamps types (no sample request available).
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
6 years
[JBoss JIRA] (TEIID-2941) Add documentation that explains expected usage patterns
by Steven Hawkins (Jira)
[ https://issues.redhat.com/browse/TEIID-2941?page=com.atlassian.jira.plugi... ]
Steven Hawkins resolved TEIID-2941.
-----------------------------------
Resolution: Out of Date
> Add documentation that explains expected usage patterns
> -------------------------------------------------------
>
> Key: TEIID-2941
> URL: https://issues.redhat.com/browse/TEIID-2941
> Project: Teiid
> Issue Type: Enhancement
> Components: Documentation
> Reporter: Van Halbert
> Priority: Optional
>
> Would like to see documentation added to explains certain data source usage patterns for which cannot be explained just by using the data source. Example is:
> How to access a JSON REST service:
> You read JSON using file or web service translator, then use the JSONToXML function, to convert to XML, then use the XMLTABLE function to parse.
> This isn't straight forward in the documentation to a novice.
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
6 years