6 Replies Latest reply on Nov 14, 2012 6:55 AM by BreakawayPaul

    My first attempt at using a query inside a cfscript tag

    BreakawayPaul Level 2

      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?

        • 1. Re: My first attempt at using a query inside a cfscript tag
          Adam Cameron. Level 5

          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

          1 person found this helpful
          • 2. Re: My first attempt at using a query inside a cfscript tag
            BreakawayPaul Level 2

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

            • 3. Re: My first attempt at using a query inside a cfscript tag
              Adam Cameron. Level 5

              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

              • 4. Re: My first attempt at using a query inside a cfscript tag
                BKBK Adobe Community Professional & MVP

                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>

                • 5. Re: My first attempt at using a query inside a cfscript tag
                  BreakawayPaul Level 2

                  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.

                  • 6. Re: My first attempt at using a query inside a cfscript tag
                    BreakawayPaul Level 2

                    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!