« January 2005 | Main | April 2005 »
March 21, 2005
Copying a really long row in SQL
Programmers are typically lazy when it comes to coding and therefore do not like to type more than they have to.
Every once in a while, I need to repeat the entire contents of a very long database row but change just a few fields like the primary key and description. In other words, I need to copy the row and append it at the end of the table after making a few minor changes. Here is a method to allow you to easily do this without typing INSERT INTO longtable (field1, field2, field3, etc.) VALUES (value1, value2, value3, etc.);
--create a temporary table to copy the long row into
create global temporary table tmpLongTable as select * from longtable;
--copy the row into the temporary table
insert into tmpLongTable (select * from longtable where primary_id = 23);
--update the temporary table to reflect the new data
update tmpLongTable set primary_id = 26, description_txt = 'New Description' where primary_id = 23;
--insert the updated copied row back into the main table
insert into longtable (select * from tmpLongTable);
--drop the temporary table - not needed, but better safe than sorry
drop table tmpLongTable;
--see if everything worked
select * from longtable where primary_id = 26;
--your database supports transactions, right?
commit;
I have tested this in both Oracle and DB2. Another way to accomplish this is: INSERT INTO mytable (select 'myvalue1', 'myvalue2', FIELD3, etc. from mytable); I personally like the first way better.
Posted by Chuck at 04:00 PM | Comments (1) | TrackBack
March 17, 2005
News only nerds would care about...
Tomorrow, Fri, 18 Mar 2005 01:58:31 UTC will be exactly 1,111,111,111 seconds since the Epoch. See for yourself here. The Epoch as it relates to computer timestamps occurred on January 1, 1970. Many operating systems and programming functions rely heavily on the number of seconds or milliseconds since the Epoch for various calculations.
Posted by Chuck at 03:36 PM | Comments (1) | TrackBack
March 15, 2005
Fun With Regular Expressions
The Java Development Kit (JDK) has supported Regular Expressions without any external libraries since version 1.4. Regular Expressions are great because they allow you to do things in one line that would normally take many lines of code. Though Regex's (a common nickname for Regular Expressions) can be very cryptic, they are extremely powerful.
If you are new to Regex's, check out this tutorial. Then take a look at some things you can do with Java and Regex's to make your life easier.
Here is a quick example. Suppose I had a String with a sentence containing any number of words, some of which were email addresses. Also, suppose that I wanted to remove all the email addresses for privacy reasons. With Regular Expressions, I can easily (though cryptically) find each email address within the String and replace it with something else using the String.replaceAll() function. Below, strSentence is the example String prefilled with any number of words and email addresses:
strSentence = strSentence.replaceAll("[\\w-]+(?:\\.[\\w-]+)*@(?:[\\w-]+\\.)+[a-zA-Z]{2,7}", "<EMAIL REMOVED FOR PRIVACY>");
In this case, the Regex (unescaped) is: [\w-]+(?:\.[\w-]+)*@(?:[\w-]+\.)+[a-zA-Z]{2,7} - Note that I had to escape all the back slashes in order to put it in a Java String.
Now, my sentence is exactly the same except all email addresses have been replaced with "<EMAIL REMOVED FOR PRIVACY>".
Regex's can also do things like password constraint validation (i.e. your password must be at least 5 characters long and contain a letter and a number), complex pattern matching & replacing and other types of validation such as credit card numbers and SSN's. Struts makes heavy use of Regex's for form validation. Maybe you should too.
Posted by Chuck at 08:03 PM | Comments (0) | TrackBack