DaytaBase.org

I will be blogging SQL syntax at http://DaytaBase.org from now on, but may make plans for Crossed Logic in the future. For now, this will be an archive to maintain any links from Articles. For new content, please go to http://DaytaBase.org.

Thanks!

A MySQL Tidbit: Dynamic Export To_XML Stored Procedure

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL

Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of. So the topics of parsing and manipulating XML, importing and exporting XML files, etcetera using SQL are pretty commonplace here at Experts Exchange.

Consequently, in two of my previous MySQL tidbits, I covered some real questions from EE's Q&A forum:

A MySQL Tidbit: In-line XML Parsing

A MySQL Tidbit: In-line CSV Parsing (Using XML)

While writing those articles and participating in the mentioned questions, I was urged by a fellow database expert to write some tips on the importing and exporting of XML, specifically in SQL using MySQL database server. As a result, this particular installment of my SQL tidbits will cover: (1) dynamically constructing XML elements from a given table structure; and (2) exporting a result set to XML file.

The end product will be a technique you can customize and test for your own XML needs. This technique will be shown in a stored procedure. For beginning MySQL readers, I will be providing some background tips -- explanations on some of the inner workings of the SQL code in the procedure...

Read more of "A MySQL Tidbit: Dynamic Export To_XML Stored Procedure" on Experts-Exchange.com/articles...

Enjoy!

A SQL Tidbit: Conditional Aggregates

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time!

By the end of my Experts-Exchange Article, A SQL Tidbit: Conditional Aggregates, it is my intention to bring the meaning and value of the above quote to those who chose to read this whether as a beginning SQL programmer or an intermediate one inexperienced with this little tidbit of SQL syntax: conditional aggregation.

Conditional aggregation is simply the use of aggregates under one or more conditions, thus, potentially altering your results of functions like SUM(), COUNT(), etcetera in a particular column while allowing you to analyze your record set as a whole...

Read more about my SQL tidbit on Conditional Aggregates at Experts-Exchange.com/articles...

Expiration Date Validation Example

Here is a simple example of a utility that can validate expirations dates like '0609' or '0710' as being valid expiration dates (i.e., is the date before the current month or not).

/**
 * Utility function to determine if a four character expiration date is valid OR not!
 * 
 * @param s
 * <p>Date string in format MMyy.  e.g., '0609' as date 'June 2009'.</p>
 * @return <code>boolean</code> flag indicating if expiration date is valid or not.
 */
public static boolean isValidExpiration(String s) {

    // use SimpleDateFormat to parse values like '0609' as date 'June 2009'
    java.text.DateFormat sdf = new java.text.SimpleDateFormat("MMyy");
    
    // establish current date as last day of previous month at 23:59:59
    java.util.Calendar now = java.util.Calendar.getInstance(); 
    now.set(now.get(java.util.Calendar.YEAR), now.get(java.util.Calendar.MONTH), 0, 23, 59, 59);

    try {
        // actual parsing of the date, wrapped in try/catch 
        // parses as 1st day of month at midnight since only providing month and year
        // e.g., '0609' becomes 'June 01, 2009 00:00:00'
        java.util.Date exp = sdf.parse(s);

        // if parsed date is before current month, then return invalid!
        if (exp.before(now.getTime())) return false;
    } catch (java.text.ParseException e) {
        return false;  // if not MMYY then reject
    }

    // valid if made it this far, can do in reverse by using !exp.before and return true above.
    return true; 
}

Other Experts Exchange Articles

SQL Syntax:

Mimic IsNumeric Functionality Using RegExp in MySQL

Pivot Reporting Tips for SQL Server: Handling Rolling Periods Without Using Dynamic SQL

Converting Active Directory Timestamps in Microsoft SQL Server

Java Programming:

Custom File Filtering Using Java File Choosers