Vert.x Common SQL interface

The common SQL interface is used to interact with Vert.x SQL services.

You obtain a connection to the database via the service interface for the specific SQL service that you are using (e.g. JDBC/MySQL/PostgreSQL).

The SQL Connection

A connection to the database is represented by SQLConnection.

Auto-commit

When you obtain a connection auto commit is set to true. This means that each operation you perform will effectively execute in its own transaction.

If you wish to perform multiple operations in a single transaction you should set auto commit to false with setAutoCommit.

When the operation is complete, the handler will be called:

connection.set_auto_commit(false) { |res,res_err|
  if (res_err == nil)
    # OK!
  else
    # Failed!
  end
}

Executing queries

To execute a query use query

The query string is raw SQL that is passed through without changes to the actual database.

The handler will be called with the results, represented by ResultSet when the query has been run.

connection.query("SELECT ID, FNAME, LNAME, SHOE_SIZE from PEOPLE") { |res,res_err|
  if (res_err == nil)
    # Get the result set
    resultSet = res
  else
    # Failed!
  end
}

The ResultSet instance represents the results of a query.

The list of column names are available with getColumnNames, and the actual results available with getResults

The results are a list of JsonArray instances, one for each row of the results.

columnNames = resultSet['columnNames']

results = resultSet['results']

results.each do |row|

  id = row[0]
  fName = row[1]
  lName = row[2]
  shoeSize = row[3]

end

You can also retrieve the rows as a list of Json object instances with getRows - this can give you a somewhat simpler API to work with, but please be aware that SQL results can contain duplicate column names - if that’s the case you should use getResults instead.

Here’s an example of iterating through the results as Json object instances:

rows = resultSet['rows']

rows.each do |row|

  id = row["ID"]
  fName = row["FNAME"]
  lName = row["LNAME"]
  shoeSize = row["SHOE_SIZE"]

end

Prepared statement queries

To execute a prepared statement query you can use queryWithParams.

This takes the query, containing the parameter place holders, and a JsonArray or parameter values.

query = "SELECT ID, FNAME, LNAME, SHOE_SIZE from PEOPLE WHERE LNAME=? AND SHOE_SIZE > ?"
params = [
  "Fox",
  9
]

connection.query_with_params(query, params) { |res,res_err|

  if (res_err == nil)
    # Get the result set
    resultSet = res
  else
    # Failed!
  end
}

Executing INSERT, UPDATE or DELETE

To execute an operation which updates the database use update.

The update string is raw SQL that is passed through without changes to the actual database.

The handler will be called with the results, represented by UpdateResult when the update has been run.

The update result holds the number of rows updated with getUpdated, and if the update generated keys, they are available with getKeys.

columnNames = resultSet['columnNames']

results = resultSet['results']

results.each do |row|

  id = row[0]
  fName = row[1]
  lName = row[2]
  shoeSize = row[3]

end

Prepared statement updates

To execute a prepared statement update you can use updateWithParams.

This takes the update, containing the parameter place holders, and a JsonArray or parameter values.

update = "UPDATE PEOPLE SET SHOE_SIZE = 10 WHERE LNAME=?"
params = [
  "Fox"
]

connection.update_with_params(update, params) { |res,res_err|

  if (res_err == nil)

    updateResult = res

    puts "No. of rows updated: #{updateResult['updated']}"

  else

    # Failed!

  end
}

Executing other operations

To execute any other database operation, e.g. a CREATE TABLE you can use execute.

The string is passed through without changes to the actual database. The handler is called when the operation is complete

sql = "CREATE TABLE PEOPLE (ID int generated by default as identity (start with 1 increment by 1) not null,FNAME varchar(255), LNAME varchar(255), SHOE_SIZE int);"

connection.execute(sql) { |execute,execute_err|
  if (execute_err == nil)
    puts "Table created !"
  else
    # Failed!
  end
}

Using transactions

To use transactions first set auto-commit to false with setAutoCommit.

You then do your transactional operations and when you want to commit or rollback use commit or rollback.

Once the commit/rollback is complete the handler will be called and the next transaction will be automatically started.

# Do stuff with connection - updates etc

# Now commit

connection.commit() { |res,res_err|
  if (res_err == nil)
    # Committed OK!
  else
    # Failed!
  end
}

Closing connections

When you’ve done with the connection you should return it to the pool with close.