This is nowhere definied in the JDBC API contract, but if you’re lucky, the JDBC driver in question may return the complete SQL by just calling PreparedStatement#toString(). I.e. System.out.println(preparedStatement); To my experience, the ones which currently do so are at least the PostgreSQL 8.x and MySQL 5.x JDBC drivers. In the case that your JDBC … Read more
You need to set it in the value itself, not in the prepared statement SQL string. So, this should do for a prefix-match: notes = notes .replace(“!”, “!!”) .replace(“%”, “!%”) .replace(“_”, “!_”) .replace(“[“, “![“); PreparedStatement pstmt = con.prepareStatement( “SELECT * FROM analysis WHERE notes LIKE ? ESCAPE ‘!'”); pstmt.setString(1, notes + “%”); or a suffix-match: … Read more
It’s a scoping error. You’re making $DBH a global variable. So when you enter the function, the global variable is not available. You have 5 real options. 1. Use the global keyword function doSomething() { global $DBH; //… This is not a good idea, since it makes maintenance and testing a PITA. Imagine trying to … Read more
Using PHP 5.6 you can do this easy with help of unpacking operator(…$var) and use get_result() insted of bind_result() public function get_result($sql,$types = null,$params = null) { $stmt = $this->mysqli->prepare($sql); $stmt->bind_param($types, …$params); if(!$stmt->execute()) return false; return $stmt->get_result(); } Example: $mysqli = new database(DB_HOST,DB_USER,DB_PASS,DB_NAME); $output = new search($mysqli); $sql = “SELECT * FROM root_contacts_cfm WHERE root_contacts_cfm.cnt_id … Read more
Using prepared statements, there is no “SQL query” : You have a statement, containing placeholders it is sent to the DB server and prepared there which means the SQL statement is “analysed”, parsed, some data-structure representing it is prepared in memory And, then, you have bound variables which are sent to the server and the … Read more
Although both methods work with * queries, when bind_result() is used, the columns are usually listed explicitly in the query, so one can consult the list when assigning returned values in bind_result(), because the order of variables must strictly match the structure of the returned row. Example 1 for $query1 using bind_result() $query1 = ‘SELECT … Read more
Each method of mysqli can fail. You should test each return value. If one fails, think about whether it makes sense to continue with an object that is not in the state you expect it to be. (Potentially not in a “safe” state, but I think that’s not an issue here.) Since only the error … Read more
You can create a batch by PreparedStatement#addBatch() and execute it by PreparedStatement#executeBatch(). Here’s a kickoff example: public void save(List<Entity> entities) throws SQLException { try ( Connection connection = database.getConnection(); PreparedStatement statement = connection.prepareStatement(SQL_INSERT); ) { int i = 0; for (Entity entity : entities) { statement.setString(1, entity.getSomeProperty()); // … statement.addBatch(); i++; if (i % 1000 … Read more
From the mysqli::prepare docs: The parameter markers must be bound to application variables using mysqli_stmt_bind_param() and/or mysqli_stmt_bind_result() before executing the statement or fetching rows. bind_param docs. i.e.: $name=”one”; $age = 1; $stmt = $mysqli->prepare(“INSERT INTO users (name, age) VALUES (?,?)”); // bind parameters. I’m guessing ‘string’ & ‘integer’, but read documentation. $stmt->bind_param(‘si’, $name, $age); // … Read more
$mysqli->prepare(“SELECT username, password FROM users WHERE username = ?”); $username = $_POST[‘name’]; $stmt->bind_param(‘s’ ,$username); $stmt->execute(); $stmt->bind_result($username, $password); Your select syntax was wrong, the correct syntax is SELECT field1, field2, field3 FROM TABLE WHERE field1 = ? AND field2 = ? To select more fields simply separate them by a comma and not an AND