Because the ElevateSoft Import facility has bugs, I am in the process of building my own.  I am mostly through the process but have some questions.

1.  When a string (i.e. varchar) is to be stored in a DB table, is an empty string the same as a NULL entry?  If not, please explain?

2.  In defining the fields of a table, one important property is "Not Null".  How is that property communicated to TDataSet?  

3.  How can one use Exceptions along with the Debugger?  I found that when I used the Debugger on Exceptions, the exceptions process would not work.  It would only work if the Debugger is turned off.

David,

<<1.  When a string (i.e. varchar) is to be stored in a DB table, is an empty string the same as a NULL entry?  If not, please explain?>>

Not by default, no.
EDB deals with NULL by the SQL standard rules that means they are not the same thing at all.
Just like a 0 is not the same as NULL for an Integer, also '' is not the same as NULL for strings.
A Delphi Dataset can detect if a varchar column is null using the "IsNull" property:

SomeTable.FieldByName('SomeColumn').IsNull
  - Returns true if it's a null false otherwise

SomeTable.FieldByName('SomeColumn').AsString
  - Returns an empty string both for empty strings and NULLs

To set a column to NULL:

SomeTable.FieldByName('SomeColumn').Clear
  Sets it to NULL

SomeTable.FieldByName('SomeColumn').AsString := ''
  Sets it to an empty string
 

<<2.  In defining the fields of a table, one important property is "Not Null".  How is that property communicated to TDataSet>>

It's not.
If you want the Dataset field not to accept nulls, set it's "Required" property to True, however it's not exactly the same and I seldom use it. Anyway, EDB will check if the column is NULL or not.

<<3.  How can one use Exceptions along with the Debugger?  I found that when I used the Debugger on Exceptions, the exceptions process would not work.  It would only work if the Debugger is turned off.>>

I don't understand what you mean here... it does work.


--
Fernando Dias
[Team Elevate]

Fernando:  Thank you.  My software environment is Windows 10 and Lazarus 3.8.

Your answer to my question #1 was very clear.

Let me sharpen my question #2.  I am building my table of field parameters from the TDataSet elements.  I want to check the data before I Post it.  The code I am using to get those properties is below.  The fifth element, loading "piRequired" is the one which doesn't seem to work.  (I get the same result if I use Fields.Fields[fInx].)  Could it be that there is no code to load that element into "Required"?

 for fInx := 0 to fCount-1 do begin
   piArray[fInx].piName := currTable.Fields.Fields[fInx].FieldName;
   piArray[fInx].piLength:= currTable.Fields.Fields[fInx].Size;
   piArray[fInx].piType:= currTable.Fields.Fields[fInx].DataType;
   piArray[fInx].piPrecision:= currTable.Fields.Fields[fInx].DataSize;
   piArray[fInx].piRequired:=  currTable.FieldDefs[fInx].Required;      // this one is the issue.
 end;

 Question #3.  Exception handling when using the Debugger.  When I wrote my original message, I was getting the "Exception Message" screen with the break/continue options with the Debugger.  The last several times I have tried it with the Debugger turned on, I have not gotten that "Exception Message" screen.  I now consider this to be a non-issue.

Thank you for your help.

David,

As I said, the "Required" property is not exactly the same - you can set it to true in code, but it wont be read from the table definitions, and it's good it doesn't - it saves a lot of trouble this way, believe me.
 
Here is a function that will return what you want.
All you have to do is:

piArray[fInx].piRequired := not IsNullable(currTable, currtable.Fields[fInx].FieldName);


function IsNullable(ATable: TEDBTable; AColumnName: string): Boolean;
begin
  //Returns True if the column <AColumnName> in table ATable can be NULL
  Result :=
    ATable.Database.Execute(
      'SELECT ' +
      '  Nullable ' +
      'FROM ' +
      '  Information.TableColumns ' +
      'WHERE ' +
      '  Nullable = TRUE AND ' +
      '  TableName = ' + QuotedStr(ATable.TableName) + ' AND ' +
      '  Name = ' + QuotedStr(AColumnName)
    ) <> 0 ;
end;

--
Fernando Dias
[Team Elevate]

Fernando


Glad you answered ths

<<1.  When a string (i.e. varchar) is to be stored in a DB table, is an empty string the same as a NULL entry?  If not, please explain?>>

saves me having apoplexy

Roy Lambert

David


Fernando sort of hints at it in his response to you but I'll say it specifically - its a good idea for you to look at the Information database (the OLH covers it well). Its a very good idea to look into the use of SQL and the metadata rather than using the table methods.

ElevateDB and SQL work really well togther - I hated it when I started using it but I would find it very difficult to go backwards now.

Roy Lambert

Roy and Fernando:

Thank you both very much.

I agree that the information tables are very helpful.  I'm learning to use them more and more.

David

David


<<
<<2.  In defining the fields of a table, one important property is "Not Null".  How is that property communicated to TDataSet>>

It's not.
If you want the Dataset field not to accept nulls, set it's "Required" property to True, however it's not exactly the same and I seldom use it. Anyway, EDB will check if the column is NULL or not.
>>

Just back from the shops (I'm retired now so I can go whenever I'm forced by "her indoors"). Fernando is right about ElevateDB checking the column, and about it being the field which has the Required property not the dataset. Its worth mentioning that even if, like myself, you have the compiled version of ElevateDB you should have the source of the components in edbcomps.pas. Mine is in "C:\D2007Ex\ElevateDB\code\source" Obviously not all of the magic is there but it does give you some useful hints and clues

I may be totally misjudging why you asked this question but I feel a warning should be given. Work with the ElevateDB components, they will interface happily with Delphi and do what you want. On occasions you will be caught out (eg raising an error rather than truncating strings longer than the column) which, in my case at least, is the SQL standard not doing what I want/was used to.

Roy Lambert

I use https://www.scalabium.com/   Import Suite (and Export Suite).

All the hard work is done - it is extensible and you get the source code.

Walter

Walter,

Yes, I also used it a few times and it saved me a lot of time, but haven't update it recently.
Do you know if they built it for 64 bits now?

--
Fernando Dias
[Team Elevate]