MS Access:将图像作为 varbinary 发送到存储过程 [英] MS Access: Sending image as varbinary to stored procedure

问题描述

齿轮:微软访问 2016,SQL Server 2014

Gear: MS Access 2016, SQL Server 2014

语言:VBA，T-SQL

问题:该项目是一个商店"，它使用 MS ACCESS 作为前端连接到 SQL Server，在向数据库(SQL Server 数据库)添加项目时，我还需要添加项目的图像.所以我在此之前所做的是能够将独立图像添加到数据库中的 Images 表中，该表有 2 个字段，一个 AUTO id 为整数，ImageData 为 VARBINARY(MAX).

The Problem: The project is a "store" that use MS ACCESS as a front-end to connect to the SQL Server, and when adding items to the Database (SQL Server database) i need to add the item's image as well. So what i do before that is to to be able to add stand-alone image to Images table in the database, the table has 2 fields an AUTO id as integer and ImageData as VARBINARY(MAX).

为了将图像添加到数据库中，我创建了一个存储过程，其中一个参数为 VarBinary(MAX)，它应该能够超过 8k 字节，但是当我发送更多 8k 字节时，我收到错误消息[SQL Server Native Client 11.0]字符串数据，右截断".

In order to add an image to the database i have created a stored procedure with one parameter as VarBinary(MAX) that should be able to exceed 8k byte, but when i am sending more 8k bytes i am getting error message "[SQL Server Native Client 11.0]string data, right truncation".

在线研究问题并没有解决我的问题，我发现可能是数据太大了，要解决它，我应该将可变大小更改为可能超过 8k 字节的数据的 MAX 指示

Researching the problem online didn't solve my problem, all i found was maybe the data is too big and to solve it i should change variable size to MAX indication of data that may exceed 8k byte

这是使用带有图像的存储过程的试用 VBA 代码:

This is the trial VBA Code to use the stored procedure with an image:

Dim Connection As New ADODB.Connection
Dim strm As new ADODB.stream
strm.Type = adTypeBinary
strm.Open
strm.LoadFromFile "C:\out.png"
strm.Position = 0

Connection.Open 'Connection string that should not be published'
If Connection.State = 1 Then
    Dim cmd As New ADODB.command
    Set cmd.ActiveConnection = Connection
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "InsertImage"
    cmd.Parameters("@image").Size = -1
    cmd.Parameters("@image").Value = strm.Read

    If Not IsNull(cmd.Parameters("@image").Value) Then cmd.Execute ' Crushes on execute
End If

这是存储过程:

USE [Supply]
GO
ALTER PROCEDURE [dbo].[InsertImage]
(
    @image as VARBINARY(MAX)
)
AS
BEGIN
    INSERT INTO Images values(@image)
    SELECT Scope_Identify()
END

当使用小于 8k 字节的图像时，上述过程有效，或者在 SQL Server Management Studio 中执行以下脚本且图像权重超过 8k 字节时:

When using less than 8k byte image the process above works or when doing the following script in SQL Server Management Studio with an image weight more than 8k bytes :

INSERT INTO Images
SELECT * FROM OPENROWSET(BULK 'C:\out.png', SINGLE_BLOB) as img

推荐答案

最后我用上面的存储过程初始化"了数据库中的图像，然后创建了另一个接收图像id和更多数据的过程然后将接收到的数据与存在的数据连接起来，同时给表添加一个老化机制，这样数据就不会被再次更改并破坏数据库中的图像.

In the end i used the above stored procedure to "initialize" the image in the data base, and then created another procedure that receives image id and more data and then concatenating the data received with the data exists while adding the table an aging mechanism so the data will not be changed again and destroys the image in the database.

这篇关于MS Access:将图像作为 varbinary 发送到存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！