On 10/17/05, Christopher A. Watford <[EMAIL PROTECTED]> wrote:
> On 10/17/05, garaged <[EMAIL PROTECTED]> 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
[EMAIL PROTECTED]
