Running VBScript in 32-bit mode on a 64-bit Windows machine

I have a legacy VBScript program that has been recently been migrated to a new 64-bit Windows server by a system administrator due to a policy set by a higher power. By default, VBScript executed in 64-bit Windows automatically runs as a 64-bit program. Because I have reasons to have this program run in 32-bit mode, I modified my batch file to execute my program in a different way.

This is the BEFORE picture. When this batch file is run in 64-bit Windows, it will execute in 64-bit mode.

time /t
cscript ThisIsMyVbscriptProgram.vbs
time /t

Below is the AFTER picture. Note that I am passing my script into a new command prompt environment in the Windows SYSWOW64 folder; this new environment is strictly in 32-bit mode.

time /t
%WINDIR%SYSWOW64cmd.exe /c cscript ThisIsMyVbscriptProgram.vbs
time /t

As a side note: WOW stands for “Windows on Windows”.

Using DBA_SOURCE to query package source code

Recently I had the need to find out exactly what existing PL/SQL logic was touching a certain field that seems to update by itself, wiping out important data. Below was the quick query I put together, using the DBA_SOURCE view, to hunt down the culprit.

select name as package_name, line, text
from dba_source
where owner='MY_SCHEMA'
and type='PACKAGE BODY'
and (
  upper(text) like '%MY_TABLE_NAME%FIELD_NAME%'
  or
  upper(text) like '%FIELD_NAME%MY_TABLE_NAME%'
)
order by name, line;

Note that I could have searched for “and upper(text) like ‘%UPDATE%MY_TABLE_NAME%FIELD_NAME%'” rather than having two conditions, but I wanted to err on the side of caution, thus my wish to pull more data out to be safe.

Using PL/SQL to call IFS APIs

IFS is an ERP system built by the Swedish software firm Industrial and Financial Systems AB. It exposes most of its functionality for automation, customization, etc. Below is an example of how we can use a quick script (to be executed by tools such as SQL*Plus) to automate the batch creation of detail lines in a Customer Order. Many of the other exposed APIs offered by IFS can be called in a similar manner.

declare
	info_	varchar2(4000) := '';
	attr_	varchar2(4000) := '';
	objid_	varchar2(2000) := '';
	objversion_	varchar2(2000) := '';

	cursor recs_ is
	select order_no, cust_id, part_no, qty, unit_cost 
	from my_tmp_worklist_tab;
begin
	for rec_ in recs_ loop
		Client_Sys.Clear_Attr(attr_);
		info_ := '';
		objid_ := '';
		objversion_ := '';

		-- Add attributes to the Customer Order detail line
		Client_Sys.Add_To_Attr('ORDER_NO', rec_.order_no, attr_);
		Client_Sys.Add_To_Attr('DELIVER_TO_CUSTOMER_NO', rec_.cust_id, attr_);
		Client_Sys.Add_To_Attr('CATALOG_NO', rec_.part_no, attr_);
		Client_Sys.Add_To_Attr('PART_NO', rec_.part_no, attr_);
		Client_Sys.Add_To_Attr('BUY_QTY_DUE', rec_.qty, attr_);
		Client_Sys.Add_To_Attr('DESIRED_QTY', rec_.qty, attr_);
		Client_Sys.Add_To_Attr('REVISED_QTY_DUE', rec_.qty, attr_);
		Client_Sys.Add_To_Attr('COST', rec_.unit_cost, attr_);
		Client_Sys.Add_To_Attr('PLANNED_DELIVERY_DATE', sysdate+7, attr_);
		-- ... There may be other fields you may wish to populate...

		-- Example of how to update an already-established attribute
		if (rec_.unit_cost = 0) then
			Client_Sys.Set_Item_Value('COST', '0.01', attr_);
		end if;
		
		begin
			Customer_Order_line_API.New__(info_, objid_, objversion_, attr_, 'DO');
		exception
			when other then
			info_ := sqlerrm;
			dbms_output.put_line('Cust Order ' || rec_.order_no || ' error: ' || info_);
		end;

		commit;    
   end loop;   
end;

Using PL/SQL to return a list of values

We can use an Oracle PL/SQL function to retrieve a list of values from the database. In this article, we will look at two sample methods, one of which returns a character string (ie. varchar2) while the other returns a record set (ie. sys_refcursor). Our application will determine which method is best in our implementation.

In the first example below, we use a for-loop to iterate through a select statement. In each iteration, we concatenate the new data into a varchar2 variable. Finally, at the end of the function, we return the said variable.

create or replace function get_city_list(in_country_ in varchar2)
return varchar2
as
ret_ varchar2(1000);
begin
	for rec in(select city from cities where country=in_country_) loop
		ret_ := ret_ || rec.city || ',';
	end loop;
	return ret_;
end get_city_list;

Alternatively, we can have the function return us a record set. To do so, we declare a sys_refcursor variable, and then in the body of the function we assign a SQL statement for it.

create or replace function get_city_list(in_country_ in varchar2)
return sys_refcursor
as
	ret_ sys_refcursor
begin
	open ret_ for select city from cities where country=in_country_;
	return ret_;
end get_city_list;

Connecting from PHP to an Oracle database

This article assumes that your “tnsnames.ora” file has already been configured. For the propose of this article, we will assume that the TNS entry we are attempting to connect to is called “oradb”.

// Database connection
$conn = oci_connect("scott", "tiger", "oradb");
if (!$conn) {
   $m = oci_error();
   trigger_error(htmlentities($m["message"], ENT_QUOTES), E_USER_ERROR);
}

