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'") { |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=?", 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") { |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 (?, ?)", 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 cache prepared queries:
# Enable prepare statements caching
connectOptions['cachePreparedStatements'] = true
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_batch("INSERT INTO USERS (id, name) VALUES (?, ?)", batch) { |res_err,res|
if (res_err == nil)
# Process rows
rows = res
else
puts "Batch failed #{res_err}"
end
}