Delphi XE3, EDB 2.12 build 2

The following SQL runs perfectly in the EDBManager. Those lines that involve adding a NULL do precisely what I want them to do; those lines resolve to NULL.

SELECT DateOne + CAST(LeaveDays AS INTERVAL DAY) as EndDate
FROM TestNullDate

In a test app the result set won't display in a TDBGrid, giving the error message '0.0 is not a valid timestamp'.

The table is:
CREATE TABLE "TestNullDate"
(
"TestID" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"DateOne" DATE,
"LeaveDays" INTEGER
)
VERSION 1.00
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 8192
BLOB BLOCK SIZE 1024
PUBLISH BLOCK SIZE 1024
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768!
INSERT INTO "TestNullDate" VALUES (1,
                 DATE '2010-10-10',
                 10)!
INSERT INTO "TestNullDate" VALUES (2,
                 DATE '2010-11-11',
                 NULL)!
INSERT INTO "TestNullDate" VALUES (3,
                 DATE '2010-11-11',
                 NULL)!
INSERT INTO "TestNullDate" VALUES (4,
                 NULL,
                 20)
              
The engine's StandardNullBehaviour hasn't been molested; it is True.

Where should I look to see why my app doesn't handle the NULL values?

Regards & Thanks for reading

Peter
           

Peter,

> The engine's StandardNullBehaviour hasn't been molested; it is True.
>
> Where should I look to see why my app doesn't handle the NULL values?

I tested that and couldn't see any problems (Delphi 2010 and TdbGrid).
See the attached screenshot.

Uli

Peter,

That error message is not an EDB error message but a Delphi message.
My guess is you have a persistent TDateTimeField called EndDate defined in the query component that you are using to get the result set.

If it's not that, then I don't know... my tests produce the exactly same results as Uli is getting and posted.

--
Fernando Dias
[Team Elevate]

Peter


Works fine here in 2.13b3 - ANSI

So it may be a bug that's been fixed.

Roy Lambert [Team Elevate]

