3

I am getting a problem when I try to insert a variable of type "byte" in Java into a variable of type "bytea" in PostgreSQL.

This is my table in postgresql:

CREATE TABLE mesa (
   id_mesa              serial PRIMARY KEY,
   tag                  varchar(50),
   modelo               varchar(50),
   menor_complemento    smallint NOT NULL,
   peso_min             smallint NOT NULL,
   peso_max             smallint NOT NULL,
   som                  bytea NOT NULL,
   rotina               bytea NOT NULL,
   address64bits        bytea NOT NULL UNIQUE
);

my code in Java is the following:

private Mesa mesa;
//construtor
public MesaDAO (Mesa mesa) {
    this.mesa = mesa;
}
(...)
String stm = "INSERT INTO mesa(tag, modelo, menor_complemento, peso_min, "
            + "peso_max, som, rotina, address64bits) "
            + "VALUES(?,?,?,?,?,?,?,?)";
    try {
        pst = con.prepareStatement(stm);
        pst.setString(1, mesa.getTag());
        pst.setString(2, mesa.getModelo());
        pst.setInt(3, mesa.getMenorComplemento());
        pst.setInt(4, mesa.getPesoMin());
        pst.setInt(5, mesa.getPesoMax());
        pst.setByte(6, mesa.getSom());
        pst.setByte(7, mesa.getRotina());
        pst.setBytes(8, mesa.getAddress64Bits());
        pst.executeUpdate();
        (...)

The mesa types are:

public class Mesa{
  private Integer idMesa;
  private String tag;
  private String modelo;
  private Integer menorComplemento;
  private Integer pesoMin;
  private Integer pesoMax;
  private byte som;
  private byte rotina;
  private byte[] address64Bits;
  (...)
}

Then, when I try to insert something it throws a PSQLException stating that the column "som" is a "bytea" but the expression is a "smallint". I also think it gonna have the same SQLException on the following two lines after that one.

So I don't know how to fix that, if someone could help me I will be glad!

Thank you in advance,

EDIT:

Thanks guys for helping me, I will provide the solution here: change the variable "som" and "rotina" to "byte[]" type and then use pst.getBytes(...) instead of pst.getByte(...).

Clarifying:

I am using XBee (API mode), so I need to send the byte value through the port serial. So I was just verifying if what I am getting from the database is the same value of that one I inserted there. In conclusion, I want to know how to recover the bytes from database to send them through the serial port.

Renato Pereira
  • 834
  • 2
  • 9
  • 22
  • 1
    Can you show the type of object mesa pls. – Diversity Jan 22 '14 at 14:11
  • if `som` is a `byte` then why do you want to store as a `byte[]` (which is what `bytea` is)? From your Java variables, `som` should be a column with type `integer` –  Jan 22 '14 at 14:19
  • 2
    If you *really* need to store a single byte as a `BLOB` (which is what `bytea` is) you need to use `setBytes()` passing a `byte[]` not `setByte()` –  Jan 22 '14 at 14:23
  • Yeah, I am not sure if bytea is always byte[]. I thought it could be 1 byte too. – Renato Pereira Jan 22 '14 at 14:27
  • bytea - http://www.postgresql.org/docs/current/static/datatype-binary.html – vzamanillo Jan 22 '14 at 14:33
  • Ok, thanks guys, I changed "som" and "rotina" to byte[] so I am able to insert now. But I am getting unformatted data when I retrieve these variables using ps.getBytes(...); – Renato Pereira Jan 22 '14 at 14:37
  • 1
    What do you expect from printing a byte array? And, how could a byte array possibly be formatted? A byte array is just that: a number of raw bytes. **You** need to tell how it is going to be interpreted. – Ingo Jan 22 '14 at 15:16
  • Thanks for pointing it out. I am using XBee (API mode), so I need to send the byte value through the port serial. So I was just verifying if what I am getting from the database is the same value of that one I inserted there. In conclusion, I want to know how to recover the bytes from database to send them through the serial port. – Renato Pereira Jan 22 '14 at 15:20
  • You can make a method that prints a byte array in hex. Also, a method to compare 2 byte arrays. It's not against you, personally, but it is astonishing how many people that work with bytes, byte arrays, buffers, etc. seem to have not the slightest idea about dealing with the most basic things. – Ingo Jan 22 '14 at 15:27

1 Answers1

1

Code Java by PostgreSQL

pst.setBinaryStream(6, mesa.getSom());
pst.setBinaryStream(7, mesa.getRotina());
pst.setBinaryStream(8, mesa.getAddress64Bits());`
Gilberto
  • 11
  • 1