Practical Programming

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

Anyone who has spent time web surfing in the last few years has seen the popularity of Captchas rise to the point where it’s almost considered bad practice to not use one. The reality is in today’s world you need to protect your application from spam bots and brute force cracks. And fortunately for us Cold Fusion programmers, CF8 makes this incredibly easy with the edition of the cfimage tag.

Although relatively easy from a programmatic standpoint, when using cfimage for captchas you should take your intended audience into consideration in order to make the best decision on setting attribute values. Those you will need to take into consideration are width, height (overall image size), fontsize, and difficulty (how hard the text is to read).
Qusetions you might and should ask before proceeding:

1. How old are my readers?
2. How tech-savvy are my readers?
3. Do my users have any visual disabilities?

I decided for my purposes that my average readers would be in their 20s, technicaly-savvy and unlikely to have any visual impairment. After playing around with the sizes for a while I finally settled on this:

Here is sample output at each difficulty level:

Low:

Medium:

High:

Once you are satisifed with your captchas look you need to do a bit of programming to make it useful. You will need to:

1. Come up with a method for generating a random string
2. Store the string for comparison after form submittal
3. And lastly compare the value entered by the user to our captcha string to make sure it’s a match

[1. Come up with a method for generating a random string]

There are tons of ways you can do this… heck you probably have a method already lying around somewhere already. But incase you don’t, the key thing with random string generators is to chose random numbers from the ascii table and then use chr() to pull out the character. Since my captchas are inherently case sensitive (see part 2), I pull random numbers from 65,90 (A-Z).
*I could have technically used both upper and lowercase since my code converts the input to uppercase prior to doing the hash check anyway, however I felt using one or the other provided more uniformity, and ultimately uppercase letters were easier to read.

function randString(len){
	var i = 0;
	var str = "";

	var chr1 = "";

	for (i = 0; i < len; i++){
		chr1 = chr(randRange(65,90));

		str &=  chr1;
	}
	return str;
}

[2. Store the string for comparison after form submittal]

I chose to store mine within the form begin submitted using the Hash() function… although I could have easily stored this in the session or client scope if I wanted to. You can use any of these methods or come up with another, but consider following my lead b/c it is fundamentally simplier and doesn’t require an active memory store.
*Take note that hashing the string means the input is case sensitive.

<input name="rs" type="hidden" value="<cfoutput>#hash(rs)#</cfoutput>" />

[3. And lastly compare the value entered by the user to our captcha string to make sure it’s a match]

In this case we have to hash the input (form.captcha) and compare it to our stored value (form.rs):

<cfset rs = hash(ucase(form.captcha))> <!--- hash input --->

<cfif rs eq form.rs and len(trim(msg))> <!--- do comparison --->

For detailed information on cfimage, check out the livedocs entry from adobe.

Download the complete example.

web development cfml

No matter what type of application you are building if your users are going to be uploading files it is to your advantage (and that of your users) that you integrate real time virus checking. Before I proceed remember this:

1. Virus checking is free/low cost  (depending upon the product and wether you are an individual or business)
2. It is relatively easy to build into your applications

Looking around the web you will find various virus checking applications, some free, some not. Some things to consider when selecting antivirus software will be: 

• Does the program support a CLI (execution from the command line)
• How reliable the software is
• How easily manageable the virii definitions are
   

@echo off
cd\program files\grisoft\avg free\
avgscan.exe %1 /heur /ext=* /arc /report c:\%2.txt
cd\
type %2.txt
rem del %2.txt

You can decide on your own set of options by reviewing the avgscan /? command as shown below:

C:\Program Files\Grisoft\AVG Free>avgscan /?
AVG7 Anti-Virus command line scanner
Copyright (c) 2006 GRISOFT, s.r.o.
/SCAN        Scan test /path,path/
/HEUR        Heuristic Analysis /path,path/
/EXCLUDE     Exclude path or files from scan
/@           Command file /file name/|
/EX          test these extensions /for example EXT=*/
/NOEXT       do not test these extensions /for example NOEXT=JPG/
/SMART       Smart scan
/ARC         test archives
/RT          test run-time compressions
/CLEAN       clean automatically
/TRASH       Move infected files to the Virus vault
/QT          Quick Test
/LOG         Generate a test result file
/MACROW      report macros
/PWDW        Report password-protected files
/IGNLOCKED   Ignore locked files
/REPORT      Report to file /file name/
/REPAPPEND   Append to the report file
/REPOK       report uninfected files as OK
/STOPLEVEL   Pause on detection /1-n/
/NOBREAK     Do not allow CTRL-BREAK to abort
/NOBOOT      Skip MBR/BOOT check
/NOMEM       Do not test memory
/MEM         Scan active processes
/NOHIMEM     Do not test upper memory
/NOSELF      Do not self-check AVG
/SKIPRP      Skip reparse points (NTFS only)
/SPY         Use Anti-Spyware Scanner
/SMS         Generate report in Management Information File (MIF) format
/?           Display help on this topic
/HELP        Display help on this topic
/DELAY       Cooperative mode (sleep during scanning)

[4 Integrate this batch file into your cold fusion program]

Obviously we need to have CF call our batch file (vscan.bat). CFEXECUTE to the rescue:

<cfexecute
    name="c:\documentready\_bin\vscan.bat"
    arguments="#tmpFile# #session.user_key#"
    variable="getOutput" timeout="15"></cfexecute>

Thanks to our /report switch and the Type from our batch file the results of our virus scan is dumped to the screen allowing us to parse the results returned in getOutput. Pretty neat eh?

To help you get an idea of what you will be parsing, and what information is available, here is a sample output (take note that the file input parameter must be a full path, and the output you are viewing here is for a file that is not infected with a virus):

C:\DocumentReady\_bin>vscan.bat c:\foo.txt foo
AVG7 Anti-Virus command line scanner
Copyright (c) 2006 GRISOFT, s.r.o.
Program version 7.5.549, engine 442
Virus Database: Version 270.9.0/1771  2008-11-06
Tested: 1 files, 2 sectors
Infections: 0
Errors: 0
AVG  7.5
Copyright (c) GRISOFT,s.r.o. 2006
Program version 7.5.438  Engine: 442 database version 270.9.0/1771
Command line: [c:\foo.txt /heur /ext=* /arc /report c:\foo.txt]
------------------------------------------------------------
Test start 11/6/2008 12:10:15
Elapsed time 4 sec.
------------------------------------------------------------
Scanned         files      :    1
Scanned         sectors    :    2
No viruses found.
------------------------------------------------------------

To do our parsing we will be using Regular Expressions. If you aren’t famlair with regular expressions, they are essentially the hottest tool you can use to parse through text and although they appear duanting at first, with practice they become both second nature and essential to your programming needs.

<cftry>
	<cfexecute
            name="c:\documentready\_bin\vscan.bat"
            arguments="#tmpFile# #session.user_key#"
            variable="getOutput"
            timeout="15"></cfexecute>
<cfcatch>
      <cfset void = showError("Upload failed, virus check timed out.")>
</cfcatch>
</cftry>
<cfset infectedFiles = "Infected        files      :    (\d+?)?">
<cfset pos = REFindNoCase(infectedFiles,getOutput,1,true)>
<cfif ArrayLen(pos.pos) gte 2>
       <cfset infectedCount = mid(getOutput,pos.pos[2],pos.len[2])>
<cfelse>
       <cfset infectedCount = 0>
</cfif>
<cfif infectedCount>
       <cfset virusname = ListGetAt(getOutput,5,chr(10)&chr(13))>
       <cfset virusname = ReplaceNoCase(virusname,"""#tmpFile#"" ...
                                        Virus identified ","","ONE")>
       <cffile action="delete" file="#tmpFile#">

       <cfset void = showError("Virus Found, " & virusname)>
</cfif>

For more information on Regular Expressions check out the Introduction to Regular Expressions (Scripting) on MSDN.

Uncategorized Virus Scanning

Why?

cftimer provides a simple and easy way to determine how long your code is taking to execute. Just assign it a label and turn on debugging output. The results will be displayed under the category CFTimer Times in the debug output measured in milliseconds. Ex. “[62ms] MyCodeBlock”

How?

<cftimer label="MyCodeBlock"> ... {some code} ... </cftimer>

Note:

Make sure you have Timer Information turned on in the Debug Output Settings from within the Coldfusion Administrator. If you still aren’t seeing any debug output make sure that debugging is enabled and that your ip has been added to the Debugging IP Addresses list.

Quick Tips cfml