SQLQuery field not found

[rkamarowski]

Lazarus 1.4.2, sqlite3, on Windows 10.

I'm getting an SQLQuery 'field not found' database error for the field 'SessionTypeName' for the statement:

    item.SubItems.Add(SQLQuery1.FieldByName('SessionTypeName').AsString);

I have tested this code using SQLiteManager, and Python with no problems. Does SQLQuery return JOIN field names?

procedure TFormMain.Button1Click(Sender: TObject);
var
  item: TListItem;
begin
     SQLite3Connection1.Open;
     SQLQuery1.SQL.Clear;
     SQLQuery1.SQL.Add('SELECT SessionID, Identifier, SessionType.SessionTypeName, CameraID, Date FROM Session ');
     SQLQuery1.SQL.Add('JOIN SessionType USING (SessionTypeID) ');
     SQLQuery1.SQL.Add('JOIN Camera USING (CameraID) ');
     SQLQuery1.SQL.Add('ORDER BY Identifier,SessionType.SessionTypeName');
     SQLQuery1.Open;
     while not SQLQuery1.EOF do
           begin
                item := ListViewSession.Items.Add;
                item.Caption:= SQLQuery1.FieldByName('Identifier').AsString;
                item.SubItems.Add(SQLQuery1.FieldByName('SessionTypeName').AsString);
                item.SubItems.Add(SQLQuery1.FieldByName('Date').AsString);
                SQLQuery1.Next;
           end;


end;           

[eny]

Maybe adding an alias works.
Something like this: SELECT ..., SessionType.SessionTypeName AS SessionTypeName, ...

[rkamarowski]

No, it still doesn't work.

[rvk]

Define an integer I in your Button1Click and insert this after your "SQLQuery1.Open;".

Code: [Select]

  for I := 0 to SQLQuery1.FieldCount - 1 do
  begin
    Showmessage(SQLQuery1.Fields[I].FieldName);
  end;

It will show you all your fieldnames.
What is the 3rd field called ??
(That's the name you should use)

[rkamarowski]

Thanks for the code. As I suspected, it's not returning the JOIN name.  SQLite3 returns SessionTypeName because of the JOIN. This is needed to get the Name from the SessionType table.  Why isn't SQLQuery returning this? As I said, SQLiteManager, Python, and even Real Basic return SessionTypeName. Is there another way to get this data?

[rvk]

SQLite3 returns SessionTypeName because of the JOIN.

Do you mean it's NOT returning SessionTypeName because of the JOIN?
(Because if it IS you can use that name in FieldByName)

What exactly is SQLite returning as 3rd fieldname ?
(An empty showmessage???)

For me it's working correctly. SQLite3 shows the fieldname like it should in a DBGrid and I can do a FieldByName on it. Also... when using "AS OTHERNAME" (like eny showed) the fieldnames can be changed.

[rkamarowski]

No, it SHOULD return SessionTypeName. The 3rd filed is SessionTypeID. But in the past this query has always returned a SessionTypeName field.

It's in the SELECT section SessionType.SessionTypeName and in the JOIN section JOIN SessionType USING(SessionTypeID).

This has always returned a SessionTypeName field.

Running this in the sqlite3 shell produces the following:

   ...> ORDER BY Identifier,SessionType.SessionTypeName;
111|120 Film-10|120 Film|5|2000-06-01
112|120 Film-11|120 Film|6|1999-04-01
113|120 Film-12|120 Film|6|2000-05-01
114|120 Film-213|120 Film|15|2014-02-16
115|120 Film-214|120 Film|15|2014-02-16
116|120 Film-215|120 Film|142|2014-07-01
110|120 Film-7|120 Film|4|1999-10-01
109|120 Film-8|120 Film|5|1999-06-01
107|120 Film-9|120 Film|5|1999-06-01
108|Digital-1|Digital|3|2014-07-01

The 3rd field is the SessionTypeName from the SessionType table.  When the value is returned the field is called SessionTypeName

[rvk]

No, it SHOULD return SessionTypeName. The 3rd filed is SessionTypeID. But in the past this query has always returned a SessionTypeName field.

Ok, you didn't specify it returned SessionTypeID as fieldname. Yeah, that's wrong.

And even if it's wrong, overruling it with " as otherfield" should have worked. I'm wondering if this is really a SQLite3 problem or a Lazarus/FPC problem.

Could you try the following:

Code: [Select]

JOIN SessionType on SessionType.SessionTypeID=Session.SessionTypeID(to see if the USING way is at fault)

Normally I would do something like this:

Code: [Select]

SELECT s.SessionID, s.Identifier, t.SessionTypeName, c.CameraID, s.Date
FROM Session s
JOIN SessionType t on t.SessionTypeID=s.SessionTypeID
JOIN Camera c on c.CameraID=s.CameraID
ORDER BY s.Identifier, t.SessionTypeName
That way there is no confusion where the fields are coming from.

Running this in the sqlite3 shell produces the following:

   ...> ORDER BY Identifier,SessionType.SessionTypeName;
111|120 Film-10|120 Film|5|2000-06-01
112|120 Film-11|120 Film|6|1999-04-01

Does the sqlite3 shell not provide fieldnames ???
What is the third field named there??
If the third field is named SessionTypeName then SQLite3 is not the problem.

What version of SQLite3 and Lazarus are you using?
Has this problem surfaced since a new version of one of the two?



(edit: also using using() gives me the correct result so we need to now versionnumbers.)

[rvk]

@rkamarowski, Are you sure you have cleared all fields from the object inspector for SQLQuery1 ??

(see attached image)

If you left fields there you can encounter the problems your having. The fields should be empty because in that case the Open command will fill them automatically. If you still have fields left there the names will be as they are there.

[rkamarowski]

The 'JOIN SessionType on SessionType.SessionTypeID=Session.SessionTypeID' didn't work.

Yes, the third field is SessionTypeName.

Yes, I'm clearing the fields.  Code:

   SQLite3Connection1.Open;
     SQLQuery1.SQL.Clear;
     SQLQuery1.SQL.Add('SELECT SessionID, Identifier, SessionType.SessionTypeName, CameraID, Date FROM Session ');
   //  SQLQuery1.SQL.Add('JOIN SessionType USING (SessionTypeID) ');
     SQLQuery1.SQL.Add('JOIN SessionType on SessionType.SessionTypeID=Session.SessionTypeID');
     SQLQuery1.SQL.Add('JOIN Camera USING (CameraID) ');
     SQLQuery1.SQL.Add('ORDER BY Identifier,SessionType.SessionTypeName');
     SQLQuery1.Open;                                   

Lazarus 1.4.2  SQLite3 3.73

I'm just getting back to using Lazarus so I have no idea if it was working before.

[rvk]

Just to clarify... what does Lazarus show in a dbgrid with this statement?

Does it show
1. SessionTypeID as header and the SessionTypeID as values in the column
or
2. SessionTypeID as header and the SessionTypeName as values in the columns

[rkamarowski]

SessionTypeID  as the header, and SessionTypeID as the value.

[rvk]

Really strange.

I have attached a small test-project. Could you try it on your end. It will generate a very small test.db database with two tables (master and maindetail) and join them. I used SQLite3 3.8.5 but I don't think SQLite is at fault here because other programs don't suffer from this at your end.

(I even defined fields for sqlquery before opening and but with .Field.Clear they were reset)

(paste your own sqlite3.dll in the executable-directory.)

[rkamarowski]

Result attached.

[rvk]

So, what's different from my test-project and your project??

Maybe you can strip down your version to a bare minimum and see if the problem persists.