Hi Florian, great work with the wiki [1]. Indeed, I think this is a very important point and we should clarify this, because it can get quite confusing with all the different possibilities. Your description helps a lot! Currently, we do not support all data types. I think it makes sense to define the mappings between StreamPipes types and data base types for all available values, both for the sink and for adapters.
Regarding the capital letters in the names, is this just a problem with Postgres or might there also be other JDBC databases with the same issue? And what about special characters, do we have to filter them out too? I also like the idea with the inheritance of the JdbcClient. What do you think, should we move all sinks using JDBC into one package? Your changes look good, I would say it makes sense to merge them into dev and try to use the components as much as possible to find possible problems. A would also suggest to implement an adapter for each of the sinks. It makes the testing and usage of the databases much easier, when we can use StreamPipes to write data into the database and then use an adapter to read the data again. Philipp [1] https://cwiki.apache.org/confluence/display/STREAMPIPES/JDBC+Database+Client <https://cwiki.apache.org/confluence/display/STREAMPIPES/JDBC+Database+Client> > On 12. Jun 2020, at 13:54, Florian Micklich <[email protected]> wrote: > > Hi all, > > I created a ticket STREAMPIPES-162 and also found a solution to fix the > described issue. > > I tested it with the Postgres and MYSQL Sink. > If I get a short OK I would push the rewrite_jdbcclient branch into > dev. > > Greetings > Florian > > > > Am Montag, den 08.06.2020, 12:57 +0200 schrieb Florian Micklich: >> Hi all, >> >> I just finished rewriting the JDBC class, and added an "extends" >> postgresJDBC-client, to handle postgres and postgis as a sink with >> more >> options. >> >> I tested it with the mysql sink already and everything is working >> with >> mother JDBCClient-class. >> >> During this process I also "outsourced" the SQLAttributeEnum out of >> the >> JDBC-class and changed some names and layout for better >> understanding: >> >> >> public enum SqlAttribute { >> // DEFAULT >> INTEGER("INT"), >> LONG("BIGINT"), >> FLOAT("FLOAT"), >> DOUBLE("DOUBLE"), >> STRING("VARCHAR(255)"), >> BOOLEAN("BOOLEAN"), >> //MYSQL >> MYSQL_DATETIME("DATETIME"), >> //POSTGRES / POSTGIS >> PG_DOUBLE("NUMERIC"); >> >> >> >> I hope this is ok the way I did it. >> >> >> In this case postgres uses the TYPE numeric instead of the non >> existing >> DOUBLE type, as an example. >> So later on I will add also a postgres timestamp type, like it was >> done >> in the mysql sink last week. >> >> But there is one think, I need some assistance and it took a while to >> find out why and what is happening: >> >> So I used the randomDataset Adapter and the Postgres Sink. >> >> The database, schema and table is created as expected but during >> save, >> I get all the time: >> >> Table 'testtable' was unexpectedly not found and gets recreated. >> ERROR: column "randomText" of relation "testtable" does not exist >> >> So the first message is a litte bit misleading, because the table >> exists but can't be found and will be recreated in an endless loop. >> >> The reason is because this statement during the >> generatePreparedStatement method: >> >> INSERT INTO testschema.testtable ( "randomText", >> "randomNumber", "count", "timestamp" ) VALUES ( 'c', 69, 83531, >> 1591611708415 ); >> >> >> The problem here is that postgres uses lowercases for the column >> names. >> So instead of randomText then name has to be randomtext: >> >> INSERT INTO testschema.testtable ( "randomtext", >> "randomnumber", "count", "timestamp" ) VALUES ( 'c', 69, 83531, >> 1591611708415 ) >> >> Can someone give me a hint, where to add .toLowerCase to the correct >> positon to get a quick fix? >> >> I am working in the branch feature/jdbc_rewrite and the >> PostgresJDPCClient class. >> >> Greetings Florian >> >> >> >> >
