Databases
StackOverflow2013
Postsdbo
POwhy does execute() return true on an empty table?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POwhy does execute() return true on an empty table?
    text
    copied!<p>Through the following snippet, I am trying to run a query that either updates the data or inserts a new data into the table named <code>JustPinged</code>. The table contains a column named <code>NodesThatJustPinged</code> and <code>LastPingedAt</code>. If there is already a <em>node</em> in <code>NodesThatJustPinged</code> then the time in milliseconds in <code>LastPingedAt</code> is updated. Otherwise a new <code>node</code> information is inserted.</p> <p>The problem is, that the following snippet is unable to insert the data into the database's table. The reason is the statement:</p> <pre><code>boolean duplicateExists = searchToEliminateDuplicates.execute(); </code></pre> <p>returns <code>true</code> to start with. (Initially the table is empty) Why does this statement return true? According to the <a href="http://docs.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html#execute%28%29" rel="nofollow">documentation</a> it returns <em>true if the first result is a ResultSet object; false if the first result is an update count or there is no result.</em> So here the boolean should contain a false value. But it contains a <code>true</code> value and thus the <code>if</code> statement always works. (And in <code>if</code> section,update query works when there is nothing to update !) </p> <pre><code>String searchQuery = "select NodesThatJustPinged from JustPinged where NodesThatJustPinged = '" + nodeInfo + "'"; PreparedStatement searchToEliminateDuplicates = connection.prepareStatement(searchQuery); boolean duplicateExists = searchToEliminateDuplicates.execute(); if(duplicateExists) { // update the LastPingedAt column in the JustPinged table String updateQuery = "update JustPinged set LastPingedAt='" + pingedAt + "' where NodesThatJustPinged = '" + nodeInfo + "'"; PreparedStatement updateStatement = connection.prepareStatement(updateQuery); updateStatement.executeUpdate();System.out.println("If statement"); } else { // make a new entry into the database String newInsertionQuery = "insert into JustPinged values('" + nodeInfo + "','" + pingedAt + "')"; PreparedStatement insertionStatement = connection.prepareStatement(newInsertionQuery); insertionStatement.executeUpdate();System.out.println("else statement"); } </code></pre> <p>So how should I edit the code, so that duplicate values are updated and new values are inserted?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload