-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 6 posts ] 
  Previous topic | Next topic 
Author Message
MayconOliveira
 Post subject: PSQLException: column is of type numeric, not bytea
PostPosted: Tue Oct 17, 2017 9:37 pm 
Newbie

Joined: Tue Oct 17, 2017 9:27 pm
Posts: 3
Hi there!

I was trying to do a simple update using JPA on Wildfly 10 server:
Code:
Query q = this.em.createNativeQuery("update adm_texto set tip_texto = :tipTexto");
q.setParameter("tipTexto", null);
q.executeUpdate();


But I get this error:

Caused by: org.postgresql.util.PSQLException: ERROR: column "tip_texto" is of type numeric but expression is of type bytea
Dica: You will need to rewrite or cast the expression.


I already tryed to upgrade (5.1) and downgrade (4.3) the hibernate version but the problem remains. I also tryied to use a diferent database (oracle instead postgre) and the problem still happen.

The table is very simple:

CREATE TABLE adm_texto
(
seq_texto numeric(20,0) NOT NULL,
des_texto character varying(50) NOT NULL,
txt_texto text,
tip_texto numeric(2,0)
)


Any ideas why this happen and how to solve?

Thanks!
Top
 Profile  
 
vlad
 Post subject: Re: Simple Update command Failing (JPA)
PostPosted: Wed Oct 18, 2017 1:38 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
1. Try to cast the bind parameter:

Code:
Query q = this.em.createNativeQuery("update adm_texto set tip_texto = cast(:tipTexto as numeric)");
q.setParameter("tipTexto", null);
q.executeUpdate();


2. Try to hint the Type in Java:

Code:
Query q = this.em.createNativeQuery("update adm_texto set tip_texto = :tipTexto");
q.setParameter("tipTexto", (BigInteger) null);
q.executeUpdate();
Top
 Profile  
 
MayconOliveira
 Post subject: Re: Simple Update command Failing (JPA)
PostPosted: Wed Oct 25, 2017 9:04 pm 
Newbie

Joined: Tue Oct 17, 2017 9:27 pm
Posts: 3
Sorry the delay

The results are:

1 - Same error.

2 - the error changed: Caused by: org.postgresql.util.PSQLException: ERROR: cannot cast type bytea to numeric


I cant believe that isn't possible to do a simple null update. There must be a way to do this.
Top
 Profile  
 
vlad
 Post subject: Re: Simple Update command Failing (JPA)
PostPosted: Thu Oct 26, 2017 1:16 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
Of course, it works.

I created this test for this use case.

If you fork the repository and run the test, you will see that it runs like a charm:

Code:
public class PostgreSQLUpdateNullTest extends AbstractPostgreSQLIntegrationTest {

    @Override
    protected Class<?>[] entities() {
        return new Class<?>[] {
            Post.class
        };
    }

    @Test
    public void test() {
        doInJPA(entityManager -> {
            Post post = new Post();
            post.id = 1L;
            post.externalId = 123L;
            post.title = "High-Performance Java Persistence";

            entityManager.persist(post);
        });

        doInJPA(entityManager -> {
            int count = entityManager.createQuery(
                "update Post p " +
                "set p.externalId = :externalId, p.title = :title " +
                "where p.id = :id")
                .setParameter("externalId", null)
                .setParameter("title", null)
                .setParameter("id", 1L)
                .executeUpdate();
            assertEquals(1, count);

            Post post = entityManager.find(Post.class, 1L);
            assertNull(post.externalId);
            assertNull(post.title);
        });
    }

    @Entity(name = "Post")
    @Table(name = "post")
    public static class Post {

        @Id
        private Long id;

        private Long externalId;

        @Column(columnDefinition = "text")
        private String title;
    }
}


So, debug my test and yours and see where they differ.

Maybe you have a JDBC Driver issue, and you need to update it.
Top
 Profile  
 
MayconOliveira
 Post subject: Re: PSQLException: column is of type numeric, not bytea
PostPosted: Fri Oct 27, 2017 7:09 pm 
Newbie

Joined: Tue Oct 17, 2017 9:27 pm
Posts: 3
But on your example, you are using JPQL (this works fine).

Try to use the createNativeQuery method (doing a native query update, just like pure jdbc).
Top
 Profile  
 
vlad
 Post subject: Re: PSQLException: column is of type numeric, not bytea
PostPosted: Sat Oct 28, 2017 4:00 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
For native SQL, you have to hint the type:

Code:
int count = entityManager.createNativeQuery(
   "UPDATE post " +
   "SET externalId = :externalId, title = :title " +
   "WHERE id = :id")
.unwrap(org.hibernate.query.NativeQuery.class)
.setParameter("externalId", null, LongType.INSTANCE)
.setParameter("title", null, StringType.INSTANCE)
.setParameter("id", 1L)
.executeUpdate();
assertEquals(1, count);


We have JPA-31 for this issue.
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 6 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.