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.

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]

How to change expired password in Oracle?

In Oracle when the password is expired you will get the following error:
ORA-28001: the password has expired
This is because the password has reached 180 Default limit for a Password lifetime.
Solutions:
1.  Connect to the database using sys users.
2. Execute the following query
Sql > select * from dba_profiles;
the output of this query will be like.
 
Here PASSWORD_LIFE_TIME field is responsible for expiring of the password after 180 days.
3.  Execute the following command to disable this feature:
Sql> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
4. Now crosscheck for disabling this feature.
Sql > select * from dba_profiles;
 
The value in PASSWORD_LIFE_TIME has changed to unlimited. Now password will never expire.
5. Now change the password of the locked user and unlock using the following.
sql> alter user [user_name] identified by [password];sql> alter user [User_name] account unlock;

6. Crosscheck by the value of accout_status field in dba_users view.

sql> select username,account_status from dba_users;

The value of the account_status filed should be “OPEN” for the corresponding user.

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]

INSERT new rows from a result of SELECT query

There were numerous times when I needed to insert records in the table from a result of another SELECT query, or copy data from one table to another. And every time I forgot the syntax of insert…select query. So I thought I should write it somewhere and blog is the best place for this 🙂

A very basic example of insert…select query is:

INSERT INTO tbl_temp2 (fld_id)
  SELECT tbl_temp1.fld_order_id
  FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

Use this as a reference and build your own query. You can use joins as well. If you use MySQL and want further details see http://dev.mysql.com/doc/refman/5.1/en/insert-select.html.

MySQL Installation and Configuration

Since I have been involved with MySQL development for last several years, so I constantly look for good resources related to MySQL. I found a very good article on this at IIS site (http://learn.iis.net/page.aspx/610/walkthrough—set-up-mysql-51-for-php-applications/). I thought I should refer that to my blog. I am providing whole article as it is here.

Overview

This article provides a basic step by step guide on how to install and configure MySQL on the Windows Operating System. For more detailed instructions about installing and configuring MySQL on Windows refer to the official MySQL documentation.

Downloading and Installing MySQL

The MySQL binaries and installer can be downloaded from the official MySQL site. The instructions in this article are based on MySQL version 5.1 Community Edition installed with the Windows MSI installer.

Run the installer and choose the installation option. For a majority of the cases, the typical installation is sufficient:

However, if you want to control which components get installed or if you want to use a non-default installation path then choose the “Custom” option.

When the installation is complete, make sure to check the box to “Configure the MySQL Server now”. This will launch the “MySQL Server Instance Configuration Wizard” that will guide you through the configuration process for the MySQL instance.

Configuring MySQL instance

Follow these steps in the “MySQL Server Instance Configuration Wizard” to optimize the MySQL configuration for the kind of tasks you expect it to perform.

On the first page of the wizard choose “Detailed Configuration”:

On the next page choose the server type option:

Choose the “Database Usage” option:

The Database usage options control what kind of database storage engine is used on the server:

  • MyISAM – Optimized for high performance SELECT operations. It has low overhead in terms of memory usage and disk utilization, but at the cost of not supporting transactions
  • InnoDB – Provides fully ACID transactional capabilities, but at the cost of more aggressive usage of disk space and memory

For an in-depth comparison of these database engines, refer to MySQL Storage Engine Architecture. As a general recommendation – if the web applications on your server require multi-statement transactions, advanced isolation levels and row-level locking, foreign key constraints, or otherwise have a requirement for ACID features — use InnoDB. Otherwise, use MyISAM.

Next choose the number of concurrent connections to the server:

On the next page choose networking options :

If you have mysql and web server on the same machine you may consider not enabling TCP/IP networking and instead use named pipes. Note though that some PHP applications may require TCP connection to MySQL. Refer to the application’s documentation to confirm if it supports named pipes connection to MySQL.

Choose the default charset to use when creating new databases:

Next ensure that MySQL will be configured as a Windows Service:

Optionally, you can add the MySQL Bin directory to the Windows PATH environment variable. That will make it easier to launch MySQL tools from the command line.

Finally provide the password for the database administrative account, which in called “root” in MySQL. Make sure that you leave the “Create an Anonymous Account” checkbox cleared:

On the next page click “Execute” to apply all the configuration settings and to start the MySQL service:

Now you can logon to MySQL by opening a command line window and typing:

mysql -u root -p
Enter password: ******If MySQL was configured correctly then the MySQL prompt will be shown:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server Version 5.1.32-community MySQL Community Server (GPL)
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql>