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

SQL Issue

LEGEND ,
May 16, 2006 May 16, 2006

Copy link to clipboard

Copied

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/



TOPICS
Advanced techniques

Views

775

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
Mentor ,
May 16, 2006 May 16, 2006

Copy link to clipboard

Copied

What do you mean by "isn't working"? Error message? Unexpected results?

Phil

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 ,
May 16, 2006 May 16, 2006

Copy link to clipboard

Copied

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


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
Advocate ,
May 16, 2006 May 16, 2006

Copy link to clipboard

Copied

Showing us the actual CF code would help alot.

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
Enthusiast ,
May 16, 2006 May 16, 2006

Copy link to clipboard

Copied

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

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
Explorer ,
May 17, 2006 May 17, 2006

Copy link to clipboard

Copied

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.

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 ,
May 17, 2006 May 17, 2006

Copy link to clipboard

Copied

LATEST
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
>


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 ,
May 17, 2006 May 17, 2006

Copy link to clipboard

Copied

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


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 ,
May 17, 2006 May 17, 2006

Copy link to clipboard

Copied

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


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
Mentor ,
May 17, 2006 May 17, 2006

Copy link to clipboard

Copied

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

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 ,
May 17, 2006 May 17, 2006

Copy link to clipboard

Copied

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
>


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