Data truncation: Data too long for column

[Cezar Apulchro]

Hi guys,

more one problem for yours. I have this DAO that I want to return the code of an item, the return are ok but the problem is that when I get the code returned to write in my db, the message is data too long.

My DAO:

public ArrayList<Bean_Procedimentos_Ultra> lstCodUltra(
            String Procedimentos)
    {
        System.out.println("DAO Entrando para pegar codigo...");
        System.out.println("Procedimento recebido: " + Procedimentos);
        Connection conectar = null;            
        ResultSet rs = null;            
        PreparedStatement pstmInput = null;
        ArrayList<Bean_Procedimentos_Ultra> bcodultra = new ArrayList<Bean_Procedimentos_Ultra>();
        Bean_Procedimentos_Ultra constprcultra = new Bean_Procedimentos_Ultra();
        
        conectar = new Conectar_Banco_W00().getConnection();
        
        try
        {
        conectar = new Conectar_Banco_W00().getConnection();
        String ListarCodUltra = "SELECT Codigo FROM Tabela_Procedimentos_Ultrassonografia " +
                " WHERE Procedimentos = ?";
        
        pstmInput = conectar.prepareStatement(ListarCodUltra);
        pstmInput.setString(1, Procedimentos);
        rs = pstmInput.executeQuery();
        bcodultra.add(constprcultra);
        
        while (rs.next())
            {
                constprcultra.setCodigo(rs.getString("Codigo"));
                bcodultra.add(constprcultra);
            }
            pstmInput.close();
        }                    
        catch (SQLException e)
        {
            System.out.println(e.getMessage());
            e.printStackTrace();
            return null;
        }
        return bcodultra;
    }

And this is my access:

PacientesDAO_W01 pcdao = new PacientesDAO_W01();
            DadosPaciente dpaciente = new DadosPaciente();
            Bean_Procedimentos_Ultra bultra = new Bean_Procedimentos_Ultra();
            
            ArrayList<Bean_Procedimentos_Ultra> lbultra = new ArrayList<Bean_Procedimentos_Ultra>();
            lbultra = pcdao.lstCodUltra(procedimentos);
            codprocedimentos = lbultra.toString();
            System.out.println("Procedimento: " + procedimentos);
            
            System.out.println("entrando para gravar...");
            
            dpaciente.setCPF(prontuario);
            dpaciente.setNome(nome);
            dpaciente.setData_Nascimento(nascimento);
            dpaciente.setEndereco(endereco);
            dpaciente.setComplemento(complemento);
            dpaciente.setBairro(bairro);
            dpaciente.setCEP(cep);
            dpaciente.setCidade(cidade);
            dpaciente.setTelefone_Residencial(telefone);
            dpaciente.setCartao_SUS(cartaosus);
            dpaciente.setData_Exame(dtaExames);
            dpaciente.setHora(hora);
            dpaciente.setRequisitante(requisitante);
            dpaciente.setSexo(sexo);
            dpaciente.setData_Marcacao(datamarcacao);
            dpaciente.setProcedimento(procedimentos);
            System.out.println("Codigo antes de gravar: " + codprocedimentos);
            dpaciente.setCodigo_Procedimento(codprocedimentos);
            
            pcdao.cadastrarUltrassonografia(dpaciente);
            System.out.println("Executou pcdao para gravar...");
            request.getRequestDispatcher("EXM_Ultrassonografia_W001.jsp").forward(request, response);

The line 28 is to know the code returned, this is the result of the disply:

Codigo antes de gravar: [Bean_Procedimentos_Ultra{CNES=null, Procedimentos=null, Codigo=004}, Bean_Procedimentos_Ultra{CNES=null, Procedimentos=null, Codigo=004}]

My problem is that I can´t access the "Codigo".

Can you help me?

Thanks in advance.

Cezar Apulchro.

[Dave Tolls]

I think we need a bit more information.
What is the full error you are getting, and where is it occurring?

Do you know the value that is giving that error?
If the value is incorrect, what should the value be?

[Tim Moores]

Also post the description (column names and types) of the Tabela_Procedimentos_Ultrassonografia table.

[Randy Tong]

Set column dataType to LONGTEXT should fix the issue.

[Cezar Apulchro]

Hi Tim,
thank you for assistance.

This is my table:

CREATE TABLE Tabela_Procedimentos_Ultrassonografia(
	CNES VARCHAR(5),
	Procedimento VARCHAR(50),
	Codigo VARCHAR(5));

This is the content of my table:

INSERT INTO Tabela_Procedimentos_Ultrassonografia(CNES, Procedimentos, Codigo)
 VALUES
('12345', 'Transdutor linear', '002'),
('12345', 'Transdutor endocavitário', '003'),
('12345', 'Transdutor setorial', '004'),
('12345', 'Transdutores especiais', '005'),
('12345', 'Doppler', '006');

And the full error:

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'Codigo_Procedimentos' at row 1

The error occur  in line 31(pcdao.cadastrarUltrassonografia(dpaciente);) I believe caused by line 29(dpaciente.setCodigo_Procedimento(codprocedimentos);).
The result of this dysplay:

