Insertar, actualizar y eliminar datos con el lenguaje de manipulación de datos (DML)

void DmlStructs(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;
  std::int64_t rows_modified = 0;
  auto commit_result =
      client.Commit([&client, &rows_modified](spanner::Transaction const& txn)
                        -> google::cloud::StatusOr<spanner::Mutations> {
        auto singer_info = std::make_tuple("Marc", "Richards");
        auto sql = spanner::SqlStatement(
            "UPDATE Singers SET FirstName = 'Keith' WHERE "
            "STRUCT<FirstName String, LastName String>(FirstName, LastName) "
            "= @name",
            {{"name", spanner::Value(std::move(singer_info))}});
        auto dml_result = client.ExecuteDml(txn, std::move(sql));
        if (!dml_result) return std::move(dml_result).status();
        rows_modified = dml_result->RowsModified();
        return spanner::Mutations{};
      });
  if (!commit_result) throw std::move(commit_result).status();
  std::cout << rows_modified
            << " update was successful [spanner_dml_structs]\n";
}

using Google.Cloud.Spanner.Data;
using System;
using System.Threading.Tasks;

public class UpdateUsingDmlWithStructCoreAsyncSample
{
    public async Task<int> UpdateUsingDmlWithStructCoreAsync(string projectId, string instanceId, string databaseId)
    {
        var nameStruct = new SpannerStruct
        {
            { "FirstName", SpannerDbType.String, "Timothy" },
            { "LastName", SpannerDbType.String, "Campbell" }
        };
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";

        using var connection = new SpannerConnection(connectionString);
        await connection.OpenAsync();

        using var cmd = connection.CreateDmlCommand("UPDATE Singers SET LastName = 'Grant' WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) = @name");
        cmd.Parameters.Add("name", nameStruct.GetSpannerDbType(), nameStruct);
        int rowCount = await cmd.ExecuteNonQueryAsync();

        Console.WriteLine($"{rowCount} row(s) updated...");
        return rowCount;
    }
}

import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/spanner"
)

func updateUsingDMLStruct(w io.Writer, db string) error {
	ctx := context.Background()
	client, err := spanner.NewClient(ctx, db)
	if err != nil {
		return err
	}
	defer client.Close()

	_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
		type name struct {
			FirstName string
			LastName  string
		}
		var singerInfo = name{"Timothy", "Campbell"}

		stmt := spanner.Statement{
			SQL: `Update Singers Set LastName = 'Grant'
				WHERE STRUCT<FirstName String, LastName String>(Firstname, LastName) = @name`,
			Params: map[string]interface{}{"name": singerInfo},
		}
		rowCount, err := txn.Update(ctx, stmt)
		if err != nil {
			return err
		}
		fmt.Fprintf(w, "%d record(s) inserted.\n", rowCount)
		return nil
	})
	return err
}

static void updateUsingDmlWithStruct(DatabaseClient dbClient) {
  Struct name =
      Struct.newBuilder().set("FirstName").to("Timothy").set("LastName").to("Campbell").build();
  Statement s =
      Statement.newBuilder(
              "UPDATE Singers SET LastName = 'Grant' "
                  + "WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) "
                  + "= @name")
          .bind("name")
          .to(name)
          .build();
  dbClient
      .readWriteTransaction()
      .run(transaction -> {
        long rowCount = transaction.executeUpdate(s);
        System.out.printf("%d record updated.\n", rowCount);
        return null;
      });
}

// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');

const nameStruct = Spanner.struct({
  FirstName: 'Timothy',
  LastName: 'Campbell',
});

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

database.runTransaction(async (err, transaction) => {
  if (err) {
    console.error(err);
    return;
  }
  try {
    const [rowCount] = await transaction.runUpdate({
      sql: `UPDATE Singers SET LastName = 'Grant'
      WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) = @name`,
      params: {
        name: nameStruct,
      },
    });

    console.log(`Successfully updated ${rowCount} record.`);
    await transaction.commit();
  } catch (err) {
    console.error('ERROR:', err);
  } finally {
    // Close the database when finished.
    database.close();
  }
});

