CF8 cfinsert / cfupdate Error

[Rob12]

OS: Windows Server 2003
CF: CF8 Enterprise 8.0.1
DB: 10g

There are two problems:

** NOTE: NEITHER OF THE TWO ERRORS BELOW HAPPEN IN COLDFUSION 7, ONLY IN VERSION 8. **

(1) Inserting new database records using cfinsert.

For certain tables, when attempting to insert a new record using cfinsert, the following error is displayed:

[Macromedia][Oracle JDBC Driver]Internal error: Net8 protocol error.

This only happens some database tables and not on others (most seem to work, only a couple do not). The basic configuration of all the tables tested is the same (a primary key exists). The table is being referred to in all upper-case in the cfinsert tag, as is required for an Oracle DB. Using an insert inside a cfquery tag works fine. Also, deleting all data out of the table allows one cfinsert to work correctly, with subsequent attempts causing the error.


(2) Updating an existing database record using cfupdate.

For certain tables, when attempting to update an existing record cfupdate, the following error is displayed:

An error occurred during the update process. Error code: The primary key for the IRCCO table was not found in the '' datasource. /// Ensure that the table name is in the format that the database server expects. (For example. Oracle Server table name must be uppercase or the server table name must be qualified in the format "schema.table_name". For SQL Server, the format is "databasename.username.tablename", for example, customers.dbo.orders)

This only happens on some database tables and not on others with a similar configuration (the same tables with the cfinsert problem). The tables associated with the error do in fact have a primary key, so I'm not sure why the error above indicates otherwise. Using and update inside a cfquery tag seems to work fine.


(I have a tech-support case, 0180152620, open for these issues right now. I just wanted to see if anyone else out there had seen this problem and perhaps fixed it.)

[Dan_Bracuk]

What is the primary key and how do you get the value for the next record?

[Rob12]

The primary key is a numeric field called UNID. The value for this field is obtained from a DB sequence, and is provided to each new record in a table by a DB table trigger (which fires before an insert to the table).

[Guest]

A better (more reliable and efficient) way to perform data update (include insert and update) is to use native sql code, e.g.

insert into myDB.tblOwner.myTBL (col1, col2, col3)
values (1,'fancy dream', 'that says every good humnan being has won 1 million from God')

Same applys to update.

[Rob12]

That may be true, but the cfinsert/cfupdate tags afford us the opportunity to do some framework-level automation across multiple "modules" in our product. I suppose we could write our own tags/code to do the dynamic-SQL ourselves (we've actually written our own pre-processor tag for cfquery), but it would be great if the cfinsert/cfupdate tags just worked as well as they do in CF7 now. (It would also seem to obviate what would seem to be a primary reason for using CF in first place.)

[Dan_Bracuk]

Even if you used cfquery, as long as you are firing the trigger, you've got potential issues. Plus, if it stopped working because of an upgrade, that's just bad.

Somewhere on this site is a bug reporting/feature request page. I forget exactly where it is or how to find it.