Lucee and <cfinsert> / <cfupdate>

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

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

	<cfoutput query="tst1">

	<cfinsert datasource="one"

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?


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

1 Like

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:

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 ) 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: