Git Product home page Git Product logo

Comments (13)

andreak avatar andreak commented on May 28, 2024

FYI: Changing the query to "SELECT DISTINCT ?" works...

from pgjdbc-ng.

kdubb avatar kdubb commented on May 28, 2024

Unfortunately this is how PostgreSQL itself works and the current driver works around it. Currently you must explicitly provide the type, through a means such as casting, for unknown parameters (e.g. SELECT ?::int ). Given this solution, this has not arose as a large issue for me. Would this solve your problem as well?

On Aug 26, 2013, at 1:45 AM, Andreas Joseph Krogh [email protected] wrote:

The following test fails:

public void testSetIntegerFails() throws Exception {
    String sql = "SELECT ?";
    PreparedStatement ps = conn.prepareStatement(sql);
    ps.setInt(1, 1);
    ps.executeQuery();
}

Some ORMs generate queries like this:
SELECT ..... WHERE a.id = $1 AND EXISTS (SELECT $2 FROM my_entity WHERE ...)
and then issue a "ps.setInt(2, 1)"

This works fine with the old, official, driver but fails with pgjdbc-ng which makes using this driver with ORMs difficult.

Thanks.

—
Reply to this email directly or view it on GitHub.

from pgjdbc-ng.

andreak avatar andreak commented on May 28, 2024

No, this does not solve my problem as it's EclipseLink that is creating these SQL-statements and I have no control over them. If the current, official, driver works around it, why cannot pgjdbc-ng do the same?

from pgjdbc-ng.

andreak avatar andreak commented on May 28, 2024

Is this something you'll get around to look at or should I go bark up the EclipseLink tree and explain the issue to them? I expect this issue will bite quite a few developers trying out your new, excellent, driver and hope you'll find a similar workaround as the official driver.

The reason for me nagging is that right now I cannot deploy my app because either Blobs won't work with the official driver (because it doesn't implement Connection.createBlob) or some generated queries won't work with your NG-driver. If you don't plan on looking into this, please say so, then I can go persuading the EL-people to generate "SELECT ?::int"-queries for situations like this. I expect that to take much longer time though.

Thanks.

from pgjdbc-ng.

kdubb avatar kdubb commented on May 28, 2024

I am looking into this. I'll update you in a day or so to see where it stands.

from pgjdbc-ng.

andreak avatar andreak commented on May 28, 2024

Cool!
I wish the official driver guys were as responsive as you:-)

from pgjdbc-ng.

kdubb avatar kdubb commented on May 28, 2024

I think I have found finally found a workable solution! I have committed it in 7511c37 . Can you please test this and let me know if it works for you?

from pgjdbc-ng.

andreak avatar andreak commented on May 28, 2024

Sorry for not seeing this before. Will test now!

from pgjdbc-ng.

andreak avatar andreak commented on May 28, 2024

It doesn't work for a query generated by EclipseLink:

LOG:  statement: BEGIN
ERROR:  could not determine data type of parameter $2
STATEMENT:  SELECT t0.entity_id, t0.type, t0.VERSION, t1.entity_id, t1.attachment_size, t1.body_hash, t1.body_size_octets, t1.message_id, t1.plain_text_content, t1.sent_timestamp, t1.message_as_string, t1.from_entity_id, t1.forwarded_from_id, t1.replyto_id, t1.envelope_from_id FROM origo_email_folder_message t4, origo_email_delivery t3, onp_crm_entity t2, origo_email_message t1, onp_crm_entity t0 WHERE (((t4.folder_id IN ($1)) AND NOT EXISTS (SELECT $2 FROM origo_email_message_property t5 WHERE (((t5.is_seen = $3) AND (t5.owner_id = $4)) AND (t5.message_id = t0.entity_id))) ) AND (((t2.entity_id = t4.delivery_id) AND ((t3.entity_id = t2.entity_id) AND (t2.type = $5))) AND ((t0.entity_id = t3.message_id) AND ((t1.entity_id = t0.entity_id) AND (t0.type = $6)))))

And it somehow goes into endless loop:

ERROR:  current transaction is aborted, commands ignored until end of transaction block
STATEMENT:  SELECT t0.entity_id, t0.type, t0.VERSION, t1.entity_id, t1.attachment_size, t1.body_hash, t1.body_size_octets, t1.message_id, t1.plain_text_content, t1.sent_timestamp, t1.message_as_string, t1.from_entity_id, t1.forwarded_from_id, t1.replyto_id, t1.envelope_from_id FROM origo_email_folder_message t4, origo_email_delivery t3, onp_crm_entity t2, origo_email_message t1, onp_crm_entity t0 WHERE (((t4.folder_id IN ($1)) AND NOT EXISTS (SELECT $2 FROM origo_email_message_property t5 WHERE (((t5.is_seen = $3) AND (t5.owner_id = $4)) AND (t5.message_id = t0.entity_id))) ) AND (((t2.entity_id = t4.delivery_id) AND ((t3.entity_id = t2.entity_id) AND (t2.type = $5))) AND ((t0.entity_id = t3.message_id) AND ((t1.entity_id = t0.entity_id) AND (t0.type = $6)))))
ERROR:  current transaction is aborted, commands ignored until end of transaction block
STATEMENT:  SELECT t0.entity_id, t0.type, t0.VERSION, t1.entity_id, t1.attachment_size, t1.body_hash, t1.body_size_octets, t1.message_id, t1.plain_text_content, t1.sent_timestamp, t1.message_as_string, t1.from_entity_id, t1.forwarded_from_id, t1.replyto_id, t1.envelope_from_id FROM origo_email_folder_message t4, origo_email_delivery t3, onp_crm_entity t2, origo_email_message t1, onp_crm_entity t0 WHERE (((t4.folder_id IN ($1)) AND NOT EXISTS (SELECT $2 FROM origo_email_message_property t5 WHERE (((t5.is_seen = $3) AND (t5.owner_id = $4)) AND (t5.message_id = t0.entity_id))) ) AND (((t2.entity_id = t4.delivery_id) AND ((t3.entity_id = t2.entity_id) AND (t2.type = $5))) AND ((t0.entity_id = t3.message_id) AND ((t1.entity_id = t0.entity_id) AND (t0.type = $6)))))
ERROR:  current transaction is aborted, commands ignored until end of transaction block
STATEMENT:  SELECT t0.entity_id, t0.type, t0.VERSION, t1.entity_id, t1.attachment_size, t1.body_hash, t1.body_size_octets, t1.message_id, t1.plain_text_content, t1.sent_timestamp, t1.message_as_string, t1.from_entity_id, t1.forwarded_from_id, t1.replyto_id, t1.envelope_from_id FROM origo_email_folder_message t4, origo_email_delivery t3, onp_crm_entity t2, origo_email_message t1, onp_crm_entity t0 WHERE (((t4.folder_id IN ($1)) AND NOT EXISTS (SELECT $2 FROM origo_email_message_property t5 WHERE (((t5.is_seen = $3) AND (t5.owner_id = $4)) AND (t5.message_id = t0.entity_id))) ) AND (((t2.entity_id = t4.delivery_id) AND ((t3.entity_id = t2.entity_id) AND (t2.type = $5))) AND ((t0.entity_id = t3.message_id) AND ((t1.entity_id = t0.entity_id) AND (t0.type = $6)))))
ERROR:  current transaction is aborted, commands ignored until end of transaction block
STATEMENT:  SELECT t0.entity_id, t0.type, t0.VERSION, t1.entity_id, t1.attachment_size, t1.body_hash, t1.body_size_octets, t1.message_id, t1.plain_text_content, t1.sent_timestamp, t1.message_as_string, t1.from_entity_id, t1.forwarded_from_id, t1.replyto_id, t1.envelope_from_id FROM origo_email_folder_message t4, origo_email_delivery t3, onp_crm_entity t2, origo_email_message t1, onp_crm_entity t0 WHERE (((t4.folder_id IN ($1)) AND NOT EXISTS (SELECT $2 FROM origo_email_message_property t5 WHERE (((t5.is_seen = $3) AND (t5.owner_id = $4)) AND (t5.message_id = t0.entity_id))) ) AND (((t2.entity_id = t4.delivery_id) AND ((t3.entity_id = t2.entity_id) AND (t2.type = $5))) AND ((t0.entity_id = t3.message_id) AND ((t1.entity_id = t0.entity_id) AND (t0.type = $6)))))
...
...
...
...

from pgjdbc-ng.

kdubb avatar kdubb commented on May 28, 2024

Is there any way you could send me some DDL to create the necessary bits to make this query work? Should be a simple fix once I can test it easily.

from pgjdbc-ng.

andreak avatar andreak commented on May 28, 2024

Strange, I created a sample EclipseLink project, without Spring, and there it works correctly.

I see the following in the PG-log though:

ERROR:  could not determine data type of parameter $1
STATEMENT:  SELECT t0.id, t0.content, t0.message_id FROM part t0 WHERE NOT EXISTS (SELECT $1 FROM message t1 WHERE (t1.id = $2))
LOG:  execute 0000000000000001: SELECT t0.id, t0.content, t0.message_id FROM part t0 WHERE NOT EXISTS (SELECT $1 FROM message t1 WHERE (t1.id = $2))
DETAIL:  parameters: $1 = '1', $2 = '2'

I don't know what technique you use for making this work but it seems the server first gets a query which results in an error, then re-executes the query?
I'll create a test with Spring-managed transactions later and update this issue.

For now I use this work-around, using a custom SessionCustomizer in EclipseLink:

session.getPlatform.setShouldBindLiterals(false)

from pgjdbc-ng.

kdubb avatar kdubb commented on May 28, 2024

I think the solution to this is "don't do it". The simple fact is Postgres doesn't support it. With this driver my vision is to expose all of Postgres's awesome features; not emulate a bunch of stuff it doesn't support.

The "workaround" seems to kill in flight transactions. Which is why yours worked in a simple test but failed in the full environment. I didn't realize that.

It sounds like setShouldBindLiterals(false) is exactly what people need to do in this case. Postgres doesn't allow binding literals. Can you confirm that this completely solves your issues with EclipseLink?

from pgjdbc-ng.

andreak avatar andreak commented on May 28, 2024

I can confirm that setShouldBindLiterals(false) solves the issue. Close this ticket:-)

from pgjdbc-ng.

Related Issues (20)

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.