-
Aug 7th, 2007, 05:03 PM
#1
[RESOLVED] Image control - now getting picture from DB - datasource??
[edit] See Post # 35 for a solution on doing this without a temp file and without data binding
Go to post #35 -> http://www.vbforums.com/showpost.php...2&postcount=35
Ok - I've been loading pictures into an image control with LOADPICTURE like this:
Code:
.imgImage.Picture = LoadPicture(s1)
with s1 being a string to the filepath and filename.
Now we want to support storing pictures in our DB - and I got this code from looking at a thread by Si.
Code:
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "Exec GetStuPhoto_P '" & s3 & "'", gCn
Set .imgImage.DataSource = rs
.imgImage.DataField = rs.Fields(0).Name
rs.Close
Set rs = Nothing
Here I'm calling a stored procedure to grab the image from a table in the database.
Do I need to use .DataSource - is that the only (and best) way to do this? I've never used the DataSource property before - it sounds like a "bound-control" and I'm worried about that.
Any other opinions on this code?
Thanks!
Last edited by szlamany; Sep 28th, 2007 at 07:14 AM.
-
Aug 7th, 2007, 06:53 PM
#2
Re: Image control - now getting picture from DB - datasource??
If you want to store and retrieve file (any file) to/from database then look at two links in my signature.
But main idea is - to display image from db you will have to extract it first to hard disk and then use LoadPicture.
-
Aug 8th, 2007, 06:32 AM
#3
Re: Image control - now getting picture from DB - datasource??
Originally Posted by RhinoBull
...But main idea is - to display image from db you will have to extract it first to hard disk and then use LoadPicture.
What I showed though is working by setting the .DataSource.
Why would making it back into a jpg file and using LoadPicture be more efficient?
-
Aug 8th, 2007, 07:52 AM
#4
Re: Image control - now getting picture from DB - datasource??
Originally Posted by szlamany
I've never used the DataSource property before - it sounds like a "bound-control" and I'm worried about that.
And it is - you are binding to ado recordset in this case.
Performance wise you might not notice any difference but I am so against any kind of binding so I would not use it even if someone shows me it's 3 times faster (it's hard to believe though).
-
Aug 8th, 2007, 07:54 AM
#5
Re: Image control - now getting picture from DB - datasource??
RB - thanks for the input...
Here's my confusion - I've got the IMAGE - loaded via a OPEN/BINARY and put into a database.
Why can't I take that BINARY image - which is in memory again - in a recordset - and get it back into an IMAGE control??
-
Aug 8th, 2007, 08:03 AM
#6
Re: Image control - now getting picture from DB - datasource??
Maybe it's a nature of BLOB types so they have to be extracted first. I never found reasonable explanation.
-
Aug 8th, 2007, 08:21 AM
#7
Re: Image control - now getting picture from DB - datasource??
Using a bound control is a bit easier (hence why Beacon used in it his tutorial, which I re-posted here), but it is something I have avoided myself.
I have posted a routine here (which I should have appended to the tutorial, I'll try to do it later! ) that can be used directly with the tutorial code - I think it is pretty similar to RhinoBull's, tho more simplified, so it might not be as reliable.
In terms of speed, I would actually expect the bound version to be noticeably faster - as you do not need to use the hard drive any extra times (twice - once to save the file, then load it again) which is far slower than using memory directly (which I presume binding does). To be sure, you should do some tests tho - on the smaller/larger/average sized (in terms of KB) files you are expecting to use, as well as different file types if you are using them.
Why can't I take that BINARY image - which is in memory again - in a recordset - and get it back into an IMAGE control??
If you know the code, it is possible... but it isn't simple, and I don't think I've ever seen a reliable version.
If I was going to do it myself I would try using a DIB section, and CopyMemory to push the data across.. but would expect to spend a few days getting it right.
To be honest tho, I don't think this approach would gain much in terms of speed over the bound version - assuming that the bound version is actually noticeably faster than the non-bound version.
-
Aug 8th, 2007, 08:27 AM
#8
Re: Image control - now getting picture from DB - datasource??
Thanks Si!
So what are the downfalls of using this in a bound fashion? I never used bound controls - so I only know of the evils through reading threads on the forum here.
Obviously the image control is not really bound to the DB or the table - as I'm destroying the recordset object immediately.
So in reality is the bound-nature only really just passing the binary data from the DB into the image control - and that's the end of it??
Should I be doing anything different in the RS.OPEN call - that would make this better.
I'm such a SPROC person with parameters that it's been 6 years since I wrote a SQL statement in a RS.OPEN like this
-
Aug 8th, 2007, 08:44 AM
#9
Re: Image control - now getting picture from DB - datasource??
If the image is still displayed after rs is closed, I think it is just passing the data - hence no need for the extra work with a DIB etc!
I'm not sure what the problem(s) of the bound version of this are, I would guess the only issue you need to worry about would be the way errors are dealt with - I don't know if that error occurs when you try to set the .DataField etc, or if it occurs elsewhere. You should try loading picture files that aren't valid (such as a text file!) to see what happens; then add apt error handling.
As far as rs.Open goes, I would actually use a Command object instead (with Set rs = cmd.Execute ), tho if you do want rs.Open you should set apt parameters after the connection object - see the FAQ article here, which has explanations/recommendations of what they should be.
-
Aug 8th, 2007, 09:11 AM
#10
Re: Image control - now getting picture from DB - datasource??
Originally Posted by szlamany
Thanks Si!
So what are the downfalls of using this in a bound fashion?
The answers are very simple:
- bound mode is not reliable
- you have absolutely no control of what is going on
Implementing few lines of code that would take care of Save/Extract file is not big of a deal what so ever - you do it once and you are done.
Entire logic could be wrapped into a class but even if it's in a module it's still reusable.
-
Aug 8th, 2007, 09:28 AM
#11
Re: Image control - now getting picture from DB - datasource??
Originally Posted by RhinoBull
- bound mode is not reliable
I've been involved in several threads about Beacons image tutorial, and don't think I've heard that before when related to his code (as shown in post #1).. Do you know of any particular issues?
- you have absolutely no control of what is going on
As with any bound controls, there is some truth in that.. but as it is just for loading the picture from the recordset (which works nicely with minimal code), I don't see it being an issue.
As I've already said, I have avoided bound controls myself in cases like this in the past.. but thinking about it now (after seeing the successes of people who used Beacons tutorial), I think that may have been just avoiding binding for the sake of it.
In general (in Classic VB at least) binding is definitely evil, but I think this particular situation may be an exception.
-
Aug 8th, 2007, 09:52 AM
#12
Re: Image control - now getting picture from DB - datasource??
I think for security reasons alone I cannot really re-create the .JPG onto a file on the users machine - if the app crashes that .JPG could be left on the disk. We certainly don't want to start copying pictures of 5 to 18 year old children onto potentially a 1000 different PC's townwide.
Originally Posted by si_the_geek
... You should try loading picture files that aren't valid (such as a text file!) to see what happens; then add apt error handling.
Already doing error trapping - I simply didn't post that part. We try to load the image once - if it doesn't come back we try to load image "0" which is a pic of "No Image" and if that fails we simply make the image control not visible. We had all that error trapping in place for when we did it from .JPG files already.
As far as rs.Open goes, I would actually use a Command object instead (with Set rs = cmd.Execute ), tho if you do want rs.Open you should set apt parameters after the connection object - see the FAQ article here, which has explanations/recommendations of what they should be.
I left it with rs.Open - it's so few lines of code and it's working. But I did change it to:
Code:
rs.Open "Exec GetStuPhoto_P '", gCn, AdOpenForwardOnly, adLockReadOnly, adCmdText
Most of those were the defaults - but why not specify them anyway!
-
Aug 8th, 2007, 09:59 AM
#13
Re: Image control - now getting picture from DB - datasource??
Usually binding gives errors in odd places (and sometimes odd errors too!), so I would recommend running with the error handling disabled a couple of times, so you are sure where the error(s) are occurring.
You seem have a typo in your post (previously you had: "Exec GetStuPhoto_P '" & s3 & "'" ), I hope it's correct in the code!
-
Aug 8th, 2007, 10:01 AM
#14
Re: Image control - now getting picture from DB - datasource??
Originally Posted by si_the_geek
You seem have a typo in your post (previously you had: "Exec GetStuPhoto_P '" & s3 & "'" ), I hope it's correct in the code!
Free hand typing onto my workstation with VB in IE and looking over my shoulder at my laptop!
-
Aug 8th, 2007, 10:18 AM
#15
Re: Image control - now getting picture from DB - datasource??
I just tested lots of bad image data in the VARBINARY field - and my error trapping worked fine.
But I was able to crash the IDE when I tested a "half image" - I actually loaded 50% of the image data into the field - and the IDE crashed with the old "report to MS pop-up". But that same bad image got caught nicely in a compiled version of the .exe.
-
Aug 8th, 2007, 12:08 PM
#16
Re: Image control - now getting picture from DB - datasource??
Originally Posted by szlamany
I think for security reasons alone I cannot really re-create the .JPG onto a file on the users machine - if the app crashes that .JPG could be left on the disk. We certainly don't want to start copying pictures of 5 to 18 year old children onto potentially a 1000 different PC's townwide.
But really creating a sytem's temp file and even name is given to you by the system - it may look simething like "abc123.tmp" so you're pretty much safe.
TMP file have no default viewer too and most of your users are unlikely to check on them.
I know you can say "they are kids" but still...
-
Aug 13th, 2007, 02:18 PM
#17
Re: Image control - now getting picture from DB - datasource??
Ok - binding bit me in the...
See this thread: http://www.vbforums.com/showthread.p...93#post2974693
How do I go about the DIB/CopyMemory technique???
-
Aug 13th, 2007, 02:27 PM
#18
Re: [RESOLVED] Image control - now getting picture from DB - datasource??
I really do not understand what you're trying to accomplish - I did try to point you in the "right" (if you will) direction but to no avail...
-
Aug 13th, 2007, 02:48 PM
#19
Re: [RESOLVED] Image control - now getting picture from DB - datasource??
Really rather simple - .Net has the tools to turn a byte array into an image using IO.MemoryStream - I want that same functionality in VB6.
Hopefully I'll get some action on the link to the other thread I pasted into post #17.
-
Aug 13th, 2007, 03:59 PM
#20
Re: [RESOLVED] Image control - now getting picture from DB - datasource??
Alright then... just couldn't let go...
Code:
Private Sub Command1_Click()
'==========================
Dim RstPicture As ADODB.Recordset
Dim strSQL$, arBytes() As Byte
strSQL = "Select fImage, fImageSize From Themes Where fName = '" & your_image_name & "'"
Set RstPicture = New ADODB.Recordset
RstPicture.Open strSQL, adoConn 'assuming that adoConn is a valid connection
If Not RstPicture.EOF Then
arBytes() = RstPicture.Fields("fImage").GetChunk(RstPicture.Fields("fImageSize"))
use code from this link
End If
End Sub
NOTE: I did not test it - really no time for that.
Let me know how it goes.
-
Aug 13th, 2007, 05:03 PM
#21
Re: [RESOLVED] Image control - now getting picture from DB - datasource??
@rb - thank you very much for the link...
Some of the code references are not in the zip I downloaded
Dim oPersist As IPersistStream <-- this one
Dim oStream As IStream <-- this one
Set oStream = CreateStreamOnHGlobal(0, True) <--not sure about this one
I've googled around for this and come up more confused about what these are...
-
Aug 13th, 2007, 05:08 PM
#22
Re: [RESOLVED] Image control - now getting picture from DB - datasource??
That's unlike Eduardo...
Here is his home site: http://www.mvps.org/emorcillo/en/code/vb6/index.shtml
Try to browse it or perhaps contact him via email (it's available on his home page and he is asking to report any bug).
-
Aug 13th, 2007, 05:52 PM
#23
Re: [RESOLVED] Image control - now getting picture from DB - datasource??
I suspect it needs features from the item at the top of the page (OLELIB.TLB), especially due to the type names.
That looks like a very useful sample (once we get it working!), good find RB.
-
Aug 13th, 2007, 08:39 PM
#24
Re: [RESOLVED] Image control - now getting picture from DB - datasource??
Thanks SI. I actually re-discovered it by searching my own posts for "byte array to picture" so this post from more than a year ago was found.
It could indeed be a good thing in our code bank (with all links to original source of course).
... but let's get it done first ...
-
Aug 14th, 2007, 08:28 AM
#25
Re: [RESOLVED] Image control - now getting picture from DB - datasource??
Ok - I found a STRM.TLB type library at this download site
http://www.vbaccelerator.com/home/VB...pe_Library.asp
How do I make that part of myu VB project?
Does it simply become part of the executable or do I end up with an INSTALL/SETUP dependency from this?
-
Aug 14th, 2007, 08:31 AM
#26
Re: [NOT YET RESOLVED] Image control - now getting picture from DB - datasource??
Once you set references it will become part of your distribution I guess.
-
Aug 14th, 2007, 08:41 AM
#27
Re: [NOT YET RESOLVED] Image control - now getting picture from DB - datasource??
That seems to put me in the same boat as the MSBIND.DLL reference that I'm trying to avoid installing.
At any rate - I'm now having problems with declaring the CreateStreamOnHGlobal function.
I found this:
Code:
Private Declare Function CreateStreamOnHGlobal Lib "ole32" ( _
ByVal hGlobal As Long, _
ByVal fDeleteOnRelease As Long, _
ppstm As IStream) As Long
But the arg's are not lined up with the call properly.
Code:
Set oStream = CreateStreamOnHGlobal(0, True)
-
Aug 14th, 2007, 08:52 AM
#28
Re: [NOT YET RESOLVED] Image control - now getting picture from DB - datasource??
May I suggest something:
Hypothetically, if you decide to go with creating a temp file and then use LoadPicture then here is what you can do:
- you may save file name/user name in some table in case app crashes
- next time login you will check the hard drive for that (or those) file (files) and delete them all
- if application exit normally you will delete those temps anyway right after image is loaded into the viewer
- this will give some time to figure out the "pure memory approach"
So honestly I don't see any hassle or logic flow with this approach but I understand your concern.
-
Aug 14th, 2007, 08:55 AM
#29
Re: [NOT YET RESOLVED] Image control - now getting picture from DB - datasource??
I've used a few .tlb files before, and that's the way it worked for me. I always put them in App.Path, but I have no idea if that is the 'correct' place for them.
After a brief inspection it seems that STRM.TLB is just the relevant parts of OLELIB.TLB, so it should be OK - and saving a lot of size too!
edit: so slow! I shouldn't have taken a coffee break mid-post!
-
Aug 14th, 2007, 09:02 AM
#30
Re: [NOT YET RESOLVED] Image control - now getting picture from DB - datasource??
@si - But it's the distribution issue - 1000 pc's that I think the IT dept doesn't have time this month to mess with.
This .TLB will need to be installed on user machines - right?
-
Aug 14th, 2007, 09:11 AM
#31
Re: [NOT YET RESOLVED] Image control - now getting picture from DB - datasource??
I think so (not sure about registering, it's been too long).. but it seems you may not actually need it!
There is an alternative definition (and usage) of the API here. Even tho IUnknown doesn't get shown in intellisense, it is perfectly valid without any references.
And another version here (Karl E P.), with the 'original source' here (Brad Martinez).
I don't know if any of these would work for you, but if they do then they take out the requirement for additional files.
-
Aug 14th, 2007, 09:16 AM
#32
Re: [NOT YET RESOLVED] Image control - now getting picture from DB - datasource??
They "push" it silently - it's getting done in every large company. If they support 1000+ PCs then admins must have some scripts they run for updates, etc.
All that's needed is an XCopy and regserv to be excuted via batch on login.
If they do all the updates manually then somebody has go back to school.
-
Aug 14th, 2007, 09:25 AM
#33
Re: [NOT YET RESOLVED] Image control - now getting picture from DB - datasource??
Si - that worked great - can I post this code? Seems like MVPS.ORG doesn't like posting of their code on other forums - what's the rules for this??
-
Aug 14th, 2007, 09:42 AM
#34
Re: [NOT YET RESOLVED] Image control - now getting picture from DB - datasource??
Well unfortunately this copyright message is on Brad's page:
It is PROHIBITED to distribute or reproduce any of the files or code found in this site for profit or otherwise, on any web site, ... without the EXPRESS WRITTEN PERMISSION of the author.
If you have just used it as-is, then linking to the original is probably the best idea (tho if you want, you can ask him for permission).
However.. if you have made modifications to get it working for this situation, then that would be considered fair use and you can post it - preferably with a link to the original of course.
-
Aug 14th, 2007, 09:48 AM
#35
Re: [RESOLVED] Image control - now getting picture from DB - datasource??
Ok - I used this download:
the 'original source' here (Brad Martinez).
Gave me a nice small little MODULE that I could add to my vb project.
Which gave me a function called PictureFromBits() that I used like this:
Code:
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim arBytes() As Byte
rs.Open "Exec " & Mid(s1, 2) & " '" & s3 & "'", gCn, adOpenForwardOnly, adLockReadOnly, adCmdText
If rs.EOF Then
rs.Close
Set rs = Nothing
End If
arBytes() = rs(0).GetChunk(rs(1))
.imgImage.Picture = PictureFromBits(arBytes())
rs.Close
Set rs = Nothing
Allowing me to convert an image stored as a BINARY field in a DB back to a picture object in memory without ever going to a JPG file on disk.
And without using DATABINDING...
and without adding references to the project that would need to be installed.
[edit] btw - it might seem odd that I'm checking rs.EOF and destroying the RS but continuing on with the code. That's because the error trap is designed to "re-try" to load a default image of "No Picture Available" if the first load fails.
Last edited by szlamany; Jan 23rd, 2008 at 05:33 AM.
-
Aug 14th, 2007, 09:59 AM
#36
Re: [RESOLVED] Image control - now getting picture from DB - datasource??
PictureFromBits is basically the only difference between your last post and post #20.
So, did it actually for you Steve?
-
Aug 14th, 2007, 10:02 AM
#37
Re: [RESOLVED] Image control - now getting picture from DB - datasource??
Originally Posted by RhinoBull
PictureFromBits is basically the only difference between your last post and post #20.
So, did it actually for you Steve?
It's working fine - the big difference is the lack of requirement for the .TLB.
Being able to modify the app without changing the install requirements is kind of a big thing for us here.
-
Aug 14th, 2007, 10:35 AM
#38
Re: [RESOLVED] Image control - now getting picture from DB - datasource??
That's great! One headach less.
EDIT: just want to confirm that it definitely works - you just have to plug and play that module from Brad's download page.
Last edited by RhinoBull; Aug 14th, 2007 at 11:29 AM.
-
Feb 6th, 2009, 03:03 AM
#39
Re: [RESOLVED] Image control - now getting picture from DB - datasource??
Originally Posted by szlamany
Code:
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim arBytes() As Byte
rs.Open "Exec " & Mid(s1, 2) & " '" & s3 & "'", gCn, adOpenForwardOnly, adLockReadOnly, adCmdText
If rs.EOF Then
rs.Close
Set rs = Nothing
End If
arBytes() = rs(0).GetChunk(rs(1))
.imgImage.Picture = PictureFromBits(arBytes())
rs.Close
Set rs = Nothing
May I know what does rs(1) returns?
-
Feb 6th, 2009, 04:14 AM
#40
Re: [RESOLVED] Image control - now getting picture from DB - datasource??
Originally Posted by dee-u
May I know what does rs(1) returns?
We store the size of the image in bytes along with the image - just as a double-check when we re-load later that we got the "amount" of image we expected.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|