Solved

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

Posted on 2004-10-04
17
632 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this short web based tutorial, I wanted to show users how they can still use the powers of FrontPage in conjunction with Expression Web 3.  Even though Microsoft eliminated the use of Web components, we can still use them with FrontPage and edit …
Now that Expression Web 4.0 (http://www.microsoft.com/expression/products/Upgrade.aspx) is free if you buy or have the full version of Expression Web 3.0, now is the best time to  migrate from FrontPage to Expression Web (http://www.frontpage-to-exp…
The purpose of this video is to demonstrate how to make a WordPress Site faster and smaller in size by cleaning up the database. This will be demonstrated using a Windows 8 PC. Plugin WP Optimize will be used. Go to your WordPress login page. T…
The purpose of this video is to demonstrate how to set up basic WordPress SEO. This will be demonstrated using a Windows 8 PC. The plugin used will be WordPress SEO by Yoast. Go to your WordPress login page. This will look like the following: myw…

738 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