Just this morning I came in and first thing I needed to do was check a stored procedure on my production website to make sure it was the same version as the stored procedure I’m working on locally. No big deal but the problem is the only computer I have that could connect with that SQL Server through Enterprise Manager crashed and burned on me a couple of days ago so I needed a solution while I’m waiting on the Help Desk to get to me. Enter sp_helptext, check out the MSDN definition.
exec sp_helptext 'dbo.storedProcedureName'
So I took this code and ran it in my little online SQL admin on my production website and got the production version of the Stored Procedure that I needed. Hope everyone finds this as helpful as I did.
Programming
Full disclosure, this was something I just decided to do with my afternoon and is a first pass at it. Also it is meant for SQL Server, not MySql or whatever other database server you’re running. That being said this procedure accepts a table name and returns all the rows of that database table as a nice JSON string. This is as basic as it gets though and could use a little real effort.
CREATE PROCEDURE dbo._ReturnJSON
@table_name nvarchar(50)
AS
DECLARE @i int
SET @i = 1
DECLARE @TableColumns TABLE
(
ColumnID int IDENTITY (1,1),
Column_Name nvarchar(256),
Data_Type nvarchar(256)
)
INSERT INTO @TableColumns
(Column_Name, Data_Type)
SELECT column_name, DATA_TYPE
FROM information_schema.columns I
WHERE TABLE_NAME = @table_name
DECLARE @first bit
SET @first = 1
DECLARE @column_name nvarchar(256)
DECLARE @data_type nvarchar(256)
DECLARE @sql nvarchar(4000)
SET @sql = 'SELECT ''{'
WHILE @i < (SELECT MAX(ColumnID) FROM @TableColumns)
BEGIN
SELECT @column_name = Column_Name, @data_type = Data_Type FROM @TableColumns WHERE ColumnID = @i
SET @i = @i + 1
IF @first = 1
IF @data_type <> 'nvarchar' AND @data_type <> 'varchar'
SET @sql = @sql + '"' + @column_name + '":"'' + CAST(' + 'ISNULL(X.' + @column_name + ', '''') AS nvarchar(50)) + ''"'
ELSE
SET @sql = @sql + '"' + @column_name + '":"'' + ' + 'ISNULL(X.' + @column_name + ', '''') + ''"'
ELSE
IF @data_type <> 'nvarchar' AND @data_type <> 'varchar'
SET @sql = @sql + ', "' + @column_name + '":"'' + CAST(' + 'ISNULL(X.' + @column_name + ', '''') AS nvarchar(50)) + ''"'
ELSE
SET @sql = @sql + ', "' + @column_name + '":"'' + ' + 'ISNULL(X.' + @column_name + ', '''') + ''"'
SET @first = 0
END
SET @sql = @sql + '}'' FROM information_schema.columns I INNER JOIN ' + @table_name + ' X ON I.table_name = ''' + @table_name + ''''
EXECUTE sp_executesql @sql
UPDATE: Okay so I couldn’t sit still till I did a little more and cleaned it up some so I cleaned up some of the script and then I added in the most basic of filtering to you could get just one row back if you wanted.
CREATE PROCEDURE dbo._ReturnJSON
@table_name nvarchar(50),
@WhereColumn nvarchar(256) = N'',
@WhereId nvarchar(256) = N'-1'
AS
DECLARE @i int
SET @i = 1
DECLARE @TableColumns TABLE
(
ColumnID int IDENTITY (1,1),
Column_Name nvarchar(256),
Data_Type nvarchar(256)
)
INSERT INTO @TableColumns
(Column_Name, Data_Type)
SELECT column_name, DATA_TYPE
FROM information_schema.columns I
WHERE TABLE_NAME = @table_name
DECLARE @first bit
SET @first = 1
DECLARE @column_name nvarchar(256)
DECLARE @data_type nvarchar(256)
DECLARE @sql nvarchar(4000)
SET @sql = 'SELECT DISTINCT ''{'
WHILE @i < (SELECT MAX(ColumnID) FROM @TableColumns)
BEGIN
SELECT @column_name = Column_Name, @data_type = Data_Type FROM @TableColumns WHERE ColumnID = @i
SET @i = @i + 1
IF @first = 1
SET @sql += '"' + @column_name
ELSE
SET @sql += ', "' + @column_name
SET @first = 0
IF @data_type <> 'nvarchar' AND @data_type <> 'varchar'
SET @sql += '":"'' + CAST(' + 'ISNULL(X.' + @column_name + ', '''') AS nvarchar(50)) + ''"'
ELSE
SET @sql += '":"'' + ' + 'ISNULL(X.' + @column_name + ', '''') + ''"'
END
SET @sql += + '}'' FROM information_schema.columns I INNER JOIN '
+ @table_name + ' X ON I.table_name = ''' + @table_name + ''''
IF @WhereColumn <> N''
SET @sql += ' WHERE X.' + @WhereColumn + ' = ''' + @WhereId + ''''
EXECUTE sp_executesql @sql
GO
Programming
I’ve recently had to convert a website from a Username and Password (Forms Authentication) scenario to Smart Card Authentication. This new task immediately brought to mind a couple of questions:
- How do I map current user accounts to the Smart Card?
- How do I efficiently read the Smart Card without overhauling my web site?
The first thing I had to do was figure out how to read the Smart Card. A little digging turned up the Request.ClientCertificate object. If you pull this back and just try to read it you get a jumbled mess but there are some key pieces of info here that are useful.
- First Name
- Last Name
- Identification Number (10-digit)
To get this info I have a nice little function…
Once I could read the Smart Card I needed to figure out what to do with the information and how it pertained to my existing users. I didn’t want to release the site and make everyone register all over again so behind the scenes I started capturing their Smart Card information after they logged in with their Username/Password. The Identification Number from the Smart Card just became another property of their User record.
So while I was capturing this Smart Card information I came up with a new log in scenario.
- A user visits the site and is prompted for his Smart Card and authenticating pin number.
- My function reads the Smart Card identification number and passes that number to the back-end database to see if it’s been collected before.
- If it has not then the user is redirected to a Registration page.
- If it has then the corresponding User record is returned.
- Once the user record is returned I authenticate in the same way I always have with a username.
I was able to keep Forms Authentication using this scenario as well as my original usernames for all the user accounts. This made sure I didn’t have to make any other back-end or programming changes. Since I would still be creating new users I had to come up with usernames for those new accounts. I opted to create them myself and used information I got from the smart card, [FIRST NAME].[LAST NAME].[LAST 4-DIGITS of the IDENTIFICATION NUM].
When I was first tasked with this and started my initial research this didn’t seem like it would be as easy as it was. There wasn’t much out there dealing with Smart Cards and what I did find made a bigger deal out of it than need be. Hopefully this is helpful.
Programming
Currently I’m working on a custom theme specifically for Crossfit boxes. It’s based on the TwentyEleven theme with modifications making it more Crossfit. The major change I’ve introduced so far is a custom post type for WODs. This will allow coaches to keep simple blog posts seperate from the daily WODs. This doesn’t seem like a big deal but it could allow online users to get a feed of only WODs without their other posts mixed in. I’m contemplating adding in another type for Nutrition but that will wait until later. I’m also working on a plug-in that will act as a sidebar widget for displaying a class schedule.
From a clean install I’ll provide the following pages:
- Home – a mix of blog posts and wods
- Coaches – a list of the coaches working at the box
- Contact – all the contact information the coaches will provide plus a contact form powered by Contact Form 7
- Gallery – gallery page using NextGEN Gallery and Slimbox
I’d also like to add in some social media features before I’m done but if you’re interested in using the theme for your box or just providing some feedback please feel free to do so.
Programming
For whatever reason you need custom post types in your blog. They’re pretty easy to setup and manage, I’ve found the key is to just make sure you do it right the first time. They’re installed through the plugins admin and that’s where the code will reside so you should keep your code in “wp-content/plugins/[INSERT NAME]“. So what does the code look like?
There are 3 main parts to this. First the commented portion at the top of the code. We need this for WordPress to know the details of your plug-in. Second there’s a line for adding in the hook for WordPress to notice plug-in, add_action(). Thirdly I’ve defined a function that register the new post type with the register_post_type function. As you can see it accepts an array as a parameter and I’ve laid out several options in that array but to get a full list of the options you should visit the WordPress Codex for more information. There are also a couple of helper functions that I use for the purpose of helping to declare labels and messages that I’ve detailed below. Enjoy.
Programming
For all the wonderful features it provides, CSS does a surprisingly poor job of the fundamentals of page layout. But options for richer, more dynamic pages are on their way, as Peter Gasston explains
After years of promise, CSS3 has finally arrived in style (if you’ll pardon the pun). It’s added a whole new array of tools to our front-end toolbox, giving us rounded corners, gradients, opacity, transformations, transitions, animations and much more. But now that we have the fun stuff, the eye candy, what’s next?
The next problem for CSS3 to address will be layouts. Until now we’ve got by with floats, relative positioning and negative margin tricks, but we still have to work incredibly hard to produce anything beyond the fairly standard two- to three-column layouts.
The W3C and the browser makers are aware of this problem, and working on a range of solutions. Chief among them (you may be surprised to learn) is the Internet Explorer team. IE10 looks set to herald an exciting new era of CSS layouts, making possible rich, dynamic and eye-catching websites that were previously beyond our reach.
In this article I’m going to take a look at the various layout methods that are at different stages of development, from the well-implemented to the purely theoretical. Perhaps not all of these will make it to the stage where we’re able to use them (or, at least, not in their current form), but it’s worth taking a look to see where the future lies. If you want more detail about some of the methods in this article, as well as much more of what CSS3 offers, I humbly recommend my book, The Book of CSS3.
Continue at The future of CSS layouts.
Programming Technology
So I’ve been away for a couple of months, I’ve been pretty busy. My wife and I just built a home so I was dealing with packing, closing, moving, renovating, and unpacking. Not too much fun but now that it’s done we’re much happier in our new space. Now that we’re in I’m ready to get back to this blog and a couple of other projects that I’ve been neglecting the past few months. One of which is a redesign of a site I did the first of the year, Reel Film Source.
The site is an asp.net website that collects news from various movie related RSS feeds around the web and delivers the links to the user. It’s essentially a RSS reader but centered completely around movie news. The initial release was mainly just to do it and get something out there but now I want to make it a bit more presentable. So I started this morning just laying out a couple of pages and how they’ll look.
I’d also like to convert the site to WordPress. I’ll keep the SQL Server back end that I use now and just write a windows service to collect my RSS links. I’ll write a WordPress plugin that uses my existing web services to import the RSS data into my WordPress site.
As far as the actual look I really like the .net magazine’s site looks. I think the theme they’re currently using would lend itself well to a movie themed website. I guess we shall see soon.
Programming Technology
After being a little bored and wanting to add to my skill set I started taking a look at PHP development this week. I tore through the W3C tutorials in about a hour and then moved on to seeing how I could incorporate jQuery and $.ajax calls into a PHP site. So to do that I came up with a simple test project. I have a single PHP page that upon loading fires a jQuery function that uses the built-in $.get method to call a function in another PHP file to get the date from the web server. There’s also a link on the PHP page that runs the same jQuery function to refresh the date.
<html>
<head>
<title>Test</title>
<link type="text/css" href="css/styles.css" />
<script type="text/javascript" src="jquery/jquery-1.5.1.min.js"></script>
<script type="text/javascript">
$(document).ready(function(){
refresh();
$("a").click(function() {
refresh();
});
function refresh() {
$("#spDate").text("Loading...");
$.get("functions.php",{'func':'getDate'},function(data){
$("#spDate").text(data);
});
};
});
</script>
</head>
<body>
<span id="spDate"></span><br /><br />
<a href="javascript:void(0);">Refresh</a>
</body>
</html>
Nothing special on this page, nothing even PHP related besides the call to the functions.php file. The highlight of what I’m doing here is the $.get function. This call specifies the PHP file I’m calling, the data I’m passing, and then the return function. Upon completion of the $.get call I fill my span tag with the resulting data.
<?php
function show_getDate() {
echo date(DATE_RFC850);
}
switch($_GET['func']) {
case 'getDate':
show_getDate();
break;
default:
echo "error";
}
?>
This is a simple PHP file. I’ve defined one function and a switch statement that operates on the data that I pass in my $.get function. All my function does is pass back the current date which gets passed up to my page and displayed.
This was a simple, easy example of how to use jQuery‘s built in $.ajax functionality with PHP. As I learn more in the coming weeks and months I’m sure I’ll be writing more of these posts. Cheers.
Programming
I did an earlier post on jQuery’s UI and specifically drag and drop and realized I was also hitting on the $.ajax function so I decided to do a post dedicated strictly to that. I’ll preface this post by saying that I did NOT figure this out on my own but found most of what I’m gonna talk about over at Encosia. I used the information I found at Encosia and implemented it while also coming across some of my own tricks to save time.
Lets start with the basic jQuery structure of an $.ajax call…
$.ajax({
type: "POST",
async: false,
contentType: "application/json; charset=utf-8",
url: "WebServiceFileName.asmx/FunctionTitle",
data: "{}",
dataType: "json",
success: function(msg) {
alert(msg.d);
}
});
This is all the code you need to call an ASP.Net web service using jQuery. Lets take a look at the parameters.
- type
REQUIRED and must be POST when using with ASP.Net.
- async
This one isn’t required but it comes in handy and took me doing some research to find. I was running into a problem with my JavaScript code trying to run my ajax calls and not waiting for a result before moving on. Setting the async property to false will make this happen for you and save you some headaches.
- contentType
This isn’t required either but with ASP.Net you NEED to put it in there and specify it as application/json; charset=utf-8.
- url
REQUIRED. This is going to be the path to your web service. I will also recommend that you put this file in the root of your site/application other wise jQuery has a hard time finding it for some reason I’ve found. I’ve listed my example as two parts, the file name, and then the title of the function you’re calling.
- data
REQUIRED. Even if you’re not passing anything you have to pass a blank JSON string. My example isn’t passing data but lets say your web service accepts two parameters called
FName and LName. If this is the case your data will look like….
data: "{'FName' : 'John', 'LName' : 'Doe'}";
- dataType
This isn’t required either but with ASP.Net you NEED to put it in there and specify it as json.
- success
This isn’t required either because your web service may not return anything or you may not want to take any action if the service completes successfully. I like to return data though so I use it frequently. Take a look at the example, you see a variable being passed back called msg. This is the OBJECT your results are in and to get to them call msg.d. I’m not sure what the logic is here but this is how it works so throw your hands up and accept it.
The other part of this is the web service…
The first piece to note is the top line.
<System.Web.Script.Services.ScriptService()> _
If you’re coding in Visual Studio like myself this line is commented out when you add the Web Service file to your solution. It MUST be uncommented in order for your $.ajax call to work. Next is the line of code before the function heading, you MUST include the following line…
<WebMethod()> _
The last couple of things to note are that when passing values to and from a web service it MUST be done as a String. When values are passed from the $.ajax call to a web service it will be interpreted as a String so there’s no need to try and declare it as anything else.
In conclusion, using the $.ajax call in conjunction with ASP.Net web services can be done quite easily and also efficiently.
Programming
I’ve been playing with the jQuery UI for the past few weeks and I’m LOVING what I’ve tried. As of right now I’ve implemented the the autocomplete and datepicker widgets but I for the purpose of this post I want to talk about the draggable and droppable interactions.
First lets include the files that are gonna get the job done. I’ve got my own style sheet along with the style sheet for the jQuery UI theme I’m using, and of course I have my jQuery JavaScript file along with the jQuery UI JavaScript file. Don’t forget these files or NOTHING will work.
What I’ve done is basic and straightforward. I’ve defined one containing div with two floating divs inside. In the left most div I’ve created a bunch of floating divs that I would like to drag to the right most div. My container also has a loading div defined that display over the left most div while I’m waiting for my $.ajax call to fill my first div.
The CSS for these divs doesn’t have anything fancy either but here’s a look at what I’ve got.
Now, I’ve got to add some content to my #exercises_picker div. I accomplish this by using jQuery’s $.ajax function which I don’t want to get into on this post, but I’ll show you how I take the results and make them draggable. I’ve written two JavaScript functions that are run when the page is loaded. The first one, getAllExercises(), is run when the page loads and it retrieves my content. If it successfully retrieves the content then my second function, buildExercises(exercises), is fired.
Looking at getAllExercises() first lets gloss over the $.ajax call and go right down to the success parameter. This bit of code runs upon the successful completion of my $.ajax call, and the first thing it does is pass the data I’ve retrieved into my second function buildExercises(exercises). If we take a look at that function this is where I’m really adding in the drag capability. The exercises parameter is just a string when it’s passed in but I need JSON so I use the jQuery.parseJSON function and then I’m ready to jump through my content and build some html. For each part of my JSON array I’m creating a div and appending it to my output I’ll be passing back. Once my output is ready I pass it back to the calling function getAllExercises() and make it the content for my #exercises_picker div.
Now I’ve got my content built and in place, I need to make it draggable. I specify all the divs inside #exercises_picker as draggable using the following syntax..
$("#exercises_picker div").draggable();
This line by itself will make the elements draggable however I’ve also specified some of the other parameters to suit my needs like customizing the cursor and specifying a zone that the elements have to stay in when being dragged.
Now we can drag my content all around but we need a region to drop it or it doesn’t really serve a purpose. It’s time for another jQuery function…
I take the div sitting on the right side of my container, #exercises_chosen, and make it droppable. This is done with the following syntax…
$("#exercises_chosen").droppable();
Again I’ve specified some additional functionality besides the default. Implementing the default functionality means I could drag my content over this droppable region and drop it, but it wouldn’t be clean. I wanted my content to realign itself and look nice and neat so I wrote an additional function, dropExercise($item) , to be run when content is dropped in #exercises_chosen.
The first thing I do is make the content fade out using jQuery’s fadeOut function and then I append I to the region I’ve just dropped it in to force it to line up. Second I added a click function to the $item so when it is clicked it fades out from the #exercises_chosen drop area and goes back to the #exercises_picker region where it can be dragged from again.
I hope this has been helpful and that this post might save you some time.
Programming
Older Entries >>
