Error in Uni SQL with insert select statement and empty string parameter

[cointec]

Hello, I have observed the next problem using Unidac 6.4.14 with ORACLE. The same with FIREBIRD runs fine.

I have the next statement

Code: Select all

insert into TableA( FIELD1, FIELD2, FIELD3, FIELD4, FIELD5 )
select FIELD1, FIELD2, :PARAMSTR1, :PARAMINT2, :PARAMDATE3
from TableB
where PKTABLEB = 1

PARAMSTR1, is an string parameter
PARAMINT2 is an integer parameter
PARAMDATE3 is a Datetime parameter

If I run the query with the next values, no record is inserted
ParamByName( 'PARAMSTR1' ).Value := '' <- empty string
ParamByName( 'PARAMINT2' ).value  := 1
ParamByName( 'PARAMDATE3' ).Value := now

If I run the query with the next values, record is inserted
ParamByName( 'PARAMSTR1' ).Value := ' ' <- Now there is an space
ParamByName( 'PARAMINT2' ).value  := 1
ParamByName( 'PARAMDATE3' ).Value := now

If I run the query with the next values, record is inserted
ParamByName( 'PARAMSTR1' ).Value := 'x'
ParamByName( 'PARAMINT2' ).value  := 1
ParamByName( 'PARAMDATE3' ).Value := now

If I change the query and remove the string parameter, allways run fine

insert into TableA( FIELD1, FIELD2, FIELD4, FIELD5 )
select FIELD1, FIELD2,  :PARAMINT2, :PARAMDATE3
from TableB
where PKTABLEB = 1

ParamByName( 'PARAMINT2' ).value  := 1
ParamByName( 'PARAMDATE3' ).Value := now

It seems the problem is with an string parameter value empty

[cointec]

I have done another test

If I run the query with the next values, record is inserted
ParamByName( 'PARAMSTR1' ).Value := null
ParamByName( 'PARAMINT2' ).value := 1
ParamByName( 'PARAMDATE3' ).Value := now

If I set null instead of empty string, also runs fine

[MaximG]

Please send the script creating the tables used in your sample to maximg*devart*com

[MaximG]

You can publish the tables-creating script directly in this topic.

[cointec]

I have sent the script to you by email.
Thanks.

[MaximG]

We have received scripts for creating tables and checked UniDAC functioning according to your description. Unfortunately, we couldn't reproduce the issue with using empty string as a query parameter value. Try to perform the following steps.

1. Create the following tables:

Code: Select all

CREATE TABLE PET_PRUEBA1 (
    IDPETICIONPRUEBA Number(19) DEFAULT 0 NOT NULL,
    RESULTADO VarChar2(127 Char),
    CONSTRAINT PK_PET_PRUEBA1 PRIMARY KEY (IDPETICIONPRUEBA)
                         );

CREATE TABLE Pet_Prueba_resultado_Registro1 (
    IDPETICIONPRUEBA Number(19) DEFAULT 0 NOT NULL,
    RESULTADO VarChar2(127 Char),
    FECHA Timestamp(4) DEFAULT CURRENT_TIMESTAMP NOT NULL,
    IDUSUARIO Number(5) DEFAULT 0 NOT NULL,
    COMENTARIO VarChar2(512 Char)
                                            );

2.Insert the following row to the Pet_Prueba1 table:

Code: Select all

Insert Into PET_PRUEBA1 Values (4, 'RESULTADO Values');

3. Execute the following lines in your project code:

Code: Select all

 
...
 UniConnection.Connect;
  UniSQL.SQL.Text := 'Insert Into Pet_Prueba_resultado_Registro1 (IDPETICIONPRUEBA, RESULTADO, FECHA, IDUSUARIO, COMENTARIO)' +
                     '  Select pp.IDPETICIONPRUEBA, pp.RESULTADO, :FECHA, :IDUSUARIO, :COMENTARIO' +
                     '    From PET_PRUEBA1 pp' +
                     '   Where pp.IDPETICIONPRUEBA = :IDPETICIONPRUEBA';
  UniSQL.ParamByName('FECHA').Value := Now;
  UniSQL.ParamByName('IDUSUARIO').Value := 245;
  UniSQL.ParamByName('COMENTARIO').Value := '';
  UniSQL.ParamByName('IDPETICIONPRUEBA').Value := 4;
  UniSQL.Execute;
...

4. Make sure on record was inserted to the Pet_Prueba_resultado_Registro1 table after execution of this code

