Author: C. Peter Chen

Founder of Lava Development, LLC.
Posted on

Updating Oracle table with data from multiple tables using subquery

I ran into a situation today when I need to update 10,000 records in a master table totaling 1,000,000 rows, and the value that I’m going to use comes from the accounting department. Let us set up the scenario below.

Master Table
Job Number * Job Type Invoice Amount
101 Installation 1000
102 Installation 1000
103 Maintenance 500
104 Repair 400
105 Installation 3000
Data From Accounting
Job Number * Updated Invoice Amount
103 400
104 600

* Primary key

In sum, accounting wants to update job 103’s invoice amount to $400 and job 104’s invoice amount to $600, while leaving all other amounts the same. In this scenario, accounting only sent me two records, but in the real life situation I ran into, it was over 10,000 records. Also, this scenario’s master table has only 5 records; my real master table has close to 1,000,000 records. Thus, we need an update statement that is efficient.

Because Oracle does not allow multiple tables within the same update statement, I know at least one subquery will be needed. My first attempt, which was incorrect, was this:

-- Incorrect example!
update master_table m
set m.invoice_amount=(
	select a.updated_invoice_amount from data_from_accounting
	where m.job_number=a.job_number
);

The problem with this update statement was that I realized it would update all 5 records instead of just the 2 records accounting wanted to update; this would result in 103 and 104 being updated to the updated amounts, but all other amounts wiped out to null value. To remedy that, we could throw in a where clause to the update statement, as below:

-- Better example, but still inefficient
update master_table m
set m.invoice_amount=(
	select a.updated_invoice_amount from data_from_accounting a
	where m.job_number=a.job_number
)
where m.job_number in(
	select a2.job_number from data_from_accounting a2
	where m.job_number=a2.job_number
);

This would correctly update the records I wanted to update while leaving the others alone, but this query is a little inefficient as it needs to go through the data from accounting twice.

Finally, I came up with a solution that works very efficiently, even with my large 1,000,000-record table.

-- Best practice
update (
	select m.invoice_amount, a.updated_invoice_amount
	from master_table m, data_from_accounting a
	where m.job_number=a.job_number
)
set m.invoice_amount=a.updated_invoice_amount;

Note that the job number field in both tables in this example scenario are both primary keys. In order for the “best practice” update statement to work, they must be either primary keys or unique indexes, otherwise the query will fail with the error message “ORA-01779: Cannot modify a column which maps to a non key-preserved table”. As an aside, to create primary key or unique indexes, follow the examples below.

-- Primary key
alter table master_table add (
  constraint pk_master_table primary key (job_number)
);

-- Unique index
create unique index idx_data_from_accounting on data_from_accounting (job_number);
Posted on

Javascript pause function

The sample Javascript pause() function below performs a pause for X milliseconds. During the time specified, the browser would appear “frozen” to the user.

Quick reminder: 1000 milliseconds = 1 second, 60000 milliseconds = 1 minute

function pause(milliseconds) {
	var dt = new Date();
	while ((new Date()) - dt <= milliseconds) { /* Do nothing */ }
}

Below is a live example, followed by the HTML/Javascript code used.



<script type="text/javascript">
function pause(milliseconds) {
	var dt = new Date();
	while ((new Date()) - dt <= milliseconds) { /* Do nothing */ }
}

function preStuff() {
	alert('This is before the 3-second pause');
}

function postStuff() {
	alert('This is after the pause; it should have been paused for 3 seconds');
}

</script>

<input type="button" onclick="javascript: preStuff(); pause(3000); postStuff();" value="Do a 3-second pause">

If you are looking for a way to simply delay execution of code without "freezing" the browser during the wait time, try the Javascript delay() method.

Posted on

Using Javascript to manipulate HTML select-dropdown boxes

First, let us establish a sample select object as below. The Javascript object “objSelect” will also be established to refer to this HTML object.


<select id="colors">
	<option value="r">Red</option>
	<option value="g">Green</option>
	<option value="b">Blue</option>
</select>

<script type="text/javascript">
var objColors = document.getElementById("colors");
</script>

