4

According to the CF9 cfquery documentation, I should be able to return the oracle ROWID in the cfquery result.

I've failed on all counts, it simply does not return any identity or generated keys

I am using the jdbc oracle thin client, can anyone point me in the right direction here?

Leigh
  • 28,765
  • 10
  • 55
  • 103
Jay Rizzi
  • 4,196
  • 5
  • 42
  • 71
  • If you are using a sequence to generate your key, select the nextval first, set it to a variable, then use the variable in your insert query. – Dan Bracuk Feb 07 '14 at 20:49
  • I seem to recall that either `IDENTITYCOL` or `GENERATED_KEY` were actually returned by all db's even though the docs say otherwise. I may be wrong, but worth a try? – Abram Feb 07 '14 at 20:51
  • @DanBracuk not in this case , the table does not have a unique identifier other than rowid, no sequence – Jay Rizzi Feb 07 '14 at 20:57
  • @Abram it does not seem to be returning GENERATED_KEY, i've even specifically tried dumping the value it returns a CF error – Jay Rizzi Feb 07 '14 at 20:58
  • The thin client is listed in the original question, jdbc oracle thin client...i can post the code but it was just generic, any insert to any table, and yes i am dumping the result variable – Jay Rizzi Feb 07 '14 at 21:28
  • I meant if there is more than one version of the driver, ie like there are different versions of Oracle. *RE: yes i am dumping the result variable* Okay, just wanted to confirm. Unfortunately I am out of ideas, so I will leave this one to the Oracle folks. – Leigh Feb 07 '14 at 21:33

1 Answers1

9

If you were using one of the Oracle drivers that ships with ColdFusion, then you should be able to access GENERATEDKEY from the RESULT struct within the ColdFusion query object. Since you are using the JDBC Oracle thin client driver, where you setup a data source using "Add a new data source > Other", then enter the JDBC configuration, you don't have access to the RESULT struct described in the documentation.

I ran into the same issue when we used the MS JDBC driver with CF8. After converting to CF9 with the built-in SQL Driver, we were able to update our code to correctly reference the RESULT struct.

You will have to write your INSERT statements to also SELECT the value of ROWID, which you should be able to retrieve from the final query object.

Adrian J. Moreno
  • 14,350
  • 1
  • 37
  • 44
  • Thnaks for this answer...as a side note, i updated the driver to oracle native from using the jdbc thin client, and the rowid does return now – Jay Rizzi Feb 10 '14 at 15:43