Field Size Error when running Insert in Postgresql in Delphi application

Question

I have a problem that could only be happening on a Friday #13.

I have an application in Delphi that controls SEDEX submissions and use the Postgresql database.

In a given column, called sdx_cepnet, store the information that generates the Barcode. This information comes from a field in the form (a Tmaskedit masked 00000-000;0;)

From the contents of this field I apply a formula to add a check digit and include a control character at the beginning (start) and in the end (stop) of the bar code.

Ex.: Assuming the zip code 12345-678 the CEPNET will be /123456784\ - 4 is DV, / is start character, stop character.

So far everything happens as the costume says, all values arrive correct to SQL statement.

Only when executing the INSERT statement it fails with the very long value error for the char(11) field (which I have already confirmed to be the sdx_cepnet fields, due to the doubts).

Follows relevant part of the DDL of the table

CREATE TABLE public.tbsdx (
  sdx_numobj NUMERIC(9,0),
  sdx_siglaobj CHAR(2),
  sdx_paisorigem CHAR(2),
  sdx_cep CHAR(8),
  sdx_numobj2 VARCHAR(13),
  sdx_cepnet CHAR(11), -- << CAMPO EM QUESTÃO
-- (...)
) WITH (oids = true);

Unit code

  // (...) Outros códigos

  // Início Calculo DV CEP
  v_soma := 0;
  for i:= 1 to Length(MkEdCep.Text) do
    v_soma := v_soma + StrToInt(copy(MkEdCep.Text, i, 1));

  v_soma := v_soma Mod 10;
  if v_soma > 0 then
      v_soma := 10 - v_soma;

  CEPr := Format('/%s%d\', [MkEdCep.Text, v_soma]);
  v_soma := Length(CEPr); // DEBUG
  // Fim Calculo DV CEP

  with dm do
    begin
      SqlAux1.Close;
      SqlAux1.SQL.Clear;
      SqlAux1.SQL.Add('UPDATE tbsdx ');
      SqlAux1.SQL.Add('SET sdx_cepnet = :cepnet, sdx_valor = :valor, ');
      // (...) Outros campos
      SqlAux1.SQL.Add('WHERE sdx_numobj2 = :numboj2 ');
      SqlAux1.ParamByName('cepnet').AsString := CEPr;
      SqlAux1.ParamByName('valor').AsFloat := Moeda2Float(EdValor.Text);
      SqlAux1.ParamByName('numboj2').AsString := EdObjeto.Text;
      try
        SqlAux1.ExecSQL; // Executando a instrução
        if SqlAux1.RowsAffected > 0 then
     // ...continua

Follow the DEBUG and error

What I’ve tried hasn’t worked

• Place *'/123456784 ' directly in the CEPR variable
• Trim
• Use copy(string, 1, 11)

What makes the statement run correctly

• Use copy(string, 1, 10)
• Use '12345678940'

The question is: How to make the field accept the correct value??

Answer 1

I do not recommend you use a CHAR field to store this type of information, switch to VARCHAR and run the tests. CHAR fields are not good to handle some symbols! Not to mention wasting space to store information!

Here you can find more information on the subject: Types for strings of characters.

Answer 2

After a long time of testing and research I found out why the problem was happening.

I blindly believed that using the methods of Bind of lib ZEOS was the same as in other objects of other languages, such as Hibernate or PDO, but was completely wrong.

The method .Bindbyparam does not escape characters. It just replaces the placeholder with the past content. The same happens with .Params[Indice] and how \ is a special character for Postgresql, he understood that I was escaping the next character, making the mistake happen.

Probably this problem is not unique to the use of ZEOS with Postgresql. It should also happen with Oracle and SQL Server.

The solution was to use the Postgresql escape operator (And) in string base.

SqlAux1.SQL.Add('SET sdx_cepnet = E:cepnet, ');

what will make the code

SqlAux1.ParamByName('cepnet').AsString := '/123456784\';

result in

SqlAux1.SQL.Add('SET sdx_cepnet = E''/123456784\'', '); // '' É a forma do Delphi de escapar aspas em uma string

More details about correct escape of PGSQL characters can be found in the official documentation at http://www.postgresql.org/docs/9.1/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-ESCAPE