I am trying to call a MySQL stored procedure via linked server from MS SQL Server. The MySQL stored procedure will insert records that contain longblobs (coming in from MS SQL Server as VARBINARY(Max). I'm not sure if I have the quotes correct around my @myFile parameter. Here is how I am calling the procedure from the SQL Server side:
SQL Server Code
EXEC('CALL InsertFiletest('+@ID+',"'+ @myName +'",'''+@myLocation+''', "'+ **@myFile** +'", ' + '''2020-10-15 00:00:000''' +');') AT FILEX
I have tried using no quotes around the parameter, single quotes, double quotes and haven't had any luck, is it possible to pass a VARBINARY(MAX) param like this? `
DECLARE @ID INTEGER, @myName VARCHAR(200), @myLocation VARCHAR(200), @myFile VARBINARY(MAX) , @insertDate datetime
DECLARE file_cursor CURSOR FOR
SELECT
fileID,
CAST(name AS VARCHAR(200)),
CAST(location AS VARCHAR(200)),
CAST([file] AS VARBINARY(MAX))
FROM
cis_file
WHERE
fileid in
(
65502
)
--, 65115, 65116)
OPEN file_cursor;
FETCH NEXT
FROM
file_cursor INTO @ID,
@myName,
@myLocation,
@myFile WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('CALL InsertFiletest(' + @ID + ',"' + @myName + '","' + @myLocation + '", "' + @myFile + '");') AT FILEX;
FETCH NEXT
FROM
file_cursor INTO @ID,
@myName,
@myLocation,
@myFile
END
;
CLOSE file_cursor;
DEALLOCATE file_cursor;
}
MySQL Stored Procedure
CREATE DEFINER=`ASIMYSQLADMIN`@`%` PROCEDURE `InsertFiletest`(
IN fileID int,
IN name VARCHAR(200),
IN location VARCHAR(200),
IN file longblob
)
BEGIN
INSERT INTO CIS_FILEX (name, location, file, fileID) VALUES ( name, location, file, fileID);
END