PDF (US Ltr)
- 1.4Mb
PDF (A4)
- 1.4Mb
When you execute an SQL operation on a Session using the
sql() method an SqlResult is returned.
Iterating an SqlResult is identical to working with results from
CRUD operations. The following example assumes that the users
table exists.
MySQL Shell JavaScript Code
var res = mySession.sql('SELECT name, age FROM users').execute();
var row;
while (row = res.fetchOne()) {
print('Name: ' + row['name'] + '\n');
print(' Age: ' + row.age + '\n');
}MySQL Shell Python Code
res = mySession.sql('SELECT name, age FROM users').execute()
row = res.fetch_one()
while row:
print('Name: %s\n' % row[0])
print(' Age: %s\n' % row.age)
row = res.fetch_one()Node.js JavaScript Code
mySession.sql('SELECT name, age FROM users')
.execute()
.then(res => {
while (row = res.fetchOne()) {
console.log('Name: ' + row[0] + '\n');
console.log(' Age: ' + row[1] + '\n');
}
});Alternatively, you can use callbacks:
mySession.sql('SELECT name, age FROM users')
.execute(function (row) {
console.log('Name: ' + row[0] + '\n');
console.log(' Age: ' + row[1] + '\n');
});C# Code
var res = Session.SQL("SELECT name, age FROM users").Execute();
while (res.Next())
{
Console.WriteLine("Name: " + res.Current["name"]);
Console.WriteLine("Age: " + res.Current["age"]);
}Python Code
# Connector/Python
res = mySession.sql('SELECT name, age FROM users').execute()
row = res.fetch_one()
while row:
print('Name: %s\n' % row[0])
print(' Age: %s\n' % row.age)
row = res.fetch_one()Java Code
SqlResult res = mySession.sql("SELECT name, age FROM users").execute();
Row row;
while ((row = res.fetchOne()) != null) {
System.out.println(" Name: " + row.getString("name") + "\n");
System.out.println(" Age: " + row.getInt("age") + "\n");
}C++ Code
SqlResult res = mysession.sql("SELECT name, age FROM users").execute();
Row row;
while ((row = res.fetchOne())) {
cout << "Name: " << row[0] << endl;
cout << " Age: " << row[1] << endl;
}
SqlResult differs from results returned by CRUD operations in the
way how result sets and data sets are represented. A SqlResult
combines a result set produced by, for example,
INSERT, and a data set, produced
by, for example, SELECT in one.
Unlike with CRUD operations there is no distinction between the
two types. A SqlResult exports methods for data access and to
retrieve the last inserted id or number of affected rows.
Use the hasData() method to learn whether a
SqlResult is a data set or a result. The method is useful when
code is to be written that has no knowledge about the origin of a
SqlResult. This can be the case when writing a generic application
function to print query results or when processing stored
procedure results. If hasData() returns true,
then the SqlResult origins from a
SELECT or similar command that can
return rows.
A return value of true does not indicate whether the data set
contains any rows. The data set can be empty, for example it is
empty if fetchOne() returns NULL or
fetchAll() returns an empty list. The following
example assumes that the procedure my_proc
exists.
MySQL Shell JavaScript Code
var res = mySession.sql('CALL my_proc()').execute();
if (res.hasData()){
var row = res.fetchOne();
if (row){
print('List of rows available for fetching.');
do {
print(row);
} while (row = res.fetchOne());
}
else{
print('Empty list of rows.');
}
}
else {
print('No row result.');
}MySQL Shell Python Code
res = mySession.sql('CALL my_proc()').execute()
if res.has_data():
row = res.fetch_one()
if row:
print('List of rows available for fetching.')
while row:
print(row)
row = res.fetch_one()
else:
print('Empty list of rows.')
else:
print('No row result.')Node.js JavaScript Code
mySession.sql('CALL my_proc()')
.execute()
.then(function (res) {
if (!res.hasData()) {
return console.log('No row result.');
}
var row = res.fetchOne();
if (!row) {
return console.log('Empty list of rows.');
}
console.log('List of rows available for fetching.');
do {
console.log(row);
} while (row = res.fetchOne());
})C# Code
var res = Session.SQL("CALL my_proc()").Execute();
if (res.HasData)
{
var row = res.FetchOne();
if (row != null)
{
Console.WriteLine("List of rows available for fetching.");
do
{
PrintResult(row);
} while ((row = res.FetchOne()) != null);
}
else
{
Console.WriteLine("Empty list of rows.");
}
}
else
{
Console.WriteLine("No row result.");
}Python Code
# Connector/Python
res = mySession.sql('CALL my_proc()').execute()
if res.has_data():
row = res.fetch_one()
if row:
print('List of rows available for fetching.')
while row:
print(row)
row = res.fetch_one()
else:
print('Empty list of rows.')
else:
print('No row result.')Java Code
SqlResult res = mySession.sql("CALL my_proc()").execute();
if (res.hasData()){
Row row = res.fetchOne();
if (row != null){
System.out.println("List of rows available for fetching.");
do {
for (int c = 0; c < res.getColumnCount(); c++) {
System.out.println(row.getString(c));
}
} while ((row = res.fetchOne()) != null);
}
else{
System.out.println("Empty list of rows.");
}
}
else {
System.out.println("No row result.");
}C++ Code
SqlResult res = mysession.sql("CALL my_proc()").execute();
if (res.hasData())
{
Row row = res.fetchOne();
if (row)
{
cout << "List of rows available for fetching." << endl;
do {
cout << "next row: ";
for (unsigned i=0 ; i < row.colCount(); ++i)
cout << row[i] << ", ";
cout << endl;
} while ((row = res.fetchOne()));
}
else
{
cout << "Empty list of rows." << endl;
}
}
else
{
cout << "No row result." << endl;
}
It is an error to call either fetchOne() or
fetchAll() when hasResult()
indicates that a SqlResult is not a data set.
MySQL Shell JavaScript Code
function print_result(res) {
if (res.hasData()) {
// SELECT
var columns = res.getColumns();
var record = res.fetchOne();
while (record){
for (index in columns){
print (columns[index].getColumnName() + ": " + record[index] + "\n");
}
// Get the next record
record = res.fetchOne();
}
} else {
// INSERT, UPDATE, DELETE, ...
print('Rows affected: ' + res.getAffectedItemsCount());
}
}
print_result(mySession.sql('DELETE FROM users WHERE age < 30').execute());
print_result(mySession.sql('SELECT * FROM users WHERE age = 40').execute());MySQL Shell Python Code
def print_result(res):
if res.has_data():
# SELECT
columns = res.get_columns()
record = res.fetch_one()
while record:
index = 0
for column in columns:
print("%s: %s \n" % (column.get_column_name(), record[index]))
index = index + 1
# Get the next record
record = res.fetch_one()
else:
#INSERT, UPDATE, DELETE, ...
print('Rows affected: %s' % res.get_affected_items_count())
print_result(mySession.sql('DELETE FROM users WHERE age < 30').execute())
print_result(mySession.sql('SELECT * FROM users WHERE age = 40').execute())Node.js JavaScript Code
function print_result(res) {
if (res.hasData()) {
// SELECT
var columns = res.getColumns();
var record = res.fetchOne();
while (record) {
for (index in columns) {
console.log(columns[index].getColumnName() + ": " + record[index]);
}
// Get the next record
record = res.fetchOne();
}
} else {
// INSERT, UPDATE, DELETE, ...
console.log('Rows affected: ' + res.getAffectedItemsCount());
}
}
mySession.sql(`DELETE FROM users WHERE age < 30`)
.execute()
.then(function (res) {
print_result(res);
});
mySession.sql(`SELECT * FROM users WHERE age = 40`)
.execute()
.then(function (res) {
print_result(res);
});C# Code
private void print_result(SqlResult res)
{
if (res.HasData)
{
// SELECT
}
else
{
// INSERT, UPDATE, DELETE, ...
Console.WriteLine("Rows affected: " + res.RecordsAffected);
}
}
print_result(Session.SQL("DELETE FROM users WHERE age < 30").Execute());
print_result(Session.SQL("SELECT COUNT(*) AS forty FROM users WHERE age = 40").Execute());Python Code
# Connector/Python
def print_result(res):
if res.has_data():
# SELECT
columns = res.get_columns()
record = res.fetch_one()
while record:
index = 0
for column in columns:
print("%s: %s \n" % (column.get_column_name(), record[index]))
index = index + 1
# Get the next record
record = res.fetch_one()
else:
#INSERT, UPDATE, DELETE, ...
print('Rows affected: %s' % res.get_affected_items_count())
print_result(mySession.sql('DELETE FROM users WHERE age < 30').execute())
print_result(mySession.sql('SELECT * FROM users WHERE age = 40').execute())Java Code
private void print_result(SqlResult res) {
if (res.hasData()) {
// SELECT
Row row;
while ((row = res.fetchOne()) != null){
for (int c = 0; c < res.getColumnCount(); c++) {
System.out.println(row.getString(c));
}
}
} else {
// INSERT, UPDATE, DELETE, ...
System.out.println("Rows affected: " + res.getAffectedItemsCount());
}
}
print_result(mySession.sql("DELETE FROM users WHERE age < 30").execute());
print_result(mySession.sql("SELECT COUNT(*) AS forty FROM users WHERE age = 40").execute());C++ Code
void print_result(SqlResult &&_res)
{
// Note: We need to store the result somewhere to be able to process it.
SqlResult res(std::move(_res));
if (res.hasData())
{
// SELECT
const Columns &columns = res.getColumns();
Row record = res.fetchOne();
while (record)
{
for (unsigned index=0; index < res.getColumnCount(); ++index)
{
cout << columns[index].getColumnName() << ": "
<< record[index] << endl;
}
// Get the next record
record = res.fetchOne();
}
}
else
{
// INSERT, UPDATE, DELETE, ...
// Note: getAffectedItemsCount() not yet implemented in Connector/C++.
cout << "No rows in the result" << endl;
}
}
print_result(mysession.sql("DELETE FROM users WHERE age < 30").execute());
print_result(mysession.sql("SELECT * FROM users WHERE age = 40").execute());
Calling a stored procedure might result in having to deal with
multiple result sets as part of a single execution. As a result
for the query execution a SqlResult object is returned, which
encapsulates the first result set. After processing the result set
you can call nextResult() to move forward to
the next result, if any. Once you advanced to the next result set,
it replaces the previously loaded result which then becomes
unavailable.
MySQL Shell JavaScript Code
function print_result(res) {
if (res.hasData()) {
// SELECT
var columns = res.getColumns();
var record = res.fetchOne();
while (record){
for (index in columns){
print (columns[index].getColumnName() + ": " + record[index] + "\n");
}
// Get the next record
record = res.fetchOne();
}
} else {
// INSERT, UPDATE, DELETE, ...
print('Rows affected: ' + res.getAffectedItemsCount());
}
}
var res = mySession.sql('CALL my_proc()').execute();
// Prints each returned result
var more = true;
while (more){
print_result(res);
more = res.nextResult();
}MySQL Shell Python Code
def print_result(res):
if res.has_data():
# SELECT
columns = res.get_columns()
record = res.fetch_one()
while record:
index = 0
for column in columns:
print("%s: %s \n" % (column.get_column_name(), record[index]))
index = index + 1
# Get the next record
record = res.fetch_one()
else:
#INSERT, UPDATE, DELETE, ...
print('Rows affected: %s' % res.get_affected_items_count())
res = mySession.sql('CALL my_proc()').execute()
# Prints each returned result
more = True
while more:
print_result(res)
more = res.next_result()Node.js JavaScript Code
function print_result(res) {
if (res.hasData()) {
// SELECT
var columns = res.getColumns();
var record = res.fetchOne();
while (record) {
for (index in columns) {
console.log(columns[index].getColumnName() + ": " + record[index]);
}
// Get the next record
record = res.fetchOne();
}
} else {
// INSERT, UPDATE, DELETE, ...
console.log('Rows affected: ' + res.getAffectedItemsCount());
}
}
mySession.sql('CALL my_proc()')
.execute()
.then(function (res) {
// Prints each returned result
var more = true;
while (more) {
print_result(res);
more = res.nextResult();
}
})C# Code
var res = Session.SQL("CALL my_proc()").Execute();
if (res.HasData)
{
do
{
Console.WriteLine("New resultset");
while (res.Next())
{
Console.WriteLine(res.Current);
}
} while (res.NextResult());
}Python Code
# Connector/Python
def print_result(res):
if res.has_data():
# SELECT
columns = res.get_columns()
record = res.fetch_one()
while record:
index = 0
for column in columns:
print("%s: %s \n" % (column.get_column_name(), record[index]))
index = index + 1
# Get the next record
record = res.fetch_one()
else:
#INSERT, UPDATE, DELETE, ...
print('Rows affected: %s' % res.get_affected_items_count())
res = mySession.sql('CALL my_proc()').execute()
# Prints each returned result
more = True
while more:
print_result(res)
more = res.next_result()Java Code
SqlResult res = mySession.sql("CALL my_proc()").execute();C++ Code
SqlResult res = mysession.sql("CALL my_proc()").execute();
while (true)
{
if (res.hasData())
{
cout << "List of rows in the resultset." << endl;
for (Row row; (row = res.fetchOne());)
{
cout << "next row: ";
for (unsigned i = 0; i < row.colCount(); ++i)
cout << row[i] << ", ";
cout << endl;
}
}
else
{
cout << "No rows in the resultset." << endl;
}
if (!res.nextResult())
break;
cout << "Next resultset." << endl;
}
When using Node.js, individual rows can be returned immediately
using a callback, which has to be provided to the
execute() method. To identify individual result
sets you can provide a second callback, which is called for meta
data that marks the beginning of a result set.
Node.js JavaScript Code
var resultcount = 0;
var res = session
.sql('CALL my_proc()')
.execute(
function (row) {
console.log(row);
},
function (meta) {
console.log('Begin of result set number ', resultCount++);
});The number of result sets is not known immediately after the query execution. Query results can be streamed to the client or buffered at the client. In the streaming or partial buffering mode a client cannot tell whether a query emits more than one result set.