SQL syntax for returning multiple values from a select?

The DBI doc's seem to indicate that multiple values can be
returned from an insert query.

Q:
	insert into foo
	( bar bletch )
	values
	( bar$seq.nextval, bletch$seq.nextval )
	returning
	...

I've tried variations on "( bar, bletch ) into (?,?)" with
and without parens and "bar into ?, bletch into ?" without
any luck so far...

What is the correct sql syntax (googling variations on the
select returning syntax hasn't gotten me anywhere).

thanx

<http://search.cpan.org/~timb/DBD-Oracle-1.16/Oracle.pm#Returning_A_Value_from_an_INSERT>

Oracle supports an extended SQL insert syntax which will return
one or more of the values inserted. This can be particularly useful
for single-pass insertion of values with re-used sequence values
(avoiding a separate "select seq.nextval from dual" step).

  $sth = $dbh->prepare(qq{
      INSERT INTO foo (id, bar)
      VALUES (foo_id_seq.nextval, :bar)
      RETURNING id INTO :id
  });
  $sth->bind_param(":bar", 42);
  $sth->bind_param_inout(":id", \my $new_id, 99);
  $sth->execute;
  print "The id of the new record is $new_id\n";

If you have many columns to bind you can use code like this:

  @params = (... column values for record to be inserted ...);
  $sth->bind_param($_, $params[$_-1]) for (1..@params);
  $sth->bind_param_inout(@params+1, \my $new_id, 99);
  $sth->execute;



-- 
Steven Lembark                                       85-09 90th Street
Workhorse Computing                                Woodhaven, NY 11421
lembark@wrkhors.com                                     1 888 359 3508
0
lembark
2/1/2005 1:09:19 AM
perl.dbi.users 11100 articles. 1 followers. Follow

0 Replies
423 Views

Similar Articles

[PageSpeed] 11
Get it on Google Play
Get it on Apple App Store

Reply:

Similar Artilces:

Multiple-Select Listbox only returns first selected value (and I'm not using SelectedItem...)
I'm looping through the items of a listbox, as you can see I'm raising a custom event, passing the selected values with custom event args up...  What happens however, is that only the first item will be tagged as being selected, no matter how many listbox items I select with control. Ie If I select "Transducer, Pressure Transmitter, Pressure & Temperature" the following is returned: Transducer is selected;Pressure transmitter is not selected;Pressure & Temperature is not selected; I encoded the ampersand & so do not worry about string concat. This is t...

sql select fails to return value
Hello there! I have a database in access and I'm trying (with perl using DBI and DBD::ODBC in win32) to select specific fields from it and write them to a file. The script that I'm using is the one I'm providing below. The connection to the database works fine but I think there is a problem with the SELECT statement. The only thing that gets printed in the file is DBI::st=HASH(0x1914394) which is the only thing that I get as a response when the script runs. Essentially the problem is that I can not print the results of the query. Do you have any ideas on this? Any help wo...

Why does a simple select query in Microsoft Access return the error [Intersolv][ODBC SQL Server Driver][SQL Server]ct_results(): user api layer: internal client library error: Read from the server has
I have applied the information from support document 10447954 but that does not solve the problem. I am running a simple select query with one criteria from one table. Thanks. ...

Why does a simple select query in MS Access return the error: [Intersolv][ODBC SQL Server Driver][SQL Server]ct_results(): user api layer: internal client library error: Read from the server has timed
I have applied the information contained in support document 10447954. That did not resolve the problem. I am running a simple query with one criteria and one table Thanks. Hi Bill, The doc refers to a different ODBC driver (for DirectConnect product) so you might have a completely different problem. The message implies you are using ODBC 12.0 (Client Library based) or prior since the message indicates CtLib info. You might want to post more details involved with getting to this error. You also might want to post exact versions of ASE and ODBC driver, including Open ...

Select Distinct returns multiple rows with same value
I have a Select Distinct myfield that returns multiple rows with same value for myfield when it should only one. Why is this happening? Are you selecting other fields, as well?  If you select multiple fields, and one of those other fields is not the same for all records having the same 'myfield' value, then they are not the same value. For example, if you select Distinct(myfield) and myfield_2, if all rows having a myfield value of '1' all have the same myfield_2 value of '6', then only one row is returned. If one row having myfield = '1', a...

Help with DISTINCT return in SQL query with multiple selections
I'm exporting the following query to a datagrid, however in the result set, some values are duplicated (for various reasons... mostly old software and poor categorization)...On the records with identical values, I want to look at the account number and the DateOfService fields and search for joint distinct values and only display that...Current Example:  ACCT NUM   |  DATE OF SERVICE  |________________________________   43490          |     10/01/2006  08:15:23  | ...

SQL Query Help For Selecting Multiple Integer Values
 Hi,I am looking for a SQL Query Help.I am in a situation where I need to select something like this:--- Select * from Table where myage is 30 or 35 or otherage is 40 or 45  myage and otherage are two different columns in the Table.  Is such a type of query possible? Please help.  Currently I am using this command,Select * from Table where myage=30 or otherage=40and it works fine. Please help with my required command if such a command is possible... thanx use next Select  * from Table where myage in (30 , 35) or otherage in (40 , 45) Regards,St...

Multiple Selects
Is it possible to specify more than one select in the Infomaker SQL Select Painter? Example: SELECT Tab1.Num FROM Tab1 SELECT Tab2.Ref FROM Tab2 If it is possible, please let me know how can I do that. If it isn't possible, how can I overcome this issue? Thanks and regards, Filipe Freitas Yes, inside a UNION. "Filipe Freitas" <filipe.freitas@hp.com> wrote in message news:435f98ea$1@forums-2-dub... > Is it possible to specify more than one select in the Infomaker SQL Select > Painter? > > Example: > SELECT Tab1.Num FROM Tab1 ...

How to return a string value from a select Query in SQL Server 2005?
Hello, I run a stored procedure from my asp.net. Normally the stored procedures return the select statement into a DataSet. But I just want the SP to return a single string value from the select so that I don't have to go through all the code to get that value from the DataSet. Lets say I want a user lastName from the table users.  Can I do something like the following: if exists (Select *  users  where username = @username)        -----  then return the last name as a stringelse    return 'Not Found' ...

select cheboxlist if any values are selected in sql
hello and thanks in advance,  I have a checkboxlist that is created dynamically from table1 once the records are inserted they are inserted in table2. i can save the values into the table and remove them if i uncheckd them that is working fine. the problem is to checked the items that have been selected already, how to i do this?? really really confuse...   here is the cs code; namespace IMS_2K9.Admin { public partial class Order_Charges : System.Web.UI.Page {private string connectionString = WebConfigurationManager.ConnectionStrings["IMSCon...

Return value for one selected row from a SQL database/Dataset problem
I try to get a value from ArtikkelId from a SQL database into a textbox. Can anyone help me? My Code----------------------------------------- Imports System.DataImports System.Data.SqlClient Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim Conn_News_db As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("NEWS_CS1").ToString()Dim SQL_conn As New SqlConnection(Conn_News_db)Dim SQL_cmd1 As New SqlCommand()Dim myDataSet As New DataSetDim Select_ArtId_Max As String = "Select ArtikkelId from Hoved where A...

how to auto insert the value of multiple records into dropdownlist base on user selection in c#
Hi, I have one gridview with column emp#, status.   'status' column is a dropdownlist with 3 types of status for user to select, that is 'approve,disapprove,onhold'.  By default system will set status as 'onhold'.  Example of gridview data as below: Emp#        Status(dropdownlist)         001           onhold            002    &nb...

DBI , Oracle
Dear gurus ! I'm considering to write a PL/SQL procedure that returns multiple rows (most likely via REF cursor , although i might think of PL/SQL tables or VARRAYs) .. How can i accept such an output in PERL script ? Thanks a lot for your help, DBAndrey Hi Andrey, > I'm considering to write a PL/SQL procedure that returns multiple rows (most > likely via REF cursor , although i might think of PL/SQL tables or VARRAYs) > How can i accept such an output in PERL script ? You could try the private DBMS_OUTPUT methods available via DBD::Oracle, and the fun...

Selecting values in DDLs with values from SQL DB
On my page I have four drop-down lists: startWeek, startYear, endWeek and endYear which sets a period of time.The default values, that must be displayed on page load, are selected from a table called Period in a SQL database using a Linq.DataContext class called MyContextClass. The periods (a period is a week in a given year) in this table can be active/inactive. In the code I select two active periods: The one with the lowest ID (the earliest period) and the one with the highest ID (the latest period). I then try to display these two periods in the four drop downs: the first two lists conta...

Web resources about - SQL syntax for returning multiple values from a select? - perl.dbi.users

Returning - Wikipedia, the free encyclopedia
In retail , returning is the process of a customer taking previously purchased merchandise back to the retailer, and in turn, receiving a cash ...

Mark Zuckerberg Returning To TechCrunch Disrupt
Facebook Co-Founder and CEO Mark Zuckerberg took the stage at last year’s TechCrunch Disrupt conference in San Francisco, where he discussed ...

How-to: Improve the Experience for Returning Users
... – your inbox, your favorite news sources, or your friends’ activity. If you’ve integrated Facebook Login on your website, you can give returning ...

Search Twitter - emilia-clarke-not-returning
Sign in Sign up No results for emilia-clarke-not-returning Settings Help Back to top · Turn images off

Is the Returning CEO a Trend?
... well be wondering if it is a new trend for boards to reach back into their alumni ranks to bring back a CEO when a company is in Is the Returning ...

Fire engine returning from a call
District of Columbia Fire & EMS Station No. 3

Zillow: Long Distance (Returning Soldier Commercial) - YouTube
Introducing Zillow's latest TV commercial as part of its national advertising campaign, "Find Your Way Home." The spot, entitled "Long Distance," ...

WWE’s Daniel Bryan on WWE 2K16, facing Brock Lesnar at WrestleMania, returning to the ring and life on ...
The four-time WWE world champion discusses whether it is right for Stone Cold Steve Austin to be the cover star of WWE 2K16, as well as who he ...

Damn Fine News of the Day: Kyle MacLachlan is Returning to ‘Twin Peaks’
In case you missed it, David Lynch announced on Twitter Monday that Kyle MacLachlan will reprise his role as Special Agent Dale Cooper in the ...

Returning Cat Daniel Menzel hopes for a pain-free summer
Having starred in his first match in almost four years, Geelong forward Daniel Menzel says his next major challenge is to train - pain free - ...

Resources last updated: 1/17/2016 7:07:29 AM