System.out.println("Codigo antes de gravar: " + codprocedimentos);

Is:
Codigo antes de gravar: [Bean_Procedimentos_Ultra{CNES=null, Procedimentos=null, Codigo=004}, Bean_Procedimentos_Ultra{CNES=null, Procedimentos=null, Codigo=004}]

CNES, Procedimentos and Codigo, are columns of my table and of my Bean. Note in this dysplay Codigo=004, the content of Codigo are ok, my problem is that I can't access this content in return of my Select.

Thanks.

[Tim Holloway]

It's extra challenging when not only the object, property and method names are not in English, but so are the error messages!

It appears that you have a fault in your registration method (cadastrarUltrassonografia), but unless I'm just blind today, you didn't show us what the PacientesDAO_W01 class that defines it looks like, so we have no idea what it's expecting.

The error message looks possibly like you have 2 patient records in it, and you're attempting to force them into a single database field - multiple columns AND multiple rows!

But that is only my best attempt at divination..

[Dave Tolls]

Data too long for column 'Codigo_Procedimentos'

That is not a column of the table you posted.

It looks like this is an error (as Tim says) from another method, not the one you have already posted.
From the stack trace is would be the cadastrarUltrassonografia method.

[Cezar Apulchro]

Hi Dave,

this is my method cadastrarUltrassonografia:

public void cadastrarUltrassonografia(DadosPaciente Tabela_Pacientes_Ultrassonografia)
    {
        String sqlInsertUltra = "INSERT INTO Tabela_Pacientes_Ultrassonografia " +
             "(CNES, Nome, Data_Nascimento, Sexo, Telefone_Residencial, Telefone_Celular," +
             " Endereco, Complemento, Bairro, CEP, Cidade, Cartao_SUS, Data_Exame," +
             " Requisitante, Hora, Data_Marcacao, Raca_Cor, Codigo_Procedimentos, " +
             " Procedimentos, Quantidade, Servico, Classificacao, CID_10, " +
             " Carater_de_Atendimento, Numero_de_Autorizacao, Numero, CPF_Prontuario) " +
             "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        
        try
        {
            
            PreparedStatement pstmInput;
            pstmInput = conectar.prepareStatement(sqlInsertUltra);
            
            pstmInput.setString( 1, ("123456"));
            /*pstmInput.setString( 1, Tabela_Pacientes_Ultrassonografia.getCnes());*/
            pstmInput.setString( 2, Tabela_Pacientes_Ultrassonografia.getNome());
            pstmInput.setString( 3, Tabela_Pacientes_Ultrassonografia.getData_Nascimento());
            pstmInput.setString( 4, Tabela_Pacientes_Ultrassonografia.getSexo());
            pstmInput.setString( 5, Tabela_Pacientes_Ultrassonografia.getTelefone_Residencial());
            pstmInput.setString( 6, Tabela_Pacientes_Ultrassonografia.getTelefone_Celular());
            pstmInput.setString( 7, Tabela_Pacientes_Ultrassonografia.getEndereco());
            pstmInput.setString( 8, Tabela_Pacientes_Ultrassonografia.getComplemento());
            pstmInput.setString( 9, Tabela_Pacientes_Ultrassonografia.getBairro());
            pstmInput.setString(10, Tabela_Pacientes_Ultrassonografia.getCEP());
            pstmInput.setString(11, Tabela_Pacientes_Ultrassonografia.getCidade());
            pstmInput.setString(12, Tabela_Pacientes_Ultrassonografia.getCartao_SUS());
            pstmInput.setString(13, Tabela_Pacientes_Ultrassonografia.getData_Exame());           
            pstmInput.setString(14, Tabela_Pacientes_Ultrassonografia.getRequisitante());           
            pstmInput.setString(15, Tabela_Pacientes_Ultrassonografia.getHora());           
            pstmInput.setString(16, Tabela_Pacientes_Ultrassonografia.getData_Marcacao());
            pstmInput.setString(17, Tabela_Pacientes_Ultrassonografia.getRaca_Cor());
            pstmInput.setString(18, Tabela_Pacientes_Ultrassonografia.getCodigo_Procedimento());
            pstmInput.setString(19, Tabela_Pacientes_Ultrassonografia.getProcedimento());
            pstmInput.setString(20, Tabela_Pacientes_Ultrassonografia.getQuantidade());         
            pstmInput.setString(21, Tabela_Pacientes_Ultrassonografia.getServico());
            pstmInput.setString(22, Tabela_Pacientes_Ultrassonografia.getClassificacao());
            pstmInput.setString(23, Tabela_Pacientes_Ultrassonografia.getCID_10());
            pstmInput.setString(24, Tabela_Pacientes_Ultrassonografia.getCarater_Atendimento());
            pstmInput.setString(25, Tabela_Pacientes_Ultrassonografia.getNum_Autorizacao());
            pstmInput.setString(26, Tabela_Pacientes_Ultrassonografia.getNumero());
            pstmInput.setString(27, Tabela_Pacientes_Ultrassonografia.getCPF());
    
            pstmInput.executeUpdate();
            pstmInput.close();
        }
        
        catch (SQLException e)
        {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }

    }

