Here is a witango file that demonstrates how to do this in witango. It's very simple!
What it does is select all columns from the table you tell it to with
the where clause you give it, then it loops through each of the
columns of the results and puts each column in single quotes with a
comma after it, changing the first column to '42' (you can easily
modify this code to make it change any given column to any value)
After this theres a vestigial comma at the end that it cuts off and
then inserts the data into the table.
Pretty simple stuff!
This works for me in r:base atleast, I think it uses pretty standard
sql but never had experience with oracle so cant say for sure.
I ran into a problem when using this code on a table that had a
"compute" column because it tries to include that column in the insert
command which it shouldn't be doing and so tosses an error.
Hopefully you dont hit that kind of problem in your code - but if you
do you should be able to modify this code to not include the
autocomputed columns by makign it exclude column x and column y when
building the insert column list if those are 2 computed columns.
Let me know how this works for you or if you have any questions about it! (:
Alan
On 7/28/05, Robert Garcia <[EMAIL PROTECTED]> wrote:
> This is more difficult in witango, cuz of the data type issue.
>
> I have code that does this in VB. I don't know if that is an option
> for you. But you could write a simple vb script, or console app that
> you pass the ref and a key or whatever you need. Then you do a select
> * from the table with the ref, then build a new dbrecord for insert.
> The datatypes will all work properly, just use variants to store in
> between. You can also test for certain datatypes in a select case
> statement to build the the new db record correctly then insert. Have
> the app return 0 on success, call it from witango via external
> action. The function in VB or RB would look something like this (this
> is a snippet from RB app, VB almost identical):
>
> function copyRec(oldreference as string) as integer
> dim rs as recordset
> dim i,n as integer
> dim s as string
> dim rec as database record
>
> s = "select * from myTable where theRef = '"+oldreference+"'"
> rs = db.sqlselect(s)
> if db.error or rs = nil then
> return -1
> end if
> n = rs.fieldcount
> rec = new databaserecord
> rec.column("userreference") = "mynewreference" //assuming col 1 is
> the reference, and you need a new one
> for i = 2 to n
> rec.column(rs.field(x).name) = rs.field(x).value //copy all
> values over
> next
> db.insertrecord "myTable", rec
> if db.error then
> return -1
> end if
> return 0
> end function
>
> Thats a quick example, but you can see how easy it is to dupe a row
> in VB or RB, cuz rs.field(x).value is a variant, and so is
> rec.column, so it just works, sometime you have to test for the
> datatype of the source column and do the right thing, it depends on
> the database.
>
> I am sure you could figure out a way in witango, but it would take a
> lot more work. Probably have to do a "describe fields of table"
> select first, so you could parse the datatypes.
>
> --
>
> Robert Garcia
> President - BigHead Technology
> VP Application Development - eventpix.com
> 13653 West Park Dr
> Magalia, Ca 95954
> ph: 530.645.4040 x222 fax: 530.645.4040
> [EMAIL PROTECTED] - [EMAIL PROTECTED]
> http://bighead.net/ - http://eventpix.com/
>
> On Jul 27, 2005, at 5:45 PM, MJPinckard wrote:
>
> > I'm trying to add an option for users to clone their user session.
> >
> > I have a table with ~500 columns (yeah I know, but that's how it's
> > structured, legacy code...ugh).
> >
> > I want to duplicate this row, changing only the primary key
> > (session_id). Since it is not inconceivable that the column
> > structure might change someday, I'd like to make the code as
> > generic as possible (not coding each column into an update or
> > insert action).
> >
> > Using directDBMS, I can easily extract all the existing data using
> > the following code.
> >
> > select * from Input where session_id=someNumber
> >
> > But this gives me data only (no column names) and in any event I
> > can't figure out how to get the data into another row. I've tried a
> > variety of different ways (using both INSERT and creating a row in
> > another action then performing an UPDATE) but can't get anything to
> > work.
> >
> > Has anyone any suggestions about how I might accomplish this? I'm
> > about to give in and manually code all 500 columns into an insert
> > action... but surely there has got to be an easier way...
> >
> > Mac OS X server
> > Witango 5.0.9 (I think)
> > Oracle db using Oracle OCI drivers.
> >
> > Thanks for any assistance.
> >
> > Cheers,
> >
> > Maggie Pinckard
> > Principal Research Associate
> > Lawrence Berkeley National Laboratory
> > ______________________________________________________________________
> > __
> > TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
> >
> >
>
> ________________________________________________________________________
> TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
>
________________________________________________________________________
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
sqltest.taf
Description: Binary data
