SQL question

[Paul Kocsis]

What does your sql INSERT statement actually look like?  I guess I'm not
fully understanding the dilemma...I remember "way back" when using Informix,
there was a software system where it was important to obtain an Informix
"internally generated" column called 'rowid'.  (before Informix recommended
that one *not* utilize 'rowid'....or before I actually read that
recommendation ;)

...anyway, I seem to recall some dilemma in wanting to know the rowid of a
newly inserted record....so in that case, and maybe in yours, Andre's
suggestion of SELECTing for all the columns that you inserted is certainly
an avenue...provided that the collection of data from that record, as a set,
is guaranteed to be unique...if the collection of data columns is not
guaranteed to be unique...then you might have to add another column that is
either guaranteed to be unique itself, or at least make the collection of
all the inserted fields unique...for your subsequent SELECT...

...are we getting anywhere?

Paul Kocsis
----- Original Message ----- 
From: <hershbp at verizon.net>
To: "How to use Revolution" <use-revolution at lists.runrev.com>
Sent: Sunday, July 11, 2004 3:37 PM
Subject: Re: SQL question



On Sunday, July 11, 2004, at 04:07 PM, Andre Garzia wrote:

>
> Hershel,
>
> since you're doing the INSERTS you have access to the inserted data,
> can't your refine your SELECT query so that you SELECT WHERE and put
> all the inserted data as refinements, this way you'll retrieve the
> correct record. Thats what I use here.

I don't think this will work in my case because basically what I'm
inserting is just any piece of info e.g. the time  to create a new
record (in an e.g.sales table ). The db auto creates a pk , then I need
to take this pk from the sales table and insert it for a fk in a
line_items table to have all line items (every transaction related to
its sale number ) and then I take out the fk from the line_items
(SELECT") and go back to the sales_db add the sales amount to the db
with an "ALTER db SET sale_amount ='xx.xx' WHERE pk_auto = 'thepk'
Sales_db , pk_auto, sale_amount,date,time
Line_items_db, item_name,item_price,sales_fk
Thanks , Hershel

>
> Cheers
> andre
>
>
> On Jul 11, 2004, at 4:48 PM, hershbp at verizon.net wrote:
>
>> HI ,
>> How do I "INSERT" A SQL statement and return some of  the record or
>> field info  immediately ?
>> I'll try to elaborate. A database app. running many clients, I create
>> a new record and want to get the primary key of that newly created
>> record. If I'll do an "INSERT" and then go the last record via
>> "SELECT" then between the INSERT and the SELECT somebody else from a
>> different location can insert a new record then when I do the select
>> to get the last as mentioned above I'll get the wrong pk. How do I
>> overcome that ?
>> Thanks , Hershel.
>>
> -- 
> Andre Alves Garzia  2004  BRAZIL
> http://studio.soapdog.org
>
> _______________________________________________
> use-revolution mailing list
> use-revolution at lists.runrev.com
> http://lists.runrev.com/mailman/listinfo/use-revolution
>


_______________________________________________
use-revolution mailing list
use-revolution at lists.runrev.com
http://lists.runrev.com/mailman/listinfo/use-revolution