OR insert data to a MySQL table from CSV using Teiid. I already have the JDBC and flat-file connections in place and I can preview data from both tables in Teiid designer.
Assuming that you already have the usage of TEXTTABLE in a view, then you would just use standard sql:
insert into mysqltable (column list) select columns from textview
Much appreciated Stephen.
This is what I have:
1. A relational view model holding a table that has fields of interest (fields that hold records from two tables -MySQL and textfile-.
2. A select query on select tab - which works well when i hit preview data button
SELECT field1, field2
SELECT filed1, field2
3. On insert tab, I have
CREATE VIRTUAL PROCEDURE
INSERT INTO table1 (field1,field2)
No 3 has no errors
After the above, I don't know where to go to, in Teiid designer, to have the data inserted into MySQL
1 of 1 people found this helpful
Once you deploy the VDB built with above view to a Teiid server, you can either use external JDBC clients like SquirreL or Teiid Designer data tools (right click on vdb, execute) then type a insert command
insert into view (...) values (...)
Thanks Ramesh, let me try it out.
I get the error below when i execute an insert query on VDB...any ideas....
TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 Model_ltcperson: 1366 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values:  SQL: INSERT INTO `ltcdb_prod`.`ltcperson` (`ltcPersonID`, `FirstName`, `LastName`, `CreatedBy`, `CreatedDate`) VALUES ('9000', 'Usert', 'Testt', 'Morgan', '02122014')]
There should be nested exception for this that could provide more detail. What we can infer from just that is you are seeing a mysql error 1366 - which is an incorrect string value. You'll want to ensure that what you are trying to insert are valid values.
Thanks Stephen and Ramesh.
I was able to insert data after trying on a local scaled down database as opposed to a remote MySQL complex MySQL.
Could be connection problems or validation issues.
i will find out.