iOS, Android "Field may not be NULL" error

[CristianP]

Hello,

I have an issue for iOS and Android with following code when UniSQL is executing. For win32 is working.

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
var
  uSQL: TUniSQL;
begin
  uSQL := TUniSQL.Create(nil);
  uSQL.Connection := UniConnection1;
  uSQL.SQL.Text := 'INSERT INTO Test (Field1) VALUES (:P1)';
  uSQL.ParamByName('P1').AsString := '';
  uSQL.Execute;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
  UniConnection1.Database := TPath.GetDocumentsPath + PathDelim + 'test.db';
  UniConnection1.Open;
  UniConnection1.ExecSQL('CREATE TABLE IF NOT EXISTS Test (Field1 VARCHAR(20) NOT NULL)');
end;

Best Regards,
Cristian Peta

[AlexP]

Hello,

Thank you for the information, we have reproduced the problem and will try to fix it as soon as possible.

[Minoru]

Hello,

i have same problem with Unidac 5.1.3 in Oracle 11g.
Thanks.

[AlexP]

Hello,

We will fix the problem till the next release

[Minoru]

Hello,
Thanks for your support.

This is a serious problem.
I tried search about SetEmptyStrToNull in the source, and version down to V5.0.2 ...not able to find a workaround.

When is the next release? Is there a workaround or patch?

[AlexP]

Hello,

If you have a UniDAC version with source code, we can give you the code, that will fix the problem

[Minoru]

Hello,

Thanks for your support.
I have the source code, I will send an email to support at devart.com.
Please send to me that codes.
Thanks again.

[AlexP]

Hello,

To solve the problem, you should modify the class operator AnsiString method in the CRTypes.pas module as follows:

replace

Code: Select all

class operator AnsiString.Implicit(const Val: AnsiString): MarshaledAString;
begin
  Result := Val.FPtr;
end;

with

Code: Select all

class operator AnsiString.Implicit(const Val: AnsiString): MarshaledAString;
begin
  if Val.FPtr = nil then
    Result := #0
  else
    Result := Val.FPtr;
end;

[Minoru]

Hello,

Thanks for your support.
I'm trying it with Delph7. but not working.

>New Delphi language features since Delphi 7
http://edn.embarcadero.com/article/34324

The operator statement has been changed specifications from D2005(Not D7) is my understanding.

Is this code supported for Delph7?

[AlexP]

Hello,

This problem occurred in higher Delphi versions only when using the NEXTGEN directive for mobile platforms. We cannot reproduce the problem on Delphi 7, please send a small sample including ddl of the table reproducing the problem.

[Minoru]

Hello,

The same as the first post codes.

Code: Select all

procedure TForm1.FormCreate(Sender: TObject);
begin

  with UniConnection1 do
  begin
    if connected then connected := false;
    ProviderName := 'Oracle';
    SpecificOptions.Values['Direct'] := 'true';
    Server := '192.168.1.1:1521:XE';
    Username := 'xxxx';
    Password := 'xxxx';
    Open;
    //ExecSQL('CREATE TABLE Test (Field1 VARCHAR(20) NOT NULL)'); //First time
  end;
end;

procedure TForm1.Button1Click(Sender: TObject);
var
  uSQL: TUniSQL;
begin
  uSQL := TUniSQL.Create(nil);
  uSQL.Connection := UniConnection1;
  uSQL.SQL.Text := 'INSERT INTO Test (Field1) VALUES (:P1)';
  uSQL.ParamByName('P1').AsString := '';
  uSQL.Execute;
  uSQL.Free;
end;

Oracle said:
ORA-01400: cannot insert NULL into("xxxx"."TEST","FIELD1")

[AlexP]

Hello,

There is no 'empty string' term in Oracle. If you try to insert an empty string, Oracle will insert a Null value - therefore the issue occurs.

[Minoru]

Hello,

It was application side problem(FAQ of Oracle?). With the Firebird handling it works fine.
Thanks.

[AlexP]

Hello,

You can contact the Oracle support to get information about such behaviour.
To check the behaviour, you can use the following commands:

Code: Select all

SQL> create table varchar_null (f_null varchar2(10));

Table created.

SQL> insert into varchar_null values('');

1 row created.

SQL> select * from varchar_null where f_null = '';

no rows selected

SQL> select * from varchar_null where f_null is null;

F_NULL
----------