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);

SM console update goes pear shaped

Is is just me, or does this happen every time I update SM…  It goes bad…

I just chose to update my SM to 9.1.5.2 and of course, when I try to login, I get the above – GREAT!

Resource not found on this server.

This is where you need to trusty old recovery commands:

How to troubleshoot an unsuccessful Management Console update?

To troubleshoot an unsuccessful Management Console update:

1. Stop the Management Console service.
2. Start the Management Console service.
3. Open a command prompt and navigate to this directory:
      \jde_home\targets\home\_staging
       where jde_home is the install path of the Management Console.
4. Invoke the redeployManagementConsoleredeployManagementConsole.cmd recovery script.

The script takes one parameter, which is the password used to initially install the Management Console.
For example: redeployManagementConsoleredeployManagementConsole.cmd password

Upon successful execution of this script you can log on to the Management Console application.

And viola:

15/02/24 21:56:25 Notification ==>Initialize ManagementConsole_WAR ends...

15/02/24 21:56:25 Notification ==>Started application : ManagementConsole

15/02/24 21:56:25 Notification ==>Binding web application(s) to site default-web
-site begins...

15/02/24 21:56:25 Notification ==>Binding ManagementConsole_WAR web-module for a
pplication ManagementConsole to site default-web-site under context root /manage

15/02/24 21:56:28 Notification ==>Initializing Servlet: com.jdedwards.mgmt.web.C
onsoleInit for web application ManagementConsole_WAR

15/02/24 21:56:38 Notification ==>Initializing Servlet: oracle.cabo.servlet.UIXS
ervlet for web application ManagementConsole_WAR

15/02/24 21:56:39 Notification ==>Binding web application(s) to site default-web
-site ends...

15/02/24 21:56:39 Notification ==>Application Deployer for ManagementConsole COM
PLETES. Operation time: 50338 msecs

 

We are away again…

My changes to html4login are not coming through to the browser–weblogic cache / tmp

This is a more generic problem that changes that you are making to files in your weblogic user_project directory are not being reflected in the clients browser.  A simple change to a jsp for your EA is not being shown. 

You’ve restarted the app on a number of occasions and you are thinking that you are going mad.  Are you changing the wrong files?  What is going on.

c$\Oracle\Middleware\Oracle_Home\user_projects\domains\base_domain\servers\JDEDV_8080

Look for the directory equivilent above on your webs erver and delete the tmp and cache dirs while the WLS instance is down.  You’ll notice that the size of the tmp dir is about the same size as your EA – give or take.

Once this is done, start the application and your changes are going to be there

Check the unicode conversions and drop tables with function:

The important parts about this script is the pragma autonomous_transaction and also the use of execute immediate for the drop table.  This functional also has an additional parameter for determining whether to drop the table or not – allowing some prudent testing.

Remember the first parameter is the singlebyte tablename and the second is the unicode_tablename.

If the third parameter is a Y, and you have permission, the table will be dropped.

Another cool feature is that this information is returned to the query (and therefore spooled).

CREATE OR REPLACE FUNCTION test_unicode_conversion (singlebyte_tablename in VARCHAR, unicode_tablename IN VARCHAR, dropTable IN CHAR)
RETURN VARCHAR
IS
unicode_count integer;
singlebyte_count integer;
 unicode_cursor sys_refcursor; 
 singlebyte_cursor  sys_refcursor;
 pragma autonomous_transaction;
BEGIN
   dbms_output.put_line('Starting...');
  open unicode_cursor for 'select count(1) from ' || unicode_tablename  ;
  fetch unicode_cursor into unicode_count;
  open singlebyte_cursor for 'select count(1) from ' || singlebyte_tablename ;
  fetch singlebyte_cursor into singlebyte_count;
  if unicode_count <> singlebyte_count then
    dbms_output.put_line('cannot drop table, transation not complete');
    return 'Row counts do not match for' || unicode_tablename || ' and ' || singlebyte_tablename ;
  else
    dbms_output.put_line('Drop the temp table, all good');
    if droptable = 'Y' then
      execute immediate 'drop table ' || singlebyte_tablename ;
      return 'Row counts MATCH for ' || unicode_tablename || ' and DROPPED ' || singlebyte_tablename ;
    end if;
    return 'Row counts MATCH for' || unicode_tablename || ' and ' || singlebyte_tablename ;
  end if;
  close unicode_cursor;
  close singlebyte_cursor;
END ;

Called with

select jde.test_unicode_conversion('TESTDTA.F4009_NONUNI', 'TESTDTA.F4009', 'N') from dual ;

writing and running oracle function that uses a table name as a parameter in a cursor

I need to drop tables that have been verified as unicode converted, I’m no expert at PL/SQL, so I decided to make a function that was generic.

What I decide to do is if the NONUNI = normal row count, my assumption is that everything has worked like a peach, this is going to help me do this.

Note that the drop has not been coded, because I’m a little worried…

 

CREATE OR REPLACE FUNCTION test_unicode_conversion (singlebyte_tablename in VARCHAR, unicode_tablename IN VARCHAR)
RETURN VARCHAR
IS
unicode_count integer;
singlebyte_count integer;
 unicode_cursor sys_refcursor; 
 singlebyte_cursor  sys_refcursor;
BEGIN
  open unicode_cursor for 'select count(1) from ' || unicode_tablename  ;
  fetch unicode_cursor into unicode_count;
  open singlebyte_cursor for 'select count(1) from ' || singlebyte_tablename ;
  fetch singlebyte_cursor into singlebyte_count;
  if unicode_count <> singlebyte_count then
    dbms_output.put_line('cannot drop table, transation not complete');
    return 'Row counts do not match for' || unicode_tablename || ' and ' || singlebyte_tablename ;
  else
    dbms_output.put_line('Drop the temp table, all good');
    --drop table PRODDTA.F55B30 ;
    return 'Row counts MATCH for' || unicode_tablename || ' and ' || singlebyte_tablename ;
  end if;
  close unicode_cursor;
  close singlebyte_cursor;
END ;

select jde.test_unicode_conversion('TESTDTA.F0101', 'TESTDTA.F0101') from dual ;

 

And calling this ripper:

select jde.test_unicode_conversion('TESTDTA.F0101', 'TESTDTA.F0101') from dual ;

 

Which returns:

Row counts MATCH forTESTDTA.F0101 and TESTDTA.F0101

That is pretty cool.  Note that there are a number of specifics (syntax is a killer!)

9.1.5–What’s new (old for some)

Feature Title	Description
Platform Product Certifications for Tools 9.1.5	This release also includes a set of platform certifications for components that can be used in conjunction with JD Edwards EnterpriseOne. By updating the tools release software the JD Edwards EnterpriseOne applications are enabled to run on these updated components: - Oracle Database 12.1.0.2, including the Database In-Memory option - Oracle Database 12.1.x on IBM AIX and HP-UX - Internet Explorer 11 in native mode, rather than in compatibility mode - IBM DB2 on IBM AIX and Microsoft Windows - IBM i 7.2 - Oracle WebLogic Server 12.1.3
One View Usability Features	This feature improves the user interface for One View Reports by showing the query criteria that is used to get the report results. It also provides the ability to issue a warning if the query results exceed the number of records shown on the report when configuration options have been set that limit the number of rows displayed. In addition, the drill-back link includes a new parameter, generated by the drill-back composer. This parameter automatically executes a Find in the called application.
Introduction and Highlights	Bridge release for tools.
Outbound BSSV Clustering	This feature enables configuring a clustered Business Services Server instance for JD Edwards EnterpriseOne outbound web services. The clustering feature enables scalability, load balancing, and high availability for JD Edwards outbound web services by providing multiple ports to receive JDENet messages from the Enterprise Server, and the Enterprise Server can send JDENet messages to multiple clustered Business Services Server ports.
JD Edwards EnterpriseOne Mobile Enterprise Application Development	Starting with EnterpriseOne Tools 9.1.5.2, JD Edwards provides additional capabilities for developing and customizing JD Edwards EnterpriseOne mobile enterprise applications which are built using JD Edwards EnterpriseOne Mobile Framework and Oracle Mobile Application Framework technologies. New capabilities include a method that enables the support of URL type media object attachments in a mobile application, and an additional form service request type that enables a mobile application to perform a query on a back-end EnterpriseOne application.
Recent Breadcrumbs	It is common practice to open several applications within the same menu structure, and Breadcrumbs are a simple mechanism that enables you to keep track of the menus you have used to navigate to your current application. Breadcrumbs provide a drop-down list that represents a history of menus that you have previously accessed. This breadcrumb history provides a very simple and efficient interface to identify menus you have accessed in the past and to easily launch applications that you use repetitively through the day..
Server Only Packages	This feature removes the requirement to build a client package before building server packages, and by doing so, reduces the time required to build and deploy packages to enterprise (and HTML) servers.
Email and Calendar Integration	JD Edwards EnterpriseOne enables users to collaborate with other users by using the Send Email and Send Meeting Invite options.You can access this feature from the Collaborate submenu of the Tools menu item on the applications toolbar. You can send emails messages and meeting invites directly from the Collaborate tab that is part of AN8 based Hover Forms.
Management of Mobile Applications – Daily Monitoring	Server Manager supports the management, tracking, monitoring, logging, and clustering of the Application Interface Services Server.
Alta Adoption	JD Edwards has adopted a new style sheet called Alta for all of its interactive applications. The Alta style provides the most modern User Interface design and also provides a consistent look with JD Edwards Mobile applications along with other Oracle ERP and Cloud product offerings.
EnterpriseOne Page Gamification Support	Gamification is a concept that is gaining traction in Enterprise Software. The concept is to allow companies to define games that motivate users to exhibit behaviors that benefit productivity and overall corporate profitability.
Simplified Navigation	For JD Edwards EntepriseOne customers who have users that require simple and limited functionality, the Simplified Navigation option is a feature that provides new and casual users with quick access to self-service tasks, or professional users with access to quick-entry, high-volume tasks.

sqlplus with if conditional statement and server output… simple…

 

This is pretty simple, getting the script / server to return some output to the output of SQLPlus.  I saw a lot of articles on this, but non complete, i.e. – did not have the SET SERVEROUTPUT line, therefore, nothing was being printed.

I’m doing unicode conversions manually and need to drop the temp files once I’ve confirmed that all of the data rows have been converted.  Therefore I get a couple of counts (from the nonuni and singlebyte) versions of the tables.  If the counts are the same, then I drop the “_NONUNI” – simple!

This script is just for reference and does not have the drop command.

SET SERVEROUTPUT ON FORMAT WORD_WRAPPED
declare
unicode_count integer;
singlebyte_count integer;
cursor unicode_cursor is select count(1) from TESTDTA.F0101;
cursor singlebyte_cursor is select count(1) from TESTDTA.F0101_NONUNI;

begin

  open unicode_cursor;
  fetch unicode_cursor into unicode_count;
  open singlebyte_cursor;
  fetch singlebyte_cursor into singlebyte_count;
  if unicode_count <> singlebyte_count then
    dbms_output.put_line('cannot drop table, transation not complete');
  else
    dbms_output.put_line('Drop the temp table, all good');
  end if;
  close unicode_cursor;
  close singlebyte_cursor;

end;
/
quit ;
/