December 17th, 2008 | Tags: , , ,

Have you ever “scraped” web page contents using the following code:

<%
  Set xml = Server.CreateObject("MSXML2.ServerXMLHTTP")

  ' Opens the connection to the remote server.
  xml.Open "GET", "http://www.somedomain.com", False

  xml.Send
  v_response = xml.responseText
  response.write v_response
  Set xml = Nothing
%>

To your suprise, the web page content returned the following:


In order to access this page you must be using a browser which supports NTLM authentication. Currently, only Internet Explorer supports this

Well, this occurred because the page you accessed (http://www.somedomain.com based on the example above) requires NLTM authentication. In order to access the page successfully, you need to pass username and password credentials during the “GET” command. This can be achieved by modifying the code above to include the username and password:

<%
  Set xml = Server.CreateObject("MSXML2.ServerXMLHTTP")

  ' Opens the connection to the remote server.
  xml.Open "GET", "http://www.somedomain.com", "DOMAIN\user", "password"

  xml.Send
  v_response = xml.responseText
  response.write v_response
  Set xml = Nothing
%>

Make sure your replace “http://www.somedomain.com”, “DOMAIN\user” and “password” with values applicable to your process.

That’s it. Happy coding!

November 12th, 2008 | Tags: , , ,

There’s a great article by Tom Kyte about loading a tab delimited file into Oracle using SQL*Loader which can be viewed here. It talks about using the hexadecimal character X’9′ (tab character) as the delimiter when implementing your control file.

Although, it failed to add that if you add the OPTIONALLY ENCLOSED BY '"' option in your control file, it WILL NOT WORK. That is, if your file does not contain the specified enclosing character.

For example the following will not work, as it will still lump all tab delimiters into one if there are null values:


LOAD DATA
INFILE *
INTO TABLE DEPT
replace
FIELDS TERMINATED BY X'9'
OPTIONALLY ENCLOSED BY '"'
(DEPTNO, DNAME, LOC)
BEGINDATA
12      SARATOGA
10  ACCOUNTING  CLEVELAND
11  ART SALEM
13  FINANCE BOSTON
21  SALES   PHILA.
22  SALES   ROCHESTER
42  INT'L   SAN FRAN

(note: there are two tabs between 12 and SARATOGA in the above)

Either change the file so each null field is enclosed by the character specified in the control file, or get rid of the option altogether (if your file values aren’t enclosed by a character). In conclusion, be careful when using the OPTIONALLY ENCLOSED BY '"' option as this appears to be an Oracle bug.

October 31st, 2008 | Tags: , , , , , , , ,

OK, so you got a whole lotta movies in Divx format. You got it all saved in one large hard drive possibly categorized into multiple subdirectories. You’re sick and tired of watching your collection through your PC, and you want to watch it on the “big screen”. Well, you got a number of options:

  1. If you got an XBOX 360, connect it through the internet, then update XBOX LIVE so you can play Divxs. Plug your external hard drive to the 360 via USB, and away you go.
  2. Plug your PC/laptop via VGA, if your TV supports it.
  3. Get yourself a Hard Drive with a built-in Media Player.
  4. Get yourself a dvd player that support most video formats (including divx of course) that’s equipped with a USB port (supporting USB 2.0).

Higlander Divx Player + External HDDI’ve actually tried all 4 options. I’d say the best option would probably be option 4. I also highly recommend the Highlander HDMI DVD DivX Player. It’s cheap, fully functional, and supports a wide range of video formats. Here’s a run down of all it’s features:

  1. Highlander HDMI DVD Player - Multiregion
  2. 5.1 Channel MPEG-4/DiVX /HDMI DVD Player
  3. Region free
  4. Loader: Sanyo HD65
  5. Solution: MTK 1389HD
  6. Support DVD-R/DVD/VCD/HD-CD/CD-R/MPEG-4/DiVX/HDMI
  7. Support Multilingual Dialogue (up to 8 tracks) and subtitle
  8. Support NTSC/PAL playback
  9. 4x Zooming Playback
  10. Full function Remote Control
  11. Onscreen Display, Parental Control
  12. Selectable Screen Aspect Ratio (4:3 & 16:9)
  13. Dolby AC-3 5.1 Channel Audio Output
  14. Composite Video, S-Video Output
  15. Programmed/Repeat Play Remote Control
  16. Accessories: AV Cables and Remote Control

Pros:

  1. Great quality video through HDMI or Component input
  2. Zoom Playback
  3. USB and SD card input

Cons:

  1. It’s got a wide case and it’s not pretty
  2. Can’t seem to turn off the zoom onscreen display when zooming in
  3. 8 character file name limit makes it hard to determine which video to select
  4. Setting up component display requires connection via RCA first
  5. If you accidentally press reset on your remote control then it restores settings to factory default. This means that you have to hook up your device via RCA in order to switch the display to component

Higlander Divx Player + External HDD CloseupI’d give this device a 7.5/10. For its price and functionality, I’d push that up to 8/10. You can get more information about the product by going here:

http://www.dealsdirect.com.au/p/highlander-hdmi-dvd-divx-player/

Hands up who thinks “captcha”, an image based challenge-response test to ensure a response is not generated by a computer, is a big pain in the butt and you’re sick and tired of seeing it in every web form. Not only is it obstructive but most of the images are very difficult to decipher.

Well, an alternative to “captcha” is a well place hidden variable within a web form containing a random number. It doesn’t require user intervention so it’s seamless which would mean that your customers are less annoyed. It acts like a simple redundancy check which effectively identifies if the web form was submitted from the server, and not remotely invoked through a “page scrape” copy. If the web form was submitted from a remote copy, which is how most “robot” processes operate, then the form is not processed.

The example form below is a simple “contact us” page and it implements the hidden variable redundancy check. Make sure that you insert your own code in the following comment block:

'**************************************************************************************
'* Insert code here to post the message
'* Eg. Insert message in a database or send message to your email address
'**************************************************************************************

Save the following code as contact.asp:

<%
  '**************************************************************************************
  '* Store the FORM PASSWORD session value in MESSAGE_PASSWORD variable
  '**************************************************************************************
  MESSAGE_PASSWORD = Session("FORM_PASSWORD")

  v_error = ""
  if Request("posted") = 1 then
    name = Request("name")
    email = Request("email")
    message = Request("message")

    ' Validate web form values
    if name = "" then
      v_error = v_error & "Please enter your name<br />"
    end if
    if validateEmail(email) = false then
      v_error = v_error & "Please enter a valid email address<br />"
    end if
    if message = "" then
      v_error = v_error & "Please enter your message<br />"
    end if

    if v_error = "" then

      if CInt(Request(MESSAGE_PASSWORD)) = CInt(MESSAGE_PASSWORD) then
        '**************************************************************************************
        '* Insert code here to post the message
        '* Eg. Insert message in a database or send message to your email address
        '**************************************************************************************

        Response.write "Your message was sent to us successfully. We will respond to you within 24 hours of receiving this message."
        message = ""
      end if
    end if
  else
    '**************************************************************************************
    '* Generate new FORM PASSWORD value and store it in session variable
    '**************************************************************************************
    Session("FORM_PASSWORD") = randomNumber
    MESSAGE_PASSWORD = Session("FORM_PASSWORD")
  end if
%>
<h1>Contact Us</h1>
<p>Please enter your query in the message text area below</p>
<%
if v_error <> "" then
  Response.write v_error
end if
%>
<form action="contact.asp" method="post">
<table border="0" cellpadding="0" cellspacing="2">
  <tr>
    <td class="tdhead">name</td>
    <td class="spacer" />
    <td><input name="name" id="namefield" class="textbox" type="text" size="38" maxlength="50" value="<%= name %>" /></td>
  </tr>
  <tr>
    <td class="tdhead">email</td>
    <td class="spacer" />
    <td><input name="email" class="textbox" type="text" size="38" maxlength="50" value="<%= email %>" /></td>
  </tr>
  <tr>
    <td class="tdhead">message</td>
    <td class="spacer" />
    <td><textarea name="message" rows="5" cols="40" class="textbox"></textarea></td>
  </tr>
  <tr>
    <td colspan="2" />
    <td>
      <input type="hidden" name="posted" value="1" />
      <input type="hidden" name="<%= MESSAGE_PASSWORD %>" value="<%= MESSAGE_PASSWORD %>" />
      <input type="submit" value="Send Email" class="textbox2"  />
    </td>
  </tr>
</table>
</form>
<script type="text/javascript">
  if (document.forms.length > 0)
    if (document.forms[0].elements.length > 0)
      document.forms[0].elements[0].focus();
//-->
</script>

<%
'**************************************************************************************
'* Common functions
'**************************************************************************************

'**************************************************************************************
'* validateEmail
'* -------------------------------
'* Returns true if email is well formed, false otherwise
'**************************************************************************************
Function validateEmail(sCheckEmail)
  Dim sEmail, nAtLoc
  validateEmail = True
  sEmail = Trim(sCheckEmail)
  nAtLoc = InStr(1, sEmail, "@") 'Location of "@"

  If Not (nAtLoc > 1 And (InStrRev(sEmail, ".") > nAtLoc + 1)) Then
    '"@" must exist, and last "." in string must follow the "@"
    validateEmail = False
  ElseIf InStr(nAtLoc + 1, sEmail, "@") > nAtLoc Then
    'String can't have more than one "@"
    validateEmail = False
  ElseIf Mid(sEmail, nAtLoc + 1, 1) = "." Then
    'String can't have "." immediately following "@"
    validateEmail = False
  ElseIf InStr(1, Right(sEmail, 2), ".") > 0 Then
    'String must have at least a two-character top-level domain.
    validateEmail = False
  End If
End Function

'**************************************************************************************
'* randomNumber
'* -------------------------------
'* Generate random number
'**************************************************************************************
Function randomNumber
  Randomize timer
  Dim rndNum
  ' Randomizing the timer function
  rndNum = abs(int((rnd() * 3001)))
  ' To generate a prime based, non-negative random number..
  rndNum = rndNum + 53
  randomNumber = rndNum
End Function
%>

Some comments about the code:

When the form is first requested it generates a random number which acts like a form password.

    '**************************************************************************************
    '* Generate new FORM PASSWORD value and store it in session variable
    '**************************************************************************************
    Session("FORM_PASSWORD") = randomNumber
    MESSAGE_PASSWORD = Session("FORM_PASSWORD")

This password is stored in a session variable as well as in the form as a hidden item with the same name and value, being the password itself.

  '**************************************************************************************
  '* Store the FORM PASSWORD session value in MESSAGE_PASSWORD variable
  '**************************************************************************************
  MESSAGE_PASSWORD = Session("FORM_PASSWORD")

      <input type="hidden" name="<%= MESSAGE_PASSWORD %>" value="<%= MESSAGE_PASSWORD %>" />
 

When the form is submitted the session value and form value is compared, and if they match then the form is processed.

      if CInt(Request(MESSAGE_PASSWORD)) = CInt(MESSAGE_PASSWORD) then
        '**************************************************************************************
        '* Insert code here to post the message
        '* Eg. Insert message in a database or send message to your email address
        '**************************************************************************************

        Response.write "Your message was sent to us successfully. We will respond to you within 24 hours of receiving this message."
        message = ""
      end if

That’s it! Happy coding!

October 21st, 2008 | Tags: , , , ,

This is a hack that worked for me on several occasions. Our development environment was upgraded to Oracle ApEx 3.1, but for whatever reason, our production environment was not (it’s still sitting on the previous version ApEx 3.0). Our server administrator wouldn’t allow the upgrade on production because of a “bug” with ApEx 3.1. He didn’t really elaborate on what that bug was, but anyway, as a developer, I needed to deploy new pages to our production environment.

To import an Oracle ApEx 3.1 Page Export into an ApEx 3.0 application, do the following:

1. Export the page from the ApEx 3.1 software.

2. Using a reliable text editor, modify the page export and replace any references to the original application ID to the app ID of the application you want to import to (replace xxx with the new application ID):

prompt  APPLICATION xxx - APP V5.6

   -- SET APPLICATION ID
   wwv_flow.g_flow_id := xxx;

3. As specified in the following thread http://forums.oracle.com/forums/thread.jspa?messageID=2380177, modify the following in the page export:

in the command

wwv_flow_api.create_page(
remove this line:
p_include_apex_css_js_yn=>’Y',
\– \————————————————————-
in the command
wwv_flow_api.create_page_plug (
after the line
p_plug_caching=> …….
add
p_required_patch=> ” + wwv_flow_api.g_id_offset,
\– \————————————————————-
everywhere you find the command
wwv_flow_api.create_report_region (
remove line
p_ajax_enabled=> ‘N’,

4. Import the modified file using the Export/Import utility in ApEx 3.0, remember to specify “Application, Page or Component Export” as the file type.

5. Edit the page in Application Builder page editor and restore the following: Region Templates, Button Templates, and List Types, and whatever item attribute that may have dropped off because it doesn’t exist in the meta data of the new application.

That should be it. Cumbersome, I know, but trust me, it’s a lot quicker than recreating the page in the new application.

Dynamic PRWeb Backlinks Dynamic PR is a FREE service for website owners who want to show website PR images dynamically on their website. The website PR image shows the current google page rank for the given page. Dynamically display website PR images in your site NOW by including the html code in the text box below, no sign up necessary.

<img src="http://pr.web-backlinks.com/pr.php?domain=[DOMAIN]” />

Replace [DOMAIN] with the site you want to display the PR image for, for example:

<img src="http://pr.web-backlinks.com/pr.php?domain=google.com" />

For more information and details about conditions of use, please visit our dynamic PR home page at http://pr.web-backlinks.com

October 6th, 2008 | Tags: ,

To enable PHP in Apache you need to do the following.

You need to tell PHP the root directory of Apache ie. In PHP.INI change the doc_root variable so that it holds the root directory of Apache:

Change PHP.INI (Usually located in [DRIVE]:\PHP\) entry for doc_root:

doc_root = "C:\Program Files\Apache Software Foundation\Apache2.2\htdocs"

Add the following lines in HTTPD.CONF (Usually located in [DRIVE]:\Program Files\Apache Software Foundation\Apache2.2\conf\):

ScriptAlias /php/ "C:/Program Files/PHP/"
AddType application/x-httpd-php .php .php5
Action application/x-httpd-php "/php/php-cgi.exe"
SetEnv PHPRC "C:/Program Files/PHP"

DirectoryIndex index.html index.php

Remove deny all from the following entry in HTTPD.CONF:

<Directory />
    Options FollowSymLinks
    AllowOverride None
    Order deny,allow
    Deny from all
</Directory>

So that the new entry looks similar to:

<Directory />
    Options FollowSymLinks
    AllowOverride None
    Order deny,allow
</Directory>

That’s it… Happy coding!

October 3rd, 2008 | Tags: , , , ,

I stumbled upon this great article by Yuan Wang which allowed you to convert an XLS file into CSV in C# (Convert XLS into CSV). It was created and compiled using Visual Studio 2005. I copied the code and compiled it in VS 2008 and it compiled OK but when I ran it, I got the following exception:

C:\Documents and Settings\d205414\My Documents\Visual Studio 2008\Projects\XlsToCsv\XlsToCsv\obj\Release>XlsToCsv.exe
System.Data.OleDb.OleDbException: Could not find installable ISAM.
   at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString cons
tr, OleDbConnection connection)
   at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOpti
ons options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection o
wningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbC
onnection owningConnection, DbConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection ow
ningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection ou
terConnection, DbConnectionFactory connectionFactory)
   at System.Data.OleDb.OleDbConnection.Open()
   at XlsToCsv.Program.convertExcelToCSV(String sourceFile, String worksheetName
, String targetFile) in C:\Documents and Settings\d205414\My Documents\Visual St
udio 2008\Projects\XlsToCsv\XlsToCsv\Program.cs:line 51

I made a few modifications to the source, to fix the exception but also allow the executable to accept parameters. Here’s the modified source code:

using System;
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Collections.Generic;
using System.Text;

namespace XlsToCsv
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                string sourceFile, worksheetName, targetFile, skipHeader;
                sourceFile = args[0]; worksheetName = "Sheet1"; targetFile = args[1]; skipHeader = args[2];
                convertExcelToCSV(sourceFile, worksheetName, targetFile, skipHeader);
            }
            catch
            {
                Console.WriteLine("USAGE: XlsToCsv [XLS File] [CSV File] [Skip Header? Yes/No]");
            }
        }

        static void convertExcelToCSV(string sourceFile, string worksheetName, string targetFile, string skipHeader)
        {
            string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data Source=" + sourceFile + ";" + @"Extended Properties=""Excel 8.0;HDR=" + skipHeader + @";IMEX=1""";

            OleDbConnection conn = null;
            StreamWriter wrtr = null;
            OleDbCommand cmd = null;
            OleDbDataAdapter da = null;

            try
            {

                conn = new OleDbConnection(strConn);
                conn.Open();

                cmd = new OleDbCommand("SELECT * FROM [" + worksheetName + "$]", conn);
                cmd.CommandType = CommandType.Text;
                wrtr = new StreamWriter(targetFile);

                da = new OleDbDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);

                for (int x = 0; x < dt.Rows.Count; x++)
                {
                    string rowString = "";
                    for (int y = 0; y < dt.Columns.Count; y++)
                    {
                        rowString += "\"" + dt.Rows[x][y].ToString() + "\",";
                    }
                    wrtr.WriteLine(rowString);

                }

                Console.WriteLine("File converted successfully.");

            }
            catch
            {
                // Console.WriteLine(exc.ToString());
                Console.WriteLine("USAGE: XlsToCsv [XLS File] [CSV File] [Skip Header? Yes/No]");
                Console.ReadLine();
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                conn.Dispose();
                cmd.Dispose();
                da.Dispose();
                wrtr.Close();
                wrtr.Dispose();
            }
        }
    }
}

Save the C# code and compile in Visual Studio 2008 (I used express edition myself).

To execute, call the following in command prompt:

USAGE: XlsToCsv [XLS File] [CSV File] [Skip Header? Yes/No]

eg.

XlsToCsv source.XLS output.CSV No

October 2nd, 2008 | Tags:

This post is an extension from the URL2File script that I posted previously. While the URL2File script allowed you to save web pages to a local file, the following VbScript will allow you to save a binary file on the web to a local file.

This VbScript requires Windows script hosting which you can download from:

http://www.microsoft.com/downloads/details.aspx?FamilyID=47809025-D896-482E-A0D6-524E7E844D81&displaylang=en

Save the following code as save2file.vbs:

'Wscript.Arguments(0) - Website  URL
'Wscript.Arguments(1) - Full File Path

Const adTypeBinary = 1
Const adSaveCreateOverWrite = 2

'Create Stream object
Dim BinaryStream
Set BinaryStream = CreateObject("ADODB.Stream")

'Specify stream type - we want To save binary data.
BinaryStream.Type = adTypeBinary

'Open the stream And write binary data To the object
BinaryStream.Open
BinaryStream.Write BinaryGetURL(Wscript.Arguments(0))

'Save binary data To disk
BinaryStream.SaveToFile Wscript.Arguments(1), adSaveCreateOverWrite
Function BinaryGetURL(URL)
  'Create an Http object, use any of the four objects
  Dim Http
'  Set Http = CreateObject("Microsoft.XMLHTTP")
'  Set Http = CreateObject("MSXML2.ServerXMLHTTP")
  Set Http = CreateObject("WinHttp.WinHttpRequest.5.1")
'  Set Http = CreateObject("WinHttp.WinHttpRequest")

  'Send request To URL
  Http.Open "GET", URL, False
  Http.Send
  'Get response data As a string
  BinaryGetURL = Http.ResponseBody
End Function

To run the script in a command prompt session:

save2file.vbs [URL] [FULL OUTPUT FILE PATH]
eg. save2file.vbs http://localhost/test.XLS c:\temp\test.XLS

October 2nd, 2008 | Tags: , , , ,

I recently needed to execute a HTTP post from an ApEx application to another web page running as some sort of a service. For added security the web page service only accepted POST requests and one of the parameters passed is a secret password (not encrypted but secure enough for an Intranet application).

Anyway the requirement was to post the request using PL/SQL to the web service, and receive the response. I visited the oracle page on UTL_HTTP for a bit of reference and came up with the following code:

set serveroutput on;
exec dbms_output.enable(1000000000);set escape '\'DECLARE
  req   UTL_HTTP.REQ;
  resp  UTL_HTTP.RESP;
  value VARCHAR2(1024);  -- URL to post to
  v_url VARCHAR2(200) := 'http://T97040476TA9000/core_dmt/withdraw_job.php';
  -- Post Parameters
  v_param VARCHAR2(500) := 'pwd=password123\&core_id=12223\&type=PK\&reason=Test reason';
  v_param_length NUMBER := length(v_param);
BEGIN
  -- Set up proxy servers if required
  --  UTL_HTTP.SET_PROXY('proxy.my-company.com', 'corp.my-company.com');
  req := UTL_HTTP.BEGIN_REQUEST (url=> v_url, method => 'POST');
  --  UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Mozilla/4.0');
  UTL_HTTP.SET_HEADER (r      =>  req,
                       name   =>  'Content-Type',
                       value  =>  'application/x-www-form-urlencoded');
  UTL_HTTP.SET_HEADER (r      =>   req,
                       name   =>   'Content-Length',
                       value  =>   v_param_length);
  UTL_HTTP.WRITE_TEXT (r      =>   req,
                       data   =>   v_param);  resp := UTL_HTTP.GET_RESPONSE(req);
  LOOP
    UTL_HTTP.READ_LINE(resp, value, TRUE);
    DBMS_OUTPUT.PUT_LINE(value);
  END LOOP;
  UTL_HTTP.END_RESPONSE(resp);
EXCEPTION
  WHEN UTL_HTTP.END_OF_BODY THEN
    UTL_HTTP.END_RESPONSE(resp);
END;
/

The only thing you need to change to make the code work for you are the v_url and the v_param values to reflect the url of your service and the parameters it expects.

Happy Coding!