[solved] empty string fields from sqlite being ret. as nulls

[npage]

New to lazarus and zeoslib, so please be gentle

7.0.2 revision 2078

When retrieving data from an sqlite database fields that contain empty strings (not NULL values) are being returned as nulls and causing errors.

Database:

Code: Select all

CREATE TABLE settings (
category VARCHAR(40)  NULL,
name VARCHAR(40)  NULL,
value VARCHAR(250)  NULL
)

INSERT INTO settings (category, name, value)
VALUES ("mycat", "myname", "")

Program:

Code: Select all

procedure TSettings.LoadSettings;
var
  DbQuery: TZReadOnlyQuery;
  C: string;
  N: string;
  V: string;
begin
  DbQuery := TZReadOnlyQuery.Create(nil);
  DbQuery.Connection := DbConnection; 
  DbQuery.SQL.Text := 'SELECT * FROM settings';
  DbQuery.Open;
  while (not DbQuery.EOF) do begin
    C := DbQuery['category'];
    N := DbQuery['name'];
    // get error on the next line: EVariantTypeCastError
    // Could not convert variant of type (Null) into type (String)
    V := DbQuery['value'];
    DbLocalQuery.Next;
  end;
  DbLocalQuery.Close;
  DbLocalQuery.Free;
end;

I'm totally throwing darts, but after some investigation this seemed to fix the problem for me:

File: ZDbcSqLiteResultSet
function TZSQLiteResultSet.InternalGetString
changed line
LastWasNull := Result = '';
to
LastWasNull := Result = null;

Am I even close to doing that right?

[EgonHugeist]

npage,

i'm afraid, i didn't know the syntax you're going.

File: ZDbcSqLiteResultSet
function TZSQLiteResultSet.InternalGetString
changed line
LastWasNull := Result = '';
to
LastWasNull := Result = null;

Am I even close to doing that right?

Does this solve your issue? IMHO that's not right (null isn't pascal supported) and the Result should be the same. But you can be right too! Hmm currently we've no test for this case in our suites...

Does DbQuery.FieldByName('value').AsString solve your issue?

[Zoran]

@npage:

Instead of:

Code: Select all

    C := DbQuery['category'];
    N := DbQuery['name'];
    V := DbQuery['value'];

You probably want to use

Code: Select all

    C := DbQuery.FieldByName('category').AsString;
    N := DbQuery.FieldByName('name').AsString;
    V := DbQuery.FieldByName('value').AsString;

Plus, I notice that in the code you posted the name of the query variable changes from DbQuery to DbLocalQuery (but this is probably only here in forum post, not in real code, wright?).

@EgonHugeist:
I'd say you are right about this syntax.

[npage]

Thanks for the help guys!

@EgonHugeist:

DbQuery.FieldByName('value').AsString does resolve the problem.

Using DbQuery['value'] returned the correct value (when not null) so I figured it was a valid way to access it. When I tried DbQuery['value'].AsSting, it ran fine, but gave the error noted in my original post.

Note to self: always use FieldByName to access the values


@Zoran:

Thanks for your input. You are correct the DbLocalQuery was a typo when simplifying the code to post here (good eyes).

[Zoran]

I would add here that in your code you can check if the field is null. Use something like this:

Code: Select all

if DbQuery.FieldByName('category').IsNull then
  C := '<null>'
else
  C := DbQuery.FieldByName('category').AsString;