Pages: 1

#1 2013-12-07 04:25:36

ncook
Member
From: Australia
Registered: 2013-08-13
Posts: 19

We need to store NULL's in the database instead of empty strings

Hi Arnaud.

I have a second problem related to the minimal sample code I posted on the previous topic "Assertion failure in mORMot.pas with an empty string field"...

type
  TSQLNullTest1 = class(TSQLRecord)
  private
    fText: RawUTF8;
    fMemo: RawUTF8;
  published
    property fldText: RawUTF8 index 255 read fText write fText;
    property fldMemo: RawUTF8 read fMemo write fMemo;
  end;

procedure TfrmMORMmot_Dev_Main.btnNullTest1Click(Sender: TObject);
const
  c_DbFileExtension = '.mdb';
var
  DbFileSpec: string;
  Conn: TOleDBConnectionProperties;
  Model: TSQLModel;
  Client: TSQLRestClientDB;
  Obj: TSQLNullTest1;
begin
  DbFileSpec := ChangeFileExt(application.ExeName, c_DbFileExtension);
  Conn := TOleDBJetConnectionProperties.Create(DbFileSpec, '', '', '');
  //Conn := TOleDBMSSQL2008ConnectionProperties.Create(c_SQLTest_ServerName, c_SQLTest_DatabaseName, c_SQLTest_UserName, c_SQLTest_Password);
  try
    Model := TSQLModel.Create([TSQLNullTest1]);
    try
      VirtualTableExternalRegister(Model, TSQLNullTest1, Conn, 'tblNullTest1');

      Client := TSQLRestClientDB.Create(Model, nil, '', TSQLRestServerDB);
      try
        Client.Server.CreateMissingTables;

        Obj := TSQLNullTest1.Create;
        try
          Obj.fldText := '';
          Obj.fldMemo := '';

          Client.Add(Obj, True);
        finally
          Obj.Free;
        end;
      finally
        Client.Free;
      end;
    finally
      Model.Free;
    end;
  finally
    Conn.Free;
  end;
end;

We have been using this sample code (with a work around in place for the bug from the previous topic) to experiment with the way mORMot stores empty string values in the fields in the tables, and we have found that rather than storing NULL values in the Text fields as we expected, it actually stores the empty string values instead. That is causing us all sorts of problems...

a) If the target database is a Jet 3 file (e.g. from Access 97, which unfortunately we must still support for many clients, for backwards compatibility with other applications that cannot be upgraded or modified) the code fails with an error, because that version of the Jet database does not allow an empty string value to be stored in a Text field. At least not without modifying the table definition manually (in the MS-Access IDE), by changing the "Allow zero length" property from it's default "No" to "Yes" for every text field, which we cannot do, even if we wanted to.

NOTE: The text fields that are created in the table by mORMot are set to NOT allow zero length strings (the Jet default), so in this case mORMot cannot even write an empty string value to the tables & fields that it has created itself.

b) Even if we could change the table definitions to allow empty strings to be stored, or if we use a Jet 4 database file (which seems to allow empty strings to be stored even when the field definition indicates they should not be allowed), or an MS SQL database, then storing empty strings instead of NULL's still causes backwards compatibility problems with other applications (that cannot be upgraded or modified). These applications are using "IS NULL" tests in their SQL WHERE clauses, which do not match the records with the empty string fields. This results in records that were written by mORMot not appearing in several queries or reports.

c) In a Jet database (any version we've tested) storing an NULL in a MEMO field uses almost no space, whereas storing an empty string in a MEMO field causes a whole database page to be allocated (to store the empty string), thus causing significant bloat in the database.

d) For an field which normally contains NULL values most of the time, but for which we need to be able to very quickly select (in SQL) the few records with non-Null values, we can add an index which excludes nulls, and that index stays very short and fast, as it only contains entries for the records not containing nulls. However when the field is populated with empty strings instead of Nulls, these are not ignored by the index, so the index size bloats and it becomes much slower.

etc, etc...

We are completely happy with the way mORMot handles NULL field values when it is reading data, but we need to find a way for it to be able to write NULL's instead of empty strings to the Text and Memo fields in the tables.

We have been trying to follow through the code (both statically and by tracing the execution) but so far we have not been able to find exactly how and where this decision is made.

Is there anything that you can think of that we can do as a work around for this problem, so that we can get mORMot to store NULL values instead of empty strings?

Offline

#2 2013-12-07 20:09:04

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,272
Website

Re: We need to store NULL's in the database instead of empty strings

Weird behavior indeed.
I was not expecting it.
sad
It will indeed depend on the database back-end.

With the Sqlite3 kernel, there won't be any problem.

A database which is not able to store '' is a buggy database.
Do you know if only JET is potentially affected? Do you know any other DB which does not allow zero length strings by default?

Then we can easily add an internal parameter to store null instead of '' for such databases.
What do you think?

Offline

#3 2013-12-07 22:05:36

ncook
Member
From: Australia
Registered: 2013-08-13
Posts: 19

Re: We need to store NULL's in the database instead of empty strings

Arnaud.
Thanks for responding.
With so many details in my post, I must have given you a slightly wrong impression. Sorry about that.

Jet can store '' in it's text fields, but Not by default. You must first manually change the properties of the text & memo field definitions in the table, to enable the storing of zero length strings. By default this property is set to False, including the Text & Memo fields created by mORMot itself.

But beyond that, we need mORMot to store empty strings as NULL instead of '', for backward compatibility with other applications that need to read the same database, for all the reasons listed in my original post. I expect that other people needing to build mORMot based applications that will work well with other applications sharing the same data may also have similar issues. Especially when it is mORMot that is generating the data, not just being the consumer of data that has been generated externally.

So Yes, an internal parameter to store NULL instead of '' for an empty string would be a great solution.

But not only for Jet databases. At the minimum we will also need this for MS SQL databases also.
The best solution (IMHO) would be to make this parameter available across the whole system for all databases, but I understand that each databases behaves differently and so this may not be needed or wanted in all of them (e.g. not for Sqlite3).

Because of backwards compatibility with other applications, the accurate and controllable storage of NULL's in the database is very important to us.
As the use of mORMot becomes more widespread I expect you will find other people with the same requirements, as sharing a database between applications is very common, and several applications that we work with expect empty strings to be stored as NULL rather than ''.

Last edited by ncook (2013-12-07 22:06:36)

Offline

#4 2013-12-08 01:13:24

mingda
Member
Registered: 2013-01-04
Posts: 121

