| Author |
 Topic  |
|
|
sr_erick
Senior Member
USA
1318 Posts |
 Posted - 05 June 2004 : 01:55:58
|
I'm looping through many image files at a time. I need to see if they exist in an access database and if not I need to add them. I've done this before by submitting a giant block of SQL all at once using if statements on MSSQL, but never access. I've wondering if this will work with access because it's just too slow for me doing a Select for each image name to see if it exists, then insert it...to open and close the connection, etc... Here's what I was going to do with the block of code, but it's not working.
IF NOT EXISTS(SELECT picturename FROM files WHERE picturename = '021.jpg')
INSERT INTO files (picturename) VALUES ('021.jpg');
IF NOT EXISTS(SELECT picturename FROM files WHERE picturename = '1.jpg')
INSERT INTO files (picturename) VALUES ('1.jpg');
IF NOT EXISTS(SELECT picturename FROM files WHERE picturename = '116yd.jpg')
INSERT INTO files (picturename) VALUES ('116yd.jpg');
IF NOT EXISTS(SELECT picturename FROM files WHERE picturename = '151yd.jpg')
INSERT INTO files (picturename) VALUES ('151yd.jpg');
IF NOT EXISTS(SELECT picturename FROM files WHERE picturename = '20046420220_327.jpg')
INSERT INTO files (picturename) VALUES ('20046420220_327.jpg');
IF NOT EXISTS(SELECT picturename FROM files WHERE picturename = '200464202335_327.jpg')
INSERT INTO files (picturename) VALUES ('200464202335_327.jpg');
IF NOT EXISTS(SELECT picturename FROM files WHERE picturename = '200464202435_327.jpg')
INSERT INTO files (picturename) VALUES ('200464202435_327.jpg');
IF NOT EXISTS(SELECT picturename FROM files WHERE picturename = '200464202822_327.jpg')
INSERT INTO files (picturename) VALUES ('200464202822_327.jpg');
IF NOT EXISTS(SELECT picturename FROM files WHERE picturename = '200464203236_327.jpg')
INSERT INTO files (picturename) VALUES ('200464203236_327.jpg');
IF NOT EXISTS(SELECT picturename FROM files WHERE picturename = '200464204536_327.jpg')
INSERT INTO files (picturename) VALUES ('200464204536_327.jpg');
IF NOT EXISTS(SELECT picturename FROM files WHERE picturename = '200464205546_327.jpg')
INSERT INTO files (picturename) VALUES ('200464205546_327.jpg');
IF NOT EXISTS(SELECT picturename FROM files WHERE picturename = 'bigd-scan032-YamilaDiaz-02.jpg')
INSERT INTO files (picturename) VALUES ('bigd-scan032-YamilaDiaz-02.jpg');
IF NOT EXISTS(SELECT picturename FROM files WHERE picturename = 'shannon_001.jpg')
INSERT INTO files (picturename) VALUES ('shannon_001.jpg');
IF NOT EXISTS(SELECT picturename FROM files WHERE picturename = 'yamila25.jpg')
INSERT INTO files (picturename) VALUES ('yamila25.jpg');
IF NOT EXISTS(SELECT picturename FROM files WHERE picturename = 'yamila26.jpg')
INSERT INTO files (picturename) VALUES ('yamila26.jpg');
IF NOT EXISTS(SELECT picturename FROM files WHERE picturename = 'yamila27.jpg')
INSERT INTO files (picturename) VALUES ('yamila27.jpg');
Anyone know??? Thanks! |
Erick
Snowmobile Fanatics
|
|
|
sr_erick
Senior Member
USA
1318 Posts |
Posted - 05 June 2004 : 02:00:37
|
By the way, here's the error I'm getting. Using a database on MSSQL and query analyzer everything checks out and seems to work fine. I'm assuming access is just not powerfull enough to do this. 
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
/gallery/image2.asp, line 27
Anyone have any workarounds, any other fast ways of doing this with access?
EDIT: I don't think access can do this...anyways...I've moved it over to MSSQL and yes, it works...but I don't think it will be efficient enough with TONS of files so here's the deal. If anyone has any solution at ALL, please, please, please let me know.
I'm working on my image gallery, revising it to make it faster. The thing is, I want to keep everything in sync (thatis what's in the database needs to exist on the file system). The thing that "isn't" easy is to make sure that everything on the file system (all the images, and ones that might have been added to the folder in the file system), may not be in the database. How am I going to check and see if a directory has any new files since the last time the asp script ran on the directory? How am I going to figure out which files are new since last visit to that directory without looping through files, because we could literally have thousands of files per folder.
The idea I had was to first loop through the file system, check to make sure that each file exists in the database, if not, add it.
Then I would loop through that particular directory in the database, make sure the files from the db exists on the file system. I know this may sound redundant but it's the only way I can think of to always make sure I purge records from the db which no longer have an image, and to make sure that every image on the file system always has a record in the database.
So, any fancy ideas?
|
Erick
Snowmobile Fanatics
|
Edited by - sr_erick on 05 June 2004 03:00:04 |
 |
|
|
sr_erick
Senior Member
USA
1318 Posts |
Posted - 05 June 2004 : 03:08:19
|
| Arg...Just keep thinking of this and ideas to fix it. What about doing something like a SELECT TOP 1 FROM TABLE ORDERBY DateAdded DESC or whatever to get the newest file that has been added to the database, then somehow start looping through every file from the file system, starting with files that are newest. If the date is newer than the date I get from the database, then I add it, or else I don't do anything. |
Erick
Snowmobile Fanatics
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
redbrad0
Advanced Member
USA
3725 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 05 June 2004 : 13:11:01
|
You can't have a cake and then it too! To me things are simple. Either you manage the files under program control (that means adding, moving or removing files) or you will have to undergo that costly scheme described by sr_erick earlier, if you want the database to always have the most up-to-date info.
If you just want to check for new files, the date scheme looks good to me. You could also add a periodic all file check to ensure that you don't lose track of transfers and deletions. This looks a less costly way to do it than the first scheme. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
redbrad0
Advanced Member
USA
3725 Posts |
Posted - 05 June 2004 : 13:31:08
|
quote:
Originally posted by ruirib
If you just want to check for new files, the date scheme looks good to me. You could also add a periodic all file check to ensure that you don't lose track of transfers and deletions. This looks a less costly way to do it than the first scheme.
Yep exactly what I told him last night  . Have a variable stored in the application value of the last time the files were checked. Then every XX amount of minutes/hours/days you can then reprocess all the files. If the page takes some time you could even just have a page running for the user that happens to get the reprocess all files that just says "please wait while we check the images to make sure you are viewing the most updated images. This process should be done shortly. |
Brad
Oklahoma City Online Entertainment Guide
Oklahoma Event Tickets |
|
|
|
sr_erick
Senior Member
USA
1318 Posts |
Posted - 05 June 2004 : 15:46:50
|
Well I just do not like this scheduled running of script idea. I'd rather have a button that I would manually push to process images that were newly FTP'ed up to a folder.
Here's what I think I'll do. I currently don't have an efficient way to resort the order of the images from the file system by date, starting from newest so what I'm doing is grabbing date from db, grabbing datecreated from file system...if the file is new, I add it to db.
It actually doesn't take "too" long to do this check. about 150ms or so for 108 images, and that's on a relativly slow dual PIII 600mhz server. I will put all of the sql for adding images into a huge block and run that huge block on the database after the loop, that will save me lots of opening and closing.
I will throw some option in there so a person using the gallery can disable this check, or can have a button be displayed to "check for new images in current folder".
Oke..I think i'm happy again.
Edit: (Yes, again)
I guess that the object folder.DateLastModified will return an updated date if a file inside that folder has been edited, added, or removed. Now this is even better because all I need to do is compare the date last modified from the database to that of the folder, if they differ then I can loop through the files, doing the same date compare, and add what needs to be added.
Note: I know it may sound like I'm rambling, but I'm just spitting out ideas and I hope maybe some of this can become usefull to someone else. I want to release this gallery for free to the community here, but I want it done right, and I want it fast. |
Erick
Snowmobile Fanatics
|
Edited by - sr_erick on 05 June 2004 19:07:43 |
|
|
| |
Topic |
|
|
|
Topic Locked
