[JBoss JIRA] (TEIID-5398) Sybase translator error caused by change in ASCII function pushdown
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5398?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5398:
---------------------------------------
Based upon testing against the sybase 16 docker image and seeing similar questions online - it does not appear that uscalar behaves as expected. The change here will be to make the unicode function alias specific to sql server and remove the alias from sybase.
That leaves things with the ascii function in a fairly inconsistent state. Teiid/JDV has always returned the USC2/UTF16 value for the first char from the ASCII function (similar to H2). The previous change was attempting to match the Teiid behavior by making the source functions respect unicode strings. However for sources like mysql and sybase that functionality seems unavailable. The most consistency would be to change the Teiid behavior of ascii and char functions to ascii characters only and corresponding pushdown changes - but that could break existing usage. So for now we should probably just make things as consistent as possible and document that not all sources support unicode with the ascii and char functions.
> Sybase translator error caused by change in ASCII function pushdown
> -------------------------------------------------------------------
>
> Key: TEIID-5398
> URL: https://issues.jboss.org/browse/TEIID-5398
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.14.6_4
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
> Priority: Critical
> Fix For: 11.0
>
>
> There's a regression caused by TEIID-5313 change:
> {code:java}
> registerFunctionModifier(SourceSystemFunctions.ASCII, new AliasModifier("unicode")); //$NON-NLS-1$
> {code}
> Following query:
> {code:sql}
> SELECT INTKEY, ASCII(INTKEY) FROM BQT1.SmallA
> {code}
> being resolved to following src command:
> {code:sql}
> SELECT g_0.intkey, unicode(cast(g_0.intkey AS varchar(4000))) FROM smalla g_0
> {code}
> results in following error:
> {code:title=server.log}
> 12:42:04,102 WARN [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue18) Connector worker process failed for atomic-request=reZs6o4Ybhhc.8.0.1: org.teiid.translator.jdbc.JDBCExecutionException: 14216 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.intkey, unicode(cast(g_0.intkey AS varchar(4000))) FROM smalla g_0]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.14.6_4-redhat-64-2.jar:8.12.14.6_4-redhat-64-2]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:361)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.8.0_151]
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [rt.jar:1.8.0_151]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_151]
> at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_151]
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy79.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at java.util.concurrent.FutureTask.run(FutureTask.java:266) [rt.jar:1.8.0_151]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:284)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [rt.jar:1.8.0_151]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [rt.jar:1.8.0_151]
> at java.lang.Thread.run(Thread.java:748) [rt.jar:1.8.0_151]
> Caused by: com.sybase.jdbc4.jdbc.SybSQLException: Function 'unicode' not found. If this is a SQLJ function or SQL function, use sp_help to check whether the object exists (sp_help may produce a large amount of output).
> at com.sybase.jdbc4.tds.Tds.processEed(Tds.java:4003)
> at com.sybase.jdbc4.tds.Tds.nextResult(Tds.java:3093)
> at com.sybase.jdbc4.jdbc.ResultGetter.nextResult(ResultGetter.java:78)
> at com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:289)
> at com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:271)
> at com.sybase.jdbc4.jdbc.SybStatement.queryLoop(SybStatement.java:2408)
> at com.sybase.jdbc4.jdbc.SybStatement.executeQuery(SybStatement.java:2394)
> at com.sybase.jdbc4.jdbc.SybPreparedStatement.executeQuery(SybPreparedStatement.java:257)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123) [translator-jdbc-8.12.14.6_4-redhat-64-2.jar:8.12.14.6_4-redhat-64-2]
> ... 18 more
> 12:42:04,118 WARN [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue19) TEIID30020 Processing exception for request reZs6o4Ybhhc.8 'TEIID30504 Source: 14216 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.intkey, unicode(cast(g_0.intkey AS varchar(4000))) FROM smalla g_0]'. Originally TeiidProcessingException 'Function 'unicode' not found. If this is a SQLJ function or SQL function, use sp_help to check whether the object exists (sp_help may produce a large amount of output).
> ' Tds.java:4003. Enable more detailed logging to see the entire stacktrace.
> {code}
> SRC Command in previous version:
> {code:sql}
> SELECT g_0.intkey, ascii(cast(g_0.intkey AS varchar(4000))) FROM smalla g_0
> {code}
> NOTE: the issue is not caused by casting int to varchar, happens also when calling ASCII function on string directly.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-5398) Sybase translator error caused by change in ASCII function pushdown
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5398?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-5398:
----------------------------------
Attachment: (was: java)
> Sybase translator error caused by change in ASCII function pushdown
> -------------------------------------------------------------------
>
> Key: TEIID-5398
> URL: https://issues.jboss.org/browse/TEIID-5398
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.14.6_4
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
> Priority: Critical
> Fix For: 11.0
>
>
> There's a regression caused by TEIID-5313 change:
> {code:java}
> registerFunctionModifier(SourceSystemFunctions.ASCII, new AliasModifier("unicode")); //$NON-NLS-1$
> {code}
> Following query:
> {code:sql}
> SELECT INTKEY, ASCII(INTKEY) FROM BQT1.SmallA
> {code}
> being resolved to following src command:
> {code:sql}
> SELECT g_0.intkey, unicode(cast(g_0.intkey AS varchar(4000))) FROM smalla g_0
> {code}
> results in following error:
> {code:title=server.log}
> 12:42:04,102 WARN [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue18) Connector worker process failed for atomic-request=reZs6o4Ybhhc.8.0.1: org.teiid.translator.jdbc.JDBCExecutionException: 14216 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.intkey, unicode(cast(g_0.intkey AS varchar(4000))) FROM smalla g_0]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.14.6_4-redhat-64-2.jar:8.12.14.6_4-redhat-64-2]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:361)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.8.0_151]
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [rt.jar:1.8.0_151]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_151]
> at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_151]
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy79.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at java.util.concurrent.FutureTask.run(FutureTask.java:266) [rt.jar:1.8.0_151]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:284)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [rt.jar:1.8.0_151]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [rt.jar:1.8.0_151]
> at java.lang.Thread.run(Thread.java:748) [rt.jar:1.8.0_151]
> Caused by: com.sybase.jdbc4.jdbc.SybSQLException: Function 'unicode' not found. If this is a SQLJ function or SQL function, use sp_help to check whether the object exists (sp_help may produce a large amount of output).
> at com.sybase.jdbc4.tds.Tds.processEed(Tds.java:4003)
> at com.sybase.jdbc4.tds.Tds.nextResult(Tds.java:3093)
> at com.sybase.jdbc4.jdbc.ResultGetter.nextResult(ResultGetter.java:78)
> at com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:289)
> at com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:271)
> at com.sybase.jdbc4.jdbc.SybStatement.queryLoop(SybStatement.java:2408)
> at com.sybase.jdbc4.jdbc.SybStatement.executeQuery(SybStatement.java:2394)
> at com.sybase.jdbc4.jdbc.SybPreparedStatement.executeQuery(SybPreparedStatement.java:257)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123) [translator-jdbc-8.12.14.6_4-redhat-64-2.jar:8.12.14.6_4-redhat-64-2]
> ... 18 more
> 12:42:04,118 WARN [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue19) TEIID30020 Processing exception for request reZs6o4Ybhhc.8 'TEIID30504 Source: 14216 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.intkey, unicode(cast(g_0.intkey AS varchar(4000))) FROM smalla g_0]'. Originally TeiidProcessingException 'Function 'unicode' not found. If this is a SQLJ function or SQL function, use sp_help to check whether the object exists (sp_help may produce a large amount of output).
> ' Tds.java:4003. Enable more detailed logging to see the entire stacktrace.
> {code}
> SRC Command in previous version:
> {code:sql}
> SELECT g_0.intkey, ascii(cast(g_0.intkey AS varchar(4000))) FROM smalla g_0
> {code}
> NOTE: the issue is not caused by casting int to varchar, happens also when calling ASCII function on string directly.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-5398) Sybase translator error caused by change in ASCII function pushdown
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5398?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-5398:
----------------------------------
Attachment: java
> Sybase translator error caused by change in ASCII function pushdown
> -------------------------------------------------------------------
>
> Key: TEIID-5398
> URL: https://issues.jboss.org/browse/TEIID-5398
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.14.6_4
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
> Priority: Critical
> Fix For: 11.0
>
> Attachments: java
>
>
> There's a regression caused by TEIID-5313 change:
> {code:java}
> registerFunctionModifier(SourceSystemFunctions.ASCII, new AliasModifier("unicode")); //$NON-NLS-1$
> {code}
> Following query:
> {code:sql}
> SELECT INTKEY, ASCII(INTKEY) FROM BQT1.SmallA
> {code}
> being resolved to following src command:
> {code:sql}
> SELECT g_0.intkey, unicode(cast(g_0.intkey AS varchar(4000))) FROM smalla g_0
> {code}
> results in following error:
> {code:title=server.log}
> 12:42:04,102 WARN [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue18) Connector worker process failed for atomic-request=reZs6o4Ybhhc.8.0.1: org.teiid.translator.jdbc.JDBCExecutionException: 14216 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.intkey, unicode(cast(g_0.intkey AS varchar(4000))) FROM smalla g_0]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.14.6_4-redhat-64-2.jar:8.12.14.6_4-redhat-64-2]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:361)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.8.0_151]
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [rt.jar:1.8.0_151]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_151]
> at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_151]
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy79.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at java.util.concurrent.FutureTask.run(FutureTask.java:266) [rt.jar:1.8.0_151]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:284)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [rt.jar:1.8.0_151]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [rt.jar:1.8.0_151]
> at java.lang.Thread.run(Thread.java:748) [rt.jar:1.8.0_151]
> Caused by: com.sybase.jdbc4.jdbc.SybSQLException: Function 'unicode' not found. If this is a SQLJ function or SQL function, use sp_help to check whether the object exists (sp_help may produce a large amount of output).
> at com.sybase.jdbc4.tds.Tds.processEed(Tds.java:4003)
> at com.sybase.jdbc4.tds.Tds.nextResult(Tds.java:3093)
> at com.sybase.jdbc4.jdbc.ResultGetter.nextResult(ResultGetter.java:78)
> at com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:289)
> at com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:271)
> at com.sybase.jdbc4.jdbc.SybStatement.queryLoop(SybStatement.java:2408)
> at com.sybase.jdbc4.jdbc.SybStatement.executeQuery(SybStatement.java:2394)
> at com.sybase.jdbc4.jdbc.SybPreparedStatement.executeQuery(SybPreparedStatement.java:257)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123) [translator-jdbc-8.12.14.6_4-redhat-64-2.jar:8.12.14.6_4-redhat-64-2]
> ... 18 more
> 12:42:04,118 WARN [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue19) TEIID30020 Processing exception for request reZs6o4Ybhhc.8 'TEIID30504 Source: 14216 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.intkey, unicode(cast(g_0.intkey AS varchar(4000))) FROM smalla g_0]'. Originally TeiidProcessingException 'Function 'unicode' not found. If this is a SQLJ function or SQL function, use sp_help to check whether the object exists (sp_help may produce a large amount of output).
> ' Tds.java:4003. Enable more detailed logging to see the entire stacktrace.
> {code}
> SRC Command in previous version:
> {code:sql}
> SELECT g_0.intkey, ascii(cast(g_0.intkey AS varchar(4000))) FROM smalla g_0
> {code}
> NOTE: the issue is not caused by casting int to varchar, happens also when calling ASCII function on string directly.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-5396) Querying SQL Server variant type for a string fails
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-5396?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration updated TEIID-5396:
-------------------------------------------
Bugzilla References: https://bugzilla.redhat.com/show_bug.cgi?id=1595185
Bugzilla Update: Perform
> Querying SQL Server variant type for a string fails
> ---------------------------------------------------
>
> Key: TEIID-5396
> URL: https://issues.jboss.org/browse/TEIID-5396
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.12.14.6_4
> Reporter: Andrej Šmigala
> Assignee: Steven Hawkins
> Priority: Critical
> Fix For: 11.0
>
>
> Table on SQL Server source created as
> {code:sql}CREATE TABLE table_with_variant(id INT, var SQL_VARIANT){code}
> and loaded with int, float and string data.
> Running the following query against a dynamic VDB with metadata import
> {code:sql}SELECT cast(var AS string) FROM table_with_variant WHERE id = 1{code}
> fails with
> {noformat}java.sql.SQLException: Remote com.microsoft.sqlserver.jdbc.SQLServerException: The "variant" data type is not supported.{noformat}
> This query used to work with DV 6.4.2, it appears it is a further regression from TEIID-5313 . Querying rows with non-string data works as expected.
> SOURCE SRC command with 6.4.2 (working):
> {code:sql}SELECT cast(g_0."var" AS varchar(4000)) FROM "dballo05"."dbo"."table_with_variant" g_0 WHERE g_0."id" = 1{code}
> SOURCE SRC command with 6.4.3 (not working), note missing cast to varchar:
> {code:sql}SELECT g_0."var" FROM "dballo06"."dbo"."table_with_variant" g_0 WHERE g_0."id" = 1{code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-5397) RETURN_GENERATED_KEYS not working
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-5397?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-5397:
-------------------------------------
You need to define "id" column on "_view_workflow_data" as "AUTO_INCREMENT", and whatever column you are backing up with the "id" (here "public"."form_shoes_2"."id") must also be defined with autogeneration.
If the source does not support the autogeneration, then you can write a function in the view layer, that does the sequence generation and use that in "instead of trigger" code for insert of that view to insert the value of "id" when "id" is not provided.
> RETURN_GENERATED_KEYS not working
> ---------------------------------
>
> Key: TEIID-5397
> URL: https://issues.jboss.org/browse/TEIID-5397
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 10.2.1
> Reporter: Lukáš Svačina
> Assignee: Steven Hawkins
>
> *+VDB:+*
> {code}
> <vdb name="_GENERATED_form_shoes" version="2">
> <model name="_INTERNAL_internalModel" type="PHYSICAL">
> <source name="internal_postgresql" translator-name="postgresql" connection-jndi-name="java:/internal"/>
> </model>
> <model name="GEN_view" type="VIRTUAL">
> <metadata type="DDL">
> <![CDATA[ CREATE VIEW "_view_workflow_data" OPTIONS (UPDATABLE 'true')
> AS SELECT "public"."form_shoes_2"."size" AS "size", "public"."form_shoes_2"."model" AS "model",
> "public"."form_shoes_2"."id" AS "id" FROM "public"."form_shoes_2" ]]>
> </metadata>
> </model>
> </vdb>
> {code}
> *+TABLE:+*
> name: form_shoes_2
> columns: id (SERIAL) | size (INTEGER *NULLABLE*) | model (VARCHAR *NULLABLE*)
> *+PROBLEM:+*
> Connected into VDB using JDBC like:
> {code:java}
> final PreparedStatement statement = c.prepareStatement(...INSERT..., Statement.RETURN_GENERATED_KEYS);
> statement.executeUpdate();
> final ResultSet generatedKeys = statement.getGeneratedKeys();
> {code}
> *generatedKeys is empty if:*
> # INSERT INTO "form_shoes_2" ( "model" ) VALUES ( 'adidas x1' ) ...... e.g. not all columns are enumerated ... if so, you can provide NULL values to optional columns and generated keys WORK!
> # INSERT INTO "_view_workflow_data" (id, name, size) VALUES (42, 'adidas x2', 12 ) ....... e.g. insering into view (1:1, no joins involved) even when all columns ARE enumerated ... probably no way how to get generated keys here?
> *+QUESTIONS:+*
> # How to get last_insert_id() when inserting into foreign table/views (with/without joins)?
> # How to get updated rows (UPDATE ... RETURNING *)? At least primary keys of affected rows?
> Thanks for fixing/adding this functionality.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-5397) RETURN_GENERATED_KEYS not working
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-5397?page=com.atlassian.jira.plugin... ]
Ramesh Reddy updated TEIID-5397:
--------------------------------
Description:
*+VDB:+*
{code}
<vdb name="_GENERATED_form_shoes" version="2">
<model name="_INTERNAL_internalModel" type="PHYSICAL">
<source name="internal_postgresql" translator-name="postgresql" connection-jndi-name="java:/internal"/>
</model>
<model name="GEN_view" type="VIRTUAL">
<metadata type="DDL">
<![CDATA[ CREATE VIEW "_view_workflow_data" OPTIONS (UPDATABLE 'true')
AS SELECT "public"."form_shoes_2"."size" AS "size", "public"."form_shoes_2"."model" AS "model",
"public"."form_shoes_2"."id" AS "id" FROM "public"."form_shoes_2" ]]>
</metadata>
</model>
</vdb>
{code}
*+TABLE:+*
name: form_shoes_2
columns: id (SERIAL) | size (INTEGER *NULLABLE*) | model (VARCHAR *NULLABLE*)
*+PROBLEM:+*
Connected into VDB using JDBC like:
{code:java}
final PreparedStatement statement = c.prepareStatement(...INSERT..., Statement.RETURN_GENERATED_KEYS);
statement.executeUpdate();
final ResultSet generatedKeys = statement.getGeneratedKeys();
{code}
*generatedKeys is empty if:*
# INSERT INTO "form_shoes_2" ( "model" ) VALUES ( 'adidas x1' ) ...... e.g. not all columns are enumerated ... if so, you can provide NULL values to optional columns and generated keys WORK!
# INSERT INTO "_view_workflow_data" (id, name, size) VALUES (42, 'adidas x2', 12 ) ....... e.g. insering into view (1:1, no joins involved) even when all columns ARE enumerated ... probably no way how to get generated keys here?
*+QUESTIONS:+*
# How to get last_insert_id() when inserting into foreign table/views (with/without joins)?
# How to get updated rows (UPDATE ... RETURNING *)? At least primary keys of affected rows?
Thanks for fixing/adding this functionality.
was:
*+VDB:+*
<vdb name="_GENERATED_form_shoes" version="2"><model name="_INTERNAL_internalModel" type="PHYSICAL"><source name="internal_postgresql" translator-name="postgresql" connection-jndi-name="java:/internal"/></model><model name="GEN_view" type="VIRTUAL"><metadata type="DDL"><![CDATA[ CREATE VIEW "_view_workflow_data" OPTIONS (UPDATABLE 'true') AS SELECT "public"."form_shoes_2"."size" AS "size", "public"."form_shoes_2"."model" AS "model", "public"."form_shoes_2"."id" AS "id" FROM "public"."form_shoes_2" ]]></metadata></model></vdb>
*+TABLE:+*
name: form_shoes_2
columns: id (SERIAL) | size (INTEGER *NULLABLE*) | model (VARCHAR *NULLABLE*)
*+PROBLEM:+*
Connected into VDB using JDBC like:
{code:java}
final PreparedStatement statement = c.prepareStatement(...INSERT..., Statement.RETURN_GENERATED_KEYS);
statement.executeUpdate();
final ResultSet generatedKeys = statement.getGeneratedKeys();
{code}
*generatedKeys is empty if:*
# INSERT INTO "form_shoes_2" ( "model" ) VALUES ( 'adidas x1' ) ...... e.g. not all columns are enumerated ... if so, you can provide NULL values to optional columns and generated keys WORK!
# INSERT INTO "_view_workflow_data" (id, name, size) VALUES (42, 'adidas x2', 12 ) ....... e.g. insering into view (1:1, no joins involved) even when all columns ARE enumerated ... probably no way how to get generated keys here?
*+QUESTIONS:+*
# How to get last_insert_id() when inserting into foreign table/views (with/without joins)?
# How to get updated rows (UPDATE ... RETURNING *)? At least primary keys of affected rows?
Thanks for fixing/adding this functionality.
> RETURN_GENERATED_KEYS not working
> ---------------------------------
>
> Key: TEIID-5397
> URL: https://issues.jboss.org/browse/TEIID-5397
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 10.2.1
> Reporter: Lukáš Svačina
> Assignee: Steven Hawkins
>
> *+VDB:+*
> {code}
> <vdb name="_GENERATED_form_shoes" version="2">
> <model name="_INTERNAL_internalModel" type="PHYSICAL">
> <source name="internal_postgresql" translator-name="postgresql" connection-jndi-name="java:/internal"/>
> </model>
> <model name="GEN_view" type="VIRTUAL">
> <metadata type="DDL">
> <![CDATA[ CREATE VIEW "_view_workflow_data" OPTIONS (UPDATABLE 'true')
> AS SELECT "public"."form_shoes_2"."size" AS "size", "public"."form_shoes_2"."model" AS "model",
> "public"."form_shoes_2"."id" AS "id" FROM "public"."form_shoes_2" ]]>
> </metadata>
> </model>
> </vdb>
> {code}
> *+TABLE:+*
> name: form_shoes_2
> columns: id (SERIAL) | size (INTEGER *NULLABLE*) | model (VARCHAR *NULLABLE*)
> *+PROBLEM:+*
> Connected into VDB using JDBC like:
> {code:java}
> final PreparedStatement statement = c.prepareStatement(...INSERT..., Statement.RETURN_GENERATED_KEYS);
> statement.executeUpdate();
> final ResultSet generatedKeys = statement.getGeneratedKeys();
> {code}
> *generatedKeys is empty if:*
> # INSERT INTO "form_shoes_2" ( "model" ) VALUES ( 'adidas x1' ) ...... e.g. not all columns are enumerated ... if so, you can provide NULL values to optional columns and generated keys WORK!
> # INSERT INTO "_view_workflow_data" (id, name, size) VALUES (42, 'adidas x2', 12 ) ....... e.g. insering into view (1:1, no joins involved) even when all columns ARE enumerated ... probably no way how to get generated keys here?
> *+QUESTIONS:+*
> # How to get last_insert_id() when inserting into foreign table/views (with/without joins)?
> # How to get updated rows (UPDATE ... RETURNING *)? At least primary keys of affected rows?
> Thanks for fixing/adding this functionality.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-5398) Sybase translator error caused by change in ASCII function pushdown
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5398?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-5398:
----------------------------------
Fix Version/s: 11.0
I mistakenly took saw sql anywhere support as general sybase support. The sybase specific function is uscalar http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc36271_125...
> Sybase translator error caused by change in ASCII function pushdown
> -------------------------------------------------------------------
>
> Key: TEIID-5398
> URL: https://issues.jboss.org/browse/TEIID-5398
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.14.6_4
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
> Priority: Critical
> Fix For: 11.0
>
>
> There's a regression caused by TEIID-5313 change:
> {code:java}
> registerFunctionModifier(SourceSystemFunctions.ASCII, new AliasModifier("unicode")); //$NON-NLS-1$
> {code}
> Following query:
> {code:sql}
> SELECT INTKEY, ASCII(INTKEY) FROM BQT1.SmallA
> {code}
> being resolved to following src command:
> {code:sql}
> SELECT g_0.intkey, unicode(cast(g_0.intkey AS varchar(4000))) FROM smalla g_0
> {code}
> results in following error:
> {code:title=server.log}
> 12:42:04,102 WARN [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue18) Connector worker process failed for atomic-request=reZs6o4Ybhhc.8.0.1: org.teiid.translator.jdbc.JDBCExecutionException: 14216 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.intkey, unicode(cast(g_0.intkey AS varchar(4000))) FROM smalla g_0]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.14.6_4-redhat-64-2.jar:8.12.14.6_4-redhat-64-2]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:361)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.8.0_151]
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [rt.jar:1.8.0_151]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_151]
> at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_151]
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy79.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at java.util.concurrent.FutureTask.run(FutureTask.java:266) [rt.jar:1.8.0_151]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:284)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [rt.jar:1.8.0_151]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [rt.jar:1.8.0_151]
> at java.lang.Thread.run(Thread.java:748) [rt.jar:1.8.0_151]
> Caused by: com.sybase.jdbc4.jdbc.SybSQLException: Function 'unicode' not found. If this is a SQLJ function or SQL function, use sp_help to check whether the object exists (sp_help may produce a large amount of output).
> at com.sybase.jdbc4.tds.Tds.processEed(Tds.java:4003)
> at com.sybase.jdbc4.tds.Tds.nextResult(Tds.java:3093)
> at com.sybase.jdbc4.jdbc.ResultGetter.nextResult(ResultGetter.java:78)
> at com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:289)
> at com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:271)
> at com.sybase.jdbc4.jdbc.SybStatement.queryLoop(SybStatement.java:2408)
> at com.sybase.jdbc4.jdbc.SybStatement.executeQuery(SybStatement.java:2394)
> at com.sybase.jdbc4.jdbc.SybPreparedStatement.executeQuery(SybPreparedStatement.java:257)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123) [translator-jdbc-8.12.14.6_4-redhat-64-2.jar:8.12.14.6_4-redhat-64-2]
> ... 18 more
> 12:42:04,118 WARN [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue19) TEIID30020 Processing exception for request reZs6o4Ybhhc.8 'TEIID30504 Source: 14216 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.intkey, unicode(cast(g_0.intkey AS varchar(4000))) FROM smalla g_0]'. Originally TeiidProcessingException 'Function 'unicode' not found. If this is a SQLJ function or SQL function, use sp_help to check whether the object exists (sp_help may produce a large amount of output).
> ' Tds.java:4003. Enable more detailed logging to see the entire stacktrace.
> {code}
> SRC Command in previous version:
> {code:sql}
> SELECT g_0.intkey, ascii(cast(g_0.intkey AS varchar(4000))) FROM smalla g_0
> {code}
> NOTE: the issue is not caused by casting int to varchar, happens also when calling ASCII function on string directly.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-5398) Sybase translator error caused by change in ASCII function pushdown
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5398?page=com.atlassian.jira.plugin... ]
Work on TEIID-5398 started by Steven Hawkins.
---------------------------------------------
> Sybase translator error caused by change in ASCII function pushdown
> -------------------------------------------------------------------
>
> Key: TEIID-5398
> URL: https://issues.jboss.org/browse/TEIID-5398
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.14.6_4
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
> Priority: Critical
>
> There's a regression caused by TEIID-5313 change:
> {code:java}
> registerFunctionModifier(SourceSystemFunctions.ASCII, new AliasModifier("unicode")); //$NON-NLS-1$
> {code}
> Following query:
> {code:sql}
> SELECT INTKEY, ASCII(INTKEY) FROM BQT1.SmallA
> {code}
> being resolved to following src command:
> {code:sql}
> SELECT g_0.intkey, unicode(cast(g_0.intkey AS varchar(4000))) FROM smalla g_0
> {code}
> results in following error:
> {code:title=server.log}
> 12:42:04,102 WARN [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue18) Connector worker process failed for atomic-request=reZs6o4Ybhhc.8.0.1: org.teiid.translator.jdbc.JDBCExecutionException: 14216 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.intkey, unicode(cast(g_0.intkey AS varchar(4000))) FROM smalla g_0]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.14.6_4-redhat-64-2.jar:8.12.14.6_4-redhat-64-2]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:361)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.8.0_151]
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [rt.jar:1.8.0_151]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_151]
> at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_151]
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy79.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at java.util.concurrent.FutureTask.run(FutureTask.java:266) [rt.jar:1.8.0_151]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:284)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [rt.jar:1.8.0_151]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [rt.jar:1.8.0_151]
> at java.lang.Thread.run(Thread.java:748) [rt.jar:1.8.0_151]
> Caused by: com.sybase.jdbc4.jdbc.SybSQLException: Function 'unicode' not found. If this is a SQLJ function or SQL function, use sp_help to check whether the object exists (sp_help may produce a large amount of output).
> at com.sybase.jdbc4.tds.Tds.processEed(Tds.java:4003)
> at com.sybase.jdbc4.tds.Tds.nextResult(Tds.java:3093)
> at com.sybase.jdbc4.jdbc.ResultGetter.nextResult(ResultGetter.java:78)
> at com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:289)
> at com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:271)
> at com.sybase.jdbc4.jdbc.SybStatement.queryLoop(SybStatement.java:2408)
> at com.sybase.jdbc4.jdbc.SybStatement.executeQuery(SybStatement.java:2394)
> at com.sybase.jdbc4.jdbc.SybPreparedStatement.executeQuery(SybPreparedStatement.java:257)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123) [translator-jdbc-8.12.14.6_4-redhat-64-2.jar:8.12.14.6_4-redhat-64-2]
> ... 18 more
> 12:42:04,118 WARN [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue19) TEIID30020 Processing exception for request reZs6o4Ybhhc.8 'TEIID30504 Source: 14216 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.intkey, unicode(cast(g_0.intkey AS varchar(4000))) FROM smalla g_0]'. Originally TeiidProcessingException 'Function 'unicode' not found. If this is a SQLJ function or SQL function, use sp_help to check whether the object exists (sp_help may produce a large amount of output).
> ' Tds.java:4003. Enable more detailed logging to see the entire stacktrace.
> {code}
> SRC Command in previous version:
> {code:sql}
> SELECT g_0.intkey, ascii(cast(g_0.intkey AS varchar(4000))) FROM smalla g_0
> {code}
> NOTE: the issue is not caused by casting int to varchar, happens also when calling ASCII function on string directly.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-5396) Querying SQL Server variant type for a string fails
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5396?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5396:
---------------------------------------
> SOURCE SRC command with 6.4.2 (working):
That works, but is not generally correct - if the string contains unicode characters they will be replaced/lost.
> SOURCE SRC command with 6.4.3 (not working), note missing cast to varchar:
We will probably have to address that with a cast to nvarchar.
> Querying SQL Server variant type for a string fails
> ---------------------------------------------------
>
> Key: TEIID-5396
> URL: https://issues.jboss.org/browse/TEIID-5396
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.12.14.6_4
> Reporter: Andrej Šmigala
> Assignee: Steven Hawkins
> Priority: Critical
> Fix For: 11.0
>
>
> Table on SQL Server source created as
> {code:sql}CREATE TABLE table_with_variant(id INT, var SQL_VARIANT){code}
> and loaded with int, float and string data.
> Running the following query against a dynamic VDB with metadata import
> {code:sql}SELECT cast(var AS string) FROM table_with_variant WHERE id = 1{code}
> fails with
> {noformat}java.sql.SQLException: Remote com.microsoft.sqlserver.jdbc.SQLServerException: The "variant" data type is not supported.{noformat}
> This query used to work with DV 6.4.2, it appears it is a further regression from TEIID-5313 . Querying rows with non-string data works as expected.
> SOURCE SRC command with 6.4.2 (working):
> {code:sql}SELECT cast(g_0."var" AS varchar(4000)) FROM "dballo05"."dbo"."table_with_variant" g_0 WHERE g_0."id" = 1{code}
> SOURCE SRC command with 6.4.3 (not working), note missing cast to varchar:
> {code:sql}SELECT g_0."var" FROM "dballo06"."dbo"."table_with_variant" g_0 WHERE g_0."id" = 1{code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-5396) Querying SQL Server variant type for a string fails
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5396?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-5396:
----------------------------------
Fix Version/s: 11.0
> Querying SQL Server variant type for a string fails
> ---------------------------------------------------
>
> Key: TEIID-5396
> URL: https://issues.jboss.org/browse/TEIID-5396
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.12.14.6_4
> Reporter: Andrej Šmigala
> Assignee: Steven Hawkins
> Priority: Critical
> Fix For: 11.0
>
>
> Table on SQL Server source created as
> {code:sql}CREATE TABLE table_with_variant(id INT, var SQL_VARIANT){code}
> and loaded with int, float and string data.
> Running the following query against a dynamic VDB with metadata import
> {code:sql}SELECT cast(var AS string) FROM table_with_variant WHERE id = 1{code}
> fails with
> {noformat}java.sql.SQLException: Remote com.microsoft.sqlserver.jdbc.SQLServerException: The "variant" data type is not supported.{noformat}
> This query used to work with DV 6.4.2, it appears it is a further regression from TEIID-5313 . Querying rows with non-string data works as expected.
> SOURCE SRC command with 6.4.2 (working):
> {code:sql}SELECT cast(g_0."var" AS varchar(4000)) FROM "dballo05"."dbo"."table_with_variant" g_0 WHERE g_0."id" = 1{code}
> SOURCE SRC command with 6.4.3 (not working), note missing cast to varchar:
> {code:sql}SELECT g_0."var" FROM "dballo06"."dbo"."table_with_variant" g_0 WHERE g_0."id" = 1{code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months