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