JD Edwards, media objects analysis and more

Forgotten media objects is something that I’ve been involved in a number of times.  Leaving them on old deployment servers, not copying the directory for an upgrade – the list goes on.  Poor old media objects are often lost.

Quite often they are pretty important too, like an original PO that you need for payment – etc etc.  Quality results for some materials that have gone wrong.

I was recently asked to audit some media objects at a client and I thought I’d articulate my method and findings (generically and anonymously).

First and foremost, this was part of an upgrade, so feel free to get rid of MO’s that you are not going to need.  This leaves you with TEXT, IMAGE, OLE, LINK & SHORTCUT.

Say goodbye to shortcut items, they generally reference something on a persons desktop – never great.

OLE will point to where the OLE queue is defined in the F98MOQUE – that one is fairly reliable – unless you’ve moved machines or forgotten to change this link.

Then you have TEXT, very easy – it’s all in the BLOB!

IMAGE is kinda like OLE, it uses F98MOQUE to find the dir where they are stored Stored POs

Finally Link is a generic link, not using F98MOQUE, these can be anywhere!  (\\machine\location\text\HQ\document.pdf )

So, how can you determine what is still about and what is gone?  I have a vbscript (i’ve seen some powershell stuff online too).

[sourcecode language='vb'  padlinenumbers='false' light='false' firstline='1' highlight='1']

  fileSuffix=Left(FormatDateTime(dateStamp, 1),6) & day(dateStamp) & monthName(month(dateStamp))  & hour(dateStamp)
  Const ForAppending = 8
  logfile="d:\myriad\" & fileSuffix & ".txt"
  Set objFSO = CreateObject("Scripting.FileSystemObject")
  set objTextFile = objFSO.OpenTextFile(logfile, ForAppending, True)
DIM fso    
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 cmd 
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=e1prod;" & _
                          "User ID=jde;" & _
                          "Password=jde;"

Oracon.Open
Set cmd.ActiveConnection = Oracon
Set cmdInsert.ActiveConnection = Oracon
cmd.CommandText = "Select gdobnm, gdtxky, gdgtfilenm, gdgtmotype, gdgtitnm, gdqunam from proddta.F00165_91 where gdgtmotype in ('2', '5','1')"
'cmd.CommandText = "Select gdobnm, gdtxky, gdgtfilenm, gdgtmotype, gdgtitnm, gdqunam from proddta.F00165_91 where gdgtmotype in ('1')"
Set recset = cmd.Execute

'load the folder map array
Dim moqueue 
Set moqueue = CreateObject("scripting.dictionary") 
Set cmdf98moque.ActiveConnection = Oracon
cmdf98moque.CommandText = "Select omqunam, omqupath from sys7333.F98MOQUE"
set f98moquerecset=cmdf98moque.execute
while (f98moquerecset.EOF=false)
  moqueue(trim(f98moquerecset("OMQUNAM")))=trim(f98moquerecset("OMQUPATH"))
  f98moquerecset.movenext
wend
wscript.echo moqueue("AA Iplex Mud Maps")
i=0
while (recset.EOF=false)
    'Generally this would be in a function, but it needs to be fast!!
    'NOte that type 2 and 1 cld be same function, as they are based on queues
    'but i'm doing an upgrade, and it's complicated...  this hard code of DIR
    'does not really need to be there
    if recset("GDGTMOTYPE") = "2" then
       filenametotest = "D:\MEDIAOBJ\Oleque\" & trim(recset("GDGTFILENM"))
    elseif recset("GDGTMOTYPE") = "1" then
       filenametotest = moqueue(trim(recset("GDQUNAM"))) & "\" & trim(recset("GDGTFILENM"))
       'wscript.echo "queue (" & trim(recset("GDQUNAM")) & ") path (" & moqueue(trim(recset("GDQUNAM"))) & ")"
    else
       filenametotest = trim(recset("GDGTFILENM"))
    end if
	If (fso.FileExists(filenametotest)) Then
	  'WScript.Echo("File exists! TP(" & recset("GDGTMOTYPE") &"): " & filenametotest)
	  fileexistsstatus="YES"
	Else
	  fileexistsstatus="NO"
	  'WScript.Echo("File does not exist! TP(" & recset("GDGTMOTYPE") &"): " & filenametotest)
	End If
	cmdInsert.CommandTExt = "INSERT INTO PRODDTA.f00165SRM (GDOBNM, GDTXKY, FILEEXISTS) values ('" & _
	recset("GDOBNM") & "', '" & _
	recset("GDTXKY") & "', '" & _
	fileexistsstatus & "')"
	'wscript.echo "SQL:" & cmdInsert.CommandTExt
	Set insertrecset = cmdInsert.Execute

  recset.MoveNext
  'wscript.echo recset("gdgtfilenm")
  'tuple=" "
wend


set recset = nothing
oracon.close
set oracon = nothing
[/sourcecode]

Remember to create F00165SRM

[sourcecode language='sql' ]
create table proddta.f00165srm as select GDOBNM, GDTXKY, '    ' AS FILEEXISTS from proddta.f00165 where 1=0 ;
[/sourcecode]

This will then tell you which MO’s exist and which don’t.  It loads table F00165SRM with the YES or NO whether the physical file exists.  This will let you know how much trouble you are in if you think you are missing objects.

Note that this script demonstrates some cool things:

• reading and writing to oracle database from vbscript


• using a scripting.dictionary object to reference an array using strings as the index, wow – that was cool


• other random stuff about ADODB, connect strings and other things.