Drupal - Rotating Banners

Playing website is my hobby since year 2000 when we set up an IT company. It's interesting to try to stay current while my daily job is mainly database stuff:). So I keep revamping my website from WordPress, Joomla, now Drupal, just to learning some new stuff.

Imaging 2000 until now, how the technology changed. It only takes 10 minutes to have a good, professional looking website and so many free themes you could choose. I had some popular Drupal themes, but I had problem to make the rotating banners working....

Documentation! Documentation! I had to be patient to read carefully on the documentation. Sometimes I was just lazy to read :(((. I missed whole bunch modules and now I would like to share those steps from Drupal site(https://www.drupal.org/node/1786134).

"Rotating Banners requires two other modules and a pair of scripts to function.
The dependent modules are Media, http://drupal.org/project/media, and Styles, http://drupal.org/project/styles.
The two server scripts are jquery.cycle.all.min.js and jquery.easing.1.3.js.
The first script can be downloaded here: http://jquery.malsup.com/cycle/download.html.
Once you download it, rename it to jquery.cycle.js.
The second can be downloaded here: http://gsgd.co.uk/sandbox/jquery/easing/
Once you download it, rename it to jquery.easing.js.
Install rotating Banners through the Administration/Modules page.
Then copy both scripts to the following directory: …/sites/all/modules/rotating_banner/includes/.
This will create an interface in the Admin/Structure/Blocks menu."

Basically we need media, style, and 2 jQuery scripts, and media needs Ctools module! I totally missed Requirement section! I am thinking I am close to retirement:(

The weird thing I found after installing Rotating Banners/Ctools, some modules got unchecked such as Toolbar, Locale etc., so I had to enable them again.

2013-14 NBA Playoff

ORCL - REGEXP_REPLACE

I really like the listagg function, but it can not be used with distinct.

Sometimes I want remove the duplicate values return form listagg function and I don't like to use subquery, etc.

Finally I found a great solution using regular expression! really cool and I thought maybe useful to someone:))


select '1223-1223-1223-1345' t1, rtrim( REGEXP_REPLACE('1223-1223-1223-1345', '([^-]*)(-\1)+($|-)', '\1\3'), '-') t2

from dual;

1223-1223-1223-1345 will be 1223-1345, but be sure the list is order by the item!

Here is a example for me:

select regexp_replace( (listagg(a.short_name, ',' ) within group (order by a.short_name)), '([^,]*)(,\1)+($|,)','\1\3')

from gift g, allocation a where g.gift_donor_id = l.gift_donor_id and g.gift_associated_allocation = a.allocation_code and a.alloc_school = 'LS') distinct_allocs      

Explanation is following: Courtesy to Srinivasan - bean farmer 

http://srinisqlwork.blogspot.com/2016/12/sql-remove-duplicates-in-listagg-result.html

Understanding the solution with example:

select

REGEXP_REPLACE( 'English,English,English,Hindi,Hindi,Kannada,Kannada,Kannada,Tamil,Tamil,Telugu,Telugu','([^,]*)(,\1)+($|,)'   ,'\1\3')

from dual

1. Understanding regExMatch expression: '([^,]*)(,\1)+($|,)'

There are three groups in above expression

group 1: ([^,]*) : match all or no characters till comma   

match Result: English

group 2: (,\1)+ : \1 - stands for first group which is Kannada, so it becomes (,English)+, meaning match one or more occurrences of  ',English'.

match Result: ,English,English

group 3: ($|,) : $ stands for 'end of string', | stands or. So it says match either end of string or a comma

match Result: ,    (this is comma which is following group2 match: ,English,English)

2. Understanding regExReplace expression '\1\3':

\1\3 represent the group number which are used in regExMatch expression.

replace Result: English,

REGEXP_REPLACE

Syntax

Description of the illustration regexp_replace.gif

Purpose

REGEXP_REPLACE extends the functionality of the REPLACE function by letting you search a string for a regular expression pattern. By default, the function returns source_char with every occurrence of the regular expression pattern replaced with replace_string. The string returned is in the same character set as source_char. The function returns VARCHAR2 if the first argument is not a LOB and returns CLOB if the first argument is a LOB.
This function complies with the POSIX regular expression standard and the Unicode Regular Expression Guidelines. For more information, please refer to Appendix C, "Oracle Regular Expression Support".

• source_char is a character expression that serves as the search value. It is commonly a character column and can be of any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB.
• pattern is the regular expression. It is usually a text literal and can be of any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. It can contain up to 512 bytes. If the datatype of pattern is different from the datatype of source_char, Oracle Database converts pattern to the datatype of source_char. For a listing of the operators you can specify in pattern, please refer to Appendix C, "Oracle Regular Expression Support".
• replace_string can be of any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. If replace_string is a CLOB or NCLOB, then Oracle truncates replace_string to 32K. The replace_string can contain up to 500 backreferences to subexpressions in the form \n, where n is a number from 1 to 9. If n is the backslash character in replace_string, then you must precede it with the escape character (\\). For more information on backreference expressions, please refer to the notes to "Oracle Regular Expression Support", Table C-1.
• position is a positive integer indicating the character of source_char where Oracle should begin the search. The default is 1, meaning that Oracle begins the search at the first character of source_char.
• occurrence is a nonnegative integer indicating the occurrence of the replace operation:
  
  • If you specify 0, then Oracle replaces all occurrences of the match.
  • If you specify a positive integer n, then Oracle replaces the nth occurrence.
• match_parameter is a text literal that lets you change the default matching behavior of the function. This argument affects only the matching process and has no effect on replace_string. You can specify one or more of the following values for match_parameter:
  
  • 'i' specifies case-insensitive matching.
  • 'c' specifies case-sensitive matching.
  • 'n' allows the period (.), which is the match-any-character character, to match the newline character. If you omit this parameter, the period does not match the newline character.
  • 'm' treats the source string as multiple lines. Oracle interprets ^ and $ as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string. If you omit this parameter, Oracle treats the source string as a single line.
  • 'x' ignores whitespace characters. By default, whitespace characters match themselves.
  If you specify multiple contradictory values, Oracle uses the last value. For example, if you specify 'ic', then Oracle uses case-sensitive matching. If you specify a character other than those shown above, then Oracle returns an error.
  If you omit match_parameter, then:
  
  • The default case sensitivity is determined by the value of the NLS_SORT parameter.
  • A period (.) does not match the newline character.
  • The source string is treated as a single line.

How are you, Air Canada?!

About 20 years ago, my 1st flight with Air Canada, I still remember that's Dec 8, a very cold winter day, ground covered with all snow. I arrived in Edmonton, but I lost one of my bag with bottles of expensive Chinese Mao Tai if you know some liquor from China:). That time my spoken English and listening are so limited, but I had to communicate with Air Canada to get my bag back. After a few weeks my bag was dragged to my apartment and finally I got it back even though the Mao Tai was opened, I guess it was checked by some officer:(

1996 I went to my home town through Shanghai with Air Canada, my home town is about 6 hours bus from Shanghai at that time without highway yet. Again I lost one new luggage, which I just bought for this trip. I left my home town contact information and went home; a few days later my new luggage came dirty on crowded bus, mot much complained, I felt fortune to get it back in just a few days.


2005 I visited China with whole family by Air Canada and again we missed one luggage on the way back to Canada! After a few month registering case on Air Canada website and talking to agents, I almost had no hope to get my luggage back (only compensation by Air Canada is $100!). Well, it was finally located and sent it back to me. When I opened my out-of-shape luggage, most gift/stuff was squeezed. Well, what can I say, it came back after a few month travelling around world, I wish I could did this free travelling!

Until now such losing luggage experience gave me a big stress every time I travel with checked luggage, I was so worried and always triple check to make sure I keep those luggage receipts in case any luggage gets lost. Recently I travel to China a lot to visit my parents and I believe Air Canada improved the process about the checked luggage, but unfortunately I encounter some new headache about cancelled and delayed air flights.

This Summer (2013) we booked tickets with Air Canada and we were stuck at Vancouver! That evening all passenger were on board and airplane is driving out and ready to take off, after 30 minutes waiting Captain told us there were some air-conditioning issue to fix before taking off. So we were patiently waiting and then were told that airplane had to go back for checking, shortly after reaching the dock we were notified to get off and wait inside. Soon it announced that the flight cancelled.

What a chaos and we had some miscommunication and finally whole family ran to some hotel outside airport in the Vancouver city at 2am in the morning and had to get back airport for re-check-in for another airplane departing at next day 9am. A lot of passenger were mad, especially those with connection flights, one of lady was so upset and ask all of us refuse to aboard, but nobody listened to her, since everyone was so tired and wanted to leave as soon as we could and got over with this. I was pretty sad to see that lady was screaming and kicking the coupon paper which Air Canada offers for this chaos, it's 15% off next purchase, come on, I don't think that lady will take Air Canada again:(

We were glad to take off next day and arrived safely in Beijing even it's was 12 hours delay, I have sympathy for those who missed next connecting flight, train or bus. When coming back the flight was delay again! We were late at Vancouver, and could not catch the booked flight to Edmonton. This time we were experienced with no panic and we were staying in the Fairmount hotel inside Vancouver airport, not bad, my daughters were excited to stay this nice hotel for a few hours.

That's my stories with Air Canada, I just want to say Air Canada, how are you doing? I wish you getting better and I heard news doing well. Do I ride with Air Canada again?

Yes, I think so, there are many reasons, just to speak a couple :))
1. I am a Canadian, hohoho
2. I just know a high school classmate, working at Air Canada Montreal office, Mr Yu, hahaha
......

Do I choose Air Canada if there is other option? I don't know honestly....

UTF-8 without BOM

I promise this post will be good:)

A while ago set up a group forum using phpbb, overall phpbb3 is not bad even though it lacks of some popular features and you need some module for some popular features.

As more and more posts on our forum, I thought it might be a good idea to mark those posts with over a certain number of views. I checked and phpbb3 comes with popular topic based on posts, I want to have popular posts based on views.

Thanks and hates to http://startrekguide.com/mods, I found that some simple update of a few php files could get what I wanted:)) since I am so lazy and web stuff is just one of my hobbies. Why I hate? After I followed the instruction to update those files, I found the crazy warning about "Cannot modify header information - headers already sent by (output started t /includes/acp/acp_board.php:1)"!

What the heck? I changed file back (didn't save a copy since I was thinking those changes are so simple:(). Still same error pops up, double checked the space, commas, etc, still same error! It gets me frustrated, so I decided to change configure php file (even it says don't touch this file:( ) to uncomment the debug mode line to see what's really going on. Damn, even worse, whole phpbb forum is not running!

Thanks to the Internet search, sounds like I was having some encoding type issue, I edited those files in notepad to make sure same as UTF-8, but still same thing! Searched again, I was educated that I need to save as UTF-8 without BOM, the notepad doesn't have this option so called BOM:(

After downloaded notepad++ and edit the files and applied the changes according to http://startrekguide.com/mods, now I havd to say thanks. After that everything was working properly:)

I thought I should write this out and again LONG LIVE INTERNET SEARCH! (mostly google or baidu?:))

Byte order mark

From Wikipedia, the free encyclopedia

      

The byte order mark (BOM) is a Unicode character used to signal the endianness (byte order) of a text file or stream. It is encoded at U+FEFF byte order mark (BOM). BOM use is optional, and, if used, should appear at the start of the text stream. Beyond its specific use as a byte-order indicator, the BOM character may also indicate which of the several Unicode representations the text is encoded in.^[1]

ORCL - FORMAT_ERROR_BACKTRACE

Having a few packages to send email around, often I find exception error is hard to trace since I was just using SQLerrm function in exception, which only shows the error without telling the source.

1st I thought I could look for ways to find where exactly the error occurs, such as showing function/procedure name, then after a little online search, I found dbms_utility package has a nice function: FORMAT_ERROR_BACKTRACE, which displays the error line number, better than function/procedure name!
 

This procedure displays the call stack at the point where an exception was raised, even if the procedure is called from an exception handler in an outer scope. The output is similar to the output of the SQLERRM function, but not subject to the same size limitation.

Syntax

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE 
  RETURN VARCHAR2;

Return value

 

NICE! Also if you want to look at full error stack, check FORMAT_ERROR_STACK function.

 

To use, just simply add such line (red) as the below:

 

EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line (dbms_utility.FORMAT_ERROR_BACKTRACE);
      V_MSG_SUB_ERROR := V_MSG_SUB_ERROR;
      V_MSG_ERROR     := V_MSG_ERROR || SQLERRM || '

' || dbms_utility.FORMAT_ERROR_BACKTRACE || V_MSG_SIG;
      UTL_MAIL.SEND(SENDER     => EMAIL_FROM,
                    RECIPIENTS => EMAIL_ERROR_TO,
                    CC         => EMAIL_BCC,
                    SUBJECT    => V_MSG_SUB_ERROR,
                    MESSAGE    => V_MSG_ERROR,
                    MIME_TYPE  => 'text/html');
  END;

"In a real-world application, the error backtrace could be very long. Generally, debuggers and support people don't really want to have to deal with the entire stack; they are mostly going to be interested in that top-most entry. The developer of the application might even like to display that critical information to the users so that they can immediately and accurately report the problem to the support staff. In this case, it is necessary to parse the backtrace string and retrieve just the top-most entry." - by Steven Feuerstein 

 

Home 2 routers with 2 ssids

We had the Cisco Linksys router at home for quite time, maybe 2,3 years. Since we moved into the current house, the upstairs wireless singal is kind of weak, especaily for Dell/HP laptops. So I decided buy a new router.

After some homework, I bought WestDigital Mynet N750, was thinking about N900 with DLNA support and 7 ports, but the comments on N750 looks better than N900. Anyway, I may check out N900 later some time, so far N750 singal is pretty good.

With 2 routers I have now I decided to setup 2 routers with 2 ssids, one is for N, one is for mixed B/G since I am still using 1st Gen iPod (ancient, isn't it?) and a HP mini laptop (very slow).  Even though my network skill is very rusty, but I thought it should be easy to set up.

Pretty easy to set up and the old iPod is connected to the 2nd router, obtaining right IP address, just could not get into Internet complainting about DNS problem, then set the DNS to the 1st router default gateway. Then it is all working:). This reminds me of old days when I was taking Network Specialist courses.

Some key points I could think of:

Any router had external and internal IP address, internal IP is easy and most time is just DHCP, external IP is what ever the IP talking outside the own network, for 2nd router the external IP is the IP within 1st router range, and for 1st router is the IP with my Internet Service Provider network range.

DNS is just like chain pass from internal to external, from router 2 go to router 1, then router to ISP DNS server to find real route to the web pages you want to see.

And also dont forget the 2 routers are in different subnets (1.1 and 0.1, you can do whatever number you want, just keep 192.168 or if you prefer the other private network start with 10 and 172).

Here is the diagram of my home network of 2 routers with 2 ssids:)

Next I will talk a bit about the hot DLNA topic.

Joomla or Wordpress

Doing some web site revamp recently, and amazed by the easiness and friendless of making website and there are so many nice templates you could choose, too many choice.

Looks Word press is very popular and I did get one for one of my clients, and will make it ready for themselves to update the content. For Joomla, was working with it before, and seems more complicated and powerful than Word press.

For my next project, I would like to choose Joomla if I could get the template I like. Now start to clean our hosting site stuff, so many blog/forum when I was testing. I would like to have one good forum package well support multiple languages such as English and Chinese.

Over all, here is a nice way to decide if you have difficult to choose which one:
(picture from http://www.sitepoint.com/ and thanks!)

Just now working on 2 templates, one is WordPress, one is Joomla, WordPress is OK to apply the dump.sql, but Joomla is a pain when dealing with different version. If I have time, I will definitely create my own template instead of buying. The version compatibility of Joomla is sure a pain.

Confucius and Confusion

Sounds really funny, since I was very young I am a confused boy, very puzzled and bewildered, but never lost for what I want.

I am confused by the life, the meaning of life, and think the world is full of confusion. That's why I picked confusion as my nick. Then I tried to find the adjective for confusion and I don't like confusional(?), so I decided to use confusious(even though no such word), then people are thinking I was wrong and should be confucius. Well, should I change this to the famous ancient great Master Kong? Let me know your answer:)

Confucius Says (子曰）is so profound, and let's review the Top 10 quotes from Master Kong (so many and I just pick what I like:):

Just a thought: I have to say sorry to Master Kong, since in the end he was confused and failed. The Only savior is Our Lord! Don't you agree?!

1. “Never impose on others what you would not choose for yourself.”

己所不欲，勿施于人。

2. “Real knowledge is to know the extent of one’s ignorance.”

知之为知之，不知为不知，是知也。

3. “Keep what you say and carry out what you do.”

言必信，行必果。

4. “A gentleman sets strict demands on himself while a petty man set strict demands on others.”

君子求诸己，小人求诸人。

5. “The Superior Man is aware of Righteousness, the inferior man is aware of advantage.”

君子喻於义,小人喻於利。

6. “The gentleman wishes to be slow in speech but quick in action.”

君子欲讷于言而敏于行。

7. “When I walk along with two others, they may serve me as my teachers.”

三人行，必有我师焉。

8. “He who learns but does not think, is lost. He who thinks but does not learn is in great danger.”

学而不思则罔,思而不学则殆。

9. “He that would perfect his work must first sharpen his tools.”

工欲善其事，必先利其器。

10. “If you look into your own heart, and you find nothing wrong there, what is there to worry about? What is there to fear?”

君子坦荡荡，小人长戚戚。

太多啦，加一个：有朋自远方来，不亦乐乎？
Is it not delightful to have friends coming from distant quarters?

ORCL - LISTAGG

Very handy Listagg Function

 

During my daily routine, I constantly need to display multiple values for single row, I have to write a few lines of code until the LISTAGG function!

 

 

http://docs.oracle.com/cd/E14072_01/server.112/e10592/functions087.htm

 

Examples from oracle

 

The following single-set aggregate example lists all of the employees in Department 30 in the hr.employees table, ordered by hire date and last name:

 

SELECT LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date, last_name) "Emp_list",
    MIN(hire_date) "Earliest"
    FROM employees
    WHERE department_id = 30;

Emp_list                                                     Earliest
------------------------------------------------------------ ---------
Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares            07-DEC-02

 

 

ORCL - Restart Database and EM

Back from a week vacation and found that the database is not working as well the ORCLE EM. What a pity for me as a newbie DBA without any mentor or help inside office. The good thing is I have Intenet to look for help....

OK, after half day working around, here I record the steps after server is accidently shutdown:

1. my bad is not setup swap space persistent, so I have to swap -a to make enough space, will do the persistent setup for swap space.
swap -a /export/home/oracle/orcl_swap then swap -l to check

2. Start the listener: lsnrctl start

3. Set up the ORACLE_SID, could add the line in .profile: export ORACLE_SID=orcl

4. Set up the ORACLE_HOSTNAME and UNQNAME for ORALE EM
export ORACLE_HOSTNAME=solaris
export ORACLE+UNQNAME=orcl
OM will look inside folder solaris_orcl

5. Startup database
sqlplus / as sysdba then startup

6. Start Oracle EM
emctl start dbconsole

Thanks to the link ORACLE-BASE.com:
http://www.oracle-base.com/articles/misc/basic-enterprise-manager-troubleshooting.php

ORCL - Data Pump

We need move 10g production data into 11g test so we could test advance system upgrade, there are three upgrade methods offered to upgrade database from 10g to 11g.

