Recent

Author Topic: TSQLQuery lookup field?  (Read 17720 times)

panoss

  • Full Member
  • ***
  • Posts: 162
  • You only live twice
Re: TSQLQuery lookup field?
« Reply #15 on: November 25, 2014, 11:32:44 am »
I can't understand? Do you have TSQLQuery which use table Products
Yes.

and TDataSet which use query Products?
I have no TDataSet. I have TDataSource with it's property DataSet: qryProducts.
Windows 10 64bit, Lazarus Version 2.2.0    FPC 3.2.2.

exdatis

  • Hero Member
  • *****
  • Posts: 668
    • exdatis
Re: TSQLQuery lookup field?
« Reply #16 on: November 25, 2014, 11:34:17 am »
That's ok. So, you need an other dataset(the second query) if you want to use filed from the second dataset as lookup in the first(products) data set.

panoss

  • Full Member
  • ***
  • Posts: 162
  • You only live twice
Re: TSQLQuery lookup field?
« Reply #17 on: November 25, 2014, 11:37:59 am »
Yes, but before I do this, first I must find out why I can't get the list of fields as I previously mentioned, even though Connection and Transaction are active.
« Last Edit: November 25, 2014, 11:40:02 am by panoss »
Windows 10 64bit, Lazarus Version 2.2.0    FPC 3.2.2.

exdatis

  • Hero Member
  • *****
  • Posts: 668
    • exdatis
Re: TSQLQuery lookup field?
« Reply #18 on: November 25, 2014, 11:42:14 am »
Set query active:= True and in fieldsEditor press  add fields(+)

panoss

  • Full Member
  • ***
  • Posts: 162
  • You only live twice
Re: TSQLQuery lookup field?
« Reply #19 on: November 25, 2014, 11:45:09 am »
exdatis, as previously mentioned, I can't.
If somebody has a working example (with SQLite) so that I can compare with mine might help a lot.
Maybe that's the problem, that it 's SQLite.
In the dblookup example of Lazarus, which is with DBase (I think) I can see the list of fields in the TDBf component. (even though they have 'active:false)
« Last Edit: November 25, 2014, 12:18:08 pm by panoss »
Windows 10 64bit, Lazarus Version 2.2.0    FPC 3.2.2.

exdatis

  • Hero Member
  • *****
  • Posts: 668
    • exdatis
Re: TSQLQuery lookup field?
« Reply #20 on: November 25, 2014, 12:17:27 pm »
I do not use SQLite, I have no experience with sqlite.
If you want an embedded server I prefer Firebird. I use PostgreSQL and Firebird.

p.s. See: http://wiki.freepascal.org/SQLite
        for more information
« Last Edit: November 25, 2014, 12:20:11 pm by exdatis »

exdatis

  • Hero Member
  • *****
  • Posts: 668
    • exdatis
Re: TSQLQuery lookup field?
« Reply #21 on: November 25, 2014, 12:23:49 pm »
Also, you can try this : http://zeoslib.sourceforge.net/portal.php (database layer)

panoss

  • Full Member
  • ***
  • Posts: 162
  • You only live twice
Re: TSQLQuery lookup field?
« Reply #22 on: November 25, 2014, 12:30:35 pm »
I guess I 'll have to switch to Firebird or dBase or something...
Thank you anyway.
« Last Edit: November 25, 2014, 12:32:21 pm by panoss »
Windows 10 64bit, Lazarus Version 2.2.0    FPC 3.2.2.

panoss

  • Full Member
  • ***
  • Posts: 162
  • You only live twice
Re: TSQLQuery lookup field?
« Reply #23 on: November 25, 2014, 03:57:31 pm »
Ok a good (I believe) solution for SQLite, is a subquery in the main query, like this:
Tables:
Product (pname, price, cid)
Company(cid, cname, city)
Code: [Select]
SELECT X.pname , (SELECT Y.city FROM Company Y WHERE Y.cid = X.cid ) as City
FROM
Product X
Example taken from here.
« Last Edit: November 25, 2014, 04:37:40 pm by panoss »
Windows 10 64bit, Lazarus Version 2.2.0    FPC 3.2.2.

kapibara

  • Hero Member
  • *****
  • Posts: 610
Re: TSQLQuery lookup field?
« Reply #24 on: November 26, 2014, 01:39:16 am »
I guess I 'll have to switch to Firebird or dBase or something...
Thank you anyway.

Not so fast..   ;)

