Inserting image into SQL database

Started by Jeff, June 12, 2019, 01:19:31 PM

Previous topic - Next topic

Jeff

I am trying to insert an image into a sql table named tblAttachments. It seems to insert, but not all of the data saves. Does anyone have any ideas? I have attached a screen shot of the table, the row that was inserted and the "Thelma.png" image I am saving.

Does anyone know what I am doing wrong.


SQL_CMD=""
File1DescriptionStr="Thelma from scooby doo! "
File1PathStr="C:\Thelma.png"
fs=FileSize(File1PathStr) + 100
BinBuf = BinaryAlloc(fs)
BinaryRead(BinBuf, "%File1PathStr%")
;Pull just the file 1 name
File1NameStr=ItemExtract(-1,File1PathStr,"\")

;I did this to make sure it was working as I understand.
;BinaryWrite(binbuf, "C:\Log\Thelma.png")

SQL_CMD = StrCat("INSERT INTO dbo.tblAttachments(Description,FileName, Attachment) VALUES ('%File1DescriptionStr%','%File1NameStr%','%BinBuf%')")

;Inserting new attachement.
MrKIA_SQLConn = CreateObject('ADODB.Connection')
MrKIA_SQLConn.ConnectionString = "Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=MyDatabase;User ID=MyUser;Password=MyPassword"
MrKIA_SQLConn.Open()
MrKIA_SQLConn.Execute(SQL_CMD)
MrKIA_SQLConn.Close()
MrKIA_SQLConn=0

BinaryFree(binbuf)
Exit
Jeff

td

Your script is using substitution to convert a binary buffer handle into a string value in an SQL statement.  You are making two mistakes doing this: using substitution and placing a binary buffer handle (converted to a string or not) in an SQL statement. 

You should consider looking at the BinaryOleType function in the WIL Consolidated Help file.   Once you have the binary buffer setup with the function to identify the binary buffer as a byte array (VT_UI1 | VT_ARRAY) you will need to figure out how to put it into the database.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

Jeff

I have made the changes you suggested. <see below> It did not change the end result. I know that I can write the file back out of the buffer to another location and it opens correctly. At this point I am guessing the insert command can't handle it????


SQL_CMD=""
File1DescriptionStr="Thelma from scooby doo! "
File1PathStr="C:\Thelma.png"
fs=FileSize(File1PathStr) + 100
BinBuf = BinaryAlloc(fs)
BinaryOleType(BinBuf,103,0,0,0)
BinaryRead(BinBuf, File1PathStr)
;Pull just the file 1 name
File1NameStr=ItemExtract(-1,File1PathStr,"\")

;I wrote the file back out to another location before I attempted the insert to make sure it was working as I understand.
BinaryWrite(binbuf, "C:\Log\Thelma.png")

SQL_CMD = StrCat("INSERT INTO dbo.tblAttachments(Description,FileName, Attachment) VALUES ('",File1DescriptionStr,"','",File1NameStr,"','",BinBuf,"')")

;Inserting new attachement.
MrKIA_SQLConn = CreateObject('ADODB.Connection')
MrKIA_SQLConn.ConnectionString = "Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=MyDatabase;User ID=MyUser;Password=MyPassword"
MrKIA_SQLConn.Open()
MrKIA_SQLConn.Execute(SQL_CMD)
MrKIA_SQLConn.Close()
MrKIA_SQLConn=0

BinaryFree(binbuf)
Exit

Jeff

kdmoyers

I think inserting large binary objects into SQL databases is a whole different thing.

* the database must be set up for that kind of thing, the column is special and I think maybe the table itself is special.

* the way you interact with SQL is different.  It's no longer just a regular INSERT SQL statement.

* I think most folks do it via ADO method calls rather than SQL statement text.

* be aware that the SQL server may not have the same file access you do.

That said, I've never done it myself.  I just know it's not business-as-usual.

here's an interesting article: https://blogs.msdn.microsoft.com/walzenbach/2010/05/20/how-to-insert-binary-data-like-imagesdocuments-into-a-sql-server-database-with-sql-server-management-studio/

-Kirby
The mind is everything; What you think, you become.

td

Quote from: Jeff on June 13, 2019, 06:07:24 AM
I have made the changes you suggested.

Not exactly.  You appear to have made only one of at least three suggested changes.

As Kirby suggested your database needs to be capable of accepting and possibly configured to accept binary data in a table field.  It is possible to perform an SQL Insert of binary data into a table when the database supports it.  The key is to convert the binary data into a hex string and use that hex string as the field value to insert.  WinBatch has the BinaryPeekHex function which can be used to convert an image in a binary buffer to a hex string.  There are likely other ways to perform the task that do not rely on an SQL insert statement and use the originally mentioned byte safe array.

Code (winbatch) Select
strHex = BinaryPeekHex( binbuf, 0, BinaryEodGet(binbuf))
SQL_CMD = StrCat("INSERT INTO dbo.tblAttachments(Description,FileName, Attachment) VALUES ('",File1DescriptionStr,"','",File1NameStr,"','",strHex,"')")


Note that the above code snippet is offered as a starting point and not as a solution.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

Jeff

Thanks for the replies! Your help is second to none. After thinking about your points and searching through the tech support site, I found an old post from Stan Littlefield W15587 that really helped. (Thanks Stan!) He added   
the following.
   Fld = MrKIA_RS.Fields("Attachment")
   Fld.AppendChunk(BinBuf)

Below is the sample script...


File1DescriptionStr="Thelma from scooby doo! "
File1PathStr="C:\Thelma.png"


fs=FileSize(File1PathStr) + 100
BinBuf = BinaryAlloc(fs)
BinaryOleType(BinBuf,103,0,0,0)
BinaryRead(BinBuf, File1PathStr)
;Pull just the file 1 name
File1NameStr=ItemExtract(-1,File1PathStr,"\")
Temp=Environment("TEMP")
;I did this to make sure it was working as I understand.
BinaryWrite(binbuf, StrCat(Temp,"\",File1NameStr))

;Inserting new attachement.
MrKIA_SQLConn = CreateObject('ADODB.Connection');I don't think I need this.
MrKIA_RS = CreateObject("ADODB.Recordset")
call (StrCat(DirScript(),"sqlconnection.wbc"),"") ; Load settings
;MrKIA_SQLConn.Open()
;YES I know Tony hates substitution varibles, but I am being lazy!
MrKIA_RS.Open("tblAttachments","Provider=SQLOLEDB;Data Source=%SQLSERVER%;Initial Catalog=%SQLDB%;User ID=%SQLUSER%;Password=%SQLPASSWORD%",2,3,2)
MrKIA_RS.addnew()
   MrKIA_RS.Fields("FileName").Value = File1NameStr
   MrKIA_RS.Fields("Description").Value = File1DescriptionStr
   Fld = MrKIA_RS.Fields("Attachment")
   Fld.AppendChunk(BinBuf)
   BinaryFree(binbuf)
MrKIA_RS.Update()

ObjectClose(Fld)
MrKIA_RS.Close()
ObjectClose(MrKIA_RS)
;MrKIA_SQLConn.Close()
ObjectClose(MrKIA_SQLConn)
Drop(MrKIA_SQLConn,MrKIA_RS,Fld )

Exit

Jeff

stanl

In addition to AppendChunk() you might also consider an ADO.Stream object


Code (WINBATCH) Select


S = CreateObject("ADODB.Stream")
S.Type = 1 ;binary stream
S.Open()
S.LoadFromFile(cFile)
oRS.Collect("[field_name]") = S.Read(-1)
S.Close()
S=0