Re: 4.1 informix jdbc issue

[Michael Gentry]

Hugi...you need to save that story for Halloween...


On Thu, Sep 10, 2020 at 3:00 PM Hugi Thordarson  wrote:

> Hi Sébastien,
>
> Do you have the option of upgrading the Informix JDBC-driver? I seem to
> recall that their more recent drivers *do* support
> prepareStatement(String,int) but to use a newer JDBC driver you need a
> compatible version of Informix (can't use the new JDBC-drivers to connect
> to older versions of the DB). We couldn't do that—all the people that knew
> the DB server were either dead or retired—but if that's possible in your
> case, that would probably be the best solution.
>
>  Story of an ugly workaround starts. Do not read if you are young,
> impersonable or mentally unstable. In fact, probably just don't read this
> at all.
>
> But yes, I encountered this issue a couple of years ago and I didn't
> really solve it. Cayenne 4.1+ will use prepareStatement( String, int ) if
> the table has a DB-generated PK column. In my case, the non-solution I
> ended with was make the generated PK "Cayenne generated". Then I
> implemented a PKGenerator that just returned "null" for the PK. Then I
> excluded the PK-column from insert statements using an
> InsertBatchTranslator (because Informix would not populate the PK/serial's
> value if it was set to null in the insert statement). Then I just threw
> away the poor ObjectContext that did all these horrible things and
> re-fetched the inserted objects in a new context, now with DB generated PKs.
> This is obviously horrid. But it worked as a temporary workaround while we
> were in the process of migrating to Postgres.
>
>  Ugly workaround ends
>
> So in short: I hope you can upgrade the JDBC driver. Or that I am in the
> wrong and someone has a better solution for you.
>
> - hugi
>
>
>
> > On 10 Sep 2020, at 10:24, Sébastien Pérès-Labourdette <
> sebastien.peres-labourde...@omerin.com> wrote:
> >
> > Hi there.
> >
> > While integrating 4.1 we found that the informix jdbc driver from ibm
> > does not support
> > IfxSqliConnection:prepareStatement(String,int)
> >
> > Invoked from
> > connection.prepareStatement(compiled.getSql(), generatedKeys); in
> > SQLTemplateAction.execute
> >
> > FYI,4.0.2 sqltemplates are fine with this jdbc driver.
> >
> > Is there anything we can do (replacing informix with any other rdbms not
> > being an option) to get 4.1 sqltemplates running as smooth as the 4.0.2
> > ones ?
> >
> > --
> > Sébastien
> >
>
>

Re: Cayenne 4.2—Modifying SQL generation to always use COALESCE when updating a certain column

[Andrus Adamchik]

4.2 for the first time exposes generated SQL as an AST, so tweaking it has 
become much easier. This is the feature that made possible to support 
geospatial and JSON types.

Nikita, please correct me if I am wrong with the following instructions (as 
this API is still in flux). So.. To customize produced SQL, you will need to 
register a custom DbAdapter that returns a custom SQLTreeProcessor.  
SQLTreeProcessor is a SQL tree walking callback that can change the default 
tree structure. Your own processor can extend TypeAwareSQLTreeProcessor. See 
for instance PostgreSQLTreeProcessor for how to change the default behavior.

Andrus


> On Sep 8, 2020, at 11:12 PM, Hugi Thordarson  wrote:
> 
> Working with old DB designs really results in the weirdest questions…
> 
> So… I've been working around a design problem in a customer DB by using my 
> own BatchTranslatorFactory. The functionality was that if a column is called 
> "company", every update wraps the column's new value in a coalesce function 
> to ensure that it's never set to null (for… reasons). This has worked great 
> as a workaround for our problem.
> 
> However, SQL generation in Cayenne 4.2 is all new so my current solution ( 
> https://gist.github.com/hugith/a33a20fc7da7fd8f709f59ce3a30a96a 
>  ) doesn't 
> really port.
> Before I start considering migration to 4.2, is this possible to do there? Or 
> should I just bite the bullet and start fixing up that bloody DB before 
> upgrading?
> 
> Cheers,
> - hugi

Re: 4.1 informix jdbc issue

[Sébastien Pérès-Labourdette]

Hi Hugi

Thanks for sharing this "not so ugly at all" workaround.
Upgrading the server side (Informix SE 7.25.UC7 on IBM AIX 5.3) not
being an option as well, we'll try something in that direction.

Thanks again.

--
Sébastien


*De : *Hugi Thordarson 
*Envoyé : * Jeudi 10 Septembre 2020 21:00:38
*À : *User 
*Objet : *Re: 4.1 informix jdbc issue
> Hi Sébastien,
>
> Do you have the option of upgrading the Informix JDBC-driver? I seem to 
> recall that their more recent drivers *do* support 
> prepareStatement(String,int) but to use a newer JDBC driver you need a 
> compatible version of Informix (can't use the new JDBC-drivers to connect to 
> older versions of the DB). We couldn't do that—all the people that knew the 
> DB server were either dead or retired—but if that's possible in your case, 
> that would probably be the best solution.
>
>  Story of an ugly workaround starts. Do not read if you are young, 
> impersonable or mentally unstable. In fact, probably just don't read this at 
> all.
>
> But yes, I encountered this issue a couple of years ago and I didn't really 
> solve it. Cayenne 4.1+ will use prepareStatement( String, int ) if the table 
> has a DB-generated PK column. In my case, the non-solution I ended with was 
> make the generated PK "Cayenne generated". Then I implemented a PKGenerator 
> that just returned "null" for the PK. Then I excluded the PK-column from 
> insert statements using an InsertBatchTranslator (because Informix would not 
> populate the PK/serial's value if it was set to null in the insert 
> statement). Then I just threw away the poor ObjectContext that did all these 
> horrible things and re-fetched the inserted objects in a new context, now 
> with DB generated PKs.
> This is obviously horrid. But it worked as a temporary workaround while we 
> were in the process of migrating to Postgres.
>
>  Ugly workaround ends
>
> So in short: I hope you can upgrade the JDBC driver. Or that I am in the 
> wrong and someone has a better solution for you.
>
> - hugi
>
>
>
>> On 10 Sep 2020, at 10:24, Sébastien Pérès-Labourdette 
>>  wrote:
>>
>> Hi there.
>>
>> While integrating 4.1 we found that the informix jdbc driver from ibm
>> does not support
>> IfxSqliConnection:prepareStatement(String,int)
>>
>> Invoked from
>> connection.prepareStatement(compiled.getSql(), generatedKeys); in
>> SQLTemplateAction.execute
>>
>> FYI,4.0.2 sqltemplates are fine with this jdbc driver.
>>
>> Is there anything we can do (replacing informix with any other rdbms not
>> being an option) to get 4.1 sqltemplates running as smooth as the 4.0.2
>> ones ?
>>
>> --
>> Sébastien
>>


signature.asc
Description: OpenPGP digital signature