I am using node.js, express, express-fileupload and SQL Server. I am trying to upload images of signatures into the SQL Server database table. When I use the code below, I get an error
Cannot bulk load because the file "\tmp\tmp-1-1686629760086" could not be opened
This is my stored procedure:
CREATE PROCEDURE Insert_Signatures_SP
(@SignatureUri NVARCHAR(1000),
@UserID INT)
AS
DELETE FROM Signatures
WHERE UserID_FK = @UserID;
DECLARE @err INT
DECLARE @sql NVARCHAR(MAX)
SET @sql = N'INSERT INTO Signatures (UserID_FK, [Signature])
SELECT @UserID, BulkColumn
FROM OPENROWSET
(
Bulk '''+ @SignatureUri +''', SINGLE_BLOB
) AS [Signature]';
EXEC @err = sp_executesql @sql, N'@UserID INT', @UserID;
RETURN(@err);
GO
My EJS file:
<form action="/admin-signatures-upload" method="post" enctype="multipart/form-data">
<input type="hidden" class="hiddenField" id="userid" name="userid" value="<%= user.UserID %>" required>
<button id="<%= user.UserID %>" class="btn1 catbtn" >Upload Signature</button>
<div>
<input type="file" name="myImage">
</div>
</form>
My server.js file:
const fileUpload = require('express-fileupload');
app.use(fileUpload({
useTempFiles : true,
tempFileDir : '/tmp/'
}));
app.post("/admin-signatures-upload", async function (req, res) {
try {
if (!req.files) {
return res.status(400).send("No files were uploaded.");
}
console.log('Path = ' + file.tempFilePath);
await pool.connect();
const resultUserCategories = await pool.request()
.input('SignatureUri', file.tempFilePath)
.input('UserID', req.body.id)
.execute(`Insert_Signatures_SP`);
await pool.close();
res.redirect('/admin-signatures');
} catch (error) {
}
})
