Problem using TZTable, Lazarus, PostgreSQL and INET Datatype

[jwhitten]

Hello,

I'm using Zeoslib 7.1.2-Stable with Lazarus 1.3 (from SVN) and I'm having a problem with using TZTable, PostgreSQL and the "INET" (PostgreSQL-specific) datatype. The "Inet" datatype is designed to work with IP Addresses, CIDR's and whatnot. I'm not really sure where to tackle this specific issue-- it's either a Zeos thing or else a Lazarus thing-- can someone help me figure this out please?

So here's the setup...

I have a table which uses several fields which are of the INET datatype.

I have NO issue using any of the fields if I am SETTING a value-- e.g., 'ip_address'='1.2.3.4' works fine.

I have NO issue if I'm using a BRAND NEW record and only setting one of the fields, 'ip_address1' = '1.2.3.4' (it DOESN'T barf when I OMIT any reference to 'ip_address2' and 'ip_address3', for instance).

I *DO* have a problem if an INET field USED to have a value and has now been cleared. E.g., it USED to be '1.2.3.4' and I've used the TDbEdit control to backspace (or whatever) to clear out the value. The resulting SQL to the database is apparently something like 'ip_address' = '' which is a no-no. It can be NULL but not EMPTY. Whenever I clear a field, it gets replaced by an EMPTY value as opposed to a NULL value.

Is this a "bug" (overlooked item) in the TZTable component? Is this something I should (or could) be addressing at the TDbEdit level (the data-aware edit component on the form)? Is there some auxillary property of the TZTable component (or alternately the TZQuery component) that could potentially intercept the EMPTY value and convert it to a NULL value? This is an area where the Zeoslib documentation (afaik) is really weak.


I'm really stuck here and I need to move forward. Can someone give me a hand figuring this out?

(BTW, I have also posted this on the Lazarus Forum: http://forum.lazarus.freepascal.org/ind ... #msg133188)

Thanks!

John

[EgonHugeist]

What about TField.Clear? It isn't task of Zeos to null a value even if the IP-Field is a string-field with fixed Length. IMHO it's your task to null the field-value.

Btw. Waiting some days for reply is quiet normal, since everybody helps in it's rare freetime...

[jwhitten]

>>What about TField.Clear? It isn't task of Zeos to null a value even if the IP-Field is a string-field with fixed Length. IMHO it's your task to null the field-value.

Thanks for the opinion. That's kind of what I've been attempting to work out. I ran up the red flag awhile ago whilst still trying different stuff. I finally figured out where I can intercept the value prior to the query preparation stage-- in the TField's "OnChange" event-- but I hadn't figured out yet how to nullify the field-- you might have given me the answer...

(checking...)

Nope, that doesn't do it. Same issue. I get a popup error:

Code: Select all

SQL Error: ERROR: invalid input syntax for type inet: ""

The problem seems to be figuring out a way to get it to actually NULL the field. The 'INET' data type is a specific data type in PostgreSQL (in case you aren't familiar with it). It is permitted to be NULL but not EMPTY (eg., NULL is okay, but '' is not).

I do understand your comment about it not being Zeos's job... etc., not totally sure I agree, but I do understand the perspective-- that's why I posted in both forums-- not sure where/how to deal with the problem.

IMO, the REAL place the problem should be dealt with is in the PostgreSQL database-- foo on them for making it such a pain in the butt to deal with !!!



>>Btw. Waiting some days for reply is quiet normal, since everybody helps in it's rare freetime...

Sure, I understand. And I definitely do appreciate all the assistance!!

(Wave)


John

[jwhitten]

Your suggestion did end up working. I simply needed a little more experimentation in order to locate the best place to apply it.

What I ended up doing was adding an 'OnChange' handler to the TDbEdit control like this:

Code: Select all

procedure TForm1.OnNullIfEmptyChange(Sender: TObject);
begin
  with Sender as TDbEdit do begin
    if (Text = '') then begin // if its empty, null it
       Field.Clear;
  end;
end;

This seems to do the trick nicely.

On a related note however, it seems to me that this is an entirely reasonable thing to expect a database component to be able to handle-- as a convenience feature. Would you (or whomever maintains the Zeos database lib) be willing to entertain adding it as such? So the field could have a property such as "NullIfEmpty" or some such, which would substitute a NULL as needed?

Thanks!

John