Why database should be taught before programming in universities?

Database Programming
Learn Database before Coding

Often students from the initial semester ask me how do we store our data in our programming projects? When students join university to learn about computer science and technology they are usually taught programming first in courses like introduction to programming. As part of the coursework, students are required to work on a project. The majority of the projects, in fact almost all projects involve data handling and that data needs to be stored somewhere, usually in databases.

Problems Students Face

As a novice students don’t know how to store data. One option is to store data in plain text files if filing is taught to them but in that case, their project becomes too complex for them. In my opinion, file format is an advanced topic for students that have just started learning how to program. So, students get stuck on where and how to store data. They create variables and arrays to store data in memory but that is not very useful until they have the option to store their data somewhere permanently that they can retrieve later. Otherwise, every time they run their project they have to feed data from the beginning.

Teach Database Before Programming

If universities modify their courses and add database in the first semester and replace programming courses with it then it would be easier for students to get started in computer science degree. Introduction to databases is a relatively easier course than programming and students will know what a database is, how to store data in the database, and how to retrieve it later using SQL. Then in the next semester if they do a programming course then it will require only one lecture to teach them how to access a database from your code and how to store and retrieve data. That will make their projects more valuable and make more sense to them and they can take it to an advanced level in forthcoming courses.

Your Take?

What is your opinion? Please, let me know in the comments.

Click here to read more about Databases.

List of FREE services & tools Startups should be using

I have curated a list of free tools, services, and apps that startups could and in fact should use to grow at the initial stage. Free doesn’t mean they lack quality, instead, these free tools are from top-notch companies like RedHat, Google, Asana, and GitHub and in all areas from infrastructure to version controlling to marketing and sales to project management.

Have a look at this list here and don’t forget to give your feedback.

I compiled this list a long time ago and recently updated it but it still might have some outdated links that I didn’t get a chance to update yet. Feel free to let me know and I’ll update it.

Enjoy!

Subscribe to get updates from our blog.

Deploy your first ASP.Net MVC App to AppHarbor

If you’ve been developing ASP.Net MVC apps lately you might be thinking of some online or cloud-based app hosting platform available as PaaS for Microsoft technologies especially for hosting ASP.Net MVC apps just like OpenShift, Heroku and other platforms are available for technologies like Ruby, Python, PHP, Node.js and even supporting CMS like WordPress. The good news for ASP.Net developers is that there is a PaaS platform available that you might already know. The platform is AppHarbor. AppHarbor runs over Amazon AWS and has some nice features that I won’t go into the details of. If you are interested in knowing how AppHarbor works you can see their page here.

Regardless of whether AppHarbor provides a decent service, new developers might still face some difficulty in deploying their applications to AppHarbor. Especially, if you are developing apps based on new Visual Studio 2015 templates like MVC. There are different ways to deploy but I would follow the below approach which in my opinion is good and provide auto deployment or in other words Continuous Integration (CI).

What do you need?

We will be using the following tools and accounts.

  • Visual Studio 2015 (any edition, I used the Professional version)
  • ASP.Net MVC app created from VS2015 MVC template
  • GitHub repo for the app/project
  • Local git repo for the app with remote repo set as your GitHub app repo
  • AppHarbor app

What’s not covered?

Our focus today is the deployment of our ASP.Net MVC app to AppHarbor. Therefore, we won’t be going into the details of how the application is created or its architecture, what’s new in Visual Studio 2015, what is MVC, what is Git and GitHub, and how to connect your GitHub repo to AppHarbor etc. We will assume that you already have all the prerequisites and we will just focus on what problems can we come across during deployment and how to fix them.

Deployment Steps

  1. Initialize a Git repo and connect it with your GitHub repo.
  2. Create the AppHarbor app from your GitHub repo. Whenever we commit/push our changes to our GitHub repo AppHarbor will automatically fetch the latest push and build it. Upon successful build it will deploy the app on its server otherwise it will keep the last successful build. This makes things very easy.
  3. Create an ASP.Net MVC application using the Visual Studio 2015 MVC template.
  4. Add the packages folder to .gitignore
  5. Enable NuGet Package Restore. In VS2015 click Tools>Options and then select NuGet Package Manager and make sure both checkboxes are checked in this section.
  6. In VS2015 right click on the project and click properties, then go to the Build Events tab. In the Post-build event command line text area paste the following command.
    1. if not exist “$(WebProjectOutputDir)\bin\Roslyn” md “$(WebProjectOutputDir)\bin\Roslyn”
      start /MIN xcopy /s /y /R “$(OutDir)roslyn\*.*” “$(WebProjectOutputDir)\bin\Roslyn”
  7. Now commit and push your changes to GitHub.
  8. That’s it! AppHarbor will automatically fetch the latest version changes and build it and you can check your AppHarbor application on its URL.

What’s Next?

