Modifying a column in SQL manager, the right way

If you have a column of a table that needs to be changed, don't always go to the modify table page in SQL manager to do your changes. I tried something simple as increasing the column size of a nvarchar field, but the update kept on giving timeouts in the SQL manager. Then I tried the generate SQL script button to see what SQL manager was trying to do. The contents gave me an idea why I was getting timeouts:
-alter the table:
-drop constraints
-drop triggers
-create temporary table with new column sizes/fields
-add defaults from table to temp table
-add constraints from table to temp table
-add index to temp table
-insert all values from original table to temp table
-drop relationships from original table
-drop original table
-rename temp table name to original table name
-add relationships to temp table

Which it needed to do is:
ALTER TABLE sometablename
ALTER COLUMN somecolumnname
nvarchar(100) NULL;

Next time if I modify a table with much data, or a lot of relationships and/or constraints I will stay clear of the SQL manager "modify table" dialog.

Query of queries weirdness

I was playing around with the query service object when I found some strange behavour. It looks like there is a differency between the query object and the QueryNew() object.
See the test case below:


//test case for query of queries usings querynew and query object:

//Make query object with queryNew()
qUsers = queryNew("usename,userID","varChar,integer");
queryAddRow(qUsers);
querySetCell(qUsers,"usename","Jorrit");
querySetCell(qUsers,"userID",1);
queryAddRow(qUsers);
querySetCell(qUsers,"usename","Ben");
querySetCell(qUsers,"userID",2);
queryAddRow(qUsers);
querySetCell(qUsers,"usename","Tom");
querySetCell(qUsers,"userID",3);

writeDump(qUsers);

queryService = new Query();
//queryService.setName("qGetUserByID");
queryService.setDBType("query");
queryService.addParam(name="userID", cfsqltype="cf_sql_integer", value="1");
SaveContent variable="queryBody" {
WriteOutput("SELECT userID, usename ");
WriteOutput("FROM qUsers ");
WriteOutput("WHERE userID = :userID ");
}
queryService.setSql(queryBody);
test = queryService.execute();

writeDump(test);

/*
The code above will result in an error
Error Executing Database Query.

Query Of Queries runtime error.
Table named qUsers was not found in memory. The name is misspelled or the table is not defined.
*/

ColdFusion ignores typo in for loop statement

I was making a simple XML iteration in script using a for loop, but it only returned one element in stead of all the elements. In a further inspection I discovered a typo which was not reported by ColdFusion.
Here is the for loop:

for (sCount=1; sCount<+arrayLen(xmlSomething["anotherElement"]); sCount++){

}

The problem is the <+ operator which should be <=
But no error report, just the first element that is returned. I wonder why!

Coldfusion 9 problems

This is to make a note which problems I encountered with ColdFusion 9:

SpreadSheetRead has no spreadsheet to query possibility.
returns a query, but with the script function there is no option so it only returns an Excel object:
excelObj = SpreadSheetRead('c:\some.xls',1);

Next stop, the query object with double single quotes:
queryService = new Query();
queryService.setName("qUserExists");
queryService.setDataSource(application.settings.DSN);
queryService.addParam(name="userName", cfsqltype="cf_sql_varchar", value="someusername");
SaveContent variable="queryBody" {
WriteOutput("SELECT TOP 1 CASE ISNULL(U.firstname, '') WHEN '' THEN '*no firstname*' ELSE U.firstname END AS lastname ");
WriteOutput("FROM Users U ");
WriteOutput("WHERE U.Username = :userName ");
}
queryService.setSql(queryBody);
qUserExists = queryService.execute();

Don't mind the uglyness of the query, it is there to prove a bug. The query will return an error. If you see the details of the error and look at the executed query you will see that the two single quotes are replaced by one. So ISNULL(U.firstname, '') will be executed as ISNULL(U.firstname, ').

If someone knows a solution, please let me know.

UPDATE: The query issue is a known bug (80210) and is resolved in update 9.0.1

Using database default values in ColdFusion ORM

I stumbled upon a problem when I was using ORM on a SQL table which has a default value (column not null, default value 0). The problem is that ORM does not accept a value which isn't set because then it will insert it as NULL.

Example:
/**
* @persistent true
* @entityName UserError
* @table UserErrors
* @schema dbo
*/
component output="false"
{
property name="user_id" column="user_id" type="numeric" ormtype="int" fieldtype="id" generator="identity";
property name="error_count" column="error_count" type="numeric" ormtype="int"; //column with default value
}

The step for me was to force that you can set this value as NULL, which caused an error:
property name="error_count" column="error_count" type="numeric" ormtype="int" notnull="false" ;

Instead of an ORM error you get a database error. So the next try is to tell ORM not to use this:
property name="error_count" column="error_count" type="numeric" ormtype="int" persistent="false" ;

This worked for the insert because the value was set correctly in the database. The problem is that you cannot retrieve the value after it is inserted.
So the next step is to modify the data before it is inserted. In the component I added the folowing function:
public void function preInsert() {
if(isNull(this.getError_count()))
this.setError_count(0);
}
Now I get an error converting char to int. This gives me a clue that the preInsert function is executed. So now I have to force ColdFusion to use int for the Error_count default value:
public void function preInsert() {
if(isNull(this.getError_count()))
this.setError_count(javacast("int",0));
}

Now it is working as a charm.
The only problem is that you have two places where your database defaults exists. This is something to keep in mind if you are changing databse default values. It is working for me, but it is not ideal.
If I find a better solution I will post it here. Suggestions are welcome in the comments.

Connecting to sites which use SSL

If you want to connect to a site which uses https you might get a connection error like "peer not authenticated". To solve this problem you need to import the SSL certificate to your ColdFusion Java keystore. There are a few steps to do this depending on your situation.

If you have a keystore which contains the certificate (which was my case) then you need to export this to a certificate file using the keytool application.

Keytool can be found in your ColdFusion root \runtime\jre\bin\keytool.exe

keytool.exe -export -alias aliasOfKey -v -keystore keystoreFile -file myCertificate.crt
If keytool asks for a password and you don't have ony you can try the default password "changeit".
This exports the certificate with the given alias from the given keystore file to a crt file.

If you don't know the alias of the certificate then you can list the contents of the keystore file using:

keytool.exe -list -v -keystore keystoreFile

You can also open the site in a webbrowser and double click on the key icon. This will view the SSL certificate. Click the details tab. In this window you can save the certificate. Choose BASE64 for encoding and save it to a known location.

Next you need to copy the crt file to the security folder in ColdFusion. This is located in: ColdFusion root runtime\jre\lib\security

In this folder is also the cacerts file which is the keystore for ColdFusion.
Now you need to add the crt file to the keystore:

keytool.exe -import -keystore cacerts -alias aliasOfKey -file myCertificate.crt

And you are done!

Connecting to webservices which require login using cookies

U stumbled upon a problem connecting a ColdFusion site to an external application using a webservice. The normal howtos are insufficient because none of them describe connection to a webservice that requires a login AND use multiple requests. Here is a description how it is done for a webservice which has a separate method for the login and the other functions:

Step one: initiating the webservice
Nothing special here:
ws_securityService = createobject('webservice','http://www.somesite.com/someSecurityservice.asmx?WSDL');
ws_someOtherService = createobject('webservice','http://www.somesite.com/someOtherService.asmx?WSDL');

Step two: maintaining the connection
If you visit a website in a browser it will recognize if you did a previous request. If you login on a page it will remember you so you can load your profile page and so on. By default every request by ColdFusion is a new one, nothing from previous request are remembered, that is why you need to tell ColdFusion to do otherwise:
ws_securityService.setMaintainSession(true);
ws_someOtherService.setMaintainSession(true);

Step three: logging in
In this case the authentication is in a few steps: if you initialize the login the method returns a string which you need to to do a login, this is simplified because there is no need to go in depth here.
challenge = ws_securityService.InitializeLogin(serverUserName); //function returns a key required by the login method
loginsucces = ws_securityService.Login(response); //the real login method

Step four: reading the cookies
After a successful login the webservice will set a cookie with a key to see if the user is authenticated. So this is required by all the other webservices.
server_cookies = ws_securityService._getCall().getMessageContext().getProperty("Cookie"); //this returns an array with cookies

Step five: decoding the cookies
Because I use the underlying Java to add the cookies to a request I need to convert this from the typeless ColdFusion to Java.
Because there will be a conversion error you have to explicitly cast the cookie array by the following code:
String = CreateObject("java", "java.lang.String"); //Get a Java cast string
Array = CreateObject("java", "java.lang.reflect.Array"); //Get a Java cast array
Cookies = Array.newInstance(String.getClass(), arrayLen(server_cookies)); //create the cookie array

Now you need to fill this newly created array with the ColdFusion cookie array:
<cfloop from="1" index="i" to="#arrayLen(server_cookies)#">
<cfset #urldecode(server_cookies[i])#")="" array.set(cookies,="" i-1,=""><!--- ColdFusion arrays start at 1, Java (and all the other programming languages) start at 0 --->
</cfloop>


  


Step six: adding the cookies to all your other webservices
ws_someOtherService._setProperty("Cookie", cookies); //this adds the cookie to your webservice call
Before, you already set the MaintainSession to true so with every request to this webservice the cookies will be added.

Step seven: call the other webservices
Actually: you are done, now you can call the other webservice which require authentication.
someResult = ws_someOtherService.doSomething(someArgument);

If you want to call another authenticated webservice you just have to do the folowing things:

1. create the webservice to a variable (step one)
2. set maintain connection (step two)
3. add the cookie to the webservice (step six)

I hope this helps you and save you some frustrating hours.