Setting Index for database lazarus / 16-Databases ---------------------------------------------------------------------- marsheng · 2012-04-24 07:54 I may have this all wrong but some of the bits work. I have a MYSql database called REGISTER. In register I have a table called  MEMBERS. Members has two indexes NamesI and NumbersI In order to populate a DBGrid, I use 'SELECT * FROM members' in the SQL  property of an SQLQuery. On my DBGrid I want to change the display order from Names to Numbers. Do I do this with new SQL string in the property of the SQLQuery or is there a way to just select one or the other index ? Although I'm connected to register, I can't seem to find any tables in the drop downs. I can only see data by manually entering the table names in an SQLQuery.  A great saying, SQL is easy, if you have done it before. Thanks Wallace ---------------------------------------------------------------------- sfeinst · 2012-04-24 12:01 In SQL, there is no guarantee of ordering when you do a select unless you also include an order by clause to the select. So in your instance, to order by name: select * from register order by name then to order by number select * from register order by number This assumes name and number are columns in your database.  If not, replace with the correct column name. ---------------------------------------------------------------------- BigChimp · 2012-04-24 12:32 BTW, indexes on your MySQL columns have nothing to do with sorting in Lazarus... except that an index on a column may speed up ORDER BY or WHERE clauses in your SQL. So you can write something like: Code: [Select]select number, name, birthday from register order by birthday desc to show the youngest people first (assuming you have the relevant fields) even if you have no index on birthday. ---------------------------------------------------------------------- ludob · 2012-04-24 12:53 You can do this also by adding indices to TSQLQuery without re- querying the database. Suppose the fields you want to sort on are called 'name' and 'number'. Before opening the query, add the following lines: Code: [Select]  SQLQuery1.AddIndex('name','name',[]);   SQLQuery1.AddIndex('number','number',[]);   SQLQuery1.Active:=true; The 3rd parameter is a set of TIndexOption. Useful values in this context are ixDescending and ixCaseInsensitive. When the query is active, changing the indexname will change the order of the data displayed in the dbgrid: Code: [Select]  SQLQuery1.IndexName:='name'; Note that the index of the table is not used at all. AddIndex is only an in memory sorting index and unrelated to the table index. There are also a few problems when using index and adding data (http://bugs.freepascal.org/view.php?id=20514). When you only want to display data it works fine though. ---------------------------------------------------------------------- BigChimp · 2012-04-24 14:15 PMFJI; I suppose it also would be possible to easily sort the grid (ascending vs descending) based on double clicking the column title, right? If so, that might be a good addition to the SQLDBTutorial1 page and the grids page... ---------------------------------------------------------------------- ludob · 2012-04-24 14:41 Quote from: BigChimp on April 24, 2012, 04:15:48 pmPMFJI; I suppose it also would be possible to easily sort the grid (ascending vs descending) based on double clicking the column title, right? If so, that might be a good addition to the SQLDBTutorial1 page and the grids page... There is a rather convoluted example in the wiki http://wiki.lazarus.freepascal.org/How_to_write_in-memory_database_app lications_in_Lazarus/FPC#Sorting_DBGrid_on_TitleClick_event_for_TBufDa taSet. TSQLQuery is a TBufDataset descendant so the code should work.  I haven tried it and I have my doubts on adding indexes while the dataset is active. At least MaxIndexesCount should be set high enough before opening the dataset because this is a hard limit on the number of indexes once it is active. ---------------------------------------------------------------------- goodname · 2012-04-25 00:24 I decided to give this sorting a try. Define the indexes in SQLQuery1.BeforeOpen. The DBGrid TitleClick event switches the sorted column. Just changing the options does not seam to be enough to reverse the sort order. Tried to do indexDefs.update or set the IndexName:='' then IndexName:='name' but it always sorts ascending. It is like the asc/desc sort option is only taken into account when the index is being built and I don't see a way to rebuild it. The wiki sample creates a new index for each sort but this seams like a poor way of just reversing the order. Code: [Select]//In Before Open.   SQLQuery1.AddIndex('name','name',[]);   SQLQuery1.AddIndex('number','number',[]); procedure TMyFrm.DBGrid1TitleClick(Column: TColumn); var def: TIndexDef; begin   if SQLQuery1.Active and((Column.FieldName='name') or (Column.FieldName='number')) then begin     if SQLQuery1.IndexName=Column.FieldName then begin      def:=SQLQuery1.IndexDefs.Find(Column.FieldName);      if ixDescending in def.Options then       def.Options:=def.Options-[ixDescending]      else def.Options:=def.Options+[ixDescending];      SQLQuery1.IndexName:=''; //did not reverse sort      SQLQuery1.IndexDefs.Updated:=false; //did not reverse sort     end;     SQLQuery1.IndexDefs.Update; //did not reverse sort     SQLQuery1.IndexName:=Column.FieldName;    end; end; ---------------------------------------------------------------------- marsheng · 2012-04-27 21:28 Thanks for the replies. I forgot to add notify to the post so I didn't get your replies. I'll go through the options later today. Cheers Wallace ---------------------------------------------------------------------- marsheng · 2012-04-27 21:36 I've just added a new post and I see there isn't an option to select notify. Only when you vies the post can you select notify. Hmmmm. ---------------------------------------------------------------------- BigChimp · 2012-04-28 06:07 If you're logged in, you can see some links that may help: show unread, show replies. Recently IIRC, somebody filed a bug/feature request against Simple Machines Forum (the software behind the forum) for something like that functionality... ---------------------------------------------------------------------- SteveF · 2012-04-30 16:28 Marsheng, It appears that you are coming into SQL from an xBase environment. Unlike xBase, in SQL setting an index does not automatically set a sort order in a table.  In SQL, a table is a "bucket of data"; which record you get when you dip into the bucket and pull out a record is not guaranteed to be consistent.  SQL indexes are primarily used to speed up data retrieval. To sort SQL data for a dbGrid, combo, or any other multi-row data control, you must use a query with an ORDER BY clause: Code: [Select]SELECT * FROM AddressList ORDER BY Lname, Fname; Will return all fields and all records in the AddressList table sorted in ascending order by the fields Lname and then Fname.  To sort in descending order, use this: Code: [Select]SELECT * FROM AddressList ORDER BY Lname desc, Fname desc; Use the query rather than the table as the rowsource for your data controls. Steve ---------------------------------------------------------------------- goodname · 2012-04-30 21:42 Sorts can be done on the server or client side. The sort is done on the server using the SQL ORDER BY clause. The sort is done on the client using SQLDB indexes. Your application needs should dictate where a sort should be done. This thread has good posts on how to sort in each location.