Tipo de datos vectoriales

using Microsoft.Data;
using Microsoft.Data.SqlClient;
using Microsoft.Data.SqlTypes;

namespace VectorSampleApp
{
    class Program
    {
        // Set your environment variable or fallback to local server
        private static readonly string connectionString =
            Environment.GetEnvironmentVariable("CONNECTION_STR")
            ?? "Server=tcp:localhost,1433;Database=Demo2;Integrated Security=True;TrustServerCertificate=True";

        private const int VectorDimensions = 3;
        private const string TableName = "dbo.Vectors";

        static void Main()
        {
            using var connection = new SqlConnection(connectionString);
            connection.Open();
            SetupTables(connection, TableName, VectorDimensions);
            InsertVectorData(connection, TableName);
            ReadVectorData(connection, TableName);
        }

        private static void SetupTables(SqlConnection connection, string tableName, int vectorDimensionCount)
        {
            using var command = connection.CreateCommand();

            command.CommandText = $@"
                IF OBJECT_ID('{tableName}', 'U') IS NOT NULL DROP TABLE {tableName};
                IF OBJECT_ID('{tableName}Copy', 'U') IS NOT NULL DROP TABLE {tableName}Copy;";
            command.ExecuteNonQuery();

            command.CommandText = $@"
                CREATE TABLE {tableName} (
                    Id INT IDENTITY(1,1) PRIMARY KEY,
                    VectorData VECTOR({vectorDimensionCount})
                );

                CREATE TABLE {tableName}Copy (
                    Id INT IDENTITY(1,1) PRIMARY KEY,
                    VectorData VECTOR({vectorDimensionCount})
                );";
            command.ExecuteNonQuery();
        }

        private static void InsertVectorData(SqlConnection connection, string tableName)
        {
            using var command = new SqlCommand($"INSERT INTO {tableName} (VectorData) VALUES (@VectorData)", connection);
            var param = command.Parameters.Add("@VectorData", SqlDbTypeExtensions.Vector);

            // Insert null using DBNull.Value
            param.Value = DBNull.Value;
            command.ExecuteNonQuery();

            // Insert non-null vector
            param.Value = new SqlVector<float>(new float[] { 3.14159f, 1.61803f, 1.41421f });
            command.ExecuteNonQuery();

            // Insert typed null vector
            param.Value = SqlVector<float>.CreateNull(VectorDimensions);
            command.ExecuteNonQuery();

            // Prepare once and reuse for loop
            command.Prepare();
            for (int i = 0; i < 10; i++)
            {
                param.Value = new SqlVector<float>(new float[]
                {
                    i + 0.1f,
                    i + 0.2f,
                    i + 0.3f
                });
                command.ExecuteNonQuery();
            }
        }

        private static void ReadVectorData(SqlConnection connection, string tableName)
        {
            using var command = new SqlCommand($"SELECT VectorData FROM {tableName}", connection);
            using var reader = command.ExecuteReader();

            while (reader.Read())
            {
                var sqlVector = reader.GetSqlVector<float>(0);

                Console.WriteLine($"Type: {sqlVector.GetType()}, IsNull: {sqlVector.IsNull}, Length: {sqlVector.Length}");

                if (!sqlVector.IsNull)
                {
                    float[] values = sqlVector.Memory.ToArray();
                    Console.WriteLine("VectorData: " + string.Join(", ", values));
                }
                else
                {
                    Console.WriteLine("VectorData: NULL");
                }
            }
        }
    }
}

using Microsoft.Data.SqlClient;
using Dapper;
using DotNetEnv;
using System.Text.Json;

namespace DotNetSqlClient;

class Program
{
    static void Main(string[] args)
    {
        Env.Load();

        var v1 = new float[] { 1.0f, 2.0f, 3.0f };

        using var conn = new SqlConnection(Env.GetString("MSSQL"));
        conn.Execute("INSERT INTO dbo.vectors VALUES(100, @v)", param: new {@v = JsonSerializer.Serialize(v1)});

        var r = conn.ExecuteScalar<string>("SELECT v FROM dbo.vectors") ?? "[]";
        var v2 = JsonSerializer.Deserialize<float[]>(r);
        Console.WriteLine(JsonSerializer.Serialize(v2));
    }
}

Se aplica a: Microsoft JDBC Driver for SQL Server 13.1.0 y versiones posteriores.

