Solved

CF and Oracle 9i: INSERT INTO table_a ... RETURNING ID INTO variable_a

Posted on 2004-10-04
17
626 Views
Last Modified: 2013-12-20
The question of getting the new ID after INSERT keeps popping up in forums but my question is very specific. I don't need any workarounds like MAX(ID), sequences, triggers, timestamps, etc.

Here is my question:

Does ColdFusion(6.1)/JRun4/JDBC support this specific Oracle 9i extension to SQL and, if it does, what is the CF syntax of doing it:

Table: table_a
ID number(10)
name varchar2(50)
address varchar2(50)

Trigger and sequence are used to generate a new ID value

Oracle 9i SQL:

   INSERT INTO table_a (name, address) VALUES ('Bob', '100 Main st') RETURNING ID INTO v_variable_id

Thanks
0
Comment
Question by:kgene521
  • 7
  • 4
  • 3
  • +1
17 Comments
 
LVL 21

Expert Comment

by:pinaldave
ID: 12221359
Hi kgene521,

No it will not support.

Regards,
---Pinal
0
 

Author Comment

by:kgene521
ID: 12221734
Any more details besides stating the fact? Where did you get this information from? Macromedia documentation? What is the source? You use future tense in your statement. Do you have any information of the plans by Macromedia not to support it in the future? Where did you get this information from?
I need specific proof one way or another.

Thanks

0
 
LVL 21

Expert Comment

by:pinaldave
ID: 12221756
Hi kgene521,
I do not have any proof... this is what I happen to know. Maybe someother expert will post the proof.
I have tried to use before the oracle special sequence and never got sucess...
may be you can use triggers but I am not aware how to code them.

Regards,
---Pinal
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 12222009
I would think that you could use a stored procedure that is triggered from CF and that basically executes the above code and then returns the variable back to CF.

So it may be a wrapper, but should work.
0
 

Author Comment

by:kgene521
ID: 12222887
Stored procedure is not going to work for several reasons. I need either to get it work directly from CF or find out if it is not possible with CF.

Again, I don't need workarounds.

Thanks for your responses, but they have not answered my question yet.
0
 
LVL 21

Expert Comment

by:pinaldave
ID: 12222895
Hi kgene521,
It is not possible directly through CF. That is answer.
It is from experience so there is no documentation to support the claim.

Regards,
---Pinal
0
 

Author Comment

by:kgene521
ID: 12222979
I need proof. I cannot just take your word for it. Prove it with documentation and code. This is not a specific programming problem, this is fundamental issue of Macromedia's support of an Oracle 9i SQL extension. So just stating the fact is not going to be enough. This is one of those tricky, seemingly easy questions that buffle you big time.

Thanks
0
 
LVL 9

Expert Comment

by:Jerry_Pang
ID: 12223157
similar post
Do oracle sequences get recognized by Coldfusion?
http://www.experts-exchange.com/Web/WebDevSoftware/ColdFusion/Q_21146809.html

i have no proof. i run into some sql functions in sybase before that where not supported in coldfusion, i had to look for proof but i could not find any on the web, the only proof i gave to my boss is the documentation found in the help files.

Like - Export function
like
select * from statement
export to file

it turns out that the export function only works on ISQL (Interactive sql). so it wont work on other external programs like coldfusion or any third party tools.

its probably same in your case.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 21

Expert Comment

by:pinaldave
ID: 12223305
Please visit for more info : http://www.macromedia.com/support/
If there is proof they will have it.

Regards,
---Pinal
0
 
LVL 21

Expert Comment

by:pinaldave
ID: 12223312
Also there is phone and email support link : hope you can take their words as proof.
Regards,
---Pinal
0
 
LVL 9

Accepted Solution

by:
Jerry_Pang earned 500 total points
ID: 12223615
Returning clause is not a standard SQL command.
Returning Clause command is external to the SQL, it cannot be understood OUTSIDE of the database.
it can only be run on Oracle Products only.

I could not find any links to gave you any proof, there are no information in oracle's site that tells me that this is possible outside of oracle software.
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_108a.htm#2073654
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 12227431
If you need proof this is not the place to get it.

We can give you guidance, suggestions, comments from experience, but the only people who can give you a definitive answer is Macromedia.

Here is the phone number to call for support for Cold Fusion:  1-415-252-9080

That is the only way to get what you want.
0
 
LVL 21

Expert Comment

by:pinaldave
ID: 12228225
hello Jerry,
that page requires the username and password just for your referance.
0
 

Author Comment

by:kgene521
ID: 12228329
Thanks to everybody, if I insisted on getting the impossible done no apologies. The programming is always "show me the code" thing, and not "I'm the expert, take my word for it". Been there, heard that.

Take care, people, you are all great help.
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 12228463
Really Jerry's answer deserved m0ore.


I think you should be more polite to the people trying to help if you expect anyone to continue to try and answer your questions.
0
 
LVL 21

Expert Comment

by:pinaldave
ID: 12228487
Totally Agree with Mrichmon.
0
 
LVL 9

Expert Comment

by:Jerry_Pang
ID: 12235636
thanx.

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_108a.htm#2073654
returning_clause
The returning clause retrieves the rows affected by a DML (INSERT, UPDATE, or DELETE) statement. You can specify this clause for tables and materialized views, and for views with a single base table.

When operating on a single row, a DML statement with a returning_clause can retrieve column expressions using the affected row, rowid, and REFs to the affected row and store them in host variables or PL/SQL variables.

When operating on multiple rows, a DML statement with the returning_clause stores values from expressions, rowids, and REFs involving the affected rows in bind arrays.

expr
Each item in the expr list must be a valid expression syntax. All forms are valid except scalar subquery expressions.

INTO
The INTO clause indicates that the values of the changed rows are to be stored in the variable(s) specified in data_item list.

data_item
Each data_item is a host variable or PL/SQL variable that stores the retrieved expr value.

For each expression in the RETURNING list, you must specify a corresponding type-compatible PL/SQL variable or host variable in the INTO list.

Restrictions on the RETURNING Clause
You cannot:

Specify the returning_clause for a multitable insert.
Use this clause with parallel DML or with remote objects.
Retrieve LONG types with this clause.
Specify this clause for a view on which an INSTEAD OF trigger has been defined.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction In this tutorial, I'll explain how to create an animated progress meter in a wireframe prototype developed using Axure RP 7.0 - a leading prototyping tool for designing web sites and software. (For more information about Axure and gett…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
The purpose of this video is to demonstrate how to integrate Mailchimp with Facebook. This will be demonstrated using a Windows 8 PC. Mailchimp and Facebook will be used. Log into your Mailchimp account. : Click on your name. Go to Account Setti…
The purpose of this video is to demonstrate how to set up an RSS Feed on a WordPress Website. This will be demonstrated using a Windows 8 PC. Feedburner will be used for this demonstration. Go to your WordPress login page. This will look like the…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now