admin - Nick Ali

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

iPad Pin It Pinterest Bookmarklet in Safari

It looks like Pinterest hasn’t provided a convenient way of adding the Pin It bookmarklet to Safari on the iPad. But try the instructions below. Its been tested with iOS 5.

Step 1
Bookmark this page in Safari on the iPad. Give it a useful name like Pin It.

Step 2
Copy the javascript below:

javascript:void((function(){var%20e=document.createElement('script');e.
setAttribute('type','text/javascript');e.setAttribute('charset','UTF-8');e.setAttribute('src','http://assets.pinterest.com/js/pinmarklet.js?r='+Math.random()*99999999);document.body.appendChild(e)})());

Step 3
Go back and replace the bookmarklet address with the javascript above.

Step 4
Pin away!

Follow me on Pinterest.

Tweet me if the Pin It bookmarklet no longer works.

Mac OS X, Social Media

Mac OS X: Quickly Resize Images with sips

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:

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

Author Archives: admin

Database SQL Snippets

Mac OS X: Quickly Resize Images with sips

Magento 1.6: Fix Missing Submit Shipment Button

Fixing Contact Form 7 Redirection Referrer Failure with IE

Nick Cast Episode 24 – iPad vs Books #volomo11

Nick Cast Episode 23 – Packing…Or Not #volomo11

Nick Cast Episode 22 – The Verge and Carne Asada Burritos #volomo11

Nick Cast Episode 21 – Sushi and Zoe’s Kitchen #vlomo11

Nick Cast Episode 20 – Passport Stamps