1. Database Upgrade Assistant (DBUA)
2. Manual Upgrade (Oracle provided scripts)
3. Export/Import (exp/imp, or expdp/impdp)

Using data pump looks promising, I prefer using Oracle EM, rather than manually following the steps:

1 impdp user/pass full=y directory =test_dir logfile=exp.log dumpfile=exp.dmp
2 copy dump file to new server
3 create database in new server with same tablespaces as source database -- IS IT NECESSARY?! (if different structure)
4 impdp user/pass full=y directory =test_dir logfile=exp.log dumpfile=exp.dmp

I used EM and finally got this error:

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.LOOK_FOR_OBJECT 

looks some problem with temp tablespace:

assign the temp tablespace to the schema owner as Oracle is probably using whatever default TEMP tablespace is specified for the database.
SQL> alter user advance temporary tablespace TEMP;

At leaset I was able to login through PL/SQL developer.

helpful links:

Oracle Doc: http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#autoId45

http://oraclepoint.com/oralife/2010/07/28/upgrade-oracle-database-from-10g-to-11g-with-data-pump-3/

ORCL - Installation

I decide to blog this after some frustration on installing Oracle 11g on Solaris 10. (For Solaris 11 you need some support account to get the Oracle 11g).

Intenet has a lot of information which sometimes could mislead you if you dont read carefully :(. I will recommend to read the official manual.

1st intall Oracle-Solaris 10, pretty straight forawrd, login as root.

Preinstallation Requirements:
PACKAGE: check all needed packages: pkginfo -i SUNWarc SUNWbtool SUNWhea SUNWlibC SUNWlibms SUNWsprot \ SUNWtoo SUNWi1of SUNWi1cs SUNWi15cs SUNWxwfnt get missing package from installaton DVD if missing any:
pkgadd -d /cdrom/sol*/Solaris*/Product SUNWi1cs SUNWi15cs
GROUP AND USER:
# groupadd oinstall
# groupadd dba
# useradd -d /export/home/oracle -m -s /usr/bin/bash -g oinstall -G dba oracle
# passwd -r files oracle
KERNEL PARAMETERS (IMPORTANT!)
Set Resource Control to Oralce Recommended Value
# projadd -U oracle -K "project.max-shm-memory=(priv,6g,deny)" group.dba
# projmod -sK "project.max-sem-nsems=(privileged,256,deny)" group.dba
# projmod -sK "project.max-sem-ids=(privileged,100,deny)" group.dba
# projmod -sK "project.max-shm-ids=(privileged,100,deny)" group.dba
This will make them persist, confirm by cat /etc/project
NOTE: When you use the prctl command (Resource Control) to change system parameters, you do not have to restart the system for these parameter changes to take effect. However, the changed parameters do not persist after a system restart.
Make sure oracle user is associated with the project (group.dba). This is missing in documents, and will possible produce "out of memory" error.
# id -p oracle uid=100(oracle) gid=100(oinstall) projid=3(default)
# usermod -K project=group.dba oracle
# id -p oracle uid=100(oracle) gid=100(oinstall) projid=100(group.dba)  
SWAP (IMPORTANT!)
# swap -l swapfile dev swaplo blocks free
/dev/dsk/c0t0d0s1 30,65 8 1092408 1092408
# mkfile 4096m /export/home/oracle/orcl_swap
# swap -a /export/home/oracle/orcl_swap
# swap -l swapfile dev swaplo blocks free
/dev/dsk/c0t0d0s1 30,65 8 1092408 1092408
/export/home/oracle/orcl_swap - 8 8388600 8388600
Now wh have enough swap space.
if you need persistent swap, add line inside /etc/vfstab.
INSTALL ORACLE Downlaod Oracle 11g and create necessary folders.
# mkdir -p /export/home/oracle/product/11.2.0/db_1
# mkdir /export/home/oracle/tmp
# chown -R oracle:oinstall /export/home/oracle
Configure profile for oracle user with enviroment variables:
vi/gedit /export/home/oracle/.profile
export ORACLE_BASE=/export/home/oracle
export ORACLE_HOME=$ORACLE_HOME/product/11.2.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
export TMP=/export/home/oracle/tmp
export TMPDIR=/export/home/oracle/tmp
export DISPLAY=0:0
Unzip and install
# unzip solaris.x64_11gR2_database_1of2.zip
# unzip solaris.x64_11gR2_database_2of2.zip
# cd databse
# ./runInstaller

After 5 time installation I am feeling OK with Oracle installation. Next step I woild like to copy 10g production data into 11g new server.

Japan 2011 earthquake

Popup Image/Text

University somehow uses the software called Sitecore for WCM(web content management), it's a dot net application using IIS. wonder why away from *nix open sources?

Anyway I have to use Sitecore to put up our Organization chart, good thing is that sitecore has the Edit Html option even though taking me some time to get famliar with the basic stuff in Sitecore.

The Chart is from Powerpoint since it was prepared for the presentation. We have adobe CS5 but looks the Powerpoint picture is the way to go. Sitecore limits the picture size to 700px, so the quality is kind of bad.

How to get better presentation of our organization chart based on the ppt converted picture:

1. Mouseover to get the original size, see effect1. This one is from Dynamicdrive.com (a super cool DHTML site)
2. Mouseover to magnify the portion, see effect2. This is also from Dynamicdrive.com, using jquery from google api libray , very cool.

The above effects have a shared weakness, can not get hotspot links, since it's a whole picture, I can only think to make link is doing hotspots.

So finally I did use CSS and javascript to have the CSS popout, as shown in effect3 , this one is ok for click the hotspots.

If ever anyone interests in the code, you can download here.

If you have any cool ways to do those thing, please drop me a link/links, appreciated.

PHP dynamic file

Was busy with html5 recently, even though the security issue and lack of broswer support. Did not wrok on php stuff for a while. I created a website long time ago, the owner doesn't want to spend money and ask some no-brain guy to maitain the site and made a big mess.

The site has a testmonial side note to display some customer's profile (a few words and pictures), they want to dynamically display different customers' profile each time (refresh, entering page, etc). So it's very easy for PHP to set up that.

step 1: creating different files names as note1, 2, 3, .....
step 2: dynamically include those files like this:
<?php include 'includes/side'.$n.'.inc'; ?>
step 3: random generating the number using rand(begin_number, end_number)

Overall just like this: <?php $n=rand(1,9); include 'includes/side'.$n.'.inc'; ?>

pretty cool? PHP is the king :)

ORCL - How cool it is!

在oracle中把连串字符转变为table fields，原来要做loop很麻烦，现在用expression就行了。


真爽！特此记录，怕以后忘了，呵呵。

if A_PROSPECTIDS is not null

then

-- remove from prospects_main any not in list of ids:

T_UPDATESQL := 'delete from prospects_main pm where pm.id_number

not in (( select lpad(trim(regexp_substr(a_prospectids,'[^,]+{1}',1,level)), 10, 0) ids from dual

connect by level <= length(regexp_replace(a_prospectids,'[^,]*'))+1 ) ';

execute immediate T_UPDATESQL;

commit;

exception

when others then

RAISE_APPLICATION_ERROR(-20999,

priority_prospects: Error removing prospects: ' ||

sqlcode || ': ' || sqlerrm);

end;

end if;


另外在用动态的SQL （dynamic SQL)时，变量要用加串，两个双引号相当于一个单引号：

lpad(trim(regexp_substr(' ||''''|| t_formattedids ||'''' || ',' || '''' || '[^,]+{1}' || '''' || ',1,level)), 10, 0) ids from dual

connect by level <= length(regexp_replace(' ||''''|| t_formattedids ||'''' || ',' || '''' || '[^,]*' || '''' || '))+1

TSQL - Job Failed (sp_addlinkedsrvlogin)

Hey, last time I just mentioned we are working on MS SharePoint stuff, so SQL server and T-SQL, all familar stuff...uh...

I learned database stuff through T-SQL at school, since our school has a good deal with MS, which school deesn't have? I have to admire MS's business strategy (sarcastic? I don't think so:)

So Our T-SQL team has a procedure to get data from oracle database and insert data into SQL table using OpenQuery, the procedure is working fine on local, but it failed when they put it as SQL job. The team jsut asked me to help so after quite some tries, we found the error is caused by invalid login. The log error is long and confusing, talking about expected NT user failed to login.

Then the problem is narrow down to the sp_addlinkedsrvlogin (Transact-SQL). There are 2 logins, local and remote login. The procedure uses some real account such as SA and some other accounts created for local login. The server is set up to use Windows and SQL loin option. Since I just jump in to try to help the team, I just trouble shooting from my experience without knowing all the set up in servers.

From MSDN:
[ @locallogin = ] 'locallogin'
Is a login on the local server. locallogin is sysname, with a default of NULL. NULL specifies that this entry applies to all local logins that connect to rmtsrvname. If not NULL, locallogin can be a SQL Server login or a Windows login. The Windows login must have been granted access to SQL Server either directly, or through its membership in a Windows group granted access.

after reading this, I tried to use NULL for local login, and it worked! I am not sure how to use actual local login to make it work on SQL job. I may check on this if I have time, or if the team has interest on that... And it's working, most time we are just lazy to explore more... hehe.

So I put this on net, hopefully someone has same situation and might be helpful. Long live sharing knowledge!

ORCL - Remove ^M in VI

Our management loves to use MS office products, Word, Excel, Sharepoint, to name a few...Our dev is Oracel/Unix, good thing we picked Oracle, since that is the main stream of univeristy? (BTW, I am working in university external relation department).

Quite often, we need get excel file to import into Oracle database and massaging data. Most colleagues use some gui developer tools such as Toad, PL/SQL Developer to do the job, which are pretty good. I like the Oracle external table (Worked a few years on SQL loader :).

To get file from local (of course Windows) to unix server, the PSCP comes handy! It's from Putty, maintained by a small team based in Cambridge, England, and easy command line. There are also some GUI tools like WinSCP, etc.

But sometimes when I convert excel into csv (comma, tab delimited), the external table will not work due to the stupid ( I should not say this :( )^M at the end of lines. UNIX treats the end of line differently than other operating systems. Sometimes when editing files in both Windows and UNIX environments, a CTRL-M character is visibly displayed at the end of each line as ^M in vi.

Good thing that Vi powerful replace command, :%s/^V^M//g, make sure ^V^M are ctrl-v and ctrl M, not copying/pasting :) . It looks not showing on VIM - GUI vi.

The :%s is a basic search and replace command in vi. It tells vi to replace the regular expression between the first and second slashes (^M) with the text between the second and third slashes (nothing in this case). The g at the end directs vi to search and replace globally (all occurrences).

Just another comment: I don't know why some IT people, especially the network/helpdesk IT guys like windows stuff so much (acutally I love that too, but just dont give up on *nix:), really user friendly? They will do anything to eacpae from any possibility of *nix stuff. Sigh... Are we thinking about converting data from Oracle to SQL server to work on Sharepoint stuff? Maybe it's good for performance.

We are IT people, we should handle everything and anything :)))

ORCL - sys_connect_by_path

I really want to share this cool use of sys_connect_by_path function!

To deal the unknown length of concatenating string, I need to concatenate multiple rows into 1 string without decaring at certain length such as varchar2(X).

So here is the magic, hope you like it...

select ltrim(sys_connect_by_path(names, ','),',') jonit_entities from (
select names, row_number() over (order by names) rn, count(*) over () cnt
from (select entity.pref_mail_name names
from prospect_entity P, entity
where P.Prospect_Id = &id
and P.ID_NUMBER = entity.id_number
order by P.PRIMARY_IND desc)
)
where rn = cnt
start with rn = 1
connect by prior rn = rn-1;

cheers, for detail information about sys_connect_by_path, please check here.