[teiid-issues] [JBoss JIRA] (TEIID-2075) Value of 'NEW' attribute is not set when an update procedure is executed

[Steven Hawkins (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-2075?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins closed TEIID-2075.
---------------------------------


    
> Value of 'NEW' attribute is not set when an update procedure is executed
> ------------------------------------------------------------------------
>
>                 Key: TEIID-2075
>                 URL: https://issues.jboss.org/browse/TEIID-2075
>             Project: Teiid
>          Issue Type: Bug
>    Affects Versions: 8.1
>         Environment: Windows 7, Java 1.6.0_29, JBoss 7.1.1, Microsoft SQl Server 2005, Microsoft SQL server jdbc driver sqljdbc4_3.0.1301.101
>            Reporter: heidi muehlebach
>            Assignee: Steven Hawkins
>              Labels: procedure, update
>             Fix For: 8.1
>
>
> I'm using teiid 8.1 Alpha with Jboss 7.1.1 and the teiid designer 7.7.0.
> I have created a VDB by combining a flat file and a MS SQL database. I used the Data from the Dynamic Portfolio example that comes with teiid.
> My VDB combines columns from the product sql table and the price file.
> The product table has the columns: ID:int, SYMBOL:string(16), COMPANY_NAME:string(256)
> The price file has the columns: SYMBOL:string(10), PRICE:string(10)
> I have combined this into a single view that has: ID:int, SYMBOL:string(16), COMPANY_NAME:string(256), PRICE:string(10) and it's joind by SYMBOL
> I am able to successfully query this view and see all the records.
> Now I want to be able to update the value of COMPANY_NAME for records in this view.
> Using the TEIID designer I have created an UPDATE procedure, and it looks like this:
> FOR EACH ROW
> BEGIN ATOMIC
>     IF(CHANGING.COMPANY_NAME)
>     BEGIN
>         UPDATE "teiid-portfolio-sample".dbo.PRODUCT SET COMPANY_NAME = "NEW".COMPANY_NAME WHERE (SYMBOL = "NEW".SYMBOL) AND (COMPANY_NAME = "NEW".COMPANY_NAME);
>     END
> END
> However when I try to do an update it fails as follows:
> 'TEIID30504 CustomerSource: 0 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: ['?', 'BA', '?'] SQL: UPDATE "teiid-portfolio-sample"."dbo"."PRODUCT" SET COMPANY_NAME = ? WHERE "teiid-portfolio-sample"."dbo"."PRODUCT".SYMBOL = ? AND "teiid-portfolio-sample"."dbo"."PRODUCT".COMPANY_NAME = ?]' for request dWYVGH00C9jI.17.  Exception type org.teiid.core.TeiidProcessingException thrown from com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:171). Enable more detailed logging to see the entire stacktrace.
> which was caused  by:
> Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The conversion from UNKNOWN to VARCHAR is unsupported.
> When I look in the TEIID log file I see the following for the update request:
> 13:37:43,046 DEBUG [org.teiid.CONNECTOR] (Worker37_QueryProcessorQueue452) Source-specific command: UPDATE "teiid-portfolio-sample"."dbo"."PRODUCT" SET COMPANY_NAME = ? WHERE "teiid-portfolio-sample"."dbo"."PRODUCT".SYMBOL = ? AND "teiid-portfolio-sample"."dbo"."PRODUCT".COMPANY_NAME = ?
> So it looks like the new value for COMPANY_NAME doesn't exist.
> My original update procedure didn't have '(COMPANY_NAME = "NEW".COMPANY_NAME)' in the where clause, I just put it there to see if it would show the new value.
> If I use '(COMPANY_NAME = "OLD".COMPANY_NAME)' in the where clause instead, then I see the old value of COMPANY_NAME in the log.
> If I use a SET COMPANY_NAME ='new name' instead of "NEW".COMPANY_NAME in the SET clause then it changes the name to 'new name'.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira