On 10/17/05, Christopher A. Watford christopher.watford@gmail.com wrote:
On 10/17/05, garaged garaged@gmail.com wrote:
Do you think is cleaner or easy to understand to do prepared queries vs correct quotation??
It is much cleaner to do (psuedo):
q = "SELECT field1, field2 FROM table1 WHERE fieldX = :? AND fieldY = :?"; statement = prepare(q); bind_outvalue(statement, 0, &field1, SQL_INT); bind_outvalue(statement, 1, &field2, SQL_BOOLEAN); bind_invalue(statement, 0, &fieldX, SQL_INT); bind_invalue(statement, 1, &fieldY, SQL_STRING); query(statement);
print field1, field2;
rather than:
if(!is_int(fieldX)) error;
if(!is_string(fieldY)) error;
q = "SELECT field1, field2 FROM table1 WHERE fieldX = " + fieldX + " AND fieldY = " + quote(fieldY);
result = query(q); row = get_row(result); field1 = row[0]; field2 = row[1];
if(!is_int(field1))
You have to remember exactly the correct sequence of parameters for every query. I'm not that good with memory, but I migth be one in a million.
Max
Gmail sent too soon, but you get the point. Error checking done by the database is cleaner and more maintainable. Types are checked, maintainability is increased. Plus your query is cached if the DB supports it!
Say you have a massive IN (...) clause that is static in your WHERE. Optimizations made on the static IN clause will have happened w/ a prepared statement, and won't have to be made again the next time you call the query. Speeding up the time to call the query.
Also, prepared queries batch multiple inserts MUCH faster:
myArray = int[500]; .. populate myArray ... q = "INSERT INTO myTable (myNum) VALUES (?)"; statement = prepare(q); bind_invalue(statement, 0, myArray, SQL_INT, 500);
while((e = query(q)) != SQL_SUCCESS) { if(e == SQL_ERROR) error;
if(e == SQL_MORE) continue; }
-- Christopher A. Watford christopher.watford@gmail.com