ColdFusion CLIENT cookie issue

MSSQL error: The data types text and varchar are incompatible in the equal to operator.

In the ColdFusion administrator under there is the option to save CLIENT variables as cookies, in the registry, a datasource or not at all.

If the client variables are stored as cookies, it is fairly easy to retrieve and parse to determine things like the identity of a returning user from the cookie. This cookie is called CFCLIENT_<cfapplication name>. If the client variables are stored in the database, it's not so easy. Actually impossible, the cookie variables are NOT stored in the datasource.

First to locate the CLIENT variables, which are stored in a table called CDATA. CFGLOBALS client variables are session variables, like CFID, CFTOKEN and timestamp details and are stored in the table CGLOBAL.

So what about the error? The data types text and varchar are incompatible in the equal to operator. I ran some queries to try and see if there was some information I could glean from the client variables table.

view plain print about
1SELECT TOP 1000 [cfid]
2 ,[app]
3 ,[data]
4 FROM [hoton].[dbo].[cdata]
5 where [data] != ''
Error: The data types text and varchar are incompatible in the not equal to operator.

[data] is a text field permitting nulls. So I checked for null:

view plain print about
1SELECT TOP 1000 [cfid]
2 ,[app]
3 ,[data]
4 FROM [hoton].[dbo].[cdata]
5 where [data] is null
pulled up everything, to test, I ran this:
view plain print about
1SELECT TOP 1000 [cfid]
2 ,[app]
3 ,[data]
4 FROM [hoton].[dbo].[cdata]
5 where [data] is not null
which turned up nothing! So I added a space in the quotes:
view plain print about
1SELECT TOP 1000 [cfid]
2 ,[app]
3 ,[data]
4 FROM [hoton].[dbo].[cdata]
5 where [data] != ' '
The data types text and varchar are incompatible in the not equal to operator.
No go! But this worked:
view plain print about
1SELECT TOP 1000 [cfid]
2 ,[app]
3 ,[data]
4 FROM [hoton].[dbo].[cdata]
5 where [data] not like ''
which pulled up what I wanted to look at, but unfortunately could not use. No cookie info is stored in with the datasource client session storage option in the ColdFusion administrator.

Ubuntu Mounting HTC One M8 SD Card

error: exited with non-zero exit status 32: mount: unknown filesystem type 'exfat'

I added a 64 Gigabyte SDcard to my HTC One M8 phone and could not connect to add music or files. The issue is that Ubuntu 14.01 cannot read exFAT formatted drives, which includes most SDCards.

We have to enable exFAT in Ubuntu 14.01 using fuse. Fuse is already in Ubuntu but we have to install the exFAT packages.

view plain print about
1sudo apt-get install exfat-fuse exfat-utils

Then uplug your phone from the USB port and plug it back in. Then, in your phone, go into settings (I just scrolled the top bar down) and tap USB File sharing - USB file sharing mode. Then enable Disk Drive - Mount as disk drive.

In just a second you will than be able to see your SDCard in File Manager. Start dragging and dropping your folder onto your phone.

The multi-part identifier "variable" could not be bound.

Error Executing Database Query.

[Macromedia][SQLServer JDBC Driver][SQLServer]The multi-part identifier "my.variable.name" could not be bound.

This error means that you did not surround your variable(s) with pound signs (hash marks) in your query.

view plain print about
1<cfquery name="getData" >
2    SELECT     [ID],
3         [Value],
4         [Identifier],
5         [Identifier_id]
6    FROM     [MASTER_PLANNING]
7    WHERE    0 = 0
8        <cfif StructKeyExists(arguments, "ID")>
9            AND ID = arguments.ID
10        </cfif>
11        <cfif StructKeyExists(arguments, "Value")>
12            AND [Value] = arguments.Value
13        </cfif>
14        <cfif StructKeyExists(arguments, "Identifier")>
15            AND Identifier = arguments.Identifier
16        </cfif>
17        <cfif StructKeyExists(arguments, "Identifier_id")>
18            AND Identifier_id = arguments.Identifier_id
19        </cfif>
20</cfquery>

Should look like:

view plain print about
1<cfquery name="getData>
2    SELECT     [ID],
3         [Value],
4         [Identifier],
5         [Identifier_id]
6    FROM     [MASTER_PLANNING]
7    WHERE    0 = 0
8        <cfif StructKeyExists(arguments, "
ID")>
9            AND ID = #arguments.ID#
10        </cfif>
11        <cfif StructKeyExists(arguments, "
Value")>
12            AND [Value] = #arguments.Value#
13        </cfif>
14        <cfif StructKeyExists(arguments, "
Identifier")>
15            AND Identifier = #arguments.Identifier#
16        </cfif>
17        <cfif StructKeyExists(arguments, "
Identifier_id")>
18            AND Identifier_id = #arguments.Identifier_id#
19        </cfif>
20</cfquery>

can't cast String [] to a value of type [numeric]

Railo Error Message
invalid call of the function getJerseyColor (), first Argument (Color) is of invalid type, can't cast String [] to a value of type [numeric]

Using a UDF (User Defined Function) created to convert a numeric value to a string within a returned query. We have an integer stored in the database table which represents a color value. It is much easier for the user to actually read the color name instead of mentally trying to convert the number to a the color value each time. We are actually looking at Jersey colors and Jersey numbers, so coaches and evaluators can quickly and easily identify each player.

Here's the UDF:

view plain print about
1<cffunction name="getJerseyColor">
2    <cfargument name="Color" type="numeric" required="true" />
3    <cfset var JerseyColor = "" />
4    <cfswitch expression="#arguments.Color#">
5        <cfcase value="1">
6            <cfset JerseyColor = "Yellow" />
7        </cfcase>
8        <cfcase value="2">
9            <cfset JerseyColor = "Blue" />
10        </cfcase>
11        <cfcase value="3">
12            <cfset JerseyColor = "Purple" />
13        </cfcase>
14        <cfcase value="4">
15            <cfset JerseyColor = "Yellow/Blue" />
16        </cfcase>
17        <cfcase value="5">
18            <cfset JerseyColor = "Green/Blue" />
19        </cfcase>
20    </cfswitch>
21    <cfreturn JerseyColor />
22</cffunction>

Here's how the UDF is called:

view plain print about
1<cfset filterPlayer.Color = getJerseyColor(filterPlayer.Color) />

Here it is utilized replacing the returned query value for "Color":

view plain print about
1<cfquery name="filterPlayer" datasource="#application.datasource#">
2    SELECT     playerid    AS "ID",
3        rate1        AS "Color",
4        rate2        AS "Num"
5    FROM     tblplayerinfo
6</cfquery>
7<!--- make jersey color human readable --->
8<cfloop query="filterPlayer">
9    <cfset filterPlayer.Color = getJerseyColor(filterPlayer.Color) />
10</cfloop>

Now about the error....

Testing on development produced no error, while Production generated the error. Both columns in each MySQL database were numeric, of type smallint(6) and defaulted to Null. In this case, we are talking about "rate1" column aliased as "Color". The only difference between the two database columns was that the development MySQL database had actual numeric values in each row. Production had Null values in every "rate1" column row.

Doing a little reasearch, mainly Googling "can't cast string to a number value" turned up some issues about Railo and null values while casting strings to numbers. Some pondering led to realization that the column needed non Null numeric values to allow the UDF to run.

The solution was to replace the Null values in the numeric int() column to actual numeric values. Setting to 0 (zero) worked. Now the UDF runs perfectly.

Railo MySQL Error with last_insert_id()

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT last_insert_id() as newplayerid' at line ##.

Using Railo, MySQL and a SELECT last_insert_id() just after an INSERT query threw an error. It's not a problem with last_insert_id().

First, to explain what LAST_INSERT_ID() is in MySQL. If you insert a record into a table that contains an AUTO_INCREMENT column you can obtain the value stored in that column for the most recently inserted row. Instead of running an INSERT query and than attempting to find out the newly generated ID by following up with a SELECT query using the same WHERE values from the INSERT query, LAST_INSERT_ID() will return it for you.

Example:

view plain print about
1<cfquery datasource="#application.datasource#" name="registrationInsert">
2INSERT INTO tblplayerinfo (firstname,lastname,birthdate,region,
3email,cellphone,homephone)
4VALUES (    
5 <cfqueryparam value="#form.firstname#" cfsqltype="cf_sql_varchar"/>,
6 <cfqueryparam value="#form.lastname#" cfsqltype="cf_sql_varchar" />,
7 <cfqueryparam value="#form.birthdate#" cfsqltype="cf_sql_date" />,
8 <cfqueryparam value="#form.region#" cfsqltype="cf_sql_varchar" />,
9 <cfqueryparam value="#form.email#" cfsqltype="cf_sql_varchar" />,
10 <cfqueryparam value="#form.cellphone#" cfsqltype="cf_sql_varchar" />,
11 <cfqueryparam value="#form.homephone#" cfsqltype="cf_sql_varchar" />
12);
13SELECT last_insert_id() as newplayerid;
14</cfquery>

The select query at the bottom will return the the newly generated auto incremented ID as "newplayerid" referenced in ColdFusion or CFML as the variable "registrationInsert.newplayerid", in this case.

This variable can now be referenced for other purposes, such as:

view plain print about
1<cfset session.playerid = registrationInsert.newplayerid />

or with <cflogin> which, in this case, allowed the user to come back and edit their player information.

OK, so I made you read a whole bunch of stuff about last_insert_id() without giving the answer to the Railo error, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT last_insert_id() as newplayerid' at line XX"/

The Answer:
Make sure that "Allow multiple Queries" is set to TRUE in the Railo admin for your datasource and that you actually seperate your queries with a semicolon(";") in your coded CFML query, <cfquery>.

The answer is a simple as that.

SVN Post-Commit Hook Failed (exit code 1) with output

post-commit hook failed (exit code 1) with output:
Conflict discovered in
'C:/filestorage/directory/yada/yada/screwed-up-file.cfc'.
Select: (p) postpone, (df) diff-full, (e) edit,
(mc) mine-conflict, (tc) theirs-conflict,
(s) show all options: svn:E00714: Can't read stdin: End of file found

Using SVN through CFEclipse and SubVersion the above error occurred when trying to commit a new template. A newer user pushed up the 'screwed-up-file.cfc', though I am unsure what caused this.

What did NOT work

  1. Deleting the local project from the workspace and rechecking out the project.
  2. Google - could not find any help.

What DID work

Deleting the offending file from the local project. Committing the deletion to the SVN repository and then creating the offending file again. In my case I made a copy in Notepad (or someother IDE outside of CFEclipse) before deleting and just recopied the file back. Then I committed the new (same) file back up.

Hopefully this will help you, if you are Googling all over the place for some advice!

Debugging CF and JavaScript, flexbox combobox jQuery

Invalid JSON property [object Error] found when trying to apply resultTemplate or paging.summaryTemplate. Please check your spelling and try again. Error: Unable to get value of the property 'name': object is null or undefined

Getting errors when using flexbox combobox on IE7 and IE8. The code runs on IE9, Chrome, and all Firefox.

Using ColdFusion to generate JavaScript can sure lead to complications when trying to debug. It's complicated trying to write the CFML to generate the code, but we do it to make our lives easier!

This post is more about the process of debugging than writing or even the actual solution. First I will list efforts that proved worthless. I'll bet you tried at least one of these before finding this post.

  1. Searching for "resultTemplate" and/or "summaryTemplate"
  2. Editing jquery.flexbox.js. I actually put in "alert"s to step debug – waste of time.
  3. Edited the generating CF code to add spaces in the resultant JS.
  4. Did a LOT of Googling. This only helped if you found this article, which wasn't here when I went through the process!
So what worked? or how do we find the cause?

The first objective is to narrow down the area which is causing the problem. Because it was clicking in the combobox throwing the error, that sure helped. Next is to look at the JavaScript generated for the combobox. Here's what I had.

view plain print about
1<scr!pt type="text/javascript">var values_6789 = {};values_6789.results = [{id: "View The Collection", name: "View The Collection"},{id: "View The Trunk Show Collection", name: "View The Trunk Show Collection"},{id: "Personal Appearance", name: "Personal Appearance"},];values_6789.total = values_6789.results.length;$(document).ready(function () {$("#misc2").flexbox(values_6789, {autoCompleteFirstMatch: false, selectBehavior: true, paging: false, width: 180, noResultsText: "" });});</script>

Looking through this code, something very small is out of place, a comma.

view plain print about
1......name: "Personal Appearance"},];values_6789.total =......

The comma just before the closing bracket, "]".

Here's the code that builds that part of the JavaScript

view plain print about
1<cfloop query="arguments.qProductAddorValues">
2 <cfif arguments.qProductAddorValues.productAddorID eq arguments.productAddorID>
3 <!--- write element for the JSON object --->
4 <cfset fldString.append('{id: "#arguments.qProductAddorValues.value#", name: "#arguments.qProductAddorValues.name#"} ' )>
5 <cfif arguments.qProductAddorValues.currentrow neq arguments.qProductAddorValues.recordcount>
6 <cfset fldString.append(',')>
7 </cfif>
8 <cfif (len(arguments.fldval) and not cbBoxFldValAdded)>
9 <!--- this string is used for initialization of the control --->
10 <cfset defaultSelection = ',initialValue: "#arguments.fldval#"'>
11 <cfif arguments.qProductAddorValues.currentrow neq arguments.qProductAddorValues.recordcount>
12 <cfset fldString.append(',')>
13 </cfif>
14 <cfset cbBoxFldValAdded = 1>
15 <cfelse>
16 <cfif (arguments.qProductAddorValues.default eq 1 and not cbBoxFldValAdded)>
17 <cfset defaultSelection = ',initialValue: "#arguments.qProductAddorValues.value#"'>
18 </cfif>
19 </cfif>
20 </cfif>
21</cfloop>

Why is that extra comma there? There's a checking for the end...

view plain print about
1<cfif arguments.qProductAddorValues.currentrow neq arguments.qProductAddorValues.recordcount>
2 <cfset fldString.append(',')>
3</cfif>

The logic is probably bad. To prove it we have to output the 2 variables in the test. We have to output them and NOT break jQuery so the page will render AND still display the variable output.

This is done by commenting out the variable output with JavaScript syntax, /* stuff */. First, I create a string with the 2 test variables and concatenate them together with opening and closing JS comments.

view plain print about
1<cfset JSStringTest = "/* " & arguments.qProductAddorValues.currentrow & " = " & arguments.qProductAddorValues.recordcount & " */" />

Then I stick output of the string into the generated JS string where I think it will be innocuous. Outside of the array but inside the single quote.

view plain print about
1<cfset fldString.append('{id: "#arguments.qProductAddorValues.value#", name: "#arguments.qProductAddorValues.name#"} #JSStringTest#' )>

Here is the resultant JavaScript output:

view plain print about
1<scr!pt type="text/javascript">var values_6789 = {};values_6789.results = [{id: "View The Collection", name: "View The Collection"} /* 2 = 63 */,{id: "View The Trunk Show Collection", name: "View The Trunk Show Collection"} /* 3 = 63 */,{id: "Personal Appearance", name: "Personal Appearance"} /* 4 = 63 */,];values_6789.total = values_6789.results.length;$(document).ready(function () {$("#misc2").flexbox(values_6789, {autoCompleteFirstMatch: false, selectBehavior: true, paging: false, width: 180, noResultsText: "" });});</script>

You can see that the commented /* 4 = 63 */ for the final entry is obviously false. Thus proving the test is bad.

From here I have 2 options

  1. Find an accurate test.
  2. Just remove the last comma.

In this instance there really wasn't a valid test that could be easily and accurately generated. Information was being pulled from too many different places. I decided to just add the trailing comma after every iteration and remove it from end of the string after the loop is completed, but before we appended more JavaScript.

This turned out to not be easy either. If you recall, the string, fldString was appending JavaScript using:

view plain print about
1fldString.append()

This was because fldString is not really a string but a Java object created with:

view plain print about
1fldString = createobject("java", "java.lang.StringBuffer").init()

using CF's create Java object and Java's StringBuffer class. So the "fldString.append()" is a StringBuffer method.

What other StringBuffer method can I use to trim off the pesky trailing comma? We could use deleteCharAt() but how to find what the index is of the comma. It's at the end of a variable length string, changing every time the function is called. Well we just find the length of the string using another StringBuffer method, length(). Can't get much simpler than that! So here is how that code looks:

view plain print about
1<!--- remove trailing comma which is always added above ~ line 156, using Java StringBuffer class --->
2<cfset fldString.deleteCharAt(fldString.length()-1) />

Starting from the inside, we find the fldString length, subtract one and delete the character in that position.

So here's the final bit of completed code:

view plain print about
1<cfloop query="arguments.qProductAddorValues">
2 <cfif arguments.qProductAddorValues.productAddorID eq arguments.productAddorID>
3 <!--- write element for the JSON object --->
4 <cfset fldString.append('{id: "#arguments.qProductAddorValues.value#", name: "#arguments.qProductAddorValues.name#"},' )>
5 <cfif (len(arguments.fldval) and not cbBoxFldValAdded)>
6 <!--- this string is used for initialization of the control --->
7 <cfset defaultSelection = ',initialValue: "#arguments.fldval#"'>
8 <cfset cbBoxFldValAdded = 1>
9 <cfelse>
10 <cfif (arguments.qProductAddorValues.default eq 1 and not cbBoxFldValAdded)>
11 <cfset defaultSelection = ',initialValue: "#arguments.qProductAddorValues.value#"'>
12 </cfif>
13 </cfif>
14 </cfif>
15</cfloop>
16<!--- remove trailing comma which is always added above ~ line 156, using Java StringBuffer class --->
17<cfset fldString.deleteCharAt(fldString.length()-1) />

Bam! The JSON error is gone in IE7 and IE8 and the code still runs perfectly in all other browsers. This is a long story with a few important pieces. I'll break out the useful tidbits and post those in separate blog entries so that others may find specific answers without having to read this saga.

jQuery flexbox IE8 invalid JSON property

Invalid JSON property TypeError: Unable to get value of the property 'name': object is null or undefined found when trying to apply resultTemplate or paging.sumaryTemplate.

We are using a jQuery flexbox for a dropdown combobox in a form. Clicking on the dropdown combobox field generates the above JavaScript alert ONLY in IE8. In all other browsers the combobox works correctly.

So, in short, the error occurs because of the IE8 parsing engine. Quit looking elsewhere!

Here is the fix, place this in your head:

view plain print about
1< meta http-equiv="X-UA-Compatible" content="IE=EmulateIE7" />

This worked. Newer versions of jQuery are backward compatible so having IE run as IE7 doesn't cause any problems for us in our situation.

Reference: MSDN

BlogCFC tblblogpagescategories' doesn't exist

Table 'tblblogpagescategories' doesn't exist

Trying to add pages in BlogCFC 5.9.8.012 throws the error.

The reason is there was a table added in 5.9.8.11, but the readme.txt doesn't tell you which table was added. It also says the SQL files were updated for the new table BUT it fails to mention the installer was not updated. This means you have to get the error before you know to fix it. Once you figure all that out....

Just run the SQL for the new table, tblblogpagescategories:

view plain print about
1CREATE TABLE tblblogpagescategories (
2 categoryidfk varchar(35) NOT NULL,
3 pageidfk varchar(35) NOT NULL
4 )

Now you can add pages!

BlueOnyx Recursion Error

We moved a site off of the BlueOnyx box to a wordpress site, but wanted to continue using the BlueOnyx server for mail and mailing list management. I went into my domain registrar and reset the DNS servers to the new ones outside of my hosting provider's.

When sending an email to one of the mailing lists on this domain, I received the following error:

An error occurred while sending mail. The mail server responded:
5.0.0 rewrite: excessive recursion (max 50), ruleset canonify. Please check the message recipient
touchplayers@dallastouch.org and try again.

In the BlueOnyx GUI, I changed the Host Name for the domain from "www" to "mail". I ensured that the site was not managing any domains. Under DNS, the Selected domains box was empty. I restarted sendmail

view plain print about
1/sbin/service sendmail restart

Testing the email list produced this error on a returned email:

----- The following addresses had permanent fatal errors -----

(reason: 553 5.3.5 system config error)
----- Transcript of session follows -----
553 5.3.5 mail.mydomain.org. config error: mail loops back to me
(MX problem?)
554 5.3.5 Local configuration error

I made sure we had a 2 A records in the new DNS server. An A record that generated mail.mydomain.org and an MX record that pointed to that A record.

Testing the email server after that produced the same loop back error.

Changed the the Host name back to "www" from "mail" and restarted sendmail. Definition of insanity - same result. Recursion error. Changed the Host name, once again, to "mail".

Back to BlueOnyx GUI. Under "Services" on the left menu, clicked Web, checked to make sure the domain was a web server alias - mydomain.org. Checking just below on the menu, Email, and made sure that the Email Server Aliases were there - mydomain.org & mail.mydomain.org. Saved.

Went to /etc/mail/local-host-names and made sure that mydomain.org & mail.mydomain.org were present and www.mydomain.org was NOT present.

Restarted sendmail.

view plain print about
1cat /etc/mail/local-host-names
2/sbin/service sendmail restart

Here's the checklist:

  1. Change the Host name from "www" to "mail".
  2. Add A record for mail.mydomain.org and an MX record to point to it.
  3. In BO, make sure the domain, mydomain.org, is a Web Server Alias, under Web Settings
  4. In BO, make sure that mydomain.org & mail.mydomain.org are Email Server Aliases under Email Settings
  5. Check /etc/mail/local-host-names - mydomain.org & mail.mydomain.org present, www.mydomain.org absent
  6. Restart sendmail

More Entries

BlogCFC was created by Raymond Camden. This blog is running version 5.9.7. Contact Blog Owner