10 Replies Latest reply on May 17, 2006 9:25 AM by Newsgroup_User

    SQL Issue

    Level 7
      I'm hoping someone here will see what I'm missing.

      Can someone tell me why this isn't working?

      SELECT tn.tnID, tn.tnTitle, cr.crID, cr.crAction, cr.crAuditTrail
      FROM dbo.tbl_tempNav tn INNER JOIN
      dbo.tbl_tempNavRelations tnr ON tn.tnID =
      tnr.tnrChildID INNER JOIN
      dbo.tbl_tempPages tp ON tn.tnID = tp.tpNavID INNER JOIN
      dbo.tbl_changeRequest cr ON tp.tpID = cr.crObjectID
      WHERE (cr.crPublisher = @person OR (cr.crPublisher = '' AND (tnr.tnrParentID
      IN (@publisherApps) OR tn.tnID IN (@publisherApps)))) AND cr.crObjectType =
      'page' AND cr.crLocked = 1
      ORDER BY tn.tnTitle

      --
      Bryan Ashcraft (remove brain to reply)
      Web Application Developer
      Wright Medical Technologies, Inc.
      =============================
      Macromedia Certified Dreamweaver Developer
      Adobe Community Expert (DW) :: http://www.adobe.com/communities/experts/



        • 1. Re: SQL Issue
          paross1 Level 2
          What do you mean by "isn't working"? Error message? Unexpected results?

          Phil
          • 2. Re: SQL Issue
            Level 7
            Sorry. I'm brain dead at the moment.

            This is code from a stored procedure.

            SELECT tn.tnID, tn.tnTitle, cr.crID, cr.crAction, cr.crAuditTrail
            FROM dbo.tbl_tempNav tn INNER JOIN
            dbo.tbl_tempNavRelations tnr ON tn.tnID =
            tnr.tnrChildID INNER JOIN
            dbo.tbl_tempPages tp ON tn.tnID = tp.tpNavID INNER JOIN
            dbo.tbl_changeRequest cr ON tp.tpID = cr.crObjectID
            WHERE (cr.crPublisher = @person OR (cr.crPublisher = '' AND (tnr.tnrParentID
            IN (@publisherApps) OR tn.tnID IN (@publisherApps)))) AND cr.crObjectType =
            'page' AND cr.crLocked = 1
            ORDER BY tn.tnTitle

            I have narrowed it down to the IN statements. If I hard code the list it
            will function fine, but it doesn't return any records when the same data is
            passed in from a CFC. Unfortunately I can't hard code the values as
            different user roles will have different approved applications. It doesn't
            error, it just doesn't return any records. For the life of me I can't figure
            out what is going wrong from the stored procedure call in my cfc to the
            actual stored procedure itself.

            --
            Bryan Ashcraft (remove brain to reply)
            Web Application Developer
            Wright Medical Technologies, Inc.
            =============================
            Macromedia Certified Dreamweaver Developer
            Adobe Community Expert (DW) :: http://www.adobe.com/communities/experts/


            "paross1" <webforumsuser@macromedia.com> wrote in message
            news:e4dgsk$rf7$1@forums.macromedia.com...
            > What do you mean by "isn't working"? Error message? Unexpected results?
            >
            > Phil


            • 3. Re: SQL Issue
              Kronin555 Level 1
              Showing us the actual CF code would help alot.
              • 4. Re: SQL Issue
                The ScareCrow Level 1
                I would suggest the problem is that you are passing a list to the sp.
                But as the cr.crPublisher field is char you need to pass a quoted list.

                Ken
                • 5. Re: SQL Issue
                  Stefan_K. Level 1
                  ScareCrow is correct.
                  Passing a list as IN-parameter to a stored procedure is bound to fail.
                  SQL simply treats a list as a string, by no means a a list of numeric-values or list of several strings.

                  The simple workaround would be to write a normal query, don't use stored proc in this case.
                  The other way is using one of the following SQL-udf, they take such a string-list and returns a temp-table(position, value).
                  There's a version for working with string-lists and one for numeric-lists.
                  • 6. Re: SQL Issue
                    Level 7
                    I am passing a quoted list. Here is the method that calls the sp.

                    <!--- Get listing of pages --->
                    <cffunction name="getPageListing" displayname="Get an alphabetical listing
                    of pages" access="public" output="false" returntype="query">
                    <cfargument name="approvalStage" displayname="Approval Stage of page
                    listing" required="no" default="approved" />
                    <cfargument name="userRole" displayname="User's role" required="no"
                    default="user" />
                    <cfargument name="person" displayname="Name of user" required="no"
                    default="" />
                    <cfargument name="publisherApps" displayname="List of approved applications
                    for user" required="no" default="" />
                    <cfset var pageListing = "" />
                    <cfset var appListing = listQualify(arguments.publisherApps,"'",",","all")
                    />
                    <cfstoredproc procedure="dbo.getPageListing"
                    datasource="#request.extranetDSN#">
                    <cfprocparam type="in" dbvarname="@approvalStage"
                    value="#arguments.approvalStage#" cfsqltype="cf_sql_longvarchar" />
                    <cfprocparam type="in" dbvarname="@userRole" value="#arguments.userRole#"
                    cfsqltype="cf_sql_longvarchar" />
                    <cfprocparam type="in" dbvarname="@person" value="#arguments.person#"
                    cfsqltype="cf_sql_longvarchar" />
                    <cfprocparam type="in" dbvarname="@publisherApps" value="#appListing#"
                    cfsqltype="cf_sql_longvarchar" />
                    <cfprocresult name="pageListing" />
                    </cfstoredproc>
                    <cfreturn pageListing />
                    </cffunction>

                    --
                    Bryan Ashcraft (remove brain to reply)
                    Web Application Developer
                    Wright Medical Technologies, Inc.
                    =============================
                    Macromedia Certified Dreamweaver Developer
                    Adobe Community Expert (DW) :: http://www.adobe.com/communities/experts/


                    "The ScareCrow" <info@krcaldwell.com> wrote in message
                    news:e4dpqp$89o$1@forums.macromedia.com...
                    >I would suggest the problem is that you are passing a list to the sp.
                    > But as the cr.crPublisher field is char you need to pass a quoted list.
                    >
                    > Ken


                    • 7. Re: SQL Issue
                      Level 7
                      Just in case you're wondering and/or it will help. appListing is a list of
                      UUIDs.

                      --
                      Bryan Ashcraft (remove brain to reply)
                      Web Application Developer
                      Wright Medical Technologies, Inc.
                      =============================
                      Macromedia Certified Dreamweaver Developer
                      Adobe Community Expert (DW) :: http://www.adobe.com/communities/experts/


                      "The ScareCrow" <info@krcaldwell.com> wrote in message
                      news:e4dpqp$89o$1@forums.macromedia.com...
                      >I would suggest the problem is that you are passing a list to the sp.
                      > But as the cr.crPublisher field is char you need to pass a quoted list.
                      >
                      > Ken


                      • 8. Re: SQL Issue
                        paross1 Level 2
                        You probably should enclose appListing within the preservesinglequotes() function.

                        Something like this, perhaps.

                        <cfprocparam type="in" value="#preservesinglequotes(appListing)#" cfsqltype="cf_sql_longvarchar" />

                        Also, if you are using CFMX, the dbvarname attribute is depricated and does nothing, so you can remove them from your cfprocparam tags.

                        Phil
                        • 9. Re: SQL Issue
                          Level 7
                          No luck.

                          --
                          Bryan Ashcraft (remove brain to reply)
                          Web Application Developer
                          Wright Medical Technologies, Inc.
                          =============================
                          Macromedia Certified Dreamweaver Developer
                          Adobe Community Expert (DW) :: http://www.adobe.com/communities/experts/


                          "paross1" <webforumsuser@macromedia.com> wrote in message
                          news:e4ff1v$cie$1@forums.macromedia.com...
                          > You probably should enclose <b>appListing</b> within the
                          > preservesinglequotes()
                          > function.
                          >
                          > Something like this, perhaps.
                          >
                          > <cfprocparam type="in" value="#preservesinglequotes(appListing)#"
                          > cfsqltype="cf_sql_longvarchar" />
                          >
                          > Also, if you are using CFMX, the <b>dbvarname</b> attribute is depricated
                          > and
                          > does nothing, so you can remove them from your cfprocparam tags.
                          >
                          > Phil
                          >


                          • 10. Re: SQL Issue
                            Level 7
                            Thanks everyone for all the suggestions. The udf worked great!

                            --
                            Bryan Ashcraft (remove brain to reply)
                            Web Application Developer
                            Wright Medical Technologies, Inc.
                            =============================
                            Macromedia Certified Dreamweaver Developer
                            Adobe Community Expert (DW) :: http://www.adobe.com/communities/experts/


                            "Stefan K." <webforumsuser@macromedia.com> wrote in message
                            news:e4eldj$9mv$1@forums.macromedia.com...
                            > ScareCrow is correct.
                            > Passing a list as IN-parameter to a stored procedure is bound to fail.
                            > SQL simply treats a list as a string, by no means a a list of
                            > numeric-values
                            > or list of several strings.
                            >
                            > The simple workaround would be to write a normal query, don't use stored
                            > proc
                            > in this case.
                            > The other way is using one of the following SQL-udf, they take such a
                            > string-list and returns a temp-table(position, value).
                            > There's a version for working with string-lists and one for numeric-lists.
                            >
                            >
                            > Example:
                            > SELECT TOP 1 *
                            > FROM dbo.udf_iter_stringlist_to_table( 'firstItem,secondItem', ',')
                            > Would return:
                            > listpos | string
                            > 1 | 'firstItem'
                            >
                            > Numeric-list UDF:
                            > CREATE FUNCTION dbo.udf_iter_intlist_to_table (@list ntext, @delim nchar)
                            > RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
                            > number int NOT NULL) AS
                            > BEGIN
                            > DECLARE @pos int,
                            > @textpos int,
                            > @chunklen smallint,
                            > @str nvarchar(4000),
                            > @tmpstr nvarchar(4000),
                            > @leftover nvarchar(4000)
                            >
                            > SET @textpos = 1
                            > SET @leftover = ''
                            > WHILE @textpos <= datalength(@list) / 2
                            > BEGIN
                            > SET @chunklen = 4000 - datalength(@leftover) / 2
                            > SET @tmpstr = ltrim(@leftover + substring(@list, @textpos,
                            > @chunklen))
                            > SET @textpos = @textpos + @chunklen
                            >
                            > SET @pos = charindex(@delim, @tmpstr)
                            > WHILE @pos > 0
                            > BEGIN
                            > SET @str = substring(@tmpstr, 1, @pos - 1)
                            > INSERT @tbl (number) VALUES(convert(int, @str))
                            > SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1, len(@tmpstr)))
                            > SET @pos = charindex(@delim, @tmpstr)
                            > END
                            >
                            > SET @leftover = @tmpstr
                            > END
                            >
                            > IF ltrim(rtrim(@leftover)) <> ''
                            > INSERT @tbl (number) VALUES(convert(int, @leftover))
                            >
                            > RETURN
                            > END
                            >
                            > String-list-UDF
                            > CREATE FUNCTION dbo.udf_iter_stringlist_to_table (@list ntext, @delim
                            > nchar)
                            > RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
                            > string varchar(8000) COLLATE database_default NOT
                            > NULL) AS
                            > BEGIN
                            > DECLARE @pos int,
                            > @textpos int,
                            > @chunklen smallint,
                            > @str nvarchar(4000),
                            > @tmpstr nvarchar(4000),
                            > @leftover nvarchar(4000)
                            >
                            > SET @textpos = 1
                            > SET @leftover = ''
                            > WHILE @textpos <= datalength(@list) / 2
                            > BEGIN
                            > SET @chunklen = 4000 - datalength(@leftover) / 2
                            > SET @tmpstr = ltrim(@leftover + substring(@list, @textpos,
                            > @chunklen))
                            > SET @textpos = @textpos + @chunklen
                            >
                            > SET @pos = charindex(@delim, @tmpstr)
                            > WHILE @pos > 0
                            > BEGIN
                            > SET @str = substring(@tmpstr, 1, @pos - 1)
                            > INSERT @tbl (string) VALUES(@str)
                            > SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1, len(@tmpstr)))
                            > SET @pos = charindex(@delim, @tmpstr)
                            > END
                            >
                            > SET @leftover = @tmpstr
                            > END
                            >
                            > IF ltrim(rtrim(@leftover)) <> ''
                            > INSERT @tbl (string) VALUES(@leftover)
                            >
                            > RETURN
                            > END
                            >