1
0
-1

my sql statement goes to four tables.  It works in the database query window, but not within the DDL.  Suggestions?

  1. Jye Cusch

    Hi Elaine, could you please provide a little more info. An example of the kind of query, any errors you're seeing. Length alone should not be an issue for an SQL query in most cases.

CommentAdd your comment...

4 answers

  1.  
    1
    0
    -1

    Hi,

    Please provide the code.

    Thanks

    Chantal

     

     

      CommentAdd your comment...
    1.  
      1
      0
      -1

      When using Groovy SQL please make sure you close the connection.

      cheers Malcolm

      ---------------------------------------------------------------------------------------

      import groovy.sql.Sql

      def sql = Sql.newInstance("jdbc:mysql://localhost:3306/mydb", "root", "password", "com.mysql.jdbc.Driver")

      // Insert your xml values
      sql.execute("insert into form_submissions (submission_id, receipt_number) values (${submissionId}, ${receiptNumber})")

      // Close the connection
      sql.close()

        CommentAdd your comment...
      1.  
        1
        0
        -1

        It is within the following service definition

        /* Provides form lookup form data service by calling a configurable Groovy script.

        Script parameters include:

               form : com.avoka.fc.core.entity.Form

               request : javax.servlet.http.HttpServletRequest

               serviceDefinition : com.avoka.fc.core.entity.ServiceDefinition

               serviceParameters : Map<String, String>

        Script return:

               JSON string data value to be bound into the form data model

        */

         

        // Simulates an SQL Database Search

        import javax.naming.InitialContext

        import javax.sql.DataSource

        import java.sql.Date

        import java.text.SimpleDateFormat

        import groovy.sql.Sql

         

        import net.sf.json.*

        import net.sf.json.xml.*

        import com.avoka.component.json.JsonBuilder

        import groovy.json.JsonSlurper

         

        // Get a reference to the event log

        def eventLogService = new com.avoka.fc.core.service.EventLogService()

         

        // Log the event that the service has started

        eventLogService.logInfoEvent(serviceDefinition.serviceName + ' Started')

         

        def searchParam = request.getParameter('ItemHeaderID')

        eventLogService.logInfoEvent('Item Header ID: ' + searchParam)

         

        def foundSomething = false

        def itemList = []

         

        // Build SQL query

        def sqlStatement = '''SELECT old_item_id, item_status_desc, item_ownership_desc, market_segment_name, [language], (CASE When person_id = author_person_id then Last_name + ', ' + first_name ELSE 'no result' End) as Writer, (CASE When person_id = peer_reviewer_person_id  then Last_name + ', ' + first_name ELSE 'no result' End) as Peer, solution, ih.created_datetime, ih.modified_datetime

        FROM  item_header  ih

        left outer join item_status stat on ih.item_status_id = stat.item_status_id

        left outer join item_ownership_type iot on ih.item_ownership_type_id = iot.item_ownership_id

        left outer join persons per on ih.author_person_id = per.person_id

        left outer join market_segment ms on ih.market_segment_id = ms.market_segment_id WHERE ih.item_header_id = ?'''

         

        eventLogService.logInfoEvent('SQL: ' + sqlStatement)

         

        // Setup connection to the database for lookup

        InitialContext initCont = new InitialContext()

        DataSource dataSource = (DataSource)initCont.lookup(serviceParameters.get('PSI_Prod'))

        def db = Sql.newInstance(dataSource)

         

        try {

          db.eachRow(sqlStatement, [searchParam]) {

            def item =[:]

            item.OldItemID = it.old_item_id

            item.ItemStatus = it.item_status_desc

            item.ItemOwnership = it.item_ownership_desc

            item.MarketSegment = it.market_segment_desc

            item.Language = it.language

            item.Author = it.Writer

            item.PeerReviewer = it.Peer

            item.Solution = it.solution

            item.CreatedDate = it.created_datetime

            item.ModifiedDate = it.modified_datetime

            itemList.add(item)

            foundSomething = true

          }

        } catch(dummyEx) {

          // Just failed, return empty.

            eventLogService.logInfoEvent('SQL execution failed...')  

        }

         

        eventLogService.logInfoEvent('Found something? : ' + foundSomething)

         

        def jsonBuilder = new JsonBuilder()

        def jsonText = jsonBuilder.appendArray(itemList).toString()

        eventLogService.logInfoEvent('JSON: ' + jsonText)

         

        // Log the event that the service has stopped

        eventLogService.logInfoEvent(serviceDefinition.serviceName + " Stopped")

         

        if(foundSomething) {

            return jsonText

        } else {

          return """

           [{ "OldItemID" : "No results found!",

               "ItemStatus" : "No results found!",

               "ItemOwnership" : "No results found!",

               "MarketSegment" : "No results found!",

               "Language" : "No results found!",

               "Author" : "No results found!",

               "PeerReviewer" : "No results found!",

               "Solution" : "No results found!",

               "CreatedDate" : "No results found!",

               "ModifiedDate" : "No results found!"

          }]

        """

        }

          CommentAdd your comment...
        1.  
          1
          0
          -1

          SELECT

                  old_item_id

                , item_status_desc

                , item_ownership_desc

                , market_segment_name

                , [language]

                , (CASE When person_id = author_person_id then Last_name + ', ' + first_name ELSE 'no result' End) as Author

                , (CASE When person_id =peer_reviewer_person_id  then Last_name + ', ' + first_name ELSE 'no result' End) as Peer

                , solution

                , ih.created_datetime

                , ih.modified_datetime

            FROM  dbo.item_header  ih

               left outer join dbo.item_status stat on ih.item_status_id = stat.item_status_id

              left outer join dbo.item_ownership_type iot on ih.item_ownership_type_id = iot.item_ownership_id

              left outer join dbo.persons per on ih.author_person_id = per.person_id

              left outer join dbo.market_segment ms on ih.market_segment_id = ms.market_segment_id

            where item_header_id = 129424

            CommentAdd your comment...