TSQLQuery lookup field?
lazarus / 16-Databases

----------------------------------------------------------------------
panoss · 2014-11-24 18:33

I 'm trying to make a lookup field in a TSQLQuery.
Right click->Edit fields->Create new fields->LookUp.
I choose the 'Dataset' from the drop down list and a message appears:
"Access violation.

Press OK to ignore and risk data corruption.
Press Cancel to kill the program."

If I press cancel, Lazarus gets killed.

If I press Ok, 'LookUp keys' combo and 'Result field' combo are
disabled.
In 'keyfields' combo, I can type the field name, but there's no list
of fields.

----------------------------------------------------------------------
mangakissa · 2014-11-25 07:49

The lookupfiels will not work in TSQLQuery as you expect. Is there a
reason to create this lookupfield?

----------------------------------------------------------------------
exdatis · 2014-11-25 07:58

Of course you can use select query to show everything but also you can
create new field lookup-type. It works excellent.
see: http://lazarus-
ccr.sourceforge.net/docs/lcl/dbctrls/tdblookupcombobox.html

I guess that the problem is an active SqlQuery, try to set query
active tru or false and then try again

p.s. http://wiki.freepascal.org/dblookupcombobox
       http://free-pascal-lazarus.989080.n3.nabble.com/Lazarus-How-to-
use-DBCombobox-td4024465.html

----------------------------------------------------------------------
panoss · 2014-11-25 08:07

Quote from: mangakissa on November 25, 2014, 08:49:38 amThe
lookupfiels will not work in TSQLQuery as you expect. Is there a
reason to create this lookupfield?
I have a grid which takes data from the table OrderDetails.
The table has a field, productID.

Another table, Products, has the fields ProductD and Name.

I want in the grid to show the product Name, not the productID that is
shown now.

So I thought of a lookup field, like in ms Access.

----------------------------------------------------------------------
panoss · 2014-11-25 08:10

Quote from: exdatis on November 25, 2014, 08:58:42 amOf course you can
use select query to show everything but also you can create new field
lookup-type. It works excellent.
see: http://lazarus-
ccr.sourceforge.net/docs/lcl/dbctrls/tdblookupcombobox.html

I guess that the problem is an active SqlQuery, try to set query
active tru or false and then try again
How do I create a lookup field?
I 'm using SQLite.

----------------------------------------------------------------------
exdatis · 2014-11-25 08:16

See :
/usr/share/lazarus/1.2.6/examples/database {* this is path on Debian
7.7 *}
there is an example: dblookup and sqlitetutorial(find it in lazarus
folder).

----------------------------------------------------------------------
exdatis · 2014-11-25 08:29

Quote from: panoss on November 25, 2014, 09:07:27 am
So I thought of a lookup field, like in ms Access.

Use fields editor to create new field(query->fields)
(M$access is not similar)

----------------------------------------------------------------------
panoss · 2014-11-25 08:35

Quote from: exdatis on November 25, 2014, 09:29:37 amQuote from:
panoss on November 25, 2014, 09:07:27 am
So I thought of a lookup field, like in ms Access.

Use fields editor to create new field(query->fields)
(M$access is not similar)
You mean a new field in the TSQLQuery?

----------------------------------------------------------------------
panoss · 2014-11-25 08:45

Quote from: exdatis on November 25, 2014, 09:16:23 amSee :
/usr/share/lazarus/1.2.6/examples/database {* this is path on Debian
7.7 *}
there is an example: dblookup and sqlitetutorial(find it in lazarus
folder).
There are no lookup fields in these examples. (or maybe there are but
I can't understand them. Also, in the dblookup they use DBase
database)

----------------------------------------------------------------------
talorigomat · 2014-11-25 09:56

Hi Panoss

I also had the access violation error when I tried to set up a lookup
field.  After I recieved this error I restarted Lazarus and opened the
project again.  Before trying to set up the Lookup field set all the
relevant TSQLQuery(s) to active so that the TSQLQuery that you want to
set up the lookup filed is showing some data.  Then proceed as before.

----------------------------------------------------------------------
panoss · 2014-11-25 10:14

Quote from: talorigomat on November 25, 2014, 10:56:47 am 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.

----------------------------------------------------------------------
exdatis · 2014-11-25 10:16

OnHold field is created as new field, calculated, boolean, the same
way as create lookup field

----------------------------------------------------------------------
panoss · 2014-11-25 10:20

exdatis read my first post.

----------------------------------------------------------------------
exdatis · 2014-11-25 10:26

Quote from: panoss on November 25, 2014, 11:14:58 am
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.

I can't understand? Do you have TSQLQuery which use table Products and
TDataSet which use query Products?

----------------------------------------------------------------------
panoss · 2014-11-25 10:28

I guess I'm doing something wrong.
I must tell you how each component connects to the others.
I use 4 components:
Code: [Select]TSQLite3Connection
name: Connection
DatabaseName: data
Transaction: Transaction

TSQLTransaction
name: Transaction
Database: Connection

TSQLQuery
name: qryProducts
Database: Connection
Transaction: Transaction
SQL: Select * FROM Products

TDataSource
name:DataSource
DataSet: qryProducts

----------------------------------------------------------------------
panoss · 2014-11-25 10:32

Quote from: exdatis on November 25, 2014, 11:26:25 amI can't
understand? Do you have TSQLQuery which use table Products
Yes.

Quote from: exdatis on November 25, 2014, 11:26:25 amand TDataSet
which use query Products?I have no TDataSet. I have TDataSource with
it's property DataSet: qryProducts.

----------------------------------------------------------------------
exdatis · 2014-11-25 10:34

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 · 2014-11-25 10:37

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.

----------------------------------------------------------------------
exdatis · 2014-11-25 10:42

Set query active:= True and in fieldsEditor press  add fields(+)

----------------------------------------------------------------------
panoss · 2014-11-25 10:45

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)

----------------------------------------------------------------------
exdatis · 2014-11-25 11:17

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

----------------------------------------------------------------------
exdatis · 2014-11-25 11:23

Also, you can try this : http://zeoslib.sourceforge.net/portal.php
(database layer)

----------------------------------------------------------------------
panoss · 2014-11-25 11:30

I guess I 'll have to switch to Firebird or dBase or something...
Thank you anyway.

----------------------------------------------------------------------
panoss · 2014-11-25 14:57

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.

----------------------------------------------------------------------
fpcoder · 2014-11-26 00:39

Quote from: panoss on November 25, 2014, 12:30:35 pmI 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.

----------------------------------------------------------------------
fpcoder · 2014-11-26 01:08

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"

Quote from: panoss on November 25, 2014, 11:14:58 amQuote from:
talorigomat on November 25, 2014, 10:56:47 am 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.

----------------------------------------------------------------------
panoss · 2014-11-26 10:36

Quote from: kapibara on November 26, 2014, 02:08:31 amI 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!

----------------------------------------------------------------------
panoss · 2014-11-26 11:00

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;