Re: We need to store NULL's in the database instead of empty strings

ncook wrote:

Arnaud.

But beyond that, we need mORMot to store empty strings as NULL instead of ''

I also think so, In my test against MSSQL, I found this behavior, now I use BatchAppend/BatchUpdate
to update data. Before post data, I check the data is empty, if empty, then exclude it in the updatebit
parameter,  this will make empty string will not update to ''.

Offline

#5 2013-12-08 15:56:16

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,272
Website

Re: We need to store NULL's in the database instead of empty strings

I've added TSQLDBConnectionProperties.StoreVoidStringAsNull, which will be set e.g. for MS SQL and Jet databases which do not allow by default to store '' values, but expect NULL instead.
See http://synopse.info/fossil/info/549ab3276e

It should handle now simple binding or array binding.

Feedback is welcome!

Offline

#6 2013-12-08 22:10:40

ncook
Member
From: Australia
Registered: 2013-08-13
Posts: 19

Re: We need to store NULL's in the database instead of empty strings

Thanks Arnaud.
Especially for doing this on a Sunday.  Wow, that really is great support!

I'll be testing this today (Australian time, overnight your time) and I'll let you know the outcome.

Offline

#7 2013-12-09 00:02:07

ncook
Member
From: Australia
Registered: 2013-08-13
Posts: 19

Re: We need to store NULL's in the database instead of empty strings

Arnaud.
The modification has been only partly successful.
It works perfectly for MS SQL, but unfortunately not for Jet.

Now when using Jet, attempting to set an empty string value raises an exception from deep within the TOleDBConnection class.
The exception is "EOleDBException ("OLEDB Error 80040E21 - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.")"

This is being raised in TOleDBConnection.OleDBCheck.EnhancedTest at the "raise E;" line near the bottom.
However the problem is coming from TOleDBStatement.ExecutePrepared at his line near the bottom "OleDBConnection.OleDBCheck(fCommand.Execute(nil,DB_NULLGUID,fDBParams,@fUpdateCount,nil));"

I'll include an extract from the log file in case it is useful. In this case I was attempting to set Null into 2 text fields in the same record...

20131209 09402152  +    TSQLRestServerDB(01FB8A40).root/NullTest1
20131209 09402152 SQL   	TOleDBStatement(01F50370) select max(ID) from tblNullTest1
20131209 09402152  +    	TOleDBStatement(01F50370).00112B9C SynOleDB.TOleDBStatement.ExecutePrepared (1522) 
20131209 09402152  -    	TOleDBStatement(01F50370).00113077 SynOleDB.TOleDBStatement.ExecutePrepared (1612)  00.004.230
20131209 09402152  +    	TOleDBStatement(01F50370).CreateAccessor
20131209 09402152  -    	TOleDBStatement(01F50370).CreateAccessor 00.000.010
20131209 09402152 SQL   	TOleDBStatement(01F50510) insert into tblNullTest1 (ID,fldText,fldMemo) VALUES (?OOR?,?OOR?,?OOR?)
20131209 09402152  +    	TOleDBStatement(01F50510).00112B9C SynOleDB.TOleDBStatement.ExecutePrepared (1522) 
20131209 09402152 ERROR 		"EOleDBException(0205E340)":"OLEDB Error 80040E21 - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." stack trace API 001148A7 SynOleDB.EnhancedTest (2038) 001149AB SynOleDB.TOleDBConnection.OleDBCheck (2044) 00113027 SynOleDB.TOleDBStatement.ExecutePrepared (1605) 001BAF9D mORMotDB.TSQLRestServerStaticExternal.ExecuteFromJSON (1529) 001B9147 mORMotDB.TSQLRestServerStaticExternal.EngineAdd (1025) 001A0F58 mORMot.TSQLRestServerURIContext.ExecuteORMWrite (24405) 001A23A0 mORMot.TSQLRestServer.URI (24866) 001BE685 mORMotSQLite3.TSQLRestClientDB.InternalURI (1479) 0019CB2A mORMot.TSQLRestClientURI.URI (22689) 0019CFAA mORMot.TSQLRestClientURI.EngineAdd (23019) 001A7404 mORMot.TSQLRestClient.Add (27484) 001C2CF9 mORMot_Dev_TestGroup3.TfrmMORMot_Dev_TestGroup3.btnNullTest1Click (121) 000A8422 Controls.TControl.Click 000E4EAE RzButton.TRzCustomButton.MouseUp 000A8825 Controls.TControl.DoMouseUp 000ABF14 Controls.TWinControl.WndProc 000AB63B Controls.TWinControl.MainWndProc 00027F9A Classes.StdWndProc 
20131209 09402351 EXC   		EOleDBException ("OLEDB Error 80040E21 - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.") at 001148AF SynOleDB.EnhancedTest (2039)  stack trace 00001007 System.SysFreeMem 00002211 System.@ReallocMem 00005C61 System.DynArraySetLength 000F6BE4 SynCommons.TDynArrayHashed.HashFind (23430) 000F6BE4 SynCommons.TDynArrayHashed.HashFind (23430) 000FC836 SynCommons.TRawUTF8ListHashed.Changed (27652) 00002211 System.@ReallocMem 000F5E59 SynCommons.TDynArray.InternalSetLength (22833) 000F5F67 SynCommons.TDynArray.SetCount (22870) 00111568 SynOleDB.TOleDBStatement.CheckParam (1125) 001BAF9D mORMotDB.TSQLRestServerStaticExternal.ExecuteFromJSON (1529) 0017261D SynSQLite3Static.sqlite3_create_collation (1355) 001B9147 mORMotDB.TSQLRestServerStaticExternal.EngineAdd (1025) 001A0F58 mORMot.TSQLRestServerURIContext.ExecuteORMWrite (24405) 001A23A0 mORMot.TSQLRestServer.URI (24866) 0000DD9B SysUtils.EncodeTime 
20131209 09402353 ERROR 		"EOleDBException(0205E340)":"OLEDB Error 80040E21 - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." stack trace API 00113052 SynOleDB.TOleDBStatement.ExecutePrepared (1609) 001BAF9D mORMotDB.TSQLRestServerStaticExternal.ExecuteFromJSON (1529) 001B9147 mORMotDB.TSQLRestServerStaticExternal.EngineAdd (1025) 001A0F58 mORMot.TSQLRestServerURIContext.ExecuteORMWrite (24405) 001A23A0 mORMot.TSQLRestServer.URI (24866) 001BE685 mORMotSQLite3.TSQLRestClientDB.InternalURI (1479) 0019CB2A mORMot.TSQLRestClientURI.URI (22689) 0019CFAA mORMot.TSQLRestClientURI.EngineAdd (23019) 001A7404 mORMot.TSQLRestClient.Add (27484) 001C2CF9 mORMot_Dev_TestGroup3.TfrmMORMot_Dev_TestGroup3.btnNullTest1Click (121) 000A8422 Controls.TControl.Click 000E4EAE RzButton.TRzCustomButton.MouseUp 000A8825 Controls.TControl.DoMouseUp 000ABF14 Controls.TWinControl.WndProc 000AB63B Controls.TWinControl.MainWndProc 00027F9A Classes.StdWndProc 
20131209 09402353 EXC   		EOleDBException ("OLEDB Error 80040E21 - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.") at 001148AF SynOleDB.EnhancedTest (2039)  stack trace API 00102CC4 SynCommons.SynRtlUnwind (32472) 00003B58 System.@HandleAnyException 0007A58B ExceptionLog.Call_HookedRaise (15678) 001148AF SynOleDB.EnhancedTest (2039) 001149AB SynOleDB.TOleDBConnection.OleDBCheck (2044) 00113027 SynOleDB.TOleDBStatement.ExecutePrepared (1605) 001BAF9D mORMotDB.TSQLRestServerStaticExternal.ExecuteFromJSON (1529) 001B9147 mORMotDB.TSQLRestServerStaticExternal.EngineAdd (1025) 001A0F58 mORMot.TSQLRestServerURIContext.ExecuteORMWrite (24405) 001A23A0 mORMot.TSQLRestServer.URI (24866) 001BE685 mORMotSQLite3.TSQLRestClientDB.InternalURI (1479) 0019CB2A mORMot.TSQLRestClientURI.URI (22689) 0019CFAA mORMot.TSQLRestClientURI.EngineAdd (23019) 001A7404 mORMot.TSQLRestClient.Add (27484) 001C2CF9 mORMot_Dev_TestGroup3.TfrmMORMot_Dev_TestGroup3.btnNullTest1Click (121) 000A8422 Controls.TControl.Click 000E4EAE RzButton.TRzCustomButton.MouseUp 000A8825 Controls.TControl.DoMouseUp 
20131209 09402353  -    	TOleDBStatement(01F50510).00113077 SynOleDB.TOleDBStatement.ExecutePrepared (1612)  02.008.380
20131209 09402353 srvr  	POST root/NullTest1 ERROR=400 (Bad Request)
20131209 09402353  -    TSQLRestServerDB(01FB8A40). 02.013.517
20131209 09402353 ERROR TSQLRestClientDB(01F87FB0) POST root/NullTest1 returned 400 Bad Request with message  {  "ErrorCode":400,  "ErrorText":"Bad Request"  } stack trace API 0019CCB3 mORMot.TSQLRestClientURI.URI (22719) 0019CFAA mORMot.TSQLRestClientURI.EngineAdd (23019) 001A7404 mORMot.TSQLRestClient.Add (27484) 001C2CF9 mORMot_Dev_TestGroup3.TfrmMORMot_Dev_TestGroup3.btnNullTest1Click (121) 000A8422 Controls.TControl.Click 000E4EAE RzButton.TRzCustomButton.MouseUp 000A8825 Controls.TControl.DoMouseUp 000ABF14 Controls.TWinControl.WndProc 000AB63B Controls.TWinControl.MainWndProc 00027F9A Classes.StdWndProc 
20131209 09402353 info  TSQLRestServerDB(01FB8A40) {   "ClientsCurrent": 0,   "ClientsMax": 0,   "Invalid": 1,   "Responses": 1,   "Modified": 0,   "IncomingBytes": 172,   "OutcomingBytes": 132,   "OutcomingFiles": 0,   "ServiceCalls": 1,   "ProcessTime": "2.01s"  }

I've tried Jet V3 and V4 databases with the same results.
This is not dependant on the value of the "Allow Zero Length" property of the text field. Having that set True or False makes no difference.

Actually in some ways this is a move forward, because there is now consistency between this and another new issue I was going to raise today. Previously this would have been a different problem. So fixing one problem will probably fix both of them.

I have been getting exactly the same error when I have been attempting to use other techniques to write NULL values into fields of other data types (not text fields). I have been trying 2 techniques.

The first technique I have tried is by using a variant in Delphi, like this...

type
  TSQLNullTest2 = class(TSQLRecord)
  private
    fText: RawUTF8;
    fDouble: Variant; // This is a Double field in the database table.
  published
    property fldText: RawUTF8 index 255 read fText write fText;
    property fldDouble: Variant read fDouble write fDouble;
  end;

  // Use this class when creating the table.
  TSQLNullTest2_Create = class(TSQLRecord)
  private
    fText: RawUTF8;
    fDouble: Double;
  published
    property fldText: RawUTF8 index 255 read fText write fText;
    property fldDouble: Double read fDouble write fDouble;
  end;

procedure TfrmMORMot_Dev_TestGroup3.btnNullTest2_AppendClick(Sender: TObject);
var
  Conn: TOleDBConnectionProperties;
  Model: TSQLModel;
  Client: TSQLRestClientDB;
  Obj: TSQLNullTest2;
begin
  Conn := CreateTestDbConn;
  try
    Model := TSQLModel.Create([TSQLNullTest2]);
    try
      VirtualTableExternalRegister(Model, TSQLNullTest2, Conn, 'tblNullTest2');

      Client := TSQLRestClientDB.Create(Model, nil, '', TSQLRestServerDB);
      try
        Client.Server.CreateMissingTables; // The table will already exist in the database.

        Obj := TSQLNullTest2.Create;
        try
          Obj.fldText := 'xx';
          Obj.fDouble := Null

          Client.Add(Obj, True);
        finally
          Obj.Free;
        end;
      finally
        Client.Free;
      end;
    finally
      Model.Free;
    end;
  finally
    Conn.Free;
  end;
end;

This technique seems to work perfectly when reading the data, and works fine when writing a normal (non-Null) value into the fldDouble field.
It only seems to fail when attempting to write a Null value into the fldDouble field in a Jet database. (MS SQL works fine)
And it fails with exactly the same exception, in exactly the same way.

The second technique I have tried uses the InternalRegisterCustomProperties feature, like this...

