Posts by arrgh

ColdFusion supports several persistent scopee. Take a look at the documentation for the session (per user) and application (entire app) scoopes.

I found that by using a direct query, rather than a QoQ, it is less sensitive to what it thinks the datatype is.

Good. A direct query offers the most control anyway.

What you say about the IN operator makes sense, but from what I've read elsewhere on this CF passes the multiple values over as a list.

If you're using cfqueryparam list="true" it does. ie

        WHERE field IN ( <cfqueryparam value="x,y,z" cfsqltype="(your type)" list="true"> )

Also what's the data type of the paid_date column?

And I get this error:

Invalid parameter type.

It's hard to follow all of that without seeing the values. Can you put together a small concrete example of the QoQ that's failing (with the values)? Btw, do you really have to use a QoQ? Couldn't you use INSERT ... SELECT.

the WHERE statement generated drops an extra single quote

Probably because you're using dynamic sql? CF always escapes single quotes. It guards against sql injection. Only way to avoid it is disable the protection altogether by using PreserveSingleQuotes.

field IN ('#form.field#')'

You need to quote each value inside the variable. ie

  `field IN ( 'x', 'y', 'z' )`

... by putting quotes around the whole thing you're searching for a single value instead ie

  `field =  'x,y,z' 

Also while it's a bad idea to send passwords in the url, method "post" isn't any more secure than "get".

Since you can't share cookies across domains, maybe both ends could establish a short term "token" for authenticated users. Then you'd pass the token in the cflocation url. The other side checks the token and destroys it. If the token is valid it logs the user in (or whatever it's supposed to do).

Unfortunately I don't know how to decode UTF16. If it were a regular UTF8 string - or the values always had 2 leading zeroes - you could decode it like any 2 char hex string. Loop and grab every 4 characters. Use inputBaseN to get the ascii code, and convert it to a character. Problem is that would not work if the first 2 chars were something other than 00. Not much help I know, but it may give you some ideas.

<cfloop from="1" to="#len(theString)#" index="x" step="4">
                <cfset fourChars = mid(theString, x, 4)>
                <cfset theChar = chr( inputBaseN( fourChars, 16))>
                <cfoutput> character at [#x#] = #theChar# </cfoutput>
            </cfloop>

That's not possible w/cfhttp. Think of cfhttp like a mini browser. The post, and any redirection on the remote server's end, all takes place inside cfhttp. Once the call exits, the user browser is still on your server. The only way to redirect them to another url programmatically is w/cflocation (or a javascript/html form submit).

However, I am not redirected to the URL indicated, I am stuck at the page with the script running my validations.

But it's not clear from the description what part you're stuck on. You cfhttp post data to "some other server". Then what? ie What're you trying to do that's not working?

That won't work. <cflocation> only works with relative paths

cflocation can redirect to an absolute url ie http://www.somesite.com, so I don't think that's the problem.

Double the pound signs. ie Use ## instead of #

examples:
<cfset value = "print ## sign">  
<cfoutput>print ## symbol</cfoutput>

Yeah I think it'd be easier if the table had a date range (effective start to end). I have a meeting now so I'll have to check back later.

1) where date discontinued is between the start and end date
2) If the start date is NEQ to the end date I need to find if there is another entry with a discontinued date
3) if not then output the entries that have a NULL endate.

The 1st one's clear ... but I don't follow the rest of it. Why display records that don't match the filtered dates?

Can you give an example of all 3 cases using your sample data?

I'm just glad it was the 31st. Often examples are ambiguous like 06/10 .. so you don't even know there's a problem until the query blows up. lol

31/8/2011

Also you can't use dd/mm/yyyy format. The standard functions expect values to be in U.S. date format ie mm/dd/yyyy. If your CF server is running under a locale where the standard is dd/mm/yyyy (like in England) then use the LS functions to parse the value first.
http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_p-q_18.html

<cfqueryparam value="#LSParseDateTime("31/8/2011")#" ....>

If your locale IS English/US, then you'll have to split it into month, day and year manually. Then use createDate() to construct the date.

<cfqueryparam value="#createDate(year, month, day)#" ....>

Ok. Sorry it's been so hectic I haven't had a chance yet.

What's your CF version?

I tinkered with that a bit, with no luck before trying a few others I found from searching for DSN-less connections.

Sorry I didn't test it. Just assumed it worked. So much for assumptions.. lol Still it was a bad example anyway. So no loss there.

if ( left(trim("query statements"), 6 ) is 'select'){//select statements

That function's better, but not very robust. If I get a chance, I'll try and find one and post it.

However, it did not seem to update my database, which is a different matter for me to investigate.

Run the same statement, credentials,etc... in your php example. If it works there it's a problem with the cffunction.

The info being passed is being generated after the payment info is saved. So I'm not too worried about that.

Just be sure it's not info that can be used to access the payment info either. Because that's just as insecure ;-)

Let me know if you have problems w/the link. If use CF's pure jdbc driver instead of the one in the link. I didn't have time to hunt down a better example. But if you search a little, I know they're some better ones out there.

This information is passed whenever someone registers and pays for a class on there.

Don't they provide other transfer methods, like a web service? Seems strange an outside company would even want you connecting directly to their servers.

Is there a similar way that I can get around using a datasource defined at the admin level, and just directly call the db, as the above script does?

It's possible to create direct jdbc connections, instead of using cfquery. The link below should work .. but it's not a good example. It uses the slow jdbc:odbc driver. Do a search on dsn-less connections for better examples
http://www.hosting.com/support/mysql/coldfusionstring

But I'd be concerned about security. I doubt that method's secure and sending payment info insecurely is a recipe for disaster.

The one on server A needs to pass some info to the one on server B.

How much information? How often?

dbname.db.0000000.thisdbhost.com

What are you pointing to that location? Connecting to a remote database MAY be possible if both your firewalls and permissions allow. They often don't for obvious security reasons. What's your setup?

Can't help you with jquery. But why not just use CF's built in autosuggest? Are you running CF8 or higher?

http://tutorial7.learncf.com/

Is there a way, where i can get two values from a option tag

No. If there isn't a unique key you can use instead, you could concatenate the values into a single string. Pick a delimiter that doesn't occur in the data. Then use list functions to separate the values.

<!--- form --->
<option value="#value1#:#value2#">#value2#</option>

<!--- action page --->
<cfset value1 = getToken(form.selvalues, 1, ":")>
<cfset value2 = getToken(form.selvalues, 2, ":")>

Where col1="#value1#" and col2="#value2#"

Don't forget to use cfqueryparam to protect against sql injection.

So how can i know which button was clicked.

Only the button clicked will exist on the action page. If all 4 buttons share the same name, use the button value ie #form.submitButton#

ie

<cfparam name="form.submitButton" default="">
<!--- display selection --->
<cfdump var="#form#">

<form method="post">
<input type="submit" name="submitButton" value="add" />
<input type="submit" name="submitButton" value="divide" />
<input type="submit" name="submitButton" value="multiply" />
<input type="submit" name="submitButton" value="divide" />
</form>

<cfif #dateAdd("d", -1, checkResident.dayleft)

That only checks whatever dates happen to be in the 1st record of the query. Remember you removed the WHERE clause. So there might be more than 1 record returned. For accurate results you'd have to cfloop through the query to check all of them.

The original sql didn't work because I didn't know the data contained times too. So it was the wrong the comparison. Just adjust it to account for the time.

Example: if today is 8/30/2011, #selectedDate# will be 8/10/2011 and #dayAfter# will be 8/11/2011.

<cfset todaysDate = createODBCDate(NOW())>
<cfset selectedDate = dateAdd("d", -20, todaysDate)> 
<cfset dayAfter     = dateAdd("d", 1, selectedDate)>

<cfquery name="checkResident" datasource="residents5">
SELECT *
FROM hospital
<!--- be sure to use cfqueryparam if resident id is a variable --->
WHERE ID = 193
AND   dayLeft    < <cfqueryparam value="#dayAfter#" cfsqltype="cf_sql_date">
AND   dayReturn > <cfqueryparam value="#selectedDate#" cfsqltype="cf_sql_date">
</cfquery>

Oh, no. Don't use that code. If it works, it's pure luck ;-)

What's this "dayout" and "dayin" value? Are you using those columns or just DAYreturned and DAYleft?

Oops... our responses keep overlapping. lol

We are storing date/times but not using the times for this report. This is a MYSQL DB

Yep. That explains it :) What #selectedDate# did you actually use in your test? Still "07/11/2011"?

Since it's not trivial stuff, I'd rather recommend the right code. But without the debugging output, I don't know if what you posted is right or not ;-) If you can post it, I can tell you what went wrong and how to fix it. Correctly.

I changed cfsqltype="cf_sql_timestamp" to cfsqltype="cf_sql_date"

Shouldn't make a difference. Not unless you're storing times too, not just dates. Are you? Also, what db type?

It will not output if the selectedDate = dayleft.

Let's do some debugging. Grab all records for resident #193. Can you dump the dates only. Then post the results. So we can see why they're not matching?

<cfquery name="checkResident" ...>
SELECT dayleft , dayreturned 
FROM hospital
WHERE residentID = 193
</cfquery>

Debugging stuff <br>
selectedDate = <cfdump var="#selectedDate#">
<cfdump var="#checkResident#">

Then all you'd need is this query.

ie
<cfset selectedDate = "07/11/2011">

<cfquery name="checkResident" ...>
SELECT *
FROM   hospital
<!--- be sure to use cfqueryparam if resident id is a variable --->
WHERE  residentID = 193
AND    dayleft <= <cfqueryparam value="#selectedDate#" cfsqltype="cf_sql_timestamp">
AND    dayreturned >= <cfqueryparam value="#selectedDate#" cfsqltype="cf_sql_timestamp">
</cfquery>

<!--- if a matching record was found .. --->
<cfif checkResident.recordCount gt 0>
  <cfoutput>resident was in the hospital on #selectedDate#</cfoutput>
</cfif>

Or you could generate a list of all residents in the hospital on date X by eliminating the residentID = 193 clause. Then output the query results.

<cfoutput query="checkResident">
    ... show names ...
</cfoutput>

I think so. Can you also post a few examples? Those are better than a 1000 words of description. Something like this

theSelectedDate => 08/30/2011

Sample Data
recordID, dayLeft, dayreturned,
#1, 08/01/2011, NULL
#2, 08/10/2011, 08/16/2011
#3, 08/28/2011, 09/05/2011
....

Which records should the query return and why?

It's not going to be nearly that complicated.

What's your query used for in plain english? ie Is it a reservation system and you're trying to find out what's booked?

Neither of these seemed to work

Can you give an an example of all 3 dates and what you expect to happen?

When I do this in MSAccess sql view it works but doesn't work on cfm page.

(Yeah, that's special "Access" syntax. It may work with some drivers, or it may not.) But the correct way to pass dates in cfquery is with cfqueryparam.

WHERE Stuff.RegDate = <cfqueryparam value="5/1/2004" cfsqltype="cf_sql_timestamp">

Please help I have also tried shifting the date formats.

That only changes the user display. It doesn't make any difference to your query.

This has nothing to do with CF. It is a pure SQL question ;)

AND MoveOutDate = 'NULL'

NULL isn't a string. It is a special system value. You can't use equals "=" either. To find nulls you must use

WHERE  ColumnName IS NULL

Yes, I've tried the cfdocument tag, and it is creating pdf forms for me, but I'd like to get data from a database to populate the pdf form based on the user id that is passed in the url.

Huh? ;) cfdocument doesn't create or populate forms. But you can populate forms with cfpdfform.

You're welcome. I don't know why the charset="utf-8" isn't taking effect. But at least there is a work around!

That is the same code they started with. Anyway, it's been 3 months. I'm pretty sure he's solved it by now ;-)

Huh?

Don't know about your code. But Paolo's example shows a java.io.ByteArrayOutputStream as the fileContent.

<cfdump var="#response#">

A possible fix is explicitly convert it to UTF8 first. Then deserialize.

...
<!--- response is a java.io.ByteArrayOutputStream for some reason?
      convert it to UTF8 string --->
<cfset utf8Response = response.fileContent.toString("UTF8")>
<cfset response = DeSerializeJSON(utf8Response) />
<cfset townName = response.geonames[1].name />
...

... or

...
<cfset response = DeSerializeJSON(ToString(response.fileContent.toByteArray(), "UTF8")) />
<cfset townName = response.geonames[1].name />
<cfcontent type="text/html; charset=utf-8"/>
<cfoutput>#townName#</cfoutput><br />

You need to read the file content into a variable. Then use <cfmailparam content="...">. Then it won't matter if you delete the file.
http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7c15.html

I think the mail could not be sent before the directory is deleted.

No, not if you're using spooling. With spooling mail is sent via a separate thread and may be sent after you delete the file. If you read the docs on cfmail that's the expected behavior (see below). If you're on 8.0.1+, a better option is to use <cfmailparam content="..."> instead.

http://livedocs.adobe.com/coldfusion/8/Tags_m-o_01.html
Note: The cfmail tag does not make copies of attachments when spooling mail to disk. If you use the cfmail tag to send a message with an attachment with spooling enabled and you use the cffile tag to delete the attachment file, ColdFusion might not send the mail because the mailing process might execute after the file was deleted. ....

.. and what happened with the test page? Does the character appear on ALL cfm pages or just that one?

If you view the source for the 2 pages, it's different. So what's the CFM code used to generate this section? It's not in the HTML you posted above.

src="images/linkedin.jpg" /></marquee></p>
                	</td>
				</tr>             
				</table>

	Â 			<br />&nbsp;<br />	
    		</td>

Did you check all directories in the heirarchy? Because the code you posted doesn't create that character when run as a .cfm page. Create a simple test page with just 1 line:
<cfoutput>#now()#</cfoutput>

If the character shows up, it's almost certainly an Application.cfm/cfc problem. If not, try copying the code into a new page. (Use a text editor like notepad). Does it still occur?

Ok. You might want to read up on the http specs, so you'll understand why it's not possible. Best of luck.

I don't see a stray characters. If it only happens with .cfm files, check your Application.cfm/cfc files. It runs every time you request a .cfm page. So it might be the culprit.

Even with a link, you can't hide url variables. They're visible by design and you can't change that. The best you can do is encode them or hash them into a "tiny url". But you're still passing the values. But like I said, obfuscation is a poor substitute for real security. If there's information people shouldn't see, it should be protected by a login and validated on each page.

You can't hide a query string. That's not how it's designed. You can however

- encrypt query strings
- use method=post instead of method=get, so variables are not sent through the query string
- or store variables in the session scope instead

.. But obfuscation isn't a substitute for good app security.

What's going to happen when the records increase to 35K, then 40K? Trying to do it all at once isn't scalable. The right way to do things is break the process into smaller batches.

That's too much for 1 page. Set it up as a scheduled task. Have the task pull 500 records. Process them, then have the task reschedule itself if there's more records to process.

Styling dynamic values is difficult at best. You have to at least know what type of value you're working with. And it has to be kept separate from any html. Otherwise, it's impossible to do it reliably imo.

I've had those weeks before .. and they're hell. But since you are parsing/manipulating html, sad to say that's par for the course, lol.

Yeah, lately there's almost no action here. Adobe's forum is the go-to-place I guess.