[cointec]

For me runs fine with 6.3.13 but nof in 6.4.14.
I'm using Direct Mode for oracle connection and Delphi 2007.

[cointec]

I have done a test with my original tables and Unidac 6.3.13 vs 6.4.14.

6.3.13=> Rows inserted = 1 rows affected = 1
6.4.14=> Rows inserted = 1 rows affected = 0

Code: Select all

TForm1 = class(TForm)
    UniConnection1: TUniConnection;
    UniQuery1: TUniQuery;
    UniQuery2: TUniQuery;
    coButton1: TcoButton;
    procedure coButton1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

procedure TForm1.coButton1Click(Sender: TObject);
var lRowsInserted, lRowsAffected: integer;
begin
   UniQuery1.SQL.Text := 'delete from PET_PRUEBA where IDPETICIONPRUEBA = 9000000000 ';
   UniQuery1.Execute;
   UniQuery1.Close;
   UniQuery1.SQL.Text := 'INSERT INTO PET_PRUEBA (IDPETICIONPRUEBA, IDPETICION, NPRUEBA, FECHA, IDPRUEBA, IDPRUEBA_LISTA, IDPRUEBA_PERFIL, IDPACIENTE, TIPO, ' +
                                                 'NIVEL, PREFIJO, NRESULTADO, RESULTADO, TRESULTADO, FECHA_RESULTADO, IDTIPORESULTADO, ' +
                                                 'IDVALIDACION, FECHA_VALIDACION, IDUSUARIO_VALIDA, PATOLOGIA, COMENTARIO, DELTA_CHECK, ' +
                                                 'MARGEN_ULTIMO, IDEQUIPO, IDCENTROPROCESO, IDPRUEBAREF, ORDEN_IMPRESION, FECHA_INFORME, ' +
                                                 'IDUSUARIO_RESULTADO, FECHA_ALTA, NREPETICION, IDPRUEBAMU, FECHA_PROCESO, NPRUEBA_LISTA, ' +
                                                 'NLISTA, REGISTRO_PROCESO, IDESTADORESULTADO, REGISTRO_PROTOCOLO, IDAREA, IDPETICIONMUESTRA, ' +
                                                 'IDPRUINTERPRETACION, IMPRIMIR, IDPETICION_ORIGEN, NPRUEBA_ORIGEN, NDOCUMENTO, IDVALIDACION_TECNICA, ' +
                                                 'IDUSUARIO_VALIDACION_TECNICA, FECHA_VALIDACION_TECNICA, IDPRUINTERPRETACIONRES, TRESULTADOE, ' +
                                                 'IDINSTRUMENTO, NRESULTADO_CORREGIDO, IDSECCION, IDPETICIONPLATO, FECHA_ENTREGA, PARAMETROS, ' +
                                                 'NALARMA, IDMETODO, IDUNIDAD, IDDOCUMENTO, FIRMA_DIGITAL, IDTIPOMUESTRAN, IDESTADOEXPORTACION, ' +
                                                 'IDTUBO, FIRMA_DIGITAL_FECHA ) ' +
                         'VALUES (9000000000, 16390, 2, TO_TIMESTAMP(''04/26/2016 00:00:00.000'', ''MM/DD/YYYY HH24:MI:SS.FF3''), ' +
                                  '10001, 11483, NULL, 8455, ''N'', 1, NULL, 67.4, ''067,4'', NULL, TO_TIMESTAMP(''04/25/2016 17:05:00.000'', ''MM/DD/YYYY HH24:MI:SS.FF3''), ' +
                                  '1, 4, TO_TIMESTAMP(''05/20/2016 11:56:05.000'', ''MM/DD/YYYY HH24:MI:SS.FF3''), ' +
                                  '0, 0, NULL, 0, 0, 26, 0, 10, 1200104, NULL, -2, ' +
                                  'TO_TIMESTAMP(''04/26/2016 09:57:31.000'', ''MM/DD/YYYY HH24:MI:SS.FF3''), ' +
                                  '1, 282, NULL, NULL, 0, 0, 10, 0, 10, NULL, NULL, 1, NULL, NULL, 0, 0, NULL, ' +
                                  'NULL, NULL, NULL, 26, NULL, 110, NULL, ' +
                                  'TO_TIMESTAMP(''04/27/2016 09:57:59.000'', ''MM/DD/YYYY HH24:MI:SS.FF3''), 0, 0, 0, 5, ' +
                                  'NULL, NULL, 20, NULL, 33, NULL) ';
   UniQuery1.Execute;
   Uniquery1.Close;

   UniQuery1.SQL.Text := 'select * from PET_PRUEBA where IDPETICIONPRUEBA = 9000000000 ';
   UniQuery1.Execute;
   lRowsInserted := UniQuery1.RecordCount;
   UniQuery1.Close;

   UniQuery2.SQL.Text := 'insert into Pet_Prueba_resultado_Registro( IDPETICIONPRUEBA, TIPO, IDTIPORESULTADO, FECHA_RESULTADO, IDUSUARIO_RESULTADO, ' +
                                                                                                    'PREFIJO, NRESULTADO, RESULTADO, TRESULTADO, ' +
                                                                                                    'FECHA_VALIDACION, IDUSUARIO_VALIDA, IDVALIDACION, ' +
                                                                                                    'IDESTADORESULTADO, IDINSTRUMENTO, FECHA_INFORME, FIRMA_DIGITAL, FECHA_PROCESO, FIRMA_DIGITAL_FECHA, ' +
                                                                                                    'COMENTARIO, IDUSUARIO, FECHA ) ' +
                                                        'select IDPETICIONPRUEBA, TIPO, IDTIPORESULTADO, FECHA_RESULTADO, IDUSUARIO_RESULTADO, ' +
                                                               'PREFIJO, NRESULTADO, RESULTADO, TRESULTADO, ' +
                                                               'FECHA_VALIDACION, IDUSUARIO_VALIDA, IDVALIDACION, ' +
                                                               'IDESTADORESULTADO, IDINSTRUMENTO, FECHA_INFORME, FIRMA_DIGITAL, FECHA_PROCESO, FIRMA_DIGITAL_FECHA, ' +
                                                               ':COMENTARIO, :IDUSUARIO, :FECHA ' +
                                                        'from Pet_Prueba pp ' +
                                                        'where pp.IDPETICIONPRUEBA = :IDPETICIONPRUEBA';
   UniQuery2.ParamByName('FECHA').Value := Now;
   UniQuery2.ParamByName('IDUSUARIO').Value := 245;
   UniQuery2.ParamByName('COMENTARIO').Value := '';
   UniQuery2.ParamByName('IDPETICIONPRUEBA').Value := 9000000000;
   UniQuery2.Execute;
   lRowsAffected := UniQuery2.RowsAffected;

   coButton1.Caption := Format( 'Executes rows inserted %d affected %d ', [lRowsInserted, lRowsAffected ] );
end;

[MaximG]

Please, specify the used Oracle version and NLS parameters of the database instance.

[cointec]

Oracle 12C
PARAMETER;VALUE
"NLS_RDBMS_VERSION";"12.1.0.2.0"
"NLS_NCHAR_CONV_EXCP";"FALSE"
"NLS_LENGTH_SEMANTICS";"BYTE"
"NLS_COMP";"BINARY"
"NLS_DUAL_CURRENCY";"$"
"NLS_TIMESTAMP_TZ_FORMAT";"DD-MON-RR HH.MI.SSXFF AM TZR"
"NLS_TIME_TZ_FORMAT";"HH.MI.SSXFF AM TZR"
"NLS_TIMESTAMP_FORMAT";"DD-MON-RR HH.MI.SSXFF AM"
"NLS_TIME_FORMAT";"HH.MI.SSXFF AM"
"NLS_SORT";"BINARY"
"NLS_DATE_LANGUAGE";"AMERICAN"
"NLS_DATE_FORMAT";"DD-MON-RR"
"NLS_CALENDAR";"GREGORIAN"
"NLS_NUMERIC_CHARACTERS";".,"
"NLS_NCHAR_CHARACTERSET";"AL16UTF16"
"NLS_CHARACTERSET";"AL32UTF8"
"NLS_ISO_CURRENCY";"AMERICA"
"NLS_CURRENCY";"$"
"NLS_TERRITORY";"AMERICA"
"NLS_LANGUAGE";"AMERICAN"

[MaximG]

Thank you for the information. We have reproduced the issue, and we will inform you about the results shortly.

[MaximG]

Thank you for the information. We have reproduced the problem and fixed the bug. The fix will be included in the next UniDAC build. Currently, we can send you a night build of UniDAC with the fix. For this, please specify your license number and the exact version of Delphi you are using to maximg*devart*com

[MaximG]

A link to download the night build was sent to your email address