vbscript to insert filenames into oracle database table navigating through all children dirs

 

I have my media objects in a flat file structure on the deployment server and need to do some analysis in oracle on my F00165.  I really need to see what physical files still exist and then populate F00165 with the new file locations (yes we are moving).  This is a continuation of a series of emails about media objects and how they can go wrong.

This particular situation is at a client that has about 20 million records in F00165 and reference to over 5 000 000 physical files.  You may or may not know that trying to restore this amount of physical files to a single directory in windoze is going to kill the system.  The file allocations and internal referencing for a folder seems to crap out at about 100 000 files, and the copy then takes longer than me doing it on an abacus and arranging the bits and bytes with a magnet.

So I’ve created a oracle table with the command:

create table proddta.f00165 (mofilename varchar(256)) ;

Then ran the script

Set FSO = CreateObject("Scripting.FileSystemObject")

Dim Oracon
set oraccon = wscript.createobject("ADODB.Connection")
Dim recset 
set recset = wscript.createobject("ADODB.Recordset")
set Insertresults = wscript.createobject("ADODB.Recordset")
set f98moquerecset = wscript.createobject("ADODB.Recordset")
Dim cmdInsert 
set cmd = wscript.createobject("ADODB.Command")
set cmdInsert = wscript.createobject("ADODB.Command")
set cmdf98moque = wscript.createobject("ADODB.Command")
Set Oracon = wscript.CreateObject("ADODB.Connection")

Oracon.ConnectionString = "DSN=pjde;" & _
"User ID=jde;" & _
"Password=jde;"

Oracon.Open
Set cmdInsert.ActiveConnection = Oracon

ShowSubfolders FSO.GetFolder("Z:\MediaObjectsData\HTMLUploads"), 3 

Sub ShowSubFolders(Folder, Depth)
    If Depth > 0 then
        For Each Subfolder in Folder.SubFolders
            if(instr(subfolder,"RECYCLE")=0) then
              'Wscript.Echo Subfolder.Path
              ShowSubFolders Subfolder, Depth -1 
		Set colFiles = subFolder.Files
		For Each file In colFiles
    		  absPath = FSO.GetAbsolutePathName(file)
    		  'wscript.echo absPath
    		  cmdInsert.CommandText = "INSERT INTO PRODDTA.F00165SRM VALUES ('" & absPath & "')"
    		  Set recset = cmdInsert.Execute
		Next
            end if
        Next
    End if
End Sub

Note that 

Note that "Z:\MediaObjectsData\HTMLUploads" is the root of the copy of the media objects.  We had to use a series of sub folders because of the windoze problems with performance.

So then I had my oracle table with a FULL path to all of the media objects.

I followed the above with some SQL against the F00165 to update the location of many of the physical file references that are wrong.

update proddta.f00165
set gdgtfilenm = (select replace(mofilename, 'Z:\','\\jdedatastore\') 
    from  proddta.f00165srm 
    where substr(mofilename,instr(mofilename,'\',-1,1)+1, length(trim(mofilename))-(instr(mofilename,'\',-1,1))) = substr(gdgtfilenm,instr(gdgtfilenm,'\',-1,1)+1, length(trim(gdgtfilenm))-(instr(gdgtfilenm,'\',-1,1))) )
where  gdgtmotype = '5'
and gdgtfilenm like '\\%'
and length(trim(gdgtfilenm))-(instr(gdgtfilenm,'\',-1,1)) > 1
and exists
(select 1 from
proddta.f00165srm
where substr(mofilename,instr(mofilename,'\',-1,1)+1, length(trim(mofilename))-(instr(mofilename,'\',-1,1))) = substr(gdgtfilenm,instr(gdgtfilenm,'\',-1,1)+1, length(trim(gdgtfilenm))-(instr(gdgtfilenm,'\',-1,1))) 
and length(trim(mofilename))-(instr(mofilename,'\',-1,1)) > 1);