Inserting Image into varbinary(max) field 1

[CBellucci]

I have a BMP Image I want to insert into an SQL varbinary(max) field. I came up with this code:

Store Cast(FileToStr(Pic.bmp) As Blob) To jcBMP
CursorSetProp('MapBinary', .T., 0)
SQL_Status = SQLExec(SQL_Connection, "INSERT Into SFile (BMPField) Values (' + M.jcBMP + "');")

When I execute this, I get the error: "Unclosed quotation mark after the character string 'BM -- That suggests to me the code is trying to insert the FileToStr string and not jcBMP. If I look at the jcBMP string interactively in VFP, it looks like hex. But if I do a StrToFile(M.jcBMP, "I:\Out.txt", 0), it has the "BM" characters at the beginning that BMP images do.

Any suggestions? Am I missing an environment setting? What am I doing wrong?

 

[Steve Yu]

you have mismatching quote marks : two single quote(') but three double quotes(")

 

[Joe Crescenzi]

Inserting using a simple string like that won't work for a variety of reasons, including the fact that even a single character that happens to be another single quote will terminate the first one.

There are a lot of alternatives, but the approach I use for things like that are updatable remote views. In a nutshell, I open a connection, then define a Remote View and I ensure it has a key field and that it's configured to save updates.

For example, if you called your remove view SFile_View, you could just do this.

Use SFile_View
Insert into Sfile (BMPField) values (M.jcBMP)

If you append a blank, you could even say "replace BMPField with m.jcBMP"

 

[Steve Yu]

I have a BMP Image I want to insert into an SQL varbinary(max) field. I came up with this code:

Store Cast(FileToStr(Pic.bmp) As Blob) To jcBMP
CursorSetProp('MapBinary', .T., 0)
SQL_Status = SQLExec(SQL_Connection, "INSERT Into SFile (BMPField) Values (' + M.jcBMP + "');")

When I execute this, I get the error: "Unclosed quotation mark after the character string 'BM -- That suggests to me the code is trying to insert the FileToStr string and not jcBMP. If I look at the jcBMP string interactively in VFP, it looks like hex. But if I do a StrToFile(M.jcBMP, "I:\Out.txt", 0), it has the "BM" characters at the beginning that BMP images do.

Any suggestions? Am I missing an environment setting? What am I doing wrong?

SQLExec(SQL_Connection, "INSERT Into SFile (BMPField) Values ( ?M.jcBMP ) ;")

 

[Joe Crescenzi]

SQLExec(SQL_Connection, "INSERT Into SFile (BMPField) Values ( ?M.jcBMP ) ;")

Yes... that definitely should work. Unlike trying to use single quotes, the SQLExec function should be able to correctly format it whenever you use ? followed by a variable or field reference.

 

[CBellucci]

Okay, as a result of both of your posts, I figure it out. (I think. It's working, that's all I care at 5pm on Friday.)

I ended up creating a cursor with one field of type W (blob). Here's the new code:

Create Cursor SigTmp(Signature W)
Select SigTmp
Append Blank
Store Cast(FileToStr(Pic.bmp) As Blob) To jcBMP
Replace SigTmp.Signature With M.jcSignature
CursorSetProp('MapBinary', .T., 0)
SQL_Status = SQLExec(SQL_Connection, "INSERT Into SFile (BMPField) Values ("?SigTMP.Signature);")

 

[CBellucci]

SQLExec(SQL_Connection, "INSERT Into SFile (BMPField) Values ( ?M.jcBMP ) ;")

Much easier than what I did. And it works.

Thanks, everyone!

 

[Steve Yu]

Syntax aside, a different design consideration is worth mentioning:
In our shop, we don't imbed binary data (image, pdf, .xls, etc) in the DB as a general rule for a number of reasons.
Instead we store only the name of the object in the DB field and the actual object separately in a designated folder(s) on the hard drive.
Retrieval requires a little more coding but benefits are many.

 

[GTGeek88]

Syntax aside, a different design consideration is worth mentioning:
In our shop, we don't imbed binary data (image, pdf, .xls, etc) in the DB as a general rule for a number of reasons.
Instead we store only the name of the object in the DB field and the actual object separately in a designated folder(s) on the hard drive.
Retrieval requires a little more coding but benefits are many.

Yeah, that's the way I approach it.

 

[Joe Crescenzi]

Syntax aside, a different design consideration is worth mentioning:
In our shop, we don't imbed binary data (image, pdf, .xls, etc) in the DB as a general rule for a number of reasons.
Instead we store only the name of the object in the DB field and the actual object separately in a designated folder(s) on the hard drive.
Retrieval requires a little more coding but benefits are many.

I do likewise. My older apps store files in a series of folders (year\month), then I store a pointer to the files.

My newer versions uploads files in an Azure Blob, so instead of storing the path locally, I store them in the cloud, and reference the cloud container and path.

But at one point I did store images in SQL columns. I eventually moved away from that because I thought it would be slower, but as it turns out it worked just as fast because those larger data fields were actually stored in another area in the database. I stopped doing that at least 15 years ago, because I liked the simplicity of just storing references to the files.

 

[Chris Miller]

Regarding files, SQL Server supports features like FILESTREAM and based on FILESTREAM the even simpler to use FILETABLES. See https://learn.microsoft.com/en-us/s...b/filetables-sql-server?view=sql-server-ver16

A FILETABLE is a table that works in principle as a table only holding a reference to a file and the file is outside of the database in the filesystem. What's cool about that is that you can still use the INSERT you just learned and the SQL Server will etrieve the binary file parameter and put it into the directory it maintains for the FILETABLE. If you make the FILETABLE directory shared and accessible from clients, they can also read or write or delte files and that's reflected in the FILETABLE.

You might then say what good is a FILETABLE, if it's only available in a dedicated FILETABLE table and has no relations in your other SQL Server database tables. Well, the FILETABLE maintains the files and every file then has a stream id you can use in all your other tables as foreign key into the FILETABLE. So instead of your tables having a legacy (depracated) IMAGE type field, a modern Blob or Varbinary(MAX) field, it will only need a GUID field for a stream_id and a FILETABLE, which holds the file by this GUID as ID. And even the filetable will also not hold the file directly, but what else MSSQL needs to maintain a file and know where to find it in the filesystem - local to MSSQL.

The advanatages are, that you can use a twoway approach to files now, with simple usual file access and with SQL.

You'd need to go through some learning curve to enable FILESTREAMS and be able to create a FILETABLE (or several) in your MSSQL database. But once you do, you store files in the state of the art of MSSQL databases. And if you know a bit about how data in MSSQL tables is stored technically, you know for Blobs and other long fields writing and reading is an overhead for the database engine not storing them in the fastest acceible in-row pages of data, not in the second best row-overflow pages, but in LOB pages.

I haven't looked in detail but think the files themselves will then not count to storage limits you have with express editions.

 

[Joe Crescenzi]

Regarding files, SQL Server supports features like FILESTREAM and based on FILESTREAM the even simpler to use FILETABLES. See https://learn.microsoft.com/en-us/s...b/filetables-sql-server?view=sql-server-ver16

For what it's worth, I used this feature for a while when it first came out, and it works seamlessly once you set things up.

I eventually migrated all my local SQL Servers to Azure SQL, which doesn't support FileTables or FileStreams, which wasn't an issue for me because I never used any of the more advanced features. In the end, I went back to my old method of just storing paths, except in my newer code, they also point to Azure Blob Containers.