There are a few things that you need to take care of specially related to security. This MVC app uses SQL Server Compact which isn’t a good option for production-level apps. Secondly, your connection string or password to the database must not be committed to a public GitHub repo.

This post will just give you a smooth start without any difficulties which I faced among other people that you can see in the resources section below.

If you think this was helpful or if I have missed anything please do let me know in the comments below.

Happy coding!

Resources

  1. https://blog.appharbor.com/2012/02/06/use-nuget-package-restore-to-avoid-pushing-packages-to-appharbor
  2. https://support.appharbor.com/discussions/problems/79727-error-msb3202-when-building
  3. https://support.appharbor.com/discussions/problems/78633-cant-build-aspnet-mvc-project-generated-from-vstudio-2015-enterprise#comment_37577678

How to Find a Stored Procedure in all the Databases?

When working with MS SQL Server sometimes you need to find a stored procedure in the databases and all that you know is the name of the stored procedure that your teammate told you that he had used but couldn’t remember in which database it was. All the information about the stored procedures created is saved in the master database. So, if you have access to the master database, you can run the following query to get information about the stored procedure.

select * 
from 
   sys.procedures 
where 
   name like '%name_of_proc%'

This will tell you in which schema you will find the stored procedure. Even, if you want to look into the code you can do so by running the following query.

select text 
from 
    syscomments c
    inner join sys.procedures p on p.object_id = c.id
where 
    p.name like '%name_of_proc%'

The text field contains the code of the stored procedure.

Let me know in the comments if you find this helpful or if you have any other ways to find the stored procedures.

Searching Lowercase data in MS SQL Server

A few days back I came across a requirement to fetch the records from the table having all lowercase letters. This was an interesting scenario. The SQL Server database by default is set for case-insensitive. But, I need to do a case-sensitive search. Case-sensitive search in SQL Server can be achieved in two ways. Either by using COLLATE or by using BINARY_CHECKSUM().

  • COLLATE is the T-SQL clause used to define collation.
  • BINARY_CHECKSUM() is a built-in system function used to compare the binary checksum value.

In this article, I will show you how to use both options. In addition to the lowercase search, I’ll show you the uppercase search and mixed case search as well. 

To start with the experiment, let’s create a table.

CREATE TABLE [dbo].[MyTecBits_Table_1](

[Sl_no] [int] IDENTITY(1,1) NOT NULL,

[Name] [varchar](50) NULL,

[Description] [varchar](500) NULL

) ON [PRIMARY]

GO

Insert records some with all lowercase, some with all uppercase and some with mixed case.

insert into MyTecBits_Table_1 values (‘MYTECBITS’,’THISWEBSITEHASBITSANDPIECESOFTECHNICALINFORMATION’)

insert into MyTecBits_Table_1 values (‘mytecbits’,’thiswebsitehasbitsandpiecesoftechnicalinformation’)

insert into MyTecBits_Table_1 values (‘MyTecBits’,’ThisWebSiteHasBitsAndPiecesOfTechnicalInformation’)

insert into MyTecBits_Table_1 values (‘MY TEC BITS’,’THIS WEB SITE HAS BITS AND PIECES OF TECHNICAL INFORMATION’)

insert into MyTecBits_Table_1 values (‘my tec bits’,’this web site has bits and pieces of technical information’)

insert into MyTecBits_Table_1 values (‘My Tec Bits’,’This Web Site Has Bits And Pieces Of Technical Information’)

SQL Server Case-Sensitive Search

 

Now our test table with test strings with different case types is ready for our experiment.

Fetching all lowercase records:

Fetching only the rows having all lowercase in one of the columns using COLLATE method.

select * from MyTecBits_Table_1 where Name COLLATE Latin1_General_CS_AI = Lower(Name)

Fetching only the rows having all lowercase in one of the columns using the BINARY_CHECKSUM() method.

select * from MyTecBits_Table_1 where BINARY_CHECKSUM(Name) = BINARY_CHECKSUM(Lower(Name))

See the results for lower case search:

SQL Server Case-Sensitive Search

 

Fetching all uppercase records:

Similar to fetching the lowercase string, we can search for an uppercase string just by changing the LOWER string function to the UPPER string function. See the example below.

Fetching only the rows having all uppercase in one of the columns using COLLATE method.

select * from MyTecBits_Table_1 where Name COLLATE Latin1_General_CS_AI = Upper(Name)

Fetching only the rows having all uppercase in one of the columns using the BINARY_CHECKSUM() method.

select * from MyTecBits_Table_1 where BINARY_CHECKSUM(Name) = BINARY_CHECKSUM(Upper(Name))

See the results for upper case search:

SQL Server Case-Sensitive Search

Fetching only the mixed case records:

Just like uppercase and lowercase sensitive search, we can search for mixed case records by combining upper and lowercase search conditions.

Fetching only the rows having mixed cases in one of the columns using COLLATE method.

