Inserting image into SQL database

[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.

Code Select Expand


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


[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.

[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????

Code Select Expand


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


[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

[td]

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 Expand

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.

[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...

Code Select Expand


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



[stanl]

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

Code (WINBATCH) Select Expand



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