Re: [sqlite] Help with query
Fred, A query I can understand! This was my attempt, and worked to some degree, but yours is more workable and, I feel, more accurate "select NewsData.Postcode,NewsData.Address1,Agents.Code,Agents.Shopname,Agents.Addre ss1.Agents.Price from NewsData join Agents on NewsData.Postcode where NewsData.Shop1>'' or NewsData.Shop2>'' order by NewsData.Postcode asc" Thanks very much for your interest and help, Brian
[sqlite] Help with query
Hi, This query, "select distinct Shop1,Shop2,Postcode from NewsData where Shop1>'' or Shop2>'' order by Postcode asc" works as I require, but I want to expand the query to include data from another table, and have got a bit stuck The second table is called Agents. If the above query is successful, I want it to include the columns called Code, Shopname, Address1 and Price, but only if the Shopname column in the Agents table matches either Shop1 or Shop2 in the NewsData table Can anyone help me with this? Regards, Brian Pugh
[sqlite] Changing a Field
Hi, I need to do a multiple search and replace on the same field in my database. Can anyone suggest a suitable bit of code for this? I have over 100,000 records, so any automation is very welcome! Thanks for any suggestions, Brian Pugh
[sqlite] Compiling
Hi, Does anyone know how to compile the source code with lcc-win32? It's not that I don't want to use the supplied dll - I just wanted to have a go at it myself! Thanks for any step-by-step information, Brian Pugh
[sqlite] Re: SQLite command line
Paul, Thanks very much for a clear explanation, and one which I could understand! Pity about the no-timing returns, but I suppose it's obvious that you should employ indexes wherever possible Now to test all my "selects" Thanks again, Brian
[sqlite] Network Performance
Hi, Now that I've completed my SQLite application (written in Pyxia's IBasic), using the SQLite DLL, I have some questions My database is about 37mb, and has 114,380 records (rows) If I open and query the database locally (ie, the .db file is in the same folder as the application), things move pretty quick. I can do a query for a certain range of records (59,840) in about 4 seconds Doing the same query with the same .db on a network server is taking 18 seconds. Is this acceptable? I find that pretty slow! Most of my queries are fashioned so that they use indexes - I try not to use the "like" operator We have a certified 100mb network, with Cisco gigabit switches, and I am running the application from a 2.4gHz P4 PC How do other users deal with querying large amounts of information over a network? Are you paging in records, say 500 at a time, and then paging in the next 500 records? Could I set my database up differently? At the moment, when the .db loads up, I am setting: pragma cache_size=8192; pragma synchronous=off; pragma count_changes=off; Are there any other tweaks I could set in this area? If anyone has any ideas or suggestions as to how to improve the query speed over a network, I would love to hear them Thanks a lot, Brian Pugh Halifax, England
[sqlite] Indexes
Hi, My current database is mainly names and addresses. Most of my record viewing is done by: "select * from TableName order by Zip,Address,No" Zip, Address and No (house number) have separate indexes. On other databases I have used, I have combined these three columns into one index, and used that index to display records I tried creating a new index, "create index DefView on TableName(Zip,Address,No)" SQLite didn't complain about that, but when I tried to use the index, by using "select * from TableName order by DefView" I didn't get any records returned to view I have obviously got the wrong end of the stick about indexes! Could someone put me right, or point me gently in the right direction? Thanks, Brian Pugh
[sqlite] Re: Jump to record
Bronislav, Mrs Brisby and Derrell, Thanks a lot for your interest. It now remains for me to try your suggestions Thanks again, Brian
[sqlite] Jumping to a record
Hi, I have a set of records selected, for example, with: "select * from NewsData where (upper(Zip)>='HX1' and upper(Zip)<'HX2') order by Zip,Address,No" There is an INTEGER PRIMARY KEY field in the database called ID Given that I know I have a record with an ID of 12345, how would it be possible to "jump" to that record, and still maintain my original select parameters and order? I guess that this is down to a select within a select, but I'm not sure how to go about it. Well, I'll qualify that - anything that I've tried hasn't worked yet! Thanks a lot, Brian Pugh, Halifax, England
[sqlite] Select from a Select
Hello, My current SQLite application is written with Pyxia's IBasic, and by nature of the database, is form based. This works well now I have applied all the speed tweaks I have picked up from this forum I have added a button to the form to show records in Listview format. This works OK as long as I send a second SELECT to the Listview for it to display the required range of records Of course, I have already sent a SELECT to the main form-based window for it to show my required range of records. How do I send the same SELECT to the listview for it to show the already selected range? I see there is "create view" and it probably appears to be what I want, but I have no idea how to apply it, or make a "pre-packaged" select Any answers or suggestions would be most gratefully welcomed Thanks a lot, Brian Pugh
[sqlite] Select from Select
Hi, I would like to be able to run a query on my names and addresses database, returning the following: How many records in a certain Postcode area (Zip code to our US friends!); How many of those have telephones (ie, the field is blank if they don't have a telephone); How many with telephones can be canvassed (I have a field called Canvass that has either a 'Y' or 'N' in there) I can do the first (how many records), but I don't want to have to run a query for every return needed. I think a "select from select" is needed, but I don't know how to code it Table is called NewsData; Postcode field is called Postcode; Telephone field is called Tel; Canvass field is called Canvass Many thanks for any help, Brian Pugh
