Calling MySQL stored procedure that takes longblob/varbinary(max) param from MS SQL Server

178 views Asked by At

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
0

There are 0 answers