Recent

Author Topic: SQLQuery field not found  (Read 12175 times)

rkamarowski

  • New Member
  • *
  • Posts: 40
Re: SQLQuery field not found
« Reply #15 on: September 02, 2015, 06:09:39 pm »
Your text fields are VARCHAR(). Mine are TEXT.

That's the only difference I can see.  But it shouldn't matter because SQLite3 does dynamic typing, and the classes are NULL, INTEGER, REAL, TEXT and BLOB. At least it hasn't mattered in other languages.  Is there a restriction in SQLQuery?
« Last Edit: September 02, 2015, 06:20:00 pm by rkamarowski »

rvk

  • Hero Member
  • *****
  • Posts: 6110
Re: SQLQuery field not found
« Reply #16 on: September 02, 2015, 11:11:48 pm »
Your text fields are VARCHAR(). Mine are TEXT.
True... For SQLite3 it all comes down to just text-fields. But there is a difference. When using TEXT you get a MEMO-field in Lazarus while if you use VARCHAR(25) (or other length) you get a STRING-field. See attached image if I use TEXT in my example-project.

But even with TEXT I get the correct column in my join so there must really be something wrong with your project if you get an ID. My advise still stands to make a bare minimum example or strip down your own project until it works.  For example delete your SQLQuery1 and drop a new one on your form just to see if you set some weird option the first time.

Edit: B.T.W. could you try adding another column for SessionType-table? What fields does that give you? (or does this table only exist as two fields?)
« Last Edit: September 02, 2015, 11:35:17 pm by rvk »

Ign_17

  • New Member
  • *
  • Posts: 43
Re: SQLQuery field not found
« Reply #17 on: September 03, 2015, 10:12:45 am »
Apologies for my poor english.

Try this:

SQLQuery1.SQL.Add('SELECT [SessionID], [Identifier], [SessionType.SessionTypeName], [CameraID], [Date] FROM [Session] ');

In the past I became mad with a similar problem, and I solved it using ODBCConnection.ExecuteDirect instead of SQLQuery, and closing all the fields and the table´s name with symbols [ ]

rvk

  • Hero Member
  • *****
  • Posts: 6110
Re: SQLQuery field not found
« Reply #18 on: September 03, 2015, 10:20:01 am »
SQLQuery1.SQL.Add('SELECT [SessionID], [Identifier], [SessionType.SessionTypeName], [CameraID], [Date] FROM [Session] ');
In that case SessionType.SessionTypeName is seen as a column-name and it spits out an error in SQLite3.

This however is valid (but my guess is that is doesn't make much difference):
SQLQuery1.SQL.Add('SELECT [SessionID], [Identifier], [SessionType].[SessionTypeName], [CameraID], [Date] FROM [Session] ');

The [] are for when the field-names themselves contain special characters, spaces or are named after reserved words, which in this case they don't.

rkamarowski

  • New Member
  • *
  • Posts: 40
Re: SQLQuery field not found
« Reply #19 on: September 03, 2015, 02:25:45 pm »
The plot thickens... :)

I had a select statement entered at design time.  When I deleted that the SQLQuery now works.  I had both the following statements before attempting to set the SQL property:

     SQLQuery1.SQL.Clear;

     for i := 0 to SQLQuery1.SQL.Count -1 do
     begin
          SQLQuery1.SQL.Delete(i);
     end;         

I'll do some testing to see if the Clear method is working.

Thanks everyone for helping.  Wish I had caught this earlier.
         

rvk

  • Hero Member
  • *****
  • Posts: 6110
Re: SQLQuery field not found
« Reply #20 on: September 03, 2015, 02:53:53 pm »
The plot thickens... :)

I had a select statement entered at design time.  When I deleted that the SQLQuery now works.
Oooo, that would be "hilarious"... if the SQLQuery1.SQL.Clear didn't work  :o

I tested it but here is works:
Code: [Select]
  Showmessage(SQLQuery4.SQL.Text); // <-- filled with designtime-sql
  SQLQuery4.SQL.Clear;
  Showmessage(SQLQuery4.SQL.Text); // <-- empty
  SQLQuery4.SQL.Add('.....')

But in my test-project I did an assign to .SQL.Text and that should also clear the design-time statement:
Code: [Select]
  SQLQuery4.SQL.Text := '......';

Good that you got it working but if .Clear doesn't work it is indeed very strange.

 

TinyPortal © 2005-2018