type
  TExactHandicap = Double;

  TSQLNullTest3 = class(TSQLRecord)
  private
    fText: RawUTF8;
    fExactHandicap: Double;

    function getExactHandicap: TExactHandicap;
    procedure setExactHandicap(aNewValue: TExactHandicap);

  protected
    class procedure InternalRegisterCustomProperties(Props: TSQLRecordProperties); override;

  public
    property fldExactHandicap: TExactHandicap read getExactHandicap write setExactHandicap;

  published
    property fldText: RawUTF8 index 50 read fText write fText;
  end;

  // Use this class when creating the table.
  TSQLNullTest3_Create = class(TSQLRecord)
  private
    fText: RawUTF8;
    fExactHandicap: Double;
  published
    property fldText: RawUTF8 index 50 read fText write fText;
    property fldExactHandicap: Double read fExactHandicap write fExactHandicap;
  end;

{ TSQLNullTest3 }

function TSQLNullTest3.getExactHandicap: TExactHandicap;
begin
  Result := fExactHandicap;
end;

procedure TSQLNullTest3.setExactHandicap(aNewValue: TExactHandicap);
begin
  fExactHandicap := aNewValue;
end;

procedure ExactHandicap_RawData2Text(aRawDataIn: Pointer; aRawDataLength: Integer; var arefTextOut: RawUTF8); // TOnSQLPropInfoRecord2Text signature
const
  c_ExactHandicap_Null = 'null';
  c_ExactHandicap_Invalid = -99;
var
  ExactHandicap: Double;
begin
  arefTextOut := c_ExactHandicap_Null;

  if (aRawDataLength = SizeOf(Double)) then begin
    ExactHandicap := PDouble(aRawDataIn)^;

    if (ExactHandicap <> c_ExactHandicap_Invalid) then arefTextOut := StringToUTF8(FloatToStr(ExactHandicap));
  end;
end;

procedure ExactHandicap_Text2RawData(aTextIn: PUTF8Char; var arefRawDataOut: RawByteString); // TOnSQLPropInfoRecord2Data signature
const
  c_ExactHandicap_Null = 'null';
  c_ExactHandicap_Invalid = -99;
var
  ExactHandicap: Double;
begin
  if (aTextIn = c_ExactHandicap_Null) then ExactHandicap := c_ExactHandicap_Invalid
  else if (aTextIn = '') then              ExactHandicap := c_ExactHandicap_Invalid
  else                                     ExactHandicap := StrToFloat(aTextIn);

  SetString(arefRawDataOut, PAnsiChar(@ExactHandicap), SizeOf(Double));
end;

class procedure TSQLNullTest3.InternalRegisterCustomProperties(Props: TSQLRecordProperties);
begin
  inherited;

  Props.RegisterCustomFixedSizeRecordProperty(Self, SizeOf(Double), 'fldExactHandicap', @TSQLNullTest3(nil).fExactHandicap, [], 0, ExactHandicap_RawData2Text, ExactHandicap_Text2RawData);
end;

procedure TfrmMORMot_Dev_TestGroup3.btnNullTest3_AppendClick(Sender: TObject);
const
  c_ExactHandicap_Invalid = -99;
var
  Conn: TOleDBConnectionProperties;
  Model: TSQLModel;
  Client: TSQLRestClientDB;
  Obj: TSQLNullTest3;
begin
  Conn := CreateTestDbConn;
  try
    Model := TSQLModel.Create([TSQLNullTest3]);
    try
      VirtualTableExternalRegister(Model, TSQLNullTest3, Conn, 'tblNullTest3');

      Client := TSQLRestClientDB.Create(Model, nil, '', TSQLRestServerDB);
      try
        Client.Server.CreateMissingTables; // The table will already exist in the database.

        Obj := TSQLNullTest3.Create;
        try
          Obj.fldText := 'xx';
          Obj.fExactHandicap := c_ExactHandicap_Invalid;

          Client.Add(Obj, True);
        finally
          Obj.Free;
        end;
      finally
        Client.Free;
      end;
    finally
      Model.Free;
    end;
  finally
    Conn.Free;
  end;
end;

The concept here is that occasionally for some fields, the database concept of NULL (meaning "Not specified" or "Invalid") does not map well to the value Zero. This happens when Zero is a valid value for the field, and it must be distinguishable from "Not specified" or "Invalid".
Internally in Delphi, we define a different value for "Invalid" which is -99 in this case.

The custom property is attempting to map between -99 in Delphi and NULL in the database field.

This technique seems to work perfectly when reading the data, and works fine when writing a normal (non-Null) value into the fldExactHandicap field.
It only seems to fail when attempting to write a Null value into the fldExactHandicap field in a Jet database. (MS SQL works fine)
And it fails with exactly the same exception, in exactly the same way.

So hopefully, if we can fix the first problem, these other two techniques for storing NULL fields in the database will also be fixed.

PS: As a side issue, I would appreciate any feedback you may have on the two techniques I am trying to use, either on the concepts, or on the code itself.

Offline

#8 2013-12-09 09:01:36

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,272
Website

Re: We need to store NULL's in the database instead of empty strings

This is pretty weird.
There is no difference between MS SQL and Jet, despite the OleDB connection string...

But perhaps there is something wrong with our implementation, since I discovered problems when running OleDB on 64 bit.
When running targeting Win64, MS SQL perf tests do trigger a "OLEDB Error 80040E21 - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. Status[0]=3" exception.
The very same which occurred for Jet.

Could you help us by checking the method signatures of OleDB and how parameters are bound.
In fact, "Status[0]=3" indicates a "bsBadBindInfo" error.

About your workaround techniques:
1) variant are stored as UTF-8 in the database, not as double... So it is perhaps not the best option.
2) InternalRegisterCustomProperties / RegisterCustomFixedSizeRecordProperty is a good idea.
But I guess the "bsBadBindInfo" error is still there, and needs to be fixed.

Offline

#9 2013-12-09 13:32:15

ncook
Member
From: Australia
Registered: 2013-08-13
Posts: 19

Re: We need to store NULL's in the database instead of empty strings

Hi Arnaud.
I've been working through the code and trying to interpret what is happening.

I've found my way to this code in SynOleDB so far...

procedure TOleDBStatement.ExecutePrepared;
...
        case P^.VType of
        ftNull: begin
          // bind a NULL parameter
          B^.dwPart := DBPART_STATUS;
          B^.obStatus := PAnsiChar(@P^.VInt64)-pointer(fParams);
          P^.VInt64 := Ord(stIsNull);
        end;
