Running queries

When you don’t need a transaction or run single queries, you can run queries directly on the pool; the pool will use one of its connection to run the query and return the result to you.

Here is how to run simple queries:

client.query("SELECT * FROM users WHERE id='julien'").execute() { |ar_err,ar|
  if (ar_err == nil)
    result = ar
    puts "Got #{result.size()} rows "
  else
    puts "Failure: #{ar_err.get_message()}"
  end
}

Prepared queries

You can do the same with prepared queries.

The SQL string can refer to parameters by position, using the database syntax {PREPARED_PARAMS}

require 'vertx-sql-client/tuple'
client.prepared_query("SELECT * FROM users WHERE id=?").execute(VertxSqlClient::Tuple.of("julien")) { |ar_err,ar|
  if (ar_err == nil)
    rows = ar
    puts "Got #{rows.size()} rows "
  else
    puts "Failure: #{ar_err.get_message()}"
  end
}

Query methods provides an asynchronous RowSet instance that works for SELECT queries

client.prepared_query("SELECT first_name, last_name FROM users").execute() { |ar_err,ar|
  if (ar_err == nil)
    rows = ar
    rows.each do |row|
      puts "User #{row.get_string(0)} #{row.get_string(1)}"
    end
  else
    puts "Failure: #{ar_err.get_message()}"
  end
}

or UPDATE/INSERT queries:

require 'vertx-sql-client/tuple'
client.prepared_query("INSERT INTO users (first_name, last_name) VALUES (?, ?)").execute(VertxSqlClient::Tuple.of("Julien", "Viet")) { |ar_err,ar|
  if (ar_err == nil)
    rows = ar
    puts rows.row_count()
  else
    puts "Failure: #{ar_err.get_message()}"
  end
}

The Row gives you access to your data by index

puts "User #{row.get_string(0)} #{row.get_string(1)}"

or by name

puts "User #{row.get_string("first_name")} #{row.get_string("last_name")}"

The client will not do any magic here and the column name is identified with the name in the table regardless of how your SQL text is.

You can access a wide variety of of types

firstName = row.get_string("first_name")
male = row.get_boolean?("male")
age = row.get_integer("age")

# ...

You can use cached prepared statements to execute one-shot prepared queries:

require 'vertx-sql-client/tuple'

# Enable prepare statements caching
connectOptions['cachePreparedStatements'] = true
client.prepared_query("SELECT * FROM users WHERE id = ?").execute(VertxSqlClient::Tuple.of("julien")) { |ar_err,ar|
  if (ar_err == nil)
    rows = ar
    puts "Got #{rows.size()} rows "
  else
    puts "Failure: #{ar_err.get_message()}"
  end
}

You can create a PreparedStatement and manage the lifecycle by yourself.

require 'vertx-sql-client/tuple'
sqlConnection.prepare("SELECT * FROM users WHERE id = ?") { |ar_err,ar|
  if (ar_err == nil)
    preparedStatement = ar
    preparedStatement.query().execute(VertxSqlClient::Tuple.of("julien")) { |ar2_err,ar2|
      if (ar2_err == nil)
        rows = ar2
        puts "Got #{rows.size()} rows "
        preparedStatement.close()
      else
        puts "Failure: #{ar2_err.get_message()}"
      end
    }
  else
    puts "Failure: #{ar_err.get_message()}"
  end
}

Batches

You can execute prepared batch

require 'vertx-sql-client/tuple'

# Add commands to the batch
batch = Array.new
batch.push(VertxSqlClient::Tuple.of("julien", "Julien Viet"))
batch.push(VertxSqlClient::Tuple.of("emad", "Emad Alblueshi"))

# Execute the prepared batch
client.prepared_query("INSERT INTO USERS (id, name) VALUES (?, ?)").execute_batch(batch) { |res_err,res|
  if (res_err == nil)

    # Process rows
    rows = res
  else
    puts "Batch failed #{res_err}"
  end
}