• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

My first attempt at using a query inside a cfscript tag

Contributor ,
Nov 13, 2012 Nov 13, 2012

Copy link to clipboard

Copied

My first attempt at this is not going well.

Here's what I have:

<cfscript>

function calbox(topic) {

    queryService = new query();

    queryService.setDatasource("hepoffice");

    queryService.setName("getevents");

    queryService.addParam(name="topic",value="arguments.topic",cfsqltype="cf_sql_numeric");

    result = queryService.execute(sql="SELECT event_title" &

                                            ", event_desc" &

                                            ", event_start_date" &

                                            ", event_url" &

                                            ", exit_door" &

                                        "FROM events" &

                                        "INNER JOIN pagetopics" &

                                                "ON pagetopics.topic_id = events.event_topic" &                                   

                                        "WHERE pagetopics.topic_id = :topic");

}

</cfscript>

When I view a page that has this code block on it, I get the following error:

Invalid CFML construct found on line 29 at column 28.

ColdFusion was looking at the following text:

query

The CFML compiler was processing:

    A script statement beginning with queryService on line 29, column 9.

    A script statement beginning with function on line 28, column 1.

    A cfscript tag beginning on line 27, column 2.

This isn't where I expected to get an error.  Anyone have any idea where I've gone wrong?

Views

938

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Nov 13, 2012 Nov 13, 2012

Copy link to clipboard

Copied

What verison of CF are you on?  You code compiles OK for me on CF 9.0.2, however I get your exact error on CF8.0.1. The service CFCs like Query.cfc weren't added to CF until CF9, I'm afraid.  And the new keyword wasn't either.

--

Adam

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Nov 13, 2012 Nov 13, 2012

Copy link to clipboard

Copied

That's the problem then.  We're on CF8.  Any way to pull off a query inside a cfscript tag in CF8?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Nov 14, 2012 Nov 14, 2012

Copy link to clipboard

Copied

That's the problem then.  We're on CF8.  Any way to pull off a query inside a cfscript tag in CF8?

It's very easy to write a wrapper function for a CFML tag: just create a <cffunction> which takes the arguments the tag does, and then use them to call the tag. It's made slighly more complex with <cfquery> as it can have sub tags, but that's not so hard to do.

Indeed that's all that Query.cfc is.  You could even use that very one: grab it out of a CF9 install and plonk it in your CF8 install: it should work (I dunno if it uses any CF9-only code, but that'll be fairly clear fairly quickly).  That said, I dunno how that will go as far as licensing goes.

But other than that, fortunately other people have already asked (and resolved) the same question you're asking: https://github.com/CFCommunity/CFScript-Community-Components

That said: <cfquery> is a much nicer way of doing an inline query than any way that you'll be able to find that works in a CFScript-only environment. Do you need to write this code in script?

--
Adam

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Nov 14, 2012 Nov 14, 2012

Copy link to clipboard

Copied

Well I don't think I need to do this in any paricular way, so let me explain what I'm trying to do.

Our website has a calendar of events.  The calendar is populated via our CMS.  Each office (there are five) has an admin assisstant who has an account in the CMS, and who has rights to add events for their office in the calendar.

Each office has several topics, and each topic has its own separate section on our website.  When you create an event in the calendar, you can assign it to one of these topics.

I'm now trying to create a calendar "widget" that can be placed on each topic's section on the website that makes an "upcoming events" box.  The problem is that some topics are combined, so I was trying to achieve something like this:

#calwidget(26,31,109)#

And this would create a div that pulled the events for topics 26, 31, and 109 from the database.  My first hurdle was to get the thing to work with one topic before trying to tackle multiple arguments.

Perhaps a <cfinvoke> would work better for this.  Then I could pass the topic list right in the tag itself.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Nov 14, 2012 Nov 14, 2012

Copy link to clipboard

Copied

LATEST

I just made this work beautifully with a <cfinvoke> tag and a CFC.  Does everything I need it to do, and it even takes multiple arguments.

Thanks for the extra set of eyes!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Nov 14, 2012 Nov 14, 2012

Copy link to clipboard

Copied

If you insist on cfscript, then you could also do it in Java. Here follows an example in MySQL. It is a quick job, and I have omitted niceties like try-catch.

<cfscript>

    class = createobject("java","java.lang.Class");

    Class.forName("com.mysql.jdbc.Driver");

    driverManager = createobject("java","java.sql.DriverManager");

    connection = driverManager.getConnection("jdbc:mysql://localhost:3306/testDB", "myUserName", "myPassword") ;

    statement = connection.createStatement() ;

    query = "select * from animals";

    resultSet = statement.executeQuery(query) ;

    while ( resultSet.next() ) {

               writeoutput(resultSet.getstring('id') & ' | ' & resultSet.getstring('species') & '<br>');        

        }

      resultSet.close();

      statement.close();

      connection.close();

</cfscript>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation