Another Random Order Retrieval of Record Threads

I did a search and none of the solution posted worked for me. I'm using MS Access for database and a ColdFusion as front end. I want to be able to retreive in SQL a set of records in random order. I tried various things, like rnd(), rand() and newid().

Since rand() and newid() didn't seem to be part of MS Access it didn't work. rnd() used in a similar fashion as follows:

SELECT * FROM some_table WHERE condition = 0 ORDER BY rnd(integer_primary_key);

only worked in Access, but did not return random results in ColdFusion Page...

Anyone have any suggestions on how to randomize the order of how records are returned?

ColdFusion has nothing to do with this. Or rather, it shouldn't have anything to do with this. If your SQL statement worked for you in Access, that is how it will be returned to ColdFusion. The only possibilities I can think of are, either, you're not calling it right. Or, CF is attempting to be smart and is reordering the results.

I don't know a lot of CF (pretty obvious why ;) ), but show some code, we may attempt to decipher it.

Sorry about the late response. I left on a week long trip and recently came back. To top it off I forgoto my password to this account.

Anyhow, as you said I did not see why this has anything to do with CF, but considering it works with Access and not in combination of the two...???

Here is the code:

Code:

---


<cfquery name="qryListingsGold" datasource="#_dsn_#">
SELECT *
FROM table
WHERE field = value
ORDER BY rnd(one_field_integer_primary_key);
</cfquery>

<cfoutput query="qryOutput">
        #field_name#...
</cfoutput>

---

The wierd thing I noticed that if I kept refreshing the page, I get two different orders, but they are always in the same order.

If no one comes up with an answer, I'll be talking to a CF expert tomorrow and I'll put this situation to him. But I think it would be more efficient to just take the records from the returned recordset in random order than asking Access to randomize the return.

Okay ...

My expert says it's probably the ODBC driver that's causing the problem, since there's obviously (hah! it may be obvious to him ...) no error in your CF or SQL. He says to look for a type 4 JDBC driver, and that there should be some open source drivers available. (Don't ask me, I'm just repeating what he said as he ran off to bed.)

Does coldfusion have a recordset or dataset type object (a collection of the rows) which you can randomly select from?

http://en.wikipedia.org/wiki/JDBC_type_4_driver

I don't know much about Java or JDBC and not many people here will know it either, mostly MS technologies in use here. And PHP.

According to my expert, CF uses a JDBC link to the database.

According to my left pinky, you need to try to find out about some sort of a dataset or record collection object in which you can programmatically choose an index from, so that you amy implement random record selection in your coldfusion code. If CF can do that.

I know what JDBC is, it's quite obvious from the name, but I don't know much about how it works and how it may be affecting this. Why don't you ask him to give you an example?

I would, mendhak, but it was an off-the-cuff "is there anything you can see wrong with this" kind of question. His answer was that the ODBC driver might be causing it if it's an older one. What I'm paying him for isn't to debug code on vbf.

I agree with you though, that getting the recordset and then choosing random records would be a lot easier.

Al42,

Thanks for checking in with your expert!!! I think he might be right unfortunately, since this is shared hosting I cannot simply install a Type 4 driver. Worse is that I'm using MS Access for simplicity, using type 4 usually requires a much more sophisticated DB. Hostibng is through GoDaddy so I assume they are pretty good with patching and keeping stuff up to date...

I will check with the CF forums and see what they suggest. I'll post my result here if it gets resolved...

Quote:

---

Originally Posted by oioioi

Hostibng is through GoDaddy

---

Have you called tech support? They've been pretty nice to me when I've made obvious goofs to my code. Never called me an idiot, although I deserved it a few times. :)

Ok, update on the solution for those facing the same issue. Turns out it was something I ruled out immediatley due to a bad assumption. Problem was caching in CF is automatic wether you specify cachedwithin attribute or NOT...

I assumed that not including cachedwithin would default to no caching... One should default to most accurate information and allow for optimization and NOT optimize for speed for the cost of accuracy. The livedocs is full of errors it seems like as I have encountered this sort of thing before... That is why Java as a language rocks. Everything is specified up front!!! Well as long as I programmed in it have I ever encountered stuff like this (over 6 years of java programming).