And this is piece of my bean:

public String getCodigo_Procedimento()
    {
        return this.Codigo_Procedimento;
    }
    
    public void setCodigo_Procedimento(String Codigo_Procedimento)
    {
        this.Codigo_Procedimento = Codigo_Procedimento;
    }

public String toString()
    {
        return "DadosPaciente{" + 
                 
                ", Codigo_Procedimento=" + Codigo_Procedimento + 

                 '}';
    }

My bean is very long.

Thanks.

[Dave Tolls]

pstmInput.setString(18, Tabela_Pacientes_Ultrassonografia.getCodigo_Procedimento());

Now, based on your first post, the value being used here is:
"
[Bean_Procedimentos_Ultra{CNES=null, Procedimentos=null, Codigo=004}, Bean_Procedimentos_Ultra{CNES=null, Procedimentos=null, Codigo=004}]
"
which has come from using the codprocedimentos String in the call to setCodigo_Procedimento.
And that String comes from:

codprocedimentos = lbultra.toString();

And lbultra is a List.

So, the question is, what exactly is supposed to be supplied to setCodigo_Procedimento?
What is it you actually want to go in there?

If it is (as it sounds like) the value of Codigo from that String, then which Codigo, as there are two (which happen to be the same in this example).

[Cezar Apulchro]

Dave I don't understand what you mean, I have an table with 3 collumns, I use only collumn 'Procedimentos' to populate a box to user choice in my jsp page. I get the user option and I want to get the code 'Codigo' of that option in my table. I have constructed another method but the problem still the same.

public Bean_Procedimentos_Ultra pegaCodUltra(
            String Procedimentos) throws Exception
    {
        System.out.println("DAO Entrando para pegar codigo...");
        System.out.println("Procedimento recebido: " + Procedimentos);
        Connection conectar = null;            
        ResultSet rs = null;            
        PreparedStatement pstmInput = null;
        Bean_Procedimentos_Ultra constprcultra = new Bean_Procedimentos_Ultra();
        
        conectar = new Conectar_Banco_W00().getConnection();
        
        String ListarCodUltra = "SELECT Codigo FROM Tabela_Procedimentos_Ultrassonografia " +
                " WHERE Procedimentos = ?";
        
        pstmInput = conectar.prepareStatement(ListarCodUltra);
        pstmInput.setString(1, Procedimentos);
        rs = pstmInput.executeQuery();
                
        if (rs.next())
            {
                constprcultra.setCodigo(rs.getString("Codigo"));
                System.out.println("Codigo do procedimento enviado: " + constprcultra);
            }
        else
            {        
                JOptionPane.showMessageDialog(null, "Erro ao pegar o código.");
                System.out.println("Que erro é esse: ");
            }
        pstmInput.close();
        
        return constprcultra;
    }

This method was constructed first, but has same problem, then I cosntructed the 'lstCodUltra' method.

Thanks.

[igoy kumar]

I think you need to increase the size of your table column.

[Tim Holloway]

You are leaking resources. If you get a SQLException, it causes the PreparedStatement.close() method to be bypassed.

What it looks like is that instead of sending the value of the Codigo_Procedimento - which appears to be a class instance, not a simple value - to the SQL preparer you are sending the value of the Codigo_Procedimento.toString() method, which is much longer than the actual data value, since it contains additional information about the Codigo_Procedimento which appears to be not a single value, but an entire class.

SQL isn't designed to stuff multiple objects into a single column. So you have to select an alternative:

1. Put the data into multiple columns, one column per data element
2. Encode the data into a single value (for example a String or serialized Java) and store it into a column large enough to hold it.
3. Write the data into a separate and distinct table and have your primary data reference that data (via a foreign key).

What it looks like you are attempting is option 2 but without a large enough column. Aside from needing enough space to hold the data, you'd also need a corresponding decode (deserialize) operation when you read the data back. And while I mentioned Java serialization as an alternate format, I don't recommend using it, because different JVM versions do it differently so you might not be able to deserialize it later.

[Dave Tolls]

Cezar Apulchro wrote:Dave I don't understand what you mean...

OK.

According to your second post above you are getting the error:
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'Codigo_Procedimentos' at row 1

That highlighted name is the column causing the issue.
That column is populated by the PreparedStatement code you posted just above here.
And this is the line that does that:

pstmInput.setString(18, Tabela_Pacientes_Ultrassonografia.getCodigo_Procedimento());

So your problem is that the value supplied there (which, if we work back up the code, comes from that toString call) is incorrect.

I am trying to find out what it should be.

[Cezar Apulchro]

Hi guys,
I solved my problem "Data truncation: Data too long for column".

Solution:

in mysql table: codigo varchar changed to int

in bean: I changed String to int

    public int getCodigo_Procedimento()
    {
        return this.Codigo_Procedimento;
    }
     
    public void setCodigo_Procedimento(int Codigo_Procedimento)
    {
        this.Codigo_Procedimento = Codigo_Procedimento;
    }
 
    }

thanks a lot everybody.

Cezar.