|
Re: Is bind useful for one-time queries and commands?
by psini (Deacon) on May 18, 2008 at 13:58 UTC
|
I disagree.
Bing is mainly useful for it separes the "code" and "data" parts of your query and checks the validity of data.
For instance, if you bind an integer parameter, bind checks that the data really is an integer value; more important, if you bind a string parameter bind escapes the characters that could be interpreted as statement by the DBMS and doing so it prevents the all-present risk of code injection if data comes from user input.
All this, checks and escaping, could be done by the program but always binding your data is a good programming practice for you can always forget a check leaving a security hole, but if you forget a bind your program doesn't work
Rule One: Do not act incautiously when confronting a little bald wrinkly smiling man.
| [reply]
[d/l] |
|
|
if you bind a string parameter bind escapes the characters that could be interpreted as statement by the DBMS and doing so it prevents the all-present risk of code injection if data comes from user input.
That's what placeholders are for, too. I mean, duh!
You can combine use of placeholders with bind_param (between prepare and the first execute) to make sure only certain datatypes are acceptable.
In fact, I had to do this when I used DBD::ODBC with MS-Access, in case a parameter was undefined in the first call to execute, because otherwise, DBI performed an implicit bind_param with possibly the wrong type, and I occasionally got an error in one of the next loops, with a defined value for that parameter.
| [reply]
[d/l]
[select] |
Re: Is bind useful for one-time queries and commands?
by dragonchild (Archbishop) on May 18, 2008 at 14:26 UTC
|
Frankly, I use placeholders everywhere. And, if I remember correctly, bind and placeholders are both implemented internally the same way.
Now, YMMV with certain DBDs, such as DB2. I dunno. Just pick one and be consistent. Or, better yet, wrap the DB call. :-)
My criteria for good software:
- Does it work?
- Can someone else come in, make a change, and be reasonably certain no bugs were introduced?
| [reply] |
Re: Is bind useful for one-time queries and commands?
by CountZero (Bishop) on May 18, 2008 at 15:17 UTC
|
I never use bind. It smells too much of action at a distance.And as far as type-checking is concerned, I understood it is implemented at the DBD level, so it may be there or partially there or not at all depending on the driver you use, so it is not fully portable. On the other hand I may have to read-up on the docs of the latest version of DBI ... Better to do these typechecks yourself, esp. on data that may be tainted.
CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James
| [reply]
[d/l] |
|
|
Better to do these typechecks yourself, esp. on data that may be tainted.
…seems like poor advice. It amounts to: I'm sure those of you here to take this advice know what you are doing better than the 100s of contributors and bug reporters over the last 10 years to the various DBD projects. So don't rely on them, start from scratch.
Anyone with that level of expertise -- guaranteed to get it right -- should instead read the DBD source(s) and contribute patches if necessary. Anyone without that level of expertise should pretty much expect to get it wrong or miss edge cases compared to what's there already.
| [reply] |
|
|
Oh no not at all, I just meant to say that in my understanding --which may be wrong-- the typechecking is not at the same level in all of the DBD-modules and thus it would be dangerous to generally rely on it unless you have checked that this feature is fully implemented.
CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James
| [reply] |
Re: Is bind useful for one-time queries and commands?
by DrHyde (Prior) on May 19, 2008 at 09:49 UTC
|
If you think that they might be slower, but don't know, then that tells me you haven't benchmarked it. Furthermore, you probably haven't profiled the code to see if it's even worth bothering to try to find a speed-up in that area. Until you know that you have a speed problem to solve, and that the binds are the problem, you shouldn't consider removing them to make stuff faster. Only consider removing them to make the code easier to read. | [reply]
[d/l] |
|
|
If you think that they might be slower, but don't know, then that tells me you haven't benchmarked it.
Exactly right -- neither have I peered into the DBI code, but I will do both before I make any sweeping changes. But I'm doing this because I want to find out what the best possible solution is, in terms of safety (not crashing, getting the right results without error) and also in terms of clarity (getting the code to be as clutter-free as possible).
Furthermore, you probably haven't profiled the code to see if it's even worth bothering to try to find a speed-up in that area.
That's an interesting one -- because part of the profiling will be on the client machine (where my code is located) and part if it will be on the server (where DB2 is). But I'll see what I can do; I've already talked with the DBA and I'm sure I can ask him to help out with this exercise.
Until you know that you have a speed problem to solve, and that the binds are the problem, you shouldn't consider removing them to make stuff faster. Only consider removing them to make the code easier to read.
I'm equally concerned about speed and readability, but if pressed would probably favour the latter. You can always buy more hardware, but you may not be able to afford another two weeks while a developer tries to understand some chunk of code with half a dozen authors. Or, worse, goes ahead with some change that turns out to be wrong because she/he couldn't understand the code.
Alex / talexb / Toronto
"Groklaw is the open-source mentality applied to legal research" ~ Linus Torvalds
| [reply]
[d/l] |
Re: Is bind useful for one-time queries and commands?
by ruzam (Curate) on May 19, 2008 at 21:45 UTC
|
Any query to a DB2 server must first go through a bind step. The bind step not only validates the query and parameters, but it also 'compiles' the query into the most efficient form (as the database engine sees it) given the data queried, indexes available, table statistics, etc, etc. The results of a bind (the final query executed internally) one day, may not be the same as the results of a bind on another day because contents of one or more tables may have grown (or shrunk) making it more efficient to execute the query differently now. This may not appear to make much sense if you're only querying one table, but if your joining multiple tables with sub-selects, it can have a real impact. The bind step is a fact of life with DB2. You either do it in advance or the DB2 server will do it for you internally when the query is executed.
There's significant overhead involved in the bind step. If you're making repetitive database operations, that overhead can be greatly reduced by binding the queries in advance. When the query is bound, DB2 stores the final compiled version of the query within its own environment and the program executes the stored, bound query directly, bypassing the internal bind step on every call. In compiled languages like COBOL, you submit a pre-compile step which scans the code for queries, binds them, then comments the original code replacing it with new calls to the bound queries. If the database statistics change significantly later, the bound queries may become inefficient again and will require re-binding. Rebinding existing stored queries happens automatically with a database re-org (so don't have to continually update your code, but you do have to bring the databases down for the re-org). If your queries are ad-hoc, one time operations, then there's nothing to be gained by binding in advance.
At least that's how binding in DB2 works (to my recollection) with compiled languages. I don't know how DBI and interpreted Perl handle the bind step. It may very well be that what you call a bind step through the DBI interface may have nothing to do with the DB2 bind step.
| [reply] |
|
|
| [reply] |
|
|
The DB2 server will execute the bind step internally if it has not already been done by the application. In other words, DB2 will intercept it's own query requests and bind them (temporarily) before actually getting the data. I don't know if or how long DB2 might cache ad hoc query requests, it's probably configurable. A cached query request would effectively eliminate the need for a bind step at all from the application side.
I have experience with DB2, but not DB2 and Perl. It's an educated guess.
| [reply] |
|
|
|
|
| [reply] |
|
|
check the following sample code ( for sabari )..
#Establish the connection to the database.
my $dbh = DBI->connect( 'dbi:Pg:database=test','test','1234',{RaiseErr
+or => 1,AutoCommit => 0})
|| die "Database connection not made: $DBI::errstr";
#It get the port number and IP address from the data base.
my $sql = qq{ SELECT port,ipaddr,log_file FROM details where name='$na
+me'};
my $sth = $dbh->prepare( $sql );
$sth->execute();
#bind_columns - It stored the port number and IP address to variable.
my( $port, $ipaddr,$log_file );
$sth->bind_columns(\$port, \$ipaddr,\$log_file);
#It fetch the values from table
$sth->fetch();
$sth->finish();
#Dis-connect the database,if we miss it tells warnings, that destroy h
+ad been
#called with out disconnect.
$dbh->disconnect();
| [reply]
[d/l] |
Re: Is bind useful for one-time queries and commands?
by runrig (Abbot) on May 20, 2008 at 17:33 UTC
|
I don't know about DB2, but some databases will cache every SQL statement (along with its query plan), so there can still be a benefit from using placeholders even if the statement is only run once in your program. | [reply] |