Lucee 5.3.3.62 and <cfinsert> / <cfupdate>

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!

@Gunter, I’ve checked both cfinsert and cfupdate. In my test, it seems cfinsert only has a problem, but cfupdate working fine in my local.
Could you please intimate if cfupdate working correctly or not?
If YES means, please share here which types of error did you faced. If possible :slight_smile:

@Gunter, I filed a bug for cfinsert :slight_smile:
https://luceeserver.atlassian.net/browse/LDEV-2566

Thanks. Can you add the “regression” label to the bug report as asked by Zac_Spitzer?

Now, I’ve added. @Gunter, Did you saw? Please make sure of this :slight_smile:

Yes, verifying it now with a test database/table/script :slight_smile:

Yes, cfupdate seems to be ok. I run into problems with date fields (related to this https://luceeserver.atlassian.net/browse/LDEV-1344 ) and assumed that it was because of the problems I faced with cfinsert, but it isn’t. Thanks for sorting this out!

@Gunter, OK YWC :slight_smile:

if you change the table name to a non existent table, i see the same error

logging with sql server profiler, the error we are seeing is caused by

use [];

i.e. the database name isn’t being passed in

Msg 1038, Level 15, State 4, Line 1
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.
Msg 911, Level 16, State 1, Line 1
Database '' does not exist. Make sure that the name is entered correctly.

(also posted in jira)