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.

Using Full Text Indexing

In this page I will explain how to create a full text index on large character columns, Filestream or XML.
Step 1: create a catalog
USE [my_db]

CREATE FULLTEXT CATALOG [proverbs]
WITH ACCENT_SENSITIVITY = ON
AUTHORIZATION [dbo]

You can check this with:
SELECT fulltext_catalog_id, name FROM sys.fulltext_catalogs

Here I have created a full text catalog called proverbs which will be stored in the database. If you are using SQL 2005 you can use the IN PATH N'd:\somepath' to specify it's location, this tag is depricated in SQL 2008. ACCENT_SENSITIVITY means that there will be a difference between café and cafe. The last line sets the dbo as owner.

Step 2: create an index
CREATE FULLTEXT INDEX ON [dbo].[proverbs]
KEY INDEX PK_proverbs ON proverbs
WITH CHANGE_TRACKING AUTO

This creates an index on the table [dbo].[proverbs] using the cluster index name PK_proverbs in the catalog proverbs created in step 1. Basically adds the table with the primary key to the catalog

Then add the column on which you want to search:
ALTER FULLTEXT INDEX ON [dbo].[proverbs] ADD ([proverb])

This enables fulltext indexing on the column proverb of the table [dbo].[proverbs].


Then enable the index which will do a rebuild of the index:
ALTER FULLTEXT INDEX ON [dbo].[proverbs] ENABLE

Step 3: test with a select
SELECT * FROM [dbo].[proverbs] WHERE CONTAINS ([proverb], 'something')

With the CONTAINS statement you can use keywords AND, OR, NEAR, and use wildcards like '*'