How to compact MS Access database
16 April 2007 15:20 (EST)
This example works only if MS Access is installed on web server (but such case is not advised for security reasons):
And make sure that database is not locked with CF server.
<cfscript>
caller.error = 0;
if (not isDefined("attributes.database"))
caller.error = caller.error + 1;
else {
if (not FileExists(attributes.database))
caller.error = caller.error + 2;
path = GetDirectoryFromPath(attributes.database);
success = 0;
while (success eq 0) {
attributes.tempdatabase= path & randrange(1000,9999) & ".mdb";
if (not FileExists(attributes.tempdatabase))
success = 1;
}
}
if (isDefined("attributes.backup"))
if (FileExists(attributes.backupdatabase))
caller.error = caller.error + 4;
</cfscript>
<cfif not caller.error>
<cfif isDefined("attributes.backupdatabase")>
<cffile action="copy" source="#attributes.database#" destination="#attributes.backupdatabase#" />
</cfif>
<cftry>
<cfobject type="com" action="connect" name="objaccess" class="Access.Application" />
<cfcatch type="Any">
<cfset request.comerror = cfcatch.message />
<cfset caller.error = 10 />
<cfobject type="com" action="create" name="objaccess" class="Access.Application" />
</cfcatch>
</cftry>
<cftry>
<cfscript>
objDBEngine = objaccess.DBEngine;
temp = objDBEngine.CompactDatabase("#attributes.database#","#attributes.tempdatabase#");
</cfscript>
<cfcatch type="any">
<cfset request.comerror = cfcatch.message />
<cfif cfcatch.message is "">
<cfset request.comerror = cfcatch.detail />
</cfif>
<cfset caller.error = 10 />
</cfcatch>
</cftry>
<cfif FileExists(attributes.tempdatabase)>
<cffile action="delete" file="#attributes.database#" />
<cffile action="rename" source="#attributes.tempdatabase#" destination="#attributes.database#" />
</cfif>
</cfif>
caller.error = 0;
if (not isDefined("attributes.database"))
caller.error = caller.error + 1;
else {
if (not FileExists(attributes.database))
caller.error = caller.error + 2;
path = GetDirectoryFromPath(attributes.database);
success = 0;
while (success eq 0) {
attributes.tempdatabase= path & randrange(1000,9999) & ".mdb";
if (not FileExists(attributes.tempdatabase))
success = 1;
}
}
if (isDefined("attributes.backup"))
if (FileExists(attributes.backupdatabase))
caller.error = caller.error + 4;
</cfscript>
<cfif not caller.error>
<cfif isDefined("attributes.backupdatabase")>
<cffile action="copy" source="#attributes.database#" destination="#attributes.backupdatabase#" />
</cfif>
<cftry>
<cfobject type="com" action="connect" name="objaccess" class="Access.Application" />
<cfcatch type="Any">
<cfset request.comerror = cfcatch.message />
<cfset caller.error = 10 />
<cfobject type="com" action="create" name="objaccess" class="Access.Application" />
</cfcatch>
</cftry>
<cftry>
<cfscript>
objDBEngine = objaccess.DBEngine;
temp = objDBEngine.CompactDatabase("#attributes.database#","#attributes.tempdatabase#");
</cfscript>
<cfcatch type="any">
<cfset request.comerror = cfcatch.message />
<cfif cfcatch.message is "">
<cfset request.comerror = cfcatch.detail />
</cfif>
<cfset caller.error = 10 />
</cfcatch>
</cftry>
<cfif FileExists(attributes.tempdatabase)>
<cffile action="delete" file="#attributes.database#" />
<cffile action="rename" source="#attributes.tempdatabase#" destination="#attributes.database#" />
</cfif>
</cfif>
And make sure that database is not locked with CF server.
Comments: 1
ColdFusion 8 "Scorpio" Beta
11 April 2007 12:45 (EST)
Applications for the Scorpio Beta are now being accepted.
Please visit Adobe Prerelease Program and click on "Join Adobe Prerelease Program Now".
Please visit Adobe Prerelease Program and click on "Join Adobe Prerelease Program Now".
ABN Validation
10 April 2007 17:48 (EST)
Australian Business Number (ABN) validation function, based on ABN format description from Australian Taxation Office (ATO):
<cffunction name="isValidABN" returntype="boolean" access="public" output="false">
<cfargument name="ABN" type="string" required="true" />
<cfset var aWeight = listtoarray("10, 1, 3, 5, 7, 9, 11, 13, 15, 17, 19") />
<cfset var sABN = "" />
<cfset var i = 0 />
<cfset var hashSum = 0 />
<cfset sABN = rereplace(arguments.ABN,"[^\d]","","all") />
<cfif len(sABN) eq 11>
<cfloop index="i" from="1" to="11">
<cfset hashSum = hashSum + aWeight[i]*(mid(sABN,i,1)-iif(i eq 1,de(1),de(0))) />
</cfloop>
<cfif hashSum mod 89 eq 0>
<cfreturn true />
</cfif>
</cfif>
<cfreturn false />
</cffunction>
<cfargument name="ABN" type="string" required="true" />
<cfset var aWeight = listtoarray("10, 1, 3, 5, 7, 9, 11, 13, 15, 17, 19") />
<cfset var sABN = "" />
<cfset var i = 0 />
<cfset var hashSum = 0 />
<cfset sABN = rereplace(arguments.ABN,"[^\d]","","all") />
<cfif len(sABN) eq 11>
<cfloop index="i" from="1" to="11">
<cfset hashSum = hashSum + aWeight[i]*(mid(sABN,i,1)-iif(i eq 1,de(1),de(0))) />
</cfloop>
<cfif hashSum mod 89 eq 0>
<cfreturn true />
</cfif>
</cfif>
<cfreturn false />
</cffunction>
Melbourne CFUG
6 April 2007 20:17 (EST)
When: 19 April 2007 @ 7:00pm
Location: NGA.net, Level 2, 17 Raglan St, South Melbourne (map)
Agenda:
1) WebDU Overview.
We'll cover what happened at WebDU, what was interesting, what was not, as well as all the interesting goings on at all the keynotes.
2) Annual CFUG General Meeting
Here is you chance to have you say about the CFUG.
What do you want to hear about? What don't you want to hear about?
We'll cover some of the general organisational changes that went on, as well as all the interesting stuff that we have planned for the future.
RSVP: mark DOT mandel AT gmail DOT com
See the CFUG Melbourne Calendar at:
http://www.cfcentral.com.au/Events/index.cfm
Location: NGA.net, Level 2, 17 Raglan St, South Melbourne (map)
Agenda:
1) WebDU Overview.
We'll cover what happened at WebDU, what was interesting, what was not, as well as all the interesting goings on at all the keynotes.
2) Annual CFUG General Meeting
Here is you chance to have you say about the CFUG.
What do you want to hear about? What don't you want to hear about?
We'll cover some of the general organisational changes that went on, as well as all the interesting stuff that we have planned for the future.
RSVP: mark DOT mandel AT gmail DOT com
See the CFUG Melbourne Calendar at:
http://www.cfcentral.com.au/Events/index.cfm
UTF8 sorting order with MySQL
20 March 2007 13:00 (EST)
By default MySQL sorts UTF8 fields using following order:
- Numbers,
- English characters, and than
- Non-English characters.
SELECT name, IF(name REGEXP '^[a-zA-Z0-9]', 0, 1) AS sort FROM test ORDER BY sort DESC, name
T-SQL field formatting
18 March 2007 21:41 (EST)
Here is a tip on number formatting with T-SQL.
For example, you have a numeric field called ProductNumber and want to have it as six-char string with leading zeros.
For example, you have a numeric field called ProductNumber and want to have it as six-char string with leading zeros.
SELECT RIGHT(REPLICATE('0',5)+CONVERT(VARCHAR,ProductNumber),6) FROM Products
cfDecrypt
8 March 2007 11:14 (EST)
cfDecrypt is an utility to decrypt (decode) .cfm files encrypted with cfCrypt (cfEncode) program included in ColdFusion.
Renaming Files As They Are Uploaded (how CFFILE actually works)
8 March 2007 11:12 (EST)
When receiving a file uploaded from a client, you can actually save it and rename it all in one step rather than two steps as the documentation implies.
The documentation states that the "destination" attribute of the CFFILE tag specifies the "pathname of the directory in which to upload the file." This is misleading for two reasons:
The file has actually already been uploaded by the time your CFFILE tag is encountered. The file has automatically been saved in ColdFusion's temporary directory. Using the CFFILE tag with the action attribute set to "upload" is really just moving the file from one place to another. If no CFFILE tag is encountered, the file is simply deleted.
You can actually specify a file name in addition to a directory in the destination attribute. Rather than this...
... you can just do this...
This moves the uploaded file from ColdFuson's temporary directory into the specified directory, and renames it using a unique ID generated by ColdFusion, all in one step.
The documentation states that the "destination" attribute of the CFFILE tag specifies the "pathname of the directory in which to upload the file." This is misleading for two reasons:
The file has actually already been uploaded by the time your CFFILE tag is encountered. The file has automatically been saved in ColdFusion's temporary directory. Using the CFFILE tag with the action attribute set to "upload" is really just moving the file from one place to another. If no CFFILE tag is encountered, the file is simply deleted.
You can actually specify a file name in addition to a directory in the destination attribute. Rather than this...
<cffile action="upload" destination="/path/to/some/directory" ... />
<cffile action="rename" ... />
<cffile action="rename" ... />
... you can just do this...
<cffile action="upload" destination="/path/to/some/directory/#createUUID()#.gif" ... />
This moves the uploaded file from ColdFuson's temporary directory into the specified directory, and renames it using a unique ID generated by ColdFusion, all in one step.
Write Out Binary Data
8 March 2007 11:11 (EST)
In case CFContent is restricted on server:
<cffile action="readbinary" file="/home/cantrell/Pictures/Corrs2.jpg" variable="pic"/>
<cfscript>
context = getPageContext();
context.setFlushOutput(false);
response = context.getResponse().getResponse();
out = response.getOutputStream();
response.setContentType("image/jpeg");
response.setContentLength(arrayLen(pic));
out.write(pic);
out.flush();
out.close();
</cfscript>
<cfscript>
context = getPageContext();
context.setFlushOutput(false);
response = context.getResponse().getResponse();
out = response.getOutputStream();
response.setContentType("image/jpeg");
response.setContentLength(arrayLen(pic));
out.write(pic);
out.flush();
out.close();
</cfscript>