TransWikia.com

How to write to a VARBINARY field using a stored procedure

Mathematica Asked by Chris Degnen on August 25, 2021

Is it possible to write a binary file (e.g. an EMF) to a VARBINARY field using a stored procedure?

Creating an example EMF

circle = Graphics[{Blue, Circle[{2, 0}]}, ImageSize -> Small];
Export["circle.emf", circle];

Reading EMF into Mathematica and writing to a Sequel VARBINARY field works using SQLInsert.

emfToBinary[file_] := Module[{inputstream, data},
   inputstream = OpenRead[file, BinaryFormat -> True];
   data = BinaryReadList[inputstream];
   Close[inputstream];
   data];

streamdata = emfToBinary["circle.emf"];

varEntityID = 123;
varFileName = "circle.emf";
varMimeType = "image/emf";
varData = SQLBinary[streamdata];

SQLInsert[conn, "media",
 {"entity_id", "file_name", "content_type", "content"},
 {varEntityID, varFileName, varMimeType, varData}]

1

However, for security I need to use a stored procedure, which I can’t get to work.

SQLExecute[conn, StringJoin["exec AddChart ",
  varEntityID, ", '", varFileName, "', '", varMimeType, "', `1`"],
 {SQLBinary[streamdata]}]

-1

SQLExecute[conn, StringJoin["exec AddChart ",
  varEntityID, ", '", varFileName, "', '", varMimeType, "', ",
  ExportString[streamdata, "Byte"]]]

$Failed

SQLExecute[conn, StringJoin["exec AddChart ",
  varEntityID, ", '", varFileName, "', '", varMimeType, "', '0x010203040506'"]}]

JDBC::error: Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query.

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP