Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 6 posts ] 
Author Message
 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  
 
 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: 1516
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();

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 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  
 
 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: 1516
Of course, it works.

I created this test in my book GitHub repository.

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

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.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 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  
 
 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: 1516
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.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 6 posts ] 

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:
cron
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.