请问nodejs 提取sql server 中image字段类型的buffer数据流,并且将其转为图片的办法
发布于 7 年前 作者 xumjs8623 5058 次浏览 来自 问答

公司业务需求,需要去sql server中提取存在里面的图片,并把它保存在本地 数据中的该表结构如图 image.png 请问各位大神 有啥好办法么,我直接拿出来去存图片是打不开的。然后怀疑是sqlserver中的编码问题,sqlserver是GBK编码, 但是用iconv去转的时候 提示无效的字符串序列image.png

13 回复

有没有人来帮帮忙,没辙了 给点思路也好

SQL Server 没用过,不过image应该也是binary类型吧,直接读出来不行?binary 又不存在编码问题

可以转成base64

@DevinXian 我代码是这样子的 image.png 输出是这样子的 image.png 图片就是打不开 image.png 😌

@sunkuo base64是不是这样转,在ctx.body中

const sql = require('mssql');
const sqlConfig = require('../config/db.js').dbConfig;
var Iconv = require('iconv').Iconv;
const fs = require('fs');

exports.getList = async (ctx, next) => {
  const pool = await sql.connect(sqlConfig);
  // 取出附件表,存放图片的地方
  var result = await pool.request().query('select * from t_Accessory');
  await sql.close();

  // var localImage = fs.readFileSync('/Users/mj/www/SWGlove/upload/111.gif');
  // console.log(localImage);
  // fs.writeFileSync('./upload/ces.gif',localImage);
  for (let i = 0; i < result.recordset.length; i++) {
    // 将图片的二进制流数据提取出来
    var imageBuffer = result.recordset[i].FData;
    // 输出文件名称
    console.log(result.recordset[i].FFileName);
    // 输出文件二进制流
    console.log(imageBuffer);
    fs.writeFileSync('./upload/' + result.recordset[i].FFileName, imageBuffer);
  }
  ctx.body = {
    name:result.recordset[4].FFileName,
    base64:result.recordset[4].FData.toString('base64')
  }
};

根据得到的结果中去base64转图片在线工具里 还是显示不了图片

