Web lists-archives.com

Re: How to retrieve next record?




----- Original Message -----
> From: "Wm Mussatto" <mussatto@xxxxxxx>
> Subject: Re: How to retrieve next record?

> Related what is the form of the prmary key.  If its numeric something like
> $sDBQuery1 = "SELECT * FROM kentekenlogtest WHERE kenteken <
> '$sActueelkenteken' limit 1"
> might work.

No, kenteken is dutch for license plate. If so, not numeric, although greater/less comparisons do work on strings, too. My guess, from the sample queries, would be that this is processing for some form of automated number plate recognition system :-)

Now, Hans, besides pointing you in the right direction, I'm going to be whining a bit about some pet peeves of mine. I'm waiting for the start of a midnight intervention, anyway :-p

That query, as pointed out already, is only asking for a single kenteken. I'll stick to the dutch column names for clarity for other readers, btw - although one of the aforementioned pet peeves is nonenglish variable names. Makes code an absolute bitch to maintain for someone who doesn't speak that language. That's from experience; I've had to debug crap in french and spanish, among other languages.

Your code (or, more precisely, the DB driver) is only going to make those records available to your program that you have explicitly asked for, so that query will only ever make the one record available. You will need to build a query that returns all the records you want to access, or, alternatively, make repeated queries. The former is more efficient by far; the latter is useful if the next set depends on what you find in the previous set.

Another pet peeve: don't use select *. Explicitly select the columns you're looking for. It a) saves network bandwith; b) guards against later table structure changes; c) potentially allows the use of covering indexes and d) reduces the server memory footprint required for sorting etc.

Once you built the correct query, you'll need to have a cursor to loop through it. Your DB driver will probably refer to it as a resultset or a similar denomination. The typical buildup for a database connection (bar advanced abstraction layers) is db_connect (returns a database handle); dbh->execute(sql) (returns a resultset handle); loop using rs->fetch_next (probably returns an array or hash with the data). See your language's db class documentation for the gritty details there. You may also find a fetch_all or similar which returns you the entire resultset in a single call. Can be useful, but remember that that means allocating memory clientside for the entire dataset in one go, instead of reusing the same variables row for row.

A further pet peeve: don't just dump variables into your sql string, use bind variables. The "easy" method opens you up for little Bobby Tables. Google that, if you're unfamiliar with it. Then weep in despair :-p

The idea of bind variables is fairly simple: you stick placeholders in your sql string where you would otherwise use string interpolation; then tell the statement handle the variables that should go in there. The database is actually aware of this method, so there is no chance that the variables might get interpreted as part of the SQL - it KNOWS they're variables, not keywords.

Additionally, if you're going to be executing the same statement repeatedly, use prepared statements instead of regular executes. On MySQL the benefit is marginal (but still noticeable), on other databases it might be considerable - sometimes orders of magnitude faster. Oracle, for instance, has an execution plan cache; so if you use prepared statements, it can skip the whole parse - analyze - pick plan bit and skip straight to the next execution round with the new values you provided. On fast statements (like primary key lookups) that can sometimes save 80% and more of the roundtrip time.

The abovementioned where-clause with limit is probably also going to work; but then you'll need to re-query time after time; and limit does not always work quite intuitively - although in this simple case, it does. If you *must* re-query time after time, do a speed comparison with and without prepared statements; otherwise do go for the fetch_next loop.


Now, you've got documentation to read, I believe. Off you go :-)

/johan

-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql