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'.