This function prepares an SQL query when you use SQL variables in it. SQL variables are case-insensitive.
Read more about using Using SQL variables.
Syntax
DB_PrepareSQL(SQL)
Arguments
SQL
a query with variables.
Return value
The prepared SQL query.
Example 1
In the AfterAdd event, you can use:
sql = DB_PrepareSQL("insert into log (lastid) values (:new.id)")
DB_Exec( sql )
Example 2
You can use this code in the Server event of a Custom Button or in Field events for the View/Edit pages:
sql = DB_PrepareSQL("insert into log (lastname) values (:name)")
DB_Exec( sql )
Example 3
You can also use several variables within the function:
sql = DB_PrepareSQL("select * from customers where username=':session.userid' and age>:1 and last_name=':2'", 20, "smirnoff")
The resulting query will look like this:
select * from customers where username='jsmith' and age>20 and last_name='smirnoff'
Note: the '20' and 'smirnoff' in the example above can be replaced with any ASP function or variable.
Note: this function will also protect you from "bad" characters and SQL injection.
Imagine the situation where we need to select all customers where last name is O'Rourke. If we simply build the SQL query and concatenate the value with an apostrophe, our SQL will break not to mention that it opens the doors welcoming SQL injection. PrepareSQL function takes care of this.
sql = DB_PrepareSQL("select * from customers where last_name=':1'", "O'Rourke")
This will produce a proper SQL query with apostrophe character properly screened.
select * from customers where last_name='O\'Rourke'
See also:
•QueryResult object: fetchNumeric()
•QueryResult object: fetchAssoc()