Lucee 5.3.3.62 and <cfinsert> / <cfupdate>

Hello,
I’ve inherited a big application which is running on CF 9.01. I’m in the process to port it to Lucee 5.3.3.62, but have some problems with and
I know that I should replace it with , but this application has ~1000 source files (!!), and replacing all those tags is currently not obvious for timing reasons.
Lucee is throuwing errors like:
“An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as “” or are not allowed. Change the alias to a valid name.”

At first, I thought there were problems with date field, because Lucee is handling them differently than CF 9.01, but this is not the case.
So, I created a test table (on MS-SQL Server 2008R2):

CREATE TABLE [dbo].[LuceeTest01](
	[Field1] [nvarchar](50) NULL,
	[Field2] [nvarchar](50) NULL
) ON [PRIMARY]

In Lucee, I’m using as datasource: Microsoft SQL Server (Vendor Microsoft), called “one”

This is my test application:

<cfset Form.Field1 = "Field1">
<cfset Form.Field2 = "Field2">
<cfoutput>
	<cfinsert datasource="one"
		tablename="LuceeTest01"
		formfields="Field1, Field2">
</cfoutput>

When I run this, I get the same error. Any idea why?
Full trace here: https://justpaste.it/6k0hw

Thanks!

Are the errors coming from Lucee proper or from the DB due to invalid SQL being sent? If the latter, use FusionReactor or a trace on the DB to identify the actual SQL being run to look for clues. I’ve honestly never used cfinsert on Lucee so I don’t know how well it works.

does removing the space before field2 make any difference?

Unfortunately not. Thanks for your input!

Will try this asap, thanks!

Curious. I tried using “jTDS Type 4 JDBC Driver for MS SQL Server and Sybase” as datasource driver, and now the error is:

The database name component of the object qualifier must be the name of the current database.

This traces back to this statement:

{call []..sp_columns 'LuceeTest01', '', '', 'null', 3}

When I try this in the Microsoft SQL Server Management Studio, I get the same error.
However, when I specify the database name (‘one’ as third argument), no error in MS SQL SMS.

EXEC sp_columns 'LuceeTest01', '', 'one', 'null', 3

Shouldn’t Lucee take this argument from the datasource configuration or something?

Does your datasource connection have a database specified?

Yes, “one”. Strange thing is, <cfquery> stuff is working with this datasource, only trouble with <cfinsert> and <cfupdate> (so far).

<cfset Form.Field1 = "Field1">
<cfset Form.Field2 = "Field2">

<cfoutput>
	<cfquery name="tst1" datasource="one">
		select count(*) as cnt from LuceeTest01;
	</cfquery>

	<cfoutput query="tst1">
		#cnt#</br>
	</cfoutput>

	<cfinsert datasource="one"
		tablename="LuceeTest01"
		formfields="Field1,Field2">
</cfoutput>

So, the error is on the cfinsert, the cfquery is outputing nicely, same datasource…

That sp_columns call doesn’t even come from Lucee, it comes from a java.sql.DatabaseMetaData class which is probably just part of the JDK. I would assume this is somehow related to the configuration of the datasource. Can you show us those details?

Sure:

Yes, but the parameters are coming from Lucee, no?

Same error result when I use this application.cfc and use for datasource in the test code “onecfc”:

Yep, everything seems in order in your datasource definitions. Just wanted to 100% confirm the presence of the DB.

Yes, but the parameters are coming from Lucee, no?

Yes, ultimately. That’s why I wanted to see the data source definition :slight_smile: I reviewed the code for the cfinsert tag hoping to just find a place where the DB wan’t being passed, but like I said, all that work seems to be coming form that JDK functionality and it just passes in the existing datasource connection. I was hoping for a low hanging fruit. I’ve never used this tag however on Lucee and there’s a really good chance not many other have either. I wouldn’t be surprised if it’s just plain broken. I’d enter a ticket at this point. Your most likely bet is to dig into the Java code and fix it yourself and submit a pull.

Yes that might be true. I remember having some limitations with cfinsert and cfupdate back with CF4.5 when I’ve read Ben Fortas CFML-Bibles. At that time I’ve decided to transwrite the code to cfquery, having full access to pure SQL. Never used cfinsert and cfupdate again.

Yes, I know I should move away from and , but the application is too big to replace them all right now, and in my opinion, if those tags are still on the supported list, they should work, or been removed. But then, Lucee shouldn’t claim it can be a replacement for CF with little changes to the code.
But I realize Lucee is for free, so I wouldn’t make any demands or have expectations. I’ll see what I can do / find out in the java code as soon I find the time for it.

You are totally right gunter. I don’t blame you for that. It"s just that (as far as I know) there haven’t been many issues with cfinsert with lucee before. Maybe because of lack of usage. Then, somtimes in certain situations/environments these issues pop up. Sometimes also because of some new behaviours in new connector versions. I’m sure the lucee dev team will address it as soon as they can.

I replied to this originally over on Stackoverflow, but I’ll confirm here too for completeness. I had a largely-unused dev box sat on Lucee 5.2.4.37 and MS SQL Server 2016 and could not reproduce the OP’s errors with either the jTDS (MSQL and Sybase) or Microsoft SQL Server (JDBC4 - Vendor Microsoft) drivers.

As soon as I updated to Lucee 5.3.3.62 I could reproduce the errors reported for both drivers, with no changes in DB permissions, datasource config or sample code. It does indeed appear that something changed in the JDK or Lucee in this time which prevents the DB name getting passed through to the stored proc.

Ok, it’s a regression. Time to file a bug, please include the label regression

Thanks for taking the time to look into this. Much appreciated!