The following Javascript code can be used to display which option has been selected by the user. If you choose “Blue”, for example, you will get alert dialogues “b”, “Blue”, and then “2”.

alert(objColors.value); // Display the value; in this example, red is "r", green is "g", and blue is "b"
alert(objColors.options[objColors.selectedIndex].text); // Display the text; in this example, "red", "green", and "blue"
alert(objColors.selectedIndex); // Display the index number; in this example, red is 0, green is 1, and blue is 2

The following Javascript code provides you the means to add a new option to the HTML select object. “Yellow” is going to be added to the end of the list, and “White” is going to be added as the third choice.

var newOption1 = document.createElement('option');
newOption1.text = "Yellow";
newOption1.value = "y";

var newOption2 = document.createElement('option');
newOption2.text = "White";
newOption2.value = "w";

try {
	// For standard browsers
	objColors.add(newOption1, null);
}
catch (ex) {
	// For Microsoft Internet Explorer and other non-standard browsers.
	objColors.add(newOption1);
}

try {
	// For standard browsers
	objColors.add(newOption2, objColors.options[2]);
}
catch (ex) {
	// For Microsoft Internet Explorer and other non-standard browsers.
	objColors.add(newOption2, 1);
}

The code below removes an option. The first example removes index #1, or the second option. The second example removes whatever the last option is.

objColors.remove(1); // Removes index #1, or "Green" in our original example.
objColors.remove(objColors.length-1); // Removes whatever the last option is.

Finally, the code below selects a particular option.

< pre class="code">
objColors.options[1].selected = true; // Selects the second option, or “Green” in our original example

You may see the running examples below.





Complete code to the running examples is shown below.


<select id="colors">
	<option value="r">Red</option>
	<option value="g">Green</option>
	<option value="b">Blue</option>
</select><br /><br />
<input type="button" value="check current choice" onclick="check();"><br />
<input type="button" value="add" onclick="add();"><br />
<input type="button" value="remove second option" onclick="remove();"><br />
<input type="button" value="remove last option" onclick="removeLast();"><br />
<input type="button" value="select" onclick="selected();"><br />

<script type="text/javascript">
var objColors = document.getElementById("colors");


function check() {
	alert(objColors.value); // Display the value; in this example, red is "r", green is "g", and blue is "b"
	alert(objColors.options[objColors.selectedIndex].text); // Display the text; in this example, "red", "green", and "blue"
	alert(objColors.selectedIndex); // Display the index number; in this example, red is 0, green is 1, and blue is 2

}

function add() {
	var newOption1 = document.createElement('option');
	newOption1.text = "Yellow";
	newOption1.value = "y";

	var newOption2 = document.createElement('option');
	newOption2.text = "White";
	newOption2.value = "w";

	try {
		// For standard browsers
		objColors.add(newOption1, null);
	}
	catch (ex) {
		// For Microsoft Internet Explorer and other non-standard browsers.
		objColors.add(newOption1);
	}

	try {
		// For standard browsers
		objColors.add(newOption2, objColors.options[2]);
	}
	catch (ex) {
		// For Microsoft Internet Explorer and other non-standard browsers.
		objColors.add(newOption2, 2);
	}
}

function remove() {
	objColors.remove(1); // Removes index #1, or "Green" in our original example.
}

function removeLast() {
	objColors.remove(objColors.length-1); // Removes whatever the last option is.
}

function selected() {
	objColors.options[1].selected = true;
}
</script>
Posted on

HTML meta tags guide with focus on SEO

Meta tags are HTML elements used to provide some metadata (data about data) about a web page. They are overwhelmingly used for search engine crawlers as opposed to for users, thus they are often viewed as vital component of search engine optimization (SEO). Meta tags should reside in the HEAD portion of your HTML code. Some of the meta tags, particularly those important in SEO work.

Keywords

This is one of the most heavily used meta tag for SEO purposes. You should provide a few keywords relevant to the particular page, and only the relevant ones only. You should not include keywords only because they are popular, as the long term potential negative effects of keyword stuffing far outweighs any short term benefits you may gain.


<meta name="keywords" content="cooking, boiling, egg, recipe" />

Robots

As the name suggests, this tag is not for humans. Instead, it provides some instructions for search engine crawlers. Some possible values are as follows.

  • index: Recommends the search engine to include this page from its index. This can be considered a little bit useless, as normally the crawler is out to index your page anyway. It does not hurt to include it, however.
  • noindex: Recommend the search engine to exclude this page from its index.
  • follow: Recommend the search engine crawler to follow all links found on this page. Like index, this can also be considered a little bit useless, as it is a normal function of a crawler regardless of this instruction.
  • nofollow: Recommends the search engine crawler to not follow any links found on this page.
  • noarchive: Recommends the search engine to not cache a copy of the page in its archive.
  • nosnippet: Recommends the search engine to not cache a copy of the page in its archive (this portion is same as noarchive), and also recommends the search engine to not display any snippets of the page’s content on the search results page. This may be useful if you are under strict intellectual property limitations that none of the page’s content can be displayed anywhere other than your site.
  • noodp: Recommends the search engine to not place the Open Directory Project description of your page next to the search results.
  • none: This is equivalent to “noindex, nofollow”.

Should you wish to use more than one value, use a comma to separate them. Spaces are ignored, so they make no difference. Also, the values found in the robots tag are processed without case sensitivity (eg. “noindex” and “NoIndex” are considered the same). Some examples are listed below.


<meta name="robots" content="none" />
<meta name="robots" content="index, nofollow" />
<meta name="robots" content="nofollow, nosnippet, noodp" />

Revisit-After

This allows you to recommend the crawler to return to the same page for another round of indexing. Like most other tags, this is just a recommendation to the search engines and their crawlers, so you should not expect this to be followed exactly. Along the same lines, you should also note that most major search engines do not obey this meta tag at all; they will re-index your pages when they deem necessary.


<meta name="revisit-after" content="15 days" />

Description

The description meta tag provides a space for you to provide a very brief description of the current page. This is not a recommendation to the crawlers like the ones mentioned thus far. This information is used by some search engines to display as the brief description on the search results page, so it is important for you to provide something concise and to-the-point so that users will know whether your page will give them the information they want. If you are unsure what to put in here, just put in whatever you have for the title tag (not discussed in this article) for that page, as shown in the example below.


<meta name="description" content="How to boil an egg" />

Content-Type

This informational entry describes the content of the page. There are too many possibilities to list all possible values for content-type, but the examples below should illustrate the usage.

Example 1: Notes the page is a HTML page, using the iso-8859-1 character set.


<meta http-equiv=Content-Type content="text/html; charset=iso-8859-1" />

Example 2: Notes the page is actually a .xls file (Microsoft Excel spreadsheet), so the user should use the appropriate program to handle it instead of trying to interpret it as HTML.


<meta http-equiv=Content-Type content="application/xls" />

Content-Language

This provides information on the language your page is written in. The following three examples are for American English, Traditional Chinese, and German, respectively.


<meta http-equiv="content-language" content="en-us" />
<meta http-equiv="content-language" content="zh-tw" />
<meta http-equiv="content-language" content="de" />

The two meta tags below are not really related to SEO, but it might be useful to know them in any case.

Generator

On the surface, it tells of the software package used to generate the page, particularly if the software package uses any proprietary stuff in the code. However, since HTML code is mostly standardized, this tag is not really useful even though it is still very popular as many software packages still make use of them. It may be cynical to say this tag is no more than advertisement for the software packages.


<meta name="generator" content="HTML Software v0.1.2.3" />

Pragma

The only option that I know of for the pragma is no-cache, which recommends the visitors’ browsers to not cache this page locally. This is useful if you have a page with content changes very frequently (ie. more than once for each visitor within the same visit) while the URL does not change. Try this meta tag if your users complain that they are always seeing stale data but you cannot reproduce the experience on other machines.


<meta http-equiv="pragma" content="no-cache" />

Additional Information

Author, contact_addr, and copyright meta tags allow you to provide some information about the page. They are purely informational and do not provide any recommendations to the crawlers.


<meta name="copyright" content="Copyright © 2008 C. Peter Chen" />
<meta name="author" content="C. Peter Chen" />
<meta name="contact_addr" content="email@me.here" />
Posted on

Creating an Oracle table pre-filled with random values

The code makes use of Oracle’s dbms_random package and some very light-weight PL/SQL. You may adjust the number of fields, field data types, total number of records, etc. to suit your needs.

create table schema.test_data (
  txt  varchar2(10), 
  numb  number(10), 
  dt  date
);
begin
  for i in 1..10000 loop
    insert into schema.test_data values (dbms_random.string('X',10), dbms_random.value(0,20), to_date(trunc(dbms_random.value(2422599, 2457085)),'j'));
  end loop;
  commit;
end;

When we are done, we will see that the table contains 10,000 records as we specified in the sample code above.

select count(*) from schema.test_data;

  COUNT(*)
----------
     10000

Below is what the data may look like.

select txt, numb, to_char(dt,'mm/dd/yyyy') as dt from schema.test_data where rownum<10;

TXT              NUMB DT
---------- ---------- ----------
35W6DQ986O          7 11/26/1943
8NOOSRH6R2          8 01/18/1993
7HPKA10GKQ         16 10/07/2012
I90Z9YVWHW          2 11/01/1939
WNNW1M7BNM         15 10/27/1982
R9OQF67QWP         13 08/28/1926
PD39YGY35D         10 02/04/1952
N8R8DKMAIO         15 07/06/1986
43MELQ9M0Q          3 06/19/1921
Posted on

Generating random numbers, characters, and dates with Oracle SQL

Please review the following series of samples for how the dbms_random package works.

Random number:

select dbms_random.random as rand_numb from dual;

 RAND_NUMB
----------
-1.642E+09

Random number between 0 and 1:

select dbms_random.value as rand_numb_btwn_0_1 from dual;

RAND_NUMB_BTWN_0_1
------------------
        .950707301

Random number between x and y:

select dbms_random.value(10,20) as rand_numb_btwn_10_20 from dual;

RAND_NUMB_BTWN_10_20
--------------------
          14.6714295

Random upper case letters:

select dbms_random.string('U',10) as rand_upper from dual;

RAND_UPPER
----------
WVWZVYKNKP

Random lower case letters:

select dbms_random.string('L',10) as rand_lower from dual;

RAND_UPPER
----------
jdoyttefuw

Random mixed case letters:

select dbms_random.string('A',10) as rand_mixed from dual;

RAND_MIXED
----------
UBagEltDAr

Random upper case alphanumeric characters:

select dbms_random.string('X',10) as rand_upper_alphanumeric from dual;

RAND_UPPER_ALPHANUMERIC
-----------------------
JSANG89O8S

Random characters:

select dbms_random.string('P',10) as rand_chars from dual;

RAND_CHARS
----------
}![ NSOoo1

Random date between 1 Oct 1920 and 15 Mar 2015:
Note: The values used, 2422599 and 2457085, are Julian date numbers.

select to_date(trunc(dbms_random.value(2422599,2457085)),'j') as rand_date from dual;

RAND_DATE
---------
07-NOV-62
Posted on

Custom trim() functions in Javascript

I come from a database background, so I have always taken Oracle’s trim() functions for granted. In short, the trim() function removes spaces at the start and end of a string, the ltrim() function removes spaces at the start (ie. left side) of a string, and finally the rtrim() function removes spaces at the end (ie. right side) of a string. As I dabble into web development nowadays, I thought it would be handy to have similar functions in Javascript at my disposal.

function trim(txt) {
	return txt.replace(/^s+|s+$/g,"");
}


function ltrim(txt) {
	return txt.replace(/^s+/,"");
}


function rtrim(txt) {
	return txt.replace(/s+$/,"");
}

Below are some usage examples.

alert(trim(' hello '));
// This should return 'hello'; spaces on both sides is removed

alert(ltrim(' hello '));
// This should return 'hello '; only space on left side is removed

alert(rtrim(' hello '));
// This should return ' hello'; only space on right side is removed
Posted on

Get image width and height with PHP

I was working on my World War II photo gallery recently. The gallery’s HTML code, particularly the main content section, looks something like the following.


<div id="content">
	<div class="main_column">
		<img decoding="async" src="images/helloWorld.jpg">
		<!--
		The photograph and other "main content" goes here.
		The width of this column is 600px.
		-->
	</div>

	<div class="extra_column">
		<!--
		This is the vertical side bar.
		If the width of the photo in the main section is more
		than 600px, it will overlap this div.
		-->
	</div>
</div>

Because my vertical navigation bar is on the right side of the page, if a photograph is larger than a particular width, it overlaps the vertical bar. I decided to remove the vertical bar if the photo is too large; let us assume that if the image width is larger than 580 pixels, it is too large. To apply the logic, I used the getimagesize() function. A quick example on this function is below.

list($imageWidth, $imageHeight, $imageType, $imageAttr) = getimagesize($imgName);
echo("The width of the image is ".$imageWidth." pixels.");
// Sample output: "The width of the image is 600 pixels."

I used the following PHP code to make my page work the way I want to based on width of the image.


<div id="content">
	<?php
	$imgName = "helloWorld.jpg";

	list($imageWidth, $imageHeight, $imageType, $imageAttr) = getimagesize($imgName);

	if ($imageWidth <= 580) { ?>
		<div class="main_column">
	<?php } ?>

	<img decoding="async" src="images/helloWorld.jpg">
	<!--
	The photograph and other "main content" goes here.
	-->

	<? if ($imageWidth <= 580) { ?>
		</div>
		<div class="extra_column">
		<!--
		This is the vertical side bar.
		Only show this if image width
		is 580 pixels or smaller.
		-->
		</div>
	<?php } ?>
</div>

Beyond width, the getimagesize() function in PHP also outputs other properties; the output is an array containing four elements. As you may have noticed, in my above example, I captured the following properties.

  • 0 $imageWidth : Width of the image in pixels; integer.
  • 1 $imageHeight : Height of the image in pixels; integer.
  • 2 $imageType : Type of the image; integer. Possible values are as follows:
    Value Description
    1  GIF
    2  JPG
    3  PNG
    4  SWF
    5  PSD
    6  BMP
    7  TIFF (Intel)
    8  TIFF (Motorola)
    9  JPC
    10  JP2
    11  JPX
    12  JB2
    13  SWC
    14  IFF
    15  WBMP
    16  XBM
  • 3 $imageAttr : HTML-formatted attributes; string. Example: ‘height=”600″ width=”400″‘
Posted on

Building an AIM buddy list from your database employee table with VBScript

The code below can be used against Oracle, SQL Server, or MySQL database table to automatically create an AIM buddy list. The SQL statement should select out a “group_name” field (ideas: a department name such as “Accounting” or “Purchasing”, an office location such as “Taipei” or “New York”, etc.) and an “aim_name” field. The “group_name” will be used as AIM groups, while “aim_name” are the users’ actual registered AIM names. The output file is a flat text file in a format that can be imported into AIM.

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' AIM Buddy List Builder                                        '
'                                                               '
' Description: Builds an AIM buddy list from your database      '
'      employee table.                                          '
' Author: C. Peter Chen                                         '
' Version Tracker:                                              '
'       1.0   20081021   Base version                           '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
option explicit
dim dbType, dbHost, dbName, dbUser, dbPass, outputFile, sqlstr

'''''''''''''''''
' Configuration '
'''''''''''''''''
dbType = "oracle"                ' Valid values: "oracle", "sqlserver", "mysql"
dbHost = "hostName"              ' Hostname of the database server
dbName = "dbName"                ' Name of the database/SID
dbUser = "user"                  ' Name of the user
dbPass = "password"              ' Password of the above-named user
outputFile = "c:buddylist.blt"  ' Path and file name of the output CSV file

' SQL statement below; be sure to select out "group_name" and "aim_name" in your SQL statement.
sqlStr = "select department_name as group_name, aim_name from employees_table where aim_name is not null order by department_name, aim_name"
'''''''''''''''''''''
' End Configuration '
'''''''''''''''''''''

dim fso, conn

'Create filesystem object 
set fso = CreateObject("Scripting.FileSystemObject")

'Database connection info
set Conn = CreateObject("ADODB.connection")
Conn.ConnectionTimeout = 30
Conn.CommandTimeout = 30
if dbType = "oracle" then
	conn.open("Provider=MSDAORA.1;User ID=" & dbUser & ";Password=" & dbPass & ";Data Source=" & dbName & ";Persist Security Info=False")
elseif dbType = "sqlserver" then
	conn.open("Driver={SQL Server};Server=" & dbHost & ";Database=" & dbName & ";Uid=" & dbUser & ";Pwd=" & dbPass & ";")
elseif dbType = "mysql" then
	conn.open("DRIVER={MySQL ODBC 3.51 Driver}; SERVER=" & dbHost & ";PORT=3306;DATABASE=" & dbName & "; UID=" & dbUser & "; PASSWORD=" & dbPass & "; OPTION=3")
end if

dim a, showList, prevGroup

set a = fso.createtextfile(outputFile)
a.writeline ("Config {")
a.writeline (" version 1")
a.writeline ("}")
a.writeline ("User {")
a.writeline (" screenName dummyAimName")
a.writeline ("}")
a.writeline ("Buddy {")
a.writeline (" list {")

set showList = conn.execute(sqlstr)

prevGroup = "placeholder"
do while not showList.eof
	if (showList("group_name") <> prevGroup) then
		if (prevGroup <> "placeholder") then
			a.writeline ("  }")
		end if
		a.writeline ("  """ + showList("group_name") + """ {")
	end if
	a.writeline ("   " + showList("aim_name"))
	prevGroup = showList("group_name")
	showList.movenext
loop
showList.close
set showList = nothing

a.writeline ("  }")

a.writeline (" }")
a.writeline ("}")

' Close
set a = nothing
set fso = nothing
conn.close
set conn = nothing

'You're all done!!  Enjoy the file created.
msgbox("AIM Buddy List Created!")

Interested in obtaining a generic AIM buddy list import file format? Please see below for an illustration with fictional data.

Config {
 version 1
}
User {
 screenName dummyAimName
}
Buddy {
 list {
  "Accounting" {
   MrCFO_fictionalUser
   BobAtAccounting_fictionalUser
   JaneDoe_fictionalUser
  }
  "Purchasing" {
   LewisTheBuyer_fictionalUser
  }
  "Useless Employees" {
   PaulJohnson_fictionalUser
  }
 }
}
Posted on

Checking for unauthorized local Windows administrator group members with VBScript

The only item to configure is the arrRealAdmins array of strings, where you may put in a list of user names that you do not wish to show in the report. The example already include two common administrator names that should be valid, “Administrator” and “Domain Administrators”.

Note that the sample code below outputs the invalid local administrator group members in a msgbox() pop-up box. You may wish to substitute this output method with something that may be more useful to you, such as outputting them to a report, write into a database, send email, etc.

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' List local admin users                                        '
'                                                               '
' Description: Finds a list of local admin users on a Windows   '
'     machine                                                   '
' Author: C. Peter Chen, http://dev-notes.com                   '
' Version Tracker:                                              '
'       1.0   20081021   Base version                           '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

option explicit
dim arrRealAdmins

'''''''''''''''''
' Configuration '
'''''''''''''''''
arrRealAdmins = Array("Administrator","Domain Admins")  ' List of users that *are* supposed to be administrators; we'll ignore these people later

'''''''''''''''''''''
' End configuration '
'''''''''''''''''''''

dim adminGroup, groupMember, ret

function isPermitedAdmin(MemberName)
	dim i
	for i = lbound(arrRealAdmins) to ubound(arrRealAdmins)
		if ucase(MemberName) = ucase(arrRealAdmins(i)) then
			isPermitedAdmin = true
			exit function
		end if
	next

	isPermitedAdmin = false
end function

set adminGroup = getObject("WinNT://./Administrators, group")
for each groupMember in adminGroup.members
	if not isPermitedAdmin(groupMember.name) then
    		ret = ret & groupMember.name & ","
	end if
next

if ret = "" then
	msgbox("No invalid local administrators found.")
else
	ret = mid(ret, 1, len(ret)-1) ' To get rid of the last comma
	msgbox("The following users are in the local admin group: " & vbcrlf & ret)
end if
Proudly powered by WordPress