Extracting the contents of a record's field. lazarus / 16-Databases ---------------------------------------------------------------------- cov · 2013-04-11 16:52 Sorry for dominating the forum! How do I get values from a table? If I have a DBGrid displaying the result of 'SELECT * FROM FIELDS;', all the entries in the FIELDS table. If the User double-clicks on a row, I can get the ID of the record selected by id:=DBGrid1.DataSource.DataSet.Fields[0].Value; <- the ID field is the first column declared. How do I get the Path of the document from the PATHS table whose record ID corresponds to the record ID obtained from the FIELDS table above? Do I need to drop another TQuery onto the form? I obviously don't want to change the contents of the DBGrid, which surely would happen if I use the SQLQuery1 component again? Presumably I would use 'SELECT PATH FROM PATHS WHERE ID='+strtoint(id)+';'; But how do I access the document path? ---------------------------------------------------------------------- goodname · 2013-04-11 20:05 If the PATH field is displayed by the DBGrid then clicking should give you the column index which should correspond to the field index. Click on column 3 then the value should be at DBGrid1.DataSource.DataSet.Fields[2].Value As for the 'WHERE ID= ...' suggest you look into parameter queries. http://wiki.freepascal.org/SqlDBHowto#How_to_use_parameters_in_a_query .3F ---------------------------------------------------------------------- cov · 2013-04-11 20:24 Hi, GN, thanks for the response. No, The PATHS table is different from the FIELDS table which is displayed in the DBGrid and which is constructed from the query 'SELECT * FROM FIELDS;'. I was aware of the technique of parametising the variables in a query, but thought it simpler, for the purposes of this enquiry, to present it as a concatenated string. ---------------------------------------------------------------------- goodname · 2013-04-11 23:06 In that case a single query can join the two tables together. SELECT fields.*, paths.* FROM fields INNER JOIN fields.key=paths.id ---------------------------------------------------------------------- cov · 2013-04-12 06:11 I don't actually want the DBGrid to display the Document path. I just want to read it so that I can access the document. ---------------------------------------------------------------------- BigChimp · 2013-04-12 06:20 I have trouble understanding your remarks... QuoteI don't actually want the DBGrid to display the Document path.Then don't display the document path i.e. don't include it in the query on which the databound control (e.g. grid)  is based!??! QuoteI just want to read it so that I can access the document.I'd second Goodname's suggestion about getting the path, but it may help if you show your actual data structure, or in other words, how do *you* think you'll get from the FIELDS table to the PATHs table? Also, please read http://wiki.lazarus.freepascal.org/SQLdb_Tutorial3 if you haven't already. It shows you how to read data from queries etc without displaying them. ---------------------------------------------------------------------- cov · 2013-04-12 12:18 I have read all three Tutorials, numerous times. I understand how SQL works and have previously written (admittedly not very complex) SQL queries for Postgres and MySQL. I understand that the TSQLQuery has TIBConnection as it's Database component, and both TSQLQuery and TIBConnection are connected to the TSQLTransaction. The TDBGrid has the TDatasource as it's Datasource component, which, in turn, has it's Dataset component linked to TSQLQuery. So, when the TSQLQuery runs an SQL query, the result is displayed in the TDBGrid. In this case, the Query is 'SELECT * FROM FIELDS;' the result of which is correctly displayed in the TDBGrid. I want to take the ID field from the Record selected from the FIELDS table and use it to get the document path from the PATH field in the PATHS table for the document, whose ID matches that ID. I hope this is clearer. As far as I can tell this is not addressed in tutorial one, nor in tutorials two or three, although I'm happy to be proven wrong. ---------------------------------------------------------------------- BigChimp · 2013-04-12 12:24 Yep, thanks, that's certainly clearer. No, the exact scenario is not described in the tuts. However, once you have the ID field value, you can use the scenario in tutorial3 to get the path you are looking for by running SQL and putting the result in a variable. As for how to get the ID field: you might be able to do it via the datasource (it keeps step with the db bound controls), or via the dbgrid (Goodname's suggestion could be adapted... it uses the datasource anyway...). If not, the dbgrid docs should come in handy. Edit: updated datasource info ---------------------------------------------------------------------- cov · 2013-04-12 13:33 Ok, so if I use a different TSQLQuery, say SQLQuery2 on the form and connect it to IBConnection1 and run a query 'SELECT PATH WHERE ID=7654', how can I access the result of that query? ---------------------------------------------------------------------- BigChimp · 2013-04-12 13:36 http://wiki.lazarus.freepascal.org/SQLdb_Tutorial3#Getting_database_da ta_into_normal_controls QuoteIt is also possible to programmatically retrieve database content and fill any kind of control (or variable) with that content. ---------------------------------------------------------------------- cov · 2013-04-12 14:43 Brilliant! Just what I'm after! Thanks! So I would need to instantiate a second TSQLQuery? I thought I would. That's fine! Thanks again. As ever, your help is much appreciated. ---------------------------------------------------------------------- cov · 2013-04-14 08:11 Just to add that a second TSQLQuery component does not stop the DBGrid from losing the results of the first SQLQuery1 query. A second IBConnection component seems to be required. ---------------------------------------------------------------------- cov · 2013-04-14 08:21 Hmmm. That doesn't work. Using the second TSQLQuery2 when it's connected to IBConnection1 works, but it resets the DBGrid. Connecting it to IBConnection2 doesn't work. Code: [Select]IBConnection2: PrepareStatement: -invalid transaction handle (expecting explicit transaction start) ---------------------------------------------------------------------- cov · 2013-04-14 08:26 Ok. I removed the second TIBConnection and pointed the SQLQuery2 back to IBConnection1. I then just opened SQLQuery1 again and my DBGrid repopulated itself. ---------------------------------------------------------------------- taazz · 2013-04-14 08:54 Not a second connection a second transaction is required. Using a different transaction for different sql queries you can control when those queries get closed or not. The idea seems simple enough but the transaction isolation level makes things complicated. Try reading the Firebird/interbase documents on transaction isolation. Personally I use modified version of MDO (http://sourceforge.net/projects/mdo/?source=directory) that allows me to have different read and write transactions making easy to post updates with out closing the read transaction. ---------------------------------------------------------------------- BigChimp · 2013-04-15 05:05 Strange. Did - an sql query - bound to the existing ibconnection+transaciton - not bound to the datasource not work? Having the lookup query in the same transaction as the grid lets it show the same data... ---------------------------------------------------------------------- Pascaluvr · 2013-04-17 09:36 cov, I am a newbie also and just learning this stuff by trial and error - so  what I say may be wrong (or inefficient) but it works for me: 1.  You only need one Transaction (I don't know about other dbms, but sqlite will not allow a transaction within a transaction.    So far, i have not  found a need for a 2nd Transaction) 2.  To access the a second 'select' without losing the first, you not only need a 2nd Query, but also a 2nd Datasource (and prossibly a 2nd DBgrid to display it). 3.  Since you need to access columns that you don't want displayed, simply: Code: [Select]        DBGrid1.Columns[x].Visible := False;  (where x= column not to be displayed but you still wish to access, x=0 is first column) Hope this helps ---------------------------------------------------------------------- teos · 2013-04-18 15:50 I wonder if using a second sqlquery component would work (with its own datasource and dbgrid) and using the afterscroll event to fill parameters of a second sqlquery which is then opened. (would give the right record and also only records required). A pitty that master-detail is not implemented which would handle that on the fly. b.t.w.: I think that ZTable with use of master-detail could be easier.. ---------------------------------------------------------------------- BigChimp · 2013-04-19 05:57 http://wiki.lazarus.freepascal.org/MasterDetail ---------------------------------------------------------------------- teos · 2013-04-19 09:06 Okay.. missed it. But nevertheless the link provides a perfect answer. ---------------------------------------------------------------------- cov · 2013-04-19 21:08 Thank you. I'll look at that.