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:

Copy to Clipboard

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?