Transform Query inside cfscript to not access java objects

[lovewebdev]

When I uploaded my page, now my webhost is saying my queries accessing java objects isn't allowed. I'm having trouble figuring out how to change these queries so they don't use a java object.

Here is one of my queries. All my other queries are in the same format:

objFactory1 = CreateObject(
"java",
"coldfusion.server.ServiceFactory"
);
// Get the Data Source service from the service factory.
objDataService1 = objFactory1.DataSourceService;
// The data service object has access to all data sources
// running on the server. Let's get a connection to our
// datasource before running the query.
objDataSource1 = objDataService1.GetDataSource(
"myapp"
);
// Open the connection. Here, we have the option to pass
// in a username and password. Since I am on the dev
// server, no need to do so.
objConnection1 = objDataSource1.GetConnection(
// USERNAME, PASSWORD if needed //
dsnlogin,dsnpwd);
// Prepare the SQL statement that you want to run. Much
// harder than the CFQuery tag, but not impossible.
getevents = objConnection1.PrepareStatement(
"SELECT " &
"dtstamp, " &
"eventname " &
"FROM " &
"events " &
"WHERE MONTH(DATE(dtstamp)) = " & CurMonth & " AND DAYOFMONTH(DATE(dtstamp)) = " & i
);
// of object that we are all used to working with.
getevents = CreateObject(
"java",
"coldfusion.sql.QueryTable"
).Init( getevents.ExecuteQuery() );
// Close the connection.
objConnection1.Close();

I'm returning columns like this : getevents.eventname[intRow]

[Adam Cameron.]

Why on Earth are you running a query like that?

--

Adam

[lovewebdev]

lol I don't know. It was part of function I had to tweak. I didn't write the query. I just had to work with it.

[Adam Cameron.]

Just turn it into a <cfquery> call and be done with it.

Well that's what I'd do.

--

Adam

[lovewebdev]

This is all inside cfscript and can't leave the cfscript tag so I need to keep it a query inside the cfscript tag.

[Adam Cameron.]

People do make rods for their own backs, don't they? 😕

Which version of CF?  If CF9, queries are supported in CFScript without all that horsing around you're doing here.

If an earlier version, create a UDF or a CFC method which does all this work and then call the function/method from the CFScript block.

Is it too late to line up and shoot whoever wrote that code initially?  Just a suggestion.

--

Adam

[lovewebdev]

It's coldfusion 9. I don't know how to turn that query into a CF9 query supported by cfscript?

[Adam Cameron.]

How far did you get doing it yourself, where's your code that's giving you a problem what is the problem, and how have you gone about solving it?

--

Adam

[lovewebdev]

I just don't get queries in cfscript much. I don't have my code saved. I just test and delete when it doesn't work. Adobe docs don't have very good examples and the online resources I've found didn't really help me much.

If you can't help, I'd appreciate a link online to how queries are done in cfscript, then I'll figure it out. Thanks anyway.

[Adam Cameron.]

I don't do other people's work for them, I'm afraid.  Sorry.  But I will try to help them do it themselves if they seem to be putting some effort into it.

I do not believe that there's nothing in any of these links that can't explain it to you:

http://lmgtfy.com/?q=cf9+query+in+cfscript

And if none of them can, I don't know how to better explain it. I don't mean to sound obtuse (well: maybe a bit), but there's really nothing complicated about it.  Even just the official docs - first link - explains it thoroughly.

Can you not get even a basic SELECT * FROM someTable query working with it?  Have you actually tried?

You are going to have to give it a go, and pony-up with some code that you've having problems with.  Otherwise it would just seem like you can't be bothered.  In which case: neither can I.

It's 2am and I've got better things to do (sleep, mostly ;-), but I'll check back in in the morning.  Well... late morning... I've got a day off tomorrow.

--

Adam

[lovewebdev]

Seriously, you should read what you write before you click submit, because you're really rude and you make generalizations and hurtful assumptions about people.

Thanks anyway, go to sleep bro.

[Adam Cameron.]

There is nothing in that post that is rude.  It is, however, to the point.  You might not like my assessment of the situation, but that doesn't make it rude, it just makes it be something you didn't like the sound of.  Those are two different things.

All I'm asking is for you to meet me half way with some effort into getting this problem sorted.  Where are you getting this expectation that I (or anyone else here) should be doing your work for you?  That's a serious question.  You might say I'm making unfounded assumptions here, but look at it from my perspective: how else would you interpret it?

Put a small amount of effort into trying to learn how to solve this problem for yourself, demonstrate you've given it a bash by posting the code you get stuck with, and we can go from there.  How are you seeing this as an unreasonable suggestion?  That's another serious question.

--

Adam

[lovewebdev]

This is my code:

<cfscript> 
curmonth = 12;
i = 5;
q = new Query();
q.setDatasource( "teamapp" );
 q.setSQL( "SELECT eventname FROM events WHERE MONTH(DATE(dtstamp)) =  :themonth AND DAYOFMONTH(DATE(dtstamp)) = :theday" );
 q.addParam( name="themonth", value=#curmonth#, cfsqltype="CF_SQL_INTEGER" );
q.addParam( name="theday", value=#curmonth#, cfsqltype="CF_SQL_INTEGER" );
 result = q.execute();
writeoutput(result.eventname[intRow]);
// dump query object
//writeDump( result );
</cfscript> 

Even though eventname is in the sql, I get an eventname not found when i access the column at: writeoutput(result.eventname[intRow]);

[lovewebdev · Accepted solution]

I think I got it. here's the solution for anyone else who runs into the same problem

q = new Query();
q.setDatasource( "myapp" );
// build the SQL statement 
//q.setSQL( "SELECT eventname FROM events WHERE MONTH(DATE(dtstamp)) =  :themonth AND DAYOFMONTH(DATE(dtstamp)) = :theday" );
q.addParam( name="themonth", value=#curmonth#, cfsqltype="CF_SQL_INTEGER" );
q.addParam( name="theday", value=#i#, cfsqltype="CF_SQL_INTEGER" );
// run the query and get a query object 
getevents = q.execute(sql="SELECT * FROM events WHERE MONTH(DATE(dtstamp)) =  :themonth AND DAYOFMONTH(DATE(dtstamp)) = :theday").getResult();    

Loop over the query to get the results

for (
intRow = 1 ;
intRow LTE getevents.RecordCount ;
intRow = (intRow + 1)
)
{
getevents.eventname[intRow]
}

Not as hard as I thought, just REALLY long to find the solution.

[Adam Cameron.]

I think I got it. here's the solution for anyone else who runs into the same problem

Good work.  I knew you'd get there if you gave it a go.

--

Adam

[Dan_Bracuk]

Even though you got it to work, using a function in the where clause of a query generally slows down performance.  Also, since you are not specifying the year, you could get wrong answers.

I generally go with this sort of thing

where datefield >= #date1#

and datefield < #the day after date2#