use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Database;
use Google\Cloud\Spanner\Transaction;
use Google\Cloud\Spanner\StructType;
use Google\Cloud\Spanner\StructValue;

/**
 * Update data with a DML statement using Structs.
 *
 * The database and table must already exist and can be created using
 * `create_database`.
 * Example:
 * ```
 * insert_data($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function update_data_with_dml_structs(string $instanceId, string $databaseId): void
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $database->runTransaction(function (Transaction $t) {
        $nameValue = (new StructValue)
            ->add('FirstName', 'Timothy')
            ->add('LastName', 'Campbell');
        $nameType = (new StructType)
            ->add('FirstName', Database::TYPE_STRING)
            ->add('LastName', Database::TYPE_STRING);

        $rowCount = $t->executeUpdate(
            "UPDATE Singers SET LastName = 'Grant' "
             . 'WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) '
             . '= @name',
            [
                'parameters' => [
                    'name' => $nameValue
                ],
                'types' => [
                    'name' => $nameType
                ]
            ]);
        $t->commit();
        printf('Updated %d row(s).' . PHP_EOL, $rowCount);
    });
}

# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"

spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)

record_type = param_types.Struct(
    [
        param_types.StructField("FirstName", param_types.STRING),
        param_types.StructField("LastName", param_types.STRING),
    ]
)
record_value = ("Timothy", "Campbell")

def write_with_struct(transaction):
    row_ct = transaction.execute_update(
        "UPDATE Singers SET LastName = 'Grant' "
        "WHERE STRUCT<FirstName STRING, LastName STRING>"
        "(FirstName, LastName) = @name",
        params={"name": record_value},
        param_types={"name": record_type},
    )
    print("{} record(s) updated.".format(row_ct))

database.run_in_transaction(write_with_struct)

# project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new project: project_id
client  = spanner.client instance_id, database_id
row_count = 0
name_struct = { FirstName: "Timothy", LastName: "Campbell" }

client.transaction do |transaction|
  row_count = transaction.execute_update(
    "UPDATE Singers SET LastName = 'Grant'
     WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) = @name",
    params: { name: name_struct }
  )
end

puts "#{row_count} record updated."

void InsertUsingDmlReturning(google::cloud::spanner::Client client) {
  // Insert records into SINGERS table and return the generated column
  // FullName of the inserted records using `THEN RETURN FullName`.
  auto commit = client.Commit(
      [&client](google::cloud::spanner::Transaction txn)
          -> google::cloud::StatusOr<google::cloud::spanner::Mutations> {
        auto sql = google::cloud::spanner::SqlStatement(R"""(
            INSERT INTO Singers (SingerId, FirstName, LastName)
              VALUES (12, 'Melissa', 'Garcia'),
                     (13, 'Russell', 'Morales'),
                     (14, 'Jacqueline', 'Long'),
                     (15, 'Dylan', 'Shaw')
              THEN RETURN FullName
        )""");
        using RowType = std::tuple<std::string>;
        auto rows = client.ExecuteQuery(std::move(txn), std::move(sql));
        // Note: This mutator might be re-run, or its effects discarded, so
        // changing non-transactional state (e.g., by producing output) is,
        // in general, not something to be imitated.
        for (auto& row : google::cloud::spanner::StreamOf<RowType>(rows)) {
          if (!row) return std::move(row).status();
          std::cout << "FullName: " << std::get<0>(*row) << "\n";
        }
        std::cout << "Inserted row(s) count: " << rows.RowsModified() << "\n";
        return google::cloud::spanner::Mutations{};
      });
  if (!commit) throw std::move(commit).status();
}

using Google.Cloud.Spanner.Data;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;

public class InsertUsingDmlReturningAsyncSample
{
    public async Task<List<string>> InsertUsingDmlReturningAsync(string projectId, string instanceId, string databaseId)
    {
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";

        using var connection = new SpannerConnection(connectionString);
        await connection.OpenAsync();

        // Insert records into the SINGERS table and return the
        // generated column FullName of the inserted records using
        // 'THEN RETURN FullName'.
        // It is also possible to return all columns of all the
        // inserted records by using 'THEN RETURN *'.
        using var cmd = connection.CreateDmlCommand(
            @"INSERT INTO Singers(SingerId, FirstName, LastName) VALUES
            (6, 'Melissa', 'Garcia'), 
            (7, 'Russell', 'Morales'), 
            (8, 'Jacqueline', 'Long'), 
            (9, 'Dylan', 'Shaw') THEN RETURN FullName");

        var reader = await cmd.ExecuteReaderAsync();
        var insertedSingerNames = new List<string>();
        while (await reader.ReadAsync())
        {
            insertedSingerNames.Add(reader.GetFieldValue<string>("FullName"));
        }

        Console.WriteLine($"{insertedSingerNames.Count} row(s) inserted...");
        return insertedSingerNames;
    }
}

import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/spanner"
	"google.golang.org/api/iterator"
)

func insertUsingDMLReturning(w io.Writer, db string) error {
	ctx := context.Background()
	client, err := spanner.NewClient(ctx, db)
	if err != nil {
		return err
	}
	defer client.Close()

	// Insert records into the SINGERS table and returns the
	// generated column FullName of the inserted records using
	// 'THEN RETURN FullName'.
	// It is also possible to return all columns of all the
	// inserted records by using 'THEN RETURN *'.
	_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
		stmt := spanner.Statement{
			SQL: `INSERT INTO Singers (SingerId, FirstName, LastName)
			        VALUES (21, 'Melissa', 'Garcia'),
			               (22, 'Russell', 'Morales'),
			               (23, 'Jacqueline', 'Long'),
			               (24, 'Dylan', 'Shaw')
			        THEN RETURN FullName`,
		}
		iter := txn.Query(ctx, stmt)
		defer iter.Stop()
		for {
			row, err := iter.Next()
			if err == iterator.Done {
				break
			}
			if err != nil {
				return err
			}
			var fullName string
			if err := row.Columns(&fullName); err != nil {
				return err
			}
			fmt.Fprintf(w, "%s\n", fullName)
		}
		fmt.Fprintf(w, "%d record(s) inserted.\n", iter.RowCount)
		return nil
	})
	return err
}

import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.ResultSet;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.Statement;

public class InsertUsingDmlReturningSample {

  static void insertUsingDmlReturning() {
    // TODO(developer): Replace these variables before running the sample.
    final String projectId = "my-project";
    final String instanceId = "my-instance";
    final String databaseId = "my-database";
    insertUsingDmlReturning(projectId, instanceId, databaseId);
  }

  static void insertUsingDmlReturning(String projectId, String instanceId, String databaseId) {
    try (Spanner spanner =
        SpannerOptions.newBuilder()
            .setProjectId(projectId)
            .build()
            .getService()) {
      final DatabaseClient dbClient =
          spanner.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));
      // Insert records into the SINGERS table and returns the
      // generated column FullName of the inserted records using
      // ‘THEN RETURN FullName’.
      // It is also possible to return all columns of all the
      // inserted records by using ‘THEN RETURN *’.
      dbClient
          .readWritreadWriteTransaction     .run(
              transaction -> {
                String sql =
                    "INSERT INTO Singers (SingerId, FirstName, LastName) VALUES "
                        + "(12, 'Melissa', 'Garcia'), "
                        + "(13, 'Russell', 'Morales'), "
                        + "(14, 'Jacqueline', 'Long'), "
                        + "(15, 'Dylan', 'Shaw') THEN RETURN FullName";

                // readWriteTransaction.executeQuery(..) API should be used for executing
                // DML statements with RETURNING clause.
                try (ResultSeResultSetet = transaction.executeQuery(StatemenStatement)) {
                  while (resultSet.next()) {
                    System.out.println(resultSet.getString(0));
                  }
                  System.out.printf(
                      "Inserted row(s) count: %d\n", resultSet.getStats().getRowCountExact());
                }
                return null;
              });
    }
  }
}

// Imports the Google Cloud client library.
const {Spanner} = require('@google-cloud/spanner');

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});

function insertUsingDmlReturning(instanceId, databaseId) {
  // Gets a reference to a Cloud Spanner instance and database.
  const instance = spanner.instance(instanceId);
  const database = instance.database(databaseId);

  database.runTransaction(async (err, transaction) => {
    if (err) {
      console.error(err);
      return;
    }
    try {
      const [rows, stats] = await transaction.run({
        sql: 'INSERT Singers (SingerId, FirstName, LastName) VALUES (@id, @firstName, @lastName) THEN RETURN FullName',
        params: {
          id: 18,
          firstName: 'Virginia',
          lastName: 'Watson',
        },
      });

      const rowCount = Math.floor(stats[stats.rowCount]);
      console.log(
        `Successfully inserted ${rowCount} record into the Singers table.`,
      );
      rows.forEach(row => {
        console.log(row.toJSON().FullName);
      });

      await transaction.commit();
    } catch (err) {
      console.error('ERROR:', err);
    } finally {
      // Close the database when finished.
      database.close();
    }
  });
}
insertUsingDmlReturning(instanceId, databaseId);

use Google\Cloud\Spanner\SpannerClient;

/**
 * Inserts sample data into the given database using DML returning.
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function insert_dml_returning(string $instanceId, string $databaseId): void
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    // Insert records into SINGERS table and returns the generated column
    // FullName of the inserted records using ‘THEN RETURN FullName’. It is also
    // possible to return all columns of all the inserted records by using
    // ‘THEN RETURN *’.

    $sql = 'INSERT INTO Singers (SingerId, FirstName, LastName) '
        . "VALUES (12, 'Melissa', 'Garcia'), "
        . "(13, 'Russell', 'Morales'), "
        . "(14, 'Jacqueline', 'Long'), "
        . "(15, 'Dylan', 'Shaw') "
        . 'THEN RETURN FullName';

    $transaction = $database->transaction();
    $result = $transaction->execute($sql);
    foreach ($result->rows() as $row) {
        printf(
            '%s inserted.' . PHP_EOL,
            $row['FullName'],
        );
    }
    printf(
        'Inserted row(s) count: %d' . PHP_EOL,
        $result->stats()['rowCountExact']
    );
    $transaction->commit();
}

# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"

spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)

# Insert records into the SINGERS table and returns the
# generated column FullName of the inserted records using
# 'THEN RETURN FullName'.
# It is also possible to return all columns of all the
# inserted records by using 'THEN RETURN *'.
def insert_singers(transaction):
    results = transaction.execute_sql(
        "INSERT INTO Singers (SingerId, FirstName, LastName) VALUES "
        "(21, 'Luann', 'Chizoba'), "
        "(22, 'Denis', 'Patricio'), "
        "(23, 'Felxi', 'Ronan'), "
        "(24, 'Dominik', 'Martyna') "
        "THEN RETURN FullName"
    )
    for result in results:
        print("FullName: {}".format(*result))
    print("{} record(s) inserted.".format(results.stats.row_count_exact))

database.run_in_transaction(insert_singers)

require "google/cloud/spanner"

##
# This is a snippet for showcasing how to use DML return feature with insert
# operation.
#
# @param project_id  [String] The ID of the Google Cloud project.
# @param instance_id [String] The ID of the spanner instance.
# @param database_id [String] The ID of the database.
#
def spanner_insert_dml_returning project_id:, instance_id:, database_id:
  spanner = Google::Cloud::Spanner.new project: project_id
  client = spanner.client instance_id, database_id

  client.transaction do |transaction|
    # Insert records into the SINGERS table and returns the generated column
    # FullName of the inserted records using ‘THEN RETURN FullName’.
    # It is also possible to return all columns of all the inserted records
    # by using ‘THEN RETURN *’.
    results = transaction.execute_query "INSERT INTO Singers (SingerId, FirstName, LastName)
                                         VALUES (12, 'Melissa', 'Garcia'), (13, 'Russell', 'Morales'), (14, 'Jacqueline', 'Long'), (15, 'Dylan', 'Shaw')
                                         THEN RETURN FullName"
    results.rows.each do |row|
      puts "Inserted singers with FullName: #{row[:FullName]}"
    end
    puts "Inserted row(s) count: #{results.row_count}"
  end
end

void InsertUsingDmlReturning(google::cloud::spanner::Client client) {
  // Insert records into SINGERS table and return the generated column
  // FullName of the inserted records using `RETURNING FullName`.
  auto commit = client.Commit(
      [&client](google::cloud::spanner::Transaction txn)
          -> google::cloud::StatusOr<google::cloud::spanner::Mutations> {
        auto sql = google::cloud::spanner::SqlStatement(R"""(
            INSERT INTO Singers (SingerId, FirstName, LastName)
                VALUES (12, 'Melissa', 'Garcia'),
                       (13, 'Russell', 'Morales'),
                       (14, 'Jacqueline', 'Long'),
                       (15, 'Dylan', 'Shaw')
                RETURNING FullName
        )""");
        using RowType = std::tuple<std::string>;
        auto rows = client.ExecuteQuery(std::move(txn), std::move(sql));
        for (auto& row : google::cloud::spanner::StreamOf<RowType>(rows)) {
          if (!row) return std::move(row).status();
          std::cout << "FullName: " << std::get<0>(*row) << "\n";
        }
        std::cout << "Inserted row(s) count: " << rows.RowsModified() << "\n";
        return google::cloud::spanner::Mutations{};
      });
  if (!commit) throw std::move(commit).status();
}

using Google.Cloud.Spanner.Data;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;

public class InsertUsingDmlReturningAsyncPostgresSample
{
    public async Task<List<string>> InsertUsingDmlReturningAsyncPostgres(string projectId, string instanceId, string databaseId)
    {
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";

        using var connection = new SpannerConnection(connectionString);
        await connection.OpenAsync();

        // Insert records into SINGERS table and return the
        // generated column FullName of the inserted records
        // using 'RETURNING FullName'.
        // It is also possible to return all columns of all the
        // inserted records by using 'RETURNING *'.
        using var cmd = connection.CreateDmlCommand(
            @"INSERT INTO Singers(SingerId, FirstName, LastName) VALUES
            (6, 'Melissa', 'Garcia'), 
            (7, 'Russell', 'Morales'), 
            (8, 'Jacqueline', 'Long'), 
            (9, 'Dylan', 'Shaw') RETURNING FullName");

        var reader = await cmd.ExecuteReaderAsync();
        var insertedSingerNames = new List<string>();
        while (await reader.ReadAsync())
        {
            insertedSingerNames.Add(reader.GetFieldValue<string>("fullname"));
        }

        Console.WriteLine($"{insertedSingerNames.Count} row(s) inserted...");
        return insertedSingerNames;
    }
}

import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/spanner"
	"google.golang.org/api/iterator"
)

func pgInsertUsingDMLReturning(w io.Writer, db string) error {
	ctx := context.Background()
	client, err := spanner.NewClient(ctx, db)
	if err != nil {
		return err
	}
	defer client.Close()

	// Insert records into the SINGERS table and returns the
	// generated column FullName of the inserted records using
	// 'RETURNING FullName'.
	// It is also possible to return all columns of all the
	// inserted records by using 'RETURNING *'.
	_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
		stmt := spanner.Statement{
			SQL: `INSERT INTO Singers (SingerId, FirstName, LastName)
			        VALUES (21, 'Melissa', 'Garcia'),
			               (22, 'Russell', 'Morales'),
			               (23, 'Jacqueline', 'Long'),
			               (24, 'Dylan', 'Shaw')
			        RETURNING FullName`,
		}
		iter := txn.Query(ctx, stmt)
		defer iter.Stop()
		for {
			row, err := iter.Next()
			if err == iterator.Done {
				break
			}
			if err != nil {
				return err
			}
			var fullName string
			if err := row.Columns(&fullName); err != nil {
				return err
			}
			fmt.Fprintf(w, "%s\n", fullName)
		}
		fmt.Fprintf(w, "%d record(s) inserted.\n", iter.RowCount)
		return nil
	})
	return err
}

import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.ResultSet;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.Statement;

public class PgInsertUsingDmlReturningSample {

  static void insertUsingDmlReturning() {
    // TODO(developer): Replace these variables before running the sample.
    final String projectId = "my-project";
    final String instanceId = "my-instance";
    final String databaseId = "my-database";
    insertUsingDmlReturning(projectId, instanceId, databaseId);
  }

  static void insertUsingDmlReturning(String projectId, String instanceId, String databaseId) {
    try (Spanner spanner =
        SpannerOptions.newBuilder()
            .setProjectId(projectId)
            .build()
            .getService()) {
      final DatabaseClient dbClient =
          spanner.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));
      // Insert records into SINGERS table and returns the
      // generated column FullName of the inserted records
      // using ‘RETURNING FullName’.
      // It is also possible to return all columns of all the
      // inserted records by using ‘RETURNING *’.
      dbClient
          .readWritreadWriteTransaction     .run(
              transaction -> {
                String sql =
                    "INSERT INTO Singers (SingerId, FirstName, LastName) VALUES "
                        + "(12, 'Melissa', 'Garcia'), "
                        + "(13, 'Russell', 'Morales'), "
                        + "(14, 'Jacqueline', 'Long'), "
                        + "(15, 'Dylan', 'Shaw') RETURNING FullName";

                // readWriteTransaction.executeQuery(..) API should be used for executing
                // DML statements with RETURNING clause.
                try (ResultSeResultSetet = transaction.executeQuery(StatemenStatement)) {
                  while (resultSet.next()) {
                    System.out.println(resultSet.getString(0));
                  }
                  System.out.printf(
                      "Inserted row(s) count: %d\n", resultSet.getStats().getRowCountExact());
                }
                return null;
              });
    }
  }
}

// Imports the Google Cloud client library.
const {Spanner} = require('@google-cloud/spanner');

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});

function pgInsertUsingDmlReturning(instanceId, databaseId) {
  // Gets a reference to a Cloud Spanner instance and database.
  const instance = spanner.instance(instanceId);
  const database = instance.database(databaseId);

  database.runTransaction(async (err, transaction) => {
    if (err) {
      console.error(err);
      return;
    }
    try {
      const [rows, stats] = await transaction.run({
        sql: 'INSERT Into Singers (SingerId, FirstName, LastName) VALUES ($1, $2, $3) RETURNING FullName',
        params: {
          p1: 18,
          p2: 'Virginia',
          p3: 'Watson',
        },
      });

      const rowCount = Math.floor(stats[stats.rowCount]);
      console.log(
        `Successfully inserted ${rowCount} record into the Singers table.`,
      );
      rows.forEach(row => {
        console.log(row.toJSON().fullname);
      });

      await transaction.commit();
    } catch (err) {
      console.error('ERROR:', err);
    } finally {
      // Close the database when finished.
      database.close();
    }
  });
}
pgInsertUsingDmlReturning(instanceId, databaseId);

use Google\Cloud\Spanner\SpannerClient;

/**
 * Inserts sample data into the given postgresql database using DML returning.
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function pg_insert_dml_returning(string $instanceId, string $databaseId): void
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    // Insert records into SINGERS table and returns the generated column
    // FullName of the inserted records using ‘RETURNING FullName’. It is also
    // possible to return all columns of all the inserted records by using
    // ‘RETURNING *’.

    $sql = 'INSERT INTO Singers (Singerid, FirstName, LastName) '
      . "VALUES (12, 'Melissa', 'Garcia'), "
      . "(13, 'Russell', 'Morales'), "
      . "(14, 'Jacqueline', 'Long'), "
      . "(15, 'Dylan', 'Shaw') "
      . 'RETURNING FullName';

    $transaction = $database->transaction();
    $result = $transaction->execute($sql);
    foreach ($result->rows() as $row) {
        printf(
            '%s inserted.' . PHP_EOL,
            $row['fullname'],
        );
    }
    printf(
        'Inserted row(s) count: %d' . PHP_EOL,
        $result->stats()['rowCountExact']
    );
    $transaction->commit();
}

# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"

spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)

# Insert records into the SINGERS table and returns the
# generated column FullName of the inserted records using
# 'RETURNING FullName'.
# It is also possible to return all columns of all the
# inserted records by using 'RETURNING *'.
def insert_singers(transaction):
    results = transaction.execute_sql(
        "INSERT INTO Singers (SingerId, FirstName, LastName) VALUES "
        "(21, 'Luann', 'Chizoba'), "
        "(22, 'Denis', 'Patricio'), "
        "(23, 'Felxi', 'Ronan'), "
        "(24, 'Dominik', 'Martyna') "
        "RETURNING FullName"
    )
    for result in results:
        print("FullName: {}".format(*result))
    print("{} record(s) inserted.".format(results.stats.row_count_exact))

database.run_in_transaction(insert_singers)

require "google/cloud/spanner"

##
# This is a snippet for showcasing how to use DML return feature with insert
# operation in PostgreSql.
#
# @param project_id  [String] The ID of the Google Cloud project.
# @param instance_id [String] The ID of the spanner instance.
# @param database_id [String] The ID of the database.
#
def spanner_postgresql_insert_dml_returning project_id:, instance_id:, database_id:
  spanner = Google::Cloud::Spanner.new project: project_id
  client = spanner.client instance_id, database_id

  client.transaction do |transaction|
    # Insert records into SINGERS table and returns the generated column
    # FullName of the inserted records using ‘RETURNING FullName’.
    # It is also possible to return all columns of all the inserted
    # records by using ‘RETURNING *’.
    results = transaction.execute_query "INSERT INTO Singers (SingerId, FirstName, LastName)
                                         VALUES (12, 'Melissa', 'Garcia'), (13, 'Russell', 'Morales'), (14, 'Jacqueline', 'Long'), (15, 'Dylan', 'Shaw')
                                         RETURNING FullName"
    results.rows.each do |row|
      puts "Inserted singers with FullName: #{row[:fullname]}"
    end
    puts "Inserted row(s) count: #{results.row_count}"
  end
end

void UpdateUsingDmlReturning(google::cloud::spanner::Client client) {
  // Update MarketingBudget column for records satisfying a particular
  // condition and return the modified MarketingBudget column of the
  // updated records using `THEN RETURN MarketingBudget`.
  auto commit = client.Commit(
      [&client](google::cloud::spanner::Transaction txn)
          -> google::cloud::StatusOr<google::cloud::spanner::Mutations> {
        auto sql = google::cloud::spanner::SqlStatement(R"""(
            UPDATE Albums SET MarketingBudget = MarketingBudget * 2
              WHERE SingerId = 1 AND AlbumId = 1
              THEN RETURN MarketingBudget
        )""");
        using RowType = std::tuple<absl::optional<std::int64_t>>;
        auto rows = client.ExecuteQuery(std::move(txn), std::move(sql));
        // Note: This mutator might be re-run, or its effects discarded, so
        // changing non-transactional state (e.g., by producing output) is,
        // in general, not something to be imitated.
        for (auto& row : google::cloud::spanner::StreamOf<RowType>(rows)) {
          if (!row) return std::move(row).status();
          std::cout << "MarketingBudget: ";
          if (std::get<0>(*row).has_value()) {
            std::cout << *std::get<0>(*row);
          } else {
            std::cout << "NULL";
          }
          std::cout << "\n";
        }
        std::cout << "Updated row(s) count: " << rows.RowsModified() << "\n";
        return google::cloud::spanner::Mutations{};
      });
  if (!commit) throw std::move(commit).status();
}