select * from MyTecBits_Table_1 where Name COLLATE Latin1_General_CS_AI != Upper(Name) and Name COLLATE Latin1_General_CS_AI != Lower(Name)

Fetching only the rows having all mixed cases in one of the columns using the BINARY_CHECKSUM() method.

select * from MyTecBits_Table_1 where BINARY_CHECKSUM(Name) != BINARY_CHECKSUM(Upper(Name)) and BINARY_CHECKSUM(Name) != BINARY_CHECKSUM(Lower(Name))

See the results for mixed case search:

SQL Server Case-Sensitive Search

Click here to read more on the Databases.

[This article was originally published here]

Converting Oracle DATE types and PHP/Unix Timestamps

If you need to convert between Oracle date and PHP/Unix timestamp then you might need to implement two functions in Oracle.

The following two Oracle functions implement this for DATE types.

To convert a UNIX timestamp into an Oracle DATE type:

CREATE OR REPLACE
    FUNCTION unixts_to_date(unixts IN PLS_INTEGER) RETURN DATE IS
        /**
         * Converts a UNIX timestamp into an Oracle DATE 
         */
        unix_epoch DATE := TO_DATE('19700101000000','YYYYMMDDHH24MISS');
        max_ts PLS_INTEGER := 2145916799; -- 2938-12-31 23:59:59
        min_ts PLS_INTEGER := -2114380800; -- 1903-01-01 00:00:00
        oracle_date DATE;

        BEGIN

            IF unixts > max_ts THEN
                RAISE_APPLICATION_ERROR(
                    -20901,
                    'UNIX timestamp too large for 32 bit limit'
                );
            ELSIF unixts < min_ts THEN
                RAISE_APPLICATION_ERROR(
                    -20901,
                    'UNIX timestamp too small for 32 bit limit' );
            ELSE
                oracle_date := unix_epoch + NUMTODSINTERVAL(unixts, 'SECOND');
            END IF;

            RETURN (oracle_date);

    END;

The following PHP script shows how this might be used. Note that this script requires PHP 5.x+, as it uses the new OCI extension function names:

<?php
$conn = oci_connect('scott', 'tiger');

$sql = "
    SELECT
        *
    FROM EMP
    WHERE
        hiredate
    BETWEEN
        unixts_to_date(:startdate)
    AND
        unixts_to_date(:enddate)
    ORDER BY
        hiredate
    DESC
    ";

$stmt = oci_parse($conn, $sql);

// Bind a UNIX timestamps to :startdate and :enddate
oci_bind_by_name($stmt, ":startdate", mktime(0,0,0,1,1,1981));
oci_bind_by_name($stmt, ":enddate", mktime(0,0,0,1,1,1990));

oci_execute($stmt);

print "NAME  : HIREDATE\n";
while ( $row = oci_fetch_assoc($stmt) ) {
   print "{$row['ENAME']} : {$row['HIREDATE']}\n";
}
oci_free_statement($stmt);

oci_close($conn);
?>

In reverse, the following function returns a UNIX timestamp given an Oracle DATE type:

CREATE OR REPLACE
    FUNCTION date_to_unixts(oracle_date IN DATE) RETURN PLS_INTEGER IS
        /**
         * Converts an Oracle DATE to a UNIX timestamp
         */
        unix_epoch DATE := TO_DATE('19700101000000','YYYYMMDDHH24MISS');
        max_date DATE := TO_DATE('20380101000000','YYYYMMDDHH24MISS');
        min_date DATE := TO_DATE('19030101000000','YYYYMMDDHH24MISS');
        unix_ts PLS_INTEGER;

        BEGIN

            IF oracle_date > max_date THEN
                RAISE_APPLICATION_ERROR( -20902,'Date too large for 32bit UNIX timestamp' );
            ELSIF oracle_date < min_date THEN
                RAISE_APPLICATION_ERROR( -20902,'Date too small for 32bit UNIX timestamp' );
            ELSE
                unix_ts := (oracle_date - unix_epoch) / (1/86400);
            END IF;

            RETURN (unix_ts);

        END;

The following query shows how it might be used:SELECT
    ename,
    TO_CHAR(hiredate, 'YYYY') AS hired_year,
    TO_CHAR(hiredate, 'YYYYMM') AS hired_month,
    TO_CHAR(hiredate, 'ddth Mon, YYYY') AS hired_pretty
    date_to_unixts(hiredate) AS hired_unixts
FROM
    emp
ORDER BY
    hiredate

It’s now easy to convert the timestamp into a formatted date, using the date() function as you loop through the result set.

Working with Dates and Times in Oracle and PHP

Both PHP and Oracle provide functionality manipulating dates and times. Which to use and when?

If you’re new to PHP or Oracle, working out how to handle dates efficiently can be tricky. You may have strategies, which you’ve applied successfully on other platforms, but will they fit the combination of Oracle and PHP?