I made an example for SQLite. You need to complete a number of steps in the right order. Hope I'll get it right here..

In general, first make the tables. In the demo they are created by code. Then, when you have a lookup table, go ahead and make a lookup query.

Right here is something easy to miss: For designtime, DatabaseName in object inspector needs the full path, or you get a message like "table doesn't exist" because SQLite autocreates its database and it will be empty without tables. So the message will be correct. And you need to activate the queries at designtime to make fields available, or you cant make the lookup table at design time.

Now activate the lookup query (genres in this example) and you get the FieldDefs property populated.

Here comes the next easy thing to miss: Double click the Lookup Query. Then click the plus sign. Then add all fields just to be sure. (maybe you dont really need all of them, but do it for now) If you dont do this step, the fields will not be available for the lookup later and you get message that some field doesn't exist even though it exists in the table.

Right. On to the movies table. Here we will create the lookup field, but first we should do as with the lookup query. Activate the movies query, then doubleclick it and add all fields.

And now its time for the lookup field: click the sheet icon which is to the right of the plus sign. Select Lookup and fill in:

Code: [Select]
Key fields: fk_genre_id
Dataset: qryGenres
Lookup fields: genre_id
Result fields: genre_name

Click OK and now you have a lookup field called genre_name that you can use to get the name instead of the id number of records etc.

In the demo its used as a combobox in a grid. Btw, you can add, edit and delete movie records. Check out the SQL statements in the movies query.
« Last Edit: November 26, 2014, 10:37:46 pm by kapibara »
Lazarus trunk / fpc 3.2.2 / Kubuntu 22.04 - 64 bit

kapibara

  • Hero Member
  • *****
  • Posts: 610
Re: TSQLQuery lookup field?
« Reply #25 on: November 26, 2014, 02:08:31 am »
I think thats because you have just the name in the databasename property. Write the whole path, otherwise SQLite makes an empty db with no tables in it. And then you get "no such table"

Before trying to set up the Lookup field set all the relevant TSQLQuery(s) to active
I tried this and returns me error: 'no such table: Products'.
(the table, of course, exists)
The Connection and the Transaction (the respective ones to my TSQLQuery) are active.
Lazarus trunk / fpc 3.2.2 / Kubuntu 22.04 - 64 bit

panoss

  • Full Member
  • ***
  • Posts: 162
  • You only live twice
Re: TSQLQuery lookup field?
« Reply #26 on: November 26, 2014, 11:36:08 am »
I think thats because you have just the name in the databasename property. Write the whole path, otherwise SQLite makes an empty db with no tables in it. And then you get "no such table"
I wrote the whole path in the Connection, but still gives me the message "no such table" when I try to make the TSQLQuery active.
I will study you example, thank you so much for bothering trying to help me!
Windows 10 64bit, Lazarus Version 2.2.0    FPC 3.2.2.

panoss

  • Full Member
  • ***
  • Posts: 162
  • You only live twice
Re: TSQLQuery lookup field?
« Reply #27 on: November 26, 2014, 12:00:14 pm »
I had to reset the components and then put the full path in the propert 'DatabaseName' of the Connection componenet.
So now TSQLQuery I set the property 'Active' to true...and no error message appears.
And I get the fields' list as its supposed to!

I also set it programmatically:
Code: [Select]
procedure TDM.DataModuleCreate(Sender: TObject);
begin
    //Set database name and path to program directory
  Connection.DatabaseName:=ProgramDirectory + 'data';
end; 
« Last Edit: November 26, 2014, 12:44:38 pm by panoss »
Windows 10 64bit, Lazarus Version 2.2.0    FPC 3.2.2.

 

TinyPortal © 2005-2018