Skip to main content
lovewebdev
Inspiring
December 19, 2011
Answered

Transform Query inside cfscript to not access java objects

  • December 19, 2011
  • 1 reply
  • 2911 views

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]

This topic has been closed for replies.
Correct answer 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]);


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.

1 reply

Inspiring
December 20, 2011

Why on Earth are you running a query like that?

--

Adam

lovewebdev
Inspiring
December 20, 2011

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.

Inspiring
December 20, 2011

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

Well that's what I'd do.

--

Adam