Problem with DataMGR Generated SQL for related field (list)

[seanford]

Just tried to upgrade to latest version on github (as of 3/19/21).

Getting an error when running a simple getRecords request when there is a Related List field.

Old SQL Output (version from 4/3/2020):
SELECT [Users].[userID] , ( CAST( [Users].[userID] AS varchar(500)) ) AS [roleNames] FROM [Users] WHERE 1 = 1 AND [Users].[userID] = 10
New SQL Output (version from 3/19/2021):
SELECT [Users].[userID] , ( STUFF ( ( SELECT ',' + CONVERT( nvarchar(20), ) FROM [roles] INNER JOIN [Users_Roles] jt ON = WHERE 1 = 1 AND = ORDER BY FOR XML PATH('') ) ,1,1,'') ) AS [roleNames] FROM [Users] WHERE 1 = 1 AND [Users].[userID] = (10)
which generates the following error: " Incorrect syntax near ')'. "

Was looking for new documentation to see if I could debug, but it seems there have been a bunch of commits, but the Docs for DataMGR haven't been updated in quite a while, so I don't really know where to start here.

Any ideas?

[seanford]

This is running on a Lucee Server with a MSSQL Database.

[seanford]

I've pinpointed it down to the cf_DMSQL tags in DataMGR_MSSQL (getFieldSQL_List function).

I went back a few commits to find where this section used cf_DMObject tags instead. This seems to work for now, but I've yet to test to see if what I did ends up breaking anything else.

Not sure how often @sebtools looks at this repository, but I'd like to work with him to debug this if at all possible.

[sebtools]

What version of SQL Server are you using?

[seanford]

Correction...

Microsoft SQL Server 2019 (RTM-CU9) (KB5000642) - 15.0.4102.2 (X64) Jan 25 2021 20:16:12 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)

I forgot that the Database was located on a remote server for this project.

[sebtools]

That STUFF syntax should work on SQL Server 2019. Does it if you run it on the database directly? I'm curious if the code is running into a situation where it doesn't create the correct SQL or if it is the Lucee driver somehow getting in the way.

[seanford]

The Generated SQL is wrong. I've narrowed it down to the DMSQL tag. Did some testing and all of the DMSQL tags are outputting empty strings, even when they get a single length array as input from the getFieldSelectSQL function. If you look at the SQL output, there's a tag in each place where a value is missing.

I just don't know enough about how those tags work to debug them. (Trust me, I tried.)

[sebtools]

Ah. OK. I should have tested those tags on Lucee. My bad. I'll try to get to that soon. Might be a couple of days, sorry. In the meantime, you should just be able to delete the "getFieldSQL_List" in DataMgr_MSSQL.cfc and then it will switch to using the default method of getting those list values which should work fine.

Let me know if you have any trouble with that.

[seanford]

I hacked together a working function using an old commit that used cf_DMObject tags for now, but I'll try that as well, since I'm not sure what else I may have rolled back.

Thanks for the reply and let me know if I can assist with any testing.

BTW, I upgraded to the latest commit because my related lists weren't saving as part of a SaveRecord request. Has there been any issues with that as of late? ( I can work around, but I thought the related list was supposed to save as well. Admittedly, I'm a little rusty on all of the features I used to use often.)

[seanford]

Commented out "getFieldSQL_List" in DataMgr_MSSQL.cfc and it's working for now. Thanks.

I have plenty else to work on for now. :)

[sebtools]

Glad that worked for you. I haven't had any issues with saving lists in saveRecord in years and I lean on that heavily. If you run into any, send me a repro for your issue and I'll take a look.

[seanford]

So, a little update on this...I didn't realize it at the time, but commenting out the "getFieldSQL_List" function in DataMgr_MSSQL.cfc did not work. When I did this, the error went away, but I was getting the join field returned for any list relations. I rolled back to a function I modified by taking out the DMSQL tags and replacing them with DMObject tags as I saw in a previous build. This seemed to work for now.

