MS SQL Query XML with namespaces

In the past, I have not been a huge fan of Namespaces. If you don’t understand them, it is difficult to grasp the concept.

This link from Microsoft TechNet is a good start. I just needed a more real world example.

First, look at the Microsoft TechNet Article, then look at the more complex example I have below.

http://technet.microsoft.com/en-us/library/ms177400.aspx

Also, take a look at the W3Schools page about xml namespaces. They do have a purpose.

http://www.w3schools.com/xml/xml_namespaces.asp

So, here is an example. Complete with a small sample of XML and a SQL code.

This code as been tested Microsoft SQL Server 2005.

mssql_xmlnamespaces1

If there were no namespace, then the path would be much simpler.

Pin It
Posted in code, SQL, xml | 1 Comment

Tickld Mobile – 45 Ultimate Tips For Men. Number 40 Will Help You Go Far In Life.

http://m.tickld.com/x/45-man-tips

Pin It
Posted in Uncategorized | Leave a comment

Crazy Field Names In the Database

ackcat

I don’t know who named these fields, but it made me think of the Ack Cat when I worked with the following code.

Of particular interest is the variable named pod_pu_ack_diff.

Do you have any funny code that you have worked with? Any funny photos to go along with it?

Pin It
Posted in code, SQL | Leave a comment

VBScript Rest API

This week, I created a REST API to get the books of the bible.

Now, I know that this isn’t following all the rules, but the concept is getting closer. Instead of returning HTML, it may make sense to simply return JSON or XML and parse programmatically.

To illustrate, check out this VBScript.

Then, the resulting output is HTML, which could be parsed too, but could be done better.

Pin It
Posted in Uncategorized | Leave a comment

REST Sample with Database

I was looking into creating a REST API, but what is a REST API? It is a Representational state transfer (REST). Whatever that means. To me, it is a way to interface with an application.

I’m going to demonstrate a simple application that uses a MySQL database to serve the names of the books of the bible and then show some basic information about those books.

I based this code on a tutorial created by Jason Everett.

Rest API – A simple PHP tutorial.

I was inspired to create a tutorial that showed how to connect the application to a database. What I am about to present is very similar to the application that Jason Created.

It has two functions named get_app_by_id() and get_app_list().

Here is the code for the Client

The code for the Server is where all the exciting database code is.  Of course you will need to change some of the values for your database, such as the username, password, and the name of the database.

Here is a link to the working demo:

http://www.polysyncronism.com/RestAPI/RestDBClient.php

Pin It
Posted in code, MySQL, SQL | Leave a comment

Update MySQL table from Another Table

Lets say that you have two tables.

One table called tblUser and another table called tblBookTypes.

You have the Number of chapters in the tblUser table as the MAX of ChapterNum.

Now, lets say that you want to update the table tblBookTypes with the value in MaxChapter.

Here is how you would do it.

And that is how you can perform an Update Query in MySQL on one table, with the MAX value of a field from another table.

Pin It
Posted in code, MySQL | Tagged , | Leave a comment

Using Graphviz to display a picture in a node

Here is a simple example of how to display an image inside of a node.

graph1

Pin It
Posted in Uncategorized | Leave a comment

Automated mass cropping with ImageMagick

I’ve just used the ImageMagick program for the first time today. At first I was going with -crop, but -crop requires parameters to specify the size. Thanks to your post, I was inspired to find out what the -trim option did! Much to my surprise, it did exactly what I needed. It got rid of the unwanted white space.

lightspeedbanana – Automated Mass Cropping with ImageMagick

Pin It
Posted in Uncategorized | Leave a comment

Notepad++

One of my co-workers was talking about using Notepad++ to do some Regular Expression work. As soon as I heard that, my interest was peaked! The more I learned about Notepad++ the more I needed to see what it was about.


It seems that Notepad++ has been around for quite a while. I don’t know how I did not hear about it sooner. I guess because I am so comfortable with doing things from a command line, either in Windows or Unix that I didn’t think to upgrade my text editor.

I could spend a good deal of time talking about how great Notepad++ is, but there are others who have already done that work for me. check out tektip regex with notepad++ for example.

If you use git and want to use the git plugin for Notepad++, then see this page.
git with Notepad++

The process is fairly simple, but if you run into trouble, you may need to use the msysGit Full Install. I had to because I had some difficulty setting up the proxy so just downloading the full install was easier for me.

Pin It
Posted in code, Cool, Product Review, Regular Expressions | 1 Comment

MS SQL Week Numbers

Here are some detailed instructions on how to get the ISO week number using Microsoft SQL 2000. The functions described in this article are fn_ISOWeek, fn_ISOYear, fn_ListDates, and fn_IsLeapYear.

I have been working on some reports using MS SQL 2000 and needed to calculate the week number. Some of the legacy reports were using Datepart and while it did return a week number, it was not the week number that was needed.

http://xkcd.com/1179/

This cartoon has nothing to do with the week number, but I thought it interesting and wanted to include it here.  I like the format shown because it sorts nicely because it is in the format yyyy-mm-dd, but I digress.


So, my first thought was to look into ISO standards.

One of my first Google Searches was to look at Microsoft.com sites that had the keywords: mssql, iso, week, number 

Microsoft Technet – ISO Week Function

Now to do some tests to see if this function gives me what I want. Before I can do some tests, I need to define what I want. I then looked at Wikipedia to see what it had to say about it.

Wikipedia – ISO_week_date http://en.wikipedia.org/wiki/ISO_week_date

The First Week of a Year Rule

The first week of a year is the week that contains the first Thursday of the year. It is also (equivalently) the week containing the 4th day of January.

Excellent. Now I have something I can count on. This explains why 0104 is hard coded on line 8 of the fn_ISOWeek function shown above.

Now, I need to look at the calendar.

CalendarJan2014

Here is the SQL to see what week number I get for December 29, 2013

The result is 1

I also checked December 28, 2013 to confirm that it is week 52.

What I now wanted to do was to be able to look at more than 1 day programmatically. I don’t want to have to key in each day that I want to check.

So, I used the fn_ListDates function. This is a Table-valued Function that returns a list of the dates between two dates.

Now to test the fn_listDates function

Here is the result.

sql20140114B

Now, to use this function to test the range of dates using the fn_ISOWeek function.

And here is the result.

sql20140114A

After studying these results, I see a problem. I need my report to contain the correct year for week 1. In this data, I’m seeing December 29 – 31 as 2013, when they need to be considered as part of 2014.

This means that i’ll need to use a function to get the year based on the ISO Week Number.

Now, to test.

And the results

sql20140114c

The problem of the year is corrected. December 29 and 30 are considered part of 2014, even though they are 2013 dates.

Will this work on a leap year?

What are the rules to determine if the year is a leap year?

  • 1.If the year is evenly divisible by 4, go to step 2. Otherwise, go to step 5.
  • 2.If the year is evenly divisible by 100, go to step 3.
  • 3.If the year is evenly divisible by 400, go to step 4. Otherwise, go to step 5.
  • 4.The year is a leap year (it has 366 days).
  • 5.The year is not a leap year (it has 365 days).
  • IsLeapYearFlow

    so now that I have all the functions, I can look at the last few days of the year and the first few days of the year and see everything I need to see to check if the functions are working as I need them to.

    The final result.
    sql20140114d

    Pin It
    Posted in code, SQL, Tutorial | Tagged , , , | Leave a comment