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
}