<cffunction name="getFieldSQL_List" access="public" returntype="any" output="no">
	<cfargument name="tablename" type="string" required="yes">
	<cfargument name="field" type="string" required="yes">
	<cfargument name="tablealias" type="string" required="no">

	<cfset var sField = getField(arguments.tablename,arguments.field)>
	<cfset var aSQL = []>
	<cfset var temp = 0>
	<cfset var sField2 = 0>
	<cfset var sRelation = expandRelationStruct(sField.Relation)>
	<cfset var sRelationField = getField(sRelation.table,sRelation.field)>
	<cfset var length = "">

	<cfif StructKeyExists(sRelationField,"Length")>
		<cfset length = sRelationField["Length"]>
	</cfif>

	<cfif NOT Val(length)>
		<cfset length = "max">
	</cfif>

	<cf_DMSQL name="aSQL">
		STUFF	(
			(
				SELECT
						<cfif StructKeyExists(sRelation,"distinct") AND sRelation["distinct"] IS true>
							DISTINCT
						</cfif>
							'<cfoutput>#sRelation['delimiter']#</cfoutput>'
							+
							CONVERT(
								nvarchar(<cfoutput>#length#</cfoutput>),t.<cf_DMObject name="#sRelation['field']#">
								<!---<cf_DMSQL sql="#getFieldSelectSQL(tablename=sRelation['table'],field=sRelation['field'],tablealias='t',useFieldAlias=false)#" />--->
							)
				FROM		<cf_DMObject name="#sRelation['table']#"> t
			<cfif StructKeyExists(sRelation,"join-table")>
				INNER JOIN	<cf_DMObject name="#sRelation['join-table']#"> jt
					ON		t.<cf_DMObject name="#sRelation['remote-table-join-field']#"> = jt.<cf_DMObject name="#sRelation['join-table-field-remote']#">
				WHERE		1 = 1
					AND		jt.<cf_DMObject name="#sRelation['join-table-field-local']#"> = <cf_DMObject name="#Arguments.tablealias#">.<cf_DMObject name="#sRelation['local-table-join-field']#">
			<cfelse>
				WHERE		1 = 1
					AND		t.<cf_DMObject name="#sRelation['join-field-remote']#"> = <cf_DMObject name="#Arguments.tablealias#">.<cf_DMObject name="#sRelation['join-field-local']#">
			</cfif>
			<cfif NOT ( StructKeyExists(sRelation,"distinct") AND sRelation["distinct"] IS true )>
			ORDER BY
						<cfif StructKeyExists(sRelation,"sort-field") AND Len(sRelation["sort-field"])>
							t.<cf_DMObject name="#sRelation['sort-field']#">
							<!---<cf_DMSQL sql="#getFieldSelectSQL(tablename=sRelation['table'],field=sRelation['sort-field'],tablealias='t',useFieldAlias=false)#" />--->
						<cfelse>
							t.<cf_DMObject name="#sRelation['field']#">
							<!---<cf_DMSQL sql="#getFieldSelectSQL(tablename=sRelation['table'],field=sRelation['field'],tablealias='t',useFieldAlias=false)#" />--->
						</cfif>
			</cfif>
				FOR XML PATH('')
			)
		,1,1,'')
	</cf_DMSQL>

	<cfreturn aSQL>
</cffunction>

[sebtools]

Yeah, that will work unless you try to point your list relation to another relation field. I'll try to make some time hopefully next week to test out a fix that will take care of that.

[seanford]

ok, thank you. I don't think I have any nested relations, so I guess that's why it's working for me right now.

Let me know if you need me to do any additional testing. I'm on a Lucee / MSSQL environment at the moment.

[seanford]

@sebtools Could the code I posted affect the ability to sort by a relation column.

Quick summary:
I'm using DataMGR to power a datatable (datatables.net). In this case, I can sort by any column, including Label relations, but if I need to go to page 2, it fails on an "Invalid column name" SQL error. It only does this on columns that are relations. Was thinking that my pieced together code to get it working again might be the culprit.

Thoughts?

[seanford]

Disregard my last comment. I realized my code change was specific to lists.
I'm going to open a new issue for the last post with additional information.

[seanford]

This is still an issue in DataMgr_MSSQL...checked the changes and this is the current generated output:

SELECT [Users].[userID] , ( REPLACE( STUFF ( ( SELECT ',' + CONVERT( nvarchar(20), ) FROM [roles] INNER JOIN [Users_Roles] jt ON = WHERE 1 = 1 AND = ORDER BY FOR XML PATH('') ) ,1,1,''), '&', '&' ) ) AS [roleNames] FROM [Users] WHERE 1 = 1 AND [Users].[userID] = (10)

This is a simple query where userID is an Integer and primary key and roleNames is a listRelation.

Still seems to trace back to the DMSQL tag returning an empty string instead of SQL.

[seanford]

any updates on this?

[sebtools]

Sorry I haven't gotten to this. I've been fairly busy and haven't taken the time to find or set up a Lucee environment for testing. I'll try to get to that in a bit, but realistically still at least a week or two away. Sorry for the trouble.

[seanford]

Thank you for the response. Happy to help you test this if that would help. I can provide access to Lucee and SQL Server 2019.