prepared-statement

How to set query parameters in MySQL Query Browser?

Can the MySQL Query Browser set parameters of a parameterized query? If so, how? I tried populating the Parameter Browser tab but it doesn't seem to actually set parameters when I execute the query. I searched for quite a while in Google (e.g. mySQL Query Browser parameterized) but had no luck finding the answer. I found this threa...

How to set list of parameters on prepared statement?

i have a list of names e.g.: List<String> names = ... names.add('charles'); ... and a statement: PreparedStatement stmt = conn.prepareStatement('select * from person where name in ( ? )'); how to do the following: stmt.setParameterList(1,names); Is there a workaround? can someone explain why this method is missing? using: ja...

How to get the query plan from a prepared statment

I don't remember ever seeing a way to use prepared statements from the console and somehow don't think running an explain query thought as a prepared statement from the API will get what I want. This is related to this old question of mine. I'm primarily interested in MySQL but would be interested in other DBs as well. ...

How to bypass JDBC statement cache in concurrent batch processing?

I'm developing a server that should receive nightly reports from hundreds of business units. Reports are currently encrypted csv-files. In total the reports should amount to 500 000 to 1 000 000 records each day that are saved to a database for later use. I've create a set of PreparedStatements for each transmission. These statements ar...

Java - Prepared statements and arrays

How can I handle an array in a prepared statement? i.e, I want to do a query and one of the parameters I get is an array of strings which I want to use in the query (Don't select rows that have a field that's in the array)? ...

Using wildcards in prepared statement - MySQLi

Hi! I'm trying to run the following query, and I'm having trouble with the wildcard. function getStudents() { global $db; $users = array(); $query = $db->prepare("SELECT id, adminRights FROM users WHERE classes LIKE ? && adminRights='student'"); $query->bind_param('s', '%' . $this->className . '%'); $query->...

PreparedStatement setNull(..)

Java PreparedStatement provides a possibility to explicitely set a Null value. This possibility is: prepStmt.setNull(<n>, Types.VARCHAR) Are the semantics of this call the same as when using a setType with a null value? prepStmt.setString(null) ? ...

Are there downsides to using prepared statements?

I've been reading a lot about prepared statements and in everything I've read, no one talks about the downsides of using them. Therefore, I'm wondering if there are any "there be dragons" spots that people tend to overlook? ...

prepared-statement pass by reference error

I'm not seeing the error and was hoping someone could figure it out: public static function createMessage($title, $message, $startDate, $endDate, $author, $status){ //$dbConn is now a mysqli instance with a connection to the database foobar $dbConn = Database::getConnection("foobar"); $stmt = $dbConn->prepare("INSERT...

Is ther a way to retrieve the autoincrement ID from a prepared statement

Is there a way to retrieve the auto generated key from a DB query when using a java query with prepared statements. For example, I know AutoGeneratedKeys can work as follows. stmt = conn.createStatement(); stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS); if(returnLastInsertId) { ResultSet rs = stmt.getGeneratedKeys(); ...

How can I abstract mysqli prepared statements in PHP?

I'm using my own class for database queries, extending mysqli: class iDatabase extends mysqli { public $errorMsg; private $totalQueries; private $stmt; public function __construct() { parent::__construct( 'localhost', 'asd', 'asd', 'asd' ); if ( mysqli_connect_errno() ) { $this->errorMsg = 'Could not connect to server.<...

MySQL query sets field to 0 instead of blank string

This one's puzzling me. I have a MySQL query, being run though PDO: $stmt = $db->prepare( "UPDATE member SET acode='' AND status='active' WHERE username=:u" ); $stmt->bindValue( ':u', $member->username, PDO::PARAM_STR ); $stmt->execute(); The acode field gets set to 0 for some reason. It was created with `acode` varchar(8) NOT NULL ...

Get autoincrement id after an insert query performed with a prepared statement

If I execute an insert query with a stored procedure with php and the mysqli_* functions, is there a way to retrieve the value of an autoincrement id field? mysqli->insert_id does not seem to work. ...

Dynamic MySQL with local variables

How can I use dynamic SQL statements in MySQL database and without using session variables? Right now I have such a code (in MySQL stored procedure): (...) DECLARE TableName VARCHAR(32); SET @SelectedId = NULL; SET @s := CONCAT("SELECT Id INTO @SelectedId FROM ", TableName, " WHERE param=val LIMIT 1"); PREPARE stmt FROM @s; EXECUTE stm...

Help with mysqli_stmt_store_result()

I'm having trouble trying to use $stmt->store_result() after executing a prepared statement. The manual says "Transfers a result set from a prepared statement", but to where? I would ideally like to use it, all nice an buffered in memory, like mysqli->query() returns. The php documentation examples make it seem like the only good reaso...

Unable to close JDBC resources!!!

We are running a websphere commerce site with an oracle DB and facing an issue where we are running out of db connections. We are using a JDBCHelper singleton for getting the prepared statements and cosing the connections. public static JDBCHelper getJDBCHelper() { if (theObject == null){ ...

JDBC - prepareStatement - How should I use it?

I saw this example somewhere: rs = connection.prepareStatement("select * from table").executeQuery(); Could I use this format, if I want to execute a query like this "Select * from table where column = "hello" "? The way in which I usual I use prepareStatement object is something like this: String sql = "select * from adre...

Prepared Statements with functions syntax

I'm getting an error when I'm trying to run this: mysql> prepare stmt1 from "UPDATE test.users SET password = password('?') WHERE usrid = ?"; mysql> prepare stmt1 from "UPDATE test.users SET password = password(?) WHERE usrid = ?"; How can I make a prepared statement where a function takes my variable as an argument? ...

MySQL Prepared Statements vs Stored Procedures Performance

Hi there, I have an old MySQL 4.1 database with a table that has a few millions rows and an old Java application that connects to this database and returns several thousand rows from this this table on a frequent basis via a simple SQL query (i.e. SELECT * FROM people WHERE first_name = 'Bob'. I think the Java application uses client si...

Prepared statement - using a function as part of the where clause

I am working with a Java prepared statement that gets data from an Oracle database. Due to some performance problems, the query uses a "virtual column" as an index. The query looks like this: String status = "processed"; String customerId = 123; String query = "SELECT DISTINCT trans_id FROM trans WHERE status = " + status + " AND FN_GE...