{
"name": "Report.gif",
"base64": "eNoB5gkZ9kdJRjg5YXcALgDmfwB9hKulq8bP4POrsc2epsPd4/GSmrjEx9rL3Om8xNfO3uv6+vzl8f/V2+zr9f/d7f/P4PTW6f+8zeXQ4vXa3uzs9f7U5/zr8vzK3PDa4O/A0evp8PrI2ul0e6Ps9v+3vNLZ6/+MkrPM3e3u9v/R4veCiazS5Prk5u7g5vPK3e6yt87g7v/m7fiboL7D1eTS1OLt8vvJ2eXP4fPl7/XG2Oro7/nO0+bi8P/i6PXW2OTU5/3Kz9+GjLDS5fvh7frR5frM3vDl8Prk6vbg6/HT5vzR1unK3OvI2u7R5Pns9PzQ4fLT3vLM3OfS5fjb6fLU5vrZ3Obs7fPN3/fN3fLJ3Ozi7fLM3PHI2O/l7vLH2ObO3/DL3fXP4fjh7fXI2+zJ2u3P4vfp9P/b7P/N3/PV6P3S4/XX6v/S4/fM3ert9f/O4fXW4PLO4O/R5PfV5PDX5/vg7Pvt9P3P4vXY6PfI2uzJ2uzM3/LQ4/jH2uro8//R4/fR4vXQ4/nS4/bO3/L///8h+QQBAAB/ACwAAAAAdwAuAAAH/4B/FB8DhYaHiImKi4yNjo+QkYw5gh8uTAqZmpucnZ6foKGio6SlnTEJBC8DLgoIr7CxsrO0tba3uLm6u7UcCQYBTBx4xMXGx8jJysvMzc7P0MouEiUEwsVe2drb3N3e3+Dh4uPk5dzTAARGw15UVCnw8fLz9PEi8Pf1+vv49v78/QLaywewHg0J6dBwaJcCCBA7ECNKnEixosWLGDNq3Mgx4hGEBNB4MSICiBYBAmSoUSNDZUuXL2HGZNmSJc2ZLm/ivKnzZc+aMnUKpSk0ptGXGECiMULSjgA1e8CAmTBhT9QJUqVSpWoV61SsXMFuxdp1j9evWcWqHVtVbNqva//BdCVrlm3WumG1WtGQ8B2Qp1LvCC4juLBhwocNFy6DWLHjx5AjS55MebDiLXxDvnMqlY9nPn0+i/YcWnTp0X1Oj17NurXr17Bjgx6NuW8KO0rA3DHtWvXs1allCx9OXLjv2iERiNCiZE+Z1NCjS59Ovbr169iza98efUpmNMqZO6e+Z3r51Oe1pyfPPvt66O+vx7/uPWF4Gc71QNfPPzX///vp14ceBBI44IAAHiigfwEayGB0Cx7oX4H7VaiggxNWuKAe9SVHkgDNoRedVVb1USJ65ZWo4okokogifCeq2OKI57loYootphjjjSLWKON5eyVEkngFGthfkRQiiCD/hkg+uGR/FzII5ZESJkilkhQCmCSSRnYYgwtg0kBHHV+UaeYRZn6BJpppqnlmmWzC2eacaq5J551n2omnnnceESee6BAApgtifoHBoYgmCkSijDbq6KOQPrpopJFOSumliB6UTgxZcECSSX6EKuqopJZq6qmopqrqqqy2KuoXIHHqaUlauGrrrbjmquursXb6aa27BivssLfCuqmvJ8kAgR9jNEssqs2OIQAEE7QkQKnOQtCSi2fosZtnevBBIB9IlGsuEj2YYK664Rb4WYE6KrvsFb3O+te22goQLbH7QkDtHtdeS2qz2kJA4h7dfituuOqiS0QPRBBhQcRkkDEx/xE66BAxET88jIQJC4fLh2Ak3LiHFb3i4QWtoqKUUrX+MhvtGNDOLO3LVsmAErY0+0uiHiR4+664JvTQMcVkRGBBxREw3bTTTFuwtMY9VJ1uu57dQQIJHTKBwKdA0GxqSvjJ6+/Z+k57dktVWbszz2Noe/AeQoNLrsMYWxDB03tHYIYZIIAgxuCEDx44CGb03bQOUpOhww8/pCuuZ8iBJ4IIKCkrs9hjZ+422Tq7XLO01OJn1dYiF1guxhLr3bTfgj/wwAq033ADA7jnjrvtN9AuuxiIv7631BFLXrlfApCIX777DtvvvzJURbfC4ZYbOREW8w247CvczkAe4Icvfv8Y4pe/u+/A710x8URIkZkC72ihhm5AHxy9vJs7e6rNN0d/sIkkEAy4qlc1ii1tb9ur3ffKB74wOPCBYXAABB0YPvKF73wPEEPimMaFzGhhOTKQwwREJq476MFHezDb2SCQthWyLYUwPJ23vlWgonVMBxZjGuDEoMAGVtCBDghiBYbogSIasYhDrEAQg0hBB4gPd75DHB+ukA4lsEEGbbgDEn5Qrsm9i0AlO+HcVDI36ZwhgNQjkLqMxjjX7S123Vsg+JwoQSEecQR4zKMe9XhEJDKxiRfs3RuoSAA3UKUJT3gCGTDGxR4gwV2iIZAJgxY0oAHNhHUbIAFvKDH1wW7/drdjIBAdUIE74jENcUjCBS6wgVW2kpUbgEES4pCGWuKxiCPwowQtCD44LCEdTnDCHObwhqQlzQJPmFjkygWydo2rNVwyF8QiJjW9JS0CcAzl+CJIyiPWUpUbqAELWIADFKCgABlIpzozUIACoAAHQmBBDVoZB1ry0QNKpKAP1pCOIQyhCz6Ag+yCVzFjWmxjVWPmx0zA0I+hy2oRYxz2rHnNbMqxgdwsJS5RycoaCOGcGWhAA2zwgQC0wAA8KIFKVcqDEBigBQH4gA0awE54zvMCtExDLv0YBH4SwJ9dCEIQ8sCA3q0ggyBQXPbYF1HWRbRx2fMk7MQAStyVD4il/8wlHuPAShZ8NKRF+EALQgCADnSAB8BQQQIOsIMXvGAHOzgAIcZaVgCEgAAfoCkKhFCDGsAgp3jcwC9/WoUZCNV8RT0q8IL3tIK+zph869sOZxfHi+ZhlFkdQS1X6VEUhNQGAyBrB6rxgRdE4Q+oTa1qV5taGEDhAAEIQQcAYIABFCEDe63BKi/AAr60wA1YmEErI8jLJ9rOdxlc7OGWOzjuKVCOFsSsETWrys5+NrRmNYAKcrAA1nr3u95dAAUS0AIA0HYAFGgnOgMAgBYkILitLKUSd8nL4uaOd/i1ne7GV8ElarSI3+StEApAgbAaoKwhUAEUwMvgBn/3BOQdbf9tDxyCX0BhCCzYQBLS0Mf5EpeBlw0xiMe3xG56IJcBtu5ICVCC0Q7gBd11sIxnvFooqICsBMgBDALAgwQoAAUZvkASNLtTJM6XvsRFMgRLrNGdUvcC4jxnA4pwY7O697Q0zrKWUxuFE6C2AOUNQQBu+86bAlarHU6imv+Ly41yVJw4IHARDlDesw6AElvOs55VG4UDEMC82tUrPF955j2e+MR7rCVXOyqEOBfYzzyYLQEOgOU9W/rSlTDAbGu7AwrgFgfj7OsrVTnLJMDAleGE8zkpMGUVGKDF7U2AlzFN61oLgrzmLQEwEtAAT7PTneYMdjvTKdI5D+DVZuVBAA5DMGtbO9vZUXiBWCM925a+lAABCAABCHDSEJSgrJteNgVi/Oxymxu1C4gCFHaQABVom9vbDoAKPnCAHJyA3OfOt4MDAQA7qnoq+QA="
}

看你打印文件buffer,文件头标志都错了

你可以百度下,对应后缀名的文件头

@mabu233 是的,我用nodejs去取同一张图片打印出来的buffer 和在sql server中的不一样,我一直以为是,在金蝶的k3 wise系统里面上传图片的时候进行了压缩或者其他操作。。 数据库里存储的是这样子的image.png

@mabu233 好的 好的 谢谢哈

说真的,第一次看人把图片存进数据库,现在都应该是直接存CDN再拿到图片链接存数据库的吧 没有cdn,存本地也行啊,存数据库,性能还没本地好,存取还不方便

@zy445566 是的,可能是系统比较老吧,是金蝶的k3 wise。现在需求是工厂里面通过扫描枪扫条形码 要能看到这个产品的物料图片。所以我想着直接用nodejs访问数据库去那数据。。 k3 的二开不会。。

78da 是 zlib算法的文件头

@mabu233 好了,非常非常感谢你!! 这个问题搞了我3天了!

回到顶部