the user puts in all line items.
item1 x.xx
item2 x.xx
and so on
now when the user clicks on "ok" it creates a new record in the sales_db
put the time into tTime
revdb_execute(dbid,"INSERT INTO sales_db time VALUES"& tTime&"')"
then the sales_db field sales_pk automatically generates the next unique serial number.
then I need to do
put revQueryDatabase( myDbid,"SELECT sales_pk FROM sales_db") into tCs --so I have all sales_pk's then I go to the last record to identify the one I just created and I put it into a var
revMoveToLastRecord(tCs)
put revFldByName(tCs,"sales_pk) into tPk-- so now I got the pk created last , so
set the itemDelimiter to tab
loop for each line in fld line items
put item 2 of fld line items into tPrice-- and the same for items
put revdb_execute(dbid,"INSERT INTO line_items (sales_fk,(item_description,price) VALUES ('"&tPk&"'"&,"item1,x.xx)
end repeat
Now if after I created the insert, the next register (or client ) did the same between my insert and select, I'll get the next last pk. and not the one I created.
I know there is a way to get the last record created by a certain connection. something like record set from the insert just don't how to get it
Thanks,
On Sunday, July 11, 2004, at 04:58 PM, Paul Kocsis wrote:
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: <[EMAIL PROTECTED]> To: "How to use Revolution" <[EMAIL PROTECTED]> 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, [EMAIL PROTECTED] 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 [EMAIL PROTECTED] http://lists.runrev.com/mailman/listinfo/use-revolution
_______________________________________________ use-revolution mailing list [EMAIL PROTECTED] http://lists.runrev.com/mailman/listinfo/use-revolution
_______________________________________________ use-revolution mailing list [EMAIL PROTECTED] http://lists.runrev.com/mailman/listinfo/use-revolution
_______________________________________________ use-revolution mailing list [EMAIL PROTECTED] http://lists.runrev.com/mailman/listinfo/use-revolution
