On 10/23/2014 4:21 AM, Matthew Jones wrote:
> I went over this step myself, and posted a few messages about it a
> month or so ago. The key is to determine if NULL makes sense in the
> column or not. If not, set the default for the column to be False (or

I think the original issue was around outer joins in which case one
cannot prevent NULL even if default is set and the differences between
EDB and DBISAM behaviour.

Raul

Matthew

>Yes, that's a better way of stating it. I agree that SQL is not ideal,
>but it is better that I can look up a book on SQL and apply it to my
>database than have some custom implementation where nothing standard
>applies. IMO anyway.

When I decided I should start to learn a bit about SQL rather than using navigational methods for everything I was surprised at the dearth of good books out there (and the cost of what there was).

>If you don't want the benefits, stick with DBISAM. 8-)

I thought about reverting on several occasions, but since I'm a masochist I kept on learning

>It is of course more than just .Clear to .AsString, but it is probably
>not as hard as one might think, depending on your code of course. And I
>expect your wicked self can find lots of nasties, but what other
>database doesn't have the same?

Its not the nasties (by which I hope you mean crashes) but the invidious stuff that still compiles and runs and only later on does it start delivering the wrong results.

>At least with a standard SQL you can
>use the internet to find solutions to the common problems.

I'm not sure in my seeking solutions out there I've found much that covers just a standard SQL, its generally focused on a specific implementation and either includes features that are not in or have a different name/syntax in ElevateDB, or more usually I just can't understand it!

Its probably just me but I often find SQL code, not PL/SPM but straight SQL pretty incomprehensible. All to often that includes a statement I wrote 6 months ago

Roy Lambert

Roy Lambert wrote:

> straight SQL pretty incomprehensible. All to often that includes a
> statement I wrote 6 months ago

Anything complex, I agree. My stuff is pretty simple though, and the
thing I should have done years ago but only did recently was make a
class called TSQLBuilder. My code is now similar to this:

xQueryBuilder.Clear;
xQueryBuilder.WhereClause := 'WHERE (caAccountGUID = ' + szAccountGUID
+ ')';
xQueryBuilder.TableName := 'EmailRequest';
xQueryBuilder.AddValue('erEmailType', szEmailType);
xQueryBuilder.AddValue('erEmailDestination', ':EMAIL');
xQueryBuilder.AddValue('erEmailContent', xContentValues.Text);
xQueryBuilder.AddValue('erRequestTime', BxQuotedDateTimeToSQLStr(Now,
true), true);
xWorkQuery.SQL.Text := xQueryBuilder.InsertQuery;


The key is that I can just ask for the InsertQuery, or UpdateQuery, or
SelectQuery, and it builds it to fit. And I can compile in DBISAM or
ElevateDB modes, and it sorts stuff out for me. This was partly what
made switching from DBISAM to ElevateDB so easy - fix it in one place
and everywhere is fixed.

Sure, it can't do the most complex stuff, but it does the basic. What
really used to annoy me was the Update vs Insert variations, where I'd
do an update, find nothing was updated, and then have to do an Insert.
Why the SQL is all different format I don't know, but now I don't care
as I never write it.

--

Matthew Jones

Matthew

>class called TSQLBuilder.

Did you know that we have an extensions ng that is permanently hungry?

Roy

Thanks Roy and Matthew for your contribution to a solution for me.

The ongoing discussion has also re-enlightened me on the wheres and
wherefores of the NULL debate.

For me the pragmatic solution is the setting of the StandardNullBehavior
to false on the Behavior tab (I couldn't find that anywhere before!!!).
 Since my app is C/S only, this seems to fix the SQL problems.

I had already implemented a couple of simple functions that used to
solve the problems in Delphi:-

= ''
<> ''
..IsNull

I simply whipped through hundred of modules doing find and replace ...

function TCommon.IsBlank(f: TField): Boolean;
begin
  Result := (Trim(f.AsString) = '') or f.IsNull;
end;

function TCommon.IsNotBlank(f: TField): Boolean;
begin
  Result := not IsBlank(f);
end;

Cheers and thanks again

Jeff

On 24/10/2014 2:15 a.m., Raul wrote:
> On 10/23/2014 4:21 AM, Matthew Jones wrote:
>> I went over this step myself, and posted a few messages about it a
>> month or so ago. The key is to determine if NULL makes sense in the
>> column or not. If not, set the default for the column to be False (or
>
> I think the original issue was around outer joins in which case one
> cannot prevent NULL even if default is set and the differences between
> EDB and DBISAM behaviour.
>
> Raul

Correct Raul.

Much of my work with SQL involves Master/Detail Tables, usually multi
layered i.e. more than one detail table, the detail table often in
master/detail relationship with another table.

In the case of a Master with no Details, I may wish to exclude it so my
WHERE condition has a test for a detail primary key field being not NULL
- Or have different processing for this case.

Cheers

Jeff

Roy Lambert wrote:

> > class called TSQLBuilder.
>
> Did you know that we have an extensions ng that is permanently hungry?

I will consider it, but I expect I'm the last person to write one so it
is embarrassing to admit I made it so late!

--

Matthew Jones