----- Original Message ----- From: "Stuart Felenstein" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Sunday, August 22, 2004 7:32 AM Subject: Dynamic Queries
> Not sure what to call what I'm attempting to do, > decided on dynamic queries. I should mention that I've > been working with databases for just a little over a > month. > > Example could be many web sites, but let's say Expedia > (the travel site..booking flights, cars, etc) > My understaning is that the SQL statements are > contained in a recordset, but variables would be > passed back into the sql statement based on the > particulars of the user's input? Is that correct, is > that the only way ? > I've been working with relational databases, mostly DB2, since 1985 and I'm not entirely sure of the precise meaning of the term "recordset". I've never seen a formal definition of that term. However, I'm pretty sure that a "recordset" is not the query that you pass to the database but the result that you get back. It's always been my practice to refer to the result of the query as a result set, not a recordset. I'm not trying to tell you not to use the term recordset - it might be widely used by some groups of people for all I know - I'm just trying to explain where I'm coming from. As to your specific question, yes, SQL statements can be written to contain variables. For example, if you were searching on Expedia, you could have a query that tells the database to return hotel names and addresses given a specific city and date, which the user supplies at execution time. The result set will then contain all of the rows that represent the prices of hotel rooms in that city on that date. In this case, the query would typically contain placeholders for the variables and the query would be prepared (compiled) without knowing the values of those placeholders. In other words, when the statement was prepared, the computer wouldn't know what city or date the user wanted. For example, the query might look like this: String query = "select hotel_name, hotel_address, hotel_cost_per_night from hotels where city = ? and date = ?"; That's how I would do it in Java. The question marks are the placeholders representing the variables for city name and date. It would also be possible to do it via string concatenation, although this can be bit problematic when the variables contain quotes or other special characters. For example: String query = "select hotel_name, hotel_address, hotel_cost_per_night from hotels where city = " + city_name + " and date = " + stay_date; You can also combine the two approaches. For example, String query = "select hotel_name, hotel_address, cost_per_night from " + table_name + " where city = ? and date = ?"; Naturally, in all of these approaches, the values of the variables have to be determined at execution time. There are a variety of ways that this could be accomplished but the most common is for the user to supply all variable values in text fields on a form before pressing the "go" button that launches the query. Then the firm (should) validate the data and return error messages if it finds any of the input variable values to be inappropriate. For example, if the city name was left blank or if the date supplied was "fred", the query probably should even be attempted since you know it will produce an unrealistic or empty result. Instead, your program should tell the user what problems were found and let them modify the variable values and try again. When you get valid-looking values, you can then attempt to execute the statement with those values replacing the placeholders in the query. There are other approaches to getting the values of the variables but they are not usually appropriate or would be much less attractive to the user. For example, the program could ask for the value of each variable *after* the user has pressed the "go" button but I think most users would prefer to give the values first, then press the "go" button. > Further along, when someone wants to save a search , > say like on Monster, is that done via a view or a > "personal" recordset they can call too when needed? > > I'm reading around and trying to determine the best > way. > What you're describing could be done and sometimes is. For example, an initial search might turn up all of the hotels in a city on a given date that had rooms available regardless of the specific location within the city or the price of the room. A user might very well want to do that to get an initial sense of the range and variety of rooms available. However, the user might want to refine their search after getting that overview to get something a little more specific to their needs. For example, they might want to search just the set that they got with their initial query but then filter out rooms over a certain price and hotels that were more than a certain distance from a given tourist attraction. It would certainly be possible to save the initial result and then query that initial result rather than the full table that the initial query targetted. I'm not sure of the best mechanism to do that in MySQL though since I've never had to do that in MySQL. DB2 provides for temporary tables but I don't recall if MySQL has those. I doubt I would use a view for this purpose, even if you were using a version of MySQL that supported views. (Remember, MySQL is only just acquiring views in the Version 4.1.x stream, which is still in beta. Older versions don't have views at all.) If temporary tables are not available, I would likely use a new table and then delete it when I know I don't need it any more. However, I'd want to do some benchmarking before locking in on that solution: it might actually be cheaper to do the second query against the full table, even though it would have more rows, than to create and populate a new table and pay for the additional storage that the second table would need. You also need to be careful about a proliferation of tables; if you save every query result for every query ever written, in case the user wants to run an additional query against that result, HOW LONG DO YOU SAVE THEM? Just until the user goes to the next screen, after which he starts over? Or do you assume that they might need to check with their spouse first and that the spouse might be unavailable at the moment? In the latter case, you'd likely have to save the result somewhere and save it specifically for that person because it might be hours or days before they had gotten an answer from their spouse. You would also have to decide how long you hang on to the table if the user never comes back to do the second query and then periodically delete "forgotten" tables, otherwise you'd gradually (or maybe quickly!) accumulate a large collection of those tables which would fill up your hard drives over time. Also, remember that saving a result set for a second query is only going to be useful if the subsequent query is going after a subset of the data in the first query result; if it is going after a superset, you'll need to run the second query against the full table. For example, if the first query asked for all hotels in Berlin on a certain date and the second query wanted only luxury Berlin hotels that were downtown on that same date, it might make sense to save that result set. However, if your second query wanted hotels in Berlin *and surrounding area* on a larger range of dates, you are going after a superset and need to query the full table, not the result of the first query. > One last item, if my assumptions are correct in the > passing of variables back to the recordset. In one of > my tables, I have 5 different fields for say "dog > types". dog1 dog2 dog3 dog4 dog5. Can I just use one > user input field and then scan all 5 fields in the > record ? > I'm not 100% clear on what you have in mind here. I'm going to assume that you mean the *table* has one column for "dog" and that query form you are using has five input fields in which users can specify the breed of a dog. (The other possibility is that you have five columns in the table, *each* of which could contain the breed of a dog. That is a lot less likely because a properly normalized table should not have a repeating group like that in the table design under normal circumstances.) If I'm right about the assumption, the answer to your question is "it depends". If the query was always going to be something like: select kennel from breeders where breed = 'terrier' or breed = 'poodle' or breed = 'rottweiler' or breed = 'chihuahua' or breed = 'collie' you should be fine with having a single field on your input form in which they could enter a list of breeds. You'd have to give them some rules on how to enter the data and you'd have to enforce them. For example, you could insist on columns after each breed name or on putting a separate set of apostrophes around each breed name. Otherwise, you'd find it potentially difficult to parse the answers that they gave you. For example, if your input list was: terrier poodle german shepherd you would have trouble recognizing that there are only three breeds in this list; you would probably treat 'german' and 'shepherd' as two different breeds when only a single breed 'german shepherd' is intended. However, things would get much harder for you if your query was sometimes going to be a list of conditions separated by OR -or- AND, like this: select kennel from breeders where breed = 'terrior' and breed = 'poodle' or breed = 'rottweiler' or breed = 'chihuahua' and breed = 'collie' If you put all the breeds in a single input field, you'd have to have some way of figuring out which of the breeds were intended for ANDing and which were intended for ORing. That could get quite ugly, although you may figure out something you could live with. I'd probably have at least two input fields in that case, one for breeds which should be included in the query and one for breeds which should be excluded. > Thank you, > Stuart > Sorry for the beginner questions. > There's nothing wrong with not knowing things. We were all beginners once. Rhino > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