...

which I gather may be relevant, and I am attempting to understand what it is doing.
I'm still working on that. I hope I'm looking in the right direction.

However during this process I found these two enumerations in SynOleDB...

type
  /// indicates whether the data value or some other value, such as a NULL,
  // is to be used as the value of the column or parameter
  // - see http://msdn.microsoft.com/en-us/library/ms722617(VS.85).aspx
  TOleDBStatus = (
    stOK, stBadAccessor, stCanNotConvertValue, stIsNull, stTruncated,
    stSignMismatch, stDataoverFlow, stCanNotCreateValue, stUnavailable,
    stPermissionDenied, stIntegrityViolation, stBadStatus, stDefault);
  /// binding status of a given column
  // - see http://msdn.microsoft.com/en-us/library/windows/desktop/ms720969
  TOleDBBindStatus = (
    bsOK, bsBadOrdinal, bsUnsupportedConversion, bsBadBindInfo,
    bsBadStorageFlags, bsNoInterface, bsMultipleStorage);

which do not appear to be complete when compared to the OLE DB Reference here: http://msdn.microsoft.com/en-us/library/ms716934.aspx

TOleDBStatus appears to be missing several values, including the entry stSchemaViolation between stIntegrityViolation & stBadStatus (which causes stBadStatus & stDefault to have the wrong values).
TOleDBBindStatus appears to have an additional value bsMultipleStorage on the end, which is not in the reference.

I know these are not related to the problem we are looking at now, but I thought it may be worth raising anyway, in case they make a difference one day somewhere else.

