I am working on a project that is built on an extended version of the default PDO and PDOStatement classes and as such it uses PDO style named parameters instead of the "?" placeholder for variables. However, the client is now requesting that we look into using a different driver because their version OS X Web Server apparently doesn't...
If you are using php5 and mysql5, is there a substantial advantage to using stored procs over prepared statements? ( i read somewhere you may not get substantial performance gains from mysql5 stored proc) ...
I like the flexibility of Dynamic SQL and I like the security + improved performance of Prepared Statements. So what I really want is Dynamic Prepared Statements, which is troublesome to make because bind_param and bind_result accept "fixed" number of arguments. So I made use of an eval() statement to get around this problem. But I ge...
One thing that always been a pain is to log SQL (JDBC) errors when you have a PreparedStatement instead of the query itself. You always end up with messages like: 2008-10-20 09:19:48,114 ERROR LoggingQueueConsumer-52 [Logger.error:168] Error executing SQL: [INSERT INTO private_rooms_bans (room_id, name, user_id, msisdn, nickname) VAL...
I'm using an Oracle database with a collation different to my OS language. I'm accessing the database using the ODBC driver. When I prepare a statement (e.g. a "select * from x where=?"), that involves special non-ASCII characters supported by the DB's collation, I'm finding the data row with the characters. When I execute the select dir...
How do i take advantage of MySQL's ability to cache prepared statements? One reason to use prepared statements is that there is no need to send the prepared statement itself multiple times if the same prepared statement is to be used again. Class.forName("com.mysql.jdbc.Driver"); Connection conn = DriverManager.getConnection("jdbc:mysq...
I used to use the standard mysql_connect(), mysql_query(), etc statements for doing MySQL stuff from PHP. Lately I've been switching over to using the wonderful MDB2 class. Along with it, I'm using prepared statements, so I don't have to worry about escaping my input and SQL injection attacks. However, there's one problem I'm running in...
I was wondering if there was a way to bind an ArrayList (or any kind of List, for that matter) to a PreparedStatement which will eventually be used to access an Oracle database. I found: http://stackoverflow.com/questions/178479/alternatives-for-java-sql-preparedstatement-in-clause-issue And that seems similar to my issue, but this qu...
According to the documentation, a prepared query provides a significant performance benefit if you're running a query multiple times because the overhead of the MySQL server parsing the query only happens once. I'm wondering what exactly they mean by "multiple times" there. I.e., say you have a web page that runs a query one time. Now s...
How would you write a prepared MySQL statement in PHP that takes a differing number of arguments each time. An example such query is: SELECT age, name FROM people WHERE id IN (12, 45, 65, 33) The IN CLAUSE will have a different number of id's each time it is run. I have two possible solutions in my mind but want to see if there is a b...
Here's my current SQL statement: SEARCH_ALBUMS_SQL = "SELECT * FROM albums WHERE title LIKE ? OR artist LIKE ?;"; It's returning exact matches to the album or artist names, but not anything else. I can't use a '%' in the statement or I get errors. How do I add wildcards to a prepared statement? (I'm using Java5 and MySQL) Thanks! ...
I've been using cmd.Parameters.AddWithValue, and not specifying a DBType (int, varchar,...) to run queries. After looking at SQL Profiler, it seems that queries run with this method run a lot slower than when you specify the data type. To give you an idea of how much slower it is, here's an example. The query is a simple lookup on a...
In a mysqli prepared statement, a NULL gets turned into '' (in the case of a string) or 0 (in the case of an integer). I would like to store it as a true NULL. Is there any way of doing this? ...
I have several instances where that a section of legacy sql statements is based on a dependency. for example. if (x !=null) { SQL = "SELECT z WHERE x > y"; } else { SQL = "SELECT z WHERE x <= y"; } SQL2 = SQL + " JOIN a ON b"; I am creating PreparedStatements out of this legacy code. What is the best-practice here. Should I cre...
I have a query like this - select * from tbl where ... and colname in (2,3,4) When I prepare the query (... ' colname in (?)' ) using a PreparedStatement in Java, what setter method should I call to set these integers ? The integers are available in an int array and the size varies. If it matters, the database is MySQL and the column i...
What is the recommended method for escaping variables before inserting them into the database in Java? As I understand, I can use PreparedStatement.setString() to escape the data, but PreparedStatement seems somewhat impractical if I don't plan to run the same query ever again.. Is there a better way to do it without preparing every que...
I am planning to replace repeatedly executed Statement objects with PreparedStatement objects to improve performance. I am using arguments like the MySQL function now(), and string variables. Most of the PreparedStatement queries I have seen contained constant values (like 10, and strings like "New York") as arguments used for the "?" ...
I'm using named parameters in a query to match fields in a map-like data structure. The data structure can have fields, or another map-like data structure. This nested structure is repeatable ad nauseum. I would like to name the parameters in the query using an XPath like language, that can be parsed to indicate further nested lookups. ...
How do I use prepared statements in SQlite in Android? ...
I have some code that produces a set of primary key values that I want to delete from a database table. long[] keysToDelete = { 0, 1, 2, 3 }; and I'd like to use a PreparedStatement to execute the equivalent of DELETE FROM MyTable WHERE myPrimaryKey IN (0, 1, 2, 3); Any idea how? ...