Ejemplo de inserción de datos vectoriales en la tabla:

@Test
public void getVectorData() throws SQLException {
    String query = "SELECT v FROM " + AbstractSQLGenerator.escapeIdentifier(tableName);
    try (PreparedStatement stmt = connection.prepareStatement(query)) {
        try (ResultSet rs = stmt.executeQuery()) {
            assertTrue(rs.next(), "No result found for inserted vector.");
            ResultSetMetaData meta = rs.getMetaData();
            int columnCount = meta.getColumnCount();

            while (rs.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = meta.getColumnName(i);
                    int columnType = meta.getColumnType(i); // from java.sql.Types

                    Object value = null;
                    switch (columnType) {
                        case Types.VARCHAR:
                        case Types.NVARCHAR:
                            value = rs.getString(i);
                            break;
                        case microsoft.sql.Types.VECTOR:
                            value = rs.getObject(i, microsoft.sql.Vector.class);
                    }

                    System.out.println(columnName + " = " + value + " (type: " + columnType + ")");
                }
                System.out.println("---");
            }
        }
    }
}

Ejemplo de selección de datos vectoriales de una tabla:

@Test
    public void getVectorData() throws SQLException {
        String query = "SELECT v FROM " + AbstractSQLGenerator.escapeIdentifier(tableName);
        try (PreparedStatement stmt = connection.prepareStatement(query)) {
            try (ResultSet rs = stmt.executeQuery()) {
                assertTrue(rs.next(), "No result found for inserted vector.");
                ResultSetMetaData meta = rs.getMetaData();
                int columnCount = meta.getColumnCount();

                while (rs.next()) {
                    for (int i = 1; i <= columnCount; i++) {
                        String columnName = meta.getColumnName(i);
                        int columnType = meta.getColumnType(i); // from java.sql.Types

                        Object value = null;
                        switch (columnType) {
                            case Types.VARCHAR:
                            case Types.NVARCHAR:
                                value = rs.getString(i);
                                break;
                            case microsoft.sql.Types.VECTOR:
                                value = rs.getObject(i, microsoft.sql.Vector.class);
                        }

                        System.out.println(columnName + " = " + value + " (type: " + columnType + ")");
                    }
                    System.out.println("---");
                }
            }
        }
    }

Con Python, las aplicaciones pueden escribir y leer datos vectoriales mediante json.loads y json.dumps:

Este ejemplo usa Python con el controlador oficial de Python de Microsoft más reciente para SQL Server. Las aplicaciones pueden escribir y leer datos vectoriales mediante json.loads y json.dumps:

import json
import os, json
import mssql_python

connection_string = os.environ["MSSQL"]
conn = mssql_python.connect(connection_string)

cursor = conn.cursor()
query = "INSERT INTO dbo.vectors VALUES(100, ?)"
cursor.execute(query, json.dumps([1,2,3]))
cursor.close()

cursor = conn.cursor()
query = "SELECT v FROM dbo.vectors"
cursor.execute(query)
rows = cursor.fetchall()
for row in rows:
    print(json.loads(row[0]))
cursor.close()

conn.close()

Este ejemplo usa Python con el conocido controlador PyODBC de la comunidad. Las aplicaciones pueden escribir y leer datos vectoriales mediante json.loads y json.dumps:

import os, json
from utilities import get_conn

conn = get_conn()
conn = get_conn(os.environ["MSSQL"])

cursor = conn.cursor()
query = "INSERT INTO dbo.vectors VALUES(100, ?)"
cursor.execute(query, json.dumps([1,2,3]))
cursor.commit()
cursor.close()

cursor = conn.cursor()
query = "SELECT v FROM dbo.vectors"
cursor.execute(query)
rows = cursor.fetchall()
for row in rows:
    print(json.loads(row[0]))
cursor.close()
conn.close()

Para obtener más información sobre la función get_conn, consulte Migración de una aplicación de Python para usar conexiones sin contraseña con Azure SQL Database.

Asegúrese de agregar LongAsMax=yes en la cadena de conexión que se usa con Python, para enviar datos mediante el tipo nvarchar(max) en lugar del ntext obsoleto. Esta opción evita los mensajes de error de conversión, como:

DataError: ('22018', '[22018] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Operand type clash: ntext is incompatible with vector (206) (SQLExecDirectW)').

Para obtener más información, vea PyODBC - LongAsMax.