Images (jpgs) and Replication

Hi,
At the moment we have apx 15 images per item (apx 5000/client).
Currently, these are sent to us from the client to our lan so they can be
QC'd.
They go through the QC process and a database pointer is allocated to the
database item (in tblItemImages ( itemID INT, ImageFileName varchar(255),
ImageFolderID INT )
tblItemImages is uploaded to the webserver on a scheduled basis (noon &
midnight) and the images are FTP sync'd at the same time.
We may have a requirement to upload the images every hour (or every few
minutes?), in which case the ftp sync becomes a large overhead.
My question is, how feasable is it to store images in SQL Server and have
them replicated to a remote server (dedicated 1kbps line).
The images are apx 25-50KB each and only 3 of the 15 images are needed on
the webserver.
Getting the images in and out of the database shouldn't be so much of an
issue.Rebecca,
it shouldn't be a problem. I've done that on a database with loads of
image columns running merge replication with >20 subscribers, some of
them connected by a standard ISDN connection. So in your case it should
work without too much problems. But then again in my experience
replication always creates some (un)expected problems.
Markus|||Moving from storing the images as files to storing them in image columns
would require modifications to the database model and website programming.
Why is the FTP sync becomming an overhead; does the FTP utility support a
feature for only transferring files that don't exist on the destination or
have an updated timestamp?
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:434e3f98$0$136$7b0f0fd3@.mistral.news.newnet.co.uk...
> Hi,
> At the moment we have apx 15 images per item (apx 5000/client).
> Currently, these are sent to us from the client to our lan so they can be
> QC'd.
> They go through the QC process and a database pointer is allocated to the
> database item (in tblItemImages ( itemID INT, ImageFileName varchar(255),
> ImageFolderID INT )
> tblItemImages is uploaded to the webserver on a scheduled basis (noon &
> midnight) and the images are FTP sync'd at the same time.
> We may have a requirement to upload the images every hour (or every few
> minutes?), in which case the ftp sync becomes a large overhead.
> --
> My question is, how feasable is it to store images in SQL Server and have
> them replicated to a remote server (dedicated 1kbps line).
> The images are apx 25-50KB each and only 3 of the 15 images are needed on
> the webserver.
>
> Getting the images in and out of the database shouldn't be so much of an
> issue.
>
>|||Yes it does, but the number of images is enormous and it takes ages just to
bring down the image file names and timestamps to work out which ones need
to be uploaded.
images*items*clients
3*5000*5 = 75000
Altho we do it per client at the moment, so it's only 15000 at a time, but
it's still alot of data to bring down.
"JT" <someone@.microsoft.com> wrote in message
news:uu39a8%23zFHA.3812@.TK2MSFTNGP09.phx.gbl...
> Moving from storing the images as files to storing them in image columns
> would require modifications to the database model and website programming.
> Why is the FTP sync becomming an overhead; does the FTP utility support a
> feature for only transferring files that don't exist on the destination or
> have an updated timestamp?

Image data type. Tricks and tips anyone?

Tools I want to use...

Sql2k/MSDE
ADO 2.x
VB6

Before I waste time experimenting, thought I'd ask the veterans:

I want to store Jpgs into a table, using the image datatype.

Will ADO auto-magically pick up that I'm streaming an image in when I try to do this...

Dim Pic as StdPicutre, RS as ADODB.Recordset
RS.open "select * from...."
set pic = Rs!MyImage

Or do I have to go through bizarre motions like this...

Dim Pic as StdPicutre, RS as ADODB.Recordset
Dim Blob() as byte

Blob = rs!MyImage

Open MyFilePath for Binary as #FileNum
Put #FileNum,,Blob
Close #FileNum

Set Pic = LoadPicture(MyFilePath)

Any thoughts?

(Please don't recommended linked controls, I hate those damn things with a passion.)

Thanks for reading!Agree about linked controls.
If you search you can find code related to inserting images.
Do you need to hold them in the database?
I usually hold them in files on the ado application server with the filename in the database - this saves the network traffic in saving and retrieving and gets round the transaction/trigger issues.|||I found some code, yes. All ways to acomplish this are easy, don't get me wrong...just looking for the simplest solution.

You seam to be implying that you do not recomend storing the actually image bits in the database itself...at all. The system you talk off is similar to what I'm doing now.

If it's not broken, don't fit it?

Is there anything to gain by placing these images into a table?

Thanks!|||Don't see what you would gain - you can't do a lot with them anyway.
If you do want to save them in a table then I would make this a separate database so that it doesn't compromise the rest of the data.
Would still prefer to leave them in files though.

Image data type. Tricks and tips anyone?

Tools I want to use...

Sql2k/MSDE
ADO 2.x
VB6

Before I waste time experimenting, thought I'd ask the veterans:

I want to store Jpgs into a table, using the image datatype.

Will ADO auto-magically pick up that I'm streaming an image in when I try to do this...

Dim Pic as StdPicutre, RS as ADODB.Recordset
RS.open "select * from...."
set pic = Rs!MyImage

Or do I have to go through bizarre motions like this...

Dim Pic as StdPicutre, RS as ADODB.Recordset
Dim Blob() as byte

Blob = rs!MyImage

Open MyFilePath for Binary as #FileNum
Put #FileNum,,Blob
Close #FileNum

Set Pic = LoadPicture(MyFilePath)

Any thoughts?

(Please don't recommended linked controls, I hate those damn things with a passion.)

Thanks for reading!Agree about linked controls.
If you search you can find code related to inserting images.
Do you need to hold them in the database?
I usually hold them in files on the ado application server with the filename in the database - this saves the network traffic in saving and retrieving and gets round the transaction/trigger issues.|||I found some code, yes. All ways to acomplish this are easy, don't get me wrong...just looking for the simplest solution.

You seam to be implying that you do not recomend storing the actually image bits in the database itself...at all. The system you talk off is similar to what I'm doing now.

If it's not broken, don't fit it?

Is there anything to gain by placing these images into a table?

Thanks!|||Don't see what you would gain - you can't do a lot with them anyway.
If you do want to save them in a table then I would make this a separate database so that it doesn't compromise the rest of the data.
Would still prefer to leave them in files though.

Image and tables

Is it possible to save jpgs in a table if the DataType is set to varbinary(MAX), also how would I save the images to the table.

Thanks

Hi,

yes that possible, but depends on the used coding language, would be nice if you could provide some more information about that.

-Jens Suessmeyer.

http://www.sqlserver2005.de

|||SQL Server backend with Access VBA code|||

Hi,

sorry for picking up your issue this late, but sometime posts get out of sight in my mailbox and the weekend is preferable my time to pick them all up. Have a look at some sample code here:

http://www.freevbcode.com/ShowCode.Asp?ID=1802

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de