I'll keep working to try to understand the OLE DB binding code for a while, but it's just after midnight here, and my mind is starting to get foggy, so I may have to stop soon. (Not to mention that the OLE DB API is seriously twisted - I've never seen anything quite like it before.)

Offline

#10 2013-12-09 18:17:06

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,272
Website

Re: We need to store NULL's in the database instead of empty strings

I've made a big code refactoring of the SynOleDB unit.

Now:
  *  SynOleDB now works as expected under Win64 (a lot of previous code was Win32 specific)
  *  fixed issue when binding NULL parameters with some OleDB providers (e.g. Jet)
See http://synopse.info/fossil/info/2e78485736

I hope it will fix your issues.
Feedback is welcome!

Offline

#11 2013-12-09 22:58:36

ncook
Member
From: Australia
Registered: 2013-08-13
Posts: 19

Re: We need to store NULL's in the database instead of empty strings

Hi Arnaud.
Thanks for all the work you've done trying to fix this.

Unfortunately I have to report that it still has not fixed the error while trying to save NULLs in Jet.

The error message is the same. I'll include the whole log file from a single test run, in case you find something useful.

D:\Development\Projects\MM_Monitor\mORMot_Dev\mORMot_Dev.exe 0.0.0.0 (2013-12-10 07:44:09)
Host=NEVILLE-WIN7 User=Neville CPU=4*9-6-5898 OS=13.1=6.1.7601 Wow64=1 Freq=2760019
Environment variables=ALLUSERSPROFILE=C:\ProgramData	APPDATA=C:\Users\Neville\AppData\Roaming	asl.log=Destination=file	BDS=c:\program files (x86)\codegear\rad studio\5.0	BDSAppDataBaseDir=BDS	BDSCOMMONDIR=C:\Users\Public\Documents\RAD Studio\5.0	BDSPROJECTSDIR=D:\Users\Neville\Documents\RAD Studio\Projects	BDSUSERDIR=D:\Users\Neville\Documents\RAD Studio\5.0	CASSANDRA_HOME=D:\Development\Cassandra	CLASSPATH=.;C:\Program Files (x86)\Java\jre6\lib\ext\QTJava.zip	CommonProgramFiles=C:\Program Files (x86)\Common Files	CommonProgramFiles(x86)=C:\Program Files (x86)\Common Files	CommonProgramW6432=C:\Program Files\Common Files	COMPUTERNAME=NEVILLE-WIN7	ComSpec=C:\Windows\system32\cmd.exe	Data Abstract=C:\Program Files (x86)\RemObjects Software\Data Abstract for Delphi	DELPHI=c:\program files (x86)\codegear\rad studio\5.0	EL_PATHS=;C:\Program Files (x86)\EurekaLog 6\Delphi11;C:\Program Files (x86)\EurekaLog 6\CBuilder11	Everwood=C:\Program Files (x86)\RemObjects Software\Everwood	FP_NO_HOST_CHECK=NO	HOMEDRIVE=C:	HOMEPATH=\Users\Neville	JAVA_HOME=C:\Program Files (x86)\Java\jre6	LOCALAPPDATA=C:\Users\Neville\AppData\Local	LOGONSERVER=\\NEVILLE-WIN7	NUMBER_OF_PROCESSORS=4	OS=Windows_NT	Pascal Script=C:\Program Files (x86)\RemObjects Software\Pascal Script for Delphi	Path=C:\ProgramData\Embarcadero\RAD Studio\9.0\bin;C:\Users\Public\Documents\RAD Studio\9.0\Bpl;C:\ProgramData\Embarcadero\RAD Studio\9.0\bin64;C:\Users\Public\Documents\RAD Studio\9.0\Bpl\Win64;C:\Program Files (x86)\NVIDIA Corporation\PhysX\Common;C:\Program Files\Common Files\Microsoft Shared\Windows Live;C:\Program Files (x86)\Common Files\Microsoft Shared\Windows Live;C:\Program Files (x86)\CodeGear\RAD Studio\5.0\bin;C:\Users\Public\Documents\RAD Studio\5.0\Bpl;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;c:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\;c:\Program Files\Microsoft SQL Server\100\Tools\Binn\;c:\Program Files\Microsoft SQL Server\100\DTS\Binn\;c:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\;c:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files (x86)\Microsoft ASP.NET\ASP.NET Web Pages\v1.0\;C:\Program Files (x86)\Windows Live\Shared;C:\Program Files (x86)\RemObjects Software\Everwood\Bin;C:\Program Files (x86)\RemObjects Software\RemObjects SDK for Delphi\Dcu\D11;C:\Program Files (x86)\RemObjects Software\Pascal Script for Delphi\Dcu\D11;C:\Program Files (x86)\RemObjects Software\Data Abstract (Common)\Bin;C:\Program Files (x86)\RemObjects Software\Data Abstract for Delphi\Dcu\D11;C:\Program Files (x86)\ATI Technologies\ATI.ACE\Core-Static;C:\Program Files (x86)\QuickTime\QTSystem\;C:\Program Files (x86)\RemObjects Software\Oxygene\bin;C:\Program Files (x86)\Common Files\Acronis\SnapAPI\;C:\Program Files\Microsoft\Web Platform Installer\;C:\Program Files (x86)\Git\cmd;;C:\Program Files (x86)\EurekaLog 6\Delphi11;C:\Program Files (x86)\EurekaLog 6\CBuilder11;C:\Users\Public\Documents\RAD Studio\5.0\Bpl	PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.MSC	PROCESSOR_ARCHITECTURE=x86	PROCESSOR_ARCHITEW6432=AMD64	PROCESSOR_IDENTIFIER=Intel64 Family 6 Model 23 Stepping 10, GenuineIntel	PROCESSOR_LEVEL=6	PROCESSOR_REVISION=170a	ProgramData=C:\ProgramData	ProgramFiles=C:\Program Files (x86)	ProgramFiles(x86)=C:\Program Files (x86)	ProgramW6432=C:\Program Files	PSModulePath=C:\Windows\system32\WindowsPowerShell\v1.0\Modules\	PUBLIC=C:\Users\Public	QTJAVA=C:\Program Files (x86)\Java\jre6\lib\ext\QTJava.zip	RemObjects SDK=C:\Program Files (x86)\RemObjects Software\RemObjects SDK for Delphi	RWDCP=C:\Users\Public\Documents\RAD Studio\5.0\Dcp	RWDCU=D:\Users\Neville\Documents\RAPWare\dcu	RWDSG=C:\Users\Public\Documents\RAD Studio\5.0\Bpl	RWLIB=D:\Users\Neville\Documents\RAPWare\Libraries	RWSRC=D:\Users\Neville\Documents\RAPWare\Source	SESSIONNAME=Console	SSH_AGENT_PID=8320	SSH_AUTH_SOCK=/tmp/ssh-TVvBjg8332/agent.8332	SystemDrive=C:	SystemRoot=C:\Windows	TEMP=C:\Users\Neville\AppData\Local\Temp	TMP=C:\Users\Neville\AppData\Local\Temp	USERDOMAIN=Neville-Win7	USERNAME=Neville	USERPROFILE=C:\Users\Neville	VS100COMNTOOLS=C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\Tools\	windir=C:\Windows
TSQLLog 1.18 2013-12-10T07:51:25

20131210 07512547  +    0011E3EF SynSQLite3.TSQLDatabase.DBOpen (3259) 
20131210 07512547  +    	TSQLDatabase(009669F0).0011DB46 SynSQLite3.TSQLDatabase.Execute (2898) 
20131210 07512547 SQL   		PRAGMA cache_size=10000
20131210 07512547  -    	TSQLDatabase(009669F0).0011DBB6 SynSQLite3.TSQLDatabase.Execute (2907)  00.000.173
20131210 07512547  -    0011E85B SynSQLite3.TSQLDatabase.DBOpen (3322)  00.000.298
20131210 07512547  +    TSQLDatabase(009669F0).0011DC58 SynSQLite3.TSQLDatabase.Execute (3031) 
20131210 07512547 SQL   	SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%';
20131210 07512547  -    TSQLDatabase(009669F0).0011DCCF SynSQLite3.TSQLDatabase.Execute (3040)  00.000.042
20131210 07512547 debug TSQLDatabase(009669F0) TableNames=null
20131210 07512547  +    TSQLDatabase(009669F0).0011DB46 SynSQLite3.TSQLDatabase.Execute (2898) 
20131210 07512547 SQL   	BEGIN TRANSACTION;
20131210 07512547  -    TSQLDatabase(009669F0).0011DBB6 SynSQLite3.TSQLDatabase.Execute (2907)  00.000.007
20131210 07512547  +    TSQLDatabase(009669F0).0011DB46 SynSQLite3.TSQLDatabase.Execute (2898) 
20131210 07512547 SQL   	CREATE VIRTUAL TABLE NullTest1 USING External(fldText TEXT COLLATE SYSTEMNOCASE, fldMemo TEXT COLLATE SYSTEMNOCASE);
20131210 07512547  +    	TOleDBConnection(008D8A28).0011460C SynOleDB.TOleDBConnection.Create (1964) 
20131210 07512547  -    	TOleDBConnection(008D8A28).001146A1 SynOleDB.TOleDBConnection.Create (1971)  00.000.010
20131210 07512547  +    	TOleDBConnection(008D8A28).00114374 SynOleDB.TOleDBConnection.Connect (1927) 
20131210 07512607  -    	TOleDBConnection(008D8A28).00114551 SynOleDB.TOleDBConnection.Connect (1959)  00.363.041
20131210 07512607  +    	TOleDBStatement(00860370).CreateAccessor
20131210 07512607  -    	TOleDBStatement(00860370).CreateAccessor 00.000.123
20131210 07512607  +    	TOleDBStatement(00860370).00113933 SynOleDB.TOleDBStatement.Destroy (1711) 
20131210 07512607 DB    		TOleDBStatement(00860370) Total rows = 3
20131210 07512607  -    	TOleDBStatement(00860370).001139B4 SynOleDB.TOleDBStatement.Destroy (1721)  00.000.007
20131210 07512607  +    	TOleDBStatement(00860370).CreateAccessor
20131210 07512607  -    	TOleDBStatement(00860370).CreateAccessor 00.000.027
20131210 07512607  +    	TOleDBStatement(00860370).00113933 SynOleDB.TOleDBStatement.Destroy (1711) 
20131210 07512607 DB    		TOleDBStatement(00860370) Total rows = 1
20131210 07512607  -    	TOleDBStatement(00860370).001139B4 SynOleDB.TOleDBStatement.Destroy (1721)  00.000.004
20131210 07512607  -    TSQLDatabase(009669F0).0011DBB6 SynSQLite3.TSQLDatabase.Execute (2907)  00.369.387
20131210 07512607 SQL   TOleDBStatement(00860100) SELECT top 1 ID FROM tblNullTest1
20131210 07512607  +    TOleDBStatement(00860100).00112E78 SynOleDB.TOleDBStatement.ExecutePrepared (1543) 
20131210 07512612  -    TOleDBStatement(00860100).00113342 SynOleDB.TOleDBStatement.ExecutePrepared (1629)  00.073.808
20131210 07512612  +    TOleDBStatement(00860100).CreateAccessor
20131210 07512612  -    TOleDBStatement(00860100).CreateAccessor 00.000.015
20131210 07512612  +    TSQLDatabase(009669F0).0011DB46 SynSQLite3.TSQLDatabase.Execute (2898) 
20131210 07512612 SQL   	COMMIT TRANSACTION;
20131210 07512612  -    TSQLDatabase(009669F0).0011DBB6 SynSQLite3.TSQLDatabase.Execute (2907)  00.000.810
20131210 07512612  +    TSQLRestClientDB(00897DB0).TimeStamp
20131210 07512612  +    	TSQLRestServerDB(008C8840).root/TimeStamp
20131210 07512612 call  		TSQLRestServerDB(008C8840) TimeStamp
20131210 07512612 srvr  		TSQLRestServerDB(008C8840) GET TimeStamp -> 200
20131210 07512612  -    	TSQLRestServerDB(008C8840). 00.000.014
20131210 07512612 ret   	135137492185
20131210 07512612  -    TSQLRestClientDB(00897DB0).TimeStamp 00.000.073
20131210 07512612  +    TSQLRestServerDB(008C8840).root/NullTest1
20131210 07512612 SQL   	TOleDBStatement(00860370) select max(ID) from tblNullTest1
20131210 07512612  +    	TOleDBStatement(00860370).00112E78 SynOleDB.TOleDBStatement.ExecutePrepared (1543) 
20131210 07512613  -    	TOleDBStatement(00860370).00113342 SynOleDB.TOleDBStatement.ExecutePrepared (1629)  00.004.273
20131210 07512613  +    	TOleDBStatement(00860370).CreateAccessor
20131210 07512613  -    	TOleDBStatement(00860370).CreateAccessor 00.000.011
20131210 07512613 SQL   	TOleDBStatement(00860510) insert into tblNullTest1 (ID,fldText,fldMemo) VALUES (?OOR?,?OOR?,?OOR?)
20131210 07512613  +    	TOleDBStatement(00860510).00112E78 SynOleDB.TOleDBStatement.ExecutePrepared (1543) 
20131210 07512613 ERROR 		"EOleDBException(0096E340)":"OLEDB Error 80040E21 - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." stack trace API 00114BCB SynOleDB.EnhancedTest (2061) 00114CD3 SynOleDB.TOleDBConnection.OleDBCheck (2067) 0011320A SynOleDB.TOleDBStatement.ExecutePrepared (1606) 001BB27D mORMotDB.TSQLRestServerStaticExternal.ExecuteFromJSON (1529) 001B9427 mORMotDB.TSQLRestServerStaticExternal.EngineAdd (1025) 001A1284 mORMot.TSQLRestServerURIContext.ExecuteORMWrite (24404) 001A26CC mORMot.TSQLRestServer.URI (24865) 001BE965 mORMotSQLite3.TSQLRestClientDB.InternalURI (1479) 0019CE56 mORMot.TSQLRestClientURI.URI (22688) 0019D2D6 mORMot.TSQLRestClientURI.EngineAdd (23018) 001A7730 mORMot.TSQLRestClient.Add (27483) 001C3015 mORMot_Dev_TestGroup3.TfrmMORMot_Dev_TestGroup3.btnNullTest1Click (121) 000A8422 Controls.TControl.Click 000E4EAE RzButton.TRzCustomButton.MouseUp 000A8825 Controls.TControl.DoMouseUp 000ABF14 Controls.TWinControl.WndProc 000AB63B Controls.TWinControl.MainWndProc 00027F9A Classes.StdWndProc 
20131210 07512749 EXC   		EOleDBException ("OLEDB Error 80040E21 - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.") at 00114BD3 SynOleDB.EnhancedTest (2062)  stack trace 00001007 System.SysFreeMem 00002211 System.@ReallocMem 00005C61 System.DynArraySetLength 000F6C9C SynCommons.TDynArrayHashed.HashFind (23504) 000F6C9C SynCommons.TDynArrayHashed.HashFind (23504) 000FCA2A SynCommons.TRawUTF8ListHashed.Changed (27752) 0000658B System.TInterfacedObject._AddRef 00002211 System.@ReallocMem 000F5F11 SynCommons.TDynArray.InternalSetLength (22907) 000F601F SynCommons.TDynArray.SetCount (22944) 00111844 SynOleDB.TOleDBStatement.CheckParam (1146) 001BB27D mORMotDB.TSQLRestServerStaticExternal.ExecuteFromJSON (1529) 00172945 SynSQLite3Static.sqlite3_create_collation (1355) 001B9427 mORMotDB.TSQLRestServerStaticExternal.EngineAdd (1025) 001A1284 mORMot.TSQLRestServerURIContext.ExecuteORMWrite (24404) 001A26CC mORMot.TSQLRestServer.URI (24865) 
20131210 07512750 ERROR 		"EOleDBException(0096E340)":"OLEDB Error 80040E21 - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." stack trace API 0011331D SynOleDB.TOleDBStatement.ExecutePrepared (1626) 001BB27D mORMotDB.TSQLRestServerStaticExternal.ExecuteFromJSON (1529) 001B9427 mORMotDB.TSQLRestServerStaticExternal.EngineAdd (1025) 001A1284 mORMot.TSQLRestServerURIContext.ExecuteORMWrite (24404) 001A26CC mORMot.TSQLRestServer.URI (24865) 001BE965 mORMotSQLite3.TSQLRestClientDB.InternalURI (1479) 0019CE56 mORMot.TSQLRestClientURI.URI (22688) 0019D2D6 mORMot.TSQLRestClientURI.EngineAdd (23018) 001A7730 mORMot.TSQLRestClient.Add (27483) 001C3015 mORMot_Dev_TestGroup3.TfrmMORMot_Dev_TestGroup3.btnNullTest1Click (121) 000A8422 Controls.TControl.Click 000E4EAE RzButton.TRzCustomButton.MouseUp 000A8825 Controls.TControl.DoMouseUp 000ABF14 Controls.TWinControl.WndProc 000AB63B Controls.TWinControl.MainWndProc 00027F9A Classes.StdWndProc 
20131210 07512750 EXC   		EOleDBException ("OLEDB Error 80040E21 - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.") at 00114BD3 SynOleDB.EnhancedTest (2062)  stack trace API 00102EB8 SynCommons.SynRtlUnwind (32572) 00003B58 System.@HandleAnyException 0007A58B ExceptionLog.Call_HookedRaise (15678) 00114BD3 SynOleDB.EnhancedTest (2062) 00114CD3 SynOleDB.TOleDBConnection.OleDBCheck (2067) 0011320A SynOleDB.TOleDBStatement.ExecutePrepared (1606) 001BB27D mORMotDB.TSQLRestServerStaticExternal.ExecuteFromJSON (1529) 001B9427 mORMotDB.TSQLRestServerStaticExternal.EngineAdd (1025) 001A1284 mORMot.TSQLRestServerURIContext.ExecuteORMWrite (24404) 001A26CC mORMot.TSQLRestServer.URI (24865) 001BE965 mORMotSQLite3.TSQLRestClientDB.InternalURI (1479) 0019CE56 mORMot.TSQLRestClientURI.URI (22688) 0019D2D6 mORMot.TSQLRestClientURI.EngineAdd (23018) 001A7730 mORMot.TSQLRestClient.Add (27483) 001C3015 mORMot_Dev_TestGroup3.TfrmMORMot_Dev_TestGroup3.btnNullTest1Click (121) 000A8422 Controls.TControl.Click 000E4EAE RzButton.TRzCustomButton.MouseUp 000A8825 Controls.TControl.DoMouseUp 
20131210 07512750  -    	TOleDBStatement(00860510).00113342 SynOleDB.TOleDBStatement.ExecutePrepared (1629)  01.598.803
20131210 07512750 srvr  	POST root/NullTest1 ERROR=400 (Bad Request)
20131210 07512750  -    TSQLRestServerDB(008C8840). 01.603.834
20131210 07512750 ERROR TSQLRestClientDB(00897DB0) POST root/NullTest1 returned 400 Bad Request with message  {  "ErrorCode":400,  "ErrorText":"Bad Request"  } stack trace API 0019CFDF mORMot.TSQLRestClientURI.URI (22718) 0019D2D6 mORMot.TSQLRestClientURI.EngineAdd (23018) 001A7730 mORMot.TSQLRestClient.Add (27483) 001C3015 mORMot_Dev_TestGroup3.TfrmMORMot_Dev_TestGroup3.btnNullTest1Click (121) 000A8422 Controls.TControl.Click 000E4EAE RzButton.TRzCustomButton.MouseUp 000A8825 Controls.TControl.DoMouseUp 000ABF14 Controls.TWinControl.WndProc 000AB63B Controls.TWinControl.MainWndProc 00027F9A Classes.StdWndProc 
20131210 07512750 info  TSQLRestServerDB(008C8840) {   "ClientsCurrent": 0,   "ClientsMax": 0,   "Invalid": 1,   "Responses": 1,   "Modified": 0,   "IncomingBytes": 172,   "OutcomingBytes": 132,   "OutcomingFiles": 0,   "ServiceCalls": 1,   "ProcessTime": "1.60s"  }
20131210 07512750  +    0011E31A SynSQLite3.TSQLDatabase.DBClose (3246) 
20131210 07512751  -    0011E374 SynSQLite3.TSQLDatabase.DBClose (3252)  00.019.212
20131210 07512751  +    TOleDBConnection(008D8A28).00114725 SynOleDB.TOleDBConnection.Destroy (1976) 
20131210 07512751  +    	TOleDBConnection(008D8A28).001147E7 SynOleDB.TOleDBConnection.Disconnect (1989) 
20131210 07512751  +    		TOleDBStatement(00860100).00113933 SynOleDB.TOleDBStatement.Destroy (1711) 
20131210 07512751 DB    			TOleDBStatement(00860100) Total rows = 1
20131210 07512751  -    		TOleDBStatement(00860100).001139B4 SynOleDB.TOleDBStatement.Destroy (1721)  00.000.209
20131210 07512751  +    		TOleDBStatement(00860370).00113933 SynOleDB.TOleDBStatement.Destroy (1711) 
20131210 07512751 DB    			TOleDBStatement(00860370) Total rows = 1
20131210 07512751  -    		TOleDBStatement(00860370).001139B4 SynOleDB.TOleDBStatement.Destroy (1721)  00.000.580
20131210 07512751  +    		TOleDBStatement(00860510).00113933 SynOleDB.TOleDBStatement.Destroy (1711) 
20131210 07512751 DB    			TOleDBStatement(00860510) Total rows = 0
20131210 07512751  -    		TOleDBStatement(00860510).001139B4 SynOleDB.TOleDBStatement.Destroy (1721)  00.000.036
20131210 07512761  -    	TOleDBConnection(008D8A28).0011486C SynOleDB.TOleDBConnection.Disconnect (2000)  00.155.461
20131210 07512761  -    TOleDBConnection(008D8A28).00114796 SynOleDB.TOleDBConnection.Destroy (1985)  00.155.469

I'm compiling and testing this using Delphi 2007 (obviously Win32) on a Win7x64 system. But I've also tried compiling & running the same code on a 32 bit WinXP VM and it still has exactly the same error.

I'll freely admit that I'm really a bit out of my depth down in the OleDB binding code, and while I'm slowly making sense of what it is doing, I'm not sure that I'm up to the level to spot a problem yet. I'm usually quite happy deep in technical code (much better than dropping components on forms) but having never been any deeper into database code than ADO before, OleDB is a steep learning curve for an old guy.
Still, if there's anything I can do to help (especially tests to run, etc.) then please just ask.
I wish I could work on this more today, but I'm committed to some other work for a client. So I'll get back to this in the evening.

Regards.

Offline

#12 2013-12-11 21:58:19

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,272
Website

Re: We need to store NULL's in the database instead of empty strings

I think we finally fixed the issue when binding NULL parameters with some DB providers (e.g. Jet).
See http://synopse.info/fossil/info/abd36d92eb

Jet was less tolerant than MSSQL and did not accept to bind a DBTYPE_NULL kind of value: it expected a true value - so I put DBTYPE_I4 and it seems to work now!

I've also added some regression tests using Jet, including binding '' as NULL.
See http://synopse.info/fossil/info/5e44a3d69b

Offline

#13 2013-12-12 02:14:42

ncook
Member
From: Australia
Registered: 2013-08-13
Posts: 19

Re: We need to store NULL's in the database instead of empty strings

Yes!

Thank you Arnaud.

This has fixed the original problem of storing NULL's in place of empty string into Text & Memo fields, and now both of the additional techniques I detailed above (for storing NULL's into other field types when required) also work correctly with Jet.

Thank you again for your amazing work.

Offline

Pages: 1

Board footer

Powered by FluxBB