Field Size error when executing Insert in PostgreSQL in Delphi application

Question

Field Size error when executing Insert in PostgreSQL in Delphi application

Navigation

#1 by (0 votes)
#2 by (0 votes)

2

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

I have a Delphi application that controls SEDEX submissions and I use the PostgreSQL Database.

In a given column, called sdx_cepnet , I store the information that the Barcode generates. This information comes from a form field (a TMaskEdit with mask 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 start (start) and at the end ( stop ) barcode.

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

So far, everything happens the way the costume goes, all the values come right to the SQL statement.

Follows relevant part of table's DDL

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 DEBUG and the error

What I've tried that did not work

Put '* / 123456784 \' directly into the CEPr variable
Use Trim
Use copy (string, 1, 11)

What causes the statement to execute correctly

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

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

delphi postgresql

asked by anonymous 13.11.2015 / 23:02

2 answers

0

I do not recommend using a CHAR field to store this type of information, switch to VARCHAR, and run the tests. CHAR fields are not good for handling some symbols! Not to mention the waste of space to store information!

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

14.11.2015 / 01:05

"java.lang.SecurityException: Permission Denial" on Android 6 Marshmallow (API 23) How to add a dynamic footer in WordPress?

score 0 · Accepted Answer

After a good amount of testing and research I discovered why the problem happened.

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

The .BindByParam method does not escape characters. Just replace the placeholder with the last content. The same thing happens with .params and as \ is a special character for PostgreSQL, it understood that I was escaping the next character, causing the error to happen .

Probably this problem is not unique to using ZEOS with PostgreSQL. It should also happen with Oracle and SQL Server.

The solution was to use the PostgreSQL ( E ) escape operator in the base string.

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

This 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

Further details on correct character escaping in PGSQL can be found in the official documentation at link