This Oracle+PHP recipe should help you understand the functionality, available in both PHP and Oracle, for working with dates and times and how they relate to each other. In doing so, it should help you decide where to draw a line in deciding which technology will handle what, and answer questions like “Do I calculate the difference between these two dates in PHP or Oracle?”

Dates and Times in Oracle

Oracle provides three data types for storing date/time values:

  • The DATE type, represents a date and time. A value stored in a DATE field contains “components” corresponding to the century, year, month, day, hour, minute and second. Dates can be anywhere in the range from January 1, 4712 B.C., to December 31, 9999 A.D.
  • The TIMESTAMP type, available since Oracle9i, is effectively an extended form of the DATE type and complies with ANSI SQL. It provides greater precision in time, supporting fractions of a second up to nine places and is also capable of storing time zone information.
  • The INTERVAL type, since Oracle9i, which supports storage of a time difference such as “two years and five months” or “three days, 18 hours and 45 minutes” and can be summed with a DATE or TIMESTAMP to produce a new DATE / TIMESTAMP value.

The focus here will be on the DATE type, although much that applies to DATE also applies to TIMESTAMP. (For more background about TIMESTAMP and INTERVAL types, read Jonathan Gennick’s Oracle Magazine articles ” Datetime Datatypes Add Precision” and ” Finding the Time in Between” (both published in the Nov.-Dec. 2002 issue).

How Oracle Stores DATEs. The first thing to grasp about the DATE type in Oracle is that its internal representation allows it to be displayed and manipulated in many different ways. It is effectively independent of any specific string format. If you SELECT a DATE type, Oracle automatically converts it to a readable string, but this is not how the value is actually been stored.

Selecting the current system time using SYSDATE, which returns a value of type DATE and is the current date and time set for the operating system on which the database resides :

SELECT SYSDATE FROM dual /* e.g. 25-JUL-05 */

The format is controlled by the Oracle parameter NLS_DATE_FORMAT and can change on a session basis (see below). To get a feeling for the internal representation:

SELECT DUMP(SYSDATE) FROM dual /* e.g. Typ=13 Len=8: 213,7,7,25,23,7,15,0 */

Comma-separated values in the result correspond to the bytes Oracle uses to store each component of a date and time, from century down to second One important note here; when comparing DATE types, all the DATE’s components will be compared, down to the seconds. In some cases, you may want to compare two dates on a different basis, such as the year, month or day. In such cases functions like TRUNC can be useful to round down the hours, minutes and seconds components of the two DATEs you are comparing. See ” Date Arithmetic” below for more detail.

If you’re familiar with OOP, it may also be helpful to think of DATE types as objects. They possess both properties (year, month, hour etc.) and behaviour, such as

SELECT SYSDATE - 7 FROM dual /* e.g. 18-JUL-05 */

This returns the date seven days ago. Further “behaviour” includes DATE comparisons, which implies you canSORT BY, GROUP BY, find dates BETWEEN and so on, and subtraction: subtract one DATE from another to get the integer difference in days (or an INTERVAL type, when using TIMESTAMP values).

Converting Between DATE Types and Strings. The TO_DATE() and TO_CHAR() functions are used to convert between Oracle DATE “objects” and human-readable date strings. Both functions take three arguments; the value to convert, an optional format mask and an optional string to identify a language (e.g. FRENCH). Conceptually the format mask is similar to a regular expression; you specify a pattern for a date, which tells Oracle how to relate a matching string to a DATE type. The format mask is described in the Oracle Database SQL Reference under ” Format Models.”

Working with TO_CHAR. Here’s a simple example, again using the SYSDATE function:

SELECT TO_CHAR( SYSDATE, 'YYYY-MM-DD HH24:MI:SS' ) FROM dual
    /* e.g. 2005-07-26 17:34:04 */

Looking at the format mask in detail, the’YYYY’ denotes a four-digit year, ‘MM’ a two-digit month, ‘DD’ a two-digit day of the month, ‘HH24’ the hours in a 24-hour clock, ‘MI’ the minutes between 0 and 59, and ‘SS’ the seconds between 0 and 59. Note the following characters are passed from the format mask into the output “as-is”:

/ - , . ; :

Further strings can be “passed through” by enclosing them in quotes:

SELECT TO_CHAR(SYSDATE, '"The time is now " HH24:MI:SS "precisely"') FROM dual
    /* e.g. The time is now 17:38:22 precisely

There are many more format mask patterns available to address a wide range of use cases, as you will find in the documentation.

Note: TO_CHAR can also be used with TIMESTAMP types.

Working with TO_DATE. Oracle can parse strings into DATE types, using the same format masks as TO_CHAR. Given a string like 20050726173102:

SELECT TO_DATE( '20050726173102', 'YYYYMMDDHH24MISS' ) FROM dual

Or to convert “Jul 26, 2005 17:13:05”, I can use:

SELECT TO_DATE('Jul 26, 2005 17:13:05', 'Mon DD, YYYY HH24:MI:SS') FROM dual

Note: ForTIMESTAMP types the equivalent function isTO_TIMESTAMP.

Changing the default date format. Oracle displays DATE types, by default, according to the format mask defined in the NLS_DATE_FORMAT parameter. This can be changed in the session like:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'

Date arithmetic. To determine the date six days after the 26th July, 2005, I simply add the value 6 to the DATE object:

SELECT TO_DATE( '20050726173102', 'YYYYMMDDHH24MISS' ) + 6 FROM dual
    /* e.g. 2005-08-01 17:13:05 */

The smallest whole unit for this type of operation is a single day. To subtract 18 hours, I need the appropriate fraction of a day:

SELECT TO_DATE( '20050726173102', 'YYYYMMDDHH24MISS' ) - (1/24 * 18) FROM dual
    /* e.g. 2005-07-25 23:31:02 */

Similarly, to add 59 seconds:

SELECT TO_DATE( '20050726173102', 'YYYYMMDDHH24MISS' ) + (1/(24*60*60) * 59) FROM dual

To work in months or years, given that neither can be expressed in a constant number of days (bearing in mind given leap years and months with different numbers of days), you need theADD_MONTHS function. To add twelve months to date:

SELECT ADD_MONTHS( TO_DATE( '20050726173102', 'YYYYMMDDHH24MISS' ), 12) FROM dual
    /* e.g. 2006-07-26 17:31:02 */

Note: To subtract months, use a negative sign.

The LAST_DAY function returns the last day of the month for a DATE type:

SELECT LAST_DAY( TO_DATE( '20050701', 'YYYYMMDD' ) ) FROM dual
    /* e.g. 2005-07-31 00:00:00 */

TheTRUNC function rounds down theDATE, according to the date mask it is provided as the second argument. You might use it when making DATE comparisons where you want to eliminate units such as seconds and minutes from the comparison:

SELECT TRUNC( TO_DATE( '20050726173102', 'YYYYMMDDHH24MISS' ), 'DD' ) FROM dual
    /* e.g. 2005-07-26 00:00:00 */

If no date mask is provided, TRUNC will round down the DATE to the start of the day it represents.

Other date-related functions include MONTHS_BETWEEN, for the integer difference in months between two DATE types, NEXT_DAY, to obtain a DATE type of the next weekday matching a string literal (for example, ‘MONDAY’), and ROUND, similar to TRUNC, but returning the nearest DATE rounded up or down.

Building SQL Statements using Dates

Some simple examples of using the DATE type in an SQL statement, using the “emp” (employees) table (part of the sample data that comes with an Oracle installation). The “hiredate” column of the “emp” table stores values using the DATE type.

Locating all employees hired between two dates:

SELECT
    ename, TO_CHAR(hiredate, 'ddth Mon, YYYY')
FROM
    emp
WHERE
    hiredate
BETWEEN
    TO_DATE('1980-01-01','YYYY-MM-DD')
AND
    TO_DATE('1985-01-01','YYYY-MM-DD')
ORDER BY
    hiredate
DESC

Adding a new employee:

INSERT INTO emp
   (
        empno,
        ename,
        job,
        mgr,
        hiredate,
        sal,
        deptno
    )
VALUES
   (
        8002,
        'JOHNSON',
        'ANALYST',
        7566,
        TO_DATE('2005-07-22','YYYY-MM-DD'),
        3000,
        20
    )

Finding all employees who have been with the company for more than 15 years, using an INTERVAL type returned from the TO_YMINTERVAL function:

SELECT
    *
FROM
    emp
WHERE
    SYSDATE - TO_YMINTERVAL('15-00') >  hiredate

Dates and Times in PHP

A list of all available functions, based on the UNIX timestamp, can be found in the PHP Manual in the Date and Time Functions section. The focus here will be the date() and mktime() functions, which will typically be the functions you use most. Note that PHP version 5.1.x introduces additional date-related functionality, while the examples here restrict themselves to operations available in earlier PHP versions 4.3.x and 5.0.x, although they are also forwards compatible with PHP 5.1.x.

The raw material of dates and times in PHP is the UNIX timestamp; the number of seconds before or after the UNIX epoch, which occurred at 00:00:00 UTC (GMT) on January 1, 1970. To see a UNIX timestamp, simply print the result of PHP’s time() function; which is equivalent to Oracle’s SYSDATE:

<?php
print time(); // e.g. 1122391862
?>

To add or subtract units of days, hours, and minutes, you convert the unit to seconds and apply it directly to the timestamp. Performing arithmetic using units of month and year requires the mktime() function (see below).

Formatting UNIX timestamps. The date() function is used to format UNIX timestamps:

<?php
print date('Y-m-d H:i:s',time()); // e.g. 2005-07-26 17:31:02
?>

The masks for the date() function are well documented in the manual. Any characters that date() does not recognize are automatically “passed through” into the output, such as punctuation characters. Characters, which could be mistaken for part of the format, can be escaped with a backslash, for example:

<?php
print date('l \t\he jS of F',1122391862); // e.g. Tuesday the 26th of July
?>

Resulting in “Tuesday the 26th of July.”

Note: If you place the format mask inside double quotes you may need a double backslash to escape certain characters; see the PHP Manual on string types and double quotes for details.

The date() function can also be useful for certain calculations:

<?php
$years = range(2005, 2020);
foreach ( $years as $year ) {
    if ( date('L', mktime(0,0,0,1,1,$year) ) ) {
        print "$year is a leap year\n";
    }
}
?>

Which tells me:
    2008 is a leap year
    2012 is a leap year
    2016 is a leap year
    2020 is a leap year

Note also the gmdate() function, which is almost exactly the same as the date() function except that it converts the result to Greenwich Mean Time (UTC).

Creating and manipulating UNIX timestamps. The mktime() function is used to generate a UNIX timestamp from a date, given integers values which represent the components of the date. You could regard mktime() as the parallel to Oracle’s TO_DATE function. For example:

<?php
$year = 2005;
$month = 7;
$day = 27;
$hour = 12;
$minute = 34;
$second = 43;

print mktime($hour, $minute, $second, $month, $day, $year);
?>

The mktime() function takes care of adjustments required when boundaries, such as that between months are crossed:

<?php
$year = 2005;
$month = 7;
$day = 27 + 10; // Add 10 days
$hour = 12;
$minute = 34;
$second = 43;

print date('Y-M-d H:i:s',mktime($hour, $minute, $second, $month, $day, $year));
    // e.g. 2005-Aug-06 12:34:43
?>

Here, attempting to give mktime()”July 37″ results in the correct adjustment into August.

To find the last day of a given month, you can use the proceeding month along with a zero value as the day of that month. For example:

<?php
$year = 2005;
$month = 8; // August
$day = 0; // Last day of July
$hour = 12;
$minute = 34;
$second = 43;

print date('Y-M-d',mktime($hour, $minute, $second, $month, $day, $year));
?>

As with the date() function, there’s also a gmmktime() function which also adjusts to GMT.

Problems with dates and time in PHP. One limitation of UNIX timestamps is they are tied to the 32-bit limit of today’s mainstream CPUs and operating systems. It means you can only represent a range of dates up to the year 2038 and back to 1902 on UNIX platforms. If this limitation poses a problem for your application, you may find the PEAR Date library a useful, albeit slower, alternative.

Another problem area is localization; the date() function only supports English weekday and month names. You may find the easiest practical solution, if you only need to support a handful of languages, is to translate the names using associative arrays.

Note that work is in progress to enhance PHP’s native date and time functions. You’ll find this outlined by Derick Rethans, one of the core PHP developers, in his PHP Time Handling talk which he gave at ApacheCON 2005 in Germany.

Drawing the Line

So the question is where do you draw the line? Where do you place the responsibility for handling date-related operations? In PHP or Oracle? This section discusses the options, to help you make informed decisions.

In general, Oracle’s date facilities are more powerful than PHP’s, allowing greater flexibility when it comes to parsing date strings or formatting date output, thanks to a wider range of date formatting masks. Add to that the 32-bit limitation of the UNIX timestamp, that TIMESTAMP can store time zones and localization issues and you may consider passing off all date-related work to Oracle. It should be pointed out though that many Web applications, particularly intranet applications, only target a single locale region, so operating within a single language and time zone. Also, the range of a UNIX timestamp is often more than enough of the type of data the application will be handling. In such cases, PHP’s date-time functions should pose no problems.

Date storage. Considering date and time storage, with some databases it may be necessary to use UNIX timestamps such as PHP5’s built-in SQLite. With Oracle, it’s better to use DATE or TIMESTAMP as your column types, for storage for data and time values. The supporting functionality makes date operations easy, particularly when SELECTing based on DATEs and TIMESTAMPs. You’ll also find visually checking dates becomes much easier, as tools like SQL*Plus automatically display dates in a human-readable form.

Date formatting. One argument against Oracle’s date formatting capabilities, when thinking in terms of a layered architecture, is that formatting output in your application’s data storage layer is “bad practice”, this being the job of the presentation layer.

If you’re in the (unusual) position of writing an application to run against multiple database implementations, this may be a valid argument. In such cases you may well want to investigate what John Lim has provided ADOdb to help with this problem—see his “Tips on Writing Portable SQL”, “Data Types” section.

More commonly, you’ll be developing against a single database, so vendor abstractions won’t be an issue. You might want to consider having Oracle format a date for you in a variety of ways, rather than trying to massage dates in PHP, as the following query suggests:

SELECT
    ename,
    TO_CHAR(hiredate, 'YYYY') AS hired_year,
    TO_CHAR(hiredate, 'YYYYMM') AS hired_month,
    TO_CHAR(hiredate, 'DD Mon, YYYY') AS hired_pretty,
    TO_CHAR(hiredate, 'DD Mon, YYYY', 'NLS_DATE_LANGUAGE=GERMAN') AS hired_german
FROM
    emp
ORDER BY
    hiredate

Alternatively (or in addition), returning a UNIX timestamp as part of the result fits nicely with PHP’s date() function (see below). Some of Oracle’s format masks, such as “Month”, pads the output with space characters so using date() in such instances may mean fewer lines of code.

Converting Oracle DATE types and Unix Timestamps. The following two Oracle functions implement this for DATE types.

To convert a UNIX timestamp into an Oracle DATE type:

CREATE OR REPLACE
    FUNCTION unixts_to_date(unixts IN PLS_INTEGER) RETURN DATE IS
        /**
         * Converts a UNIX timestamp into an Oracle DATE 
         */
        unix_epoch DATE := TO_DATE('19700101000000','YYYYMMDDHH24MISS');
        max_ts PLS_INTEGER := 2145916799; -- 2938-12-31 23:59:59
        min_ts PLS_INTEGER := -2114380800; -- 1903-01-01 00:00:00
        oracle_date DATE;

        BEGIN

            IF unixts > max_ts THEN
                RAISE_APPLICATION_ERROR(
                    -20901,
                    'UNIX timestamp too large for 32 bit limit'
                );
            ELSIF unixts < min_ts THEN
                RAISE_APPLICATION_ERROR(
                    -20901,
                    'UNIX timestamp too small for 32 bit limit' );
            ELSE
                oracle_date := unix_epoch + NUMTODSINTERVAL(unixts, 'SECOND');
            END IF;

            RETURN (oracle_date);

    END;

The following PHP script shows how this might be used. Note that this script requires PHP 5.x+, as it uses the new OCI extension function names:

<?php
$conn = oci_connect('scott', 'tiger');

$sql = "
    SELECT
        *
    FROM EMP
    WHERE
        hiredate
    BETWEEN
        unixts_to_date(:startdate)
    AND
        unixts_to_date(:enddate)
    ORDER BY
        hiredate
    DESC
    ";

$stmt = oci_parse($conn, $sql);

// Bind a UNIX timestamps to :startdate and :enddate
oci_bind_by_name($stmt, ":startdate", mktime(0,0,0,1,1,1981));
oci_bind_by_name($stmt, ":enddate", mktime(0,0,0,1,1,1990));

oci_execute($stmt);

print "NAME  : HIREDATE\n";
while ( $row = oci_fetch_assoc($stmt) ) {
   print "{$row['ENAME']} : {$row['HIREDATE']}\n";
}
oci_free_statement($stmt);

oci_close($conn);
?>

In reverse, the following function returns a UNIX timestamp given an OracleDATE type:

CREATE OR REPLACE
    FUNCTION date_to_unixts(oracle_date IN DATE) RETURN PLS_INTEGER IS
        /**
         * Converts an Oracle DATE to a UNIX timestamp
         */
        unix_epoch DATE := TO_DATE('19700101000000','YYYYMMDDHH24MISS');
        max_date DATE := TO_DATE('20380101000000','YYYYMMDDHH24MISS');
        min_date DATE := TO_DATE('19030101000000','YYYYMMDDHH24MISS');
        unix_ts PLS_INTEGER;

        BEGIN

            IF oracle_date > max_date THEN
                RAISE_APPLICATION_ERROR( -20902,'Date too large for 32bit UNIX timestamp' );
            ELSIF oracle_date < min_date THEN
                RAISE_APPLICATION_ERROR( -20902,'Date too small for 32bit UNIX timestamp' );
            ELSE
                unix_ts := (oracle_date - unix_epoch) / (1/86400);
            END IF;

            RETURN (unix_ts);

        END;

The following query shows how it might be used:SELECT
    ename,
    TO_CHAR(hiredate, 'YYYY') AS hired_year,
    TO_CHAR(hiredate, 'YYYYMM') AS hired_month,
    TO_CHAR(hiredate, 'ddth Mon, YYYY') AS hired_pretty
    date_to_unixts(hiredate) AS hired_unixts
FROM
    emp
ORDER BY
    hiredate

It’s now easy to convert the timestamp into a formatted date, using the date() function as you loop through the result set.

Conclusion

You have now been introduced to (or reminded of) the date-time functionality available in both Oracle and PHP. You should now have a foundation for working with dates and times in your Oracle / PHP applications. You should also have gained a fair idea of the design and implementation decisions you’ll face when handling dates.

[Originally published at http://www.oracle.com/technetwork/articles/fuecks-dates-098686.html]

Allowing Oracle ports in Windows Firewall

If you plan to install Oracle Database XE onto a computer running Windows Firewall, which was first introduced in Windows XP Service Pack 2 and Windows Server 2003 Service Pack 1, and then connect to it from another computer, check that the firewall has not been configured to block communication from the following incoming ports. These ports are the default ports that Oracle Database XE users.

  • 1521: Oracle Database Listener
  • 2030: Oracle Services for Microsoft Transaction Server
  • 8080: Oracle HTTP Transaction Server

To allow these ports follow the instructions below.

  1. Open Control Panel from theStart menu.
  2. Select Windows Firewall.open-port-windows-7-firewall-1Note: If Windows Firewall is not available, change View by to Large iconsat the top right of the Control Panel window.
  3. Select Advanced settings in the left column of the Windows Firewall window.open-port-windows-7-firewall-2
  4. Select Inbound Rules in the left column of the Windows Firewall with Advanced Security window.open-port-windows-7-firewall-3Note: You can prevent outbound traffic by selecting Outbound Rules.
  5. Select New Rule in the right column.open-port-windows-7-firewall-4
  6. Select Port in the New Inbound Rule Wizard and then click Next.open-port-windows-7-firewall-5
  7. Select which protocol this rule will apply to (TCP or UDP), select Specific local ports, type a port number (80), port numbers (80,81), or a range of port numbers (5000-5010) and then click Next.open-port-windows-7-firewall-6
  8. Select Allow the connection and then click Next.open-port-windows-7-firewall-7
  9. Select when this rule applies (check all of them for the port to always stay open) and then click Next.open-port-windows-7-firewall-8
  10. Give this rule a name and then click Finish to add the new rule.open-port-windows-7-firewall-9

The port is now open and ready to be used.

[Ref: http://docs.oracle.com/cd/E17781_01/install.112/e18803/toc.htm#BABEBCDB]
[Ref: http://maximumpcguides.com/windows-7/open-a-port-in-windows-7s-firewall/]

Xdebug in WebMatrix

Xdebug is a very popular PHP extension that helps with debugging and profiling of PHP scripts by providing a lot of valuable debug information. Microsoft WebMatrix is a development tool for building web applications. When WebMatrix is used to build or modify a PHP-based web application the debugging tasks can be greatly simplified if Xdebug extension is used. This post explains how to install and use Xdebug extension with WebMatrix.

Step 1: Enable PHP in WebMatrix from the site “Settings” page:

Note that if you installed any of the PHP applications from the Application Gallery then PHP will be automatically enabled.

Step 2: Download the appropriate build of Xdebug extension from downloads page. If your site uses PHP 5.2 then download “5.2 VC6 Non-thread safe (32 bit)”. If your site uses PHP 5.3 then download “5.3 VC9 Non-thread safe (32 bit)”. Use 32 bit build even if your Windows OS is 64 bit.

Step 3: Install the extension by copying the downloaded file to the following locations:

  • For PHP 5.2 on Windows 64 bit:
    C:\Program Files (x86)\IIS Express\PHP\v5.2\ext\
  • For PHP 5.2 on Windows 32 bit:
    C:\Program Files\IIS Express\PHP\v5.2\ext\
  • For PHP 5.3 on Windows 64 bit:
    C:\Program Files (x86)\IIS Express\PHP\v5.3\ext\
  • For PHP 5.3 on Windows 32 bit:
    C:\Program Files\IIS Express\PHP\v5.3\ext\

Step 4: Open the php.ini file located in the PHP installation folder, e.g.
C:\Program Files\IIS Express\PHP\v5.2\php.ini and append the following at the end (make sure that the absolute path is correct for your version of PHP and Windows.):

[xdebug]
zend_extension = C:\Program Files\iis express\PHP\v5.2\ext\php_xdebug-2.1.0-5.2-vc6-nts.dll

Step 5: Configure PHP to display errors by changing these PHP settings in php.ini file:

display_errors = On
error_reporting = E_ALL & ~E_NOTICE

Step 6: Test that extension is enabled and works by either calling a phpinfo() function from a script or by running a buggy script:

The Xdebug extension provides a lot of useful features that help with debugging of PHP applications. You can learn more about them from the Xdebug documentation. For example you can use it to profile a PHP application. Just change the php.ini file as shown below and then make a request to a PHP script:

[xdebug]
zend_extension = C:\Program Files\iis express\PHP\v5.2\ext\php_xdebug-2.1.0-5.2-vc6-nts.dll
xdebug.profiler_enable = On
xdebug.profiler_output_dir = C:\Windows\temp

The profile log will be saved into the specified directory and will have a name cachegrind.out.* Use WinCacheGrind to open and analyze it: