Start Free Trial

asked on

How to copy files from one location to another via path/filename in MySQL DB

I have a MySQL DB table that has a column that stores the pointers of image files. The actual images are stored on a different server.

What I want to do is write a script, triggered from an ASP page, that will search the DB based on form criteria then, based on the image field path/filename, copy that file to another loaction.

I'm not sure if this is the correct area for this question but I figured I would start here since I don't know which way this will go.

Thank you in advance for any support.....

Mark

The following is an example of copying files

<%
FileCopy      "C:\New Folder\file.txt", "C:\New Folder\file2.txt"

Private Sub FileCopy(source, destination)
      Dim objFSO, objToCopy, boolErr, strErrDesc
      On Error Resume Next
      Set objFSO = Server.CreateObject("scripting.filesystemobject")
      Set objToCopy = objFSO.GetFile(source)
      objToCopy.Copy destination
      if Err Then
            boolErr = True
            strErrDesc = Err.Description
      end if
      Set objToCopy = Nothing
      Set objFSO = Nothing
      On Error GoTo 0
      if boolErr then Err.Raise 5104, "FileCopy Statement", strErrDesc
End Sub
%>

but keep in mind that you will need write permissions in order for the file copy to be successful

ASKER

tctekkie,

How exactly would I use this code? Does it need to run in it's own page? What I need is a button on an existing page that will (when clicked) run the file copy function.
Could you show me an example of what the page would look like?

Thanks,

Mark

Sure. Code for the form and button as follows:

<form method="post" action="thisfile.asp">
<input type="submit" name="btnSubmit" id="btnSubmit" value="Copy">
</form>

In the top of "thisfile.asp" include the following code:

<%@ Language=VBScript>
<%
if Request.Form("btnSubmit") = "Copy" then
Call FileCopy(nameofsourcefile, nameofdestinationfile)
end if
Response.Redirect("redirectpage.asp")

Sub FileCopy(source, destination)
Dim objFSO, objToCopy, boolErr, strErrDesc
On Error Resume Next
Set objFSO = Server.CreateObject("scripting.filesystemobject")
Set objToCopy = objFSO.GetFile(source)
objToCopy.Copy destination
if Err Then
boolErr = True
strErrDesc = Err.Description
end if
Set objToCopy = Nothing
Set objFSO = Nothing
On Error GoTo 0
if boolErr then Err.Raise 5104, "FileCopy Statement", strErrDesc
End Sub
%>

ASKER

Let me rephrase my issue...

When a search is done on the DB, the results are displayed which include the image filename stored iin the DB. What I need to do is copy those images(filenames), that the search result shows, from their location on the network to a file on a cd or harddisk. So, the results from the search and the files that would be on the cd are matching.

I hope that might clear up my problem...

Mark

Then when you query your database, reference the field in the recordset that contains the file path & name. From that use FileCopy...

=============================================

cn = Server.CreateObject("ADODB.Connection")
rs = Server.CreateObject("ADODB.Recordset")

cn.open <your connection string>

sSQL = "SELECT ImagePath FROM tblImages"
rs.open sSQL, cn

if NOT rs.eof then
Do until rs.eof
Call FileCopy(rs("ImagePath"), nameofdestination)
rs.movenext
Loop
end if
rs.close
set rs = nothing

cn.close
set cn = nothing

=============================================

something such as that should work...

ASKER

Here is the code I have, that doesn't work. Could you take a look at it to see what I am missing?

Thank you very much for your help on this.....Mark

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>


<%
Dim rsTickArchive__MMStartDate
rsTickArchive__MMStartDate = "%"
If (Session("startdate") <> "") Then
rsTickArchive__MMStartDate = Session("startdate")
End If
%>
<%
Dim rsTickArchive__MMEndDate
rsTickArchive__MMEndDate = "%"
If (Session("enddate") <> "") Then
rsTickArchive__MMEndDate = Session("enddate")
End If
%>
<%
'Output the page in plain text format
Response.ContentType = "text/csv"
Response.AddHeader "Content-Disposition", "attachment; filename=ticketarchive.csv"

Dim rsTickArchive
Dim rsTickArchive_numRows

Set rsTickArchive = Server.CreateObject("ADODB.Recordset")
rsTickArchive.ActiveConnection = MM_dbDocImg_STRING
rsTickArchive.Source = "SELECT tickets.documentid, images.imagesrc FROM docimg.tickets JOIN docimg.images ON images.documentid=tickets.documentid WHERE ticketdate BETWEEN '" + Replace(rsTickArchive__MMStartDate, "'", "''") + "' AND '" + Replace(rsTickArchive__MMEndDate, "'", "''") + "' ORDER BY documentid"
rsTickArchive.CursorType = 0
rsTickArchive.CursorLocation = 2
rsTickArchive.LockType = 1
rsTickArchive.Open()

rsTickArchive_numRows = 0

if NOT rsTickArchive.Fields.Item("imagesrc").Value.eof then
      DO until rsTickArchive.Fields.Item("imagesrc").Value.eof
            Call FileCopy(rsTickArchive("imagesrc"),"c:\test\test.txt")
            rsTickArchive.movenext
      Loop
End if

%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
rsTickArchive_numRows = rsTickArchive_numRows + Repeat1__numRows

%>
<%
'FileCopy
Sub FileCopy(source, destination)
Dim objFSO, objToCopy, boolErr, strErrDesc
On Error Resume Next
Set objFSO = Server.CreateObject("scripting.filesystemobject")
Set objToCopy = objFSO.GetFile(source)
objToCopy.Copy destination
if Err Then
boolErr = True
strErrDesc = Err.Description
end if
Set objToCopy = Nothing
Set objFSO = Nothing
On Error GoTo 0
if boolErr then Err.Raise 5104, "FileCopy Statement", strErrDesc
End Sub
%>

<%
'destroy recordset and close connection
rsTickArchive.Close()
Set rsTickArchive = Nothing

'flush buffer
Response.Flush
'Response.End()
%>

ASKER

This code needs to search the DB based on the query, then based on the filename in the DB field, copy each individual image from the remote server where the images are stored, to a new specified folder. In the DB, each field is the name of an image file...(example: Table=images, Column1=documentid, Column2=imagesrc..............159110 00028323.TIF)

Any help would be greatly appreciated.

Mark

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Thanks for the post!

I had to modify the SQL query to get it to work, but when I look at the folder where the images should be copied to, there is only one file which has no extension and the filename is "4"

Any ideas on this???

Mark

What is the actual output of the rsTickArchive("ImageSrc")? If I See the output I should be able to tell you why it's only copying without an extension.

ASKER

tctekkie,

How would I output the field data to a blank page?

Mark

Add a Response.Write to your loop

Do Until rsTickArchive.EOF
Call FileCopy(rsTickArchive("ImageSrc"), sDestination)
'Write the Field value to the page
Response.Write rsTickArchive("ImageSrc") & "<BR>"
rsTickArchive.MoveNext
Loop

' then add the following line TEMPORARILY!
Response.end

The Response.end will end the script execution and you will see the values of the ImageSrc fields.

ASKER

tctekkie,

Thanks again for your excellent help on this! :)

The resulting page shows the field data like this(which is exactly as it is in the DB):

\\Rock\DocImages\0002840F.TIF
\\Rock\DocImages\0002841D.TIF
\\Rock\DocImages\0002841E.TIF
.....on and on it goes til the end of the file.....

So the data it is pulling from the DB is correct. There must be something in the FileCopy routine that isn't working properly. How does the sub routine know which array is the image filename? Is the line.... sImageName=UBound(arSource) ....pointing to the correct array? Maybe the routine sees an error and doesn't know how to handle it...just thinnking???

Mark

oh! Duh! My mistake!!! Sorry I ommtted a part of the UBound Function...

Replace

sImageName = UBound(arSource)

with

sImageName = arSource(UBound(arSource))

that should work... Sorry about the mistake!

ASKER

tctekkie,

Great! That worked perfectly! Thank you!

One last question about this....can the destination be coded to ask the user where to copy the images to, instead of a pre-determined path? In the end, I need to have these images copied to a DVD for archiving. So if the user had the option where to save it to, this task would be easily acomplished.

Mark

It could be... Again with the FileSystemObject you can get a list of Drives, Directories, etc. You could display a dropdown of drives and upon selection of a drive, you could display a list of directories, etc. then allow the user to click on a submit button. From that store the selections into the destination path variable.

ASKER

tctekkie,

Can you offer any assistance on this? I am fairly new to web development but am working my way through it, with much help.

Thanks,

Mark

A quick and dirty example of accomplishing this is:

<%@ Language=VBScript %>
<%

Dim objFSO
Dim objDrive
Dim objFolder

Dim sDrive, sFolder

Dim sSelDrive, sSelFolder

set objFSO = Server.CreateObject("Scripting.FileSystemObject")

sDrive = Request.Form("theDrive")

if len(trim(sDrive)) = 0 then sDrive = "C:"

For each objDrive in objFSO.Drives
      sSelDrive = sSelDrive & "<option value=" & objDrive & ">" & objDrive & "</option>"
Next
sSelDrive = Replace(sSelDrive,"value=" & sDrive,"value=" & sDrive & " selected")

objFSO.GetDrive(sDrive)
set objFolder = objFSO.GetFolder(sDrive & "\")
For each sFolder in objFolder.SubFolders
      sSelFolder = sSelFolder & "<option value=""" & sFolder & """>" & Replace(sFolder,sDrive & "\", "") & "</option>"
Next




%>

The in the body of the page:

<form name="theForm" id="theForm" action="SelPath.asp" method="POST">
Select a drive:  
<select name="theDrive" id="theDrive">
<%=sSelDrive%>
</select><BR><BR>

Select a folder:  
<select name="theFolder" id="theFolder">
<%=sSelFolder%>
</select> &nbsp
<input type="submit" value="submit">
</form>

Hope that gives you some sort of idea how it works. Another good reference is this:

http://www.devguru.com/Technologies/vbscript/QuickRef/filesystemobject.html

Good Luck! and Happy Coding! :)

ASKER

tctekkie,

When I test this code, no matter what drive I select, the folders displayed are from the c:\ drive on the server. I need to be able to select the drives that are on the computer which has the browser open, not the server.

Mark

Yes. the FileSystem object is being created by the server therefore collecting the drives/folder information from itself. I don't think there's a safe way to do this clientside as that depends on Javascript and not all people have this turned on. But a couple of examples using the clientside filesystem object can be found here:

http://yaldex.com/wjscript/jsFolderSubFolders.htm

http://yaldex.com/wjscript/jscolDrives.htm

Good Luck!

ASKER

Here is what I have so far, but it doesn't read the drive letters. Any help on this?

<%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%>

<%
function ShowDriveList()
{
var fso, s, n, e, x;
fso = new ActiveXObject("Scripting.FileSystemObject");
e = new Enumerator(fso.Drives);
s = "";
for (; !e.atEnd(); e.moveNext())
{
x = e.item();
s = s + x.DriveLetter;
s += " - ";
if (x.DriveType == 3)
n = x.ShareName;
else if (x.IsReady)
n = x.VolumeName;
else
n = "[Drive not ready]";
s += n + "<br>";
}
return(s);
}

%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>

<body>

<form name="theForm" id="theForm" action="SelPath.asp" method="POST">
Select a drive:  
<select name="theDrive" id="theDrive">
<%='javascript:ShowDriveList'%>
</select>           
<input name="submit" type="submit" value="submit" />
<BR><BR>
</form>

</body>
</html>

Make the javascript clientside...

<script language="javascript">
function ShowDriveList()
{
var fso, s, n, e, x;

var theDrive = document.theForm.elements['theDrive']

fso = new ActiveXObject("Scripting.FileSystemObject");
e = new Enumerator(fso.Drives);
s = "";
for (var x = 0; !e.atEnd(); e.moveNext())
{
x = e.item();
s = s + x.DriveLetter;

if (x.DriveType == 3) {
n = x.ShareName;
} else if (x.IsReady) {
n = x.VolumeName;
} else {
n = " - Drive not ready ";
}
      var myEle = document.createElement("option") ;
      myEle.value = s ;
      myEle.text = s + n ;
      theDrive.add(myEle) ;
      s = ""
      n = ""
}

}

</script>

And then remove the server side call to the javascript function
and finally place this at the very bottom of your page (underneath the </HTML> tag)

<script language = "javascript">
ShowDriveList()
</script>

ASKER

I tried your code and got the same result as before, just the form elements.

<%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%>

<script language="javascript">
function ShowDriveList()
{
var fso, s, n, e, x;

var theDrive = document.theForm.elements['theDrive']

fso = new ActiveXObject("Scripting.FileSystemObject");
e = new Enumerator(fso.Drives);
s = "";
for (var x = 0; !e.atEnd(); e.moveNext())
{
x = e.item();
s = s + x.DriveLetter;

if (x.DriveType == 3) {
n = x.ShareName;
} else if (x.IsReady) {
n = x.VolumeName;
} else {
n = " - Drive not ready ";
}
var myEle = document.createElement("option") ;
myEle.value = s ;
myEle.text = s + n ;
theDrive.add(myEle) ;
s = ""
n = ""
}

}

</script>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>

<body>

<form name="theForm" id="theForm" action="SelPath.asp" method="POST">
Select a drive:  
<select name="theDrive" id="theDrive">

</select>           
<input name="submit" type="submit" value="submit" />
<BR><BR>
</form>

</body>
</html>
<script language = "javascript">
ShowDriveList()
</script>

I thought that was what you wanted? You wanted some way for a user to browse the drives and then the directories to select a location for file copy?... Perhaps I misunderstood.

Regardless, I'm not that familiar with using the FileSystemObject client side and I really wouldn't recommend it. The code that you have is still working on the server side by the following line:
<%@Language="Javascript"%>

Therefore the filesystem object (and please correct me if I'm wrong) is still going to be created on the server therefore pulling from the server's hard drive...

ASKER

tctekkie,

That's the thing, I need to be able to select a drive from the client machine not the server. I thought the javascript you posted was for client side processing?

I did remove the server side line and still same result. There has to be a way to do this!!!

Mark

I agree Mark, there has to be a way to do it! I'm at work right now (and have been all day). Let me see what I can find when I get home from work this evening...

ASKER

tctekkie,

I hope you didn't take my comment in a bad way! I am very greatfull for the help you have given me today. It's just frustrating to me since I am very new to this stuff and it isn't make sense all the time. I know the more I use and develop these types of pages/code, the easier things will get. When it comes to networking or servers I have no problems figuring things out but this stuff is beyond me.

Again, thank you for all of your help!!!!!

Mark

I didn't take your comment badly. I know how aggravating it is when you know something can be accomplished but do not know how to get there.

Are these scripts/pages to be used on an intranet or on the internet? As I've stated previously you have to be very careful when using clientside scripts on the internet as it will not work for everybody.

Regardless, I will see what I can come up with this evening and will either post back tonight or in the morning.

Regards-
Jamie

ASKER

tctekkie,

Hey,

All of these scripts will only be used on our intranet. Sorry I didn't mention that before.

Mark

Ah! Okay... I will post an example shortly then Mark.

Okay Mark,

Here's what I have... Keep in mind that this is written in ClientSide VBScript so will ONLY be compatible with Internet Explorer... So! Here goes!

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
<script language="VBScript">

      Function ShowDriveList

            Dim objFSO
            Dim objDrive
            Dim myEle

            set objFSO = CreateObject("Scripting.FileSystemObject")

            For each objDrive in objFSO.Drives
                  set myEle = document.createElement("option")
                  myEle.value = objDrive
                  myEle.text = objDrive
                  document.theForm.theDrive.add(myEle)
            Next

      end Function

      Function ShowFolderList
            Dim objFSO
            Dim objDrive
            Dim objFolder
            Dim sDrive, sFolder
            Dim myEle

            set objFSO = CreateObject("Scripting.FileSystemObject")
            sDrive = document.theForm.theDrive.options(document.theForm.theDrive.selectedIndex).text

            if len(trim(sDrive)) = 0 then sDrive = "C:"

            objFSO.GetDrive(sDrive)
            set objFolder = objFSO.GetFolder(sDrive & "\")
            For each sFolder in objFolder.SubFolders
                  set myEle = document.createElement("option")
                  myEle.value = sFolder
                  myEle.text = sFolder
                  document.theForm.theFolder.add(myEle)
            Next
      End Function

      Function ShowSubFolderList
            Dim oSO
            Dim oDrive
            Dim oFolder
            Dim sDrive, sFolder
            Dim myEle
            Dim x

            set oFSO = CreateObject("Scripting.FileSystemObject")
            sDrive = document.theForm.theFolder.options(document.theForm.theFolder.selectedIndex).text

            oFSO.GetDrive(document.theForm.theDrive.options(document.theForm.theDrive.selectedIndex).text)
            msgbox(sDrive)
            set oFolder = oFSO.GetFolder(sDrive)

            document.theForm.theFolder.length = 0

            For each sFolder in oFolder.SubFolders
                  set myEle = document.createElement("option")
                  myEle.value = sFolder
                  myEle.text = sFolder
                  document.theForm.theFolder.add(myEle)
            Next
      End Function

</script>

</head>

<body>
<form name="theForm" id="theForm" action="SelPath.asp" method="POST">
<table align="Center">
      <tr>
            <td>
                  Select a drive:
            </td>
            <td>
                  <select name="theDrive" id="theDrive" onchange="ShowFolderList">
                  </select>
            </td>
      </tr>
      <tr>
            <td>
                  Select a Folder:
            </td>
            <td>
                  <select name="theFolder" id="theFolder">
                  </select> 
                  <input type="button" name="btnSubFolder" id="btnSubFolder" value="Show SubFolders" onclick="ShowSubFolderList()">
            </td>
      </tr>
      <tr>
            <td name="AddListDesc" id="AddListDesc">
            </td>
            <td name="AddList" id="AddList">
            </td>
      </tr>
      <tr>
            <td colspan="2" align="right">
<input name="submit" type="submit" value="submit">
</td>
</tr>
</table>
</form>
</body>
</html></BODY>
</HTML>
<script language="VBScript">
ShowDriveList()
</script>

ASKER

tctekkie,

Thanks for working on this! I tried your code and all I get is the form without any drive information. When I looked at the code, my Dreamweaver8 is showing that there appears to be something wrong in the second function set at line 39. Not sure what the problem is.

Mark

ASKER

skrodzkim,

When I check the error on the display page, it says: ActiveX component can't create object: 'Scripting.FileSystemObject'

Mark

ASKER

Jamie,

I have some code that you wrote that is working now, at least for the drive letter. Can you help me with some code for displaying the subfolders after the drive letter is selected?

Here is the code so far....

<script language="javascript">
function ShowDriveList()
{
var fso, s, n, e, x;

var theDrive = document.theForm.elements['theDrive']

fso = new ActiveXObject("Scripting.FileSystemObject");
e = new Enumerator(fso.Drives);
s = "";
for (var x = 0; !e.atEnd(); e.moveNext())
{
x = e.item();
s = s + x.DriveLetter;

if (x.DriveType == 3) {
n = x.ShareName;
} else if (x.IsReady) {
n = x.VolumeName;
} else {
n = " - Drive not ready ";
}
var myEle = document.createElement("option") ;
myEle.value = s ;
myEle.text = s + n ;
theDrive.add(myEle) ;
s = ""
n = ""
}

}

</script>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>

<body>

<form name="theForm" id="theForm" action="imgexport.asp" method="POST">
Select a drive:  
<select name="theDrive" id="theDrive">
<javascript:ShowDriveList)
</select> <BR><BR>
Select a folder:  
<select name="theFolder" id="theFolder">
<javascript:ShowFolderList)
</select> &nbsp
<input type="submit" value="submit">
</form>

</body>
</html>
<script language = "javascript">
ShowDriveList()
</script>

Mark

ASKER

tctekkie,

Can you offer any other help with this???

Mark

ASKER

Sorry for the delay in closing this question. The first part was answered and is working great, the second section is still in the works. I have given out the points based on the original question and answer. Thank you.

Mark