Category Archives: Web Development

Database SQL Snippets

Database SQL Snippets

These are some of my frequently used bits of SQL.

Tested: MS SQL Server 2008 R2

List of all empty tables in SQL Server Database

EXEC sp_MSforeachtable 'IF NOT EXISTS (SELECT 1 FROM ?) PRINT ''?'' '

List of all tables with at least 1 row of data in SQL Server Database

EXEC sp_MSforeachtable 'IF EXISTS (SELECT 1 FROM ?) PRINT ''?'' '

Find latest modified stored procedures

SELECT name, create_date, modify_date
FROM sys.objects
WHERE TYPE = 'P' -- Only stored procedures
ORDER BY modify_date DESC
GO

Change ‘P’ to ‘V’ if you want to search for views.

Find string in stored procedures

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%STRINGTOSEARCHFOR%'
GO

Find all constraints

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT' ORDER BY NameofConstraint
GO

Find all foreign keys

SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id

image

asp.net/mvc, Coding, Microsoft, Web Development

Mac OS X: Quickly Resize Images with sips

Easy Peasy

I had previously written about how to batch resize images and rename on Ubuntu, but on Mac OS X, there is an even quicker way to resize images with a command line utility called sips.

Open up a Terminal. Go the directory where your images or photos are located. Lets say you want to max width of 640 pixels. This is all you do:

sips -Z 640 *.jpg

Easy peasy.

(image source)

Mac OS X, Web Development

Magento 1.6: Fix Missing Submit Shipment Button

If you have installed Magento 1.6 or upgraded to Magento 1.6, and if you click on the Ship button on the order page in the back-end, the Submit Shipment button at the bottom right may be missing. Only an empty yellow box will be displayed.

To fix this, copy app/code/core/Mage/Sales/Model/Order.php to app/code/local/Mage/Sales/Model/Order.php (because you never, ever, want to be editing Magento core files). Open up the local Order.php, on line 1218-ish, you will find:

$shippingMethod = parent::getShippingMethod();

Change it to:

$shippingMethod = $this->getData('shipping_method');

For possible reasons on why this is happening in the first place, see the Magento boards.

Magento, Web Development

Fixing Contact Form 7 Redirection Referrer Failure with IE

On a client WordPress site, we wanted to use Contact Form 7 to grab visitor information and redirect them to an inner page after submission. To make sure they filled out the submission form, the destination page checked the referrer to make sure they came from the form page. Now, there are other ways of doing this, like setting cookies, but this method was good enough for the client.

So this all worked in Chrome and Firefox, but not Internet Explorer.

Shocking.

The Contact Form 7 blog has instructions on how to redirect users to a different page after a successful submission. Under Additional Settings, simply do this:

on_sent_ok: "location='http://www.fubar.com/thanks';"

The on_sent_ok is a Javascript hook. Inside of scripts.js in the plugin, it eventually makes this call:

if (data.onSentOk)
    $.each(data.onSentOk, function(i, n) { eval(n) });

So “location=’http://www.fubar.com/thanks’;” gets evaluated by Javascript and the page redirects. Great.

On the destination page, we were checking the referrer to verify they were coming from the form, here is an example of how you could do it:

/* Set where they should be redirected to if user didn't come from the form */
$redirectString = "Location: ". get_site_url() . "/contactform";

$referrer = $_SERVER['HTTP_REFERER'];

if ($referrer == NULL)
{
        header($redirectString);
        exit();
}
else
{
        $domain = parse_url($referrer);
        $pos = strpos ($domain["path"], "contactform");
        if ($pos == false)
        {
                header($redirectString);
                exit();
        }
}

Now, your PHP should be cleaner, more error checking, check for XSS, don’t hard-code anything, etc, etc.

The problem was that the referrer would always be null when the visitor was using Internet Explorer. Referrer isn’t required to be set by the browser. Browsers won’t set it if you started out on a HTTPS site but click on a non-secure link.

And IE won’t set it on redirection, but it will set it if you click on a link. So if a fake a link click, IE will set the referrer.

Lets create a Javascript function to fake a link click. This code is stolen from Stack Overflow:

function goTo(url)
{
    var a = document.createElement("a");
    if (a.click)
    {
        // HTML5 browsers and IE support click() on <a>, early FF does not.
        a.setAttribute("href", url);
        a.style.display = "none";
        document.body.appendChild(a);
        a.click();
    } else {
        // Early FF can, however, use this usual method
        // where IE cannot with secure links.
        window.location = url;
    }
}

Ok. Remember, the Contact Form 7 redirection is a Javascript hook. So now we change the on_sent_ok to call the goTo function instead:

on_sent_ok: "goTo('http://www.fubar.com/thanks');"

Bam. Done.

Web Development, Wordpress

Magento + Prototype.js + New Window + PDF Links

Prototype.jsThis is relatively straightforward to do, but I’m posting it so I have the Javascript next time I need it. On a Magento client site, all PDFs needed to be opened in a new window. Why? No clue. The Magento template wasn’t doing anything fancy, it was simply using the standard Magento Prototype.js library. Not having used Prototype.js in a long time, it took me a while to figure out the function calls to set the target attribute on all PDFs links to _blank, but here it is:

document.observe("dom:loaded", function() {

    $$('a[href!=""]').each(function(link){
        if(link.readAttribute('href').toLowerCase().include('.pdf')) {
            link.writeAttribute('target','_blank');
        }
    });
});

This is pretty much self-explanatory, but I’ll go through it anyway. document.observe(“dom:loaded”) is the jQuery equivalent of $(document).ready. readAttribute is like jQuery.attr(), include is like a Javascript IndexOf.

Thats pretty much it.

Magento, Web Development

How to Fix Internet Explorer + iFrame + PNG Transparency Issue

Working on a client site, we ran into issues with Internet Explorer, iFrames, and transparent PNGs. For the site, we are using an iFrame to load content when the user clicks on a button. On the fly, with javascript, a bit of code gets generated like:

<iframe name="fooFrame" src="fooFrame.html" allowTransparency="true">
</iframe>

Code snippel from fooFrame.html:

<body>
<div id="bazImage">Blah, Blah, Blah</div>
</body>
#bazImage {
color: white;
font-size: 16px;
font-weight: bold;
padding: 25px 30px 0px 18px;
font-family: Arial, Helvetica, sans-serif;
background: url(images/bazImage.png) no-repeat;
width: 256px;
height: 107px;
}

The background image is bazImage.png, a transparent PNG. This worked fine in Chrome, Firefox, and Safari, but all the transparent pixels turned up as white in IE. My first thought was we needed to apply a PNG fix.

But that didn’t help.

Not so surprisingly, Internet Explorer handles transparency differently than other browsers. Elements have an allowTransparency attribute that defaults to false, so embedded iFrames won’t be transparent. Simply set it to true.

One final step, set ‘background-color: transparent;’ in the body element.

Web Development

WordPress + Headway Themes: Remove RSS Feed Links

WordPress + Headway Themes: Remove RSS Feed LinksCurrently, this site is running WordPress MultiSite + Headway Themes. The main site is information I think other folks on the intertubes might find helpful. My Stream is generally junk that I find interesting. So, there is not one big RSS feed for the site. Don’t want the main site feed to get polluted by the stream feed.

I don’t know how many folks actually use auto RSS browser discovery, so I wanted to get rid of the RSS links that Headway makes automatically and replace it with a general RSS page that can be reached when the user clicks on the RSS icon.

So, lets see how Headway Themes adds feeds to the HTML. Towards the bottom of wp-content/themes/headway-version/library/core/head.php, you have Headway code being added to the standard WordPress header hook:

add_action('wp_head', 'headway_head_extras', 9);

Slightly higher up in head.php, the headway_head_extras function is defined and set as a hook itself:

function headway_head_extras(){
?>

<!-- Extras -->
<link rel="alternate" type="application/rss+xml" href="<?php echo headway_rss() ?>" title="<?php echo get_bloginfo('name')?>" />
<link rel="pingback" href="<?php bloginfo('pingback_url') ?>" />
<?php
        do_action('headway_head_extras');
}

Its important to notice that this bit of code has the feed and pingback URL. We do want to keep the pingback URL, so we’ll need to handle that.

To stop headway_head_extras from being called in general by the WordPress header, I did the following:

function my_headway_head_extras(){
?>

<!-- Extras -->
<link rel="pingback" href="<?php bloginfo('pingback_url') ?>" />
<?php
}

remove_action('wp_head', 'headway_head_extras', 9);
add_action('wp_head', 'my_headway_head_extras', 9);

Yes, I really should come up with a better function name than my_headway_head_extras, but thats not the point.

I first define my own function that includes the pingback URL. Then I stop the headway_head_extras function from being called by the WordPress header. And finally, I add my pingback function to the WordPress header instead.

I stuck the above code in wp-content/themes/headway-version/custom/custom_functions.php. The main RSS feed and comment RSS feeds disappeared as expected. But there was no change to the Stream site. I decided to put the code in wp-content/themes/headway-version/custom/sites/subsiteidentifier/custom_functions.php and it worked.

This may not be Headway Themes way of doing this, and might not be maintainable through upgrades, so try at your own risk. But I would imagine the custom_functions.php should be copied over as the theme gets upgraded, so it shouldn’t be an issue.

Web Development, Wordpress

Clear Cache Manually in CMS Made Simple: string(103) “Smarty error: [in template:20 line 3] [plugin] unknown tag

CMS Made SimpleAs a continuation of a project to move a CMS Made Simple site from one webhost to another, I ran into some caching issues.

Having successfully moved the CMS Made Simple files and database over, and editing the configuration file to use the right path and DB credentials, I went to the homepage. I was unceremoniously presented with:

string(103) “Smarty error: [in template:20 line 3]: [plugin] unknown tag – ‘title’ (core.load_plugins.php, line 124)” string(106) “Smarty error: [in template:20 line 4]: [plugin] unknown tag – ‘metadata’ (core.load_plugins.php, line 124)” string(108) “Smarty error: [in template:20 line 5]: [plugin] unknown tag – ‘stylesheet’ (core.load_plugins.php, line 124)” string(110) “Smarty error: [in template:20 line 6]: [plugin] unknown tag – ‘cms_selflink’ (core.load_plugins.php, line 124)” string(106) “Smarty error: [in template:20 line 43]: [plugin] unknown tag – ‘content’ (core.load_plugins.php, line 124)”

Nice.

Having never worked with a CMS Made Simple site before, this was really not helpful. My first assumption was that it was a caching issue. But I didn’t have the admin login information, so I couldn’t go into the backend and delete the cache. I would have to delete the cache manually.

When uploading the site, I noticed that there was a tmp directory. I imagined thats where the cache files would be located. Turns out I was right.

Deleting the contents of the tmp/cache and tmp/templates_c got rid of the nasty error and you can see the contents of those directories being refreshed once you hit the site.

CMS Made Simple, Web Development

CMS Made Simple MySQL Error: #1044 – Access denied for user ‘foo’@'%’ to database ‘bar’

CMS Made SimpleToday, I was helping to move a CMS Made Simple site from one webhost to another. It seemed easy enough. I was provided a database dump and copy of the files. It should have been just a matter of importing the database dump with phpmyadmin, scping the files to the new host, editing the config.php, clearing the cache, and refreshing the browser. Right, right? Of course not.

The export of the original tables from MySQL were pretty standard: drop the table if it exists, recreate it, lock it, populate it, unlock it:

DROP TABLE IF EXISTS `cms_additional_htmlblob_users`;
CREATE TABLE `cms_additional_htmlblob_users` (
  `additional_htmlblob_users_id` INT(11) NOT NULL DEFAULT '0',
  `user_id` INT(11) DEFAULT NULL,
  `htmlblob_id` INT(11) DEFAULT NULL,
  PRIMARY KEY  (`additional_htmlblob_users_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `cms_additional_htmlblob_users`
--

LOCK TABLES `cms_additional_htmlblob_users` WRITE;
/*!40000 ALTER TABLE `cms_additional_htmlblob_users` DISABLE KEYS */;
INSERT INTO `cms_additional_htmlblob_users` (`additional_htmlblob_users_id`, `user_id`, `htmlblob_id`) VALUES (6,1,5),(15,1,1);
/*!40000 ALTER TABLE `cms_additional_htmlblob_users` ENABLE KEYS */;
UNLOCK TABLES;

At the new webhost, in phpmyadmin, when importing the SQL, I kept getting the following error:

LOCK TABLES  `cms_additional_htmlblob_users` WRITE ;
MySQL said:
#1044 - Access denied FOR USER 'foo'@'%' TO DATABASE 'bar'

How can I not have the permission to lock tables? So I wanted to figure out if this lock really was the problem. Opening up the original SQL dump in Notepad++, I wanted to find the LOCK TABLES lines and remove them, and the corresponding UNLOCK TABLES. Now, there are way too many tables, so removing the LOCK TABLES by hand is a serious PITA.

Good thing Notepad++ supports regular expression in search. Use this to find all the LOCK TABLES:

LOCK\sTABLES\s`([a-zA-Z0-9_\-\.]+)`\sWRITE;

and replace with nothing. You can find UNLOCK TABLES; directly and remove them.

Turns out after doing that, the SQL imported just fine. So that meant the stupid webhost didn’t allow LOCK TABLES access for the user. Why would you not let users lock their own tables? Plain retarded. There was nothing really wrong with the SQL, just a retarded host.

Thankfully the client got wise and moved to a better host where we had no such problems.

CMS Made Simple, Coding

ASP.net MVC: The controller for path /favicon.ico does not implement IController

One of Run Level Media‘s project involves migrating a Windows application over Terminal Services, to the web. A previous programmer had started building the web app in C#, ASP.NET MVC 2. We decided to upgrade for the sake of maintainability and wanting to use some of the newer features in MVC 3 (like Razor).

After upgrading and trying to debug code, I kept running into the following error on every page load: ASP.net MVC: The controller for path /favicon.ico does not implement IController

It was true that the favicon.ico was missing, but we didn’t want to make a temporary one. We just needed requests for dot files to the root directory to be ignored.

Most Global.asax have route definitions like this:

routes.MapRoute(
     "Default",
     "{controller}/{action}/{id}",
     new { controller = "Home", action = "Index", id = "" }
);

There are really two problems here. First, the controller looks for something to handle favicon.ico. More than likely, you don’t have a function to deal with favicon.ico (why should you). Second, the HttpException should be caught and dealt with. Either log it or provide a more helpful message. Otherwise you’ll see ‘HttpException was unhandled by user code’ errors.

To fix the controller, try this:

routes.MapRoute(
     "Default",
     "{controller}/{action}/{id}",
     new { controller = "Home", action = "Index", id = "" },
     new { controller = @"[^\.]*" }
);

new { controller = @”[^\.]*” } means ignore anything that matches that regex.

asp.net/mvc, Microsoft, Web Development
« Older Posts

Atlanta Lions Club