// Perform a sample query
$sql = oci_parse($conn, "select user from dual");
oci_execute($sql);
 
while (($row = oci_fetch_array($sql))){
	print($row["user"]);
}

// Close the connection
oci_free_statement($sql);
oci_close($conn);

Calculating time difference in Excel

For the purpose of the demonstration, in an Excel spreadsheet, let’s say we have a date-time value of “9/13/14 3:02 PM” in cell A2 and “9/13/14 5:43 PM” in cell B2. Even though the screen displays them in a format friendly to me at my location in the United States, in the background Excel actually treats them as a decimal number that is universal regardless of the display formatting. In this case the two values are actually 41895.63
and 41895.74, respectively; the details on this number is outside the scope of this particular article. Getting back on track, in order to calculate the number of days, hours, or minutes that has elapsed between these two values, we can simply use these formulas:

Difference in days:    =(B2-A2)
Difference in hours:   =(B2-A2)*24
Difference in seconds: =(B2-A2)*24*60

Python ucs2 vs ucs4 installation

For forcing ucs4 when installing Python:

./configure --enable-unicode=ucs4
make
make install

For forcing ucs2 when installing Python:

./configure --enable-unicode=ucs2
make
make install

Once you have it installed, a ucs4 version will produce the following:

bash$ python
Python 2.5.2 (r252:60911, Mar  4 2008, 10:58:18)
[GCC 3.4.5] on linux
>>>import sys
>>> print sys.maxunicode
1114111

And, a ucs2 version will produce the following:

>>>import sys
>>> print sys.maxunicode
65535

Batch file to create regular backups with cleanup

I run a version of the Windows batch script below to create a regular backup of one of the folders that contains my daily work; for this illustration, we will call the source folder c:source. Every night, this batch file is triggered by Windows Scheduled Tasks to create a folder in my backup drive with the timestamp in the folder name; let us call this target folder D:target. Before creating this daily backup, the script also checks to see how many backup sets are already existing in the backup folder, and if above the threshhold, the oldest one(s) are deleted; this feature helps making sure the space usage of redundant backup sets do not get out of control. When scheduling this job with Windows Scheduled Tasks, you may wish to output to a file as well to create a log file of what old folders were removed and what files were copied (eg. “c:scriptsbackup.bat > logfile.log”).

@echo off

:: Start Variables

set NumberToKeep=5
set BackupCmd=xcopy /s /c /d /e /h /i /r /y

set BackupSource=C:\source\
set BackupTarget=D:\target\

:: End Variables




:: Actual Script Starts Here!
echo+
echo STARTING BACKUP
echo %date% %time%

:: 1. Delete older backup set(s) beyond the NumberToKeep
for /F "tokens=* skip=%NumberToKeep%" %%I In ('dir "%BackupTarget%" /AD /B /O-D /TW') do (
	echo+
	echo DELETING OLD BACKUP SET %BackupTarget%%%~I
	rd /s /q "%BackupTarget%%%~I"
)

:: 2. Create new backup set
set bkuphour=%time:~0,2%
if "%bkuphour:~0,1%"==" " set bkuphour=0%time:~1,1%
set bkupfldr=%date:~10,4%_%date:~4,2%_%date:~7,2%_%bkuphour%_%time:~3,2%

echo+
echo CREATING FOLDER %BackupTarget%%bkupfldr%\
if not exist "%BackupTarget%%bkupfldr%\" mkdir "%BackupTarget%%bkupfldr%\"

echo+
echo BACKING UP FILES...
%BackupCmd% "%BackupSource%*.*" "%BackupTarget%%bkupfldr%\"

echo+
echo BACKUP COMPLETED!
echo %date% %time%

Hope you will find this useful!

Sorting arrays in Javascript

Let’s suppose we have a simple array containing 5 strings. The .sort() method sorts them in ascending order and the .reverse() method sorts them in descending order, as demonstrated below.

var simpleArray1 = ["Paul", "Peter", "Mary"];

// .sort() will sort the array to become "Mary", "Paul", and then "Peter".
simpleArray1.sort();

// .reverse() will sort the array to become "Peter", "Paul", and then "Mary".
simpleArray1.reverse();

In the case of numbers, we should be aware that they are sorted in a similar fashion as strings. In the following example, 100 appears before 32 when using .sort() because 1 comes before 3.

var simpleArray2 = [100, 999, 32];

// .sort() will sort the array to become 100, 32, and then 999.
simpleArray2.sort();

// .reverse() will sort the array to become 999, 32, and then 100.
simpleArray2.reverse();

To sort an array of numbers by their numeric values, we should pass in a function into the .sort() method as follows.

// .sort(function(a, b) {return a - b}) will sort the array to become 32, 100, and then 999.
simpleArray2.sort(function(a, b) {return a - b});

// .sort(function(a, b) {return b - a}) will sort the array to become 999, 100, and then 32.
simpleArray2.sort(function(a, b) {return b - a});

We can sort an array of objects, ie. each element of the array has its own set of properties, on any of the properties as well. In the following example, each object in the array has a property called “id”, which we are using to sort.

// Ascending order
complexArray.sort(
	function(a, b) {
		return (a.id > b.id ? 1 : (a.id < b.id ? -1 : 0));
	} 
);

// Descending order
complexArray.sort(
	function(a, b) {
		return (a.id > b.id ? -1 : (a.id < b.id ? 1 : 0));
	} 
);