Practical Programming

At one point or another you’ll be faced with figuring out how to accept secure payments from your website. As with most things, once you know the basics it isn’t quite as daunting as you might expect it to be. But before you get started remember that the things you will need like a Merchant Account (from a bank), an Online Payment Gateway (such as Authorize.net), and SSL Certificates cost money. Prices vary, but the average cost of a merchant account runs around $100.00 for setup, and the Online Gateway will typically have a minimum monthly fee of $30.00 and a cost per transaction anywhere from 10 to 20 cents. SSL Certificate prices are all over the board depending upon who you go with but a good price is $200.00 a year. (Expect to pay more if you go with a name-brand company like VeriSign or want extra features.) Obviously these prices are subject to change quite radically (and vary widely depending upon who you decide to do business with) so you will need to shop around and decide what fits best for you. Make no mistake you should know exactly whos services you will be using and exactly how much it is going to cost BEFORE ever starting development on an e-commerce website. period.

Although there are three methods of implementation, we will be looking at the Advanced Integration Method (aim). Other options are available if you wish to have orders take place on a third party site, or enter transactions in manually. I recommend the Advanced Integration Method  because it allows you to incorporate the POS directly inside your own website or application; providing a professional stream lined interface where all the transactions take place in the background with no interruption to the user. 

For those of you who prefer to RTFM, take a look at The AIM Guide PDF.

The basic process of communicating with a payment gateway is simple. You send in a request for a POS and it responds back with the result. That result will contain sale deatils, for example, wether or not the credit card was accepted, and if not the reason why. In our case we will be sending our payment request via post data to:

• secure.authorize.net/gateway/transact.dll (for production)
• test.authorize.net/gateway/transact.dll (for testing)

In upcoming articles I will walk you through the basic steps using Coldfusion:

Note:

Although this article covers the defacto authorize.net implementation (which by the way works with almost every online payment gateway since they are usually resellers of authorize.net), you may wish to take a look at Google Checkout. As the service is relatively new, there are some current promotions and discounts in place. I haven’t looked into the product heavily but it looks fairly solid and the idea of not having the liability of holding credit card numbers, etc…. can take a lot of weight off your shoulders.

In my next article I will cover tying in shipping services through UPS or FEDEX to round out the e-commerce experience. Please feel free to ask any questions before hand if you can’t wait.

Tip:

If you don’t already have a good traffic base coming to your website, don’t go spending the money and using the time to build a payment infrastrcuture. New companies often make the mistake of spending money up front assuming “if we build it they will come”… unfortunately they quickly learn the hard way this is far from the truth. 

If you have already managed to establish a good traffic base or have marketing material ready to roll out take the time to double check your work. It only takes one bad experience for a user to never come back… work all the kinks out, do user studies, and make sure your hardware and software can support a sudden influx of customers without going down.

Advanced Stuff ecommerce, merchant account, payment gateway

Calculating the distance between zip codes is not nearly as difficult as you might expect. To get started you will need two things:

1. A zip code database that contains longitude and latitude coordindates
2. A formula for measuring the distance between one point and another on a circle (in this case our circle is the circumference of earth)
    

You can obtain a free zip code databse from Popular Data.com, or you may wish to try the US Census Beurau. Google (and a few other companies with mapping software) also has GEO Code/Data available but make sure you check the license agreement carefully.

Once you have all your data imported into your preferred database and you have a form setup to accept a Starting and Ending Zip you need to integrate the formula into your program. Enter the Haversine formula to the rescue.

Here is a SQL implementation that works fantastic for me:

CREATE function [dbo].[geoDistance] (@lat1 decimal(9,6), @lon1 decimal(9,6), @lat2 decimal(9,6), @lon2 decimal(9,6))
returns float
as
begin
     declare @result float
     declare @r int
     declare @kmpm float
     declare @lat1R float
     declare @lat2R float
     set @kmpm = 0.621371192
     set @r = 6371
     set @lat1R = radians(@lat1)
     set @lat2R = radians(@lat2)
     set @result = acos(sin(@lat1R)*
                        sin(@lat2R)+cos(@lat1R)*
                        cos(@lat2R)*cos(radians(@lon2-@lon1)))*
                        @r*@kmpm
     if @result is null set @result = 0
     return @result
end

Here are some links to code in various other languages:

*It is worth mentioning that the earth is not a perfect circle, so you should expect an inaccuracy somewhere around 2% depending upon where you are on the globe. For those who need more accuracy (about 3mm) check out the Vicenty’s Formula (which is based on the ellipsoidal model of earth).

Advanced Stuff measuring distance, zip code

Forwarding your emails to your cellular phone is incredibly easy as all major carriers associate an email address directly with your telephone number:

Here are the major ones:

AT&T: phonenumber@txt.att.net
T-Mobile: phonenumber@tmomail.net
Virgin Mobile: phonenumber@vmobl.com
Sprint: phonenumber@messaging.sprintpcs.com
Verizon: phonenumber@vtext.com
Nextel: phonenumber@messaging.nextel.com

Quick Tips cellular

If you have done any signficant amount of programming in SQL you will have learned, with time, that nearly everything you can do in SQL Manager/Studio is 100% scriptable. Taking advantage of this can be a huge help with automating tasks… but you don’t need to bury your head in SQL Syntax to do so. Take the time to check out the wizards in studio, the context menus, etc… and you will almost always see a button or option to “generate script”. Get used to taking advantage of this because it will save you a multitude of headaches!

Here is a quick tip for restoring a table from a backup in script:

You cannot restore a database while it has active connections… to get around this you will need to set the database up in single_user mode (which deactivates all other connections except for your own). You will also need appropriate privelages to execute the commands like drop, restore, sp_adduser, and so on… db_creator roles are a simple solution for setting access priveleges but make sure you adequately protect the account as it opens up your database to lots of malicious activity if it were to fall in the wrong hands.

Here is a example to get you started down the right track:

--disconnect any one else so you can do the restore
ALTER DATABASE {your database}
    SET SINGLE_USER
    WITH ROLLBACK IMMEDIATE

USE MASTER 

--delete the old database (you could overwrite it instead
---if you wanted, merge, etc.)
DROP DATABASE {your database}

--restore the database from the backup
RESTORE DATABASE [{your database}]
   FROM  DISK = N'{insert full path to ur backup file here}' WITH  FILE = 1,
   MOVE N'{your backupset}' TO N'{insert your path and db name here}.mdf',
   MOVE N'{your backupset}_log' TO N'{insert your path and db name here}.ldf',
   NOUNLOAD,
   RECOVERY,
   REPLACE,
   STATS = 10

--grant privileges as necessary
use {your database}
exec sp_adduser '{user account}', '{user name}', 'db_datareader'

--reset the connection mode back to multi_user (this is important)
ALTER DATABASE {your database}
    SET MULTI_USER
    WITH ROLLBACK IMMEDIATE

MSDN Ref:
   1. Restore (Transact-SQL)
   2. sp_addUser (Transact-SQL)
   3. Drop Table (Transact-SQL)
   4. Alter Database (Transact-SQL)

Quick Tips sql

When listing records from a database you will often need to break those records up into multiple pages, so that your users do not see hundreds of rows at once. What follows is a custom tag which does exactly that.

Parameters

1. pageScale - (integer) Default 0. This will limit the number of pages returned depending on the current page. 0 means no scaling will occur.
   Example: pageScale=5, startRow=24, pageSize=8, records=64. Given these parameters this tag will return an array of 5 structures. The first page will start with 2 the last page will be 6. There are 8 total pages.
2. startRow - (integer) Default 1. The first record of the current page.
3. pageSize - (integer) Default 20. How many records to list per page.
4. records - (integer) Default 0. How many records to page through.
5. variable - (string) Default “pages”. The name of the array to create in the caller scope.

Source Code

Enjoy, it’s free. Until next time, dream in digital.

Free Stuff, web development cfml, source code

Google does it again… are they on a mission for world domination? Released today, video chat straight in GMAIL!

news Software

Microsoft’s excellent implementation of XML in SQL 2005 allows you to insert multiple rows with only one insert statement. Here’s how to insert many rows without calling insert many times. In this example we will have three tables in our database. They are: PRODUCTS, PRODUCTS_COLLECTIONS, PRODUCTS_COLLECTIONS_JOIN. PRODUCTS is our main table which contains our products; a product can belong to one or many collections. Therefore, we have PRODUCTS_COLLECTIONS_JOIN to bind a product to a collection via their id’s.

Pros vs. Cons

1. PRO - Only need to connect to your database once, to execute the procedure.
2. PRO - We are using XML support built into SQL 2005.
3. PRO - Keep things neat. There is only one procedure to manage which joins these tables.
4. CON - You are passing more data with the one connection (the XML). Whether this matters to you will depend on many factors dependent on your setup.

How?

SQL 2005 supports xPath strings, go here for a quick tutorial. The first statement in this procedure deletes all joins in PRODUCTS_COLLECTIONS_JOIN where product_id is equal to our product_id and the collection_id is NOT found in the XML passed. Next the procedure inserts into PRODUCTS_COLLECTIONS_JOIN where product_id is equal to our product_id, and where a collection_id is in the XML, and where a join with this product_id and collection_id does not already exist.

create procedure [spProductsCollectionsJoin] @id int, @collections xml
as
declare @deleted bit
set @deleted = 0
delete from [products_collections_join]
where
	[product_id] = @id
	and [collection_id] not in(
            select x.item.value('@id','int') as id
            from @collections.nodes('/collections/item') as x(item))
insert into [products_collections_join]([product_id],[collection_id])
	select
		[products].[id],
		[products_collections].[id]
	from [products]
	inner join [products_collections] on [products_collections].[id] in (
                   select x.item.value('@id','int') as id
                   from @collections.nodes('/collections/item') as x(item))
	left join [products_collections_join] on
                  ([products_collections_join].[collection_id] =
                   [products_collections].[id]
                   and [products_collections_join].[product_id] = @id)
	where
		[products].[id] = @id
		and [products_collections_join].[collection_id] is null
		and [products_collections].[deleted] = @deleted

To execute the join we need to create our XML to pass to the procedure:

declare @product_id int
declare @collections xml
set @product_id = 1
set @collections = '
<collections>
<item id="1"/>
<item id="2"/>
<item id="3"/>
</collections>
'
exec spProductsCollectionsJoin @product_id, @collections

After executing the procedure there will be three new rows in PRODUCTS_COLLECTIONS_JOIN which correspond to the 3 new item nodes in the XML.

Further Reading

http://msdn.microsoft.com/en-us/library/ms345117(SQL.90).aspx

http://www.w3.org/TR/xpath

http://www.zvon.org/xxl/XPathTutorial/Output/examples.html

Source

SQL Script to create products table.
SQL Script to create products_collections table.
SQL Script to create products_collections_join table.
SQL Script to create spProductsCollectionsJoin procedure.
SQL Script to test everything.

Until next time, dream in digital.

Advanced Stuff, sql sql, xml

Based primarily on two open source projects Mozilla and WebKit, Google whipped out a new browser that absolutely blows my mind. And before you ask, yes even the release-build is 100% open source.

Expect this browser to be several orders of magnitude faster at rendering pages and executing JavaScript than both FireFox and Internet Explorer (and all the others out there, Opera, etc.). It has a host of innovative features and design constructs. Check out their story-board on Chrome for an easy introduction.

Note:
I have only had one small problem with the browser not working correctly with flash (when trying to upload a file to a post in WordPress).

news Software

Users of SQL Server 2005 already know DTS Packages have been set aside for the newer, more functional, SSIS (SQL Server Integration Services). But if you are anything like myself, you have dozens of old DTS Packages and no desire to upgrade them. Send to the rescue The Microsoft SQL Server 2005 Feature Pack; you will want to Download the Microsoft SQL Server 2000 DTS Designer Components add-on. 

X86 Package (SQLServer2005_DTS.msi) - 5088 KB

Note:
The complete package is available here.

Quick Tips sql

The title of this post may suggest to the reader that I am writing about something everyone at least moderately familiar with Cold Fusion should know. But, for the unaware, Cold Fusion compiles cfscript first, then it compiles the rest of the cfml. With this in mind, know that if you have a custom tag which has a closing tag, you are in fact calling your tag twice. This is where an ordinarily functional tag will have problems with udf’s declared in cfscript. Cold Fusion is not evaluating whether the tag is running in thistag.executionmode start or end at the time the cfscript is compiled. When this happens, the compiler will gripe about not being able to declare udf’s more than once.

I usually address this issue one of two ways:

1. If the UDF could be used more than once I’d add it to a global repository of UDFs and load it into the server scope. More on that in a later post.
2. If the UDF is specific enough to the custom tag being declared to not warrant adding it to the global repository I create a routines.inc file, declare the function there, and use cfinclude to include it from the custom tag. The compiler doesn’t gripe about declaring the tag more than once, because it is no longer in a cfscript block in the custom tag.

Final Note
If the custom tag does not have a close tag, you can still declare the function in a cfscript block within the tag. I, however, still prefer to use a separate file. I usually place my custom tags in their own folders to keep everything neet, but I’ll have more on how I organize my tags and functions in a later post. Until then, dream in digital.

Quick Tips cfml, custom tags, udf