[ https://issues.jboss.org/browse/TEIID-5229?page=com.atlassian.jira.plugin... ] Jan Stastny commented on TEIID-5229: ------------------------------------ [~shawkins] Here is the vdb: {code} <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <vdb name="googlespreadsheetcrud" version="1"> <description>Google Spreadsheet for CRUD operations</description> <model name="Source" type="PHYSICAL" visible="false"> <source name="Source" translator-name="google-spreadsheet" connection-jndi-name="java:/googlespreadsheet-crud" /> <metadata type="DDL"> <![CDATA[ CREATE FOREIGN TABLE smalla( doublenum double OPTIONS (NATIVE_TYPE 'NUMBER'), booleanvalue boolean OPTIONS (NATIVE_TYPE 'BOOLEAN'), intnum integer OPTIONS (NATIVE_TYPE 'NUMBER'), longnum long OPTIONS (NATIVE_TYPE 'NUMBER'), floatnum float OPTIONS (NATIVE_TYPE 'NUMBER'), bytenum byte OPTIONS (NATIVE_TYPE 'NUMBER'), stringkey string(10) OPTIONS (NATIVE_TYPE 'STRING'), objectvalue object OPTIONS (NATIVE_TYPE 'STRING'), stringnum string(10) OPTIONS (NATIVE_TYPE 'STRING'), bigintegervalue biginteger OPTIONS (NATIVE_TYPE 'NUMBER'), datevalue date OPTIONS (NATIVE_TYPE 'DATE'), charvalue string OPTIONS (NATIVE_TYPE 'STRING'), timevalue time OPTIONS (NATIVE_TYPE 'TIMEOFDAY'), bigdecimalvalue bigdecimal OPTIONS (NATIVE_TYPE 'NUMBER'), timestampvalue timestamp OPTIONS (NATIVE_TYPE 'DATETIME'), intkey integer OPTIONS (NATIVE_TYPE 'NUMBER'), shortvalue short OPTIONS (NATIVE_TYPE 'NUMBER') ) OPTIONS (UPDATABLE 'TRUE', NAMEINSOURCE 'smalla_${label}'); ]]> </metadata> </model> <model name="CRUD" type="VIRTUAL"> <metadata type="DDL"> <![CDATA[ CREATE VIEW SmallA (IntKey integer PRIMARY KEY, StringKey string, IntNum integer, StringNum string, FloatNum float, LongNum long, DoubleNum double, ByteNum byte, DateValue date, TimeValue time, TimestampValue timestamp, BooleanValue boolean, CharValue char, ShortValue short, BigIntegerValue biginteger, BigDecimalValue bigdecimal, ObjectValue object) OPTIONS (UPDATABLE 'TRUE') AS SELECT intkey, stringkey, intnum, stringnum, floatnum, longnum, doublenum, bytenum, datevalue, timevalue, timestampvalue, booleanvalue, convert(charvalue, char), shortvalue, bigintegervalue, bigdecimalvalue, objectvalue FROM Source.smalla; CREATE TRIGGER ON SmallA INSTEAD OF INSERT AS FOR EACH ROW BEGIN ATOMIC INSERT INTO Source.smalla (intkey, stringkey, intnum, stringnum, floatnum, longnum, doublenum, bytenum, datevalue, timevalue, timestampvalue, booleanvalue, charvalue, shortvalue, bigintegervalue, bigdecimalvalue, objectvalue) VALUES (NEW.IntKey, NEW.StringKey, NEW.IntNum, NEW.StringNum, NEW.FloatNum, NEW.LongNum, NEW.DoubleNum, NEW.ByteNum, NEW.DateValue, NEW.TimeValue, NEW.TimestampValue, NEW.BooleanValue, NEW.CharValue, NEW.ShortValue, NEW.BigIntegerValue, NEW.BigDecimalValue, convert(NEW.ObjectValue, string)); END; CREATE TRIGGER ON SmallA INSTEAD OF UPDATE AS FOR EACH ROW BEGIN ATOMIC IF(CHANGING.ObjectValue) BEGIN UPDATE Source.smalla SET intkey = NEW.IntKey, stringkey = NEW.StringKey, intnum = NEW.IntNum, stringnum = NEW.StringNum, floatnum = NEW.FloatNum, longnum = NEW.LongNum, doublenum = NEW.DoubleNum, bytenum = NEW.ByteNum, datevalue = NEW.DateValue, timevalue = NEW.TimeValue, timestampvalue = NEW.TimestampValue, booleanvalue = NEW.BooleanValue, charvalue = convert(NEW.CharValue, char), shortvalue = NEW.ShortValue, bigintegervalue = NEW.BigIntegerValue, bigdecimalvalue = NEW.BigDecimalValue, objectvalue = convert(NEW.ObjectValue, string) WHERE intkey = OLD.IntKey; END ELSE BEGIN UPDATE Source.smalla SET intkey = NEW.IntKey, stringkey = NEW.StringKey, intnum = NEW.IntNum, stringnum = NEW.StringNum, floatnum = NEW.FloatNum, longnum = NEW.LongNum, doublenum = NEW.DoubleNum, bytenum = NEW.ByteNum, datevalue = NEW.DateValue, timevalue = NEW.TimeValue, timestampvalue = NEW.TimestampValue, booleanvalue = NEW.BooleanValue, charvalue = convert(NEW.CharValue, char), shortvalue = convert(NEW.ShortValue, short), bigintegervalue = NEW.BigIntegerValue, bigdecimalvalue = NEW.BigDecimalValue, objectvalue = NEW.ObjectValue WHERE intkey = OLD.IntKey; END END; ]]> </metadata> </model> </vdb> {code} -- This message was sent by Atlassian JIRA (v7.5.0#75005)