从Microsoft SQL Server中的十六进制文字插入varbinary值 [英] insert varbinary value from hex literal in Microsoft SQL Server

问题描述

我有一个SpringBoot应用程序，在这里我使用jdbcTemplate在mssql中插入一行

I have a SpringBoot app, where I use jdbcTemplate to insert a row to a mssql

int numOfRowsAffected = remoteJdbcTemplate.update("insert into dbo.[ELCOR Resource Time Registr_]  "
                + "( [Entry No_], [Record ID], [Posting Date], [Resource No_], [Job No_], [Work Type], [Quantity], [Unit of Measure], [Description], [Company Name], [Created Date-Time], [Status] ) "
                + " VALUES (?,CONVERT(varbinary,?),?,?,?,?,?,?,?,?,?,?);",

                ELCORResourceTimeRegistr.getEntryNo(), 
                ELCORResourceTimeRegistr.getEntryNo()), 
                ELCORResourceTimeRegistr.getPostingDate(),
                ELCORResourceTimeRegistr.getResourceNo(), 
                jobNo,
                ELCORResourceTimeRegistr.getWorkType(), 
                ELCORResourceTimeRegistr.getQuantity(),
                ELCORResourceTimeRegistr.getUnitOfMeasure(), 
                ELCORResourceTimeRegistr.getDescription(),
                ELCORResourceTimeRegistr.getCompanyName(), 
                ELCORResourceTimeRegistr.getCreatedDate(), 
                0);

ELCORResourceTimeRegistr.getEntryNo()的值是值为0x00173672

但是插入到数据库中的是<30007800 30003000 31003700 33003600 37003200>

but what is inserted in the DB is <30007800 30003000 31003700 33003600 37003200>

ELCORResourceTimeRegistr.getEntryNo().getClass().getCanonicalName() => java.lang.String

推荐答案

0:

The documentation for the CONVERT function says that the default "style" for binary types is 0:

将ASCII字符转换为二进制字节，或将二进制字节转换为ASCII字符.每个字符或字节均按1:1转换.

Translates ASCII characters to binary bytes, or binary bytes to ASCII characters. Each character or byte is converted 1:1.

所以

SELECT CONVERT(VARBINARY, '0x00173672') AS foo;

返回

foo
--------------------------------------------------------------
0x30783030313733363732

是十六进制文字的ASCII字节值，而不是十六进制字节本身.为了使CONVERT能够解释十六进制文字，您需要使用样式1，即

which are the ASCII byte values of the hex literal, not the hex bytes themselves. In order for CONVERT to interpret the hex literal, you need to use style 1, i.e.

SELECT CONVERT(VARBINARY, '0x00173672', 1) AS foo;

返回

foo
--------------------------------------------------------------
0x00173672

这篇关于从Microsoft SQL Server中的十六进制文字插入varbinary值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！