Thanks for taking the time, team. I should have installed the latest version. I will install it as soon as I can (I'll be away for a couple of days) then it should be Ok.

Your help is much appreciated

Peter

Peter,

<< In a test app the result set won't display in a TDBGrid, giving the error
message '0.0 is not a valid timestamp'. >>

Is there anything "extra" in the test app ?  I haven't fixed anything like
this recently, so I don't think it's anything in EDB.  Did you try the same
SQL with the EDB Manager ?

Thanks,

Tim Young
Elevate Software
www.elevatesoft.com

Tim

I am back from minor surgery, so here goes. The components are all plain vanilla, and there are no persistent fields. The full dfm is small enough to post here, and follows the SQL. The SQL1 works just fine; SQL2 gives the error.

procedure TForm1.Button1Click(Sender: TObject);
const
SQL1 = 'SELECT * FROM TestNullDate';
SQL2 = 'SELECT DateOne + CAST(LeaveDays AS INTERVAL DAY) as EndDate'+
       ' FROM TestNullDate';
begin
EDBQuery1.SQL.Clear;
EDBQuery1.SQL.Add(SQL1);
EDBQuery1.Active := True;
end;

procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
EDBEngine1.Active := False;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
EDBEngine1.Active := True;
end;

The dfm is:
<<<<<<<<<<
object Form1: TForm1
 Left = 0
 Top = 0
 Caption = 'Form1'
 ClientHeight = 167
 ClientWidth = 414
 Color = clBtnFace
 Font.Charset = DEFAULT_CHARSET
 Font.Color = clWindowText
 Font.Height = -11
 Font.Name = 'Tahoma'
 Font.Style = []
 OldCreateOrder = False
 OnClose = FormClose
 OnCreate = FormCreate
 PixelsPerInch = 96
 TextHeight = 13
 object DBGrid1: TDBGrid
   Left = 32
   Top = 24
   Width = 265
   Height = 120
   DataSource = DataSource1
   TabOrder = 0
   TitleFont.Charset = DEFAULT_CHARSET
   TitleFont.Color = clWindowText
   TitleFont.Height = -11
   TitleFont.Name = 'Tahoma'
   TitleFont.Style = []
 end
 object Button1: TButton
   Left = 303
   Top = 24
   Width = 75
   Height = 25
   Caption = 'Button1'
   TabOrder = 1
   OnClick = Button1Click
 end
 object DataSource1: TDataSource
   DataSet = EDBQuery1
   Left = 248
   Top = 96
 end
 object EDBDatabase1: TEDBDatabase
   DatabaseName = 'MyAppDB'
   Database = 'MyAppDB'
   SessionName = 'EDBSession1_1'
   Left = 176
   Top = 40
 end
 object EDBQuery1: TEDBQuery
   DatabaseName = 'MyAppDB'
   SessionName = 'EDBSession1_1'
   SQL.Strings = (
     '')
   Params = <>
   ReadOnly = True
   Left = 184
   Top = 96
 end
 object EDBEngine1: TEDBEngine
   Active = True
   EngineType = etClient
   UseLocalSessionEngineSettings = True
   Signature = 'edb_signature'
   EncryptionPassword = 'elevatesoft'
   LicensedSessions = 4096
   ConfigPath = 'C:\ProgramData\MyApp\Version1'
   ConfigName = 'EDBConfig'
   ConfigExtension = '.EDBCfg'
   LockExtension = '.EDBLck'
   LogExtension = '.EDBLog'
   MaxLogFileSize = 1048576
   LogCategories = [lcInformation, lcWarning, lcError]
   CatalogName = 'EDBDatabase'
   CatalogExtension = '.EDBCat'
   BackupExtension = '.EDBBkp'
   UpdateExtension = '.EDBUpd'
   TableExtension = '.EDBTbl'
   TableIndexExtension = '.EDBIdx'
   TableBlobExtension = '.EDBBlb'
   TablePublishExtension = '.EDBPbl'
   TempTablesPath = 'C:\Users\PETER~1\AppData\Local\Temp\'
   ShowUserPasswords = True
   ShowDatabaseCatalogVersions = True
   ServerName = 'EDBSrvr'
   ServerDescription = 'ElevateDB Server'
   ServerAuthorizedAddresses.Strings = (
     '*')
   Left = 111
   Top = 40
 end
 object EDBSession1: TEDBSession
   Connected = True
   LoginUser = 'Administrator'
   LoginPassword = 'EDBDefault'
   SessionName = 'EDBSession1_1'
   LocalSignature = 'edb_signature'
   LocalEncryptionPassword = 'elevatesoft'
   LocalStandardNullBehavior = False
   LocalConfigPath = 'C:\ProgramData\MyApp\Version1'
   LocalConfigName = 'EDBConfig'
   LocalConfigExtension = '.EDBCfg'
   LocalLockExtension = '.EDBLck'
   LocalLogExtension = '.EDBLog'
   LocalMaxLogFileSize = 1048576
   LocalLogCategories = [lcInformation, lcWarning, lcError]
   LocalCatalogName = 'EDBDatabase'
   LocalCatalogExtension = '.EDBCat'
   LocalBackupExtension = '.EDBBkp'
   LocalUpdateExtension = '.EDBUpd'
   LocalTableExtension = '.EDBTbl'
   LocalTableIndexExtension = '.EDBIdx'
   LocalTableBlobExtension = '.EDBBlb'
   LocalTablePublishExtension = '.EDBPbl'
   LocalTempTablesPath = 'C:\Users\PETER~1\AppData\Local\Temp\'
   LocalShowUserPasswords = True
   LocalShowDatabaseCatalogVersions = True
   RemoteSignature = 'edb_signature'
   RemoteEncryptionPassword = 'elevatesoft'
   RemoteAddress = '127.0.0.1'
   Left = 119
   Top = 96
 end
end
>>>>>>>>>>
Thanks for your help

Peter

Peter wrote:

Tim

Version 2.13 Build 3 Unicode

I am back from minor surgery, so here goes. The components are all plain vanilla, and there are no persistent fields. The full dfm is small enough to post here, and follows the SQL. The SQL1 works just fine; SQL2 gives the error.

procedure TForm1.Button1Click(Sender: TObject);
const
SQL1 = 'SELECT * FROM TestNullDate';
SQL2 = 'SELECT DateOne + CAST(LeaveDays AS INTERVAL DAY) as EndDate'+
       ' FROM TestNullDate';
begin
EDBQuery1.SQL.Clear;
EDBQuery1.SQL.Add(SQL1);
EDBQuery1.Active := True;
end;

procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
EDBEngine1.Active := False;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
EDBEngine1.Active := True;
end;

The dfm is:
<<<<<<<<<<
object Form1: TForm1
 Left = 0
 Top = 0
 Caption = 'Form1'
 ClientHeight = 167
 ClientWidth = 414
 Color = clBtnFace
 Font.Charset = DEFAULT_CHARSET
 Font.Color = clWindowText
 Font.Height = -11
 Font.Name = 'Tahoma'
 Font.Style = []
 OldCreateOrder = False
 OnClose = FormClose
 OnCreate = FormCreate
 PixelsPerInch = 96
 TextHeight = 13
 object DBGrid1: TDBGrid
   Left = 32
   Top = 24
   Width = 265
   Height = 120
   DataSource = DataSource1
   TabOrder = 0
   TitleFont.Charset = DEFAULT_CHARSET
   TitleFont.Color = clWindowText
   TitleFont.Height = -11
   TitleFont.Name = 'Tahoma'
   TitleFont.Style = []
 end
 object Button1: TButton
   Left = 303
   Top = 24
   Width = 75
   Height = 25
   Caption = 'Button1'
   TabOrder = 1
   OnClick = Button1Click
 end
 object DataSource1: TDataSource
   DataSet = EDBQuery1
   Left = 248
   Top = 96
 end
 object EDBDatabase1: TEDBDatabase
   DatabaseName = 'MyAppDB'
   Database = 'MyAppDB'
   SessionName = 'EDBSession1_1'
   Left = 176
   Top = 40
 end
 object EDBQuery1: TEDBQuery
   DatabaseName = 'MyAppDB'
   SessionName = 'EDBSession1_1'
   SQL.Strings = (
     '')
   Params = <>
   ReadOnly = True
   Left = 184
   Top = 96
 end
 object EDBEngine1: TEDBEngine
   Active = True
   EngineType = etClient
   UseLocalSessionEngineSettings = True
   Signature = 'edb_signature'
   EncryptionPassword = 'elevatesoft'
   LicensedSessions = 4096
   ConfigPath = 'C:\ProgramData\MyApp\Version1'
   ConfigName = 'EDBConfig'
   ConfigExtension = '.EDBCfg'
   LockExtension = '.EDBLck'
   LogExtension = '.EDBLog'
   MaxLogFileSize = 1048576
   LogCategories = [lcInformation, lcWarning, lcError]
   CatalogName = 'EDBDatabase'
   CatalogExtension = '.EDBCat'
   BackupExtension = '.EDBBkp'
   UpdateExtension = '.EDBUpd'
   TableExtension = '.EDBTbl'
   TableIndexExtension = '.EDBIdx'
   TableBlobExtension = '.EDBBlb'
   TablePublishExtension = '.EDBPbl'
   TempTablesPath = 'C:\Users\PETER~1\AppData\Local\Temp\'
   ShowUserPasswords = True
   ShowDatabaseCatalogVersions = True
   ServerName = 'EDBSrvr'
   ServerDescription = 'ElevateDB Server'
   ServerAuthorizedAddresses.Strings = (
     '*')
   Left = 111
   Top = 40
 end
 object EDBSession1: TEDBSession
   Connected = True
   LoginUser = 'Administrator'
   LoginPassword = 'EDBDefault'
   SessionName = 'EDBSession1_1'
   LocalSignature = 'edb_signature'
   LocalEncryptionPassword = 'elevatesoft'
   LocalStandardNullBehavior = False
   LocalConfigPath = 'C:\ProgramData\MyApp\Version1'
   LocalConfigName = 'EDBConfig'
   LocalConfigExtension = '.EDBCfg'
   LocalLockExtension = '.EDBLck'
   LocalLogExtension = '.EDBLog'
   LocalMaxLogFileSize = 1048576
   LocalLogCategories = [lcInformation, lcWarning, lcError]
   LocalCatalogName = 'EDBDatabase'
   LocalCatalogExtension = '.EDBCat'
   LocalBackupExtension = '.EDBBkp'
   LocalUpdateExtension = '.EDBUpd'
   LocalTableExtension = '.EDBTbl'
   LocalTableIndexExtension = '.EDBIdx'
   LocalTableBlobExtension = '.EDBBlb'
   LocalTablePublishExtension = '.EDBPbl'
   LocalTempTablesPath = 'C:\Users\PETER~1\AppData\Local\Temp\'
   LocalShowUserPasswords = True
   LocalShowDatabaseCatalogVersions = True
   RemoteSignature = 'edb_signature'
   RemoteEncryptionPassword = 'elevatesoft'
   RemoteAddress = '127.0.0.1'
   Left = 119
   Top = 96
 end
end
>>>>>>>>>>
Thanks for your help

Peter

Peter


I've tried to simulate it here but can't get the problem. I only have ANSI so it may be a unicode specific problem, however, remember that EDBManager is just a Delphi app so if it works in there and not in your code there has to be a difference.

The only difference I can think between your app and EDBManager is that Tim alters the nulls to display as a nice grey NULL rather than just a space. Not sure just where or how but have a look in the code and you'll find it.

Something is itching away at the corner of my mind - I think I've had the same problem. It was to do with my string table component not Tim's, and my subclassed DBGrid and it was when a timestamp was 0.0

I do remember it was rather tricky to fix.

Looking at the structure of the tables there is no timestamp so can you try this for me:

SQL2 = 'SELECT CAST(DateOne + CAST(LeaveDays AS INTERVAL DAY) AS DATE) as EndDate'+
       ' FROM TestNullDate';

Roy Lambert [Team Elevate]

Roy

No, that didn't alter the outcome. It is weird, as the SQL1 query displays blank fields rather than the helpful 'NULL' that EDBManager displays. Obviously EDBMgr has some formatting of the result, but as none of you good folk can reproduce the problem I am left to conclude that it is in my setup of the properties of the EDB components. The dfm doesn't show any glaringly obvious problems though.

Thanks for your help

Regards

Peter